[MDEV-31581] rocksdb.group_min_max fails in 11.0 Created: 2023-06-29  Updated: 2023-10-24

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - RocksDB
Affects Version/s: 11.0
Fix Version/s: 11.0

Type: Bug Priority: Critical
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDBF-596 Add a builder to test RocksDB Closed

 Description   

rocksdb.group_min_max fails in 11.0.
This affects the ES release - it fails there as well.

--- storage/rocksdb/mysql-test/rocksdb/r/group_min_max.result    2023-06-29 15:05:07.852435948 +0300
 
+++ storage/rocksdb/mysql-test/rocksdb/r/group_min_max.reject    2023-06-29 18:59:25.174746843 +0300
 
@@ -144,10 +144,10 @@
 
 1      SIMPLE  t1      range   NULL    idx_t1_1        130     NULL    5       Using index for group-by
 
 explain select a1, max(a2) from t1 group by a1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   NULL    idx_t1_1        65      NULL    5       Using index for group-by
 
+1      SIMPLE  t1      range   NULL    idx_t1_2        65      NULL    5       Using index for group-by
 
 explain select a1, min(a2), max(a2) from t1 group by a1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   NULL    idx_t1_1        130     NULL    5       Using index for group-by
 
+1      SIMPLE  t1      range   NULL    idx_t1_2        130     NULL    5       Using index for group-by
 
 explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
 1      SIMPLE  t1      range   NULL    idx_t1_1        147     NULL    17      Using index for group-by
 
@@ -159,7 +159,7 @@
 
 1      SIMPLE  t2      range   NULL    idx_t2_1        #       NULL    #       Using index for group-by
 
 explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   NULL    idx_t1_1        130     NULL    5       Using index for group-by
 
+1      SIMPLE  t1      range   NULL    idx_t1_2        130     NULL    5       Using index for group-by
 
 explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
 1      SIMPLE  t1      range   NULL    idx_t1_1        147     NULL    17      Using index for group-by
 
@@ -1811,10 +1811,10 @@
 
 1      SIMPLE  t1      index   NULL    idx_t1_2        147     NULL    128     Using where; Using index
 
 explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b';
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        130     NULL    5       Using where; Using index for group-by
 
+1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        130     NULL    2       Using where; Using index for group-by
 
 explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e';
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        130     NULL    5       Using where; Using index for group-by
 
+1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        130     NULL    2       Using where; Using index for group-by
 
 explain select distinct a1,a2,b from t2;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
 1      SIMPLE  t2      range   NULL    idx_t2_2        146     NULL    #       Using index for group-by
 
@@ -1834,10 +1834,10 @@
 
 1      SIMPLE  t2      index   NULL    idx_t2_2        146     NULL    164     Using where; Using index
 
 explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b';
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   idx_t2_0,idx_t2_1,idx_t2_2      idx_t2_2        129     NULL    6       Using where; Using index for group-by
 
+1      SIMPLE  t2      range   idx_t2_0,idx_t2_1,idx_t2_2      idx_t2_1        129     NULL    2       Using where; Using index for group-by
 
 explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e';
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   idx_t2_0,idx_t2_1,idx_t2_2      idx_t2_2        129     NULL    6       Using where; Using index for group-by
 
+1      SIMPLE  t2      range   idx_t2_0,idx_t2_1,idx_t2_2      idx_t2_1        129     NULL    2       Using where; Using index for group-by
 
 select distinct a1,a2,b from t1;
 
 a1     a2      b
 
 a      a       a
 
@@ -1981,10 +1981,10 @@
 
 1      SIMPLE  t1      range   NULL    idx_t1_2        147     NULL    17      Using where; Using index for group-by; Using temporary; Using filesort
 
 explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        130     NULL    5       Using where; Using index for group-by
 
+1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        130     NULL    2       Using where; Using index for group-by
 
 explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        130     NULL    5       Using where; Using index for group-by
 
+1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        130     NULL    2       Using where; Using index for group-by
 
 explain select distinct a1,a2,b from t2;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
 1      SIMPLE  t2      range   NULL    idx_t2_2        146     NULL    #       Using index for group-by
 
@@ -2002,10 +2002,10 @@
 
 1      SIMPLE  t2      range   NULL    idx_t2_2        146     NULL    #       Using where; Using index for group-by; Using temporary; Using filesort
 
 explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   idx_t2_0,idx_t2_1,idx_t2_2      idx_t2_2        129     NULL    #       Using where; Using index for group-by
 
+1      SIMPLE  t2      range   idx_t2_0,idx_t2_1,idx_t2_2      idx_t2_1        129     NULL    #       Using where; Using index for group-by
 
 explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   idx_t2_0,idx_t2_1,idx_t2_2      idx_t2_2        129     NULL    #       Using where; Using index for group-by
 
+1      SIMPLE  t2      range   idx_t2_0,idx_t2_1,idx_t2_2      idx_t2_1        129     NULL    #       Using where; Using index for group-by
 
 select distinct a1,a2,b from t1;
 
 a1     a2      b
 
 a      a       a
 
