Details

    Description

      CREATE TABLE t1 (pk INT PRIMARY KEY, i INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,1),(2,2),(3,10);
       
      SELECT
         i,
        ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt
      FROM t1 AS outer_t1;
       
      SELECT
        ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt,
        GROUP_CONCAT(i)
      FROM t1 AS outer_t1
      GROUP BY cnt;
       
      DROP TABLE t1;
      

      This test case is very similar to MDEV-12561, with the only difference in 3rd row inserted into the table. Results are different though, the first query also seems to work wrongly:

      MariaDB [test]> SELECT
          ->    i,
          ->   ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt
          -> FROM t1 AS outer_t1;
      +------+------+
      | i    | cnt  |
      +------+------+
      |    1 |    3 |
      |    2 |    3 |
      |   10 |    0 |
      +------+------+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> SELECT
          ->   ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt,
          ->   GROUP_CONCAT(i)
          -> FROM t1 AS outer_t1
          -> GROUP BY cnt;
      +------+-----------------+
      | cnt  | GROUP_CONCAT(i) |
      +------+-----------------+
      |    0 | 1,2,10          |
      +------+-----------------+
      1 row in set (0.00 sec)
      

      The rest is different on different versions. It should be re-checked after MDEV-12561 is fixed.

      Attachments

        Activity

          I can't remember why I said that the first result is incorrect, it seems okay.
          The second result was fixed by the patch for MDEV-10053

          commit 97fb1f26797828427ad850b0420aaafc74205e71
          Author: Igor Babaev
          Date:   Fri Apr 21 14:34:24 2017 -0700
           
              Fixed bug mdev-10053.
              
              The implementation of the walk method for the class Item_in_subselect
              was missing. As a result the method never traversed the left operand
              of any IN subquery predicate.
              
              Item_exists_subselect::exists2in_processor() that performs the
              Exist-To-In transformation calls the walk method to collect info
              on outer references. As the walk method did not traverse the
              left operands of the IN subqueries the outer references there
              were not taken into account and some subqueries that were actually
              correlated were marked as uncorrelated. It could lead to an
              attempt of the materialization of such a subquery.
          

          elenst Elena Stepanova added a comment - I can't remember why I said that the first result is incorrect, it seems okay. The second result was fixed by the patch for MDEV-10053 commit 97fb1f26797828427ad850b0420aaafc74205e71 Author: Igor Babaev Date: Fri Apr 21 14:34:24 2017 -0700   Fixed bug mdev-10053. The implementation of the walk method for the class Item_in_subselect was missing. As a result the method never traversed the left operand of any IN subquery predicate. Item_exists_subselect::exists2in_processor() that performs the Exist-To-In transformation calls the walk method to collect info on outer references. As the walk method did not traverse the left operands of the IN subqueries the outer references there were not taken into account and some subqueries that were actually correlated were marked as uncorrelated. It could lead to an attempt of the materialization of such a subquery.

          People

            Unassigned Unassigned
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.