Details
Description
Hi everyone,
On the latest trunk version (0308de9), I could observe a bug, as demonstrated by the following test case:
CREATE TABLE t0(c0 INT);
|
INSERT INTO t0 VALUES (1);
|
SELECT (c0 > (NULL <=> 0)) IS NULL FROM t0; -- expected: 0, actual: 1
|
The expression seems to work correctly when it is used in a WHERE condition:
SELECT * FROM t0 WHERE (c0 > (NULL <=> 0)) IS NULL; -- no row is fetched
|
Attachments
Issue Links
- relates to
-
MDEV-21034 GREATEST() and LEAST() malfunction for NULL
- Closed
-
MDEV-21065 UNIQUE constraint causes a query with string comparison to omit a row in the result set
- Closed
-
MDEV-34004 Unexpected result when using equal operator <=>
- Open
Thanks! Reproducible on MariaDB 5.5-10.4 (also the same way on Mysql 5.6-8.0.16)
MariaDB [test]> create table t0 (c0 int);
Query OK, 0 rows affected (0.025 sec)
MariaDB [test]> insert into t0 values (1),(0),(null);
Query OK, 3 rows affected (0.001 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [test]> select (c0 > (null <=> 0)) is null from t0;
+-----------------------------+
| (c0 > (null <=> 0)) is null |
+-----------------------------+
| 1 |
| 1 |
| 1 |
+-----------------------------+
3 rows in set (0.000 sec)
MariaDB [test]> select c0, c0 > (null <=> 0), (c0 > (null <=> 0)) is null, (1 > (null <=> 0)) is null from t0;
+------+-------------------+-----------------------------+----------------------------+
| c0 | c0 > (null <=> 0) | (c0 > (null <=> 0)) is null | (1 > (null <=> 0)) is null |
+------+-------------------+-----------------------------+----------------------------+
| 1 | 1 | 1 | 0 |
| 0 | 0 | 1 | 0 |
| NULL | NULL | 1 | 0 |
+------+-------------------+-----------------------------+----------------------------+