MDEV-14816 Assertion `join->best_read < double(1.797...e+308L)` failed in bool greedy_search

5 files changed, 93 insertions, 14 deletions

diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result
index 1abd35631eb..78d09c5c019 100644
--- a/mysql-test/suite/versioning/r/partition.result
+++ b/mysql-test/suite/versioning/r/partition.result
@@ -139,7 +139,7 @@ x	C	D
 ## pruning check
 explain partitions select * from tN;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-N	SIMPLE	tN	pn	system	NULL	NULL	NULL	NULL	N	
+N	SIMPLE	tN	pN,pn	system	NULL	NULL	NULL	NULL	N	
 set @str= concat('select row_start from t1 partition (pn) into @ts0');
 prepare stmt from @str;
 execute stmt;
diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result
index 4e5d6f65e20..1f7e0306b9b 100644
--- a/mysql-test/suite/versioning/r/select.result
+++ b/mysql-test/suite/versioning/r/select.result
@@ -406,8 +406,77 @@ insert into t1 values (1, 10, '15:01:53'), (2, 20, '00:00:00');
 delete from t1;
 select * from t1 where t = '00:00:00' and i > 0 and row_end <> '2012-12-12 00:00:00';
 pk	i	t
+### MDEV-14816 Assertion `join->best_read < double(1.797...e+308L)' failed in bool greedy_search
+create or replace table t1 (f1 int) with system versioning;
+create or replace table t2 (f2 int) with system versioning;
+create or replace table t3 (f3 int);
+create or replace table t4 (f4 int);
+insert into t1 values (1), (2), (3), (4);
+insert into t2 values (1), (2), (3);
+insert into t3 values (1), (2);
+insert into t4 values (1);
+select * from
+t1 as t1a
+left join t2 as t2a left join (t3 as t3a inner join t1) on t2a.f2 = t3a.f3  on t1a.f1 = t2a.f2
+left join (t2 join t3 inner join t4) on t2a.f2 = t1a.f1;
+f1	f2	f3	f1	f2	f3	f4
+1	1	1	1	1	1	1
+2	2	2	1	1	1	1
+1	1	1	2	1	1	1
+2	2	2	2	1	1	1
+1	1	1	3	1	1	1
+2	2	2	3	1	1	1
+1	1	1	4	1	1	1
+2	2	2	4	1	1	1
+1	1	1	1	1	2	1
+2	2	2	1	1	2	1
+1	1	1	2	1	2	1
+2	2	2	2	1	2	1
+1	1	1	3	1	2	1
+2	2	2	3	1	2	1
+1	1	1	4	1	2	1
+2	2	2	4	1	2	1
+1	1	1	1	2	1	1
+2	2	2	1	2	1	1
+1	1	1	2	2	1	1
+2	2	2	2	2	1	1
+1	1	1	3	2	1	1
+2	2	2	3	2	1	1
+1	1	1	4	2	1	1
+2	2	2	4	2	1	1
+1	1	1	1	2	2	1
+2	2	2	1	2	2	1
+1	1	1	2	2	2	1
+2	2	2	2	2	2	1
+1	1	1	3	2	2	1
+2	2	2	3	2	2	1
+1	1	1	4	2	2	1
+2	2	2	4	2	2	1
+1	1	1	1	3	1	1
+2	2	2	1	3	1	1
+1	1	1	2	3	1	1
+2	2	2	2	3	1	1
+1	1	1	3	3	1	1
+2	2	2	3	3	1	1
+1	1	1	4	3	1	1
+2	2	2	4	3	1	1
+1	1	1	1	3	2	1
+2	2	2	1	3	2	1
+1	1	1	2	3	2	1
+2	2	2	2	3	2	1
+1	1	1	3	3	2	1
+2	2	2	3	3	2	1
+1	1	1	4	3	2	1
+2	2	2	4	3	2	1
+3	3	NULL	NULL	1	1	1
+3	3	NULL	NULL	1	2	1
+3	3	NULL	NULL	2	1	1
+3	3	NULL	NULL	2	2	1
+3	3	NULL	NULL	3	1	1
+3	3	NULL	NULL	3	2	1
+4	NULL	NULL	NULL	NULL	NULL	NULL
 drop view v1;
-drop table t1, t2;
+drop table t1, t2, t3, t4;
 call verify_vtq_dummy(34);
 No	A	B	C	D
 1	1	1	1	1
diff --git a/mysql-test/suite/versioning/r/view.result b/mysql-test/suite/versioning/r/view.result
index 0dc28c230c2..209a7aa77a1 100644
--- a/mysql-test/suite/versioning/r/view.result
+++ b/mysql-test/suite/versioning/r/view.result
@@ -17,7 +17,7 @@ x
 create or replace view vt1 as select * from t1;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `x` from `t1` FOR SYSTEM_TIME ALL where `t1`.`row_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `x` from `t1` FOR SYSTEM_TIME ALL where `t1`.`row_end` = MAX_RESULT or `t1`.`row_end` is null	latin1	latin1_swedish_ci
 drop view vt1;
 drop view vt2;
 create or replace view vt1 as select * from t1 for system_time all;
@@ -69,7 +69,7 @@ create or replace table t1 (x int) with system versioning;
 create or replace view vt1(c) as select x from t1;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `c` from `t1` FOR SYSTEM_TIME ALL where `t1`.`row_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `c` from `t1` FOR SYSTEM_TIME ALL where `t1`.`row_end` = MAX_RESULT or `t1`.`row_end` is null	latin1	latin1_swedish_ci
 # VIEW over JOIN of versioned tables [#153]
 create or replace table t1 (a int) with system versioning;
 create or replace table t2 (b int) with system versioning;
