Description:
1. when insert table t1 (1,1),(NULL,NULL), the select query result is NULL.
mysql> create table t0(smallint_col smallint, mediumint_col mediumint);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t0 values(NULL,NULL);
Query OK, 1 row affected (0.01 sec)
mysql> create table t1(smallint_col smallint, mediumint_col mediumint);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t1 values(1,1),(NULL,NULL);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT smallint_col FROM t0 where (mediumint_col, smallint_col) <=> (SELECT mediumint_col,smallint_col FROM t1 WHERE(mediumint_col, smallint_col) < (3,3) XOR 2 LIMIT 1);
+--------------+
| smallint_col |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)
2. when insert t1 values (NULL,NULL),(1,1), the select result is empty, expect result is same:
mysql> create table t0(smallint_col smallint, mediumint_col mediumint);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t0 values(NULL,NULL);
Query OK, 1 row affected (0.01 sec)
mysql> create table t1(smallint_col smallint, mediumint_col mediumint);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t1 values(NULL,NULL),(1,1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT smallint_col FROM t0 where (mediumint_col, smallint_col) <=> (SELECT mediumint_col,smallint_col FROM t1 WHERE(mediumint_col, smallint_col) < (3,3) XOR 2 LIMIT 1);
Empty set (0.00 sec)
How to repeat:
expect select result is same:
create table t0(smallint_col smallint, mediumint_col mediumint);
insert into t0 values(NULL,NULL);
create table t1(smallint_col smallint, mediumint_col mediumint);
insert into t1 values(1,1),(NULL,NULL);
SELECT smallint_col FROM t0 where (mediumint_col, smallint_col) <=> (SELECT mediumint_col,smallint_col FROM t1 WHERE(mediumint_col, smallint_col) < (3,3) XOR 2 LIMIT 1);
delete from t1;
insert into t1 values(NULL,NULL),(1,1);
SELECT smallint_col FROM t0 where (mediumint_col, smallint_col) <=> (SELECT mediumint_col,smallint_col FROM t1 WHERE(mediumint_col, smallint_col) < (3,3) XOR 2 LIMIT 1);
