MySQL Bugs: #11933: NULL parameter IS NULL OR AND wrong evaluation

Bug #11933	NULL parameter IS NULL OR AND wrong evaluation
Submitted:	14 Jul 2005 11:02	Modified:	1 Aug 2005 17:43
Reporter:	Berto van de Kraats	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.10-beta	OS:	Linux (Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
If a query contains the construction below, then evaluation is incorrect. The first parameter is NULL, so the condition "a0.t_empno = ?" is FALSE. The second parameter is also NULL, so "? IS NULL" is TRUE. The third parameter is 10, so for t_empno = 10 this condition is TRUE. For t_empno = 10 the entire condition should evaluate to TRUE. But in fact, the query does not return the row where empno = 10

   a0.t_empno = ? OR (? IS NULL and a0.t_empno = ?)

How to repeat:
Compile and run the attached program (which is an automatically generated trace of our mysql database driver). The problem reproduces if the program silently runs and has exit status 0.

The trace contains a call to mysql_stmt_fetch that returns no data (search for pattern "ERROR:"), where it should return the value 10.

Cpp trace of problem

Attachment: bug11933.cpp (text/plain), 24.70 KiB.

Thank you for the bug report.

Noticed that on my side I got a crash (looks like same source from other bugs reported):

[New Thread 163851 (LWP 1908)]
050714 13:26:17 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.10-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 180236 (LWP 1910)]
[Thread 180236 (LWP 1910) exited]
[New Thread 196621 (LWP 1914)]
[New Thread 213006 (LWP 1915)]
[Thread 196621 (LWP 1914) exited]
050714 13:26:45  InnoDB: Error: MySQL is freeing a thd
InnoDB: though trx->n_mysql_tables_in_use is 1
InnoDB: and trx->mysql_n_tables_locked is 0.
TRANSACTION 0 4358, not started, process no 1915, OS thread id 213006
mysql tables in use 1, locked 0
MySQL thread id 3, query id 32 localhost root
 len 676; hex 065c6e0548a4640801000000010000001568d6420200000001000000010000000000000006110000ffffffff0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000ffffffffffffffff0000000000000000000000001d9c02000000000000000000000000000000000048c1e30880c8e3080000000000000000000000008ab0640800000000000000000000000000000000000000000e4003007b07000001000000000000000000000000000000102700000000000000000000000000000000000000000000000000000000000000000000000000000a00000000000000e8961741010000000000000000000000000000000000000000000000010000000100000000000000090000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007000000000000000000000000000000ffffffff28021841000000000000000000000000281a18410000000000000000000000000000000000000000000000000000000000000000020000000000000000000000000000000000000000000000030000000000000000000000c08e65082b000000000000000e4003000000000000000000ccae1841bc02000080b064087800000081f20e000100000000000000000000000000000000000000000000000000000000000000000000008bb06408000000000000000000000000000000000000000000000000000000000000000000000000000000000000000068d01841; asc  \n H d          h B                                                                                                                                                                                                            H                     d                      @  {                    '                                                 A                                                                                                                (  A            (  A                                                                      e +        @             A      d x                                             d                                             h  A;InnoDB: Apparent memory corruption: mem dump  len 500; hex 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010400000000000000000000000000009268a52078307472782e6300510000000000000000000000000000000000000000000000e80200000000000000000000e80200004000000000000000000000001e8fa90048a4640801000000010000001568d6420200000001000000010000000000000006110000ffffffff0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000ffffffffffffffff0000000000000000000000001d9c02000000000000000000000000000000000048c1e30880c8e3080000000000000000000000008ab064080000; asc                                                                                                                                                                                            h  x0trx.c Q                                       @               H d          h B                                                                                                                                                                                                            H                     d   ;
InnoDB: Scanning backward trying to find previous allocated mem blocks
Freed mem block at - 64, file x0trx.c, line 81
Mem block at - 1088, file t0mem.c, line 194
Mem block at - 1344, file m0rec.c, line 1218
Mem block at - 1600, file 0pcur.c, line 29
Mem block at - 2112, file 0dict.c, line 3628
Mem block at - 2624, file t0mem.c, line 194
Mem block at - 3136, file mysql.c, line 591
Mem block at - 3648, file 0dict.c, line 3628
Mem block at - 4160, file t0mem.c, line 194
Mem block at - 4672, file t0mem.c, line 47
InnoDB: Scanning forward trying to find next allocated mem blocks
Freed mem block at + 960, file 0roll.c, line 67
Freed mem block at + 1984, file 0roll.c, line 1123
Mem block at + 3008, file w0sel.c, line 2924
Mem block at + 3520, file t0mem.c, line 194
Mem block at + 3776, file 0pcur.c, line 29
Mem block at + 4032, file t0mem.c, line 194
Mem block at + 4544, file 0dict.c, line 3628
Mem block at + 5056, file w0sel.c, line 2924
Mem block at + 5568, file t0mem.c, line 194
Mem block at + 6080, file t0mem.c, line 194
050714 13:26:45InnoDB: Assertion failure in thread 213006 in file ha_innodb.cc line 749
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 213006 (LWP 1915)]
0x082a6c14 in check_trx_exists (thd=0x8e3c148) at ha_innodb.cc:749
749                             ut_a(0);
(gdb)

