maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Sanja,

Ok to push after the below input is addressed.

> commit ab5fa406b4b314705cb87ffd74111a518b549ff4
> Author: Oleksandr Byelkin <sanja@xxxxxxxxxxx>
> 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