1# 2# MDEV-16917: do not use splitting for derived with join cache 3# 4CREATE TABLE t1 ( 5n1 int(10) NOT NULL, 6n2 int(10) NOT NULL, 7c1 char(1) NOT NULL, 8KEY c1 (c1), 9KEY n1_c1_n2 (n1,c1,n2) 10) ENGINE=InnoDB; 11INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a'); 12insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000; 13ANALYZE TABLE t1; 14Table Op Msg_type Msg_text 15test.t1 analyze status Engine-independent statistics collected 16test.t1 analyze status OK 17EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t 18WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; 19id select_type table type possible_keys key key_len ref rows Extra 201 PRIMARY t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort 211 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 2 222 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index 23SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t 24WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; 25n1 260 271 28DROP TABLE t1; 29# 30# MDEV-17211: splittable materialized derived joining 3 tables with 31# GROUP BY list containing fields from 2 of them 32# 33CREATE TABLE t1 ( 34id1 int, i1 int, id2 int, 35PRIMARY KEY (id1), KEY (i1), KEY (id2) 36) ENGINE=InnoDB; 37INSERT INTO t1 VALUES (1,1,1); 38CREATE TABLE t2 (id2 int, i2 int) ENGINE=InnoDB; 39INSERT INTO t2 VALUES (1, 1); 40CREATE TABLE t3 (id3 int, i3 int, PRIMARY KEY (id3)) ENGINE=InnoDB; 41INSERT INTO t3 VALUES (1,1); 42EXPLAIN SELECT id3 43FROM (SELECT t3.id3, t2.i2, t1.id2 FROM t3,t1,t2 44WHERE t3.i3=t1.id1 AND t2.id2=t1.id2 45GROUP BY t3.id3, t1.id2) AS t, 46t2 47WHERE t2.id2=t.id2; 48id select_type table type possible_keys key key_len ref rows Extra 491 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 501 PRIMARY <derived2> ref key0 key0 5 test.t2.id2 2 512 DERIVED t3 ALL NULL NULL NULL NULL 1 Using where; Using temporary; Using filesort 522 DERIVED t1 eq_ref PRIMARY,id2 PRIMARY 4 test.t3.i3 1 532 DERIVED t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) 54SELECT id3 55FROM (SELECT t3.id3, t2.i2, t1.id2 FROM t3,t1,t2 56WHERE t3.i3=t1.id1 AND t2.id2=t1.id2 57GROUP BY t3.id3, t1.id2) AS t, 58t2 59WHERE t2.id2=t.id2; 60id3 611 62DROP TABLE t1,t2,t3; 63# 64# Bug mdev-17381: equi-join of derived table with join_cache_level=4 65# 66CREATE TABLE t1 ( 67id int NOT NULL, 68amount decimal DEFAULT NULL, 69PRIMARY KEY (id) 70) ENGINE=INNODB; 71CREATE TABLE t2 ( 72id int NOT NULL, 73name varchar(50) DEFAULT NULL, 74PRIMARY KEY (id) 75) ENGINE=INNODB; 76INSERT INTO t1 VALUES 77(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), 78(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); 79INSERT INTO t2 VALUES 80(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), 81(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); 82set join_cache_level=4; 83SELECT t2.id,t2.name,t.total_amt 84FROM t2 85LEFT JOIN 86(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t 87ON t2.id=t.id 88WHERE t2.id < 3; 89id name total_amt 901 A 10 912 B 20 92EXPLAIN SELECT t2.id,t2.name,t.total_amt 93FROM t2 94LEFT JOIN 95(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t 96ON t2.id=t.id 97WHERE t2.id < 3; 98id select_type table type possible_keys key key_len ref rows Extra 991 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 Using where 1001 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 1012 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 102set join_cache_level=default; 103DROP TABLE t1,t2; 104# 105# Bug mdev-18467: join of grouping view and a base table as inner operand 106# of left join with on condition containing impossible range 107# 108create table t1 (f1 int, f2 int, key(f2)) engine=InnoDB; 109insert into t1 values (3,33), (7,77), (1,11); 110create table t2 (f1 int, f2 int, primary key (f1)) engine=InnoDB; 111insert into t2 values (3,33), (9,99), (1,11); 112create view v1 as 113select f1, max(f2) as f2 from t2 group by f1; 114select t.f2 115from t1 116left join 117(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null) 118on t1.f1=t.f1; 119f2 120NULL 121NULL 122NULL 123explain select t.f2 124from t1 125left join 126(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null) 127on t1.f1=t.f1; 128id select_type table type possible_keys key key_len ref rows Extra 1291 PRIMARY t const f2 NULL NULL NULL 1 Impossible ON condition 1301 PRIMARY <derived2> const key1 NULL NULL NULL 1 Impossible ON condition 1311 PRIMARY t1 ALL NULL NULL NULL NULL 3 1322 DERIVED t2 ALL PRIMARY NULL NULL NULL 3 Using temporary; Using filesort 133set statement optimizer_switch='split_materialized=off' for explain select t.f2 134from t1 135left join 136(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null) 137on t1.f1=t.f1; 138id select_type table type possible_keys key key_len ref rows Extra 1391 PRIMARY t const f2 NULL NULL NULL 1 Impossible ON condition 1401 PRIMARY <derived3> const key1 NULL NULL NULL 1 Impossible ON condition 1411 PRIMARY t1 ALL NULL NULL NULL NULL 3 1423 DERIVED t2 index NULL PRIMARY 4 NULL 3 143drop view v1; 144drop table t1,t2; 145# 146# MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived table subject to split 147# 148CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b)) ENGINE=InnoDB; 149CREATE TABLE t2 (c INT, KEY (c)) ENGINE=InnoDB; 150SELECT * FROM t1 t1a JOIN t1 t1b; 151a b a b 152INSERT INTO t2 VALUES (1),(2); 153INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12); 154set statement optimizer_switch='split_materialized=off' for EXPLAIN 155SELECT * 156FROM 157t1 JOIN 158(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt 159WHERE 160t1.a = dt.a; 161id select_type table type possible_keys key key_len ref rows Extra 1621 PRIMARY t1 index a,a_2 a_2 10 NULL 6 Using where; Using index 1631 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 1643 DERIVED t1 index NULL a_2 10 NULL 6 Using where; Using index 1653 DERIVED t2 ref c c 5 test.t1.b 1 Using index 166set statement optimizer_switch='split_materialized=on' for EXPLAIN 167SELECT * 168FROM 169t1 JOIN 170(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt 171WHERE 172t1.a = dt.a; 173id select_type table type possible_keys key key_len ref rows Extra 1741 PRIMARY t1 index a,a_2 a_2 10 NULL 6 Using where; Using index 1751 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 1763 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort 1773 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index 178DROP TABLE t1, t2; 179# 180# Bug mdev-25714: usage non-splitting covering index is cheaper than 181# usage of the best splitting index for one group 182# 183create table t1 ( 184id int not null, itemid int not null, index idx (itemid) 185) engine=innodb; 186insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); 187create table t2 (id int not null) engine=innodb; 188insert into t2 values (2); 189create table t3 ( 190id int not null, itemid int not null, userid int not null, primary key (id), 191index idx1 (userid, itemid), index idx2 (itemid) 192) engine innodb; 193insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); 194set use_stat_tables='never'; 195set optimizer_use_condition_selectivity=1; 196analyze table t1,t2,t3; 197Table Op Msg_type Msg_text 198test.t1 analyze status OK 199test.t2 analyze status OK 200test.t3 analyze status OK 201set optimizer_switch='split_materialized=on'; 202explain select t1.id, t1.itemid, dt.id, t2.id 203from t1, 204(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, 205t2 206where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; 207id select_type table type possible_keys key key_len ref rows Extra 2081 PRIMARY t2 ALL NULL NULL NULL NULL 1 2091 PRIMARY <derived2> ref key1 key1 4 test.t2.id 2 2101 PRIMARY t1 ALL idx NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 2112 DERIVED t3 ref idx1,idx2 idx1 4 const 5 Using where; Using index 212select t1.id, t1.itemid, dt.id, t2.id 213from t1, 214(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, 215t2 216where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; 217id itemid id id 2184 2 4 2 2194 2 4 2 220set optimizer_switch='split_materialized=off'; 221explain select t1.id, t1.itemid, dt.id, t2.id 222from t1, 223(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, 224t2 225where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; 226id select_type table type possible_keys key key_len ref rows Extra 2271 PRIMARY t2 ALL NULL NULL NULL NULL 1 2281 PRIMARY <derived2> ref key1 key1 4 test.t2.id 2 2291 PRIMARY t1 ALL idx NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 2302 DERIVED t3 ref idx1 idx1 4 const 5 Using where; Using index 231select t1.id, t1.itemid, dt.id, t2.id 232from t1, 233(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, 234t2 235where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; 236id itemid id id 2374 2 4 2 2384 2 4 2 239drop table t1,t2,t3; 240set optimizer_switch='split_materialized=default'; 241set use_stat_tables=default; 242set optimizer_use_condition_selectivity=default; 243# 244# MDEV-26337: subquery with groupby and ROLLUP returns incorrect results 245# (The testcase is taken from testcase for MDEV-13389 due to it being 246# much smaller) 247# 248create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam; 249insert into t3 values 250(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'), 251(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'), 252(5,14,'dd'), (9,12,'ee'); 253create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam; 254insert into t4 values 255(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), 256(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'), 257(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'), 258(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); 259insert into t4 select a+10, b+10, concat(c,'f') from t4; 260analyze table t3,t4; 261Table Op Msg_type Msg_text 262test.t3 analyze status Engine-independent statistics collected 263test.t3 analyze status OK 264test.t4 analyze status Engine-independent statistics collected 265test.t4 analyze status OK 266# This should use a plan with LATERAL DERIVED: 267explain select t3.a,t3.c,t.max,t.min 268from t3 join 269(select a, c, max(b) max, min(b) min from t4 group by a,c) t 270on t3.a=t.a and t3.c=t.c 271where t3.b > 15; 272id select_type table type possible_keys key key_len ref rows Extra 2731 PRIMARY t3 range idx_b idx_b 5 NULL 2 Using index condition; Using where 2741 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 2 2752 LATERAL DERIVED t4 ref idx idx 133 test.t3.a,test.t3.c 1 276# ... and if one adds WITH ROLLUP, then LATERAL DERIVED is no longer used: 277explain select t3.a,t3.c,t.max,t.min 278from t3 join 279(select a, c, max(b) max, min(b) min from t4 group by a,c with rollup) t 280on t3.a=t.a and t3.c=t.c 281where t3.b > 15; 282id select_type table type possible_keys key key_len ref rows Extra 2831 PRIMARY t3 range idx_b idx_b 5 NULL 2 Using index condition; Using where 2841 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 4 2852 DERIVED t4 ALL NULL NULL NULL NULL 40 Using filesort 286drop table t3, t4; 287# End of 10.3 tests 288