mariadb/mysql-test/main/opt_hints_split_materialized.test

--echo #
--echo # MDEV-36092 New-style hint: [NO_]SPLIT_MATERIALIZED
--echo #

--source include/have_innodb.inc
--source include/have_sequence.inc
--source include/no_view_protocol.inc

set @save_optimizer_switch=@@optimizer_switch;

--echo #
--echo # case 1
--echo #
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;

--echo # default showing lateral derived optimization in use
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;

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;

--echo # cases where hint disables lateral derived optimization
explain
select /*+ no_split_materialized(t) */ 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;

select /*+ no_split_materialized(t) */ 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;

--echo # query having two subqueries, both of which are subject to LATERAL DERIVED optimization
--echo # explain and result without hints
explain
with cte as (
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
union
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)
select * from cte;

with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select * from cte;

--echo # explain and result with first half of query disabling the optimization
explain
with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) */ * from cte;

with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) */ * from cte;

--echo # explain and result with second half of query disabling the optimization
explain
with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;

with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;

--echo # explain and result with both halves of query disabling the optimization
explain
with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;

with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;

--echo # test opposite way, where hint enables the optimization instead of disabling it
set optimizer_switch='split_materialized=off';

--echo # default showing lateral derived optimization not used
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;

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;

--echo # cases where hint enables lateral derived optimization
explain
select /*+ split_materialized(t) */ 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;

select /*+ split_materialized(t) */ 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;

--echo # query having two subqueries, both of which are subject to LATERAL DERIVED optimization
--echo # explain and result without hints
explain
with cte as (
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
union
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)
select * from cte;

with cte as (
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
union
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)
select * from cte;

--echo # explain and result with first half of query enabling the optimization
explain
with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ SPLIT_MATERIALIZED(t@qb1) */ * from cte;

with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ SPLIT_MATERIALIZED(t@qb1) */ * from cte;

--echo # explain and result with second half of query enabling the optimization
explain
with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ SPLIT_MATERIALIZED(t@qb2) */ * from cte;

with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ SPLIT_MATERIALIZED(t@qb2) */ * from cte;

--echo # explain and result with both halves of query enabling the optimization
explain
with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ SPLIT_MATERIALIZED(t@qb1) SPLIT_MATERIALIZED(t@qb2) */ * from cte;

with cte as (
select /*+ QB_NAME(qb1) */ 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
union
select /*+ QB_NAME(qb2) */ 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)
select /*+ SPLIT_MATERIALIZED(t@qb1) SPLIT_MATERIALIZED(t@qb2) */ * from cte;

set optimizer_switch=@save_optimizer_switch;
drop table t1;
--echo #
--echo # end case 1
--echo #

--echo #
--echo # case 2
--echo #
# 5 values
set @save_optimizer_switch=@@optimizer_switch;

create table t1(a int, b int);
insert into t1 select seq,seq from seq_1_to_5;

# 5 value groups of size 2 each
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;

# 5 value groups of size 3 each
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;

create table t10 (
  grp_id int,
  col1 int,
  key(grp_id)
);

# 100 groups of 100 values each
insert into t10
select
  a.seq,
  b.seq
from
  seq_1_to_100 a,
  seq_1_to_100 b;

# and x10 multiplier

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;

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);

--echo # default showing lateral derived optimization in use
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;

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;

--echo # cases where hint disables lateral derived optimization
explain
select /*+ no_split_materialized(t) */ * 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;

select /*+ no_split_materialized(t) */ * 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;

--echo # test opposite way, where hint enables the optimization instead of disabling it
set optimizer_switch='split_materialized=off';

--echo # default showing lateral derived optimization not used
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;

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;

--echo # cases where hint enables lateral derived optimization
explain
select /*+ split_materialized(t) */ * 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;

select /*+ split_materialized(t) */ * 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;

set optimizer_switch=@save_optimizer_switch;
drop table t1, t2, t3, t10, t11, t21, t22;
--echo #
--echo # end case 2
--echo #

--echo #
--echo # case 3
--echo #
set @save_optimizer_switch=@@optimizer_switch;

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;

--echo # default showing lateral derived optimization in use
explain
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
);

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
);

--echo # cases where hint disables lateral derived optimization
explain
select /*+ no_split_materialized(@qb1 sq) */ * from t1 where t1.a in (
  select /*+ qb_name(qb1) */ b from (
    select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
      select 1 from t3
    ) group by b
  ) sq
);

select /*+ no_split_materialized(@qb1 sq) */ * from t1 where t1.a in (
  select /*+ qb_name(qb1) */ b from (
    select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
      select 1 from t3
    ) group by b
  ) sq
);

--echo # test opposite way, where hint enables the optimization instead of disabling it
set optimizer_switch='split_materialized=off';

--echo # default showing lateral derived optimization not used
explain
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
);

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
);

--echo # cases where hint enables lateral derived optimization
explain select /*+ split_materialized(@qb1 sq) */ * from t1 where t1.a in (
  select /*+ qb_name(qb1) */ b from (
    select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
      select 1 from t3
    ) group by b
  ) sq
);

select /*+ split_materialized(@qb1 sq) */ * from t1 where t1.a in (
  select /*+ qb_name(qb1) */ b from (
    select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
      select 1 from t3
    ) group by b
  ) sq
);

set optimizer_switch=@save_optimizer_switch;
drop table t1, t2, t3;
--echo #
--echo # end case 3
--echo #

--echo #
--echo # case 4
--echo #
create table one_k(a int);
insert into one_k select seq from seq_1_to_1000;

CREATE TABLE t1000 (
  grp int(11) DEFAULT NULL,
  val int(11) DEFAULT NULL,
  KEY grp (grp)
);

insert into t1000 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_10 B;
analyze table t1000;

--echo # force lateral derived when optimizer otherwise wouldn't use it
explain
select *
from
  one_k T1, (select grp, count(*) from t1000 group by grp) TBL where TBL.grp=T1.a;

explain
select /*+ SPLIT_MATERIALIZED(TBL) */ *
from
  one_k T1, (select grp, count(*) from t1000 group by grp) TBL where TBL.grp=T1.a;

drop table one_k, t1000;
--echo #
--echo # end case 4
--echo #

--echo #
--echo # End 12.1 tests
--echo #