Description:
When executing the same SQL query with identical table data and structure, the query produces different results depending on whether the table has been renamed using the ALTER TABLE ... RENAME TO command. This inconsistency between the two cases is not expected behavior and could indicate a bug in the query execution or optimization process.
How to repeat:
--sql1
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t0(c0 DOUBLE ) ;
CREATE TABLE t1(c0 DOUBLE ) ;
INSERT INTO t0(c0) VALUES('0');
INSERT INTO t0(c0) VALUES("1");
INSERT INTO t0(c0) VALUES(2);
INSERT INTO t1(c0) VALUES(3);
ALTER TABLE t0 RENAME TO t2;
WITH tom10 AS (
SELECT 1 AS c3 FROM ( SELECT 1 AS c0 FROM t1 AS tom0 ) AS tom1
INTERSECT SELECT 0
)
SELECT 1 AS c11 FROM (
( SELECT tom11.c3 AS c9 FROM tom10 AS tom11 RIGHT JOIN t2 AS tom12 ON true ) AS tom15
JOIN ( SELECT 1 AS c10 FROM tom10 AS tom16 ) AS tom19) ;
--return
+-----+
| c11 |
+-----+
| 1 |
| 1 |
| 1 |
+-----+
3 rows in set (0.00 sec)
--sql2
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t2(c0 DOUBLE ) ;
CREATE TABLE t1(c0 DOUBLE ) ;
INSERT INTO t2(c0) VALUES('0');
INSERT INTO t2(c0) VALUES("1");
INSERT INTO t2(c0) VALUES(2);
INSERT INTO t1(c0) VALUES(3);
WITH tom10 AS (
SELECT 1 AS c3 FROM ( SELECT 1 AS c0 FROM t1 AS tom0 ) AS tom1
INTERSECT SELECT 0
)
SELECT 1 AS c11 FROM (
( SELECT tom11.c3 AS c9 FROM tom10 AS tom11 RIGHT JOIN t2 AS tom12 ON true ) AS tom15
JOIN ( SELECT 1 AS c10 FROM tom10 AS tom16 ) AS tom19) ;
--return
Empty set (0.00 sec)
Description: When executing the same SQL query with identical table data and structure, the query produces different results depending on whether the table has been renamed using the ALTER TABLE ... RENAME TO command. This inconsistency between the two cases is not expected behavior and could indicate a bug in the query execution or optimization process. How to repeat: --sql1 DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t0(c0 DOUBLE ) ; CREATE TABLE t1(c0 DOUBLE ) ; INSERT INTO t0(c0) VALUES('0'); INSERT INTO t0(c0) VALUES("1"); INSERT INTO t0(c0) VALUES(2); INSERT INTO t1(c0) VALUES(3); ALTER TABLE t0 RENAME TO t2; WITH tom10 AS ( SELECT 1 AS c3 FROM ( SELECT 1 AS c0 FROM t1 AS tom0 ) AS tom1 INTERSECT SELECT 0 ) SELECT 1 AS c11 FROM ( ( SELECT tom11.c3 AS c9 FROM tom10 AS tom11 RIGHT JOIN t2 AS tom12 ON true ) AS tom15 JOIN ( SELECT 1 AS c10 FROM tom10 AS tom16 ) AS tom19) ; --return +-----+ | c11 | +-----+ | 1 | | 1 | | 1 | +-----+ 3 rows in set (0.00 sec) --sql2 DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t2(c0 DOUBLE ) ; CREATE TABLE t1(c0 DOUBLE ) ; INSERT INTO t2(c0) VALUES('0'); INSERT INTO t2(c0) VALUES("1"); INSERT INTO t2(c0) VALUES(2); INSERT INTO t1(c0) VALUES(3); WITH tom10 AS ( SELECT 1 AS c3 FROM ( SELECT 1 AS c0 FROM t1 AS tom0 ) AS tom1 INTERSECT SELECT 0 ) SELECT 1 AS c11 FROM ( ( SELECT tom11.c3 AS c9 FROM tom10 AS tom11 RIGHT JOIN t2 AS tom12 ON true ) AS tom15 JOIN ( SELECT 1 AS c10 FROM tom10 AS tom16 ) AS tom19) ; --return Empty set (0.00 sec)