second use of prepared statement results in leading coma with group_concat

[GoogleCodeExporter]

What steps will reproduce the problem?
1. use prepared statement (see code below)
2. call same group_concat sql statement twice
3. second call returns a leading coma

What is the expected output? What do you see instead?
expected:
name = Y,abc
name = Y
name = Y,abc
name = Y,abc
seen:
name = Y,abc
name = Y
name = Y,abc
name = ,Y,abc

What version of the product are you using? On what operating system?

Please provide any additional information below.
public class SQLiteJDBCBug {

  public static void main(String[] args) throws Exception {

    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

    Connection connection = null;
    try {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:test.db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30); // set timeout to 30 sec.

      statement.executeUpdate("drop table if exists person");
      statement.executeUpdate("create table person (id integer, name 
string, shortname string)");
      statement.executeUpdate("insert into person values(1, 'leo','L')");
      statement.executeUpdate("insert into person values(2, 'yui','Y')");
      statement.executeUpdate("insert into person values(3, 'abc', null)");

      statement.executeUpdate("drop table if exists message");
      statement.executeUpdate("create table message (id integer, subject 
string)");
      statement.executeUpdate("insert into message values(1, 'Hello')");
      statement.executeUpdate("insert into message values(2, 'World')");

      statement.executeUpdate("drop table if exists mxp");
      statement.executeUpdate("create table mxp (pid integer, mid integer, 
type string)");
      statement.executeUpdate("insert into mxp values(1,1, 'F')");
      statement.executeUpdate("insert into mxp values(2,1,'T')");
      statement.executeUpdate("insert into mxp values(1,2, 'F')");
      statement.executeUpdate("insert into mxp values(2,2,'T')");
      statement.executeUpdate("insert into mxp values(3,2,'T')");

      ResultSet rs = statement.executeQuery("select 
group_concat(ifnull(shortname, name)) from mxp, person where mxp.mid=2 and 
mxp.pid=person.id and mxp.type='T'");
      while (rs.next()) {
        // read the result set
        System.out.println("name = " + rs.getString(1));
      }
      rs = statement.executeQuery("select group_concat(ifnull(shortname, 
name)) from mxp, person where mxp.mid=1 and mxp.pid=person.id and 
mxp.type='T'");
      while (rs.next()) {
        // read the result set
        System.out.println("name = " + rs.getString(1));
      }

      PreparedStatement ps = connection.prepareStatement("select 
group_concat(ifnull(shortname, name)) from mxp, person where mxp.mid=? and 
mxp.pid=person.id and mxp.type='T'");
      ps.clearParameters();
      ps.setInt(1, new Integer(2));
      rs = ps.executeQuery();
      while (rs.next()) {
        // read the result set
        System.out.println("name = " + rs.getString(1));
      }
      ps.clearParameters();
      ps.setInt(1, new Integer(2));
      rs = ps.executeQuery();
      while (rs.next()) {
        // read the result set
        System.out.println("name = " + rs.getString(1));
      }

    } catch (SQLException e) {
      // if the error message is "out of memory",
      // it probably means no database file is found
      System.err.println(e.getMessage());
    } finally {
      try {
        if (connection != null)
          connection.close();
      } catch (SQLException e) {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}

Original issue reported on code.google.com by simon.he...@gmail.com on 12 May 2009 at 7:34

[GoogleCodeExporter]

wrong category, should be sqlite-jdbc

Original comment by simon.he...@gmail.com on 12 May 2009 at 7:35

[GoogleCodeExporter]

version 3.6.14, windows, JDK 1.6.0_10

Original comment by simon.he...@gmail.com on 12 May 2009 at 7:36

[GoogleCodeExporter]

Original comment by taroleo on 12 May 2009 at 10:59

• Added labels: Project-sqlite-jdbc, Version-3.6.4
• Removed labels: Project-xerial-core, Version-1.0

[GoogleCodeExporter]

Original comment by taroleo on 12 May 2009 at 11:05

• Added labels: Version-3.6.14
• Removed labels: Version-3.6.4

[GoogleCodeExporter]

bug has been introduced with 3.6.14. Previous versions work correctly (I 
verified with 
3.6.7, 3.6.10, 3.6.11.2, and 3.6.13)

Original comment by simon.he...@gmail.com on 18 May 2009 at 5:48

[GoogleCodeExporter]

Confirmed the error can be reproduced.

Original comment by taroleo on 19 May 2009 at 1:22

• Changed state: Accepted

[GoogleCodeExporter]

This bug was introduced by the original SQLite. 
http://www.sqlite.org/cvstrac/tktview?tn=3841

Original comment by taroleo on 19 May 2009 at 1:43

[GoogleCodeExporter]

Fixed in sqlite-jdbc-3.6.14.1
http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.6.14.1/

Original comment by taroleo on 19 May 2009 at 2:07

• Changed state: Fixed
• Added labels: OpSys-All
• Removed labels: OpSys-Windows