mysql-test/t/derived_correlated.test

--source include/have_optimizer_trace.inc
set optimizer_trace_max_mem_size=10000000,@@session.optimizer_trace="enabled=on";
let $show_trace=
select json_extract(trace,"$.steps[*].join_optimization.steps[*].refine_plan") from information_schema.optimizer_trace;

--echo # WL#461: allow outer references in derived tables and CTEs

create table t1(a int, b int);
insert into t1 (a) values(1),(2);
create table t2 select * from t1;
analyze table t1,t2;

set optimizer_switch='derived_merge=on';

let $c=2;
while ($c)
{
dec $c;

--echo # Deep nesting: all intermediate subqueries are marked DEPENDENT

let $query=
select * from t1 where
 (select count(*) from (select * from (select * from t1 t2
    where 2=(select 2 from (select t1.a) dt1))dt3)dt4);
eval explain $query;
eval $query;

--echo # If reference is removed, not DEPENDENT

let $query=
select * from t1 where
 (select count(*) from (select * from (select * from t1 t2
    where 2=(select 2 from (select 42) dt1))dt3)dt4);
eval explain $query;
eval $query;

--echo # Outer ref is in SELECT list of derived table's definition

# Justifies delete-all-rows in clear_corr_derived_etc

let $query=
select
(select dt.a from
  (select t1.a as a, t2.a as b from t2) dt where dt.b=t1.a)
as subq
from t1;

eval explain $query;
eval $query;

--echo # In WHERE

let $query=
select
(select dt.b from
  (select t2.a as b from t2 where t1.a=t2.a) dt)
as subq
from t1;

eval explain $query;
eval $query;

--echo # In GROUP BY

# Justifies calling unit->execute() in
# TABLE_LIST::materialized_derived(), instead of
# first_select()->join->exec(), so that we get delete_all_rows on
# group-by tmp table

let $query=
select
(select dt.b from
  (select sum(t2.a) as b from t2 group by t1.a) dt)
as subq
from t1;

eval explain $query;
eval $query;

--echo # In HAVING

# Justifies not marking derived table as const (even if it has one
# row, as here), in SELECT_LEX_UNIT::optimize:
# if const it's substituted during optimization and
# thus const over all executions.

let $query=
select
(select dt.b from
  (select sum(t2.a) as b from t2 having t1.a=sum(t2.a)-1) dt)
as subq
from t1;

eval explain $query;
eval $query;

let $query=
select
(select dt.b from
  (select sum(t2.a) as b from t2 having t1.a=sum(t2.a)-2) dt)
as subq
from t1;

eval explain $query;
eval $query;

--echo # In ORDER BY

let $query=
select
(select dt.b from
  (select t2.a as b from t2 order by if(t1.a=1,t2.a,-t2.a) limit 1) dt)
as subq
from t1;

eval explain $query;
eval $query;

--echo # In window functions

let $query=
select
(select dt.b from
  (select t2.a, sum(t1.a*10+t2.a) over (order by if(t1.a=1,t2.a,-t2.a)) as b
   from t2) dt where dt.a=1)
as subq
from t1;

eval explain $query;
eval $query;

--echo # CTE referenced twice

let $query=
select
(with dt as (select t1.a as a, t2.a as b from t2)
 select dt2.a from dt dt1, dt dt2 where dt1.b=t1.a and dt2.b=dt1.b)
as subq
from t1;

eval explain $query;
eval $query;

--echo Recursive CTE
# Justifies:
# - else if (tl->is_recursive_reference())
# branch in clear_corr...
# - !tl->table->is_created() &&
# added before open_tmp_table(rec ref) in unit's execution
# - removed empty() of fake_select_lex's table list at end of unit's
# execution
# - the said empty() should be done only if (full) in cleanup; same
# for nullifying fake_select_lex->recursive_reference.

select
  (with recursive dt as
    (select t1.a as a union select a+1 from dt where a<10)
   select dt1.a from dt dt1 where dt1.a=t1.a
  ) as subq
from t1;

select
  (with recursive dt as
    (select t1.a as a union select a+1 from dt where a<10)
   select concat(count(*), ' - ', avg(dt.a)) from dt
  ) as subq
from t1;

# Same with UNION ALL
select
  (with recursive dt as
    (select t1.a as a union all select a+1 from dt where a<10)
   select concat(count(*), ' - ', avg(dt.a)) from dt
  ) as subq
from t1;

# cte-2-ref

select
(with dt as (select t1.a as a, t2.a as b from t2)
 select dt2.a from dt dt1, dt dt2 where dt1.b=t1.a and dt2.b=dt1.b)
as subq
from t1;

--echo # Two references to same CTE at different levels of nesting.
# Justifies:
# - clearing the CTE's tmp table at the right point of execution: when
# executing the unit which owns the WITH clause, instead of when
# executing the query block which owns the CTE reference (otherwise
# deeper nested CTE user would empty table under feet of less nested
# CTE user)
# - adding OUTER_REF_TABLE_BIT in fix_outer_field() (this is
# due to the added loop in that function, which assumed
# mark_as_dependent adds the bit, but it doesn't). So it's a post-fix
# for previous revision, but was discovered only after implementing
# the first item. Without this BIT, subq to the right of "=" is
# considered constant, so all execs of =() use the same value of the
# subq (in a ref access).

let $query=
select (with dt as (select t1.a as a from t2 limit 1) select * from dt dt1 where dt1.a=(select * from dt as dt2)) as subq from t1;

eval explain $query;
eval explain format=tree $query;  # Demonstrates printing of CTEs printed multiple times.
eval $query;

# Same with HAVING to test similar change in Item_ref::fix_fields
let $query=
select (with dt as (select t2.a as a from t2 having t1.a=t2.a limit 1) select * from dt dt1 where dt1.a=(select * from dt as dt2)) as subq from t1;

eval explain $query;
eval $query;

--echo # Scope of outer ref in CTE
select (select * from (select t1.a) cte) from t1;
# Equivalent query with CTE.
# Per the std, when we evaluate a query expression, we start with
# evaluation of its WITH clause elements (CTEs).
# Evaluate subquery, so evaluate CTE, t1.a is available: ok
select (with cte as (select t1.a) select * from cte) from t1;
# Now same CTE is defined in top query.
# Evaluate top query, so evaluate CTE, t1.a is not yet available:
# error. Justifies the push_context in find_common_table_expr() which
# itself implies the new "complex" loop in fix_outer_field()
--error ER_UNKNOWN_TABLE
with cte as (select t1.a) select (select * from cte) from t1;

--echo # NOT IN(subquery using derived), handled with subquery materialization

let $query=
select * from t1
where a not in (select dt.f+1 from (select t2.a as f from t2) dt);

eval explain $query;
eval $query;

--echo # Now put an outer reference inside derived table:
--echo # subquery is properly seen as correlated and subquery
--echo # materialization is thus not used.

let $query=
select * from t1
where a not in (select dt.f+1 from (select 0*t1.a+t2.a as f from t2) dt);

eval explain $query;
eval $query;

--echo # Verify that a non-lateral derived table with an outer
--echo # reference makes the semijoin be correlated and thus blocks
--echo # semijoin-materialization-scan.
create table t11 (a int);
insert into t11
 with recursive cte as (select 1 as a union all select a+1 from cte where a<124)
 select * from cte;
alter table t11 add index(a);
create table t12 like t11;
analyze table t11,t12;
--echo # No outer ref: mat-scan chosen
explain select
 /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */
 * from t11 where a in (select /*+ QB_NAME(subq1) NO_MERGE(dt) */ *
                       from (select t12.a from t12) dt);
--echo # outer ref: mat-scan not chosen
explain select
 /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */
 * from t11 where a in (select /*+ QB_NAME(subq1) NO_MERGE(dt) */ *
                       from (select t12.a+0*t11.a from t12) dt);
DROP TABLE t11,t12;

--echo LATERAL

--echo # prevents join buffer if materialized (but not if merged)
explain select dt.a from t1, lateral (select t1.a from t2) dt;

--echo # no right join
--error ER_TF_FORBIDDEN_JOIN_TYPE
explain select dt.a from t1 right join lateral (select t1.a from t2) dt on 1;

--echo # no bad left join either
--error ER_BAD_FIELD_ERROR
explain select dt.a from lateral (select t1.a from t2) dt left join t1 on 1;

--echo # more complex case

# Justifies call to propagate_table_maps() in
#  TABLE_LIST::resolve_derived()

--error ER_TF_FORBIDDEN_JOIN_TYPE
explain SELECT * FROM t1
LEFT JOIN
lateral (select t1.a) as dt ON t1.a=dt.a
RIGHT JOIN
lateral (select dt.a) as dt1 ON dt.a=dt1.a;

--echo # LATERAL DT depending on LATERAL DT

# Justifies resolving _and_ materializing dt before resolving dt1,
# i.e. changing the order in SELECT_LEX::resolve_derived().

explain SELECT * FROM t1
JOIN
lateral (select t1.a) as dt ON t1.a=dt.a
JOIN
lateral (select dt.a) as dt1 ON dt.a=dt1.a;

--echo # Placing lateral outer ref in SELECT list then HAVING

--sorted_result
select t1.a, dt.a from t1, lateral (select t1.a+t2.a as a from t2) dt;

select t1.a, dt.a from t1, lateral (select t2.a as a from t2 having t1.a) dt;

--echo # Inside view
create view v1 as
 select t1.a as f1, dt.a as f2
 from t1, lateral (select t1.a+t2.a as a from t2) dt;
show create view v1;
--sorted_result
select * from v1;
drop view v1;

--echo # Coverage for various branches in Item_ref::fix_fields

# I took obscure queries copied from various existing tests, which
# cover the interested code lines, and modified them to include
# derived tables.

SELECT COUNT(*) FROM t1 GROUP BY t1.a  HAVING t1.a IN (SELECT t3.a
FROM t1 AS t3 WHERE t3.b IN (SELECT b FROM t2, lateral (select t1.a) dt));

create view v1 as select a, b from t1;

# used to crash;
# as LATERAL table looks into the table to the left, which may be
# view, it uses find_table_in_table_ref(), which expects a view
# to be either merged or materialized at this stage; this justifies
# resolving the LATERAL table a bit later than ordinary derived table:
# i.e. at the same time as table function, in
# SELECT_LEX::resolve_derived. See bug#27152428

select vq1.b,dt.b from v1 vq1, lateral (select vq1.b) dt;

# still coverage for item_ref::fix_fields
select b from v1 vq1, lateral (select count(*) from v1 vq2 having vq1.b = 3) dt;

drop view v1;

SELECT
/*+ SET_VAR(optimizer_switch = 'materialization=off,semijoin=off') */
* FROM t1 AS ta, lateral (select 1 WHERE ta.a IN (SELECT b FROM t2 AS tb                WHERE tb.b >= SOME(SELECT SUM(tc.a) as sg FROM t1 as tc                                   GROUP BY tc.b                                   HAVING ta.a=tc.b))) dt;

# Justifies that "sut" may be NULL, so use if(sut) when adding
# OUTER_REF_TABLE_BIT in Item_ref::fix_fields, fix_outer_field

select (select dt.a from   (select 1 as a, t2.a as b from t2 having
t1.a) dt where dt.b=t1.a) as subq from t1;

select (select dt.a from   (select 1 as a, 3 as b from t2 having t1.a)
dt where dt.b=t1.a) as subq from t1;

--echo # Aggregation in outer context

# Justifies that check_sum_func(), when it calls set_aggregation(),
# doesn't stop as soon as unit->item is nullptr: otherwise we wouldn't
# mark the scalar subquery, leading to wrong results.
# Also justifies not resetting allow_sum_func to 0 when resolving a
# derived table: we want to allow aggregated outer references.
select (select f from (select max(t1.a) as f) as dt) as g from t1;

select (select f from lateral (select max(t1.a) as f) as dt) as g from t1;

--echo # LATERAL doesn't allow an aggregate to resolve to the
--echo # immediate parent (because reading of FROM tables happens
--echo # before aggregation). So it resolves in the derived table, so
--echo # the outer query doesn't produce a single-row result.
# This was the simplest behaviour to implement.
# SQL Server and PG reject such query. SQL2015 does not really
# describe this case.

select t1.a, f from t1, lateral (select max(t1.a) as f) as dt;

--echo # We support CTE inside derived table

select * from t1,
lateral (with qn as (select t1.a) select (select max(a) from qn)) as dt;

--echo # Coverage for crash in Item_ident::fix_after_pullout:
--echo # when we merge a derived table contained in a derived table,
--echo # and the merged one contains an outer ref to the top query.

select (select * from (select * from (select t1.a from t2) as dt limit 1) dt2) from t1;

--echo # Semijoin containing a correlated derived table, DT must
--echo # become LATERAL

# Justifies making a non-lateral DT become a lateral one in
# fix_tables_after_pullout.
# Indeed, before semijoin merging "dt" has
# an outer non-lateral ref to t1 so just needs to be materialized
# when its owner (the IN subquery) starts execution.
# But after semijoin merging 'dt' changes owner and we have:
# /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1`
# semi join ((/* select#3 */ select `test`.`t1`.`a` AS `a`) `dt`)
# where (`dt`.`a` = `test`.`t1`.`a`)
# so 'dt' now has "lateral" refs (to t1), so we give it the LATERAL
# word so that it is rematerialized every time and not only when its
# owner (top query) starts execution.

let $query=
select a from t1 where a in (select a from (select t1.a) dt);
eval explain $query;
eval $query;

# Justifies adding bits to dep_tables in fix_tables_after_pullout

create table t3 as with recursive cte as (select 1 as a union select a+1 from cte where a<20) select * from cte;
analyze table t3;

# Before semijoin merging, dt has a non-lateral outer ref (to t3), so
# its dep_tables is 0.
# After merging, t3 has 20 rows, so optimizer would put 'dt' first in
# plan, then t3, if dep_tables were left to 0, and this would give a
# wrong result (20,20), as actually 'dt' depends on t3 and must be
# after t3 in plan.

let $query=
select min(a),max(a) from t3 where a in (select /*+ no_merge() */ a from (select t3.a from t1) dt);
eval explain $query;
eval $query;

drop table t3;

--echo # DT containing a correlated DT which must become LATERAL

# Justifies making a non-lateral DT become a lateral one in
# fix_tables_after_pullout, see the "semijoin" text above.

let $query=
select * from t1, lateral (select * from (select * from (select t1.a from t2) as dt limit 1) dt2) dt3;

eval explain format=tree $query;  # Demonstrates Materialize -> Temporary table (forced rematerialization always).
eval explain $query;
eval $query;

# Here "dt2" must be made lateral, as its referenced table t0 will be
# a neighbour in FROM after merging

let $query=
select * from t1 as t0,
lateral
(select dt3.* from t1, lateral (select * from (select * from (select t0.a
from t2) as dt limit 1) dt2) dt3) dt4;

eval explain $query;
eval $query;

# and here "dt2" needn't be made lateral, as t0 remains in outer
# query.

let $query=
select /*+ no_merge() */ * from t1 as t0,
lateral
(select dt3.* from t1, lateral (select * from (select * from (select t0.a
from t2) as dt limit 1) dt2) dt3) dt4;

eval explain $query;
eval $query;

# In the second execution of this test, with all merging disabled,
# we observe that dt3 loses its LATERAL in the EXPLAIN warning,
# because it doesn't actually reference neighbour tables of the same
# FROM clause.

# Note that by adding LATERAL where it was not, we change a bit the
# meaning of the query, fortunately name resolution in execution of a
# prepared stmt uses cached_table. If it didn't, the last column would
# contain 42 instead of 1 or 2, if in a prepared stmt.

let $query=
select * from t1, lateral (select * from (select 42) t1, (select t1.a) dt2) dt3;
eval explain $query;
eval $query;

--echo without semijoin: index_subquery needs to re-materialize

# Justifies clear_corr_derived_tmp_tables in subselect_indexsubquery_engine::exec

let $query=
select a from t1 where a in (select /*+ no_semijoin() */ a from (select t1.a) dt);
eval explain $query;
eval $query;

# Justifies clear_corr_ctes in subselect_indexsubquery_engine::exec

select a from t1 where a in (with cte as (select t1.a)
                             select /*+ no_semijoin() */ a from cte);

--echo # Count rematerializations

--echo # In all three plans, handler_write is 2, showing that we
--echo # rematerialize only when necessary (when row of t1 changes)
let $query=
select straight_join * from t1, t2, lateral (select t1.a) as dt;
eval explain $query;
flush status;
eval $query;
--echo # when a row of t1 produces two rows of t2 passed to "dt",
--echo # it still makes one materialization.
show status like "handler_write";

let $query=
select straight_join * from t1, lateral (select t1.a) as dt, t2;
eval explain $query;
flush status;
--sorted_result
eval $query;
show status like "handler_write";

let $query=
select straight_join * from t2, t1, lateral (select t1.a) as dt;
eval explain $query;
flush status;
--sorted_result
eval $query;
--echo # Due to join buffer, order t2-t1 produces rows as a
--echo # non-buffered t1-t2 plan: t1 buffers all rows of t2, then for
--echo # each row of t1 it's joined to all rows of t2 and passed to t2;
--echo # when a row of t1 produces two rows of t2 passed to "dt",
--echo # it still makes one materialization.
show status like "handler_write";

--echo # Let the planner find the best plan.
--echo # It doesn't work so well, because of
--echo # optimizer_prune_level=1 (see bug#28629788): order specified by
--echo # the user is sorted by number of rows, which leaves it
--echo # unchanged (Card(t1)=Card(t2)=Card(dt)); then it is the first
--echo # explored plan so it's explored in full, and later t1-dt is rejected as
--echo # more expensive than t1-t2. Whereas if t1-dt had been explored
--echo # deeper, we'd see t1-dt-t2 is actually the cheapest, because
--echo # it reads dt the least number of times (and dt has a high read
--echo # cost because Temptable::scan_time() is incredibly high but
--echo # that's another issue; see bug#28631100).
--echo # t2 cannot use join buffering as between "dt" and its
--echo # dependency t1: join buffering would interlace rows of t1
--echo # thus cause more rematerializations.

let $query=
select * from t1, t2, lateral (select t1.a) as dt;
eval explain $query;
flush status;
eval $query;
show status like "handler_write";

--echo # This one finds the best plan. Yes we simply swapped tables in the query,
--echo # and it yields a different plan. This is because the order specified by
--echo # the user is sorted by number of rows, which leaves it
--echo # unchanged (Card(t1)=Card(t2)=Card(dt), then it is the first
--echo # explored plan so it's explored in full and so is never pruned by
--echo # prune_level=1, and it is the best plan. Best as: it reads
--echo # "dt" less, and t2 uses join buffering (which is ok as it's
--echo # after "dt").
--echo # If prune_level=0, all 3 variants here produce this plan.
let $query=
select * from t1, lateral (select t1.a) as dt, t2;
eval explain $query;
flush status;
--sorted_result
eval $query;
show status like "handler_write";

--echo # This one is intermediate: t1 uses join buffer (good), but
--echo # "dt" is last (bad, as it has high scan cost).

let $query=
select * from t2, t1, lateral (select t1.a) as dt;
eval explain $query;
flush status;
--sorted_result
eval $query;
show status like "handler_write";

--echo # Show the trace of planning of lateral derived tables
let $query=
select * from t1, lateral (select t1.a from t2 as t3, t2 as t4) as dt, t2;
eval explain $query;
select trace from information_schema.optimizer_trace;

--echo # LDT depending on const table only

create table t3(a int) engine=innodb;
insert into t3 values(3);
analyze table t3;
let $query=
select * from t3, lateral (select t3.a+1) as dt;
eval explain $query;
eval $query;
drop table t3;


--echo # Two LDTs depending on different tables
let $query=
select * from t2, t1, lateral (select t1.a) as dt,
                      lateral (select t2.a) as dt2;
eval explain $query;
eval $query;

--echo # Two LDTs depending on one same table

let $query=
select * from t2, t1, lateral (select t1.a) as dt,
                      lateral (select t1.a+1) as dt2;
eval explain $query;
eval explain format=json $query;
--sorted_result
eval $query;

--echo # One LDT depending on two tables. The "rematerialize" tag is
--echo # properly added to the 2nd dependency only.

let $query=
select * from t2, t1, lateral (select t1.a+t2.a) as dt;
eval explain $query;
eval explain format=json $query;
eval $query;
eval $show_trace;

--echo # Test when a dependency of LDT uses BKA: BKA code must
--echo # refresh LDT's content when it provides a row.
set @old_opt_switch=@@optimizer_switch;
set @@optimizer_switch="batched_key_access=on,mrr_cost_based=off";
CREATE TABLE t11 (t11a int, t11b int);
INSERT INTO t11 VALUES (99, NULL),(99, 3),(99,0);
CREATE TABLE t12 (t12a int, t12b int, KEY idx (t12b));
INSERT INTO t12 VALUES (100,0),(150,200),(999, 0),(999, NULL);
ANALYZE TABLE t11,t12;
let $query=
SELECT * FROM t11 LEFT JOIN t12 force index (idx) ON t12.t12b = t11.t11b
JOIN LATERAL (SELECT t12a) dt;
eval explain $query;
flush status;
eval $query;
show status like "handler_write";
DROP TABLE t11,t12;
set @@optimizer_switch=@old_opt_switch;

--echo # Test that with an auto_key on the lateral DT, the index is
--echo # properly emptied and re-filled when re-materializing.
--echo # If index weren't emptied, we'd see too many "11" matches for 2nd
--echo # row of t1; and if not re-filled, we'd see no matches for that.

create table t3 (a int, b int);
insert into t3 values(1, 10), (1, 11), (2, 10), (2, 11);
analyze table t3;
let $query=
select * from t1, lateral (select t3.b from t3 where t3.a=t1.a) dt
 where dt.b=t1.a+9;
--echo # Note the auto_key with "Using index", to test the index as
--echo # much as possible.
eval explain $query;
eval $query;
drop table t3;

set optimizer_switch='derived_merge=off';

}

