Edit File - File Manager

Edit File: join_optimizer.test

--disable_warnings
drop table if exists t0,t1,t2,t3;
--enable_warnings

--echo #
--echo # BUG#38049 incorrect rows estimations with references from preceding table
--echo #

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (a varchar(32));
insert into t1 values ('owner'),('requester'),('admincc'),('cc');

CREATE TABLE t2 (
  id int(11) NOT NULL,
  type varchar(32) default NULL,
  PRIMARY KEY  (id)
);
insert into t2 values (1,'owner'), (2,'admincc');

CREATE TABLE t3 (
  id int(11) NOT NULL,
  domain varchar(32) default NULL,
  type varchar(32) default NULL,
  PRIMARY KEY  (id)
);

set @domain='system';
set @pk=0;
INSERT INTO t3 select @pk:=@pk+1, 'system', t1.a from t1;
INSERT INTO t3 select @pk:=@pk+1, 'queue', t1.a from t1, t0 where t0.a<3;
INSERT INTO t3 select @pk:=@pk+1, 'ticket', t1.a from t1, t0 A, t0 B, t0 C;

CREATE INDEX groups_d ON t3(domain);
CREATE INDEX groups_t ON t3(type);
CREATE INDEX groups_td ON t3(type, domain);
CREATE INDEX groups_dt ON t3(domain, type);
--echo For table g this must use ref(groups_dt) and #rows should be around 15 and not 335:
explain 
SELECT STRAIGHT_JOIN g.id FROM t2 a, t3 g USE INDEX(groups_dt) 
WHERE g.domain = 'queue' AND g.type = a.type;

drop table t0,t1,t2,t3;

Back to File Manager