Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5306

EXPLAIN SELECT returns inconsistent number of ROWS in main.group_by

Details

    • Bug
    • Status: Done
    • Low
    • Resolution: Fixed
    • 8.0.x
    • 8.0.13-4
    • None

    Description

      Copy of https://bugs.mysql.com/bug.php?id=93703:

      Description:
      EXPLAIN SELECT sometimes returns 4 or 5 as "rows" what is reported as errors for `main.group_by`:

      — /tmp/results/PS/mysql-test/r/group_by.result 2018-12-19 13:10:55.000000000 +0300
      +++ /tmp/results/PS/mysql-test/var/4/log/group_by.reject 2018-12-19 15:29:24.416116648 +0300
      @@ -1752,7 +1752,7 @@
      Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `SUM(b)` from `test`.`t2` group by `test`.`t2`.`a` limit 2
      EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
      id select_type table partitions type possible_keys key key_len ref rows filtered Extra
      -1 SIMPLE t2 NULL ALL a NULL NULL NULL 5 100.00 Using temporary
      +1 SIMPLE t2 NULL ALL a NULL NULL NULL 4 100.00 Using temporary

      How to repeat:
      The minimal MTR test to show the issue:

      CREATE TABLE t1 (a INT, b INT, KEY(a));
      INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);

      CREATE TABLE t2 (a INT, b INT, KEY(a));
      INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);

      EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a;
      EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a;

      EXPLAIN SELECT a FROM t1 GROUP BY a;
      EXPLAIN SELECT a FROM t2 GROUP BY a;

      Attachments

        Activity

          People

            laurynas.biveinis Laurynas Biveinis (Inactive)
            laurynas.biveinis Laurynas Biveinis (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 7 minutes
                7m

                Smart Checklist