MDEV-23799 CREATE .. SELECT wrong result on join versioned table

For join to work correctly versioning condition must be added to table
on_expr. Without that JOIN_CACHE gets expression (1)

  trigcond(xtitle.row_end = TIMESTAMP'2038-01-19 06:14:07.999999') and
  trigcond(xtitle.elementId = x.`id` and xtitle.pkey = 'title')

instead of (2)

  trigcond(xtitle.elementId = x.`id` and xtitle.pkey = 'title')

for join_null_complements(). It is NULL-row of xtitle for
complementing the join and the above comparisons of course FALSE, but
trigcond (Item_func_trig_cond) makes them TRUE via its trig_var
property which is bound to some boolean properties of JOIN_TAB.

Expression (2) evaluated to TRUE because its trig_var is bound to
first_inner_tab->not_null_compl. The expression (1) does not evaluate
correctly because row_end comparison's trig_var is bound to
first_inner->found earlier. As a result JOIN_CACHE::check_match()
skipped the row for join_null_complements().

When we add versioning condition to table's on_expr the optimizer in
make_join_select() distributes conditions differently. tmp_cond
inherits on_expr value and in Good case it is full expression

xgender.elementId = x.`id` and xgender.pkey = 'gender' and
xgender.row_end = TIMESTAMP'2038-01-19 06:14:07.999999'

while in Bad case it is only

xgender.elementId = x.`id` and xgender.pkey = 'gender'.

Later in Good row_end condition is optimized out and we get one
trigcond in form of (2).

1 files changed, 21 insertions, 0 deletions

diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result
index 23c6e2d74af..00e257a576f 100644
--- a/mysql-test/suite/versioning/r/select.result
+++ b/mysql-test/suite/versioning/r/select.result
@@ -609,6 +609,27 @@ ERROR 22007: Incorrect date value: 'foo' for column `test`.`t2`.`b` at row 1
 drop prepare stmt;
 drop procedure pr;
 drop table t1, t2;
+#
+# MDEV-23799 CREATE .. SELECT wrong result on join versioned table
+#
+create or replace table x (id Int) with system versioning;
+create or replace table x_p (elementId Int, pkey varchar(20), pvalue varchar(20)) with system versioning;
+insert into x values (1), (2), (3);
+insert into x_p values (1, 'gender', 'male');
+insert into x_p values (2, 'gender', 'female');
+insert into x_p values (3, 'gender', 'male');
+create table tmp1
+select xgender.pvalue as gender, xtitle.pvalue as title
+from x
+left join x_p as xgender on x.id = xgender.elementId and xgender.pkey = 'gender'
+    left join x_p as xtitle on x.id = xtitle.elementId and xtitle.pkey = 'title';
+select * from tmp1;
+gender	title
+male	NULL
+female	NULL
+male	NULL
+drop table tmp1;
+drop tables x, x_p;
 call verify_trt_dummy(34);
 No	A	B	C	D
 1	1	1	1	1