--echo # Reserved word
--error ER_PARSE_ERROR
create table lateral(a int);

drop table t1,t2;

--echo #
--echo # Bug#28723670 RECENT REGRESSION: CRASH/ASSERTION IN FIND_FIELD_IN_TABLE_REF
--echo #

CREATE TABLE t(x INT);
--echo # Don't search for 'y' in top SELECT
--error ER_BAD_FIELD_ERROR
SELECT 1 FROM
  (SELECT 1 FROM (SELECT (SELECT y FROM t) FROM t) AS a) AS b;
DROP TABLE t;

--echo #
--echo # Bug#28976533 ASSERTION `JOIN->BEST_READ < DOUBLE(1.79769313486231570814527423731704357E+308L)
--echo #

CREATE TABLE bb (
pk INTEGER AUTO_INCREMENT,
col_int INTEGER ,
col_int_key INTEGER ,
col_time_key TIME ,
col_time TIME ,
col_datetime_key DATETIME ,
col_datetime DATETIME ,
col_varchar_key VARCHAR(20) ,
col_varchar VARCHAR(20) ,
PRIMARY KEY (pk DESC),
KEY (col_time_key),
KEY (col_time_key DESC)
);

SET SQL_MODE='';
let $query=
SELECT
grandparent1.col_varchar_key AS g1 FROM bb AS grandparent1
LEFT JOIN bb AS grandparent2 USING ( col_time )
WHERE grandparent1.col_int_key IN
(
  WITH qn AS (
               SELECT  parent1.col_int AS p1
               FROM bb AS parent1 LEFT JOIN bb AS parent2 USING ( col_varchar )
               WHERE parent1.col_varchar_key IN
               (
                 WITH qn1 AS (
                               SELECT DISTINCT child1.col_varchar_key AS C1
                               FROM bb AS child1 LEFT JOIN bb AS child2
                               ON child1.col_varchar_key <= child2.col_varchar
                               WHERE child1.col_time > grandparent1.col_datetime
                             )
                 SELECT  * FROM qn1
               )
               AND parent1.col_time_key BETWEEN '2008-03-18' AND
               '2004-11-14'
             )
  SELECT /*+ MERGE(qn) */ * FROM qn
)
GROUP BY grandparent1.col_int;

