Description:
The same SQL queries return different results based on whether the column c0 has a primary key constraint. The issue arises when using the MIN or MAX functions in conjunction with time-related functions like UNIX_TIMESTAMP or MONTH. This discrepancy is likely a bug, as the query logic and data are the same, and the only difference between the two cases is the presence of the primary key.
How to repeat:
***test case 1 ***
---sql1
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t2(c0 DOUBLE PRIMARY KEY);
insert INTO t2(c0) VALUES(1);
SELECT UNIX_TIMESTAMP( MIN( 'A' ) ) AS c4
FROM t2 AS tom3
GROUP BY tom3.c0 ;
--return
+------+
| c4 |
+------+
| NULL |
+------+
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(1);
SELECT UNIX_TIMESTAMP( MIN( 'A' ) ) AS c4
FROM t2 AS tom3
GROUP BY tom3.c0 ;
--return
+----------+
| c4 |
+----------+
| 0.000000 |
+----------+
1 row in set, 1 warning (0.00 sec)
***test case 2 ***
---sql1
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t2(c0 DOUBLE PRIMARY KEY);
insert INTO t2(c0) VALUES(1);
SELECT MAX( '2025' ) AS c21 FROM t2 AS tom32
GROUP BY tom32.c0 HAVING IFNULL( MONTH( c21 ) , 3.14159 );
--return
+------+
| c21 |
+------+
| NULL |
+------+
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(1);
--return
SELECT MAX( '2025' ) AS c21 FROM t2 AS tom32
GROUP BY tom32.c0 HAVING IFNULL( MONTH( c21 ) , 3.14159 );
+------+
| c21 |
+------+
| 2025 |
+------+
1 row in set, 1 warning (0.00 sec)
