MySQL Bugs: #22671: Server prepared statement may return incorrect result

Bug #22671	Server prepared statement may return incorrect result
Submitted:	25 Sep 2006 15:38	Modified:	25 Oct 2006 16:31
Reporter:	Xavier FOURNET	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.0.24a	OS:	Windows (Win32-x86)
Assigned to:		CPU Architecture:	Any

Description:
We make a SELECT query by using a server prepared statement. This query contains a LEFT JOIN.
Successive execution of this prepared statement may produce incorrect results.
The prepared statement seems to be corrupted as soon as a query is done with a non found join row. See test case below.

Also note that this behaviour depend on the JOIN colum charset and collation.

Bug appears with binary collate :
CHARACTER SET ucs2 COLLATE ucs2_bin (as in our test case, we use this one in our products)
CHARACTER SET utf8 COLLATE utf8_bin
CHARACTER SET latin1 COLLATE latin1_bin

Bug doesn't appear with case insensitive collate :
CHARACTER SET utf8 COLLATE utf8_general_ci
CHARACTER SET ucs2 COLLATE ucs2_general_ci
CHARACTER SET latin1 COLLATE latin1_general_ci

How to repeat:
/**** Test case initialization *****/

drop table t2;
drop table t1;

create table t1(id int not null, key1 varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin, PRIMARY KEY(id));
create table t2(id int not null, key2 varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin, tkey1 int NULL, PRIMARY KEY(id));

alter table t2 add constraint UQ_t2 unique (key2);
alter table t1 add constraint UQ_t1 unique (key1);
alter table t2 add constraint FK_t2_t1 foreign key (tkey1) REFERENCES t1 (id);

INSERT INTO t1(id,key1) VALUES (1,'value 1');
INSERT INTO t2(id,key2,tkey1) VALUES (1,'val1',NULL);
INSERT INTO t2(id,key2,tkey1) VALUES (2,'val2',1);

/**** Test case ****/

PREPARE stmt1 FROM "SELECT t2.id,t2.key2,t2.tkey1,t1.id,t1.key1 FROM t2 LEFT OUTER JOIN t1 ON (t2.tkey1=t1.id) WHERE t2.key2=?";

/* First execution with 'val1' is OK */
SET @stmt1_param0='val1';
EXECUTE stmt1 USING @stmt1_param0;

/* Next executions with 'val2' are KO */
SET @stmt1_param0='val2';
EXECUTE stmt1 USING @stmt1_param0;
EXECUTE stmt1 USING @stmt1_param0;

/* Reinitializing the prepared statement make it work with 'val2' */
PREPARE stmt1 FROM "SELECT t2.id,t2.key2,t2.tkey1,t1.id,t1.key1 FROM t2 LEFT OUTER JOIN t1 ON (t2.tkey1=t1.id) WHERE t2.key2=?";
EXECUTE stmt1 USING @stmt1_param0;

/* Execution with 'val1' is OK */
SET @stmt1_param0='val1';
EXECUTE stmt1 USING @stmt1_param0;

/* Next executions with 'val2' are KO */
SET @stmt1_param0='val2';
EXECUTE stmt1 USING @stmt1_param0;
EXECUTE stmt1 USING @stmt1_param0;

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.26-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.26-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t2;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(id int not null, key1 varchar(255) CHARACTER SET ucs2 COLLATE
    -> ucs2_bin, PRIMARY KEY(id));
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(id int not null, key2 varchar(255) CHARACTER SET ucs2 COLLATE
    -> ucs2_bin, tkey1 int NULL, PRIMARY KEY(id));

Query OK, 0 rows affected (0.01 sec)