eval EXPLAIN $query;
eval $query;

DROP TABLE bb;
SET SQL_MODE=DEFAULT;

--echo #
--echo # Bug #29268512: ASSERTION FAILED: INITED == NONE INTERMITTENTLY
--echo #

CREATE TABLE t1 (
  f1 integer
);

INSERT INTO t1 VALUES (0),(1);

CREATE TABLE t2 (
  f2 integer
);

SELECT * FROM t1, LATERAL ( SELECT MAX(1) FROM t2 GROUP BY t1.f1 ) AS l1;

DROP TABLE t1, t2;

--echo #
--echo # Bug #29334082: Still crashing in actual_key_parts() / assert inited == INDEX
--echo #

CREATE TABLE t1 ( f1 INTEGER );
CREATE TABLE t2 ( f2 LONGBLOB );

INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES ('abc'),('def');

# The longblob requires deduplication by means of a hidden hash field,
# which triggers a special path in MaterializeIterator that involves an index.
SELECT STD(0) FROM t2, LATERAL ( SELECT f1 FROM t1 GROUP BY f2,f1 ) AS d1;

DROP TABLE t1, t2;

--echo #
--echo # Bug#28954838 ASSERTION `(REMAINING_TABLES_AFTER != 0) || ((CUR_EMBEDDING_MAP == 0) && (JOIN->
--echo #

