Re: [Maria-developers] MDEV-19429: Wrong query result with EXISTS and LIMIT 0

Hi Sanja, Ok to push after the below input is addressed.

commit ab5fa406b4b314705cb87ffd74111a518b549ff4 Author: Oleksandr Byelkin Date: Wed Jul 17 12:31:45 2019 +0200

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

Check EXISTS LIMIT before rewriting.

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index afc42dc08d5..85d91181337 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1432,12 +1432,18 @@ void 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) - */ - thd->change_item_tree(&unit->global_parameters->select_limit, - new Item_int((int32) 1)); + // If limit is not set or it is constant more than 1 + if (!unit->global_parameters->select_limit || + (unit->global_parameters->select_limit->basic_const_item() && + unit->global_parameters->select_limit->val_int() > 1)) + { + /* + We need only 1 row to determine existence (i.e. any EXISTS that is not + an IN always requires LIMIT 1) + */ + thd->change_item_tree(&unit->global_parameters->select_limit, + new Item_int((int32) 1)); Please fix identation ^ + } DBUG_PRINT("info", ("Set limit to 1"));a

Please move the DBUG_PRINT into the if () {...} . Because right now it will print "set limit to 1" even when it didn't set it.

DBUG_VOID_RETURN; }

I also observe that LIMIT clause is not printed into EXPLAIN EXTENDED output: mysql> explain extended select * from t10 where exists (select * from one_k where a >55 order by a limit 100 offset 50); +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | PRIMARY | t10 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | | | 2 | SUBQUERY | one_k | ALL | NULL | NULL | NULL | NULL | 1342 | 100.00 | Using where; Using filesort | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------+ 2 rows in set, 1 warning (6.34 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`t10`.`a` AS `a` from `test`.`t10` where exists(select 1 from `test`.`one_k` where (`test`.`one_k`.`a` > 55) order by `test`.`one_k`.`a`) 1 row in set (0.00 sec) ^^^ Note the lack of LIMIT above. It's missing only for EXISTS subqueries, for other kinds of subqueries it is there. I guess this is outside of scope of this MDEV and should be filed separately. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog