[MDEV-30593] The incorrect result of the WHERE condition that contains a negative integer boundary value. Created: 2023-02-07  Updated: 2023-11-28

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

Type: Bug Priority: Major
Reporter: Zeng Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: not-10.3, not-10.4


 Description   

The processing result of the WHERE condition that contains a negative integer boundary value is incorrect. As a result, the query result is incorrect.

For example,

mysql> DROP TABLE IF EXISTS t0;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> CREATE TABLE `t0` (
 
    ->   `c0` tinyint NOT NULL,
 
    ->   KEY `ic2` (`c0`)
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql> insert into t0 values (127),(-128);
 
Query OK, 2 rows affected (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
mysql> SELECT c0 FROM t0 WHERE (c0 NOT IN (126, -129));
 
+-----+
 
| c0  |
 
+-----+
 
| 127 |
 
+-----+
 
1 row in set (0.00 sec)

The query result miss the -128.

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

Thanks! I repeated as described on 10.5-10.11, with Myisam/InnoDb. On 10.3,10.4 the result is correct

--source include/have_innodb.inc
 
 
 
CREATE TABLE t0 ( c0 tinyint NOT NULL,  KEY  (c0) );
 
insert into t0 values (127),(-128);
 
SELECT c0 FROM t0 WHERE (c0 NOT IN (126, -129));


MariaDB [test]> SELECT c0 FROM t0 WHERE (c0 NOT IN (126, -129));
 
+-----+
 
| c0  |
 
+-----+
 
| 127 |
 
+-----+
 
1 row in set (0,000 sec)
 
 
 
MariaDB [test]> explain extended SELECT c0 FROM t0 WHERE (c0 NOT IN (126, -129));
 
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
 
|    1 | SIMPLE      | t0    | range | c0            | c0   | 1       | NULL | 2    |   100.00 | Using where; Using index |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
 
1 row in set, 1 warning (0,001 sec)
 
 
 
Note (Code 1003): select `test`.`t0`.`c0` AS `c0` from `test`.`t0` where `test`.`t0`.`c0` not in (126,-129)

on 10.3-10.4:

SELECT c0 FROM t0 WHERE (c0 NOT IN (126, -129));
 
c0
 
-128
 
127
 
explain extended SELECT c0 FROM t0 WHERE (c0 NOT IN (126, -129));
 
id  select_type table type  possible_keys key key_len ref rows  filtered  Extra
 
1 SIMPLE  t0  index c0  c0  1 NULL  2 100.00  Using where; Using index
 
Warnings:
 
Note  1003  select `test`.`t0`.`c0` AS `c0` from `test`.`t0` where `test`.`t0`.`c0` not in (126,-129)

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