CREATE TABLE t1 (
 pk INTEGER,
 col_int INT not null,
 col_int_key INT not null,
 col_time_gckey TIME,
 col_varchar VARCHAR(20) not null,
 col_varchar_key VARCHAR(15)  not null
);

CREATE TABLE t2 (
 pk INTEGER,
 col_int INT not null,
 col_varchar VARCHAR(20) not null,
 col_varchar_key VARCHAR(15) not null
);

SET OPTIMIZER_SWITCH='derived_merge=off';

SELECT table1.col_varchar_key AS field1,
       table2.col_time_gckey AS field2
FROM t2 AS table1 STRAIGHT_JOIN t1 AS table2
     ON table2.col_varchar_key = table1.col_varchar_key
WHERE table2.col_int_key IN
   (WITH qn AS
    (SELECT sq1_t1.col_int AS sq1_field1
     FROM t2 AS sq1_t1
     WHERE sq1_t1.col_varchar_key = table2.col_varchar OR
           EXISTS (WITH qn1 AS
                   (SELECT c_sq1_t1.col_int_key AS c_sq1_field1
                    FROM t1 AS c_sq1_t1
                    WHERE c_sq1_t1.col_varchar_key > sq1_t1.col_varchar OR
                          c_sq1_t1.col_int <> c_sq1_t1.pk
                   )
                   SELECT * FROM qn1
                  )
    )
    SELECT * FROM qn
   ) AND
   EXISTS (WITH qn AS
           (SELECT sq2_t1.col_varchar AS sq2_field1
            FROM t1 AS sq2_t1 STRAIGHT_JOIN
                   t2 AS sq2_t2 INNER JOIN t1 AS sq2_t3
                   ON sq2_t3.col_varchar = sq2_t2.col_varchar_key
                 ON sq2_t3.col_int = sq2_t2.pk
           )
           SELECT * FROM qn
          ) AND
      table2.col_varchar_key <> 'j';

