[MDEV-30594] The incorrect result of the WHERE condition which contains the IN operation between the unsigned double value with the index and the negative value in String type. Created: 2023-02-07  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.5.16, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Zeng Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None


 Description   

In the WHERE condition of the query statement, the IN operation between the unsigned double value with the index and the negative value in String type is incorrect. As a result, the query result is incorrect.

mysql> DROP TABLE IF EXISTS t1;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> CREATE TABLE `t1` (
 
    ->   `c2` double unsigned NOT NULL,
 
    ->   KEY `ic3` (`c2`)
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql> INSERT INTO t1 VALUES (0);
 
Query OK, 1 row affected (0.00 sec)
 
 
 
mysql> SELECT c2,(c2 IN ('-1758183929')) FROM t1 WHERE (c2 IN ('-1758183929'));
 
+----+-------------------------+
 
| c2 | (c2 IN ('-1758183929')) |
 
+----+-------------------------+
 
|  0 |                       0 |
 
+----+-------------------------+

As the (c2 IN ('-1758183929') is 0, the condition of the WHERE statement should be wrong as well. The result should be an empty set.

 Comments   
Comment by Alice Sherepa [ 2023-02-08 ]

Thanks! I repeated on 10.3-10.11 with InnoDB, Myisam/Aria returned correct result.

--source include/have_innodb.inc
 
CREATE TABLE t1 (c2 double unsigned, KEY (c2))engine=innodb;
 
INSERT INTO t1 VALUES (0);
 
SELECT c2,(c2 IN ('-1758183929')) FROM t1 WHERE (c2 IN ('-1758183929'));


MariaDB [test]> SELECT c2,(c2 IN ('-1758183929')) FROM t1 WHERE (c2 IN ('-1758183929'));
 
+------+-------------------------+
 
| c2   | (c2 IN ('-1758183929')) |
 
+------+-------------------------+
 
|    0 |                       0 |
 
+------+-------------------------+
 
1 row in set (0,001 sec)
 
 
 
MariaDB [test]> SELECT c2,(c2 IN ('-1758183929')) FROM t1 ignore index(c2) WHERE (c2 IN ('-1758183929'));
 
Empty set (0,000 sec)
 
 
 
MariaDB [test]> explain extended SELECT c2,(c2 IN ('-1758183929')) FROM t1 WHERE (c2 IN ('-1758183929'));
 
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+
 
|    1 | SIMPLE      | t1    | ref  | c2            | c2   | 9       | const | 1    |   100.00 | Using index |
 
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+
 
1 row in set, 1 warning (0,001 sec)
 
 
 
Note (Code 1003): select `test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c2` = '-1758183929' AS `(c2 IN ('-1758183929'))` from `test`.`t1` where `test`.`t1`.`c2` = '-1758183929'

Generated at Thu Feb 08 10:17:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.