MySQL Bugs: #72702: IS NULL optimization leads to wrong results when using HANDLER

Bug #72702	IS NULL optimization leads to wrong results when using HANDLER
Submitted:	21 May 2014 4:11	Modified:	21 May 2014 7:52
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5,5.6, 5.5.38, 5.6.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	IS NULL, sql handler, wrong results

Description:
When using HANDLER, the IS NULL optimization might be inadvertently
performed on the last record read from the handler, causing problems
when the nullability differs from the actual record to be read.

The optimization seems to actually be performed using the value in
the table record buffer, which might contain the last record read
for the table, or the default value of the columns. The latter is
particular problematic if the column defaults to NULL but the record
is not actually NULL.

How to repeat:
mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT DEFAULT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> HANDLER t1 OPEN AS h1;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER `h1` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
+---+------+
1 row in set (0.00 sec)

mysql> HANDLER `h1` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
Empty set (0.00 sec)

Another variation leading to use of uninitialised data in non-debug builds:

mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT DEFAULT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES (1, NULL);
Query OK, 1 row affected (0.01 sec)

mysql> HANDLER t1 OPEN AS h1;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER `h1` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
+---+------+
| a | b    |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.00 sec)

mysql> HANDLER t1 OPEN AS h2;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER `h2` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
Empty set (0.00 sec)

mysql> HANDLER `h2` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
+---+------+
| a | b    |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.00 sec)

Hello Davi,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

// 5.5.38

mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT DEFAULT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1, NULL);
Query OK, 1 row affected (0.01 sec)

mysql> HANDLER t1 OPEN AS h1;
Query OK, 0 rows affected (0.00 sec)

mysql>  HANDLER `h1` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
+---+------+
| a | b    |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.00 sec)

mysql> HANDLER t1 OPEN AS h2;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER `h2` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
Empty set (0.00 sec)

mysql> HANDLER `h2` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
+---+------+
| a | b    |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.38-debug-log |
+------------------+
1 row in set (0.00 sec)

// 5.6.17

mysql> use test
Database changed
mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT DEFAULT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (1, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> HANDLER t1 OPEN AS h1;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER `h1` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
+---+------+
| a | b    |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.00 sec)

mysql> HANDLER t1 OPEN AS h2;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER `h2` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
Empty set (0.00 sec)

mysql> HANDLER `h2` READ `PRIMARY` = (1) WHERE TRUE AND (`b` IS NULL) OR (`b` > 5);
+---+------+
| a | b    |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.00 sec)