Bug #120027 Query Results Differ Due to Presence of Primary Key - Issue with MIN and DATE_ADD Functions
Submitted: 11 Mar 14:34 Modified: 11 Mar 19:47
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:34] b a 
Description:
The same SQL query returns different results when executed on two tables with similar data but with one key difference: the second table has a primary key column (c0 is a primary key in the first case). The presence of the primary key seems to affect the query result, which should not happen. This could be a bug related to the interaction between the MIN function and the DATE_ADD function.

How to repeat:
---sql1
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t2(c0 DOUBLE  PRIMARY KEY) ;
insert  INTO t2(c0) VALUES(6);

 SELECT min( 1 ) AS c16 ,  t2.c0   FROM t2    GROUP BY t2.c0 
    HAVING NULLIF( 1 , DATE_ADD( c16 , INTERVAL 1 HOUR ) )  ;
--return
+------+----+
| c16  | c0 |
+------+----+
| NULL |  6 |
+------+----+
1 row in set, 1 warning (0.00 sec)

---sql2
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t2(c0 DOUBLE  ) ;
insert  INTO t2(c0) VALUES(6);

 SELECT min( 1 ) AS c16 ,  t2.c0   FROM t2    GROUP BY t2.c0 
    HAVING NULLIF( 1 , DATE_ADD( c16 , INTERVAL 1 HOUR ) )  ;

--return
+------+------+
| c16  | c0   |
+------+------+
|    1 |    6 |
+------+------+
1 row in set, 1 warning (0.00 sec)
[11 Mar 14:36] b a 
the version is 8.4.8