1set @debug_tmp= @@debug_dbug; 2set global debug_dbug="+d,force_group_by"; 3drop table if exists t1; 4create table t1 ( 5a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' ' 6) engine=RocksDB; 7insert into t1 (a1, a2, b, c, d) values 8('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 9('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 10('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 11('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 12('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 13('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 14('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 15('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 16('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 17('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 18('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 19('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 20('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 21('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 22('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 23('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), 24('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 25('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 26('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 27('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 28('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 29('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 30('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 31('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 32('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 33('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 34('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 35('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 36('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 37('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 38('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 39('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); 40create index idx_t1_0 on t1 (a1); 41create index idx_t1_1 on t1 (a1,a2,b,c); 42create index idx_t1_2 on t1 (a1,a2,b); 43analyze table t1; 44Table Op Msg_type Msg_text 45test.t1 analyze status Engine-independent statistics collected 46test.t1 analyze status OK 47drop table if exists t2; 48create table t2 ( 49a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' ' 50) engine=RocksDB; 51insert into t2 select * from t1; 52insert into t2 (a1, a2, b, c, d) values 53('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 54('a','a','a',NULL,'xyz'), 55('a','a','b',NULL,'xyz'), 56('a','b','a',NULL,'xyz'), 57('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 58('d','b','b',NULL,'xyz'), 59('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 60('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'), 61('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 62('a','a','a',NULL,'xyz'), 63('a','a','b',NULL,'xyz'), 64('a','b','a',NULL,'xyz'), 65('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 66('d','b','b',NULL,'xyz'), 67('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 68('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'); 69create index idx_t2_0 on t2 (a1); 70create index idx_t2_1 on t2 (a1,a2,b,c); 71create index idx_t2_2 on t2 (a1,a2,b); 72analyze table t2; 73Table Op Msg_type Msg_text 74test.t2 analyze status Engine-independent statistics collected 75test.t2 analyze status OK 76drop table if exists t3; 77create table t3 ( 78a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' ' 79) engine=RocksDB; 80insert into t3 (a1, a2, b, c, d) values 81('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 82('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 83('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 84('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 85('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 86('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 87('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 88('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 89('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 90('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 91('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 92('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 93insert into t3 (a1, a2, b, c, d) values 94('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 95('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 96('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 97('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 98('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 99('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 100('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 101('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 102('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 103('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 104('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 105('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 106insert into t3 (a1, a2, b, c, d) values 107('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 108('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 109('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 110('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 111('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 112('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 113('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 114('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 115('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 116('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 117('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 118('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 119insert into t3 (a1, a2, b, c, d) values 120('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 121('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 122('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 123('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 124('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 125('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 126('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 127('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 128('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 129('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 130('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 131('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 132create index idx_t3_0 on t3 (a1); 133create index idx_t3_1 on t3 (a1,a2,b,c); 134create index idx_t3_2 on t3 (a1,a2,b); 135analyze table t3; 136Table Op Msg_type Msg_text 137test.t3 analyze status Engine-independent statistics collected 138test.t3 analyze status OK 139explain select a1, min(a2) from t1 group by a1; 140id select_type table type possible_keys key key_len ref rows Extra 1411 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using index for group-by 142explain select a1, max(a2) from t1 group by a1; 143id select_type table type possible_keys key key_len ref rows Extra 1441 SIMPLE t1 range NULL idx_t1_1 65 NULL 5 Using index for group-by 145explain select a1, min(a2), max(a2) from t1 group by a1; 146id select_type table type possible_keys key key_len ref rows Extra 1471 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using index for group-by 148explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; 149id select_type table type possible_keys key key_len ref rows Extra 1501 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by 151explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; 152id select_type table type possible_keys key key_len ref rows Extra 1531 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by 154explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; 155id select_type table type possible_keys key key_len ref rows Extra 1561 SIMPLE t2 range NULL idx_t2_1 # NULL # Using index for group-by 157explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; 158id select_type table type possible_keys key key_len ref rows Extra 1591 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using index for group-by 160explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; 161id select_type table type possible_keys key key_len ref rows Extra 1621 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by 163explain select min(a2) from t1 group by a1; 164id select_type table type possible_keys key key_len ref rows Extra 1651 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using index for group-by 166explain select a2, min(c), max(c) from t1 group by a1,a2,b; 167id select_type table type possible_keys key key_len ref rows Extra 1681 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by 169select a1, min(a2) from t1 group by a1; 170a1 min(a2) 171a a 172b a 173c a 174d a 175select a1, max(a2) from t1 group by a1; 176a1 max(a2) 177a b 178b b 179c b 180d b 181select a1, min(a2), max(a2) from t1 group by a1; 182a1 min(a2) max(a2) 183a a b 184b a b 185c a b 186d a b 187select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; 188a1 a2 b min(c) max(c) 189a a a a111 d111 190a a b e112 h112 191a b a i121 l121 192a b b m122 p122 193b a a a211 d211 194b a b e212 h212 195b b a i221 l221 196b b b m222 p222 197c a a a311 d311 198c a b e312 h312 199c b a i321 l321 200c b b m322 p322 201d a a a411 d411 202d a b e412 h412 203d b a i421 l421 204d b b m422 p422 205select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; 206a1 a2 b max(c) min(c) 207a a a d111 a111 208a a b h112 e112 209a b a l121 i121 210a b b p122 m122 211b a a d211 a211 212b a b h212 e212 213b b a l221 i221 214b b b p222 m222 215c a a d311 a311 216c a b h312 e312 217c b a l321 i321 218c b b p322 m322 219d a a d411 a411 220d a b h412 e412 221d b a l421 i421 222d b b p422 m422 223select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; 224a1 a2 b max(c) min(c) 225a a NULL a999 a777 226a a a d111 a111 227a a b h112 e112 228a b a l121 i121 229a b b p122 m122 230b a a d211 a211 231b a b h212 e212 232b b a l221 i221 233b b b p222 m222 234c a NULL c999 c777 235c a a d311 a311 236c a b h312 e312 237c b a l321 i321 238c b b p322 m322 239d a a d411 a411 240d a b h412 e412 241d b a l421 i421 242d b b p422 m422 243e a a NULL NULL 244e a b NULL NULL 245select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; 246min(a2) a1 max(a2) min(a2) a1 247a a b a a 248a b b a b 249a c b a c 250a d b a d 251select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; 252a1 b min(c) a1 max(c) b a2 max(c) max(c) 253a a a111 a d111 a a d111 d111 254a b e112 a h112 b a h112 h112 255a a i121 a l121 a b l121 l121 256a b m122 a p122 b b p122 p122 257b a a211 b d211 a a d211 d211 258b b e212 b h212 b a h212 h212 259b a i221 b l221 a b l221 l221 260b b m222 b p222 b b p222 p222 261c a a311 c d311 a a d311 d311 262c b e312 c h312 b a h312 h312 263c a i321 c l321 a b l321 l321 264c b m322 c p322 b b p322 p322 265d a a411 d d411 a a d411 d411 266d b e412 d h412 b a h412 h412 267d a i421 d l421 a b l421 l421 268d b m422 d p422 b b p422 p422 269select min(a2) from t1 group by a1; 270min(a2) 271a 272a 273a 274a 275select a2, min(c), max(c) from t1 group by a1,a2,b; 276a2 min(c) max(c) 277a a111 d111 278a e112 h112 279b i121 l121 280b m122 p122 281a a211 d211 282a e212 h212 283b i221 l221 284b m222 p222 285a a311 d311 286a e312 h312 287b i321 l321 288b m322 p322 289a a411 d411 290a e412 h412 291b i421 l421 292b m422 p422 293explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 294id select_type table type possible_keys key key_len ref rows Extra 2951 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 296explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 297id select_type table type possible_keys key key_len ref rows Extra 2981 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 299explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 300id select_type table type possible_keys key key_len ref rows Extra 3011 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 302explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 303id select_type table type possible_keys key key_len ref rows Extra 3041 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 305explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 306id select_type table type possible_keys key key_len ref rows Extra 3071 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 308explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 309id select_type table type possible_keys key key_len ref rows Extra 3101 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 311explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 312id select_type table type possible_keys key key_len ref rows Extra 3131 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 314explain select a1,a2,b, max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 315id select_type table type possible_keys key key_len ref rows Extra 3161 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 317explain 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') group by a1,a2,b; 318id select_type table type possible_keys key key_len ref rows Extra 3191 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 320explain select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 321id select_type table type possible_keys key key_len ref rows Extra 3221 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 323explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; 324id select_type table type possible_keys key key_len ref rows Extra 3251 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 326explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; 327id select_type table type possible_keys key key_len ref rows Extra 3281 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 329explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b; 330id select_type table type possible_keys key key_len ref rows Extra 3311 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 332explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 333id select_type table type possible_keys key key_len ref rows Extra 3341 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 335explain select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 336id select_type table type possible_keys key key_len ref rows Extra 3371 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 338explain select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 339id select_type table type possible_keys key key_len ref rows Extra 3401 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 341explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 342id select_type table type possible_keys key key_len ref rows Extra 3431 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 344explain select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 345id select_type table type possible_keys key key_len ref rows Extra 3461 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 347explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 348id select_type table type possible_keys key key_len ref rows Extra 3491 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 350explain select a1,a2,b, max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 351id select_type table type possible_keys key key_len ref rows Extra 3521 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 353explain select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 354id select_type table type possible_keys key key_len ref rows Extra 3551 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 356explain select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 357id select_type table type possible_keys key key_len ref rows Extra 3581 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 359explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; 360id select_type table type possible_keys key key_len ref rows Extra 3611 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 362select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 363a1 a2 b min(c) max(c) 364a a a a111 d111 365a a b e112 h112 366a b a i121 l121 367a b b m122 p122 368b a a a211 d211 369b a b e212 h212 370b b a i221 l221 371b b b m222 p222 372c a a a311 d311 373c a b e312 h312 374c b a i321 l321 375c b b m322 p322 376select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 377a1 a2 b min(c) max(c) 378b a a a211 d211 379b a b e212 h212 380b b a i221 l221 381b b b m222 p222 382c a a a311 d311 383c a b e312 h312 384c b a i321 l321 385c b b m322 p322 386d a a a411 d411 387d a b e412 h412 388d b a i421 l421 389d b b m422 p422 390select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 391a1 a2 b max(c) 392a a a d111 393a a b h112 394a b a l121 395a b b p122 396c a a d311 397c a b h312 398c b a l321 399c b b p322 400d a a d411 401d a b h412 402d b a l421 403d b b p422 404select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 405a1 max(c) 406a d111 407a h112 408a l121 409a p122 410c d311 411c h312 412c l321 413c p322 414d d411 415d h412 416d l421 417d p422 418select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 419a1 a2 b min(c) max(c) 420a a a a111 d111 421a a b e112 h112 422b a a a211 d211 423b a b e212 h212 424c a a a311 d311 425c a b e312 h312 426c b a i321 l321 427c b b m322 p322 428d a a a411 d411 429d a b e412 h412 430d b a i421 l421 431d b b m422 p422 432select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 433a1 a2 b max(c) 434b a a d211 435b a b h212 436b b a l221 437b b b p222 438d a a d411 439d a b h412 440d b a l421 441d b b p422 442select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 443a1 a2 b min(c) max(c) 444b a a a211 d211 445b a b e212 h212 446b b a i221 l221 447b b b m222 p222 448d a a a411 d411 449d a b e412 h412 450d b a i421 l421 451d b b m422 p422 452select a1,a2,b, max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 453a1 a2 b max(c) 454a b a l121 455a b b p122 456b b a l221 457b b b p222 458c b a l321 459c b b p322 460d b a l421 461d b b p422 462select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 463a1 a2 b min(c) max(c) 464a b a i121 l121 465a b b m122 p122 466b b a i221 l221 467b b b m222 p222 468c b a i321 l321 469c b b m322 p322 470d b a i421 l421 471d b b m422 p422 472select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 473a1 min(c) max(c) 474b a211 d211 475b e212 h212 476b i221 l221 477b m222 p222 478c a311 d311 479c e312 h312 480c i321 l321 481c m322 p322 482d a411 d411 483d e412 h412 484d i421 l421 485d m422 p422 486select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; 487a1 max(c) 488a d111 489a h112 490a l121 491a p122 492b d211 493b h212 494b l221 495b p222 496d d411 497d h412 498d l421 499d p422 500select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; 501a1 a2 b max(c) 502a a NULL a999 503a a a d111 504a a b h112 505a b a l121 506a b b p122 507b a a d211 508b a b h212 509b b a l221 510b b b p222 511c a NULL c999 512c a a d311 513c a b h312 514c b a l321 515c b b p322 516select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b; 517a1 a2 b min(c) max(c) 518a a NULL a777 a999 519a a a a111 d111 520a a b e112 h112 521a b a i121 l121 522a b b m122 p122 523b a a a211 d211 524b a b e212 h212 525b b a i221 l221 526b b b m222 p222 527c a NULL c777 c999 528c a a a311 d311 529c a b e312 h312 530c b a i321 l321 531c b b m322 p322 532select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 533a1 a2 b min(c) max(c) 534b a a a211 d211 535b a b e212 h212 536b b a i221 l221 537b b b m222 p222 538c a NULL c777 c999 539c a a a311 d311 540c a b e312 h312 541c b a i321 l321 542c b b m322 p322 543d a a a411 d411 544d a b e412 h412 545d b a i421 l421 546d b b m422 p422 547e a a NULL NULL 548e a b NULL NULL 549select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 550a1 a2 b max(c) 551a a NULL a999 552a a a d111 553a a b h112 554a b a l121 555a b b p122 556c a NULL c999 557c a a d311 558c a b h312 559c b a l321 560c b b p322 561d a a d411 562d a b h412 563d b a l421 564d b b p422 565e a a NULL 566e a b NULL 567select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 568a1 max(c) 569a a999 570a d111 571a h112 572a l121 573a p122 574c c999 575c d311 576c h312 577c l321 578c p322 579d d411 580d h412 581d l421 582d p422 583e NULL 584e NULL 585select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 586a1 a2 b min(c) max(c) 587a a NULL a777 a999 588a a a a111 d111 589a a b e112 h112 590b a a a211 d211 591b a b e212 h212 592c a NULL c777 c999 593c a a a311 d311 594c a b e312 h312 595c b a i321 l321 596c b b m322 p322 597d a a a411 d411 598d a b e412 h412 599d b a i421 l421 600d b b m422 p422 601e a a NULL NULL 602e a b NULL NULL 603select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 604a1 a2 b max(c) 605b a a d211 606b a b h212 607b b a l221 608b b b p222 609d a a d411 610d a b h412 611d b a l421 612d b b p422 613select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 614a1 a2 b min(c) max(c) 615b a a a211 d211 616b a b e212 h212 617b b a i221 l221 618b b b m222 p222 619d a a a411 d411 620d a b e412 h412 621d b a i421 l421 622d b b m422 p422 623select a1,a2,b, max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 624a1 a2 b max(c) 625a b a l121 626a b b p122 627b b a l221 628b b b p222 629c b a l321 630c b b p322 631d b a l421 632d b b p422 633select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 634a1 a2 b min(c) max(c) 635a b a i121 l121 636a b b m122 p122 637b b a i221 l221 638b b b m222 p222 639c b a i321 l321 640c b b m322 p322 641d b a i421 l421 642d b b m422 p422 643select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 644a1 min(c) max(c) 645b a211 d211 646b e212 h212 647b i221 l221 648b m222 p222 649c c777 c999 650c a311 d311 651c e312 h312 652c i321 l321 653c m322 p322 654d a411 d411 655d e412 h412 656d i421 l421 657d m422 p422 658e NULL NULL 659e NULL NULL 660select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; 661a1 max(c) 662a a999 663a d111 664a h112 665a l121 666a p122 667b d211 668b h212 669b l221 670b p222 671d d411 672d h412 673d l421 674d p422 675# 676# MariaDB: we dont have the following patch: 677# 678# commit 60a92a79a3b7fde3c6efe91799e344b977c8e5c3 679# Author: Manuel Ung <mung@fb.com> 680# Date: Thu Apr 19 23:06:27 2018 -0700 681# 682# Enhance group-by loose index scan 683# 684# So the following results are not very meaningful, but are still kept here 685explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 686id select_type table type possible_keys key key_len ref rows Extra 6871 SIMPLE t1 range NULL idx_t1_1 147 NULL 5 Using where; Using index for group-by 688explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 689id select_type table type possible_keys key key_len ref rows Extra 6901 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 691explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 692id select_type table type possible_keys key key_len ref rows Extra 6931 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 694explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; 695id select_type table type possible_keys key key_len ref rows Extra 6961 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 697explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 698id select_type table type possible_keys key key_len ref rows Extra 6991 SIMPLE t1 range NULL idx_t1_1 147 NULL 5 Using where; Using index for group-by 700explain select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 701id select_type table type possible_keys key key_len ref rows Extra 7021 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 703explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 704id select_type table type possible_keys key key_len ref rows Extra 7051 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 706explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; 707id select_type table type possible_keys key key_len ref rows Extra 7081 SIMPLE t1 range NULL idx_t1_1 147 NULL 9 Using where; Using index for group-by 709explain select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 710id select_type table type possible_keys key key_len ref rows Extra 7111 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 712explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; 713id select_type table type possible_keys key key_len ref rows Extra 7141 SIMPLE t1 range NULL idx_t1_1 147 NULL 9 Using where; Using index for group-by 715explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 716id select_type table type possible_keys key key_len ref rows Extra 7171 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 718explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; 719id select_type table type possible_keys key key_len ref rows Extra 7201 SIMPLE t1 range NULL idx_t1_1 147 NULL 9 Using where; Using index for group-by 721explain select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 722id select_type table type possible_keys key key_len ref rows Extra 7231 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 724explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 725id select_type table type possible_keys key key_len ref rows Extra 7261 SIMPLE t2 range NULL idx_t2_1 163 NULL 6 Using where; Using index for group-by 727explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 728id select_type table type possible_keys key key_len ref rows Extra 7291 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 730explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 731id select_type table type possible_keys key key_len ref rows Extra 7321 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 733explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 734id select_type table type possible_keys key key_len ref rows Extra 7351 SIMPLE t2 range NULL idx_t2_1 163 NULL 6 Using where; Using index for group-by 736explain select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 737id select_type table type possible_keys key key_len ref rows Extra 7381 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 739explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 740id select_type table type possible_keys key key_len ref rows Extra 7411 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 742explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; 743id select_type table type possible_keys key key_len ref rows Extra 7441 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by 745explain select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 746id select_type table type possible_keys key key_len ref rows Extra 7471 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 748explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; 749id select_type table type possible_keys key key_len ref rows Extra 7501 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by 751explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 752id select_type table type possible_keys key key_len ref rows Extra 7531 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 754explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; 755id select_type table type possible_keys key key_len ref rows Extra 7561 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by 757explain select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 758id select_type table type possible_keys key key_len ref rows Extra 7591 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 760explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 761id select_type table type possible_keys key key_len ref rows Extra 7621 SIMPLE t3 range NULL idx_t3_1 6 NULL 4 Using where; Using index for group-by 763explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 764id select_type table type possible_keys key key_len ref rows Extra 7651 SIMPLE t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index 766explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 767id select_type table type possible_keys key key_len ref rows Extra 7681 SIMPLE t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index 769explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 770id select_type table type possible_keys key key_len ref rows Extra 7711 SIMPLE t3 range NULL idx_t3_1 6 NULL 4 Using where; Using index for group-by 772explain select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 773id select_type table type possible_keys key key_len ref rows Extra 7741 SIMPLE t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index 775explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 776id select_type table type possible_keys key key_len ref rows Extra 7771 SIMPLE t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index 778select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 779a1 a2 b max(c) min(c) 780a a b h112 e112 781b a b h212 e212 782c a b h312 e312 783d a b h412 e412 784select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 785a1 a2 b max(c) min(c) 786a a b p122 e112 787b a b p222 e212 788c a b p322 e312 789d a b p422 e412 790select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 791a1 a2 b max(c) min(c) 792a a a h112 a111 793b a a h212 a211 794c a a h312 a311 795d a a h412 a411 796select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; 797a1 a2 b max(c) min(c) 798a a a p122 a111 799b a a p222 a211 800c a a p322 a311 801d a a p422 a411 802select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 803a1 max(c) min(c) 804a h112 e112 805b h212 e212 806c h312 e312 807d h412 e412 808select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 809a1 max(c) min(c) 810a p122 e112 811b p222 e212 812c p322 e312 813d p422 e412 814select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 815a1 max(c) min(c) 816a h112 a111 817b h212 a211 818c h312 a311 819d h412 a411 820select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; 821a1 a2 b max(c) 822a a b h112 823a b b p122 824b a b h212 825b b b p222 826c a b h312 827c b b p322 828d a b h412 829d b b p422 830select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 831a1 a2 b max(c) 832a a a h112 833a b a p122 834b a a h212 835b b a p222 836c a a h312 837c b a p322 838d a a h412 839d b a p422 840select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; 841a1 a2 b min(c) max(c) 842a a b e112 h112 843a b b m122 p122 844b a b e212 h212 845b b b m222 p222 846c a b e312 h312 847c b b m322 p322 848d a b e412 h412 849d b b m422 p422 850select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 851a1 a2 b min(c) max(c) 852a a a a111 h112 853a b a i121 p122 854b a a a211 h212 855b b a i221 p222 856c a a a311 h312 857c b a i321 p322 858d a a a411 h412 859d b a i421 p422 860select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; 861a1 a2 max(c) 862a a h112 863a b p122 864b a h212 865b b p222 866c a h312 867c b p322 868d a h412 869d b p422 870select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 871a1 a2 max(c) 872a a h112 873a b p122 874b a h212 875b b p222 876c a h312 877c b p322 878d a h412 879d b p422 880select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 881a1 a2 b max(c) min(c) 882a a b h112 e112 883b a b h212 e212 884c a b h312 e312 885d a b h412 e412 886e a b NULL NULL 887select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 888a1 a2 b max(c) min(c) 889a a b p122 e112 890b a b p222 e212 891c a b p322 e312 892d a b p422 e412 893e a b NULL NULL 894select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 895a1 a2 b max(c) min(c) 896a a a h112 a111 897b a a h212 a211 898c a a h312 a311 899d a a h412 a411 900e a a NULL NULL 901select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 902a1 max(c) min(c) 903a h112 e112 904b h212 e212 905c h312 e312 906d h412 e412 907e NULL NULL 908select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 909a1 max(c) min(c) 910a p122 e112 911b p222 e212 912c p322 e312 913d p422 e412 914e NULL NULL 915select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 916a1 max(c) min(c) 917a h112 a111 918b h212 a211 919c h312 a311 920d h412 a411 921e NULL NULL 922select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; 923a1 a2 b max(c) 924a a b h112 925a b b p122 926b a b h212 927b b b p222 928c a b h312 929c b b p322 930d a b h412 931d b b p422 932e a b NULL 933select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 934a1 a2 b max(c) 935a a a h112 936a b a p122 937b a a h212 938b b a p222 939c a a h312 940c b a p322 941d a a h412 942d b a p422 943e a a NULL 944select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; 945a1 a2 b min(c) max(c) 946a a b e112 h112 947a b b m122 p122 948b a b e212 h212 949b b b m222 p222 950c a b e312 h312 951c b b m322 p322 952d a b e412 h412 953d b b m422 p422 954e a b NULL NULL 955select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 956a1 a2 b min(c) max(c) 957a a a a111 h112 958a b a i121 p122 959b a a a211 h212 960b b a i221 p222 961c a a a311 h312 962c b a i321 p322 963d a a a411 h412 964d b a i421 p422 965e a a NULL NULL 966select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; 967a1 a2 max(c) 968a a h112 969a b p122 970b a h212 971b b p222 972c a h312 973c b p322 974d a h412 975d b p422 976e a NULL 977select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 978a1 a2 max(c) 979a a h112 980a b p122 981b a h212 982b b p222 983c a h312 984c b p322 985d a h412 986d b p422 987e a NULL 988select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 989a1 a2 b max(c) min(c) 990a a b h112 e112 991b a b h212 e212 992c a b h312 e312 993select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 994a1 a2 b max(c) min(c) 995a a b p122 e112 996b a b p222 e212 997c a b p322 e312 998select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 999a1 a2 b max(c) min(c) 1000a a a h112 a111 1001b a a h212 a211 1002c a a h312 a311 1003select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 1004a1 max(c) min(c) 1005a h112 e112 1006b h212 e212 1007c h312 e312 1008select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 1009a1 max(c) min(c) 1010a p122 e112 1011b p222 e212 1012c p322 e312 1013select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 1014a1 max(c) min(c) 1015a h112 a111 1016b h212 a211 1017c h312 a311 1018explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; 1019id select_type table type possible_keys key key_len ref rows Extra 10201 SIMPLE t2 range NULL idx_t2_1 163 NULL 6 Using where; Using index for group-by 1021explain select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; 1022id select_type table type possible_keys key key_len ref rows Extra 10231 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 1024explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; 1025id select_type table type possible_keys key key_len ref rows Extra 10261 SIMPLE t2 range NULL idx_t2_1 146 NULL 6 Using where; Using index for group-by 1027explain select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; 1028id select_type table type possible_keys key key_len ref rows Extra 10291 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 1030explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; 1031id select_type table type possible_keys key key_len ref rows Extra 10321 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by 1033explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; 1034id select_type table type possible_keys key key_len ref rows Extra 10351 SIMPLE t2 range NULL idx_t2_1 146 NULL 10 Using where; Using index for group-by 1036explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 1037id select_type table type possible_keys key key_len ref rows Extra 10381 SIMPLE t2 range NULL idx_t2_1 163 NULL 10 Using where; Using index for group-by 1039select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; 1040a1 a2 b min(c) 1041a a NULL a777 1042c a NULL c777 1043select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; 1044a1 a2 b min(c) 1045a a NULL a777 1046c a NULL c777 1047select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; 1048a1 a2 b max(c) 1049a a NULL a999 1050c a NULL c999 1051select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; 1052a1 a2 b max(c) 1053a a NULL a999 1054c a NULL c999 1055select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; 1056a1 a2 b min(c) 1057a a NULL a777 1058c a NULL c777 1059select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; 1060a1 a2 b max(c) 1061a a NULL a999 1062c a NULL c999 1063select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 1064a1 a2 b min(c) max(c) 1065a a NULL a777 a999 1066c a NULL c777 c999 1067select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 1068a1 a2 b min(c) max(c) 1069a a NULL a777 a999 1070c a NULL c777 c999 1071explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; 1072id select_type table type possible_keys key key_len ref rows Extra 10731 SIMPLE t1 range NULL idx_t1_1 147 NULL # Using where; Using index for group-by 1074explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; 1075id select_type table type possible_keys key key_len ref rows Extra 10761 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1077explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; 1078id select_type table type possible_keys key key_len ref rows Extra 10791 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by 1080explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; 1081id select_type table type possible_keys key key_len ref rows Extra 10821 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1083explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; 1084id select_type table type possible_keys key key_len ref rows Extra 10851 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1086explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; 1087id select_type table type possible_keys key key_len ref rows Extra 10881 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1089explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; 1090id select_type table type possible_keys key key_len ref rows Extra 10911 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1092explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; 1093id select_type table type possible_keys key key_len ref rows Extra 10941 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1095explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1096id select_type table type possible_keys key key_len ref rows Extra 10971 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1098explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1099id select_type table type possible_keys key key_len ref rows Extra 11001 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1101explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1102id select_type table type possible_keys key key_len ref rows Extra 11031 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by 1104explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1105id select_type table type possible_keys key key_len ref rows Extra 11061 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by 1107explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 1108id select_type table type possible_keys key key_len ref rows Extra 11091 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1110explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 1111id select_type table type possible_keys key key_len ref rows Extra 11121 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1113explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; 1114id select_type table type possible_keys key key_len ref rows Extra 11151 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1116explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b; 1117id select_type table type possible_keys key key_len ref rows Extra 11181 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1119explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; 1120id select_type table type possible_keys key key_len ref rows Extra 11211 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by 1122explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; 1123id select_type table type possible_keys key key_len ref rows Extra 11241 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1125explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; 1126id select_type table type possible_keys key key_len ref rows Extra 11271 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by 1128explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; 1129id select_type table type possible_keys key key_len ref rows Extra 11301 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1131explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; 1132id select_type table type possible_keys key key_len ref rows Extra 11331 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1134explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; 1135id select_type table type possible_keys key key_len ref rows Extra 11361 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1137explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; 1138id select_type table type possible_keys key key_len ref rows Extra 11391 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1140explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; 1141id select_type table type possible_keys key key_len ref rows Extra 11421 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1143explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1144id select_type table type possible_keys key key_len ref rows Extra 11451 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1146explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1147id select_type table type possible_keys key key_len ref rows Extra 11481 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1149explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1150id select_type table type possible_keys key key_len ref rows Extra 11511 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by 1152explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1153id select_type table type possible_keys key key_len ref rows Extra 11541 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1155explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 1156id select_type table type possible_keys key key_len ref rows Extra 11571 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1158explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 1159id select_type table type possible_keys key key_len ref rows Extra 11601 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1161explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; 1162id select_type table type possible_keys key key_len ref rows Extra 11631 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1164select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; 1165a1 a2 b max(c) 1166a a a d111 1167a a b h112 1168a b a l121 1169a b b p122 1170b a a d211 1171b a b h212 1172b b a l221 1173b b b p222 1174c a a d311 1175c a b h312 1176c b a l321 1177c b b p322 1178d a a d411 1179d a b h412 1180d b a l421 1181d b b p422 1182select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; 1183a1 a2 b min(c) max(c) 1184a a a b111 d111 1185a a b e112 h112 1186a b a i121 l121 1187a b b m122 p122 1188b a a b211 d211 1189b a b e212 h212 1190b b a i221 l221 1191b b b m222 p222 1192c a a b311 d311 1193c a b e312 h312 1194c b a i321 l321 1195c b b m322 p322 1196d a a b411 d411 1197d a b e412 h412 1198d b a i421 l421 1199d b b m422 p422 1200select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; 1201a1 a2 b max(c) 1202a a b h112 1203a b a l121 1204a b b p122 1205b a b h212 1206b b a l221 1207b b b p222 1208c a b h312 1209c b a l321 1210c b b p322 1211d a b h412 1212d b a l421 1213d b b p422 1214select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; 1215a1 a2 b min(c) max(c) 1216a a b g112 h112 1217a b a i121 l121 1218a b b m122 p122 1219b a b f212 h212 1220b b a i221 l221 1221b b b m222 p222 1222c a b f312 h312 1223c b a i321 l321 1224c b b m322 p322 1225d a b f412 h412 1226d b a i421 l421 1227d b b m422 p422 1228select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; 1229a1 a2 b max(c) 1230select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; 1231a1 a2 b min(c) max(c) 1232select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; 1233a1 a2 b max(c) 1234a a a d111 1235a a b h112 1236a b a k121 1237b a a d211 1238b a b h212 1239b b a k221 1240c a a d311 1241c a b h312 1242c b a j321 1243d a a d411 1244d a b h412 1245d b a j421 1246select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; 1247a1 a2 b min(c) max(c) 1248a a a a111 d111 1249a a b e112 h112 1250a b a i121 k121 1251b a a a211 d211 1252b a b e212 h212 1253b b a i221 k221 1254c a a a311 d311 1255c a b e312 h312 1256c b a i321 j321 1257d a a a411 d411 1258d a b e412 h412 1259d b a i421 j421 1260select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1261a1 a2 b max(c) 1262a a a d111 1263a a b h112 1264a b a l121 1265a b b p122 1266b a a d211 1267b a b h212 1268b b a l221 1269b b b p222 1270c a a d311 1271c a b h312 1272c b a l321 1273c b b p322 1274d a a d411 1275d a b h412 1276d b a l421 1277d b b p422 1278select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1279a1 a2 b min(c) max(c) 1280a a a b111 d111 1281a a b e112 h112 1282a b a i121 l121 1283a b b m122 p122 1284b a a b211 d211 1285b a b e212 h212 1286b b a i221 l221 1287b b b m222 p222 1288c a a b311 d311 1289c a b e312 h312 1290c b a i321 l321 1291c b b m322 p322 1292d a a b411 d411 1293d a b e412 h412 1294d b a i421 l421 1295d b b m422 p422 1296select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1297a1 a2 b max(c) 1298a a a d111 1299a a b h112 1300a b a l121 1301a b b p122 1302b a a d211 1303b a b h212 1304b b a l221 1305b b b p222 1306c a a d311 1307c a b h312 1308c b a l321 1309c b b p322 1310d a a d411 1311d a b h412 1312d b a l421 1313d b b p422 1314select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1315a1 a2 b min(c) max(c) 1316a a a a111 d111 1317a a b e112 h112 1318a b a i121 l121 1319a b b m122 p122 1320b a a a211 d211 1321b a b e212 h212 1322b b a i221 l221 1323b b b m222 p222 1324c a a a311 d311 1325c a b e312 h312 1326c b a i321 l321 1327c b b m322 p322 1328d a a a411 d411 1329d a b e412 h412 1330d b a i421 l421 1331d b b m422 p422 1332select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 1333a1 a2 b min(c) max(c) 1334a a a c111 d111 1335a a b e112 g112 1336b a a b211 d211 1337b a b e212 f212 1338c a a b311 d311 1339c a b e312 f312 1340d a a b411 d411 1341d a b e412 f412 1342select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 1343a1 a2 b min(c) max(c) 1344a a a a111 c111 1345b a a a211 c211 1346c a a a311 c311 1347d a a a411 c411 1348d a b g412 g412 1349d b a k421 k421 1350select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; 1351a1 a2 b min(c) max(c) 1352a a a c111 d111 1353a a b e112 h112 1354b a a b211 d211 1355b a b e212 h212 1356c a a b311 d311 1357c a b e312 h312 1358d a a b411 d411 1359d a b e412 h412 1360select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b; 1361a1 a2 b min(c) max(c) 1362a a a b111 d111 1363a a b e112 h112 1364b a a b211 d211 1365b a b e212 h212 1366c a a b311 d311 1367c a b e312 h312 1368d a a b411 d411 1369d a b e412 h412 1370select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; 1371a1 a2 b max(c) 1372a a a d111 1373a a b h112 1374a b a l121 1375a b b p122 1376b a a d211 1377b a b h212 1378b b a l221 1379b b b p222 1380c a NULL c999 1381c a a d311 1382c a b h312 1383c b a l321 1384c b b p322 1385d a a d411 1386d a b h412 1387d b a l421 1388d b b p422 1389select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; 1390a1 a2 b min(c) max(c) 1391a a a b111 d111 1392a a b e112 h112 1393a b a i121 l121 1394a b b m122 p122 1395b a a b211 d211 1396b a b e212 h212 1397b b a i221 l221 1398b b b m222 p222 1399c a NULL c777 c999 1400c a a b311 d311 1401c a b e312 h312 1402c b a i321 l321 1403c b b m322 p322 1404d a a b411 d411 1405d a b e412 h412 1406d b a i421 l421 1407d b b m422 p422 1408select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; 1409a1 a2 b max(c) 1410a a b h112 1411a b a l121 1412a b b p122 1413b a b h212 1414b b a l221 1415b b b p222 1416c a b h312 1417c b a l321 1418c b b p322 1419d a b h412 1420d b a l421 1421d b b p422 1422select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; 1423a1 a2 b min(c) max(c) 1424a a b g112 h112 1425a b a i121 l121 1426a b b m122 p122 1427b a b f212 h212 1428b b a i221 l221 1429b b b m222 p222 1430c a b f312 h312 1431c b a i321 l321 1432c b b m322 p322 1433d a b f412 h412 1434d b a i421 l421 1435d b b m422 p422 1436select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; 1437a1 a2 b max(c) 1438select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; 1439a1 a2 b min(c) max(c) 1440select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; 1441a1 a2 b max(c) 1442a a NULL a999 1443a a a d111 1444a a b h112 1445a b a k121 1446b a a d211 1447b a b h212 1448b b a k221 1449c a NULL c999 1450c a a d311 1451c a b h312 1452c b a j321 1453d a a d411 1454d a b h412 1455d b a j421 1456select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; 1457a1 a2 b min(c) max(c) 1458a a NULL a777 a999 1459a a a a111 d111 1460a a b e112 h112 1461a b a i121 k121 1462b a a a211 d211 1463b a b e212 h212 1464b b a i221 k221 1465c a NULL c777 c999 1466c a a a311 d311 1467c a b e312 h312 1468c b a i321 j321 1469d a a a411 d411 1470d a b e412 h412 1471d b a i421 j421 1472select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1473a1 a2 b max(c) 1474a a a d111 1475a a b h112 1476a b a l121 1477a b b p122 1478b a a d211 1479b a b h212 1480b b a l221 1481b b b p222 1482c a NULL c999 1483c a a d311 1484c a b h312 1485c b a l321 1486c b b p322 1487d a a d411 1488d a b h412 1489d b a l421 1490d b b p422 1491select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1492a1 a2 b min(c) max(c) 1493a a a b111 d111 1494a a b e112 h112 1495a b a i121 l121 1496a b b m122 p122 1497b a a b211 d211 1498b a b e212 h212 1499b b a i221 l221 1500b b b m222 p222 1501c a NULL c777 c999 1502c a a b311 d311 1503c a b e312 h312 1504c b a i321 l321 1505c b b m322 p322 1506d a a b411 d411 1507d a b e412 h412 1508d b a i421 l421 1509d b b m422 p422 1510select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1511a1 a2 b max(c) 1512a a NULL a999 1513a a a d111 1514a a b h112 1515a b a l121 1516a b b p122 1517b a a d211 1518b a b h212 1519b b a l221 1520b b b p222 1521c a NULL c999 1522c a a d311 1523c a b h312 1524c b a l321 1525c b b p322 1526d a a d411 1527d a b h412 1528d b a l421 1529d b b p422 1530select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1531a1 a2 b min(c) max(c) 1532a a NULL a777 a999 1533a a a a111 d111 1534a a b e112 h112 1535a b a i121 l121 1536a b b m122 p122 1537b a a a211 d211 1538b a b e212 h212 1539b b a i221 l221 1540b b b m222 p222 1541c a NULL c777 c999 1542c a a a311 d311 1543c a b e312 h312 1544c b a i321 l321 1545c b b m322 p322 1546d a a a411 d411 1547d a b e412 h412 1548d b a i421 l421 1549d b b m422 p422 1550select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 1551a1 a2 b min(c) max(c) 1552a a a c111 d111 1553a a b e112 g112 1554b a a b211 d211 1555b a b e212 f212 1556c a NULL c777 c999 1557c a a b311 d311 1558c a b e312 f312 1559d a a b411 d411 1560d a b e412 f412 1561select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 1562a1 a2 b min(c) max(c) 1563a a NULL a777 a999 1564a a a a111 c111 1565b a a a211 c211 1566c a a a311 c311 1567d a a a411 c411 1568d a b g412 g412 1569d b a k421 k421 1570select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; 1571a1 a2 b min(c) max(c) 1572a a a c111 d111 1573a a b e112 h112 1574b a a b211 d211 1575b a b e212 h212 1576c a NULL c777 c999 1577c a a b311 d311 1578c a b e312 h312 1579d a a b411 d411 1580d a b e412 h412 1581explain select a1,a2,b,min(c),max(c) from t1 1582where exists ( select * from t2 where t2.c = t1.c ) 1583group by a1,a2,b; 1584id select_type table type possible_keys key key_len ref rows Extra 15851 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using index 15861 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func 1 15872 MATERIALIZED t2 index NULL idx_t2_1 163 NULL 164 Using index 1588explain select a1,a2,b,min(c),max(c) from t1 1589where exists ( select * from t2 where t2.c > 'b1' ) 1590group by a1,a2,b; 1591id select_type table type possible_keys key key_len ref rows Extra 15921 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using index 15932 SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 1594explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1595id select_type table type possible_keys key key_len ref rows Extra 15961 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 1597explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 1598id select_type table type possible_keys key key_len ref rows Extra 15991 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 17 Using where; Using index for group-by 1600explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 1601id select_type table type possible_keys key key_len ref rows Extra 16021 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1603explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; 1604id select_type table type possible_keys key key_len ref rows Extra 16051 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 17 Using where; Using index for group-by 1606explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; 1607id select_type table type possible_keys key key_len ref rows Extra 16081 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 17 Using where; Using index for group-by 1609explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1610id select_type table type possible_keys key key_len ref rows Extra 16111 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 1612explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b; 1613id select_type table type possible_keys key key_len ref rows Extra 16141 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by 1615explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1616id select_type table type possible_keys key key_len ref rows Extra 16171 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1618explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 1619id select_type table type possible_keys key key_len ref rows Extra 16201 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1621explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 1622id select_type table type possible_keys key key_len ref rows Extra 16231 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1624explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; 1625id select_type table type possible_keys key key_len ref rows Extra 16261 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1627explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; 1628id select_type table type possible_keys key key_len ref rows Extra 16291 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1630explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1631id select_type table type possible_keys key key_len ref rows Extra 16321 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1633select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1634a1 a2 b min(c) max(c) 1635a a b e112 h112 1636b a b e212 h212 1637c a b e312 h312 1638c b b m322 p322 1639d a b e412 h412 1640d b b m422 p422 1641select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 1642a1 a2 b min(c) max(c) 1643a a a c111 d111 1644a a b e112 h112 1645b a a b211 d211 1646b a b e212 h212 1647c a a b311 d311 1648c a b e312 h312 1649c b a i321 l321 1650c b b m322 p322 1651d a a b411 d411 1652d a b e412 h412 1653d b a i421 l421 1654d b b m422 p422 1655select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 1656a1 a2 b min(c) max(c) 1657a b a i121 l121 1658b b a i221 l221 1659c b a i321 l321 1660d b a i421 l421 1661select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; 1662a1 a2 b min(c) 1663b b a k221 1664c b a k321 1665d b a k421 1666select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; 1667a1 a2 b min(c) 1668b b a k221 1669c b a k321 1670d b a k421 1671select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1672a1 a2 b min(c) 1673select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b; 1674a1 a2 b min(c) 1675select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1676a1 a2 b min(c) max(c) 1677a a b e112 h112 1678b a b e212 h212 1679c a b e312 h312 1680c b b m322 p322 1681d a b e412 h412 1682d b b m422 p422 1683e a b NULL NULL 1684select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 1685a1 a2 b min(c) max(c) 1686a a a c111 d111 1687a a b e112 h112 1688b a a b211 d211 1689b a b e212 h212 1690c a NULL c777 c999 1691c a a b311 d311 1692c a b e312 h312 1693c b a i321 l321 1694c b b m322 p322 1695d a a b411 d411 1696d a b e412 h412 1697d b a i421 l421 1698d b b m422 p422 1699select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 1700a1 a2 b min(c) max(c) 1701a b a i121 l121 1702b b a i221 l221 1703c b a i321 l321 1704d b a i421 l421 1705select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; 1706a1 a2 b min(c) 1707b b a k221 1708c b a k321 1709d b a k421 1710select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; 1711a1 a2 b min(c) 1712b b a k221 1713c b a k321 1714d b a k421 1715select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1716a1 a2 b min(c) 1717explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1718id select_type table type possible_keys key key_len ref rows Extra 17191 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 17 Using where; Using index for group-by 1720explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1721id select_type table type possible_keys key key_len ref rows Extra 17221 SIMPLE t1 range NULL idx_t1_2 147 NULL 17 Using where; Using index for group-by 1723explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1724id select_type table type possible_keys key key_len ref rows Extra 17251 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1726explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; 1727id select_type table type possible_keys key key_len ref rows Extra 17281 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1729explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1730id select_type table type possible_keys key key_len ref rows Extra 17311 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 17 Using where; Using index for group-by 1732explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1733id select_type table type possible_keys key key_len ref rows Extra 17341 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_2 146 NULL # Using where; Using index for group-by 1735explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1736id select_type table type possible_keys key key_len ref rows Extra 17371 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using where; Using index for group-by 1738explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1739id select_type table type possible_keys key key_len ref rows Extra 17401 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1741explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; 1742id select_type table type possible_keys key key_len ref rows Extra 17431 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1744explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1745id select_type table type possible_keys key key_len ref rows Extra 17461 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_2 146 NULL # Using where; Using index for group-by 1747select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1748a1 a2 b 1749a a b 1750b a b 1751c a b 1752c b b 1753d a b 1754d b b 1755select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1756a1 a2 b 1757a b a 1758b b a 1759c b a 1760d b a 1761select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1762a1 a2 b c 1763a b a i121 1764select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; 1765a1 a2 b c 1766a b a i121 1767select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1768a1 a2 b 1769select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1770a1 a2 b 1771a a b 1772b a b 1773c a b 1774c b b 1775d a b 1776d b b 1777e a b 1778select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1779a1 a2 b 1780a b a 1781b b a 1782c b a 1783d b a 1784select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1785a1 a2 b c 1786a b a i121 1787select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; 1788a1 a2 b c 1789a b a i121 1790select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1791a1 a2 b 1792explain select distinct a1,a2,b from t1; 1793id select_type table type possible_keys key key_len ref rows Extra 17941 SIMPLE t1 range NULL idx_t1_2 147 NULL 17 Using index for group-by 1795explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); 1796id select_type table type possible_keys key key_len ref rows Extra 17971 SIMPLE t1 range NULL idx_t1_2 147 NULL 17 Using where; Using index for group-by 1798explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 1799id select_type table type possible_keys key key_len ref rows filtered Extra 18001 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 100.00 Using where; Using index for group-by 1801Warnings: 1802Note 1003 select distinct `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`.`b` = 'a' and `test`.`t1`.`c` = 'i121' and `test`.`t1`.`a2` >= 'b' 1803explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 1804id select_type table type possible_keys key key_len ref rows Extra 18051 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 17 Using where; Using index for group-by 1806explain select distinct b from t1 where (a2 >= 'b') and (b = 'a'); 1807id select_type table type possible_keys key key_len ref rows Extra 18081 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index 1809explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b'; 1810id select_type table type possible_keys key key_len ref rows Extra 18111 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 1812explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e'; 1813id select_type table type possible_keys key key_len ref rows Extra 18141 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 1815explain select distinct a1,a2,b from t2; 1816id select_type table type possible_keys key key_len ref rows Extra 18171 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using index for group-by 1818explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); 1819id select_type table type possible_keys key key_len ref rows Extra 18201 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using where; Using index for group-by 1821explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 1822id select_type table type possible_keys key key_len ref rows filtered Extra 18231 SIMPLE t2 range NULL idx_t2_1 163 NULL 83 100.00 Using where; Using index for group-by 1824Warnings: 1825Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`b` = 'a' and `test`.`t2`.`c` = 'i121' and `test`.`t2`.`a2` >= 'b' 1826explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 1827id select_type table type possible_keys key key_len ref rows Extra 18281 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_2 146 NULL # Using where; Using index for group-by 1829explain select distinct b from t2 where (a2 >= 'b') and (b = 'a'); 1830id select_type table type possible_keys key key_len ref rows Extra 18311 SIMPLE t2 index NULL idx_t2_2 146 NULL 164 Using where; Using index 1832explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b'; 1833id select_type table type possible_keys key key_len ref rows Extra 18341 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 1835explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e'; 1836id select_type table type possible_keys key key_len ref rows Extra 18371 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 1838select distinct a1,a2,b from t1; 1839a1 a2 b 1840a a a 1841a a b 1842a b a 1843a b b 1844b a a 1845b a b 1846b b a 1847b b b 1848c a a 1849c a b 1850c b a 1851c b b 1852d a a 1853d a b 1854d b a 1855d b b 1856select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); 1857a1 a2 b 1858a b a 1859b b a 1860c b a 1861d b a 1862select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 1863a1 a2 b c 1864a b a i121 1865select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 1866a1 a2 b 1867select distinct b from t1 where (a2 >= 'b') and (b = 'a'); 1868b 1869a 1870select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b'; 1871a1 1872a 1873d 1874select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e'; 1875a1 1876select distinct a1,a2,b from t2; 1877a1 a2 b 1878a a NULL 1879a a a 1880a a b 1881a b a 1882a b b 1883b a a 1884b a b 1885b b a 1886b b b 1887c a NULL 1888c a a 1889c a b 1890c b a 1891c b b 1892d a a 1893d a b 1894d b a 1895d b b 1896e a a 1897e a b 1898select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); 1899a1 a2 b 1900a b a 1901b b a 1902c b a 1903d b a 1904select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 1905a1 a2 b c 1906a b a i121 1907select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 1908a1 a2 b 1909select distinct b from t2 where (a2 >= 'b') and (b = 'a'); 1910b 1911a 1912select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b'; 1913a1 1914a 1915d 1916select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e'; 1917a1 1918select distinct t_00.a1 1919from t1 t_00 1920where exists ( select * from t2 where a1 = t_00.a1 ); 1921a1 1922a 1923b 1924c 1925d 1926select distinct a1,a1 from t1; 1927a1 a1 1928a a 1929b b 1930c c 1931d d 1932select distinct a2,a1,a2,a1 from t1; 1933a2 a1 a2 a1 1934a a a a 1935b a b a 1936a b a b 1937b b b b 1938a c a c 1939b c b c 1940a d a d 1941b d b d 1942select distinct t1.a1,t2.a1 from t1,t2; 1943a1 a1 1944a a 1945b a 1946c a 1947d a 1948a b 1949b b 1950c b 1951d b 1952a c 1953b c 1954c c 1955d c 1956a d 1957b d 1958c d 1959d d 1960a e 1961b e 1962c e 1963d e 1964explain select distinct a1,a2,b from t1; 1965id select_type table type possible_keys key key_len ref rows Extra 19661 SIMPLE t1 range NULL idx_t1_2 147 NULL 17 Using index for group-by 1967explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1968id select_type table type possible_keys key key_len ref rows Extra 19691 SIMPLE t1 range NULL idx_t1_2 147 NULL 17 Using where; Using index for group-by 1970explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1971id select_type table type possible_keys key key_len ref rows Extra 19721 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by 1973explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1974id select_type table type possible_keys key key_len ref rows Extra 19751 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 17 Using where; Using index for group-by 1976explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1977id select_type table type possible_keys key key_len ref rows Extra 19781 SIMPLE t1 range NULL idx_t1_2 147 NULL 17 Using where; Using index for group-by; Using temporary; Using filesort 1979explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1; 1980id select_type table type possible_keys key key_len ref rows Extra 19811 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 1982explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1; 1983id select_type table type possible_keys key key_len ref rows Extra 19841 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 1985explain select distinct a1,a2,b from t2; 1986id select_type table type possible_keys key key_len ref rows Extra 19871 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using index for group-by 1988explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1989id select_type table type possible_keys key key_len ref rows Extra 19901 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using where; Using index for group-by 1991explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1992id select_type table type possible_keys key key_len ref rows Extra 19931 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1994explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1995id select_type table type possible_keys key key_len ref rows Extra 19961 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_2 146 NULL # Using where; Using index for group-by 1997explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1998id select_type table type possible_keys key key_len ref rows Extra 19991 SIMPLE t2 range NULL idx_t2_2 146 NULL # Using where; Using index for group-by; Using temporary; Using filesort 2000explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1; 2001id select_type table type possible_keys key key_len ref rows Extra 20021 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_2 129 NULL # Using where; Using index for group-by 2003explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1; 2004id select_type table type possible_keys key key_len ref rows Extra 20051 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_2 129 NULL # Using where; Using index for group-by 2006select distinct a1,a2,b from t1; 2007a1 a2 b 2008a a a 2009a a b 2010a b a 2011a b b 2012b a a 2013b a b 2014b b a 2015b b b 2016c a a 2017c a b 2018c b a 2019c b b 2020d a a 2021d a b 2022d b a 2023d b b 2024select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 2025a1 a2 b 2026a b a 2027b b a 2028c b a 2029d b a 2030select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 2031a1 a2 b c 2032a b a i121 2033select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 2034a1 a2 b 2035select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 2036b 2037a 2038select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1; 2039a1 2040a 2041d 2042select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1; 2043a1 2044select distinct a1,a2,b from t2; 2045a1 a2 b 2046a a NULL 2047a a a 2048a a b 2049a b a 2050a b b 2051b a a 2052b a b 2053b b a 2054b b b 2055c a NULL 2056c a a 2057c a b 2058c b a 2059c b b 2060d a a 2061d a b 2062d b a 2063d b b 2064e a a 2065e a b 2066select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 2067a1 a2 b 2068a b a 2069b b a 2070c b a 2071d b a 2072select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 2073a1 a2 b c 2074a b a i121 2075select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 2076a1 a2 b 2077select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 2078b 2079a 2080select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1; 2081a1 2082a 2083d 2084select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1; 2085a1 2086explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); 2087id select_type table type possible_keys key key_len ref rows Extra 20881 SIMPLE t1 range NULL idx_t1_2 147 NULL 17 Using where; Using index for group-by 2089explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 2090id select_type table type possible_keys key key_len ref rows Extra 20911 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 Using where; Using index for group-by 2092explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 2093id select_type table type possible_keys key key_len ref rows filtered Extra 20941 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 17 100.00 Using where; Using index for group-by 2095Warnings: 2096Note 1003 select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where `test`.`t1`.`b` = 'c' and `test`.`t1`.`a1` > 'a' and `test`.`t1`.`a2` > 'a' 2097explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); 2098id select_type table type possible_keys key key_len ref rows Extra 20991 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index 2100explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); 2101id select_type table type possible_keys key key_len ref rows filtered Extra 21021 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 17 100.00 Using where; Using index for group-by 2103Warnings: 2104Note 1003 select 98 + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `98 + count(distinct a1,a2,b)` from `test`.`t1` where `test`.`t1`.`a1` > 'a' and `test`.`t1`.`a2` > 'a' 2105select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); 2106count(distinct a1,a2,b) 21074 2108select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 2109count(distinct a1,a2,b,c) 21101 2111select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 2112count(distinct a1,a2,b) 21130 2114select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); 2115count(distinct b) 21161 2117select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); 211898 + count(distinct a1,a2,b) 2119104 2120explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; 2121id select_type table type possible_keys key key_len ref rows Extra 21221 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 2123explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; 2124id select_type table type possible_keys key key_len ref rows Extra 21251 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 2126explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; 2127id select_type table type possible_keys key key_len ref rows Extra 21281 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 2129explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 2130id select_type table type possible_keys key key_len ref rows Extra 21311 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 17 Using where; Using index for group-by 2132explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; 2133id select_type table type possible_keys key key_len ref rows Extra 21341 SIMPLE t1 range NULL idx_t1_2 147 NULL 9 Using index for group-by 2135select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; 2136a1 a2 b concat(min(c), max(c)) 2137a a a a111d111 2138a a b e112h112 2139a b a i121l121 2140a b b m122p122 2141b a a a211d211 2142b a b e212h212 2143b b a i221l221 2144b b b m222p222 2145c a a a311d311 2146c a b e312h312 2147c b a i321l321 2148c b b m322p322 2149select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; 2150concat(a1,min(c)) b 2151aa111 a 2152ae112 b 2153ai121 a 2154am122 b 2155ba211 a 2156be212 b 2157bi221 a 2158bm222 b 2159ca311 a 2160ce312 b 2161ci321 a 2162cm322 b 2163select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; 2164concat(a1,min(c)) b max(c) 2165aa111 a d111 2166ae112 b h112 2167ai121 a l121 2168am122 b p122 2169ba211 a d211 2170be212 b h212 2171bi221 a l221 2172bm222 b p222 2173ca311 a d311 2174ce312 b h312 2175ci321 a l321 2176cm322 b p322 2177select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 2178concat(a1,a2) b min(c) max(c) 2179aa a a111 d111 2180aa b e112 h112 2181ab a i121 l121 2182ab b m122 p122 2183ba a a211 d211 2184ba b e212 h212 2185bb a i221 l221 2186bb b m222 p222 2187ca a a311 d311 2188ca b e312 h312 2189cb a i321 l321 2190cb b m322 p322 2191select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; 2192concat(ord(min(b)),ord(max(b))) min(b) max(b) 21939798 a b 21949798 a b 21959798 a b 21969798 a b 21979798 a b 21989798 a b 21999798 a b 22009798 a b 2201explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b; 2202id select_type table type possible_keys key key_len ref rows Extra 22031 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 2204explain select a1,a2,b,d from t1 group by a1,a2,b; 2205id select_type table type possible_keys key key_len ref rows Extra 22061 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 2207explain extended select a1,a2,min(b),max(b) from t1 2208where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2; 2209id select_type table type possible_keys key key_len ref rows filtered Extra 22101 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 128 99.22 Using where; Using index 2211Warnings: 2212Note 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` 2213explain extended select a1,a2,b,min(c),max(c) from t1 2214where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b; 2215id select_type table type possible_keys key key_len ref rows filtered Extra 22161 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 130 NULL 128 75.00 Using where 2217Warnings: 2218Note 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` 2219explain extended select a1,a2,b,c from t1 2220where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c; 2221id select_type table type possible_keys key key_len ref rows filtered Extra 22221 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 128 75.00 Using where 2223Warnings: 2224Note 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` 2225explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; 2226id select_type table type possible_keys key key_len ref rows Extra 22271 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2228explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b < 'b') group by a1; 2229id select_type table type possible_keys key key_len ref rows Extra 22301 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2231explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b') group by a1; 2232id select_type table type possible_keys key key_len ref rows Extra 22331 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2234explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b' and b >= 'a') group by a1; 2235id select_type table type possible_keys key key_len ref rows Extra 22361 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2237explain 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; 2238id select_type table type possible_keys key key_len ref rows filtered Extra 22391 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 128 99.22 Using where; Using index 2240Warnings: 2241Note 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` 2242explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; 2243id select_type table type possible_keys key key_len ref rows Extra 22441 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2245select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; 2246a1 a2 min(b) c 2247a a a a111 2248explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1; 2249id select_type table type possible_keys key key_len ref rows Extra 22501 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2251explain select a1,a2,b,min(c),max(c) from t2 2252where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b; 2253id select_type table type possible_keys key key_len ref rows Extra 22541 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2255explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c; 2256id select_type table type possible_keys key key_len ref rows Extra 22571 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 2258explain select a1,a2,count(a2) from t1 group by a1,a2,b; 2259id select_type table type possible_keys key key_len ref rows Extra 22601 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index 2261explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; 2262id select_type table type possible_keys key key_len ref rows filtered Extra 22631 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 128 100.00 Using where; Using index 2264Warnings: 2265Note 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` 2266explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; 2267id select_type table type possible_keys key key_len ref rows filtered Extra 22681 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 128 100.00 Using where; Using index 2269Warnings: 2270Note 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` 2271explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; 2272id select_type table type possible_keys key key_len ref rows Extra 22731 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2274explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; 2275id select_type table type possible_keys key key_len ref rows Extra 22761 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2277explain select distinct(a1) from t1 where ord(a2) = 98; 2278id select_type table type possible_keys key key_len ref rows Extra 22791 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index 2280select distinct(a1) from t1 where ord(a2) = 98; 2281a1 2282a 2283b 2284c 2285d 2286explain select a1 from t1 where a2 = 'b' group by a1; 2287id select_type table type possible_keys key key_len ref rows Extra 22881 SIMPLE t1 range NULL idx_t1_2 130 NULL 5 Using where; Using index for group-by 2289select a1 from t1 where a2 = 'b' group by a1; 2290a1 2291a 2292b 2293c 2294d 2295explain select distinct a1 from t1 where a2 = 'b'; 2296id select_type table type possible_keys key key_len ref rows Extra 22971 SIMPLE t1 range NULL idx_t1_2 130 NULL 5 Using where; Using index for group-by 2298select distinct a1 from t1 where a2 = 'b'; 2299a1 2300a 2301b 2302c 2303d 2304drop table t1,t2,t3; 2305create table t1 (c1 int not null,c2 int not null, primary key(c1,c2)) engine=RocksDB; 2306insert into t1 (c1,c2) values 2307(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9); 2308select distinct c1, c2 from t1 order by c2; 2309c1 c2 231010 1 231110 2 231210 3 231320 4 231420 5 231520 6 231630 7 231730 8 231830 9 2319select c1,min(c2) as c2 from t1 group by c1 order by c2; 2320c1 c2 232110 1 232220 4 232330 7 2324select c1,c2 from t1 group by c1,c2 order by c2; 2325c1 c2 232610 1 232710 2 232810 3 232920 4 233020 5 233120 6 233230 7 233330 8 233430 9 2335drop table t1; 2336CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b)) engine=RocksDB; 2337INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4); 2338OPTIMIZE TABLE t1; 2339Table Op Msg_type Msg_text 2340test.t1 optimize status OK 2341SELECT a FROM t1 WHERE a='AA' GROUP BY a; 2342a 2343AA 2344SELECT a FROM t1 WHERE a='BB' GROUP BY a; 2345a 2346BB 2347EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a; 2348id select_type table type possible_keys key key_len ref rows Extra 23491 SIMPLE t1 ref PRIMARY PRIMARY 7 const 1000 Using where; Using index 2350EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a; 2351id select_type table type possible_keys key key_len ref rows Extra 23521 SIMPLE t1 ref PRIMARY PRIMARY 7 const 1000 Using where; Using index 2353SELECT DISTINCT a FROM t1 WHERE a='BB'; 2354a 2355BB 2356SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%'; 2357a 2358BB 2359SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a; 2360a 2361BB 2362DROP TABLE t1; 2363CREATE TABLE t1 ( 2364a int(11) NOT NULL DEFAULT '0', 2365b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '', 2366PRIMARY KEY (a,b) 2367) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 2368CREATE PROCEDURE a(x INT) 2369BEGIN 2370DECLARE rnd INT; 2371DECLARE cnt INT; 2372WHILE x > 0 DO 2373SET rnd= x % 100; 2374SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd); 2375INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR)); 2376SET x= x - 1; 2377END WHILE; 2378END| 2379CALL a(1000); 2380SELECT a FROM t1 WHERE a=0; 2381a 23820 23830 23840 23850 23860 23870 23880 23890 23900 23910 2392SELECT DISTINCT a FROM t1 WHERE a=0; 2393a 23940 2395SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0; 2396COUNT(DISTINCT a) 23971 2398DROP TABLE t1; 2399DROP PROCEDURE a; 2400CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)) engine=RocksDB; 2401INSERT INTO t1 (a) VALUES 2402(''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'), 2403('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'), 2404('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); 2405EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; 2406id select_type table type possible_keys key key_len ref rows Extra 24071 SIMPLE t1 index NULL PRIMARY 66 NULL 1000 Using index 2408SELECT DISTINCT a,a FROM t1 ORDER BY a; 2409a a 2410 2411CENTRAL CENTRAL 2412EASTERN EASTERN 2413GREATER LONDON GREATER LONDON 2414NORTH CENTRAL NORTH CENTRAL 2415NORTH EAST NORTH EAST 2416NORTH WEST NORTH WEST 2417SCOTLAND SCOTLAND 2418SOUTH EAST SOUTH EAST 2419SOUTH WEST SOUTH WEST 2420WESTERN WESTERN 2421DROP TABLE t1; 2422CREATE TABLE t1 (id1 INT, id2 INT) engine=RocksDB; 2423CREATE TABLE t2 (id2 INT, id3 INT, id5 INT) engine=RocksDB; 2424CREATE TABLE t3 (id3 INT, id4 INT) engine=RocksDB; 2425CREATE TABLE t4 (id4 INT) engine=RocksDB; 2426CREATE TABLE t5 (id5 INT, id6 INT) engine=RocksDB; 2427CREATE TABLE t6 (id6 INT) engine=RocksDB; 2428INSERT INTO t1 VALUES(1,1); 2429INSERT INTO t2 VALUES(1,1,1); 2430INSERT INTO t3 VALUES(1,1); 2431INSERT INTO t4 VALUES(1); 2432INSERT INTO t5 VALUES(1,1); 2433INSERT INTO t6 VALUES(1); 2434SELECT * FROM 2435t1 2436NATURAL JOIN 2437(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) 2438ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); 2439id2 id1 id3 id5 id4 id3 id6 id5 24401 1 1 1 1 1 1 1 2441SELECT * FROM 2442t1 2443NATURAL JOIN 2444(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2 2445ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); 2446id2 id1 id4 id3 id6 id5 id3 id5 24471 1 1 1 1 1 1 1 2448SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2); 2449id2 id1 id3 id4 id6 id5 id3 id5 24501 1 1 1 1 1 1 1 2451SELECT * FROM 2452(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) 2453ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)) 2454NATURAL JOIN 2455t1; 2456id2 id3 id5 id4 id3 id6 id5 id1 24571 1 1 1 1 1 1 1 2458SELECT * FROM 2459(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6))) 2460NATURAL JOIN 2461t1; 2462id2 id3 id5 id4 id3 id6 id5 id1 24631 1 1 1 1 1 1 1 2464DROP TABLE t1,t2,t3,t4,t5,t6; 2465CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) engine=RocksDB; 2466INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); 2467ANALYZE TABLE t1; 2468Table Op Msg_type Msg_text 2469test.t1 analyze status Engine-independent statistics collected 2470test.t1 analyze status OK 2471explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; 2472id select_type table type possible_keys key key_len ref rows Extra 24731 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 2 Using where; Using index for group-by 2474SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; 2475MAX(b) a 24761 1 2477SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a; 2478MIN(b) a 24792 1 2480CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)) engine=RocksDB; 2481INSERT INTO t2 SELECT a,b,b FROM t1; 2482ANALYZE TABLE t2; 2483Table Op Msg_type Msg_text 2484test.t2 analyze status Engine-independent statistics collected 2485test.t2 analyze status OK 2486explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; 2487id select_type table type possible_keys key key_len ref rows Extra 24881 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 2 Using where; Using index for group-by 2489SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; 2490MIN(c) 24912 2492DROP TABLE t1,t2; 2493CREATE TABLE t1 (a INT, b INT, INDEX (a,b)) engine=RocksDB; 2494INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), 2495(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); 2496ANALYZE TABLE t1; 2497Table Op Msg_type Msg_text 2498test.t1 analyze status Engine-independent statistics collected 2499test.t1 analyze status OK 2500EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; 2501id select_type table type possible_keys key key_len ref rows Extra 25021 SIMPLE t1 range NULL a 5 NULL 6 Using index for group-by 2503FLUSH STATUS; 2504SELECT max(b), a FROM t1 GROUP BY a; 2505max(b) a 25065 1 25073 2 25081 3 25096 4 2510SHOW STATUS LIKE 'handler_read__e%'; 2511Variable_name Value 2512Handler_read_key 8 2513Handler_read_next 0 2514Handler_read_retry 0 2515EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; 2516id select_type table type possible_keys key key_len ref rows Extra 25171 SIMPLE t1 range NULL a 5 NULL 6 Using index for group-by 2518FLUSH STATUS; 2519CREATE TABLE t2 engine=RocksDB SELECT max(b), a FROM t1 GROUP BY a; 2520SHOW STATUS LIKE 'handler_read__e%'; 2521Variable_name Value 2522Handler_read_key 8 2523Handler_read_next 0 2524Handler_read_retry 0 2525FLUSH STATUS; 2526SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; 2527max(b) a 25285 1 25293 2 25301 3 25316 4 2532SHOW STATUS LIKE 'handler_read__e%'; 2533Variable_name Value 2534Handler_read_key 8 2535Handler_read_next 0 2536Handler_read_retry 0 2537FLUSH STATUS; 2538(SELECT max(b), a FROM t1 GROUP BY a) UNION 2539(SELECT max(b), a FROM t1 GROUP BY a); 2540max(b) a 25415 1 25423 2 25431 3 25446 4 2545SHOW STATUS LIKE 'handler_read__e%'; 2546Variable_name Value 2547Handler_read_key 16 2548Handler_read_next 0 2549Handler_read_retry 0 2550EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION 2551(SELECT max(b), a FROM t1 GROUP BY a); 2552id select_type table type possible_keys key key_len ref rows Extra 25531 PRIMARY t1 range NULL a 5 NULL 6 Using index for group-by 25542 UNION t1 range NULL a 5 NULL 6 Using index for group-by 2555NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 2556EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x 2557FROM t1 AS t1_outer; 2558id select_type table type possible_keys key key_len ref rows Extra 25591 PRIMARY t1_outer index NULL a 10 NULL 15 Using index 25602 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by 2561EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS 2562(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 2563id select_type table type possible_keys key key_len ref rows Extra 25641 PRIMARY t1_outer index NULL a 10 NULL 15 Using index 25652 SUBQUERY t1 index NULL a 10 NULL 15 Using index 2566EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 2567(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; 2568id select_type table type possible_keys key key_len ref rows Extra 25691 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 25702 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by 2571EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 2572a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 2573id select_type table type possible_keys key key_len ref rows Extra 25741 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6 25751 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 3 Using index 25762 MATERIALIZED t1 range a a 5 NULL 6 Using where; Using index for group-by 2577EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 2578a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 2579id select_type table type possible_keys key key_len ref rows Extra 25801 PRIMARY t1_outer range NULL a 5 NULL 6 Using index for group-by 25812 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by 2582EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 2583ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 2584AND t1_outer1.b = t1_outer2.b; 2585id select_type table type possible_keys key key_len ref rows Extra 25861 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index 25871 PRIMARY t1_outer1 ref a a 10 const,test.t1_outer2.b 1 Using where; Using index 25882 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by 2589EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x 2590FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; 2591id select_type table type possible_keys key key_len ref rows Extra 25921 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index 25932 SUBQUERY t1_outer index NULL a 10 NULL 15 Using index 25943 SUBQUERY t1 range a a 5 NULL 6 Using where; Using index for group-by 2595CREATE TABLE t3 LIKE t1; 2596FLUSH STATUS; 2597INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; 2598SHOW STATUS LIKE 'handler_read__e%'; 2599Variable_name Value 2600Handler_read_key 13 2601Handler_read_next 0 2602Handler_read_retry 0 2603DELETE FROM t3; 2604FLUSH STATUS; 2605INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) 2606FROM t1 LIMIT 1; 2607SHOW STATUS LIKE 'handler_read__e%'; 2608Variable_name Value 2609Handler_read_key 8 2610Handler_read_next 0 2611Handler_read_retry 0 2612FLUSH STATUS; 2613DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; 2614SHOW STATUS LIKE 'handler_read__e%'; 2615Variable_name Value 2616Handler_read_key 8 2617Handler_read_next 0 2618Handler_read_retry 0 2619FLUSH STATUS; 2620DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 2621FROM t1) > 10000; 2622ERROR 21000: Subquery returns more than 1 row 2623SHOW STATUS LIKE 'handler_read__e%'; 2624Variable_name Value 2625Handler_read_key 8 2626Handler_read_next 1 2627Handler_read_retry 0 2628DROP TABLE t1,t2,t3; 2629CREATE TABLE t1 (a int, INDEX idx(a)) engine=RocksDB; 2630INSERT INTO t1 VALUES 2631(4), (2), (1), (2), (4), (2), (1), (4), 2632(4), (2), (1), (2), (2), (4), (1), (4); 2633ANALYZE TABLE t1; 2634Table Op Msg_type Msg_text 2635test.t1 analyze status Engine-independent statistics collected 2636test.t1 analyze status OK 2637EXPLAIN SELECT DISTINCT(a) FROM t1; 2638id select_type table type possible_keys key key_len ref rows Extra 26391 SIMPLE t1 range NULL idx 5 NULL 4 Using index for group-by 2640SELECT DISTINCT(a) FROM t1; 2641a 26421 26432 26444 2645EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; 2646id select_type table type possible_keys key key_len ref rows Extra 26471 SIMPLE t1 range NULL idx 5 NULL 4 Using index for group-by 2648SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; 2649a 26501 26512 26524 2653DROP TABLE t1; 2654CREATE TABLE t1 (a INT, b INT) engine=RocksDB; 2655INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3); 2656INSERT INTO t1 SELECT a + 1, b FROM t1; 2657INSERT INTO t1 SELECT a + 2, b FROM t1; 2658ANALYZE TABLE t1; 2659Table Op Msg_type Msg_text 2660test.t1 analyze status Engine-independent statistics collected 2661test.t1 analyze status OK 2662EXPLAIN 2663SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 2664id select_type table type possible_keys key key_len ref rows Extra 26651 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort 2666SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 2667a MIN(b) MAX(b) 26684 1 3 26693 1 3 26702 1 3 26711 1 3 2672CREATE INDEX break_it ON t1 (a, b); 2673EXPLAIN 2674SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; 2675id select_type table type possible_keys key key_len ref rows Extra 26761 SIMPLE t1 range NULL break_it 10 NULL 4 Using index for group-by 2677SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; 2678a MIN(b) MAX(b) 26791 1 3 26802 1 3 26813 1 3 26824 1 3 2683EXPLAIN 2684SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 2685id select_type table type possible_keys key key_len ref rows Extra 26861 SIMPLE t1 range NULL break_it 10 NULL 4 Using index for group-by; Using temporary; Using filesort 2687SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 2688a MIN(b) MAX(b) 26894 1 3 26903 1 3 26912 1 3 26921 1 3 2693EXPLAIN 2694SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; 2695id select_type table type possible_keys key key_len ref rows Extra 26961 SIMPLE t1 index NULL break_it 10 NULL 12 Using index 2697SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; 2698a MIN(b) MAX(b) AVG(b) 26994 1 3 2.0000 27003 1 3 2.0000 27012 1 3 2.0000 27021 1 3 2.0000 2703DROP TABLE t1; 2704create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM; 2705insert into t1 (a,b) values 2706(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6), 2707(0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13), 2708(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6), 2709(1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13), 2710(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6), 2711(2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13), 2712(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6), 2713(3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13); 2714insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a; 2715ANALYZE TABLE t1; 2716Table Op Msg_type Msg_text 2717test.t1 analyze status Engine-independent statistics collected 2718test.t1 analyze status OK 2719select * from t1; 2720a b 27210 0 27220 1 27230 2 27240 3 27250 4 27260 5 27270 6 27280 7 27290 8 27300 9 27310 10 27320 11 27330 12 27340 13 27350 14 27361 0 27371 1 27381 2 27391 3 27401 4 27411 5 27421 6 27431 7 27441 8 27451 9 27461 10 27471 11 27481 12 27491 13 27502 0 27512 1 27522 2 27532 3 27542 4 27552 5 27562 6 27572 7 27582 8 27592 9 27602 10 27612 11 27622 12 27632 13 27643 0 27653 1 27663 2 27673 3 27683 4 27693 5 27703 6 27713 7 27723 8 27733 9 27743 10 27753 11 27763 12 27773 13 2778explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a; 2779id select_type table type possible_keys key key_len ref rows filtered Extra 27801 SIMPLE t1 ref PRIMARY,index PRIMARY 4 const 15 6.67 Using index; Using temporary 2781Warnings: 2782Note 1003 select sql_buffer_result `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) + 1 AS `max(b)+1` from `test`.`t1` where `test`.`t1`.`a` = 0 group by `test`.`t1`.`a` 2783drop table t1; 2784CREATE TABLE t1 (a int, b int, c int, d int, 2785KEY foo (c,d,a,b), KEY bar (c,a,b,d)) engine=RocksDB; 2786INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4); 2787INSERT INTO t1 SELECT * FROM t1; 2788INSERT INTO t1 SELECT * FROM t1; 2789INSERT INTO t1 SELECT a,b,c+1,d FROM t1; 2790ANALYZE TABLE t1; 2791Table Op Msg_type Msg_text 2792test.t1 analyze status Engine-independent statistics collected 2793test.t1 analyze status OK 2794EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4; 2795id select_type table type possible_keys key key_len ref rows Extra 27961 SIMPLE t1 range NULL foo 10 NULL 3 Using where; Using index for group-by 2797SELECT DISTINCT c FROM t1 WHERE d=4; 2798c 27991 28002 2801DROP TABLE t1; 2802# 2803# Bug #45386: Wrong query result with MIN function in field list, 2804# WHERE and GROUP BY clause 2805# 2806CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB; 2807INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); 2808INSERT INTO t SELECT * FROM t; 2809INSERT INTO t SELECT * FROM t; 2810ANALYZE TABLE t; 2811Table Op Msg_type Msg_text 2812test.t analyze status Engine-independent statistics collected 2813test.t analyze status OK 2814# test MIN 2815#should use range with index for group by 2816EXPLAIN 2817SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; 2818id select_type table type possible_keys key key_len ref rows Extra 28191 SIMPLE t range NULL a 10 NULL 2 Using where; Using index for group-by 2820#should return 1 row 2821SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; 2822a MIN(b) 28232 1 2824# test MAX 2825#should use range with index for group by 2826EXPLAIN 2827SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; 2828id select_type table type possible_keys key key_len ref rows Extra 28291 SIMPLE t range NULL a 10 NULL 2 Using where; Using index for group-by 2830#should return 1 row 2831SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; 2832a MAX(b) 28332 0 2834# test 3 ranges and use the middle one 2835INSERT INTO t SELECT a, 2 FROM t; 2836#should use range with index for group by 2837EXPLAIN 2838SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; 2839id select_type table type possible_keys key key_len ref rows Extra 28401 SIMPLE t range NULL a 10 NULL 2 Using where; Using index for group-by 2841#should return 1 row 2842SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; 2843a MAX(b) 28442 1 2845DROP TABLE t; 2846# 2847# Bug #48472: Loose index scan inappropriately chosen for some WHERE 2848# conditions 2849# 2850CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB; 2851INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); 2852INSERT INTO t SELECT * FROM t; 2853ANALYZE TABLE t; 2854Table Op Msg_type Msg_text 2855test.t analyze status Engine-independent statistics collected 2856test.t analyze status OK 2857SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a; 2858a MAX(b) 28592 0 2860DROP TABLE t; 2861End of 5.0 tests 2862# 2863# Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in 2864# server crash 2865# 2866CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB; 2867INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); 2868INSERT INTO t SELECT * FROM t; 2869SELECT a, MAX(b) FROM t WHERE b GROUP BY a; 2870a MAX(b) 28712 1 2872DROP TABLE t; 2873CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b)) engine=RocksDB; 2874INSERT INTO t1 VALUES(1,1),(2,1); 2875ANALYZE TABLE t1; 2876Table Op Msg_type Msg_text 2877test.t1 analyze status Engine-independent statistics collected 2878test.t1 analyze status OK 2879SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b; 2880c b 28811 1 2882SELECT a FROM t1 WHERE b=1; 2883a 28841 28852 2886DROP TABLE t1; 2887# 2888# Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column 2889# for NULL 2890# 2891## Test for NULLs allowed 2892CREATE TABLE t1 ( a INT, KEY (a) ) engine=RocksDB; 2893INSERT INTO t1 VALUES (1), (2), (3); 2894ANALYZE TABLE t1; 2895Table Op Msg_type Msg_text 2896test.t1 analyze status Engine-independent statistics collected 2897test.t1 analyze status OK 2898EXPLAIN 2899SELECT MIN( a ) FROM t1 WHERE a = NULL; 2900id select_type table type possible_keys key key_len ref rows Extra 2901x x x x x x x x x Impossible WHERE noticed after reading const tables 2902SELECT MIN( a ) FROM t1 WHERE a = NULL; 2903MIN( a ) 2904NULL 2905EXPLAIN 2906SELECT MIN( a ) FROM t1 WHERE a <> NULL; 2907id select_type table type possible_keys key key_len ref rows Extra 2908x x x x x x x x x Impossible WHERE noticed after reading const tables 2909SELECT MIN( a ) FROM t1 WHERE a <> NULL; 2910MIN( a ) 2911NULL 2912EXPLAIN 2913SELECT MIN( a ) FROM t1 WHERE a > NULL; 2914id select_type table type possible_keys key key_len ref rows Extra 2915x x x x x x x x x Impossible WHERE noticed after reading const tables 2916SELECT MIN( a ) FROM t1 WHERE a > NULL; 2917MIN( a ) 2918NULL 2919EXPLAIN 2920SELECT MIN( a ) FROM t1 WHERE a < NULL; 2921id select_type table type possible_keys key key_len ref rows Extra 2922x x x x x x x x x Impossible WHERE noticed after reading const tables 2923SELECT MIN( a ) FROM t1 WHERE a < NULL; 2924MIN( a ) 2925NULL 2926EXPLAIN 2927SELECT MIN( a ) FROM t1 WHERE a <=> NULL; 2928id select_type table type possible_keys key key_len ref rows Extra 2929x x x x x x x x x No matching min/max row 2930SELECT MIN( a ) FROM t1 WHERE a <=> NULL; 2931MIN( a ) 2932NULL 2933EXPLAIN 2934SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 2935id select_type table type possible_keys key key_len ref rows Extra 2936x x x x x x x x x Impossible WHERE noticed after reading const tables 2937SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 2938MIN( a ) 2939NULL 2940EXPLAIN 2941SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 2942id select_type table type possible_keys key key_len ref rows Extra 2943x x x x x x x x x Impossible WHERE noticed after reading const tables 2944SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 2945MIN( a ) 2946NULL 2947EXPLAIN 2948SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 2949id select_type table type possible_keys key key_len ref rows Extra 2950x x x x x x x x x Impossible WHERE noticed after reading const tables 2951SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 2952MIN( a ) 2953NULL 2954EXPLAIN 2955SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 2956id select_type table type possible_keys key key_len ref rows Extra 2957x x x x x x x x x Impossible WHERE noticed after reading const tables 2958x x x x x x x x x Using where; Using index 2959SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 2960MIN( a ) 2961NULL 2962EXPLAIN 2963SELECT MIN( a ) FROM t1 WHERE a IS NULL; 2964id select_type table type possible_keys key key_len ref rows Extra 2965x x x x x x x x x No matching min/max row 2966SELECT MIN( a ) FROM t1 WHERE a IS NULL; 2967MIN( a ) 2968NULL 2969INSERT INTO t1 VALUES (NULL), (NULL); 2970ANALYZE TABLE t1; 2971Table Op Msg_type Msg_text 2972test.t1 analyze status Engine-independent statistics collected 2973test.t1 analyze status OK 2974EXPLAIN 2975SELECT MIN( a ) FROM t1 WHERE a = NULL; 2976id select_type table type possible_keys key key_len ref rows Extra 2977x x x x x x x x x Impossible WHERE noticed after reading const tables 2978SELECT MIN( a ) FROM t1 WHERE a = NULL; 2979MIN( a ) 2980NULL 2981EXPLAIN 2982SELECT MIN( a ) FROM t1 WHERE a <> NULL; 2983id select_type table type possible_keys key key_len ref rows Extra 2984x x x x x x x x x Impossible WHERE noticed after reading const tables 2985SELECT MIN( a ) FROM t1 WHERE a <> NULL; 2986MIN( a ) 2987NULL 2988EXPLAIN 2989SELECT MIN( a ) FROM t1 WHERE a > NULL; 2990id select_type table type possible_keys key key_len ref rows Extra 2991x x x x x x x x x Impossible WHERE noticed after reading const tables 2992SELECT MIN( a ) FROM t1 WHERE a > NULL; 2993MIN( a ) 2994NULL 2995EXPLAIN 2996SELECT MIN( a ) FROM t1 WHERE a < NULL; 2997id select_type table type possible_keys key key_len ref rows Extra 2998x x x x x x x x x Impossible WHERE noticed after reading const tables 2999SELECT MIN( a ) FROM t1 WHERE a < NULL; 3000MIN( a ) 3001NULL 3002EXPLAIN 3003SELECT MIN( a ) FROM t1 WHERE a <=> NULL; 3004id select_type table type possible_keys key key_len ref rows Extra 3005x x x x x x x x x Select tables optimized away 3006SELECT MIN( a ) FROM t1 WHERE a <=> NULL; 3007MIN( a ) 3008NULL 3009EXPLAIN 3010SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 3011id select_type table type possible_keys key key_len ref rows Extra 3012x x x x x x x x x Impossible WHERE noticed after reading const tables 3013SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 3014MIN( a ) 3015NULL 3016EXPLAIN 3017SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 3018id select_type table type possible_keys key key_len ref rows Extra 3019x x x x x x x x x Impossible WHERE noticed after reading const tables 3020SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 3021MIN( a ) 3022NULL 3023EXPLAIN 3024SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 3025id select_type table type possible_keys key key_len ref rows Extra 3026x x x x x x x x x Impossible WHERE noticed after reading const tables 3027SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 3028MIN( a ) 3029NULL 3030EXPLAIN 3031SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 3032id select_type table type possible_keys key key_len ref rows Extra 3033x x x x x x x x x Impossible WHERE noticed after reading const tables 3034x x x x x x x x x Using where; Using index 3035SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 3036MIN( a ) 3037NULL 3038EXPLAIN 3039SELECT MIN( a ) FROM t1 WHERE a IS NULL; 3040id select_type table type possible_keys key key_len ref rows Extra 3041x x x x x x x x x Select tables optimized away 3042SELECT MIN( a ) FROM t1 WHERE a IS NULL; 3043MIN( a ) 3044NULL 3045DROP TABLE t1; 3046## Test for NOT NULLs 3047CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY) engine=RocksDB; 3048INSERT INTO t1 VALUES (1), (2), (3); 3049ANALYZE TABLE t1; 3050Table Op Msg_type Msg_text 3051test.t1 analyze status Engine-independent statistics collected 3052test.t1 analyze status OK 3053# 3054# NULL-safe operator test disabled for non-NULL indexed columns. 3055# 3056# See bugs 3057# 3058# - Bug#52173: Reading NULL value from non-NULL index gives 3059# wrong result in embedded server 3060# 3061# - Bug#52174: Sometimes wrong plan when reading a MAX value from 3062# non-NULL index 3063# 3064EXPLAIN 3065SELECT MIN( a ) FROM t1 WHERE a = NULL; 3066id select_type table type possible_keys key key_len ref rows Extra 3067x x x x x x x x x Impossible WHERE noticed after reading const tables 3068SELECT MIN( a ) FROM t1 WHERE a = NULL; 3069MIN( a ) 3070NULL 3071EXPLAIN 3072SELECT MIN( a ) FROM t1 WHERE a <> NULL; 3073id select_type table type possible_keys key key_len ref rows Extra 3074x x x x x x x x x Impossible WHERE noticed after reading const tables 3075SELECT MIN( a ) FROM t1 WHERE a <> NULL; 3076MIN( a ) 3077NULL 3078EXPLAIN 3079SELECT MIN( a ) FROM t1 WHERE a > NULL; 3080id select_type table type possible_keys key key_len ref rows Extra 3081x x x x x x x x x Impossible WHERE noticed after reading const tables 3082SELECT MIN( a ) FROM t1 WHERE a > NULL; 3083MIN( a ) 3084NULL 3085EXPLAIN 3086SELECT MIN( a ) FROM t1 WHERE a < NULL; 3087id select_type table type possible_keys key key_len ref rows Extra 3088x x x x x x x x x Impossible WHERE noticed after reading const tables 3089SELECT MIN( a ) FROM t1 WHERE a < NULL; 3090MIN( a ) 3091NULL 3092EXPLAIN 3093SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 3094id select_type table type possible_keys key key_len ref rows Extra 3095x x x x x x x x x Impossible WHERE noticed after reading const tables 3096SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 3097MIN( a ) 3098NULL 3099EXPLAIN 3100SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 3101id select_type table type possible_keys key key_len ref rows Extra 3102x x x x x x x x x Impossible WHERE noticed after reading const tables 3103SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 3104MIN( a ) 3105NULL 3106EXPLAIN 3107SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 3108id select_type table type possible_keys key key_len ref rows Extra 3109x x x x x x x x x Impossible WHERE noticed after reading const tables 3110SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 3111MIN( a ) 3112NULL 3113EXPLAIN 3114SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 3115id select_type table type possible_keys key key_len ref rows Extra 3116x x x x x x x x x Impossible WHERE noticed after reading const tables 3117x x x x x x x x x Using where; Using index 3118SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 3119MIN( a ) 3120NULL 3121EXPLAIN 3122SELECT MIN( a ) FROM t1 WHERE a IS NULL; 3123id select_type table type possible_keys key key_len ref rows Extra 3124x x x x x x x x x Impossible WHERE 3125SELECT MIN( a ) FROM t1 WHERE a IS NULL; 3126MIN( a ) 3127NULL 3128DROP TABLE t1; 3129# 3130# Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at 3131# opt_sum.cc:305 3132# 3133CREATE TABLE t1 ( a INT, KEY (a) ) engine=RocksDB; 3134INSERT INTO t1 VALUES (1), (2), (3); 3135SELECT MIN( a ) AS min_a 3136FROM t1 3137WHERE a > 1 AND a IS NULL 3138ORDER BY min_a; 3139min_a 3140NULL 3141DROP TABLE t1; 3142End of 5.1 tests 3143# 3144# WL#3220 (Loose index scan for COUNT DISTINCT) 3145# 3146CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b)) engine=RocksDB; 3147INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1); 3148INSERT INTO t1 SELECT a, b + 4, 1 FROM t1; 3149INSERT INTO t1 SELECT a + 1, b, 1 FROM t1; 3150ANALYZE TABLE t1; 3151Table Op Msg_type Msg_text 3152test.t1 analyze status Engine-independent statistics collected 3153test.t1 analyze status OK 3154CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c)) engine=RocksDB; 3155INSERT INTO t2 VALUES (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1), 3156(1,4,1,1,1,1); 3157INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2; 3158INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2; 3159ANALYZE TABLE t2; 3160Table Op Msg_type Msg_text 3161test.t2 analyze status Engine-independent statistics collected 3162test.t2 analyze status OK 3163EXPLAIN SELECT COUNT(DISTINCT a) FROM t1; 3164id select_type table type possible_keys key key_len ref rows Extra 31651 SIMPLE t1 range NULL a 5 NULL 3 Using index for group-by 3166SELECT COUNT(DISTINCT a) FROM t1; 3167COUNT(DISTINCT a) 31682 3169EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1; 3170id select_type table type possible_keys key key_len ref rows Extra 31711 SIMPLE t1 range NULL a 10 NULL 17 Using index for group-by 3172SELECT COUNT(DISTINCT a,b) FROM t1; 3173COUNT(DISTINCT a,b) 317416 3175EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1; 3176id select_type table type possible_keys key key_len ref rows Extra 31771 SIMPLE t1 range NULL a 10 NULL 17 Using index for group-by 3178SELECT COUNT(DISTINCT b,a) FROM t1; 3179COUNT(DISTINCT b,a) 318016 3181EXPLAIN SELECT COUNT(DISTINCT b) FROM t1; 3182id select_type table type possible_keys key key_len ref rows Extra 31831 SIMPLE t1 index NULL a 10 NULL 16 Using index 3184SELECT COUNT(DISTINCT b) FROM t1; 3185COUNT(DISTINCT b) 31868 3187EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; 3188id select_type table type possible_keys key key_len ref rows Extra 31891 SIMPLE t1 range NULL a 5 NULL 3 Using index for group-by 3190SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; 3191COUNT(DISTINCT a) 31921 31931 3194EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; 3195id select_type table type possible_keys key key_len ref rows Extra 31961 SIMPLE t1 range NULL a 10 NULL 17 Using index for group-by 3197SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; 3198COUNT(DISTINCT b) 31998 32008 3201EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; 3202id select_type table type possible_keys key key_len ref rows Extra 32031 SIMPLE t1 index NULL a 10 NULL 16 Using index; Using filesort 3204SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; 3205COUNT(DISTINCT a) 32062 32072 32082 32092 32102 32112 32122 32132 3214EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1; 3215id select_type table type possible_keys key key_len ref rows Extra 32161 SIMPLE t1 index NULL a 10 NULL 16 Using index 3217SELECT DISTINCT COUNT(DISTINCT a) FROM t1; 3218COUNT(DISTINCT a) 32192 3220EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1; 3221id select_type table type possible_keys key key_len ref rows Extra 32221 SIMPLE t1 index NULL a 10 NULL 16 Using index 3223SELECT COUNT(DISTINCT a, b + 0) FROM t1; 3224COUNT(DISTINCT a, b + 0) 322516 3226EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; 3227id select_type table type possible_keys key key_len ref rows Extra 32281 SIMPLE t1 index NULL a 10 NULL 16 Using index 3229SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; 3230COUNT(DISTINCT a) 32312 3232EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; 3233id select_type table type possible_keys key key_len ref rows Extra 32341 SIMPLE t1 ALL NULL NULL NULL NULL 16 3235SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; 3236COUNT(DISTINCT a) 32372 3238EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 3239id select_type table type possible_keys key key_len ref rows Extra 32401 SIMPLE t1 range NULL a 5 NULL 3 Using index for group-by 3241SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 32421 32431 3244EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; 3245id select_type table type possible_keys key key_len ref rows Extra 32461 SIMPLE t1 range NULL a 10 NULL 17 Using index for group-by 3247SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; 32481 32491 32501 3251EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; 3252id select_type table type possible_keys key key_len ref rows Extra 32531 SIMPLE t1_1 index NULL a 10 NULL 16 Using index; Using temporary; Using filesort 32541 SIMPLE t1_2 index NULL a 10 NULL 16 Using index; Using join buffer (flat, BNL join) 3255SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; 3256COUNT(DISTINCT t1_1.a) 32571 32581 3259EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1; 3260id select_type table type possible_keys key key_len ref rows Extra 32611 SIMPLE t1 range NULL a 5 NULL 3 Using index for group-by 3262SELECT COUNT(DISTINCT a), 12 FROM t1; 3263COUNT(DISTINCT a) 12 32642 12 3265EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2; 3266id select_type table type possible_keys key key_len ref rows Extra 32671 SIMPLE t2 range NULL a 15 NULL 17 Using index for group-by 3268SELECT COUNT(DISTINCT a, b, c) FROM t2; 3269COUNT(DISTINCT a, b, c) 327016 3271EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; 3272id select_type table type possible_keys key key_len ref rows Extra 32731 SIMPLE t2 range NULL a 5 NULL 3 Using index for group-by 3274SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; 3275COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT a) 32762 3 1.5000 3277EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; 3278id select_type table type possible_keys key key_len ref rows Extra 32791 SIMPLE t2 ALL NULL NULL NULL NULL 16 3280SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; 3281COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT f) 32822 3 1.0000 3283EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; 3284id select_type table type possible_keys key key_len ref rows Extra 32851 SIMPLE t2 range NULL a 10 NULL 17 Using index for group-by 3286SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; 3287COUNT(DISTINCT a, b) COUNT(DISTINCT b, a) 328816 16 3289EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; 3290id select_type table type possible_keys key key_len ref rows Extra 32911 SIMPLE t2 ALL NULL NULL NULL NULL 16 3292SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; 3293COUNT(DISTINCT a, b) COUNT(DISTINCT b, f) 329416 8 3295EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; 3296id select_type table type possible_keys key key_len ref rows Extra 32971 SIMPLE t2 ALL NULL NULL NULL NULL 16 3298SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; 3299COUNT(DISTINCT a, b) COUNT(DISTINCT b, d) 330016 8 3301EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; 3302id select_type table type possible_keys key key_len ref rows Extra 33031 SIMPLE t2 range NULL a 15 NULL 17 Using index for group-by 3304SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; 3305a c COUNT(DISTINCT c, a, b) 33061 1 1 33071 1 1 33081 1 1 33091 1 1 33101 1 1 33111 1 1 33121 1 1 33131 1 1 33142 1 1 33152 1 1 33162 1 1 33172 1 1 33182 1 1 33192 1 1 33202 1 1 33212 1 1 3322EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2 3323WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; 3324id select_type table type possible_keys key key_len ref rows Extra 33251 SIMPLE t2 range a a 15 NULL 17 Using where; Using index for group-by 3326SELECT COUNT(DISTINCT c, a, b) FROM t2 3327WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; 3328COUNT(DISTINCT c, a, b) 3329EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 3330GROUP BY b; 3331id select_type table type possible_keys key key_len ref rows Extra 33321 SIMPLE t2 ref a a 5 const 16 Using where; Using index 3333SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 3334GROUP BY b; 3335COUNT(DISTINCT b) SUM(DISTINCT b) 3336EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 3337id select_type table type possible_keys key key_len ref rows Extra 33381 SIMPLE t2 range NULL a 10 NULL 17 Using index for group-by 3339SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 3340a COUNT(DISTINCT b) SUM(DISTINCT b) 33411 8 36 33422 8 36 3343EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 3344id select_type table type possible_keys key key_len ref rows Extra 33451 SIMPLE t2 range NULL a 10 NULL 17 Using index for group-by 3346SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 3347COUNT(DISTINCT b) SUM(DISTINCT b) 33488 36 33498 36 3350EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; 3351id select_type table type possible_keys key key_len ref rows Extra 33521 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where 3353SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; 3354COUNT(DISTINCT a, b) 33550 3356EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 3357WHERE b = 13 AND c = 42 GROUP BY a; 3358id select_type table type possible_keys key key_len ref rows Extra 33591 SIMPLE t2 range NULL a 15 NULL 3 Using where; Using index for group-by 3360SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 3361WHERE b = 13 AND c = 42 GROUP BY a; 3362a COUNT(DISTINCT a) SUM(DISTINCT a) 3363# This query could have been resolved using loose index scan since 3364# the second part of count(..) is defined by a constant predicate 3365EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; 3366id select_type table type possible_keys key key_len ref rows Extra 33671 SIMPLE t2 index NULL a 15 NULL 16 Using where; Using index 3368SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; 3369COUNT(DISTINCT a, b) SUM(DISTINCT a) 33700 NULL 3371EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; 3372id select_type table type possible_keys key key_len ref rows Extra 33731 SIMPLE t2 index NULL a 15 NULL 16 Using index 3374SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; 3375SUM(DISTINCT a) MAX(b) 33761 8 33772 8 3378EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 3379id select_type table type possible_keys key key_len ref rows Extra 33801 SIMPLE t2 range NULL a 15 NULL 17 Using index for group-by 3381SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 338242 * (a + c + COUNT(DISTINCT c, a, b)) 3383126 3384126 3385126 3386126 3387126 3388126 3389126 3390126 3391168 3392168 3393168 3394168 3395168 3396168 3397168 3398168 3399EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; 3400id select_type table type possible_keys key key_len ref rows Extra 34011 SIMPLE t2 index NULL a 15 NULL 16 Using index 3402SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; 3403(SUM(DISTINCT a) + MAX(b)) 34049 340510 3406DROP TABLE t1,t2; 3407# end of WL#3220 tests 3408# 3409# Bug#50539: Wrong result when loose index scan is used for an aggregate 3410# function with distinct 3411# 3412CREATE TABLE t1 ( 3413f1 int(11) NOT NULL DEFAULT '0', 3414f2 char(1) NOT NULL DEFAULT '', 3415PRIMARY KEY (f1,f2) 3416) engine=RocksDB; 3417insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), 3418(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D'); 3419ANALYZE TABLE t1; 3420Table Op Msg_type Msg_text 3421test.t1 analyze status Engine-independent statistics collected 3422test.t1 analyze status OK 3423SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; 3424f1 COUNT(DISTINCT f2) 34251 3 34262 1 34273 4 3428explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; 3429id select_type table type possible_keys key key_len ref rows Extra 34301 SIMPLE t1 index NULL PRIMARY 5 NULL 8 Using index 3431drop table t1; 3432# End of test#50539. 3433# 3434# Bug#17217128 - BAD INTERACTION BETWEEN MIN/MAX AND 3435# "HAVING SUM(DISTINCT)": WRONG RESULTS. 3436# 3437CREATE TABLE t (a INT, b INT, KEY(a,b)) engine=RocksDB; 3438INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5); 3439ANALYZE TABLE t; 3440Table Op Msg_type Msg_text 3441test.t analyze status Engine-independent statistics collected 3442test.t analyze status OK 3443ANALYZE TABLE t; 3444Table Op Msg_type Msg_text 3445test.t analyze status Engine-independent statistics collected 3446test.t analyze status OK 3447SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; 3448a SUM(DISTINCT a) MIN(b) 34491 1 0 34502 2 2 34513 3 2 34524 4 4 3453EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; 3454id select_type table type possible_keys key key_len ref rows Extra 34551 SIMPLE t index NULL a 10 NULL 7 Using index 3456SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; 3457a SUM(DISTINCT a) MAX(b) 34581 1 1 34592 2 2 34603 3 3 34614 4 5 3462EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; 3463id select_type table type possible_keys key key_len ref rows Extra 34641 SIMPLE t index NULL a 10 NULL 7 Using index 3465SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); 3466a MAX(b) 34671 1 34682 2 34693 3 34704 5 3471EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); 3472id select_type table type possible_keys key key_len ref rows Extra 34731 SIMPLE t index NULL a 10 NULL 7 Using index 3474SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; 3475SUM(DISTINCT a) MIN(b) MAX(b) 347610 0 5 3477EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; 3478id select_type table type possible_keys key key_len ref rows Extra 34791 SIMPLE t index NULL a 10 NULL 7 Using index 3480SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; 3481a SUM(DISTINCT a) MIN(b) MAX(b) 34821 1 0 1 34832 2 2 2 34843 3 2 3 34854 4 4 5 3486EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; 3487id select_type table type possible_keys key key_len ref rows Extra 34881 SIMPLE t index NULL a 10 NULL 7 Using index 3489DROP TABLE t; 3490# 3491# Bug#18109609: LOOSE INDEX SCAN IS NOT USED WHEN IT SHOULD 3492# 3493CREATE TABLE t1 ( 3494id INT AUTO_INCREMENT PRIMARY KEY, 3495c1 INT, 3496c2 INT, 3497KEY(c1,c2)) engine=RocksDB; 3498INSERT INTO t1(c1,c2) VALUES 3499(1, 1), (1,2), (2,1), (2,2), (3,1), (3,2), (3,3), (4,1), (4,2), (4,3), 3500(4,4), (4,5), (4,6), (4,7), (4,8), (4,9), (4,10), (4,11), (4,12), (4,13), 3501(4,14), (4,15), (4,16), (4,17), (4,18), (4,19), (4,20),(5,5); 3502ANALYZE TABLE t1; 3503Table Op Msg_type Msg_text 3504test.t1 analyze status Engine-independent statistics collected 3505test.t1 analyze status OK 3506EXPLAIN SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; 3507id select_type table type possible_keys key key_len ref rows Extra 35081 SIMPLE t1 ref c1 c1 5 const 28 Using index 3509FLUSH STATUS; 3510SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; 3511MAX(c2) c1 351220 4 3513SHOW SESSION STATUS LIKE 'Handler_read%'; 3514Variable_name Value 3515Handler_read_first 0 3516Handler_read_key 1 3517Handler_read_last 0 3518Handler_read_next 20 3519Handler_read_prev 0 3520Handler_read_retry 0 3521Handler_read_rnd 0 3522Handler_read_rnd_deleted 0 3523Handler_read_rnd_next 0 3524DROP TABLE t1; 3525# End of test for Bug#18109609 3526set global debug_dbug=@debug_tmp; 3527