Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
-
Ubuntu 19.04
Description
Hi everyone,
On the latest trunk version (0308de9), GREATEST() and LEAST() seem to malfunction for NULL values:
CREATE TABLE t0(c0 INT);
|
INSERT INTO t0 VALUES (1);
|
SELECT * FROM t0 WHERE GREATEST(c0, NULL); -- unexpected: row is fetched
|
I would not expect the row to be fetched, because the WHERE condition should evaluate to NULL for that row:
SELECT GREATEST(c0, NULL) FROM t0; -- NULL
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
The same problem is repeatable with the DOUBLE data type:
CREATE OR REPLACE TABLE t0(c0 DOUBLE); |
INSERT INTO t0 VALUES (1); |
SELECT * FROM t0 WHERE GREATEST(c0, NULL); |
+------+
|
| c0 |
|
+------+
|
| 1 |
|
+------+
|
Thank you for the report!
Reproducible on MariaDB 5.5-10.4 (and Mysql 5.6.40,5.7.26, but works correct in 8.0.16)
MariaDB [test]> SELECT 5 from dual WHERE LEAST(1, NULL);+---+| 5 |+---+| 5 |+---+1 row in set (0.000 sec)MariaDB [test]> SELECT LEAST(1, NULL);+----------------+| LEAST(1, NULL) |+----------------+| NULL |+----------------+1 row in set (0.000 sec)MariaDB [test]> create table t0 (c0 int);Query OK, 0 rows affected (0.023 sec)MariaDB [test]> insert into t0 values (1),(0),(null);Query OK, 3 rows affected (0.000 sec)Records: 3 Duplicates: 0 Warnings: 0MariaDB [test]> SELECT * FROM t0 WHERE GREATEST(c0, NULL);+------+| c0 |+------+| 1 |+------+1 row in set (0.000 sec)