[MDEV-30683] An unexpected fetch by the SELECT statement Created: 2023-02-18  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.3, 10.4, 10.11.2, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: John Jove Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: upstream-not-8.0
Environment:

ubuntu 20.04 LTS


 Description   

I deploy MariaDB with version 10.11.2 in docker and run the following statements.
I doubt the SELECT statement may return an incorrect result.

CREATE TABLE t1 (c1 INT);
 
INSERT INTO t1 VALUES (1);
 
 
 
SELECT c1 FROM t1 WHERE (! (! -1)) NOT IN (1); -- actual: 1, expected: 0
 
SELECT c1 FROM t1 WHERE (! (! -1)) LIKE (1); -- actual: 0, expected: 1
 
 
 
SELECT (! (! -1)) NOT IN (1); -- 0
 
SELECT (! (! -1)) LIKE (1); -- 1



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

Thanks for the report!
I repeated on 10.3-10.11 as described:

MariaDB [test]> SELECT 1 WHERE (! (! -1)) in  (1);
 
Empty set (0,000 sec)
 
 
 
MariaDB [test]> explain extended SELECT 1 WHERE (! (! -1)) in  (1);
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
 
1 row in set, 1 warning (0,000 sec)
 
 
 
Note (Code 1003): select 1 AS `1` where 0
 
 
 
MariaDB [test]> select (! (! -1)) in  (1);
 
+--------------------+
 
| (! (! -1)) in  (1) |
 
+--------------------+
 
|                  1 |
 
+--------------------+
 
1 row in set (0,000 sec)

Comment by Sergei Golubchik [ 2023-02-20 ]

The bug is in negate_expression() function that simplifies NOT(NOT(a)) to a if it happens in WHERE or HAVING

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