Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19429

Wrong query result with EXISTS and LIMIT 0

    Details

      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 |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              psergey Sergei Petrunia
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: