Description:
Hi, I found a logic bug in MySQL 9.6.0.
When I execute two equivalent queries, the return result of the first query contains null, while the return result of the second query does not.
How to repeat:
-- create table
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 float DEFAULT NULL,
c1 float unsigned DEFAULT NULL COMMENT 'asdf',
c3 longtext ,
UNIQUE KEY c1 (c1)
) CHARSET=utf8mb4;
INSERT INTO t0 VALUES
(NULL,000000000000,'1944254614'),(0,NULL,NULL),(0,NULL,'0.9993972517841738'),
(0.557122,NULL,NULL),(NULL,00000.442771,'1944254614'),(0.442771,001944250000,NULL),
(NULL,NULL,'-659170413'),(NULL,0000000.9133,'1944254614'),(NULL,NULL,'0.9839773668698929'),
(0,NULL,'0.793900678337341'),(NULL,NULL,'-795260709'),(0.602108,00000.602108,'0.9562866850894927'),
(-1334790000,00000.465025,NULL),(-1499050000,NULL,NULL),(NULL,NULL,'0.9562866850894927');
-- query1, result:{null, 0, 1}
SELECT DISTINCTROW (t0.c1) AND (t0.c0) AS ref0 FROM t0;
-- query2, result:{0, 1}
SELECT ref0 FROM (SELECT DISTINCT (t0.c1) AND (t0.c0) AS ref0 FROM t0 AS t0) AS t10
WHERE t10.ref0 IS NULL
UNION ALL
SELECT ref0 FROM (SELECT DISTINCT (t0.c1) AND (t0.c0) AS ref0 FROM t0 AS t0) AS t11
WHERE (
NOT (t11.ref0 IS NULL)
OR (CAST((FALSE LIKE t11.ref0) AS DECIMAL(65, 30)) - CAST(t11.ref0 AS DECIMAL(65, 30))) IS NULL IS NULL
)
Description: Hi, I found a logic bug in MySQL 9.6.0. When I execute two equivalent queries, the return result of the first query contains null, while the return result of the second query does not. How to repeat: -- create table DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 float DEFAULT NULL, c1 float unsigned DEFAULT NULL COMMENT 'asdf', c3 longtext , UNIQUE KEY c1 (c1) ) CHARSET=utf8mb4; INSERT INTO t0 VALUES (NULL,000000000000,'1944254614'),(0,NULL,NULL),(0,NULL,'0.9993972517841738'), (0.557122,NULL,NULL),(NULL,00000.442771,'1944254614'),(0.442771,001944250000,NULL), (NULL,NULL,'-659170413'),(NULL,0000000.9133,'1944254614'),(NULL,NULL,'0.9839773668698929'), (0,NULL,'0.793900678337341'),(NULL,NULL,'-795260709'),(0.602108,00000.602108,'0.9562866850894927'), (-1334790000,00000.465025,NULL),(-1499050000,NULL,NULL),(NULL,NULL,'0.9562866850894927'); -- query1, result:{null, 0, 1} SELECT DISTINCTROW (t0.c1) AND (t0.c0) AS ref0 FROM t0; -- query2, result:{0, 1} SELECT ref0 FROM (SELECT DISTINCT (t0.c1) AND (t0.c0) AS ref0 FROM t0 AS t0) AS t10 WHERE t10.ref0 IS NULL UNION ALL SELECT ref0 FROM (SELECT DISTINCT (t0.c1) AND (t0.c0) AS ref0 FROM t0 AS t0) AS t11 WHERE ( NOT (t11.ref0 IS NULL) OR (CAST((FALSE LIKE t11.ref0) AS DECIMAL(65, 30)) - CAST(t11.ref0 AS DECIMAL(65, 30))) IS NULL IS NULL )