Bug #28556 MySQL 5.1.18 (Windows) cannot match CHAR columns

• Submitted: 21 May 2007 10:05
• Modified: 27 Jul 2007 21:27
• Reporter: Asuka Kenji Siu Ching Pong (Basic Quality Contributor) (OCA)
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.1.18
• OS: Windows
• Assigned to: Iggy Galarza
• CPU Architecture: Any
• Tags: cannot match, char, primary key, regression

[21 May 2007 10:05] Asuka Kenji Siu Ching Pong

Description:
This problem did not occur in MySQL 5.1.14-beta.
(I think it did not happen in 5.1.17-beta, too.)

This is a very serious problem: MySQL cannot match CHAR columns.
Please see 'How to repeat' for details.

The first set of test cases was executed in MySQL-5.1.14-beta on Linux
The second set was executed in MySQL-5.1.18-beta in the same environment.
The third set was executed in MySQL-5.1.18-beta on Windows.

I noticed the change of return type of UUID() from MySQL-5.1.14-beta to MySQL-5.1.18-beta, but it should not be the source of the problem, because MySQL-5.1.18-beta on Linux just works fine.

I didn't try MySQL-5.1.14-beta on Windows.

How to repeat:
mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.14-beta-log |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE test.temp (
    ->     ColA CHAR(36) NOT NULL PRIMARY KEY,
    ->     ColB INT NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SET @a = UUID(),
    ->     @b = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO test.temp VALUES(@a, @b);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| dddb204a-58d1-102a-9a3c-0013722182a1 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp T1
    -> WHERE  T1.ColA = @a;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| dddb204a-58d1-102a-9a3c-0013722182a1 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE test.temp T1
    -> SET    T1.ColB = 2
    -> WHERE  T1.ColA = @a;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+
1 row in set (0.01 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| dddb204a-58d1-102a-9a3c-0013722182a1 |    2 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE test.temp
    -> SELECT UUID();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> DESC test.temp;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| UUID() | varchar(36) | NO   |     |         |       |
+--------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)

--------------------------------------------------------------------------------

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.18-beta-log |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE test.temp (
    ->     ColA CHAR(36) NOT NULL PRIMARY KEY,
    ->     ColB INT NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> SET @a = UUID(),
    ->     @b = 1;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO test.temp VALUES(@a, @b);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| 8b6fcd50-58d2-102a-845e-0013722182a1 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp T1
    -> WHERE  T1.ColA = @a;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| 8b6fcd50-58d2-102a-845e-0013722182a1 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE test.temp T1
    -> SET    T1.ColB = 2
    -> WHERE  T1.ColA = @a;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| 8b6fcd50-58d2-102a-845e-0013722182a1 |    2 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE test.temp
    -> SELECT UUID();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> DESC test.temp;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| UUID() | varchar(36) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

--------------------------------------------------------------------------------

mysql> SELECT VERSION();
+--------------------------------+
| VERSION()                      |
+--------------------------------+
| 5.1.18-beta-community-nt-debug |
+--------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE TABLE test.temp (
    ->     ColA CHAR(36) NOT NULL PRIMARY KEY,
    ->     ColB INT NOT NULL
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> SET @a = UUID(),
    ->     @b = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO test.temp VALUES(@a, @b);
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| 0fedc11b-58d2-102a-8b86-017d6fb74843 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp T1
    -> WHERE  T1.ColA = @a;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| 0fedc11b-58d2-102a-8b86-017d6fb74843 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE test.temp T1
    -> SET    T1.ColB = 2
    -> WHERE  T1.ColA = @a;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql>
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| 0fedc11b-58d2-102a-8b86-017d6fb74843 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE TABLE test.temp
    -> SELECT UUID();
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> DESC test.temp;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| UUID() | varchar(36) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

--------------------------------------------------------------------------------

[21 May 2007 10:10] Asuka Kenji Siu Ching Pong

The Test Case

Attachment: test_case.sql (application/octet-stream, text), 525 bytes.

[21 May 2007 10:11] Asuka Kenji Siu Ching Pong

The Test Result in 5.1.14-beta on Linux

Attachment: 5.1.14-beta-Linux.txt (application/x-txt, text), 1.72 KiB.

[21 May 2007 10:11] Asuka Kenji Siu Ching Pong

The Test Result in 5.1.18-beta on Linux

Attachment: 5.1.18-beta-Linux.txt (application/x-txt, text), 1.72 KiB.

[21 May 2007 10:11] Asuka Kenji Siu Ching Pong

The Test Result in 5.1.18-beta on Windows

Attachment: 5.1.18-beta-Windows.txt (application/x-txt, text), 1.79 KiB.

[21 May 2007 11:05] Valeriy Kravchuk

Thank you for a problem report. Sorry, but on both 5.0.42-BK and 5.1.19-BK I've got the following results that looks correct for me:

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 3
Server version: 5.0.42-debug Source distribution

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

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.0.42-debug |
+--------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE test.temp (
    ->     ColA CHAR(36) NOT NULL PRIMARY KEY,
    ->     ColB INT NOT NULL
    -> );

SQuery OK, 0 rows affected (0.02 sec)

mysql>
mysql> SET @a = UUID(),
    ->     @b = 1;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO test.temp VALUES(@a, @b);

Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| f66dbc58-543f-102a-8e1a-000c299a6965 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp T1
    -> WHERE  T1.ColA = @a;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| f66dbc58-543f-102a-8e1a-000c299a6965 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE test.temp T1
    -> SET    T1.ColB = 2
    -> WHERE  T1.ColA = @a;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+

| ColA                                 | ColB |
+--------------------------------------+------+
| f66dbc58-543f-102a-8e1a-000c299a6965 |    2 |
+--------------------------------------+------+
1 row in set (0.01 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
 Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE test.temp
    -> SELECT UUID();

DQuery OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> DESC test.temp;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| UUID() | varchar(36) | NO   |     |         |       |
+--------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/5.0/var/suse.pid
070515 17:27:19  mysqld ended

[1]+  Done                    bin/mysqld_safe
openxs@suse:~/dbs/5.0> cd ../5.1/
openxs@suse:~/dbs/5.1> bin/mysqld_safe &
[1] 6756
openxs@suse:~/dbs/5.1> Starting mysqld daemon with databases from /home/openxs/d
bs/5.1/var

openxs@suse:~/dbs/5.1> 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
Server version: 5.1.19-beta Source distribution

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

mysql> SELECT VERSION();
+-------------+
| VERSION()   |
+-------------+
| 5.1.19-beta |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE test.temp (
    ->     ColA CHAR(36) NOT NULL PRIMARY KEY,
    ->     ColB INT NOT NULL
    -> );

SET @a Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> SET @a = UUID(),
    ->     @b = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO test.temp VALUES(@a, @b);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| aa2884d0-5440-102a-b3fc-000c299a6965 |    1 |
+--------------------------------------+------+
1 row in set (0.01 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp T1
    -> WHERE  T1.ColA = @a;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| aa2884d0-5440-102a-b3fc-000c299a6965 |    1 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE test.temp T1
    -> SET    T1.ColB = 2
    -> WHERE  T1.ColA = @a;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT *
    -> FROM   test.temp;
+--------------------------------------+------+
| ColA                                 | ColB |
+--------------------------------------+------+
| aa2884d0-5440-102a-b3fc-000c299a6965 |    2 |
+--------------------------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS test.temp;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE test.temp
    -> SELECT UUID();

DESCQuery OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> DESC test.temp;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| UUID() | varchar(36) | NO   |     |         |       |
+--------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

So, looks like this bug (I confirm there is a bug in 5.1.18) is already fixed somehow.

[22 May 2007 2:39] Asuka Kenji Siu Ching Pong

As the problem only occurs on *Windows*, I think it is better to execute the tests on Windows (with the binaries downloaded from your site) to confirm that. Besides, I executed the tests on my colleage's platform, MySQL-5.0.33 on Windows, and found that this problem did not occur.

I am trying to re-install MySQL-5.1.18-beta on Windows and see whether the same problem occurs.

[22 May 2007 3:51] Asuka Kenji Siu Ching Pong

I have done some more tests. Let me summarize the results:

MySQL-5.0.33, Windows: Passed the test cases I uploaded. Proprietary test cases not executed because 5.0 does not support some of the constructs.

MySQL-5.1.14-beta, Linux: Passed all test cases.

MySQL-5.1.17-beta, Linux: Passed all test cases.

MySQL-5.1.17-beta, Windows: Passed all test cases.

MySQL-5.1.18-beta, Linux: Passed the test cases I uploaded, but failed my project's test cases. Under some situations, the CHAR columns could not match. I can reproduce it every time in my project's test cases, but since the test cases contain proprietary code (table structures, stored procedures, algorithms, etc) so I cannot upload them. I still cannot extract / generalize the main problem yet, but I guess that it may be related to 'SELECT ... FOR UPDATE', 'DECLARE CONDITION', or 'DECLARE HANDLER', etc.

MySQL-5.1.18-beta, Windows: Failed all test cases.

[22 May 2007 11:11] Valeriy Kravchuk

I was able to repeat the problem you described with 5.1.18 on Windows (see my final comment in last message). But according to our rules, if you are not a customer, I am forced to verify on current, latest sources. I was not able to repeat the problem there. So, it may be already fixed.

OK, this is a verified bug in our 5.1.18 binaries on Windows XP.

[3 Jun 2007 4:27] Bugs System

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/commits/28001

ChangeSet@1.2535, 2007-06-03 00:26:38-04:00, iggy@amd64.(none) +2 -0
  Bug#28556 MySQL 5.1.18 (Windows) cannot match CHAR columns
  - Cannot duplicate problem, adding test case to prevent future 
  regressions.