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

            People

            • Assignee:
              Unassigned
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: