Description:
The same SQL query returns different results depending on whether the column c1 has a primary key. This behavior is observed when using the INTERSECT operator in combination with the DAYOFYEAR() function, where the result differs despite the queries being logically identical. This inconsistency likely indicates a bug related to how INTERSECT handles primary keys.
How to repeat:
---sql1
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t1(c0 DECIMAL , c1 MEDIUMINT PRIMARY KEY) ;
INSERT IGNORE INTO t1(c1, c0) VALUES(1, 2);
SELECT 1 AS c19
FROM (
SELECT tom14.c1 FROM t1 AS tom14
INTERSECT
SELECT DAYOFYEAR( 'A' ) FROM t1
) AS tom16
;
--return
+-----+
| c19 |
+-----+
| 1 |
+-----+
1 row in set, 1 warning (0.00 sec)
---sql2
SELECT tom14.c1 FROM t1 AS tom14
INTERSECT
SELECT DAYOFYEAR( 'A' ) FROM t1;
--return
Empty set, 1 warning (0.00 sec)
---sql3
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t1(c0 DECIMAL , c1 MEDIUMINT ) ;
INSERT IGNORE INTO t1(c1, c0) VALUES(1, 2);
SELECT 1 AS c19
FROM (
SELECT tom14.c1 FROM t1 AS tom14
INTERSECT
SELECT DAYOFYEAR( 'A' ) FROM t1
) AS tom16
;
--return
Empty set, 1 warning (0.00 sec)
Description: The same SQL query returns different results depending on whether the column c1 has a primary key. This behavior is observed when using the INTERSECT operator in combination with the DAYOFYEAR() function, where the result differs despite the queries being logically identical. This inconsistency likely indicates a bug related to how INTERSECT handles primary keys. How to repeat: ---sql1 DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t1(c0 DECIMAL , c1 MEDIUMINT PRIMARY KEY) ; INSERT IGNORE INTO t1(c1, c0) VALUES(1, 2); SELECT 1 AS c19 FROM ( SELECT tom14.c1 FROM t1 AS tom14 INTERSECT SELECT DAYOFYEAR( 'A' ) FROM t1 ) AS tom16 ; --return +-----+ | c19 | +-----+ | 1 | +-----+ 1 row in set, 1 warning (0.00 sec) ---sql2 SELECT tom14.c1 FROM t1 AS tom14 INTERSECT SELECT DAYOFYEAR( 'A' ) FROM t1; --return Empty set, 1 warning (0.00 sec) ---sql3 DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t1(c0 DECIMAL , c1 MEDIUMINT ) ; INSERT IGNORE INTO t1(c1, c0) VALUES(1, 2); SELECT 1 AS c19 FROM ( SELECT tom14.c1 FROM t1 AS tom14 INTERSECT SELECT DAYOFYEAR( 'A' ) FROM t1 ) AS tom16 ; --return Empty set, 1 warning (0.00 sec)