MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside

Consider a query with subquery in form t.key=(select ...). Suppose, the
parent query uses this equality for ref access.
It will attempt to evaluate the subquery in get_best_combination(),
right before the join->join_tab[...] array is filled.  The problem was
that subquery optimization will attempt to look at parent's join->join_tab
to check how many times subquery will be executed (and crash).

Fixed by not doing that when the subquery is constant (non-constant
subqueries are only be evaluated during join execution, so they are not
affected)

1 files changed, 25 insertions, 0 deletions

diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 9d7ea17bbe2..7b09cdc5f1c 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2700,5 +2700,30 @@ explain
 select 1 from t1 where _cp932 "1" in (select '1' from t1);
 drop table t1;
 
+--echo #
+--echo # MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside
+--echo #
+set @tmp_mdev7823=@@optimizer_switch;
+set optimizer_switch=default;
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (f2 INT, KEY(f2));
+INSERT INTO t2 VALUES (8),(0);
+
+CREATE TABLE t3 (f3 INT);
+INSERT INTO t3 VALUES (1),(2);
+
+CREATE TABLE t4 (f4 INT);
+INSERT INTO t4 VALUES (0),(5);
+
+explain 
+SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
+SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
+
+drop table t1,t2,t3,t4;
+set optimizer_switch= @tmp_mdev7823;
+
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;