MySQL Bugs: #9442: Set parameter make query fail if column character set is UCS2

Bug #9442	Set parameter make query fail if column character set is UCS2
Submitted:	29 Mar 2005 5:56	Modified:	18 Jul 2005 19:59
Reporter:	River Lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Any (All)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Query fails for PreparedStatement to set parameters if column character set is UCS2, when connectj is 3.1.7 or 3.2.0-alpha, but get correct results when 3.0.16. However, it won't get this problem if put the parameters directly in sql statement

Enviroment:
mysql 4.1.10a-nt with default-character-set (ISO 8859-1)
Both database and table are created with default-character-set

How to repeat:
1. create table t1 (
     etext varchar(20),
     gtext varchar(20) CHARACTER SET GBK,
     utext varchar(20) CHARACTER SET UCS2);
   insert into t1 values ("river", "river", "river");
   insert into t1 values ("lily", "lily", "lily");
   commit;

2. Modify corresponding DB parameters and run Test.java:
import java.sql.*;

public class Test {
  private static String DB_URL = "jdbc:mysql://localhost:3306/quickstart";
  private static String DB_USER = "root";
  private static String DB_PWD = "password";

  public static void run (String col, boolean direct) {
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      Connection con = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
      String statement = "select " + col + " from t1 where " + col + " like " + (direct ? "'%%'" : "?");
      PreparedStatement prep = con.prepareStatement(statement);
      if (!direct)
        prep.setString(1, "%%");
      ResultSet rs = prep.executeQuery();
      int count = 0;
      while (rs.next()) {
        System.out.println(rs.getString(1));
        count++;
      }
      prep.close();
      con.close();
      System.out.println("Find " + count + " items totally by " + col + (direct ? " " : " un") + "directly\n");
    } catch (Exception e) {
      System.out.println(e);
    }
  }

  public static void main (String[] args) {
    run ("etext", false);
    run ("gtext", false);
    run ("utext", false);
    run ("utext", true);
  }
}

3. Get the results:
---------- Java ----------
river
lily
Find 2 items totally by etext undirectly

river
lily
Find 2 items totally by gtext undirectly

Find 0 items totally by utext undirectly          -> 2 items expected

river
lily
Find 2 items totally by utext directly

Suggested fix:
refer to connectj 3.0.16

Test case

Attachment: Test.java (application/octet-stream, text), 1.09 KiB.

This is a server bug with prepared statements, see the following testcase:

I changed the category appropriately.

mysql> create table t1 (      etext varchar(20),      gtext varchar(20) CHARACTER SET GBK,      utext varchar(20) CHARACTER SET UCS2);
Query OK, 0 rows affected (0.03 sec)

mysql>    insert into t1 values ("lily", "lily", "lily"); Query OK, 1 row affected (0.00 sec)

mysql>    insert into t1 values ("river", "river", "river"); Query OK, 1 row affected (0.00 sec)

mysql> prepare foo from 'select utext from t1 where utext like ?'; Query OK, 0 rows affected (0.02 sec)
Statement prepared

mysql> set @param1='%%';
Query OK, 0 rows affected (0.00 sec)

mysql> execute foo using @param1;
Empty set (0.02 sec)

mysql> select utext from t1 where utext like '%%'; +-------+
| utext |
+-------+
| lily  |
| river |
+-------+
2 rows in set (0.00 sec)

Verified w/4.1.12 from bk

A simplified version reproducing the problem looks like:

#
# Bug#9442 Set parameter make query fail if column character set is UCS2
#
create table t1 (utext varchar(20) character set ucs2);
insert into t1 values ("lily");
insert into t1 values ("river");
prepare foo from 'select utext from t1 where utext like ?';
set @param1='%%';
execute foo using @param1;
drop table t1;

Added a test case to the test suite, the bug can not be repeated after the fix for Bug#9379.
(http://lists.mysql.com/internals/27029)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27051

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27179

Noted in 4.1.14, 5.0.10 changelogs.