@@ -2203,26 +2203,26 @@
 
 9798   a       b
 
 explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      index   NULL    idx_t1_2        147     NULL    128
 
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    128     Using temporary; Using filesort
 
 explain select a1,a2,b,d from t1 group by a1,a2,b;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      index   NULL    idx_t1_2        147     NULL    128
 
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    128     Using temporary; Using filesort
 
 explain extended select a1,a2,min(b),max(b) from t1
 
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        130     NULL    128     99.22   Using where; Using index
 
+1      SIMPLE  t1      index   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        163     NULL    128     99.22   Using where; Using index; Using temporary; Using filesort
 
 Warnings:
 
 Note   1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`
 
 explain extended select a1,a2,b,min(c),max(c) from t1
 
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        130     NULL    128     75.00   Using where
 
+1      SIMPLE  t1      ALL     idx_t1_0,idx_t1_1,idx_t1_2      NULL    NULL    NULL    128     75.00   Using where; Using temporary; Using filesort
 
 Warnings:
 
 Note   1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
 
 explain extended select a1,a2,b,c from t1
 
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        130     NULL    128     75.00   Using where
 
+1      SIMPLE  t1      ALL     idx_t1_0,idx_t1_1,idx_t1_2      NULL    NULL    NULL    128     75.00   Using where; Using temporary; Using filesort
 
 Warnings:
 
 Note   1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c`
 
 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
 
@@ -2239,7 +2239,7 @@
 
 1      SIMPLE  t2      index   NULL    idx_t2_1        163     NULL    164     Using where; Using index
 
 explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        130     NULL    128     99.22   Using where; Using index
 
+1      SIMPLE  t1      index   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        163     NULL    128     99.22   Using where; Using index; Using temporary; Using filesort
 
 Warnings:
 
 Note   1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
 
 explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
 
@@ -2257,18 +2257,18 @@
 
 1      SIMPLE  t2      index   NULL    idx_t2_1        163     NULL    164     Using where; Using index
 
 explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      index   NULL    idx_t1_1        163     NULL    128
 
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    128     Using temporary; Using filesort
 
 explain select a1,a2,count(a2) from t1 group by a1,a2,b;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
 1      SIMPLE  t1      index   NULL    idx_t1_2        147     NULL    128     Using index
 
 explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        65      NULL    128     100.00  Using where; Using index
 
+1      SIMPLE  t1      index   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        147     NULL    128     100.00  Using where; Using index; Using temporary; Using filesort
 
 Warnings:
 
 Note   1003    select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
 
 explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 
-1      SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        65      NULL    128     100.00  Using where; Using index
 
+1      SIMPLE  t1      index   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        147     NULL    128     100.00  Using where; Using index; Using temporary; Using filesort
 
 Warnings:
 
 Note   1003    select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
 
 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1;
 
@@ -2288,7 +2288,7 @@
 
 d
 
 explain select a1 from t1 where a2 = 'b' group by a1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   NULL    idx_t1_2        130     NULL    5       Using where; Using index for group-by
 
+1      SIMPLE  t1      range   NULL    idx_t1_1        130     NULL    5       Using where; Using index for group-by
 
 select a1 from t1 where a2 = 'b' group by a1;
 
 a1
 
 a
 
@@ -2297,7 +2297,7 @@
 
 d
 
 explain select distinct a1 from t1 where a2 = 'b';
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   NULL    idx_t1_2        130     NULL    5       Using where; Using index for group-by
 
+1      SIMPLE  t1      range   NULL    idx_t1_1        130     NULL    5       Using where; Using index for group-by
 
 select distinct a1 from t1 where a2 = 'b';
 
 a1
 
 a
 
@@ -2349,10 +2349,10 @@
 
 BB
 
 EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   PRIMARY PRIMARY 7       NULL    501     Using where; Using index for group-by
 
+1      SIMPLE  t1      range   PRIMARY PRIMARY 7       NULL    1       Using where; Using index for group-by
 
 EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   PRIMARY PRIMARY 7       NULL    501     Using where; Using index for group-by
 
+1      SIMPLE  t1      range   PRIMARY PRIMARY 7       NULL    1       Using where; Using index for group-by
 
 SELECT DISTINCT a FROM t1 WHERE a='BB';
 
 a
 
 BB
 
@@ -2473,7 +2473,7 @@
 
 test.t1        analyze status  OK
 
 explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   PRIMARY,b       PRIMARY 8       NULL    2       Using where; Using index for group-by
 
+1      SIMPLE  t1      range   PRIMARY,b       PRIMARY 8       NULL    1       Using where; Using index for group-by
 
 SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
 
 MAX(b) a
 
 1      1
 
@@ -2488,7 +2488,7 @@
 
 test.t2        analyze status  OK
 
 explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   PRIMARY PRIMARY 12      NULL    2       Using where; Using index for group-by
 
+1      SIMPLE  t2      range   PRIMARY PRIMARY 12      NULL    1       Using where; Using index for group-by
 
 SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
 
 MIN(c)
 
 2
 
@@ -3171,13 +3171,13 @@
 
 2
 
 EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   NULL    a       10      NULL    17      Using index for group-by
 
