Details
Description
Description
Dear devps, Our fuzzer may found a bug.
Consider the following statements, qeury1 and query2 should reutrn the same reuslt. However, qeury1 return nothing while query2 return 95607293. Additionally, The reuslt in MySQL is correct
-- query1
|
mysql> SELECT f1 FROM (SELECT (t0.c1 - (t1.c0 IS TRUE)) AS f1 FROM t1 RIGHT OUTER JOIN t0 ON NULL) AS t WHERE f1 AND 1; |
Empty set (0.00 sec) |
 |
--query2
|
mysql> SELECT f1 FROM (SELECT (t0.c1 - (t1.c0 IS TRUE)) AS f1, ((t0.c1 - (t1.c0 IS TRUE)) AND 1) IS TRUE AS flag FROM t1 RIGHT OUTER JOIN t0 ON NULL) AS t WHERE flag=1; |
+----------+ |
| f1 |
|
+----------+ |
| 95607293 |
|
+----------+ |
1 row in set (0.00 sec) |
|
How to repeat
The bug can be reproduced from from 10.4.21 to 11.4.1
CREATE TABLE t0(c0 CHAR(100), c1 BIGINT, c2 REAL UNIQUE, PRIMARY KEY(c2, c1, c0)); |
CREATE TABLE t1 LIKE t0; |
INSERT INTO t0 VALUES ('1', 95607293, -1); |
 |
SELECT f1 FROM (SELECT (t0.c1 - (t1.c0 IS TRUE)) AS f1 FROM t1 RIGHT OUTER JOIN t0 ON NULL) AS t WHERE f1 AND 1; |
SELECT f1 FROM (SELECT (t0.c1 - (t1.c0 IS TRUE)) AS f1, ((t0.c1 - (t1.c0 IS TRUE)) AND 1) IS TRUE AS flag FROM t1 RIGHT OUTER JOIN t0 ON NULL) AS t WHERE flag=1; |
Attachments
Issue Links
- is duplicated by
-
MDEV-37653 IS TRUE incorrectly converts outer join to inner
-
- Closed
-