@@ -87,15 +87,15 @@ create or replace table t3 (x int);
 create or replace view vt1 as select * from t1, t2, t3;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t3`.`x` AS `x` from ((`t1` FOR SYSTEM_TIME ALL join `t2` FOR SYSTEM_TIME ALL) join `t3`) where `t1`.`row_end` = MAX_RESULT and `t2`.`row_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t3`.`x` AS `x` from ((`t1` FOR SYSTEM_TIME ALL join `t2` FOR SYSTEM_TIME ALL) join `t3`) where (`t1`.`row_end` = MAX_RESULT or `t1`.`row_end` is null) and (`t2`.`row_end` = MAX_RESULT or `t2`.`row_end` is null)	latin1	latin1_swedish_ci
 create or replace view vt1 as select * from t3, t2, t1;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t3`.`x` AS `x`,`t2`.`b` AS `b`,`t1`.`a` AS `a` from ((`t3` join `t2` FOR SYSTEM_TIME ALL) join `t1` FOR SYSTEM_TIME ALL) where `t2`.`row_end` = MAX_RESULT and `t1`.`row_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t3`.`x` AS `x`,`t2`.`b` AS `b`,`t1`.`a` AS `a` from ((`t3` join `t2` FOR SYSTEM_TIME ALL) join `t1` FOR SYSTEM_TIME ALL) where (`t2`.`row_end` = MAX_RESULT or `t2`.`row_end` is null) and (`t1`.`row_end` = MAX_RESULT or `t1`.`row_end` is null)	latin1	latin1_swedish_ci
 create or replace view vt1 as select a, t2.row_end as endo from t3, t1, t2;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`row_end` AS `endo` from ((`t3` join `t1` FOR SYSTEM_TIME ALL) join `t2` FOR SYSTEM_TIME ALL) where `t1`.`row_end` = MAX_RESULT and `t2`.`row_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`row_end` AS `endo` from ((`t3` join `t1` FOR SYSTEM_TIME ALL) join `t2` FOR SYSTEM_TIME ALL) where (`t1`.`row_end` = MAX_RESULT or `t1`.`row_end` is null) and (`t2`.`row_end` = MAX_RESULT or `t2`.`row_end` is null)	latin1	latin1_swedish_ci
 # VIEW over UNION [#269]
 create or replace view vt1 as select * from t1 union select * from t1;
 select * from vt1;
diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test
index 8fba9a7f68b..356406e0947 100644
--- a/mysql-test/suite/versioning/t/select.test
+++ b/mysql-test/suite/versioning/t/select.test
@@ -283,8 +283,23 @@ delete from t1;
 select * from t1 where t = '00:00:00' and i > 0 and row_end <> '2012-12-12 00:00:00';
 --enable_warnings
 
+--echo ### MDEV-14816 Assertion `join->best_read < double(1.797...e+308L)' failed in bool greedy_search
+create or replace table t1 (f1 int) with system versioning;
+create or replace table t2 (f2 int) with system versioning;
+create or replace table t3 (f3 int);
+create or replace table t4 (f4 int);
+insert into t1 values (1), (2), (3), (4);
+insert into t2 values (1), (2), (3);
+insert into t3 values (1), (2);
+insert into t4 values (1);
+select * from
+  t1 as t1a
+    left join t2 as t2a left join (t3 as t3a inner join t1) on t2a.f2 = t3a.f3  on t1a.f1 = t2a.f2
+    left join (t2 join t3 inner join t4) on t2a.f2 = t1a.f1;
+
+
 drop view v1;
-drop table t1, t2;
+drop table t1, t2, t3, t4;
 
 call verify_vtq_dummy(34);
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 135e27a3a38..1781b7ccfcb 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -844,12 +844,6 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr
       dst_cond= &table->on_expr;
     }
 
-    if (TABLE_LIST *t= table->embedding)
-    {
-      if (t->on_expr)
-        dst_cond= &t->on_expr;
-    }
-
     const LEX_CSTRING *fstart= &table->table->vers_start_field()->field_name;
     const LEX_CSTRING *fend= &table->table->vers_end_field()->field_name;
 
@@ -953,6 +947,7 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr
           curr= newx Item_int(thd, ULONGLONG_MAX);
           cond1= newx Item_func_eq(thd, row_end, curr);
         }
+        cond1= or_items(thd, cond1, newx Item_func_isnull(thd, row_end));
         break;
       case SYSTEM_TIME_AS_OF:
         cond1= newx Item_func_le(thd, row_start,