SET OPTIMIZER_SWITCH=DEFAULT;
DROP TABLE t1,t2;

--echo #
--echo # Bug#28955358 VIRTUAL LONGLONG FIELD_NEWDATE::VAL_DATE_TEMPORAL(): ASSERTION `!TABLE || (!TAB
--echo #

CREATE TABLE t1 (
pk INTEGER, col_int_key INTEGER NOT NULL,
col_date_key DATE NOT NULL, col_datetime DATETIME NOT NULL
);

INSERT INTO t1 VALUES (0, 0, '2006-07-18', '2001-09-06 02:13:59.021506');

# outer reference inside derived table 'qn'

SELECT /*+ no_merge() */  outr.pk AS x
FROM ( SELECT * FROM  t1  ) AS  outr
WHERE outr.col_int_key  IN
( SELECT /*+ no_merge() no_semijoin() */ 2
  FROM (SELECT 1 AS x FROM t1 AS  innr WHERE outr.col_date_key ) AS
  qn )
ORDER BY outr.col_datetime;

# outer reference inside JSON_TABLE

SELECT /*+ no_merge() */  outr.pk AS x
FROM ( SELECT * FROM  t1  ) AS  outr
WHERE outr.col_int_key  IN
( SELECT /*+ no_merge() no_semijoin() */ id
  FROM JSON_TABLE( IF(outr.col_date_key<>NOW(),
                      '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
                      '') ,
                   '$[*]' columns (id for ordinality,
                                   jpath varchar(100) path '$.a',
                                   jexst int exists path '$.b')   ) AS
                                   qn )
