[MDEV-21029] Incorrect result for expression with the <=> operator and IS NULL Created: 2019-11-11  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Manuel Rigger Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 19.04

Issue Links:
Relates
relates to MDEV-21034 GREATEST() and LEAST() malfunction fo... Confirmed
relates to MDEV-21065 UNIQUE constraint causes a query with... Closed

 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



 Comments   
Comment by Alice Sherepa [ 2019-11-14 ]

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 |
 
+------+-------------------+-----------------------------+----------------------------+

Comment by Manuel Rigger [ 2019-11-14 ]

Thanks a lot for verifying the bug!

Generated at Thu Feb 08 09:04:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.