| Bug #88252 | Incorrect result when using IS NULL expression | ||
|---|---|---|---|
| Submitted: | 27 Oct 2017 14:38 | Modified: | 27 Oct 2017 17:47 |
| Reporter: | Calvin Wong | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[27 Oct 2017 17:47]
MySQL Verification Team
Thank you for the bug report.
mysql 5.7 > SELECT
-> *,
-> t.C1 IS NULL,
-> t.C1 IS NOT NULL
-> FROM person p
-> LEFT JOIN (SELECT
-> StudentId,
-> Age,
-> Code,
-> 1 AS C1
-> FROM student) s
-> ON p.Id = s.StudentId
-> LEFT JOIN (SELECT
-> TeacherId,
-> Age,
-> Number,
-> 1 AS C1
-> FROM teacher) t
-> ON p.Id = t.TeacherId;
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
| Id | Name | StudentId | Age | Code | C1 | TeacherId | Age | Number | C1 | t.C1 IS NULL | t.C1 IS NOT NULL |
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
| 3 | Teacher1 | NULL | NULL | NULL | NULL | 3 | 3 | T3 | 1 | 0 | 1 |
| 4 | Teacher2 | NULL | NULL | NULL | NULL | 4 | 4 | T4 | 1 | 0 | 1 |
| 1 | Student1 | 1 | 1 | S1 | 1 | NULL | NULL | NULL | NULL | 1 | 0 |
| 2 | Student2 | 2 | 2 | S2 | 1 | NULL | NULL | NULL | NULL | 1 | 0 |
| 5 | Empty1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 0 |
| 6 | Empty2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 0 |
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
6 rows in set (0.02 sec)
mysql 5.7 > show variables like "%version%";
+-------------------------+---------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------+
| innodb_version | 5.7.21 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.21 |
| version_comment | Source distribution 2017-OCT-18 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------+
8 rows in set (0.01 sec)
mysql 5.7 >
mysql 8.0 > SELECT
-> *,
-> t.C1 IS NULL,
-> t.C1 IS NOT NULL
-> FROM person p
-> LEFT JOIN (SELECT
-> StudentId,
-> Age,
-> Code,
-> 1 AS C1
-> FROM student) s
-> ON p.Id = s.StudentId
-> LEFT JOIN (SELECT
-> TeacherId,
-> Age,
-> Number,
-> 1 AS C1
-> FROM teacher) t
-> ON p.Id = t.TeacherId;
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
| Id | Name | StudentId | Age | Code | C1 | TeacherId | Age | Number | C1 | t.C1 IS NULL | t.C1 IS NOT NULL |
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
| 1 | Student1 | 1 | 1 | S1 | 1 | NULL | NULL | NULL | NULL | 1 | 0 |
| 2 | Student2 | 2 | 2 | S2 | 1 | NULL | NULL | NULL | NULL | 1 | 0 |
| 3 | Teacher1 | NULL | NULL | NULL | NULL | 3 | 3 | T3 | 1 | 0 | 1 |
| 4 | Teacher2 | NULL | NULL | NULL | NULL | 4 | 4 | T4 | 1 | 0 | 1 |
| 5 | Empty1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 0 |
| 6 | Empty2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 0 |
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
6 rows in set (0.00 sec)
mysql 8.0 > show variables like "%version%";
+-------------------------+---------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------+
| innodb_version | 8.0.4 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 8.0.4-rc-log |
| version_comment | Source distribution 2017-OCT-18 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------+
8 rows in set (0.02 sec)
mysql 8.0 >
[27 Oct 2017 18:43]
MySQL Verification Team
mysql> SELECT
-> *,
-> t.C1 IS NULL,
-> t.C1 IS NOT NULL
-> FROM person p
-> LEFT JOIN (SELECT
-> StudentId,
-> Age,
-> Code,
-> 1 AS C1
-> FROM student) s
-> ON p.Id = s.StudentId
-> LEFT JOIN (SELECT
-> TeacherId,
-> Age,
-> Number,
-> 1 AS C1
-> FROM teacher) t
-> ON p.Id = t.TeacherId;
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
| Id | Name | StudentId | Age | Code | C1 | TeacherId | Age | Number | C1 | t.C1 IS NULL | t.C1 IS NOT NULL |
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
| 1 | Student1 | 1 | 1 | S1 | 1 | NULL | NULL | NULL | NULL | 0 | 0 |
| 2 | Student2 | 2 | 2 | S2 | 1 | NULL | NULL | NULL | NULL | 0 | 0 |
| 3 | Teacher1 | NULL | NULL | NULL | NULL | 3 | 3 | T3 | 1 | 0 | 1 |
| 4 | Teacher2 | NULL | NULL | NULL | NULL | 4 | 4 | T4 | 1 | 0 | 1 |
| 5 | Empty1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 |
| 6 | Empty2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 |
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
6 rows in set (0.02 sec)
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.20 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.20-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)
[30 Oct 2017 10:41]
Øystein Grøvlen
Posted by developer:
Workaround:
mysql> SET optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT
-> *,
-> t.C1 IS NULL,
-> t.C1 IS NOT NULL
-> FROM person p
-> LEFT JOIN (SELECT
-> StudentId,
-> Age,
-> Code,
-> 1 AS C1
-> FROM student) s
-> ON p.Id = s.StudentId
-> LEFT JOIN (SELECT
-> TeacherId,
-> Age,
-> Number,
-> 1 AS C1
-> FROM teacher) t
-> ON p.Id = t.TeacherId;
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
| Id | Name | StudentId | Age | Code | C1 | TeacherId | Age | Number | C1 | t.C1 IS NULL | t.C1 IS NOT NULL |
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
| 1 | Student1 | 1 | 1 | S1 | 1 | NULL | NULL | NULL | NULL | 1 | 0 |
| 2 | Student2 | 2 | 2 | S2 | 1 | NULL | NULL | NULL | NULL | 1 | 0 |
| 3 | Teacher1 | NULL | NULL | NULL | NULL | 3 | 3 | T3 | 1 | 0 | 1 |
| 4 | Teacher2 | NULL | NULL | NULL | NULL | 4 | 4 | T4 | 1 | 0 | 1 |
| 5 | Empty1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 0 |
| 6 | Empty2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 0 |
+----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+
6 rows in set (0,00 sec)
Description: This is the result from executing the query from the repro step. +----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+ | Id | Name | StudentId | Age | Code | C1 | TeacherId | Age | Number | C1 | t.C1 IS NULL | t.C1 IS NOT NULL | +----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+ | 1 | Student1 | 1 | 1 | S1 | 1 | NULL | NULL | NULL | NULL | 0 | 0 | | 2 | Student2 | 2 | 2 | S2 | 1 | NULL | NULL | NULL | NULL | 0 | 0 | | 3 | Teacher1 | NULL | NULL | NULL | NULL | 3 | 3 | T3 | 1 | 0 | 1 | | 4 | Teacher2 | NULL | NULL | NULL | NULL | 4 | 4 | T4 | 1 | 0 | 1 | | 5 | Empty1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | | 6 | Empty2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | +----+----------+-----------+------+------+------+-----------+------+--------+------+--------------+------------------+ It appears that the result in t.C1 IS NULL column is incorrect How to repeat: CREATE DATABASE database1 CHARACTER SET utf8 COLLATE utf8_general_ci; USE database1; CREATE TABLE person ( Id int(11) NOT NULL, Name longtext DEFAULT NULL, PRIMARY KEY (Id) ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT = DYNAMIC; CREATE TABLE student ( StudentId int(11) NOT NULL, Age int(11) DEFAULT NULL, Code longtext DEFAULT NULL, PRIMARY KEY (StudentId) ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT = DYNAMIC; CREATE TABLE teacher ( TeacherId int(11) NOT NULL, Age int(11) DEFAULT NULL, Number longtext DEFAULT NULL, PRIMARY KEY (TeacherId) ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT = DYNAMIC; INSERT INTO person(Id,Name) VALUES (1,'Student1'), (2,'Student2'), (3,'Teacher1'), (4,'Teacher2'), (5,'Empty1'), (6,'Empty2'); INSERT INTO student VALUES (1,1,'S1'),(2,2,'S2'); INSERT INTO teacher VALUES (3,3,'T3'),(4,4,'T4'); SELECT *, t.C1 IS NULL, t.C1 IS NOT NULL FROM person p LEFT JOIN (SELECT StudentId, Age, Code, 1 AS C1 FROM student) s ON p.Id = s.StudentId LEFT JOIN (SELECT TeacherId, Age, Number, 1 AS C1 FROM teacher) t ON p.Id = t.TeacherId;