1drop table if exists t0,t1,t2,t3;
2#
3# BUG#38049 incorrect rows estimations with references from preceding table
4#
5create table t0 (a int);
6insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
7create table t1 (a varchar(32));
8insert into t1 values ('owner'),('requester'),('admincc'),('cc');
9CREATE TABLE t2 (
10id int(11) NOT NULL,
11type varchar(32) default NULL,
12PRIMARY KEY  (id)
13);
14insert into t2 values (1,'owner'), (2,'admincc');
15CREATE TABLE t3 (
16id int(11) NOT NULL,
17domain varchar(32) default NULL,
18type varchar(32) default NULL,
19PRIMARY KEY  (id)
20);
21set @domain='system';
22set @pk=0;
23INSERT INTO t3 select @pk:=@pk+1, 'system', t1.a from t1;
24INSERT INTO t3 select @pk:=@pk+1, 'queue', t1.a from t1, t0 where t0.a<3;
25INSERT INTO t3 select @pk:=@pk+1, 'ticket', t1.a from t1, t0 A, t0 B, t0 C;
26CREATE INDEX groups_d ON t3(domain);
27CREATE INDEX groups_t ON t3(type);
28CREATE INDEX groups_td ON t3(type, domain);
29CREATE INDEX groups_dt ON t3(domain, type);
30For table g this must use ref(groups_dt) and #rows should be around 15 and not 335:
31explain
32SELECT STRAIGHT_JOIN g.id FROM t2 a, t3 g USE INDEX(groups_dt)
33WHERE g.domain = 'queue' AND g.type = a.type;
34id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
351	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	2	Using where
361	SIMPLE	g	ref	groups_dt	groups_dt	70	const,test.a.type	13	Using index condition
37drop table t0,t1,t2,t3;
38