evgen@moonbone mysql-5.0-bug-11933 $ g++ -g -I./include bug11933.cpp ./libmysql/.libs/libmysqlclient.a -lm -lz -lstdc++ -o bug11933
evgen@moonbone mysql-5.0-bug-11933 $ ./bug11933 
data_4 is 10 0 0 0
bug11933: bug11933.cpp:149: int main(): Assertion `mysql_ret==100' failed.
Aborted
evgen@moonbone mysql-5.0-bug-11933 $ diff -u bug11933.cpp.orig bug11933.cpp
--- bug11933.cpp.orig   2005-08-02 01:30:06.000000000 +0400
+++ bug11933.cpp        2005-08-02 01:40:27.000000000 +0400
@@ -30,8 +30,8 @@
 const char *HOST_NAME = NULL;
 const char *USER_NAME = "bsp";
 const char *USER_PWD = "bsp";
-const char *DB_NAME = "baan";
-const char *UNIX_SOCKET = "/var/lib/mysql/mysql.sock";
+const char *DB_NAME = "test";
+const char *UNIX_SOCKET = "/tmp/mysql.sock";
 // For ddl statements we use a different user/pwd
 const char *OWNER_NAME = "baan";
 const char *OWNER_PWD = "baan";
@@ -144,7 +144,9 @@
 // ERROR: the following call returns no data, while it should return t_empno = 10,
 // because 10 = <NULL> OR ( <NULL> IS NULL AND 10 = 10 ) should evaluate to TRUE.
 
-       mysql_ret = mysql_stmt_fetch(stmt_1); assert(mysql_ret==100);
+       mysql_ret = mysql_stmt_fetch(stmt_1); 
+        printf("data_4 is %i %i %i %i\n",(int)data_4[0],(int)data_4[1],(int)data_4[2],(int)data_4[3] );
+        assert(mysql_ret==100);
        mysql_ret = mysql_stmt_free_result(stmt_1); assert(mysql_ret==0);
        mysql_ret = mysql_stmt_reset(stmt_1); assert(mysql_ret==0);
        mysql_ret = mysql_stmt_close(stmt_1); assert(mysql_ret==0);
@@ -222,7 +224,7 @@
        fprintf(fp,"%s\n","/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;");
        fprintf(fp,"%s\n","");
        fclose(fp);
-       std::string cmd = std::string("mysql");
+       std::string cmd = std::string("client/mysql");
        if ( OWNER_NAME ) cmd += std::string(" --user=") + OWNER_NAME;
        if ( OWNER_PWD ) cmd += std::string(" --password=") + OWNER_PWD;
        if ( HOST_NAME ) cmd += std::string(" --host=") + HOST_NAME;

tested on mysql version 5.0.10/5.0.11 
Linux  2.6.11-gentoo-r7 x86_64 AMD