mysql> alter table t2 add constraint UQ_t2 unique (key2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 add constraint UQ_t1 unique (key1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t2 add constraint FK_t2_t1 foreign key (tkey1) REFERENCES t1
 (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1(id,key1) VALUES (1,'value 1');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t2(id,key2,tkey1) VALUES (1,'val1',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2(id,key2,tkey1) VALUES (2,'val2',1);
Query OK, 1 row affected (0.00 sec)

mysql> PREPARE stmt1 FROM "SELECT t2.id,t2.key2,t2.tkey1,t1.id,t1.key1 FROM t2
LEFT
    "> OUTER JOIN t1 ON (t2.tkey1=t1.id) WHERE t2.key2=?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @stmt1_param0='val1';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+------+------+
| id | key2 | tkey1 | id   | key1 |
+----+------+-------+------+------+
|  1 | val1 |  NULL | NULL | NULL |
+----+------+-------+------+------+
1 row in set (0.00 sec)

mysql> SET @stmt1_param0='val2';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+----+---------+
| id | key2 | tkey1 | id | key1    |
+----+------+-------+----+---------+
|  2 | val2 |     1 |  1 | value 1 |
+----+------+-------+----+---------+
1 row in set (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+----+---------+
| id | key2 | tkey1 | id | key1    |
+----+------+-------+----+---------+
|  2 | val2 |     1 |  1 | value 1 |
+----+------+-------+----+---------+
1 row in set (0.00 sec)

mysql> PREPARE stmt1 FROM "SELECT t2.id,t2.key2,t2.tkey1,t1.id,t1.key1 FROM t2
LEFT
    "> OUTER JOIN t1 ON (t2.tkey1=t1.id) WHERE t2.key2=?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt1 USING @stmt1_param0; .ke
+----+------+-------+----+---------+
| id | key2 | tkey1 | id | key1    |
+----+------+-------+----+---------+
|  2 | val2 |     1 |  1 | value 1 |
+----+------+-------+----+---------+
1 row in set (0.00 sec)

mysql> SET @stmt1_param0='val1';.ke
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+------+------+
| id | key2 | tkey1 | id   | key1 |
+----+------+-------+------+------+
|  1 | val1 |  NULL | NULL | NULL |
+----+------+-------+------+------+
1 row in set (0.00 sec)

mysql> SET @stmt1_param0='val2';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+----+---------+
| id | key2 | tkey1 | id | key1    |
+----+------+-------+----+---------+
|  2 | val2 |     1 |  1 | value 1 |
+----+------+-------+----+---------+
1 row in set (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+----+---------+
| id | key2 | tkey1 | id | key1    |
+----+------+-------+----+---------+
|  2 | val2 |     1 |  1 | value 1 |
+----+------+-------+----+---------+
1 row in set (0.00 sec)

I do not see any inconsisten results. Looks like the bug, if any, is already fixed.

Thanks you for the feedback! Please could you check with the version 5.0.25 too or indicate to me how to download pre release of future MySQL version ? I have to communicate on which MySQL release we will be compatible.

For information, I get the following result with 5.0.24a on Windows XP :

E:\sgbd\mysql\5.0.24a\bin>mysql -u root -p test
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24a-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> /**** Test case initialization *****/
mysql>
mysql> drop table t2;
ERROR 1051 (42S02): Unknown table 't2'
mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql>
mysql> create table t1(id int not null, key1 varchar(255) CHARACTER SET ucs2 COLLATE
    -> ucs2_bin, PRIMARY KEY(id));
Query OK, 0 rows affected (0.45 sec)

mysql> create table t2(id int not null, key2 varchar(255) CHARACTER SET ucs2 COLLATE
    -> ucs2_bin, tkey1 int NULL, PRIMARY KEY(id));
Query OK, 0 rows affected (0.19 sec)

mysql>
mysql> alter table t2 add constraint UQ_t2 unique (key2);
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 add constraint UQ_t1 unique (key1);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t2 add constraint FK_t2_t1 foreign key (tkey1) REFERENCES t1 (id);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO t1(id,key1) VALUES (1,'value 1');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2(id,key2,tkey1) VALUES (1,'val1',NULL);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2(id,key2,tkey1) VALUES (2,'val2',1);
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> /**** Test case ****/
mysql>
mysql> PREPARE stmt1 FROM "SELECT t2.id,t2.key2,t2.tkey1,t1.id,t1.key1 FROM t2 LEFT
    "> OUTER JOIN t1 ON (t2.tkey1=t1.id) WHERE t2.key2=?";
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql>
mysql> /* First execution with 'val1' is OK */
mysql> SET @stmt1_param0='val1';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+------+------+
| id | key2 | tkey1 | id   | key1 |
+----+------+-------+------+------+
|  1 | val1 |  NULL | NULL | NULL |
+----+------+-------+------+------+
1 row in set (0.02 sec)

mysql>
mysql> /* Next executions with 'val2' are KO */
mysql> SET @stmt1_param0='val2';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+------+------+
| id | key2 | tkey1 | id   | key1 |
+----+------+-------+------+------+
|  2 | val2 |     1 | NULL | NULL |
+----+------+-------+------+------+
1 row in set (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+------+------+
| id | key2 | tkey1 | id   | key1 |
+----+------+-------+------+------+
|  2 | val2 |     1 | NULL | NULL |
+----+------+-------+------+------+
1 row in set (0.00 sec)

mysql>
mysql> /* Reinitializing the prepared statement make it work with 'val2' */
mysql> PREPARE stmt1 FROM "SELECT t2.id,t2.key2,t2.tkey1,t1.id,t1.key1 FROM t2 LEFT
    "> OUTER JOIN t1 ON (t2.tkey1=t1.id) WHERE t2.key2=?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+----+---------+
| id | key2 | tkey1 | id | key1    |
+----+------+-------+----+---------+
|  2 | val2 |     1 |  1 | value 1 |
+----+------+-------+----+---------+
1 row in set (0.00 sec)

mysql>
mysql> /* Execution with 'val1' is OK */
mysql> SET @stmt1_param0='val1';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+------+------+
| id | key2 | tkey1 | id   | key1 |
+----+------+-------+------+------+
|  1 | val1 |  NULL | NULL | NULL |
+----+------+-------+------+------+
1 row in set (0.00 sec)

mysql>
mysql> /* Next executions with 'val2' are KO */
mysql> SET @stmt1_param0='val2';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+------+------+
| id | key2 | tkey1 | id   | key1 |
+----+------+-------+------+------+
|  2 | val2 |     1 | NULL | NULL |
+----+------+-------+------+------+
1 row in set (0.00 sec)

mysql> EXECUTE stmt1 USING @stmt1_param0;
+----+------+-------+------+------+
| id | key2 | tkey1 | id   | key1 |
+----+------+-------+------+------+
|  2 | val2 |     1 | NULL | NULL |
+----+------+-------+------+------+
1 row in set (0.00 sec)

mysql>

5.0.25 will not be released to community, so no need to test. Next official GA release will be 5.0.26. Current snapshots of the source code can be downloaded from http://downloads.mysql.com/snapshots.php.

I confirm that the bug that was present with 5.0.24a is fixed on 5.0.26