drop view if exists v1;
drop table if exists t1,t4;
create table t4 (
pk_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 ' '
) engine=innodb;
insert into t4 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
create index idx12672_0 on t4 (a1);
create index idx12672_1 on t4 (a1,a2,b,c);
create index idx12672_2 on t4 (a1,a2,b);
analyze table t4;
Table	Op	Msg_type	Msg_text
test.t4	analyze	status	OK
select distinct a1 from t4 where pk_col not in (1,2,3,4);
a1
a
b
c
d
drop table t4;
create table t1 (
a varchar(30), b varchar(30), primary key(a), key(b)
) engine=innodb;
select distinct a from t1;
a
drop table t1;
create table t1(a int, key(a)) engine=innodb;
insert into t1 values(1);
select a, count(a) from t1 group by a with rollup;
a	count(a)
1	1
NULL	1
drop table t1;
create table t1 (f1 int, f2 char(1), primary key(f1,f2)) charset utf8mb4 engine=innodb
stats_persistent=0;
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
alter table t1 drop primary key, add primary key (f2, f1);
explain select distinct f1 a, f1 b from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY	PRIMARY	8	NULL	4	100.00	Using index; Using temporary
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`f1` AS `a`,`test`.`t1`.`f1` AS `b` from `test`.`t1`
explain select distinct f1, f2 from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY	PRIMARY	8	NULL	4	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1`
drop table t1;
create table t1(pk int primary key) engine=innodb;
create view v1 as select pk from t1 where pk < 20;
insert into t1 values (1), (2), (3), (4);
select distinct pk from v1;
pk
1
2
3
4
insert into t1 values (5), (6), (7);
select distinct pk from v1;
pk
1
2
3
4
5
6
7
drop view v1;
drop table t1;
End of 5.1 tests
#
# Bug#12540545 61101: ASSERTION FAILURE IN THREAD 1256741184 IN
# FILE /BUILDDIR/BUILD/BUILD/MYSQ
#
CREATE TABLE t1 (a CHAR(1), b CHAR(1), PRIMARY KEY (a,b))
charset utf8mb4 ENGINE=InnoDB;
INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd');
EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY	PRIMARY	8	NULL	2	50.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT a)` from `test`.`t1` where (`test`.`t1`.`b` = 'b')
SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b';
COUNT(DISTINCT a)
1
DROP TABLE t1;
CREATE TABLE t1 (a CHAR(1) NOT NULL, b CHAR(1) NOT NULL, UNIQUE KEY (a,b))
charset utf8mb4 ENGINE=InnoDB;
INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd');
EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	a	a	8	NULL	2	50.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT a)` from `test`.`t1` where (`test`.`t1`.`b` = 'b')
SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b';
COUNT(DISTINCT a)
1
DROP TABLE t1;
End of 5.5 tests
#
# Bug#17909656 - WRONG RESULTS FOR A SIMPLE QUERY WITH GROUP BY
#
CREATE TABLE t0 (
i1 INTEGER NOT NULL
);
INSERT INTO t0 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
(21),(22),(23),(24),(25),(26),(27),(28),(29),(30);
CREATE TABLE t1 (
c1 CHAR(1) NOT NULL,
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
UNIQUE KEY k1 (c1,i2)
) charset utf8mb4 ENGINE=InnoDB;
INSERT INTO t1 SELECT 'A',i1,i1 FROM t0;
INSERT INTO t1 SELECT 'B',i1,i1 FROM t0;
INSERT INTO t1 SELECT 'C',i1,i1 FROM t0;
INSERT INTO t1 SELECT 'D',i1,i1 FROM t0;
INSERT INTO t1 SELECT 'E',i1,i1 FROM t0;
INSERT INTO t1 SELECT 'F',i1,i1 FROM t0;
CREATE TABLE t2 (
c1 CHAR(1) NOT NULL,
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
UNIQUE KEY k2 (c1,i1,i2)
) charset utf8mb4 ENGINE=InnoDB;
INSERT INTO t2 SELECT 'A',i1,i1 FROM t0;
INSERT INTO t2 SELECT 'B',i1,i1 FROM t0;
INSERT INTO t2 SELECT 'C',i1,i1 FROM t0;
INSERT INTO t2 SELECT 'D',i1,i1 FROM t0;
INSERT INTO t2 SELECT 'E',i1,i1 FROM t0;
INSERT INTO t2 SELECT 'F',i1,i1 FROM t0;
ANALYZE TABLE t1;
ANALYZE TABLE t2;
set optimizer_trace_max_mem_size=1048576;
set @@session.optimizer_trace='enabled=on';
set end_markers_in_json=on;
EXPLAIN SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' AND i2 = 17) OR ( c1 = 'F')
GROUP BY c1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k1	k1	8	NULL	ROWS	100.00	Using where; Using index
Warnings:
Note	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`
SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' AND i2 = 17) OR ( c1 = 'F')
GROUP BY c1;
c1	max(i2)
C	17
F	30
SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query'
AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
OK
1
EXPLAIN SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR ( c1 = 'F' AND i2 = 17))
GROUP BY c1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k1	k1	8	NULL	ROWS	100.00	Using where; Using index
Warnings:
Note	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`
SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR ( c1 = 'F' AND i2 = 17))
GROUP BY c1;
c1	max(i2)
C	30
F	17
SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query'
AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
OK
1
EXPLAIN SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR c1 = 'F' ) AND ( i2 = 17 )
GROUP BY c1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k1	k1	8	NULL	ROWS	100.00	Using where; Using index for group-by
Warnings:
Note	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`
SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR c1 = 'F' ) AND ( i2 = 17 )
GROUP BY c1;
c1	max(i2)
C	17
F	17
SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query'
AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
OK
0
EXPLAIN SELECT c1, max(i2) FROM t1 
WHERE ((c1 = 'C' AND (i2 = 40 OR i2 = 30)) OR ( c1 = 'F' AND (i2 = 40 )))
GROUP BY c1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k1	k1	8	NULL	ROWS	100.00	Using where; Using index
Warnings:
Note	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`
SELECT c1, max(i2) FROM t1 
WHERE ((c1 = 'C' AND (i2 = 40 OR i2 = 30)) OR ( c1 = 'F' AND (i2 = 40 )))
GROUP BY c1;
c1	max(i2)
C	30
SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query'
AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
OK
1
EXPLAIN SELECT c1, i1, max(i2) FROM t2
WHERE (c1 = 'C' OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 )
GROUP BY c1,i1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	range	k2	k2	8	NULL	ROWS	10.00	Using where; Using index
Warnings:
Note	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`
SELECT c1, i1, max(i2) FROM t2
WHERE (c1 = 'C' OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 )
GROUP BY c1,i1;
c1	i1	max(i2)
C	17	17
F	17	17
SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query'
AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
OK
0
EXPLAIN SELECT c1, i1, max(i2) FROM t2 
WHERE (((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 ))
GROUP BY c1,i1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	range	k2	k2	8	NULL	ROWS	10.00	Using where; Using index
Warnings:
Note	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`
SELECT c1, i1, max(i2) FROM t2 
WHERE (((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 ))
GROUP BY c1,i1;
c1	i1	max(i2)
C	17	17
F	17	17
SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query'
AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
OK
0
EXPLAIN SELECT c1, i1, max(i2) FROM t2 
WHERE ((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35) OR ( i2 = 17 ))
GROUP BY c1,i1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	k2	k2	12	NULL	ROWS	19.72	Using where; Using index
Warnings:
Note	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`
SELECT c1, i1, max(i2) FROM t2 
WHERE ((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35) OR ( i2 = 17 ))
GROUP BY c1,i1;
c1	i1	max(i2)
A	17	17
B	17	17
C	1	1
C	2	2
C	3	3
C	4	4
C	5	5
C	6	6
C	7	7
C	8	8
C	9	9
C	10	10
C	11	11
C	12	12
C	13	13
C	14	14
C	15	15
C	16	16
C	17	17
C	18	18
C	19	19
C	20	20
C	21	21
C	22	22
C	23	23
C	24	24
C	25	25
C	26	26
C	27	27
C	28	28
C	29	29
C	30	30
D	17	17
E	17	17
F	1	1
F	2	2
F	3	3
F	4	4
F	5	5
F	6	6
F	7	7
F	8	8
F	9	9
F	10	10
F	11	11
F	12	12
F	13	13
F	14	14
F	15	15
F	16	16
F	17	17
F	18	18
F	19	19
F	20	20
F	21	21
F	22	22
F	23	23
F	24	24
F	25	25
F	26	26
F	27	27
F	28	28
F	29	29
F	30	30
SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query'
AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
OK
1
SET optimizer_trace_max_mem_size=DEFAULT;
SET optimizer_trace=DEFAULT;
SET end_markers_in_json=DEFAULT;
DROP TABLE t0,t1,t2;
#
# Bug #21749123: SELECT DISTINCT, WRONG RESULTS COMBINED WITH
#                USE_INDEX_EXTENSIONS=OFF
#
CREATE TABLE t1 (
pk_col INT AUTO_INCREMENT PRIMARY KEY,
a1 CHAR(64),
KEY a1_idx (a1)
) charset utf8mb4 ENGINE=INNODB;
INSERT INTO t1 (a1) VALUES ('a'),('a'),('a'),('a'), ('a');
CREATE TABLE t2 (
pk_col1 INT NOT NULL,
pk_col2 INT NOT NULL,
a1 CHAR(64),
a2 CHAR(64),
PRIMARY KEY(pk_col1, pk_col2),
KEY a1_idx (a1),
KEY a1_a2_idx (a1, a2)
) charset utf8mb4 ENGINE=INNODB;
INSERT INTO t2 (pk_col1, pk_col2, a1, a2) VALUES (1,1,'a','b'),(1,2,'a','b'),
(1,3,'a','c'),(1,4,'a','c'),
(2,1,'a','d');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
ANALYZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	OK
EXPLAIN SELECT DISTINCT a1
FROM t1
WHERE (pk_col = 2 OR pk_col = 22) AND a1 = 'a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,a1_idx	a1_idx	261	NULL	2	100.00	Using where; Using index
Warnings:
Note	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)))
SELECT DISTINCT a1
FROM t1
WHERE (pk_col = 2 OR pk_col = 22) AND a1 = 'a';
a1
a
EXPLAIN SELECT COUNT(DISTINCT a1)
FROM t1
GROUP BY a1,pk_col;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	a1_idx	a1_idx	261	NULL	6	100.00	Using index for group-by (scanning)
Warnings:
Note	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`
SELECT COUNT(DISTINCT a1)
FROM t1
GROUP BY a1,pk_col;
COUNT(DISTINCT a1)
1
1
1
1
1
EXPLAIN SELECT COUNT(DISTINCT a1)
FROM t2
GROUP BY a1,pk_col1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	range	a1_idx,a1_a2_idx	a1_idx	261	NULL	3	100.00	Using index for group-by
Warnings:
Note	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`
SELECT COUNT(DISTINCT a1)
FROM t2
GROUP BY a1,pk_col1;
COUNT(DISTINCT a1)
1
1
EXPLAIN SELECT COUNT(DISTINCT a1)
FROM t2
GROUP BY a1,a2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	range	a1_a2_idx	a1_a2_idx	514	NULL	4	100.00	Using index for group-by (scanning)
Warnings:
Note	1003	/* select#1 */ select count(distinct `test`.`t2`.`a1`) AS `COUNT(DISTINCT a1)` from `test`.`t2` group by `test`.`t2`.`a1`,`test`.`t2`.`a2`
SELECT COUNT(DISTINCT a1)
FROM t2
GROUP BY a1,a2;
COUNT(DISTINCT a1)
1
1
1
SET @optimizer_switch_save=@@optimizer_switch;
SET @@optimizer_switch= "use_index_extensions=off";
EXPLAIN SELECT DISTINCT a1
FROM t1
WHERE (pk_col = 2 OR pk_col = 22) AND a1 = 'a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	PRIMARY,a1_idx	a1_idx	257	const	5	40.00	Using where; Using index
Warnings:
Note	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)))
SELECT DISTINCT a1
FROM t1
WHERE (pk_col = 2 OR pk_col = 22) AND a1 = 'a';
a1
a
EXPLAIN SELECT COUNT(DISTINCT a1)
FROM t1
GROUP BY a1,pk_col;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	a1_idx	a1_idx	257	NULL	5	100.00	Using index
Warnings:
Note	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`
SELECT COUNT(DISTINCT a1)
FROM t1
GROUP BY a1,pk_col;
COUNT(DISTINCT a1)
1
1
1
1
1
EXPLAIN SELECT COUNT(DISTINCT a1)
FROM t2
GROUP BY a1,pk_col1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	a1_idx,a1_a2_idx	a1_idx	257	NULL	5	100.00	Using index
Warnings:
Note	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`
SELECT COUNT(DISTINCT a1)
FROM t2
GROUP BY a1,pk_col1;
COUNT(DISTINCT a1)
1
1
EXPLAIN SELECT COUNT(DISTINCT a1)
FROM t2
GROUP BY a1,a2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	range	a1_a2_idx	a1_a2_idx	514	NULL	4	100.00	Using index for group-by (scanning)
Warnings:
Note	1003	/* select#1 */ select count(distinct `test`.`t2`.`a1`) AS `COUNT(DISTINCT a1)` from `test`.`t2` group by `test`.`t2`.`a1`,`test`.`t2`.`a2`
SELECT COUNT(DISTINCT a1)
FROM t2
GROUP BY a1,a2;
COUNT(DISTINCT a1)
1
1
1
SET @@optimizer_switch= @optimizer_switch_save;
DROP TABLE t1, t2;
#
# Bug #24671968: WHEN THE OPTIMISER IS USING INDEX FOR GROUP-BY IT OFTEN
#                OFTEN GIVES WRONG RESULTS
#
CREATE TABLE t1 (
id int NOT NULL,
c1 int NOT NULL,
c2 int,
PRIMARY KEY(id),
INDEX c1_c2_idx(c1, c2));
INSERT INTO t1 (id, c1, c2) VALUES (1,1,1), (2,2,2), (10,10,1), (11,10,8),
(12,10,1), (13,10,2);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN SELECT DISTINCT c1
FROM t1
WHERE EXISTS (SELECT *
FROM DUAL
WHERE (c2 = 2));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	index	c1_c2_idx	c1_c2_idx	9	NULL	6	100.00	Using where; Using index
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1276	Field or reference 'test.t1.c2' of SELECT #2 was resolved in SELECT #1
Note	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))
EXPLAIN SELECT DISTINCT c1
FROM t1
WHERE 1 IN (2,
(SELECT 1
FROM DUAL
WHERE (c2 = 2)),
3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	index	c1_c2_idx	c1_c2_idx	9	NULL	6	100.00	Using where; Using index
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1276	Field or reference 'test.t1.c2' of SELECT #2 was resolved in SELECT #1
Note	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))
EXPLAIN SELECT DISTINCT c1
FROM t1
WHERE c2 = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	c1_c2_idx	c1_c2_idx	9	NULL	4	100.00	Using where; Using index for group-by
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (`test`.`t1`.`c2` = 2)
EXPLAIN SELECT DISTINCT c1
FROM t1 IGNORE INDEX (c1_c2_idx)
WHERE EXISTS (SELECT *
FROM DUAL
WHERE (c2 = 2));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using temporary
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1276	Field or reference 'test.t1.c2' of SELECT #2 was resolved in SELECT #1
Note	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))
EXPLAIN SELECT DISTINCT c1
FROM t1 IGNORE INDEX (c1_c2_idx)
WHERE 1 IN (2,
(SELECT 1
FROM DUAL
WHERE (c2 = 2)),
3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using temporary
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1276	Field or reference 'test.t1.c2' of SELECT #2 was resolved in SELECT #1
Note	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))
SET optimizer_trace="enabled=on";
SELECT DISTINCT c1
FROM t1
WHERE EXISTS (SELECT *
FROM DUAL
WHERE (c2 = 2));
c1
2
10
SELECT TRACE INTO @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SELECT @trace RLIKE "keypart_reference_from_where_clause";
@trace RLIKE "keypart_reference_from_where_clause"
1
SET optimizer_trace="enabled=off";
SELECT DISTINCT c1
FROM t1
WHERE 1 IN (2,
(SELECT 1
FROM DUAL
WHERE (c2 = 2)),
3);
c1
2
10
SELECT DISTINCT c1
FROM t1
WHERE c2 = 2;
c1
2
10
SELECT DISTINCT c1
FROM t1 IGNORE INDEX (c1_c2_idx)
WHERE EXISTS (SELECT *
FROM DUAL
WHERE (c2 = 2));
c1
2
10
SELECT DISTINCT c1
FROM t1 IGNORE INDEX (c1_c2_idx)
WHERE 1 IN (2,
(SELECT 1
FROM DUAL
WHERE (c2 = 2)),
3);
c1
2
10
DROP TABLE t1;
#
# Bug #26532061: SELECT DISTINCT WITH SECONDARY KEY FOR
#                'USING INDEX FOR GROUP-BY' BAD RESULTS
#
CREATE TABLE t1(
pk INT NOT NULL,
c1 CHAR(2),
c2 INT,
PRIMARY KEY(pk),
UNIQUE KEY ukey(c1, c2)
);
INSERT INTO t1(pk, c1, c2) VALUES (1,1,1),(2,2,2),(3,3,3),(4,5,4);
SET @a:=5;
INSERT IGNORE INTo t1(pk, c1, c2)
SELECT (@a:=@a+1),@a,@a FROM t1, t1 t2,t1 t3, t1 t4;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SELECT * FROM t1 WHERE pk = 1 OR pk = 231;
pk	c1	c2
1	1	1
231	23	231
EXPLAIN SELECT DISTINCT c1
FROM t1 FORCE INDEX(ukey)
WHERE pk IN (1,231) and c1 IS NOT NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	ukey	ukey	14	NULL	260	20.00	Using where; Using index
Warnings:
Note	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))
EXPLAIN SELECT DISTINCT c1
FROM t1 IGNORE INDEX(ukey)
WHERE pk IN (1,231) and c1 IS NOT NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY	PRIMARY	4	NULL	2	90.00	Using where; Using temporary
Warnings:
Note	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))
SELECT DISTINCT c1
FROM t1 FORCE INDEX(ukey)
WHERE pk IN (1,231) and c1 IS NOT NULL;
c1
1
23
SELECT DISTINCT c1
FROM t1 IGNORE INDEX(ukey)
WHERE pk IN (1,231) and c1 IS NOT NULL;
c1
1
23
DROP TABLE t1;
#
# Bug #25989915: LOOSE INDEX SCANS RETURNING WRONG RESULT
#
CREATE TABLE t1 (
pk INT NOT NULL AUTO_INCREMENT,
c1 varchar(100) DEFAULT NULL,
c2 INT NOT NULL,
PRIMARY KEY (pk),
UNIQUE KEY ukey (c2,c1)
);
INSERT INTO t1(pk, c2) VALUES (100, 0), (101, 0), (102, 0), (103, 0);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN SELECT COUNT(DISTINCT(c2)) FROM t1 WHERE pk IN (102, 101);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY,ukey	ukey	407	NULL	4	50.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(distinct `test`.`t1`.`c2`) AS `COUNT(DISTINCT(c2))` from `test`.`t1` where (`test`.`t1`.`pk` in (102,101))
EXPLAIN SELECT COUNT(DISTINCT(c2)) FROM t1 WHERE pk IN (102, 100);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY,ukey	ukey	407	NULL	4	50.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(distinct `test`.`t1`.`c2`) AS `COUNT(DISTINCT(c2))` from `test`.`t1` where (`test`.`t1`.`pk` in (102,100))
SELECT COUNT(DISTINCT(c2)) FROM t1 WHERE pk IN (102, 101);
COUNT(DISTINCT(c2))
1
SELECT COUNT(DISTINCT(c2)) FROM t1 WHERE pk IN (102, 100);
COUNT(DISTINCT(c2))
1
DROP TABLE t1;