[MDEV-19429] Wrong query result with EXISTS and LIMIT 0 Created: 2019-05-09  Updated: 2019-07-23  Resolved: 2019-07-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.2.26, 5.5.65, 10.1.41, 10.3.17, 10.4.7

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None


 Description   

"LIMIT 0" is a degenerate case.

EXISTS (SELECT ... LIMIT 0) should evaluate to FALSE, but it evaluates to TRUE.

create table t10 (a int);
 
insert into t10 values (1),(2),(3);
 
create table t12 (a int);
 
insert into t12 values (1),(2),(3);

LIMIT 0 query:

select * from t12 order by a limit 0;
 
Empty set (0.00 sec)

Make it a subquery:

select * from t10 where exists (select * from t12 order by a limit 0);
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
|    2 |
 
|    3 |
 
+------+
 
3 rows in set (0.00 sec)


mysql> explain select * from t10 where exists (select * from t12 order by a limit 0);
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
 
|    1 | PRIMARY     | t10   | ALL  | NULL          | NULL | NULL    | NULL | 3    |                |
 
|    2 | SUBQUERY    | t12   | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using filesort |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+



 Comments   
Comment by Sergei Petrunia [ 2019-05-09 ]

The problem is caused by this code in item_subselect.cc which sets LIMIT to 1:

bool Item_exists_subselect::fix_length_and_dec()
 
{
 
  DBUG_ENTER("Item_exists_subselect::fix_length_and_dec");
 
  init_length_and_dec();
 
  /*
 
    We need only 1 row to determine existence (i.e. any EXISTS that is not
 
    an IN always requires LIMIT 1)
 
  */
 
  Item *item= new (thd->mem_root) Item_int(thd, (int32) 1);
 
  if (!item)
 
    DBUG_RETURN(TRUE);
 
  thd->change_item_tree(&unit->global_parameters()->select_limit,
 
                        item);
 
  DBUG_PRINT("info", ("Set limit to 1"));

Comment by Oleksandr Byelkin [ 2019-07-17 ] 

create table t10 (a int);
 
insert into t10 values (1),(2),(3);
 
create table t12 (a int);
 
insert into t12 values (1),(2),(3);
 
select * from t10 where exists (select * from t12 order by a limit 0);
 
explain select * from t10 where exists (select * from t12 order by a limit 0);
 
 
 
prepare stmt1 from "select * from t10 where exists (select * from t12 order by a limit ?)";
 
 
 
set @l=1;
 
execute stmt1 using @l;
 
set @l=2;
 
execute stmt1 using @l;
 
set @l=0;
 
execute stmt1 using @l;
 
 
 
deallocate prepare stmt1;
 
 
 
drop table t10, t12;

Comment by Oleksandr Byelkin [ 2019-07-17 ]

I decided to do not overoptimize the EXISTS but just keep LIMIT correct.

Comment by Oleksandr Byelkin [ 2019-07-17 ]

commit ab5fa406b4b314705cb87ffd74111a518b549ff4 (HEAD > bb-5.5MDEV-19429, origin/bb-5.5-MDEV-19429)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Wed Jul 17 12:31:45 2019 +0200

MDEV-19429: Wrong query result with EXISTS and LIMIT 0

Check EXISTS LIMIT before rewriting.

Comment by Oleksandr Byelkin [ 2019-07-17 ]

githib tree bb-5.5-MDEV-19429

Comment by Sergei Petrunia [ 2019-07-19 ]

Review input provided in email. Ok to push after it is addressed.

Generated at Thu Feb 08 08:51:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.