ORDER BY outr.col_datetime;

DROP TABLE t1;

# This bug was also wrongly accepting a bad GROUP BY query
# without functional dependency:

CREATE TABLE t1(pk INT PRIMARY KEY, a INT);
--error ER_WRONG_FIELD_WITH_GROUP
EXPLAIN SELECT pk FROM t1 GROUP BY a;
--error ER_WRONG_FIELD_WITH_GROUP
EXPLAIN SELECT (SELECT pk FROM (SELECT t1.pk) dt) FROM t1 GROUP BY a;
DROP TABLE t1;

--echo #
--echo # Bug#28960857 ASSERTION FAILED: !TR->DERIVED_WHERE_COND || TR->DERIVED_WHERE_COND->FIXED
--echo # Bug#28960789 ASSERTION FAILED: TRANSL->ITEM->FIXED,
--echo #

CREATE TABLE t0007 (
  c0008 date NOT NULL,
  c0009 char(234) NOT NULL
);

CREATE TABLE t0008 (
  c0005 tinytext NOT NULL
);

CREATE TABLE t0009 (
  c0000 time NOT NULL
);

SET SQL_MODE=0;

SELECT (SELECT t0007.c0009         FROM (SELECT t0007.c0008 AS c0003
     FROM t0009                   ) AS t0005                 ) FROM t0007
