Bug #120030 Query Results Differ Due to Primary Key and INTERSECT Operation

Submitted: 11 Mar 14:44 Modified: 11 Mar 20:31
Reporter: b a Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.4.8 OS:Any
Assigned to: CPU Architecture:Any

[11 Mar 14:44] b a
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)
[11 Mar 20:31] Roy Lyseng
Thank you for the bug report.
Verified as described.

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.