+1      SIMPLE  t1      range   NULL    a       10      NULL    16      Using index for group-by (scanning)
 
 SELECT COUNT(DISTINCT a,b) FROM t1;
 
 COUNT(DISTINCT a,b)
 
 16
 
 EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   NULL    a       10      NULL    17      Using index for group-by
 
+1      SIMPLE  t1      range   NULL    a       10      NULL    16      Using index for group-by (scanning)
 
 SELECT COUNT(DISTINCT b,a) FROM t1;
 
 COUNT(DISTINCT b,a)
 
 16
 
@@ -3196,7 +3196,7 @@
 
 1
 
 EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   NULL    a       10      NULL    17      Using index for group-by
 
+1      SIMPLE  t1      range   NULL    a       10      NULL    16      Using index for group-by (scanning)
 
 SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
 
 COUNT(DISTINCT b)
 
 8
 
@@ -3246,7 +3246,7 @@
 
 1
 
 EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   NULL    a       10      NULL    17      Using index for group-by
 
+1      SIMPLE  t1      range   NULL    a       10      NULL    16      Using index for group-by (scanning)
 
 SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
 
 1
 
 1
 
@@ -3267,7 +3267,7 @@
 
 2      12
 
 EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   NULL    a       15      NULL    17      Using index for group-by
 
+1      SIMPLE  t2      range   NULL    a       15      NULL    16      Using index for group-by (scanning)
 
 SELECT COUNT(DISTINCT a, b, c) FROM t2;
 
 COUNT(DISTINCT a, b, c)
 
 16
 
@@ -3285,7 +3285,7 @@
 
 2      3       1.0000
 
 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   NULL    a       10      NULL    17      Using index for group-by
 
+1      SIMPLE  t2      range   NULL    a       10      NULL    16      Using index for group-by (scanning)
 
 SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
 
 COUNT(DISTINCT a, b)   COUNT(DISTINCT b, a)
 
 16     16
 
@@ -3303,7 +3303,7 @@
 
 16     8
 
 EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   NULL    a       15      NULL    17      Using index for group-by
 
+1      SIMPLE  t2      range   NULL    a       15      NULL    16      Using index for group-by (scanning)
 
 SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
 
 a      c       COUNT(DISTINCT c, a, b)
 
 1      1       1
 
@@ -3325,7 +3325,7 @@
 
 EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2
 
 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   a       a       15      NULL    17      Using where; Using index for group-by
 
+1      SIMPLE  t2      range   a       a       15      NULL    16      Using where; Using index for group-by (scanning)
 
 SELECT COUNT(DISTINCT c, a, b) FROM t2
 
 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
 
 COUNT(DISTINCT c, a, b)
 
@@ -3338,14 +3338,14 @@
 
 COUNT(DISTINCT b)      SUM(DISTINCT b)
 
 EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   NULL    a       10      NULL    17      Using index for group-by
 
+1      SIMPLE  t2      range   NULL    a       10      NULL    16      Using index for group-by (scanning)
 
 SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
 
 a      COUNT(DISTINCT b)       SUM(DISTINCT b)
 
 1      8       36
 
 2      8       36
 
 EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   NULL    a       10      NULL    17      Using index for group-by
 
+1      SIMPLE  t2      range   NULL    a       10      NULL    16      Using index for group-by (scanning)
 
 SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
 
 COUNT(DISTINCT b)      SUM(DISTINCT b)
 
 8      36
 
@@ -3380,7 +3380,7 @@
 
 2      8
 
 EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t2      range   NULL    a       15      NULL    17      Using index for group-by
 
+1      SIMPLE  t2      range   NULL    a       15      NULL    16      Using index for group-by (scanning)
 
 SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
 
 42 * (a + c + COUNT(DISTINCT c, a, b))
 
 126
 
@@ -3508,7 +3508,7 @@
 
 test.t1        analyze status  OK
 
 EXPLAIN SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
 
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
-1      SIMPLE  t1      range   c1      c1      5       NULL    6       Using where; Using index for group-by
 
+1      SIMPLE  t1      range   c1      c1      5       NULL    1       Using where; Using index for group-by
 
 FLUSH STATUS;
 
 SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
 
 MAX(c2)        c1



 Comments   
Comment by Sergei Petrunia [ 2023-06-29 ]

Looking at the optimizer trace, I see somewhat unrealistic cost numbers

              "table": "t1",
 
                "range_analysis": {
 
                  "table_scan": {
 
                    "rows": 128,
 
                    "cost": 0.04301184
 
                  },


                range_scan_alternatives": [
 
                      {
 
                        "index": "idx_t1_0",
 
                        "ranges": ["(a) <= (a1) <= (a)", "(d) <= (a1) <= (d)"],
 
                        "rowid_ordered": false,
 
                        "using_mrr": false,
 
                        "index_only": false,
 
                        "rows": 128,
 
                        "cost": 1.86285707,  <<<<  1.8 seconds for 128 rows? 
                        "chosen": false,
 
                        "cause": "cost"
 
                      },

Generated at Thu Feb 08 10:24:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.