GROUP BY -23;

SELECT (SELECT c0009
        FROM (SELECT 1 AS c0003
              FROM t0009 INNER JOIN t0008
                   ON t0008.c0005
              WHERE t0007.c0008
             ) AS t0005
        GROUP BY c0008
       ),
       COUNT(c0009)
FROM t0007
GROUP BY 1, 1;

DROP TABLE t0007, t0008, t0009;
SET SQL_MODE=DEFAULT;

--echo #
--echo # Bug #29514504: WRONG RESULT WITH CORRELATED LATERAL JOIN
--echo #

#
# In this case, the derived table has an aggregation function that can get zero
# input rows, _and_ is evaluated multiple times (due to LATERAL). If so,
# we have to be careful to properly reset the value we write to the derived
# table, or the value from the previous iteration would leak through.
#
CREATE TABLE t1 (id INTEGER);
CREATE TABLE t2 (id INTEGER);
INSERT INTO t1 VALUES (10), (20), (30);
INSERT INTO t2 VALUES (20), (20);
SELECT * FROM t1 JOIN LATERAL (
  SELECT GROUP_CONCAT(t.id) AS c FROM t2 t WHERE (t.id = t1.id)
) d0 ON (1);

DROP TABLE t1, t2;

--echo #
--echo # Bug #30110851: SUBQUERY INVOLVES COUNT() AGGREGATE FUNCTION PERFORMANCE REGRESSION
--echo #

