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

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia created issue -
          psergei Sergei Petrunia added a comment -

          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"));

          psergei Sergei Petrunia added a comment - 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" ));
          psergei Sergei Petrunia made changes -
          Field Original Value New Value
          Assignee Oleksandr Byelkin [ sanja ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.4 [ 22408 ]
          sanja Oleksandr Byelkin made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          sanja Oleksandr Byelkin added a comment - 

          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;

          sanja Oleksandr Byelkin added a comment - 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;
          sanja Oleksandr Byelkin added a comment -

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

          sanja Oleksandr Byelkin added a comment - I decided to do not overoptimize the EXISTS but just keep LIMIT correct.
          sanja Oleksandr Byelkin added a comment -

          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.

          sanja Oleksandr Byelkin added a comment - commit ab5fa406b4b314705cb87ffd74111a518b549ff4 (HEAD > bb-5.5 MDEV-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.
          sanja Oleksandr Byelkin made changes -
          Affects Version/s 5.5 [ 15800 ]
          Affects Version/s 10.1 [ 16100 ]
          Affects Version/s 10.2 [ 14601 ]
          Affects Version/s 10.3 [ 22126 ]
          sanja Oleksandr Byelkin made changes -
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          sanja Oleksandr Byelkin added a comment -

          githib tree bb-5.5-MDEV-19429

          sanja Oleksandr Byelkin added a comment - githib tree bb-5.5- MDEV-19429
          psergei Sergei Petrunia made changes -
          Assignee Oleksandr Byelkin [ sanja ] Sergei Petrunia [ psergey ]
          psergei Sergei Petrunia made changes -
          Status In Progress [ 3 ] In Review [ 10002 ]
          psergei Sergei Petrunia added a comment -

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

          psergei Sergei Petrunia added a comment - Review input provided in email. Ok to push after it is addressed.
          psergei Sergei Petrunia made changes -
          Assignee Sergei Petrunia [ psergey ] Oleksandr Byelkin [ sanja ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          sanja Oleksandr Byelkin made changes -
          Fix Version/s 5.5.65 [ 23402 ]
          Fix Version/s 10.1.41 [ 23406 ]
          Fix Version/s 10.2.26 [ 23409 ]
          Fix Version/s 10.3.17 [ 23411 ]
          Fix Version/s 10.4.7 [ 23720 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.3 [ 22126 ]
          Fix Version/s 10.4 [ 22408 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 96643 ] MariaDB v4 [ 156164 ]

          People

            sanja Oleksandr Byelkin
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.