1drop view if exists v1; 2drop table if exists t1,t4; 3create table t4 ( 4pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' 5) engine=innodb; 6insert into t4 (a1, a2, b, c, d) values 7('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 8('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 9('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 10('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 11('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 12('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 13('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 14('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 15('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 16('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 17('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 18('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 19('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 20('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 21('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 22('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), 23('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 24('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 25('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 26('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 27('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 28('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 29('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 30('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 31('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 32('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 33('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 34('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 35('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 36('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 37('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 38('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); 39create index idx12672_0 on t4 (a1); 40create index idx12672_1 on t4 (a1,a2,b,c); 41create index idx12672_2 on t4 (a1,a2,b); 42analyze table t4; 43Table Op Msg_type Msg_text 44test.t4 analyze status OK 45select distinct a1 from t4 where pk_col not in (1,2,3,4); 46a1 47a 48b 49c 50d 51drop table t4; 52create table t1 ( 53a varchar(30), b varchar(30), primary key(a), key(b) 54) engine=innodb; 55select distinct a from t1; 56a 57drop table t1; 58create table t1(a int, key(a)) engine=innodb; 59insert into t1 values(1); 60select a, count(a) from t1 group by a with rollup; 61a count(a) 621 1 63NULL 1 64drop table t1; 65create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb 66stats_persistent=0; 67insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); 68alter table t1 drop primary key, add primary key (f2, f1); 69explain select distinct f1 a, f1 b from t1; 70id select_type table partitions type possible_keys key key_len ref rows filtered Extra 711 SIMPLE t1 NULL index PRIMARY PRIMARY 5 NULL 4 100.00 Using index; Using temporary 72Warnings: 73Note 1003 /* select#1 */ select distinct `test`.`t1`.`f1` AS `a`,`test`.`t1`.`f1` AS `b` from `test`.`t1` 74explain select distinct f1, f2 from t1; 75id select_type table partitions type possible_keys key key_len ref rows filtered Extra 761 SIMPLE t1 NULL index PRIMARY PRIMARY 5 NULL 4 100.00 Using index 77Warnings: 78Note 1003 /* select#1 */ select distinct `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` 79drop table t1; 80create table t1(pk int primary key) engine=innodb; 81create view v1 as select pk from t1 where pk < 20; 82insert into t1 values (1), (2), (3), (4); 83select distinct pk from v1; 84pk 851 862 873 884 89insert into t1 values (5), (6), (7); 90select distinct pk from v1; 91pk 921 932 943 954 965 976 987 99drop view v1; 100drop table t1; 101End of 5.1 tests 102# 103# Bug#12540545 61101: ASSERTION FAILURE IN THREAD 1256741184 IN 104# FILE /BUILDDIR/BUILD/BUILD/MYSQ 105# 106CREATE TABLE t1 (a CHAR(1), b CHAR(1), PRIMARY KEY (a,b)) ENGINE=InnoDB; 107INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); 108EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b'; 109id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1101 SIMPLE t1 NULL index PRIMARY PRIMARY 2 NULL 2 50.00 Using where; Using index 111Warnings: 112Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT a)` from `test`.`t1` where (`test`.`t1`.`b` = 'b') 113SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b'; 114COUNT(DISTINCT a) 1151 116DROP TABLE t1; 117CREATE TABLE t1 (a CHAR(1) NOT NULL, b CHAR(1) NOT NULL, UNIQUE KEY (a,b)) 118ENGINE=InnoDB; 119INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); 120EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b'; 121id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1221 SIMPLE t1 NULL index a a 2 NULL 2 50.00 Using where; Using index 123Warnings: 124Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT a)` from `test`.`t1` where (`test`.`t1`.`b` = 'b') 125SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b'; 126COUNT(DISTINCT a) 1271 128DROP TABLE t1; 129End of 5.5 tests 130# 131# Bug#17909656 - WRONG RESULTS FOR A SIMPLE QUERY WITH GROUP BY 132# 133CREATE TABLE t0 ( 134i1 INTEGER NOT NULL 135); 136INSERT INTO t0 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), 137(11),(12),(13),(14),(15),(16),(17),(18),(19),(20), 138(21),(22),(23),(24),(25),(26),(27),(28),(29),(30); 139CREATE TABLE t1 ( 140c1 CHAR(1) NOT NULL, 141i1 INTEGER NOT NULL, 142i2 INTEGER NOT NULL, 143UNIQUE KEY k1 (c1,i2) 144) ENGINE=InnoDB; 145INSERT INTO t1 SELECT 'A',i1,i1 FROM t0; 146INSERT INTO t1 SELECT 'B',i1,i1 FROM t0; 147INSERT INTO t1 SELECT 'C',i1,i1 FROM t0; 148INSERT INTO t1 SELECT 'D',i1,i1 FROM t0; 149INSERT INTO t1 SELECT 'E',i1,i1 FROM t0; 150INSERT INTO t1 SELECT 'F',i1,i1 FROM t0; 151CREATE TABLE t2 ( 152c1 CHAR(1) NOT NULL, 153i1 INTEGER NOT NULL, 154i2 INTEGER NOT NULL, 155UNIQUE KEY k2 (c1,i1,i2) 156) ENGINE=InnoDB; 157INSERT INTO t2 SELECT 'A',i1,i1 FROM t0; 158INSERT INTO t2 SELECT 'B',i1,i1 FROM t0; 159INSERT INTO t2 SELECT 'C',i1,i1 FROM t0; 160INSERT INTO t2 SELECT 'D',i1,i1 FROM t0; 161INSERT INTO t2 SELECT 'E',i1,i1 FROM t0; 162INSERT INTO t2 SELECT 'F',i1,i1 FROM t0; 163ANALYZE TABLE t1; 164ANALYZE TABLE t2; 165set optimizer_trace_max_mem_size=1048576; 166set @@session.optimizer_trace='enabled=on'; 167set end_markers_in_json=on; 168EXPLAIN SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' AND i2 = 17) OR ( c1 = 'F') 169GROUP BY c1; 170id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1711 SIMPLE t1 NULL range k1 k1 5 NULL ROWS 100.00 Using where; Using index 172Warnings: 173Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,max(`test`.`t1`.`i2`) AS `max(i2)` from `test`.`t1` where (((`test`.`t1`.`i2` = 17) and (`test`.`t1`.`c1` = 'C')) or (`test`.`t1`.`c1` = 'F')) group by `test`.`t1`.`c1` 174SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' AND i2 = 17) OR ( c1 = 'F') 175GROUP BY c1; 176c1 max(i2) 177C 17 178F 30 179SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' 180AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 181OK 1821 183EXPLAIN SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR ( c1 = 'F' AND i2 = 17)) 184GROUP BY c1; 185id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1861 SIMPLE t1 NULL range k1 k1 5 NULL ROWS 100.00 Using where; Using index 187Warnings: 188Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,max(`test`.`t1`.`i2`) AS `max(i2)` from `test`.`t1` where ((`test`.`t1`.`c1` = 'C') or ((`test`.`t1`.`i2` = 17) and (`test`.`t1`.`c1` = 'F'))) group by `test`.`t1`.`c1` 189SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR ( c1 = 'F' AND i2 = 17)) 190GROUP BY c1; 191c1 max(i2) 192C 30 193F 17 194SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' 195AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 196OK 1971 198EXPLAIN SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR c1 = 'F' ) AND ( i2 = 17 ) 199GROUP BY c1; 200id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2011 SIMPLE t1 NULL range k1 k1 5 NULL ROWS 100.00 Using where; Using index for group-by 202Warnings: 203Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,max(`test`.`t1`.`i2`) AS `max(i2)` from `test`.`t1` where ((`test`.`t1`.`i2` = 17) and ((`test`.`t1`.`c1` = 'C') or (`test`.`t1`.`c1` = 'F'))) group by `test`.`t1`.`c1` 204SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR c1 = 'F' ) AND ( i2 = 17 ) 205GROUP BY c1; 206c1 max(i2) 207C 17 208F 17 209SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' 210AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 211OK 2120 213EXPLAIN SELECT c1, max(i2) FROM t1 214WHERE ((c1 = 'C' AND (i2 = 40 OR i2 = 30)) OR ( c1 = 'F' AND (i2 = 40 ))) 215GROUP BY c1; 216id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2171 SIMPLE t1 NULL range k1 k1 5 NULL ROWS 100.00 Using where; Using index 218Warnings: 219Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,max(`test`.`t1`.`i2`) AS `max(i2)` from `test`.`t1` where (((`test`.`t1`.`c1` = 'C') and ((`test`.`t1`.`i2` = 40) or (`test`.`t1`.`i2` = 30))) or ((`test`.`t1`.`i2` = 40) and (`test`.`t1`.`c1` = 'F'))) group by `test`.`t1`.`c1` 220SELECT c1, max(i2) FROM t1 221WHERE ((c1 = 'C' AND (i2 = 40 OR i2 = 30)) OR ( c1 = 'F' AND (i2 = 40 ))) 222GROUP BY c1; 223c1 max(i2) 224C 30 225SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' 226AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 227OK 2281 229EXPLAIN SELECT c1, i1, max(i2) FROM t2 230WHERE (c1 = 'C' OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 ) 231GROUP BY c1,i1; 232id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2331 SIMPLE t2 NULL range k2 k2 5 NULL ROWS 10.00 Using where; Using index 234Warnings: 235Note 1003 /* select#1 */ select `test`.`t2`.`c1` AS `c1`,`test`.`t2`.`i1` AS `i1`,max(`test`.`t2`.`i2`) AS `max(i2)` from `test`.`t2` where ((`test`.`t2`.`i2` = 17) and ((`test`.`t2`.`c1` = 'C') or ((`test`.`t2`.`c1` = 'F') and (`test`.`t2`.`i1` < 35)))) group by `test`.`t2`.`c1`,`test`.`t2`.`i1` 236SELECT c1, i1, max(i2) FROM t2 237WHERE (c1 = 'C' OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 ) 238GROUP BY c1,i1; 239c1 i1 max(i2) 240C 17 17 241F 17 17 242SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' 243AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 244OK 2450 246EXPLAIN SELECT c1, i1, max(i2) FROM t2 247WHERE (((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 )) 248GROUP BY c1,i1; 249id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2501 SIMPLE t2 NULL range k2 k2 5 NULL ROWS 10.00 Using where; Using index 251Warnings: 252Note 1003 /* select#1 */ select `test`.`t2`.`c1` AS `c1`,`test`.`t2`.`i1` AS `i1`,max(`test`.`t2`.`i2`) AS `max(i2)` from `test`.`t2` where ((`test`.`t2`.`i2` = 17) and (((`test`.`t2`.`c1` = 'C') and (`test`.`t2`.`i1` < 40)) or ((`test`.`t2`.`c1` = 'F') and (`test`.`t2`.`i1` < 35)))) group by `test`.`t2`.`c1`,`test`.`t2`.`i1` 253SELECT c1, i1, max(i2) FROM t2 254WHERE (((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 )) 255GROUP BY c1,i1; 256c1 i1 max(i2) 257C 17 17 258F 17 17 259SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' 260AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 261OK 2620 263EXPLAIN SELECT c1, i1, max(i2) FROM t2 264WHERE ((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35) OR ( i2 = 17 )) 265GROUP BY c1,i1; 266id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2671 SIMPLE t2 NULL index k2 k2 9 NULL ROWS 19.72 Using where; Using index 268Warnings: 269Note 1003 /* select#1 */ select `test`.`t2`.`c1` AS `c1`,`test`.`t2`.`i1` AS `i1`,max(`test`.`t2`.`i2`) AS `max(i2)` from `test`.`t2` where (((`test`.`t2`.`c1` = 'C') and (`test`.`t2`.`i1` < 40)) or ((`test`.`t2`.`c1` = 'F') and (`test`.`t2`.`i1` < 35)) or (`test`.`t2`.`i2` = 17)) group by `test`.`t2`.`c1`,`test`.`t2`.`i1` 270SELECT c1, i1, max(i2) FROM t2 271WHERE ((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35) OR ( i2 = 17 )) 272GROUP BY c1,i1; 273c1 i1 max(i2) 274A 17 17 275B 17 17 276C 1 1 277C 2 2 278C 3 3 279C 4 4 280C 5 5 281C 6 6 282C 7 7 283C 8 8 284C 9 9 285C 10 10 286C 11 11 287C 12 12 288C 13 13 289C 14 14 290C 15 15 291C 16 16 292C 17 17 293C 18 18 294C 19 19 295C 20 20 296C 21 21 297C 22 22 298C 23 23 299C 24 24 300C 25 25 301C 26 26 302C 27 27 303C 28 28 304C 29 29 305C 30 30 306D 17 17 307E 17 17 308F 1 1 309F 2 2 310F 3 3 311F 4 4 312F 5 5 313F 6 6 314F 7 7 315F 8 8 316F 9 9 317F 10 10 318F 11 11 319F 12 12 320F 13 13 321F 14 14 322F 15 15 323F 16 16 324F 17 17 325F 18 18 326F 19 19 327F 20 20 328F 21 21 329F 22 22 330F 23 23 331F 24 24 332F 25 25 333F 26 26 334F 27 27 335F 28 28 336F 29 29 337F 30 30 338SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' 339AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 340OK 3411 342SET optimizer_trace_max_mem_size=DEFAULT; 343SET optimizer_trace=DEFAULT; 344SET end_markers_in_json=DEFAULT; 345DROP TABLE t0,t1,t2; 346# 347# Bug #21749123: SELECT DISTINCT, WRONG RESULTS COMBINED WITH 348# USE_INDEX_EXTENSIONS=OFF 349# 350CREATE TABLE t1 ( 351pk_col INT AUTO_INCREMENT PRIMARY KEY, 352a1 CHAR(64), 353KEY a1_idx (a1) 354) ENGINE=INNODB; 355INSERT INTO t1 (a1) VALUES ('a'),('a'),('a'),('a'), ('a'); 356CREATE TABLE t2 ( 357pk_col1 INT NOT NULL, 358pk_col2 INT NOT NULL, 359a1 CHAR(64), 360a2 CHAR(64), 361PRIMARY KEY(pk_col1, pk_col2), 362KEY a1_idx (a1), 363KEY a1_a2_idx (a1, a2) 364) ENGINE=INNODB; 365INSERT INTO t2 (pk_col1, pk_col2, a1, a2) VALUES (1,1,'a','b'),(1,2,'a','b'), 366(1,3,'a','c'),(1,4,'a','c'), 367(2,1,'a','d'); 368ANALYZE TABLE t1; 369Table Op Msg_type Msg_text 370test.t1 analyze status OK 371ANALYZE TABLE t2; 372Table Op Msg_type Msg_text 373test.t2 analyze status OK 374EXPLAIN SELECT DISTINCT a1 375FROM t1 376WHERE (pk_col = 2 OR pk_col = 22) AND a1 = 'a'; 377id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3781 SIMPLE t1 NULL range PRIMARY,a1_idx a1_idx 69 NULL 2 100.00 Using where; Using index 379Warnings: 380Note 1003 /* select#1 */ select distinct `test`.`t1`.`a1` AS `a1` from `test`.`t1` where ((`test`.`t1`.`a1` = 'a') and ((`test`.`t1`.`pk_col` = 2) or (`test`.`t1`.`pk_col` = 22))) 381SELECT DISTINCT a1 382FROM t1 383WHERE (pk_col = 2 OR pk_col = 22) AND a1 = 'a'; 384a1 385a 386EXPLAIN SELECT COUNT(DISTINCT a1) 387FROM t1 388GROUP BY a1,pk_col; 389id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3901 SIMPLE t1 NULL range a1_idx a1_idx 69 NULL 6 100.00 Using index for group-by (scanning) 391Warnings: 392Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`a1`) AS `COUNT(DISTINCT a1)` from `test`.`t1` group by `test`.`t1`.`a1`,`test`.`t1`.`pk_col` 393SELECT COUNT(DISTINCT a1) 394FROM t1 395GROUP BY a1,pk_col; 396COUNT(DISTINCT a1) 3971 3981 3991 4001 4011 402EXPLAIN SELECT COUNT(DISTINCT a1) 403FROM t2 404GROUP BY a1,pk_col1; 405id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4061 SIMPLE t2 NULL range a1_idx,a1_a2_idx a1_idx 69 NULL 3 100.00 Using index for group-by 407Warnings: 408Note 1003 /* select#1 */ select count(distinct `test`.`t2`.`a1`) AS `COUNT(DISTINCT a1)` from `test`.`t2` group by `test`.`t2`.`a1`,`test`.`t2`.`pk_col1` 409SELECT COUNT(DISTINCT a1) 410FROM t2 411GROUP BY a1,pk_col1; 412COUNT(DISTINCT a1) 4131 4141 415EXPLAIN SELECT COUNT(DISTINCT a1) 416FROM t2 417GROUP BY a1,a2; 418id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4191 SIMPLE t2 NULL range a1_a2_idx a1_a2_idx 130 NULL 4 100.00 Using index for group-by (scanning) 420Warnings: 421Note 1003 /* select#1 */ select count(distinct `test`.`t2`.`a1`) AS `COUNT(DISTINCT a1)` from `test`.`t2` group by `test`.`t2`.`a1`,`test`.`t2`.`a2` 422SELECT COUNT(DISTINCT a1) 423FROM t2 424GROUP BY a1,a2; 425COUNT(DISTINCT a1) 4261 4271 4281 429SET @optimizer_switch_save=@@optimizer_switch; 430SET @@optimizer_switch= "use_index_extensions=off"; 431EXPLAIN SELECT DISTINCT a1 432FROM t1 433WHERE (pk_col = 2 OR pk_col = 22) AND a1 = 'a'; 434id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4351 SIMPLE t1 NULL ref PRIMARY,a1_idx a1_idx 65 const 5 40.00 Using where; Using index 436Warnings: 437Note 1003 /* select#1 */ select distinct `test`.`t1`.`a1` AS `a1` from `test`.`t1` where ((`test`.`t1`.`a1` = 'a') and ((`test`.`t1`.`pk_col` = 2) or (`test`.`t1`.`pk_col` = 22))) 438SELECT DISTINCT a1 439FROM t1 440WHERE (pk_col = 2 OR pk_col = 22) AND a1 = 'a'; 441a1 442a 443EXPLAIN SELECT COUNT(DISTINCT a1) 444FROM t1 445GROUP BY a1,pk_col; 446id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4471 SIMPLE t1 NULL index a1_idx a1_idx 65 NULL 5 100.00 Using index 448Warnings: 449Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`a1`) AS `COUNT(DISTINCT a1)` from `test`.`t1` group by `test`.`t1`.`a1`,`test`.`t1`.`pk_col` 450SELECT COUNT(DISTINCT a1) 451FROM t1 452GROUP BY a1,pk_col; 453COUNT(DISTINCT a1) 4541 4551 4561 4571 4581 459EXPLAIN SELECT COUNT(DISTINCT a1) 460FROM t2 461GROUP BY a1,pk_col1; 462id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4631 SIMPLE t2 NULL index a1_idx,a1_a2_idx a1_idx 65 NULL 5 100.00 Using index 464Warnings: 465Note 1003 /* select#1 */ select count(distinct `test`.`t2`.`a1`) AS `COUNT(DISTINCT a1)` from `test`.`t2` group by `test`.`t2`.`a1`,`test`.`t2`.`pk_col1` 466SELECT COUNT(DISTINCT a1) 467FROM t2 468GROUP BY a1,pk_col1; 469COUNT(DISTINCT a1) 4701 4711 472EXPLAIN SELECT COUNT(DISTINCT a1) 473FROM t2 474GROUP BY a1,a2; 475id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4761 SIMPLE t2 NULL range a1_a2_idx a1_a2_idx 130 NULL 4 100.00 Using index for group-by (scanning) 477Warnings: 478Note 1003 /* select#1 */ select count(distinct `test`.`t2`.`a1`) AS `COUNT(DISTINCT a1)` from `test`.`t2` group by `test`.`t2`.`a1`,`test`.`t2`.`a2` 479SELECT COUNT(DISTINCT a1) 480FROM t2 481GROUP BY a1,a2; 482COUNT(DISTINCT a1) 4831 4841 4851 486SET @@optimizer_switch= @optimizer_switch_save; 487DROP TABLE t1, t2; 488# 489# Bug #24671968: WHEN THE OPTIMISER IS USING INDEX FOR GROUP-BY IT OFTEN 490# OFTEN GIVES WRONG RESULTS 491# 492CREATE TABLE t1 ( 493id int NOT NULL, 494c1 int NOT NULL, 495c2 int, 496PRIMARY KEY(id), 497INDEX c1_c2_idx(c1, c2)); 498INSERT INTO t1 (id, c1, c2) VALUES (1,1,1), (2,2,2), (10,10,1), (11,10,8), 499(12,10,1), (13,10,2); 500ANALYZE TABLE t1; 501Table Op Msg_type Msg_text 502test.t1 analyze status OK 503EXPLAIN SELECT DISTINCT c1 504FROM t1 505WHERE EXISTS (SELECT * 506FROM DUAL 507WHERE (c2 = 2)); 508id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5091 PRIMARY t1 NULL index c1_c2_idx c1_c2_idx 9 NULL 6 100.00 Using where; Using index 5102 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 511Warnings: 512Note 1276 Field or reference 'test.t1.c2' of SELECT #2 was resolved in SELECT #1 513Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1` where exists(/* select#2 */ select 1 from DUAL where (`test`.`t1`.`c2` = 2)) 514EXPLAIN SELECT DISTINCT c1 515FROM t1 516WHERE 1 IN (2, 517(SELECT 1 518FROM DUAL 519WHERE (c2 = 2)), 5203); 521id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5221 PRIMARY t1 NULL index c1_c2_idx c1_c2_idx 9 NULL 6 100.00 Using where; Using index 5232 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 524Warnings: 525Note 1276 Field or reference 'test.t1.c2' of SELECT #2 was resolved in SELECT #1 526Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (1 in (2,(/* select#2 */ select 1 from DUAL where (`test`.`t1`.`c2` = 2)),3)) 527EXPLAIN SELECT DISTINCT c1 528FROM t1 529WHERE c2 = 2; 530id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5311 SIMPLE t1 NULL range c1_c2_idx c1_c2_idx 9 NULL 4 100.00 Using where; Using index for group-by 532Warnings: 533Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (`test`.`t1`.`c2` = 2) 534EXPLAIN SELECT DISTINCT c1 535FROM t1 IGNORE INDEX (c1_c2_idx) 536WHERE EXISTS (SELECT * 537FROM DUAL 538WHERE (c2 = 2)); 539id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5401 PRIMARY t1 NULL ALL c1_c2_idx NULL NULL NULL 6 100.00 Using where; Using temporary 5412 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 542Warnings: 543Note 1276 Field or reference 'test.t1.c2' of SELECT #2 was resolved in SELECT #1 544Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1` IGNORE INDEX (`c1_c2_idx`) where exists(/* select#2 */ select 1 from DUAL where (`test`.`t1`.`c2` = 2)) 545EXPLAIN SELECT DISTINCT c1 546FROM t1 IGNORE INDEX (c1_c2_idx) 547WHERE 1 IN (2, 548(SELECT 1 549FROM DUAL 550WHERE (c2 = 2)), 5513); 552id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5531 PRIMARY t1 NULL ALL c1_c2_idx NULL NULL NULL 6 100.00 Using where; Using temporary 5542 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 555Warnings: 556Note 1276 Field or reference 'test.t1.c2' of SELECT #2 was resolved in SELECT #1 557Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1` IGNORE INDEX (`c1_c2_idx`) where (1 in (2,(/* select#2 */ select 1 from DUAL where (`test`.`t1`.`c2` = 2)),3)) 558SET optimizer_trace="enabled=on"; 559SELECT DISTINCT c1 560FROM t1 561WHERE EXISTS (SELECT * 562FROM DUAL 563WHERE (c2 = 2)); 564c1 5652 56610 567SELECT TRACE INTO @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 568SELECT @trace RLIKE "keypart_reference_from_where_clause"; 569@trace RLIKE "keypart_reference_from_where_clause" 5701 571SET optimizer_trace="enabled=off"; 572SELECT DISTINCT c1 573FROM t1 574WHERE 1 IN (2, 575(SELECT 1 576FROM DUAL 577WHERE (c2 = 2)), 5783); 579c1 5802 58110 582SELECT DISTINCT c1 583FROM t1 584WHERE c2 = 2; 585c1 5862 58710 588SELECT DISTINCT c1 589FROM t1 IGNORE INDEX (c1_c2_idx) 590WHERE EXISTS (SELECT * 591FROM DUAL 592WHERE (c2 = 2)); 593c1 5942 59510 596SELECT DISTINCT c1 597FROM t1 IGNORE INDEX (c1_c2_idx) 598WHERE 1 IN (2, 599(SELECT 1 600FROM DUAL 601WHERE (c2 = 2)), 6023); 603c1 6042 60510 606DROP TABLE t1; 607# 608# Bug #26532061: SELECT DISTINCT WITH SECONDARY KEY FOR 609# 'USING INDEX FOR GROUP-BY' BAD RESULTS 610# 611CREATE TABLE t1( 612pk INT NOT NULL, 613c1 CHAR(2), 614c2 INT, 615PRIMARY KEY(pk), 616UNIQUE KEY ukey(c1, c2) 617); 618INSERT INTO t1(pk, c1, c2) VALUES (1,1,1),(2,2,2),(3,3,3),(4,5,4); 619SET @a:=5; 620INSERT IGNORE INTo t1(pk, c1, c2) 621SELECT (@a:=@a+1),@a,@a FROM t1, t1 t2,t1 t3, t1 t4; 622ANALYZE TABLE t1; 623Table Op Msg_type Msg_text 624test.t1 analyze status OK 625SELECT * FROM t1 WHERE pk = 1 OR pk = 231; 626pk c1 c2 6271 1 1 628231 23 231 629EXPLAIN SELECT DISTINCT c1 630FROM t1 FORCE INDEX(ukey) 631WHERE pk IN (1,231) and c1 IS NOT NULL; 632id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6331 SIMPLE t1 NULL index ukey ukey 8 NULL 260 20.00 Using where; Using index 634Warnings: 635Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1` FORCE INDEX (`ukey`) where ((`test`.`t1`.`pk` in (1,231)) and (`test`.`t1`.`c1` is not null)) 636EXPLAIN SELECT DISTINCT c1 637FROM t1 IGNORE INDEX(ukey) 638WHERE pk IN (1,231) and c1 IS NOT NULL; 639id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6401 SIMPLE t1 NULL range PRIMARY,ukey PRIMARY 4 NULL 2 90.00 Using where; Using temporary 641Warnings: 642Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1` IGNORE INDEX (`ukey`) where ((`test`.`t1`.`pk` in (1,231)) and (`test`.`t1`.`c1` is not null)) 643SELECT DISTINCT c1 644FROM t1 FORCE INDEX(ukey) 645WHERE pk IN (1,231) and c1 IS NOT NULL; 646c1 6471 64823 649SELECT DISTINCT c1 650FROM t1 IGNORE INDEX(ukey) 651WHERE pk IN (1,231) and c1 IS NOT NULL; 652c1 6531 65423 655DROP TABLE t1; 656# 657# Bug #25989915: LOOSE INDEX SCANS RETURNING WRONG RESULT 658# 659CREATE TABLE t1 ( 660pk INT NOT NULL AUTO_INCREMENT, 661c1 varchar(100) DEFAULT NULL, 662c2 INT NOT NULL, 663PRIMARY KEY (pk), 664UNIQUE KEY ukey (c2,c1) 665); 666INSERT INTO t1(pk, c2) VALUES (100, 0), (101, 0), (102, 0), (103, 0); 667ANALYZE TABLE t1; 668Table Op Msg_type Msg_text 669test.t1 analyze status OK 670EXPLAIN SELECT COUNT(DISTINCT(c2)) FROM t1 WHERE pk IN (102, 101); 671id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6721 SIMPLE t1 NULL index PRIMARY,ukey ukey 107 NULL 4 50.00 Using where; Using index 673Warnings: 674Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`c2`) AS `COUNT(DISTINCT(c2))` from `test`.`t1` where (`test`.`t1`.`pk` in (102,101)) 675EXPLAIN SELECT COUNT(DISTINCT(c2)) FROM t1 WHERE pk IN (102, 100); 676id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6771 SIMPLE t1 NULL index PRIMARY,ukey ukey 107 NULL 4 50.00 Using where; Using index 678Warnings: 679Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`c2`) AS `COUNT(DISTINCT(c2))` from `test`.`t1` where (`test`.`t1`.`pk` in (102,100)) 680SELECT COUNT(DISTINCT(c2)) FROM t1 WHERE pk IN (102, 101); 681COUNT(DISTINCT(c2)) 6821 683SELECT COUNT(DISTINCT(c2)) FROM t1 WHERE pk IN (102, 100); 684COUNT(DISTINCT(c2)) 6851 686DROP TABLE t1; 687