Description
In theory, sql1 and sql2 should return the same result, however,sql1 return -2021 while sql2 return -20221231235899.
SQL1:
SELECT DISTINCT (t0.c0) - (SUBDATE('2023-01-01' ,INTERVAL 1 MINUTE)) AS f1 FROM t0 HAVING f1;
|
|
SQL2:
SELECT f1 FROM (SELECT DISTINCT (t0.c0) - (SUBDATE('2023-01-01' ,INTERVAL 1 MINUTE)) AS f1, ((t0.c0) - (SUBDATE('2023-01-01' ,INTERVAL 1 MINUTE))) IS TRUE AS flag FROM t0 HAVING flag=1) as tmp_t;
|
How to repeat
DROP DATABASE IF EXISTS database1;
|
CREATE DATABASE database1;
|
USE database1;
|
CREATE TABLE IF NOT EXISTS t0(c0 SMALLINT);
|
INSERT INTO t0(c0) VALUES(1);
|
|
|
version
|
|
SELECT version();
|
+----------------+
|
| version() |
|
+----------------+
|
| 11.4.0-MariaDB |
|
+----------------+
|
1 row in set (0.00 sec)
|
SELECT DISTINCT (t0.c0) - (SUBDATE('2023-01-01' ,INTERVAL 1 MINUTE)) AS f1 FROM t0 HAVING f1;
|
|
+-------+
|
| f1 |
|
+-------+
|
| -2021 |
|
+-------+
|
SELECT f1 FROM (SELECT DISTINCT (t0.c0) - (SUBDATE('2023-01-01' ,INTERVAL 1 MINUTE)) AS f1, ((t0.c0) - (SUBDATE('2023-01-01' ,INTERVAL 1 MINUTE))) IS TRUE AS flag FROM t0 HAVING flag=1) as tmp_t;
|
|
+-----------------+
|
| f1 |
|
+-----------------+
|
| -20221231235899 |
|
+-----------------+
|
|