Description:
If a table insert a illegal value, which may be treated as NULL, mysql may return different query result for "IS NULL" condition checking in the below two situations:
1. direct query on this table
2. pack the table into a table subquery, and do query on this subquery
How to repeat:
mysql> create table t2 (pk int primary key, col_int_null_key int, col_date_not_null date not null);
Query OK, 0 rows affected (0.41 sec)
mysql> insert into t2 values (1, 2, '0000-00-00');
Query OK, 1 row affected (0.11 sec)
mysql> insert into t2 values (2, 2, '0000-00-00');
Query OK, 1 row affected (0.13 sec)
mysql> insert into t2 values (3, 3, '0000-00-00');
Query OK, 1 row affected (0.14 sec)
mysql> insert into t2 values (4, 3, '0000-00-00');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT MIN( `pk`) AS field1, `col_int_null_key` AS ifield1 FROM (select * from t2) AS alias1 WHERE alias1.`col_date_not_null` IS NULL GROUP BY ifield1;
Empty set (0.00 sec)
mysql> SELECT MIN( `pk`) AS field1, `col_int_null_key` AS ifield1 FROM t2 AS alias1 WHERE alias1.`col_date_not_null` IS NULL GROUP BY ifield1;
+--------+---------+
| field1 | ifield1 |
+--------+---------+
| 1 | 2 |
| 3 | 3 |
+--------+---------+
2 rows in set (0.00 sec)
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> select @@version;
+------------+
| @@version |
+------------+
| 5.7.13-log |
+------------+
1 row in set (0.00 sec)
mysql> explain extended SELECT MIN( `pk`) AS field1, `col_int_null_key` AS ifield1 FROM (select * from t2) AS alias1 WHERE alias1.`col_date_not_null` IS NULL GROUP BY ifield1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. |
| Note | 1003 | /* select#1 */ select min(`schema_l`.`t2`.`pk`) AS `field1`,`schema_l`.`t2`.`col_int_null_key` AS `ifield1` from `schema_l`.`t2` where 0 group by `ifield1` |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