CREATE TABLE t1 ( f1 INTEGER NOT NULL, f2 INTEGER NOT NULL );
CREATE TABLE t2 ( f1 INTEGER NOT NULL, f2 INTEGER NOT NULL );
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT ( SELECT f2 FROM v1 WHERE v1.f1 = t2.f1 ) AS f3 FROM t2;

#
# Both materializations here should be marked as non-rematerialize
# (ie., not “Temporary table”). In particular, the materialization
# in the SELECT clause should be reused for each iteration, even though
# the index lookup against it is outer-correlated, and it is part of
# a query block which is also itself outer-correlated.
#
EXPLAIN FORMAT=TREE SELECT * FROM v2 WHERE f3 = 3;

DROP TABLE t1, t2;
DROP VIEW v1, v2;

--echo #
--echo # Bug#30627575 ASSERT: `INITED == NONE' IN HANDLER::HA_INDEX_INIT() AT SQL/HANDLER.CC
--echo #

CREATE TABLE t1(
a INT,
b INT NOT NULL,
c INT NOT NULL,
d INT,
UNIQUE KEY (c,b)
);
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
CREATE TABLE t2(
a INT,
b INT,
UNIQUE KEY(a,b)
);
INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
ANALYZE TABLE t1,t2;

# Should show "using index for group-by"
EXPLAIN SELECT * FROM t1 JOIN LATERAL (SELECT a+t1.a from t2 GROUP BY a) AS dt;

SELECT * FROM t1 JOIN LATERAL (SELECT a+t1.a from t2 GROUP BY a) AS dt;

DROP TABLE t1, t2;