MySQL Bugs: #52173: Reading NULL value from non-NULL index gives wrong result in embedded server

Bug #52173	Reading NULL value from non-NULL index gives wrong result in embedded server
Submitted:	18 Mar 2010 10:48	Modified:	11 Apr 2018 12:30
Reporter:	Martin Hansson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Embedded Library ( libmysqld )	Severity:	S3 (Non-critical)
Version:	6.0, 5.1-bugteam	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
This bug was exposed by merging the fix for Bug#47762 to 6.0. The optimization that reads a MIN or MAX value directly from the index fails to (fail to) fetch a NULL value from a NOT NULL column. Obviously, such a read should fail. Now however, it appears to succeed, yielding a wrong result.

- Fails in embedded mode on 32-bit linux.
- On Mac OS 10.6, this fails even in normal mode.
- On Mac OS 10.6, this core dumps in embedded server mode.

How to repeat:
Run in embedded server mode:

CREATE TABLE t1 ( a INT NOT NULL, KEY(a) );
INSERT INTO t1 VALUES (1), (2), (3);
SELECT min(a) FROM t1 WHERE a <=> NULL;
DROP TABLE t1;

Suggested fix:
The fix does not appear to be in matching_cond(), the function patched by fix for #47762. This function correctly reports that the NOT NULL index can indeed be used for finding whether a NULL value is there or not. Some other code appears to fail, and only in embedded server

Thank you for the bug report. I am getting same result on embedded server 6.0 and 6.0/5.0 normal server (5.1 could be the same). Please print here your outputs:

[miguel@hegel ~]$ dbs/6.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: 6.0.12-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> CREATE TABLE t1 ( a INT NOT NULL, KEY(a) );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT min(a) FROM t1 WHERE a <=> NULL;
+--------+
| min(a) |
+--------+
|      3 | 
+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 DROP INDEX a;
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT min(a) FROM t1 WHERE a <=> NULL;
+--------+
| min(a) |
+--------+
|   NULL | 
+--------+
1 row in set (0.01 sec)

[miguel@hegel ~]$ dbs/6.0/bin/mysqladmin -uroot shutdown 
[miguel@hegel ~]$ bzr/6.0/libmysqld/examples/mysql 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 0
Server version: 6.0.12-alpha-embedded-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> CREATE TABLE t1 ( a INT NOT NULL, KEY(a) );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT min(a) FROM t1 WHERE a <=> NULL;
+--------+
| min(a) |
+--------+
|      3 | 
+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 DROP INDEX a;
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT min(a) FROM t1 WHERE a <=> NULL;
+--------+
| min(a) |
+--------+
|   NULL | 
+--------+
1 row in set (0.00 sec)

mysql> 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> CREATE TABLE t1 ( a INT NOT NULL, KEY(a) );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT min(a) FROM t1 WHERE a <=> NULL;
+--------+
| min(a) |
+--------+
|      3 | 
+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 DROP INDEX a;
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT min(a) FROM t1 WHERE a <=> NULL;
+--------+
| min(a) |
+--------+
|   NULL | 
+--------+
1 row in set (0.02 sec)

mysql>

The bug appears on 5.1 as well. Please note that Bug#47762 was not pushed to 5.0, hence you may be observing that bug on this version.

The embedded server has been removed in 8.0