SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;
SET GLOBAL innodb_stats_persistent=0;
#
# MDEV-16917: do not use splitting for derived with join cache
#
CREATE TABLE t1 (
n1 int(10) NOT NULL,
n2 int(10) NOT NULL,
c1 char(1) NOT NULL,
KEY c1 (c1),
KEY n1_c1_n2 (n1,c1,n2)
) ENGINE=InnoDB CHARSET=latin1;
INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a');
insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t
WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ref	c1,n1_c1_n2	c1	1	const	2	Using index condition; Using where; Using temporary; Using filesort
1	PRIMARY	<derived2>	ref	key0	key0	8	test.t1.n1,test.t1.n2	1	
2	LATERAL DERIVED	t1	ref	c1,n1_c1_n2	n1_c1_n2	4	test.t1.n1	1	Using where; Using index
SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t
WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
n1
0
1
DROP TABLE t1;
#
# MDEV-17211: splittable materialized derived joining 3 tables with
#             GROUP BY list containing fields from 2 of them
#
CREATE TABLE t1 (
id1 int, i1 int, id2 int,
PRIMARY KEY (id1), KEY (i1), KEY (id2)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,1,1);
CREATE TABLE t2 (id2 int, i2 int) ENGINE=InnoDB;
INSERT INTO t2  VALUES (1, 1);
CREATE TABLE t3 (id3 int, i3 int, PRIMARY KEY (id3)) ENGINE=InnoDB;
INSERT INTO t3 VALUES (1,1);
EXPLAIN SELECT id3
FROM (SELECT t3.id3, t2.i2, t1.id2  FROM t3,t1,t2
WHERE t3.i3=t1.id1 AND t2.id2=t1.id2
GROUP BY t3.id3, t1.id2) AS t,
t2
WHERE t2.id2=t.id2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id2	1	
2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using temporary; Using filesort
2	DERIVED	t1	eq_ref	PRIMARY,id2	PRIMARY	4	test.t3.i3	1	
2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
SELECT id3
FROM (SELECT t3.id3, t2.i2, t1.id2  FROM t3,t1,t2
WHERE t3.i3=t1.id1 AND t2.id2=t1.id2
GROUP BY t3.id3, t1.id2) AS t,
t2
WHERE t2.id2=t.id2;
id3
1
DROP TABLE t1,t2,t3;
#
# Bug mdev-17381: equi-join of derived table with join_cache_level=4
#
CREATE TABLE t1 (
id int NOT NULL,
amount decimal DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE t2 (
id int NOT NULL,
name varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
INSERT INTO t1 VALUES
(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);
INSERT INTO t2 VALUES
(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL),
(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL);
set join_cache_level=4;
SELECT t2.id,t2.name,t.total_amt
FROM  t2
LEFT JOIN
(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
ON t2.id=t.id
WHERE t2.id < 3;
id	name	total_amt
1	A	10
2	B	20
EXPLAIN SELECT t2.id,t2.name,t.total_amt
FROM  t2
LEFT JOIN
(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
ON t2.id=t.id
WHERE t2.id < 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id	1	
2	LATERAL DERIVED	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.id	1	
set join_cache_level=default;
DROP TABLE t1,t2;
#
# Bug mdev-18467: join of grouping view and a base table as inner operand
#                 of left join with on condition containing impossible range
#
create table t1 (f1 int, f2 int, key(f2)) engine=InnoDB;
insert into t1 values (3,33), (7,77), (1,11);
create table t2 (f1 int, f2 int, primary key (f1)) engine=InnoDB;
insert into t2 values (3,33), (9,99), (1,11);
create view v1 as
select f1, max(f2) as f2 from t2 group by f1;
select t.f2
from t1
left join
(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null)
on t1.f1=t.f1;
f2
NULL
NULL
NULL
explain select t.f2
from t1
left join
(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null)
on t1.f1=t.f1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t	const	f2	NULL	NULL	NULL	0	Impossible ON condition
1	PRIMARY	<derived2>	const	key0,key1	NULL	NULL	NULL	0	Impossible ON condition
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
2	DERIVED	t2	ALL	PRIMARY	NULL	NULL	NULL	3	Using temporary; Using filesort
set statement optimizer_switch='split_materialized=off' for explain select t.f2
from t1
left join
(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null)
on t1.f1=t.f1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t	const	f2	NULL	NULL	NULL	0	Impossible ON condition
1	PRIMARY	<derived3>	const	key0,key1	NULL	NULL	NULL	0	Impossible ON condition
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
3	DERIVED	t2	index	NULL	PRIMARY	4	NULL	3	
drop view v1;
drop table t1,t2;
#
#  MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived table subject to split
#
CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b)) ENGINE=InnoDB;
CREATE TABLE t2 (c INT, KEY (c)) ENGINE=InnoDB;
SELECT * FROM t1 t1a JOIN t1 t1b;
a	b	a	b
INSERT INTO t2 VALUES (1),(2);
INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12);
set statement optimizer_switch='split_materialized=off' for EXPLAIN
SELECT *
FROM
t1 JOIN
(SELECT t1_inner.a, t1_inner.b FROM t1 as t1_inner, t2 as t2_inner WHERE t1_inner.b = t2_inner.c GROUP BY t1_inner.a, t1_inner.b) as dt
WHERE
t1.a = dt.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	a,a_2	a_2	10	NULL	6	Using where; Using index
1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	1	
3	DERIVED	t1_inner	index	NULL	a_2	10	NULL	6	Using where; Using index
3	DERIVED	t2_inner	ref	c	c	5	test.t1_inner.b	1	Using index
set statement optimizer_switch='split_materialized=on' for  EXPLAIN
SELECT *
FROM
t1 JOIN
(SELECT t1_inner.a, t1_inner.b FROM t1 as t1_inner, t2 as t2_inner WHERE t1_inner.b = t2_inner.c GROUP BY t1_inner.a, t1_inner.b) as dt
WHERE
t1.a = dt.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index	a,a_2	a_2	10	NULL	6	Using where; Using index
1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	1	
3	DERIVED	t1_inner	index	a,a_2	a_2	10	NULL	6	Using where; Using index
3	DERIVED	t2_inner	ref	c	c	5	test.t1_inner.b	1	Using index
DROP TABLE t1, t2;
#
# Bug mdev-25714: usage non-splitting covering index is cheaper than
#                 usage of the best splitting index for one group
#
create table t1 (
id int not null, itemid int not null, index idx (itemid)
) engine=innodb;
insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3);
create table t2 (id int not null) engine=innodb;
insert into t2 values (2);
create table t3 (
id int not null, itemid int not null, userid int not null, primary key (id),
index idx1 (userid, itemid), index idx2 (itemid)
) engine innodb;
insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1);
set use_stat_tables='never';
set optimizer_use_condition_selectivity=1;
analyze table t1,t2,t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
set optimizer_switch='split_materialized=on';
explain select t1.id, t1.itemid, dt.id, t2.id
from t1,
(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt,
t2
where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
1	PRIMARY	<derived2>	ref	key1	key1	4	test.t2.id	1	
1	PRIMARY	t1	ref	idx	idx	4	test.t2.id	3	Using where
2	DERIVED	t3	ref	idx1,idx2	idx1	4	const	5	Using where; Using index
select t1.id, t1.itemid, dt.id, t2.id
from t1,
(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt,
t2
where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
id	itemid	id	id
4	2	4	2
4	2	4	2
set optimizer_switch='split_materialized=off';
explain select t1.id, t1.itemid, dt.id, t2.id
from t1,
(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt,
t2
where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
1	PRIMARY	<derived2>	ref	key1	key1	4	test.t2.id	1	
1	PRIMARY	t1	ref	idx	idx	4	test.t2.id	3	Using where
2	DERIVED	t3	ref	idx1	idx1	4	const	5	Using where; Using index
select t1.id, t1.itemid, dt.id, t2.id
from t1,
(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt,
t2
where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
id	itemid	id	id
4	2	4	2
4	2	4	2
drop table t1,t2,t3;
set optimizer_switch='split_materialized=default';
set use_stat_tables=default;
set optimizer_use_condition_selectivity=default;
#
# MDEV-26337: subquery with groupby and ROLLUP returns incorrect results 
#  (The testcase is taken from testcase for MDEV-13389 due to it being
#   much smaller)
#
create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam charset=latin1;
insert into t3 values
(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'),
(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'),
(5,14,'dd'), (9,12,'ee');
create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam charset=latin1;
insert into t4 values
(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'),
(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'),
(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'),
(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa');
insert into t4 select a+10, b+10, concat(c,'f') from t4;
analyze table t3,t4;
Table	Op	Msg_type	Msg_text
test.t3	analyze	status	Engine-independent statistics collected
test.t3	analyze	status	OK
test.t4	analyze	status	Engine-independent statistics collected
test.t4	analyze	status	OK
# This should use a plan with LATERAL DERIVED:
explain select t3.a,t3.c,t.max,t.min
from t3 join
(select a, c, max(b) max, min(b) min from t4 group by a,c) t
on t3.a=t.a and t3.c=t.c
where t3.b > 15;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	range	idx_b	idx_b	5	NULL	2	Using index condition; Using where
1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	1	
2	LATERAL DERIVED	t4	ref	idx	idx	133	test.t3.a,test.t3.c	1	
# ... and if one adds WITH ROLLUP, then LATERAL DERIVED is no longer used:
explain select t3.a,t3.c,t.max,t.min
from t3 join
(select a, c, max(b) max, min(b) min from t4 group by a,c with rollup) t
on t3.a=t.a and t3.c=t.c
where t3.b > 15;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	range	idx_b	idx_b	5	NULL	2	Using index condition; Using where
1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	2	
2	DERIVED	t4	ALL	NULL	NULL	NULL	NULL	40	Using filesort
drop table t3, t4;
# End of 10.3 tests
#
# MDEV-26301: Split optimization refills temporary table too many times
#
create table t1(a int, b int);
insert into t1 select seq,seq from seq_1_to_5;
create table t2(a int, b int, key(a));
insert into t2
select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
create table t3(a int, b int, key(a));
insert into t3
select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
analyze table t1,t2,t3 persistent for all;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	Table is already up to date
test.t3	analyze	status	Engine-independent statistics collected
test.t3	analyze	status	Table is already up to date
explain
select * from
(t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
1	SIMPLE	t2	ref	a	a	5	test.t1.b	2	Using where
1	SIMPLE	t3	ref	a	a	5	test.t1.b	3	Using where
create table t10 (
grp_id int,
col1 int,
key(grp_id)
);
insert into t10
select
A.seq,
B.seq
from
seq_1_to_100 A,
seq_1_to_100 B;
create table t11 (
col1 int,
col2 int
);
insert into t11
select A.seq, A.seq from seq_1_to_10 A;
analyze table t10,t11 persistent for all;
Table	Op	Msg_type	Msg_text
test.t10	analyze	status	Engine-independent statistics collected
test.t10	analyze	status	Table is already up to date
test.t11	analyze	status	Engine-independent statistics collected
test.t11	analyze	status	OK
explain select * from
(
(t1 left join t2 on t2.a=t1.b)
left join t3 on t3.a=t1.b
) left join (select grp_id, count(*)
from t10 left join t11 on t11.col1=t10.col1
group by grp_id) T on T.grp_id=t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
# The important part in the below output is:
#        "lateral": 1,
#        "query_block": {
#          "select_id": 2,
#          "r_loops": 5,  <-- must be 5, not 30.
analyze format=json select * from
(
(t1 left join t2 on t2.a=t1.b)
left join t3 on t3.a=t1.b
) left join (select grp_id, count(*)
from t10 left join t11 on t11.col1=t10.col1
group by grp_id) T on T.grp_id=t1.b;
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "const_condition": "1",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 5,
          "r_rows": 5,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": 100,
          "r_filtered": 100
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ref",
          "possible_keys": ["a"],
          "key": "a",
          "key_length": "5",
          "used_key_parts": ["a"],
          "ref": ["test.t1.b"],
          "loops": 5,
          "r_loops": 5,
          "rows": 2,
          "r_rows": 2,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": 100,
          "attached_condition": "trigcond(trigcond(t1.b is not null))",
          "r_filtered": 100
        }
      },
      {
        "table": {
          "table_name": "t3",
          "access_type": "ref",
          "possible_keys": ["a"],
          "key": "a",
          "key_length": "5",
          "used_key_parts": ["a"],
          "ref": ["test.t1.b"],
          "loops": 10,
          "r_loops": 10,
          "rows": 3,
          "r_rows": 3,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": 100,
          "attached_condition": "trigcond(trigcond(t1.b is not null))",
          "r_filtered": 100
        }
      },
      {
        "table": {
          "table_name": "<derived2>",
          "access_type": "ref",
          "possible_keys": ["key0"],
          "key": "key0",
          "key_length": "5",
          "used_key_parts": ["grp_id"],
          "ref": ["test.t1.b"],
          "loops": 30,
          "r_loops": 30,
          "rows": 10,
          "r_rows": 1,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "filtered": 100,
          "r_total_filtered": 100,
          "attached_condition": "trigcond(trigcond(t1.b is not null))",
          "r_filtered": 100,
          "materialized": {
            "lateral": 1,
            "query_block": {
              "select_id": 2,
              "cost": "REPLACED",
              "r_loops": 5,
              "r_total_time_ms": "REPLACED",
              "outer_ref_condition": "t1.b is not null",
              "nested_loop": [
                {
                  "table": {
                    "table_name": "t10",
                    "access_type": "ref",
                    "possible_keys": ["grp_id"],
                    "key": "grp_id",
                    "key_length": "5",
                    "used_key_parts": ["grp_id"],
                    "ref": ["test.t1.b"],
                    "loops": 1,
                    "r_loops": 5,
                    "rows": 100,
                    "r_rows": 100,
                    "cost": "REPLACED",
                    "r_table_time_ms": "REPLACED",
                    "r_other_time_ms": "REPLACED",
                    "r_engine_stats": REPLACED,
                    "filtered": 100,
                    "r_total_filtered": 100,
                    "r_filtered": 100
                  }
                },
                {
                  "block-nl-join": {
                    "table": {
                      "table_name": "t11",
                      "access_type": "ALL",
                      "loops": 100,
                      "r_loops": 5,
                      "rows": 10,
                      "r_rows": 10,
                      "cost": "REPLACED",
                      "r_table_time_ms": "REPLACED",
                      "r_other_time_ms": "REPLACED",
                      "r_engine_stats": REPLACED,
                      "filtered": 100,
                      "r_total_filtered": 100,
                      "r_filtered": 100
                    },
                    "buffer_type": "flat",
                    "buffer_size": "1Kb",
                    "join_type": "BNL",
                    "attached_condition": "trigcond(t11.col1 = t10.col1)",
                    "r_loops": 500,
                    "r_filtered": 10,
                    "r_unpack_time_ms": "REPLACED",
                    "r_other_time_ms": "REPLACED",
                    "r_effective_rows": 10
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}
create table t21 (pk int primary key);
insert into t21 values (1),(2),(3);
create table t22 (pk int primary key);
insert into t22 values (1),(2),(3);
explain
select * from
t21, t22,
(
(t1 left join t2 on t2.a=t1.b)
left join t3 on t3.a=t1.b
) left join (select grp_id, count(*)
from t10 left join t11 on t11.col1=t10.col1
group by grp_id) T on T.grp_id=t1.b
where
t21.pk=1 and t22.pk=2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t21	const	PRIMARY	PRIMARY	4	const	1	Using index
1	PRIMARY	t22	const	PRIMARY	PRIMARY	4	const	1	Using index
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
explain
select * from
t21,
(
(t1 left join t2 on t2.a=t1.b)
left join t3 on t3.a=t1.b
) left join (select grp_id, count(*)
from
t22 join t10 left join t11 on t11.col1=t10.col1
where
t22.pk=1
group by grp_id) T on T.grp_id=t1.b
where
t21.pk=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t21	const	PRIMARY	PRIMARY	4	const	1	Using index
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
2	LATERAL DERIVED	t22	const	PRIMARY	PRIMARY	4	const	1	Using index
2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
create table t5 (
pk int primary key
);
insert into t5 select seq from seq_1_to_1000;
explain
select * from
t21,
(
(((t1 join t5 on t5.pk=t1.b)) left join t2 on t2.a=t1.b)
left join t3 on t3.a=t1.b
) left join (select grp_id, count(*)
from
t22 join t10 left join t11 on t11.col1=t10.col1
where
t22.pk=1
group by grp_id) T on T.grp_id=t1.b
where
t21.pk=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t21	const	PRIMARY	PRIMARY	4	const	1	Using index
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
1	PRIMARY	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using index
1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	
1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
2	LATERAL DERIVED	t22	const	PRIMARY	PRIMARY	4	const	1	Using index
2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t5.pk	100	Using index condition
2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
drop table t1,t2,t3,t5, t10, t11, t21, t22;
create table t1(a int, b int);
insert into t1 select seq,seq from seq_1_to_5;
create table t2(a int, b int, key(a));
insert into t2
select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
create table t3(a int, b int, key(a));
insert into t3
select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
analyze table t1,t2,t3 persistent for all;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	Table is already up to date
test.t3	analyze	status	Engine-independent statistics collected
test.t3	analyze	status	Table is already up to date
create table t10 (
grp_id int,
col1 int,
key(grp_id)
);
insert into t10
select
A.seq,
B.seq
from
seq_1_to_100 A,
seq_1_to_100 B;
create table t11 (
col1 int,
col2 int
);
insert into t11
select A.seq, A.seq from seq_1_to_10 A;
analyze table t10,t11 persistent for all;
Table	Op	Msg_type	Msg_text
test.t10	analyze	status	Engine-independent statistics collected
test.t10	analyze	status	Table is already up to date
test.t11	analyze	status	Engine-independent statistics collected
test.t11	analyze	status	OK
explain select *
from
(
(t1 left join t2 on t2.a=t1.b)
left join
t3
on t3.a=t1.b
) 
left join
( 
select grp_id, count(*)
from t10 left join t11 on t11.col1=t10.col1
group by grp_id
)dt
on dt.grp_id=t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
select *
from
(
(t1 left join t2 on t2.a=t1.b)
left join
t3
on t3.a=t1.b
) 
left join
( 
select grp_id, count(*)
from t10 left join t11 on t11.col1=t10.col1
group by grp_id
)dt
on dt.grp_id=t1.b;
a	b	a	b	a	b	grp_id	count(*)
1	1	1	1	1	1	1	100
1	1	1	1	1	2	1	100
1	1	1	1	1	3	1	100
1	1	1	2	1	1	1	100
1	1	1	2	1	2	1	100
1	1	1	2	1	3	1	100
2	2	2	1	2	1	2	100
2	2	2	1	2	2	2	100
2	2	2	1	2	3	2	100
2	2	2	2	2	1	2	100
2	2	2	2	2	2	2	100
2	2	2	2	2	3	2	100
3	3	3	1	3	1	3	100
3	3	3	1	3	2	3	100
3	3	3	1	3	3	3	100
3	3	3	2	3	1	3	100
3	3	3	2	3	2	3	100
3	3	3	2	3	3	3	100
4	4	4	1	4	1	4	100
4	4	4	1	4	2	4	100
4	4	4	1	4	3	4	100
4	4	4	2	4	1	4	100
4	4	4	2	4	2	4	100
4	4	4	2	4	3	4	100
5	5	5	1	5	1	5	100
5	5	5	1	5	2	5	100
5	5	5	1	5	3	5	100
5	5	5	2	5	1	5	100
5	5	5	2	5	2	5	100
5	5	5	2	5	3	5	100
set join_cache_level=4;
set optimizer_trace=1;
set @tmp=@@optimizer_switch, optimizer_switch='hash_join_cardinality=off';
insert into t11
select A.seq, A.seq from seq_11_to_100 A;
analyze table t11 persistent for all;
Table	Op	Msg_type	Msg_text
test.t11	analyze	status	Engine-independent statistics collected
test.t11	analyze	status	OK
explain select *
from
(
(t1 left join t2 on t2.a=t1.b)
left join
t3
on t3.a=t1.b
) 
left join
( 
select grp_id, count(*)
from t10 left join t11 on t11.col1=t10.col1
group by grp_id
)dt
on dt.grp_id=t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
2	LATERAL DERIVED	t11	hash_ALL	NULL	#hash#$hj	5	test.t10.col1	100	Using where; Using join buffer (flat, BNLH join)
select *
from
(
(t1 left join t2 on t2.a=t1.b)
left join
t3
on t3.a=t1.b
) 
left join
( 
select grp_id, count(*)
from t10 left join t11 on t11.col1=t10.col1
group by grp_id
)dt
on dt.grp_id=t1.b;
a	b	a	b	a	b	grp_id	count(*)
1	1	1	1	1	1	1	100
1	1	1	1	1	2	1	100
1	1	1	1	1	3	1	100
1	1	1	2	1	1	1	100
1	1	1	2	1	2	1	100
1	1	1	2	1	3	1	100
2	2	2	1	2	1	2	100
2	2	2	1	2	2	2	100
2	2	2	1	2	3	2	100
2	2	2	2	2	1	2	100
2	2	2	2	2	2	2	100
2	2	2	2	2	3	2	100
3	3	3	1	3	1	3	100
3	3	3	1	3	2	3	100
3	3	3	1	3	3	3	100
3	3	3	2	3	1	3	100
3	3	3	2	3	2	3	100
3	3	3	2	3	3	3	100
4	4	4	1	4	1	4	100
4	4	4	1	4	2	4	100
4	4	4	1	4	3	4	100
4	4	4	2	4	1	4	100
4	4	4	2	4	2	4	100
4	4	4	2	4	3	4	100
5	5	5	1	5	1	5	100
5	5	5	1	5	2	5	100
5	5	5	1	5	3	5	100
5	5	5	2	5	1	5	100
5	5	5	2	5	2	5	100
5	5	5	2	5	3	5	100
set optimizer_switch=@tmp;
set join_cache_level=default;
delete from t11;
insert into t11 select A.seq, A.seq from seq_1_to_10 A;
analyze table t11 persistent for all;
Table	Op	Msg_type	Msg_text
test.t11	analyze	status	Engine-independent statistics collected
test.t11	analyze	status	OK
drop index a on t2;
drop index a on t3;
explain select *
from
(
(t1 left join t2 on t2.a=t1.b)
left join
t3
on t3.a=t1.b
) 
left join
( 
select grp_id, count(*)
from t10 left join t11 on t11.col1=t10.col1
group by grp_id
)dt
on dt.grp_id=t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	50	Using where; Using join buffer (flat, BNL join)
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; Using join buffer (incremental, BNL join)
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
2	DERIVED	t10	ALL	grp_id	NULL	NULL	NULL	10000	Using temporary; Using filesort
2	DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
select *
from
(
(t1 left join t2 on t2.a=t1.b)
left join
t3
on t3.a=t1.b
) 
left join
( 
select grp_id, count(*)
from t10 left join t11 on t11.col1=t10.col1
group by grp_id
)dt
on dt.grp_id=t1.b;
a	b	a	b	a	b	grp_id	count(*)
1	1	1	1	1	1	1	100
1	1	1	2	1	1	1	100
1	1	1	1	1	2	1	100
1	1	1	2	1	2	1	100
1	1	1	1	1	3	1	100
1	1	1	2	1	3	1	100
2	2	2	1	2	1	2	100
2	2	2	2	2	1	2	100
2	2	2	1	2	2	2	100
2	2	2	2	2	2	2	100
2	2	2	1	2	3	2	100
2	2	2	2	2	3	2	100
3	3	3	1	3	1	3	100
3	3	3	2	3	1	3	100
3	3	3	1	3	2	3	100
3	3	3	2	3	2	3	100
3	3	3	1	3	3	3	100
3	3	3	2	3	3	3	100
4	4	4	1	4	1	4	100
4	4	4	2	4	1	4	100
4	4	4	1	4	2	4	100
4	4	4	2	4	2	4	100
4	4	4	1	4	3	4	100
4	4	4	2	4	3	4	100
5	5	5	1	5	1	5	100
5	5	5	2	5	1	5	100
5	5	5	1	5	2	5	100
5	5	5	2	5	2	5	100
5	5	5	1	5	3	5	100
5	5	5	2	5	3	5	100
drop table t1,t2,t3;
drop table t10, t11;
#
# MDEV-31194: Server crash or assertion failure with join_cache_level=4
# (a followup to the above bug, MDEV-26301)
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (3),(4);
CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=Aria;
INSERT INTO t2 VALUES (1),(2);
set @tmp1= @@optimizer_switch, @tmp2= @@join_cache_level;
set
optimizer_switch= 'derived_with_keys=off',
join_cache_level= 4;
SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a;
a
set optimizer_switch= @tmp1, join_cache_level= @tmp2;
DROP TABLE t1, t2;
#
# MDEV-31403: Server crashes in st_join_table::choose_best_splitting (still)
#
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
CREATE TABLE t2 (b INT) ENGINE=InnoDB;
INSERT INTO t2 VALUES (100),(200);
CREATE TABLE t3 (c INT, d INT, KEY(c)) ENGINE=InnoDB;
INSERT INTO t3 VALUES (1,1),(2,2);
CREATE VIEW v AS SELECT c, d FROM t3 GROUP BY c, d;
SELECT * FROM t1 JOIN t2 WHERE (t1.a, t2.b) IN (SELECT * FROM v);
a	b
DROP VIEW v;
DROP TABLE t1, t2, t3;
#
# MDEV-31279 Crash when lateral derived is guaranteed to return no rows
#
CREATE TABLE t1 (a CHAR(1)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('1'),('2');
CREATE TABLE t2 (b INT, KEY(b)) ENGINE=MyISAM;
ALTER TABLE t2 DISABLE KEYS;
INSERT INTO t2 VALUES (1),(2),(3);
ALTER TABLE t2 ENABLE KEYS;
CREATE TABLE t3 (c INT) ENGINE=MyISAM;
INSERT INTO t3 (c) SELECT seq FROM seq_1_to_101;
SELECT * FROM t1 WHERE t1.a IN (SELECT b FROM
(SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq);
a
DROP TABLE t1, t2, t3;
#
# MDEV-23878: Wrong result with semi-join and splittable derived table
#
CREATE TABLE t1 (
groupId int,
id int unsigned,
PRIMARY KEY (groupId, id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES
(8,1),(8,2),(8,3),(8,4),(8,5),(8,6),(8,7),(8,8),(8,9),(8,10),
(8,11),(8,12),(8,13),(8,14),(8,15),(8,16),(8,17),(8,18),(8,19);
set statement in_predicate_conversion_threshold=2 for SELECT COUNT(*) AS cnt FROM t1
JOIN
(
SELECT groupId, id
FROM t1
WHERE id IN (1,2,3,4,5,6)
GROUP BY groupId, id
) AS t2
USING (groupId, id)
WHERE id IN (1,2,3,4,5,6,7,8);
cnt
6
set statement in_predicate_conversion_threshold=2 for EXPLAIN SELECT COUNT(*) AS cnt FROM t1
JOIN
(
SELECT groupId, id
FROM t1
WHERE id IN (1,2,3,4,5,6)
GROUP BY groupId, id
) AS t2
USING (groupId, id)
WHERE id IN (1,2,3,4,5,6,7,8);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	19	
1	PRIMARY	<derived3>	ref	key0	key0	8	test.t1.groupId,test.t1.id	1	
1	PRIMARY	<derived5>	eq_ref	distinct_key	distinct_key	4	test.t1.id	1	Using where
5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	LATERAL DERIVED	t1	eq_ref	PRIMARY	PRIMARY	8	test.t1.groupId,test.t1.id	1	
3	LATERAL DERIVED	<derived7>	eq_ref	distinct_key	distinct_key	4	test.t1.id	1	Using where
7	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
set statement optimizer_switch='split_materialized=off, loosescan=off' for
set statement in_predicate_conversion_threshold=2 for
SELECT COUNT(*) AS cnt FROM t1
JOIN
(
SELECT groupId, id
FROM t1
WHERE id IN (1,2,3,4,5,6)
GROUP BY groupId, id
) AS t2
USING (groupId, id)
WHERE id IN (1,2,3,4,5,6,7,8);
cnt
6
DROP TABLE t1;
# End of 10.4 tests
#
# MDEV-30711: Crash in add_keyuses_for_splitting() when joining with a derived table
#
create table t1 (a int);
insert into t1 values (1),(2);
create table t2 (a int, index(a));
insert into t2 values (1),(3);
create view v1 as
select
nullif(tbl2.COL1,123) as COL10
from
t1 left join
(select 1 as COL1, a from t2) tbl2 on t1.a=tbl2.a;
create table t10 (grp_id int, a int, index(grp_id));
insert into t10 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_100 B;
analyze table t10;
Table	Op	Msg_type	Msg_text
test.t10	analyze	status	Engine-independent statistics collected
test.t10	analyze	status	Table is already up to date
explain
select * from
v1,
(select grp_id, count(*) from t10 group by grp_id) T
where
T.grp_id=v1.COL10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using where; Using index
1	PRIMARY	<derived2>	ref	key0	key0	5	func	10	Using where
2	DERIVED	t10	index	grp_id	grp_id	5	NULL	10000	Using index; Using temporary; Using filesort
drop table t1,t2, t10;
drop view v1;
# End of 10.11 tests
#
# MDEV-37057 Wrong result with LATERAL DERIVED
#
CREATE TABLE t1 (
a int NOT NULL,
b int default null,
amount decimal DEFAULT NULL,
KEY t1_IDX (a,b) USING BTREE
) ENGINE=INNODB;
CREATE TABLE t2 (
a int NOT NULL,
b int default null,
name varchar(50) DEFAULT NULL,
KEY t2_IDX (a,b) USING BTREE
) ENGINE=INNODB;
INSERT INTO t1 VALUES
(1, NULL, 10.0000), (2, 2, 20.0000), (3, 3, 30.0000), (4, 4, 40.0000),
(5, 5, NULL), (6, 6, NULL), (7, 7, 70.0000), (8, 8, 80.0000);
INSERT INTO t2 VALUES
(1, NULL, 'A'), (2,2, 'B'), (3,3, 'C'), (4,4, 'D'), (5,5, NULL), (6,6, NULL),
(7,7, 'E'), (8,8, 'F'), (9,9, 'G'), (10,10,'H'), (11,11, NULL), (12,12, NULL);
# Must use Split-Materialized:
explain $query;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	range	t2_IDX	t2_IDX	4	NULL	1	Using index condition
1	PRIMARY	<derived2>	ref	key0	key0	10	test.t2.a,test.t2.b	1	Using where
2	LATERAL DERIVED	t1	ref	t1_IDX	t1_IDX	9	test.t2.a,test.t2.b	1	Using index condition
$query;
a	b	name	total_amt
1	NULL	A	10
# Compare with correct result:
set statement optimizer_switch='split_materialized=off' for $query;
a	b	name	total_amt
1	NULL	A	10
DROP TABLE t1,t2;
#
# MDEV-37230 Incorrect handling of NULL join conditions when using
#   split-materialized
#
create table t1
(
a int not null,
b int,
c int,
d int,
amount decimal,
key t1_ix1 (a,b)
) engine=innodb;
insert into t1 values (0, NULL, 0, NULL, 10.0000), (1, 1, 1, 1, 10.0000),
(2, 2, 2, 2, 20.0000), (3, 3, 3, 3, 30.0000), (4, 4, 4, 4, 40.0000),
(5, 5, 5, 5, NULL), (6, 6, 6, 6, NULL), (7, 7, 7, 7, 70.0000),
(8, 8, 8, 8, 80.0000);
create table t2
(
a int NOT NULL,
b int,
name varchar(50),
key t2_ix1 (a,b)
) engine=innodb;
insert into t2 values (0, NULL, 'a'), (1, NULL, 'A'), (2, 2, 'B'), (3,3, 'C'),
(4,4, 'D'), (5,5, NULL), (6,6, NULL), (7,7, 'E'), (8,8, 'F'), (9,9, 'G'),
(10,10,'H'), (11,11, NULL), (12,12, NULL);
create table t3
(
a int not null,
b int,
description varchar(50),
key t3_ix1 (a,b)
);
insert into t3 values (1, 1, 'bar'),(2,2,'buz'),(0,NULL, 'gold');
insert into t3 select seq, seq, 'junk' from seq_3_to_13;
analyze table t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
test.t3	analyze	status	Engine-independent statistics collected
test.t3	analyze	status	OK
set optimizer_switch='default';
set statement optimizer_switch='split_materialized=on'  for explain format=json select * from t1
join t2 on t1.a = t2.a and t1.b <=> t2.b
join
(
select a, b, description from t3 group by a, b
) dt on dt.a = t1.a and dt.b <=> t1.b and dt.b <=> t2.b
where dt.a < 1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "range",
          "possible_keys": ["t1_ix1"],
          "key": "t1_ix1",
          "key_length": "4",
          "used_key_parts": ["a"],
          "loops": 1,
          "rows": 1,
          "cost": "REPLACED",
          "filtered": 100,
          "index_condition": "t1.a < 1"
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ref",
          "possible_keys": ["t2_ix1"],
          "key": "t2_ix1",
          "key_length": "9",
          "used_key_parts": ["a", "b"],
          "ref": ["test.t1.a", "test.t1.b"],
          "loops": 1,
          "rows": 1,
          "cost": "REPLACED",
          "filtered": 100,
          "index_condition": "t1.b <=> t2.b"
        }
      },
      {
        "table": {
          "table_name": "<derived3>",
          "access_type": "ref",
          "possible_keys": ["key0"],
          "key": "key0",
          "key_length": "9",
          "used_key_parts": ["a", "b"],
          "ref": ["test.t1.a", "test.t1.b"],
          "loops": 1,
          "rows": 1,
          "cost": "REPLACED",
          "filtered": 100,
          "attached_condition": "dt.b <=> t1.b and dt.b <=> t2.b",
          "materialized": {
            "lateral": 1,
            "query_block": {
              "select_id": 3,
              "cost": "REPLACED",
              "nested_loop": [
                {
                  "table": {
                    "table_name": "t3",
                    "access_type": "ref",
                    "possible_keys": ["t3_ix1"],
                    "key": "t3_ix1",
                    "key_length": "9",
                    "used_key_parts": ["a", "b"],
                    "ref": ["test.t1.a", "test.t1.b"],
                    "loops": 1,
                    "rows": 1,
                    "cost": "REPLACED",
                    "filtered": 100,
                    "index_condition": "t3.a < 1 and t3.b <=> t1.b and t3.b <=> t2.b and t3.a = t2.a"
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}
set statement optimizer_switch='split_materialized=on'  for select * from t1
join t2 on t1.a = t2.a and t1.b <=> t2.b
join
(
select a, b, description from t3 group by a, b
) dt on dt.a = t1.a and dt.b <=> t1.b and dt.b <=> t2.b
where dt.a < 1;
a	b	c	d	amount	a	b	name	a	b	description
0	NULL	0	NULL	10	0	NULL	a	0	NULL	gold
set statement optimizer_switch='split_materialized=off' for explain format=json select * from t1
join t2 on t1.a = t2.a and t1.b <=> t2.b
join
(
select a, b, description from t3 group by a, b
) dt on dt.a = t1.a and dt.b <=> t1.b and dt.b <=> t2.b
where dt.a < 1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "range",
          "possible_keys": ["t1_ix1"],
          "key": "t1_ix1",
          "key_length": "4",
          "used_key_parts": ["a"],
          "loops": 1,
          "rows": 1,
          "cost": "REPLACED",
          "filtered": 100,
          "index_condition": "t1.a < 1"
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ref",
          "possible_keys": ["t2_ix1"],
          "key": "t2_ix1",
          "key_length": "9",
          "used_key_parts": ["a", "b"],
          "ref": ["test.t1.a", "test.t1.b"],
          "loops": 1,
          "rows": 1,
          "cost": "REPLACED",
          "filtered": 100,
          "index_condition": "t1.b <=> t2.b"
        }
      },
      {
        "table": {
          "table_name": "<derived3>",
          "access_type": "ref",
          "possible_keys": ["key0"],
          "key": "key0",
          "key_length": "9",
          "used_key_parts": ["a", "b"],
          "ref": ["test.t1.a", "test.t1.b"],
          "loops": 1,
          "rows": 1,
          "cost": "REPLACED",
          "filtered": 100,
          "attached_condition": "dt.b <=> t1.b and dt.b <=> t2.b",
          "materialized": {
            "query_block": {
              "select_id": 3,
              "cost": "REPLACED",
              "nested_loop": [
                {
                  "table": {
                    "table_name": "t3",
                    "access_type": "range",
                    "possible_keys": ["t3_ix1"],
                    "key": "t3_ix1",
                    "key_length": "4",
                    "used_key_parts": ["a"],
                    "loops": 1,
                    "rows": 1,
                    "cost": "REPLACED",
                    "filtered": 100,
                    "index_condition": "t3.a < 1"
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}
set statement optimizer_switch='split_materialized=off' for select * from t1
join t2 on t1.a = t2.a and t1.b <=> t2.b
join
(
select a, b, description from t3 group by a, b
) dt on dt.a = t1.a and dt.b <=> t1.b and dt.b <=> t2.b
where dt.a < 1;
a	b	c	d	amount	a	b	name	a	b	description
0	NULL	0	NULL	10	0	NULL	a	0	NULL	gold
drop table t1, t2, t3;
# End of 11.4 tests
SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;