Bug #120030 Query Results Differ Due to Primary Key and INTERSECT Operation
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)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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)