Derived: right join is broken #383

vuvova · 2017-12-11T11:00:53Z

Reproduce

create or replace table t1 (
  x int unsigned,
  y int unsigned
) with system versioning;
create or replace table t2 (
  x int unsigned,
  y int unsigned
) with system versioning;

insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5);
insert into t2 values (1, 2), (2, 1), (3, 1);

select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) as t;

Result

+--------+------+------+------+
| RJ1_x1 | y1   | x2   | y2   |
+--------+------+------+------+
|      1 |    1 |    1 |    2 |
|      1 |    2 |    1 |    2 |
|      1 |    3 |    1 |    2 |
+--------+------+------+------+

Expected

+--------+------+------+------+
| RJ1_x1 | y1   | x2   | y2   |
+--------+------+------+------+
|      1 |    1 |    1 |    2 |
|      1 |    2 |    1 |    2 |
|      1 |    3 |    1 |    2 |
|   NULL | NULL |    2 |    1 |
|   NULL | NULL |    3 |    1 |
+--------+------+------+------+

Notes

Trying with

explain extended select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) as t;
show warnings;

one can see that right join is converted to the inner join:

select test.t1.x AS RJ1_x1,test.t1.y AS y1,test.t2.x AS x2,test.t2.y AS y2
from test.t2 FOR SYSTEM_TIME ALL
join test.t1 FOR SYSTEM_TIME ALL
where test.t1.x = test.t2.x
and test.t1.sys_trx_end = 18446744073709551615
and test.t2.sys_trx_end = 18446744073709551615

This does not happen without derived:

explain extended select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x;
show warnings;

select test.t1.x AS RJ1_x1,test.t1.y AS y1,test.t2.x AS x2,test.t2.y AS y2
from test.t2 FOR SYSTEM_TIME ALL
left join test.t1 FOR SYSTEM_TIME ALL
on (test.t1.x = test.t2.x and test.t1.sys_trx_end = 18446744073709551615)
where test.t2.sys_trx_end = 18446744073709551615

Or with left join

explain extended select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) as t;
show warnings;

select test.t1.x AS RJ1_x1,test.t1.y AS y1,test.t2.x AS x2,test.t2.y AS y2
from test.t1 FOR SYSTEM_TIME ALL
left join test.t2 FOR SYSTEM_TIME ALL
on (test.t2.x = test.t1.x and test.t2.sys_trx_end = 18446744073709551615)
where test.t1.sys_trx_end = 18446744073709551615

The text was updated successfully, but these errors were encountered:

midenok · 2017-12-11T12:34:50Z

Related to #156, #160.

midenok · 2017-12-11T13:44:28Z

LEFT JOIN

explain extended select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) as t;

RIGHT JOIN

explain extended select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) as t;

1.

#0  st_select_lex::vers_setup_conds (this=0x7fff84013a80, thd=0x7fff84000d50, tables=0x7fff84014b38, where_expr=0x7fff84017dd0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1013
#1  0x0000000000790b2c in JOIN::prepare (this=0x7fff840179c8, tables_init=0x7fff84014b38, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff84013a80, unit_arg=0x7fff84013ec0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1108
#2  0x0000000000863588 in st_select_lex_unit::prepare_join (this=0x7fff84013ec0, thd_arg=0x7fff84000d50, sl=0x7fff84013a80, tmp_result=0x7fff840178e0, additional_options=0, is_union_select=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_union.cc:660
#3  0x000000000085e949 in st_select_lex_unit::prepare (this=0x7fff84013ec0, thd_arg=0x7fff84000d50, sel_result=0x7fff840178e0, additional_options=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_union.cc:945
#4  0x00000000006f6d6c in mysql_derived_prepare (thd=0x7fff84000d50, lex=0x7fff840049f8, derived=0x7fff84016318) at /home/midenok/src/mariadb/trunk/src/sql/sql_derived.cc:868
#5  0x00000000006f9607 in mysql_handle_single_derived (lex=0x7fff840049f8, derived=0x7fff84016318, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/sql_derived.cc:197
#6  0x00000000008963af in TABLE_LIST::handle_derived (this=0x7fff84016318, lex=0x7fff840049f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/table.cc:7994
#7  0x000000000071f8a3 in st_select_lex::handle_derived (this=0x7fff84005220, lex=0x7fff840049f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/sql_lex.cc:4033
#8  0x0000000000790912 in JOIN::prepare (this=0x7fff84017158, tables_init=0x7fff84016318, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff84005220, unit_arg=0x7fff84004ac0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1074
#9  0x000000000078d0e9 in mysql_select (thd=0x7fff84000d50, tables=0x7fff84016318, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff84016a28, unit=0x7fff84004ac0, select_lex=0x7fff84005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4173
#10 0x00000000007d73d3 in mysql_explain_union (thd=0x7fff84000d50, unit=0x7fff84004ac0, result=0x7fff84016a28) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:25816
#11 0x000000000074d85a in execute_sqlcom_select (thd=0x7fff84000d50, all_tables=0x7fff84016318) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6442
#12 0x0000000000742bf8 in mysql_execute_command (thd=0x7fff84000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#13 0x000000000073d81f in mysql_parse (thd=0x7fff84000d50, rawbuf=0x7fff840137e8 "explain extended select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) as t", length=131, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

LEFT

cond1:$74 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615"
dst_cond:$75 = 0x15ec34a "(Item*)NULL"
where_expr:$76 = 0x15ec34a "(Item*)NULL"
is_where:$77 = true

RIGHT

cond1:$86 = 0x2012580 <dbug_item_print_buf> "t1.x = t2.x and t1.sys_trx_end = 18446744073709551615"
dst_cond:$87 = 0x2012580 <dbug_item_print_buf> "t1.x = t2.x"
where_expr:$88 = 0x15ec34a "(Item*)NULL"
is_where:$89 = false

where_expr is empty.

2.

#0  st_select_lex::vers_setup_conds (this=0x7fff84013a80, thd=0x7fff84000d50, tables=0x7fff84014b38, where_expr=0x7fff84017dd0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1013
#1  0x0000000000790b2c in JOIN::prepare (this=0x7fff840179c8, tables_init=0x7fff84014b38, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff84013a80, unit_arg=0x7fff84013ec0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1108
#2  0x0000000000863588 in st_select_lex_unit::prepare_join (this=0x7fff84013ec0, thd_arg=0x7fff84000d50, sl=0x7fff84013a80, tmp_result=0x7fff840178e0, additional_options=0, is_union_select=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_union.cc:660
#3  0x000000000085e949 in st_select_lex_unit::prepare (this=0x7fff84013ec0, thd_arg=0x7fff84000d50, sel_result=0x7fff840178e0, additional_options=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_union.cc:945
#4  0x00000000006f6d6c in mysql_derived_prepare (thd=0x7fff84000d50, lex=0x7fff840049f8, derived=0x7fff84016318) at /home/midenok/src/mariadb/trunk/src/sql/sql_derived.cc:868
#5  0x00000000006f9607 in mysql_handle_single_derived (lex=0x7fff840049f8, derived=0x7fff84016318, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/sql_derived.cc:197
#6  0x00000000008963af in TABLE_LIST::handle_derived (this=0x7fff84016318, lex=0x7fff840049f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/table.cc:7994
#7  0x000000000071f8a3 in st_select_lex::handle_derived (this=0x7fff84005220, lex=0x7fff840049f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/sql_lex.cc:4033
#8  0x0000000000790912 in JOIN::prepare (this=0x7fff84017158, tables_init=0x7fff84016318, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff84005220, unit_arg=0x7fff84004ac0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1074
#9  0x000000000078d0e9 in mysql_select (thd=0x7fff84000d50, tables=0x7fff84016318, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff84016a28, unit=0x7fff84004ac0, select_lex=0x7fff84005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4173
#10 0x00000000007d73d3 in mysql_explain_union (thd=0x7fff84000d50, unit=0x7fff84004ac0, result=0x7fff84016a28) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:25816
#11 0x000000000074d85a in execute_sqlcom_select (thd=0x7fff84000d50, all_tables=0x7fff84016318) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6442
#12 0x0000000000742bf8 in mysql_execute_command (thd=0x7fff84000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#13 0x000000000073d81f in mysql_parse (thd=0x7fff84000d50, rawbuf=0x7fff840137e8 "explain extended select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) as t", length=131, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

LEFT

cond1:$78 = 0x2012580 <dbug_item_print_buf> "t1.x = t2.x and t2.sys_trx_end = 18446744073709551615"
dst_cond:$79 = 0x2012580 <dbug_item_print_buf> "t1.x = t2.x"
where_expr:$80 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615"
is_where:$81 = false

where_expr is NOT empty.

RIGHT

cond1:$90 = 0x2012580 <dbug_item_print_buf> "t2.sys_trx_end = 18446744073709551615"
dst_cond:$91 = 0x15ec34a "(Item*)NULL"
where_expr:$92 = 0x15ec34a "(Item*)NULL"
is_where:$93 = true

3.

#0  st_select_lex::vers_setup_conds (this=0x7fff84005220, thd=0x7fff84000d50, tables=0x7fff84016318, where_expr=0x7fff84017560) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1013
#1  0x0000000000790b2c in JOIN::prepare (this=0x7fff84017158, tables_init=0x7fff84016318, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff84005220, unit_arg=0x7fff84004ac0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1108
#2  0x000000000078d0e9 in mysql_select (thd=0x7fff84000d50, tables=0x7fff84016318, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff84016a28, unit=0x7fff84004ac0, select_lex=0x7fff84005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4173
#3  0x00000000007d73d3 in mysql_explain_union (thd=0x7fff84000d50, unit=0x7fff84004ac0, result=0x7fff84016a28) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:25816
#4  0x000000000074d85a in execute_sqlcom_select (thd=0x7fff84000d50, all_tables=0x7fff84016318) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6442
#5  0x0000000000742bf8 in mysql_execute_command (thd=0x7fff84000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#6  0x000000000073d81f in mysql_parse (thd=0x7fff84000d50, rawbuf=0x7fff840137e8 "explain extended select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) as t", length=131, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

LEFT

cond1:$82 = 0x2012580 <dbug_item_print_buf> "t.sys_trx_end = 18446744073709551615"
dst_cond:$83 = 0x15ec34a "(Item*)NULL"
where_expr:$84 = 0x15ec34a "(Item*)NULL"
is_where:$85 = true

RIGHT

cond1:$94 = 0x2012580 <dbug_item_print_buf> "t.sys_trx_end = 18446744073709551615"
dst_cond:$95 = 0x15ec34a "(Item*)NULL"
where_expr:$96 = 0x15ec34a "(Item*)NULL"
is_where:$97 = true

midenok · 2017-12-11T16:00:41Z

JOIN printed

#0  print_table_array (thd=0x7fff84000d50, eliminated_tables=0, str=0x7fffe5249a60, table=0x7fff840be2a8, end=0x7fff840be2b8, query_type=QT_EXPLAIN_EXTENDED) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:25863
#1  0x00000000007d8379 in print_join (thd=0x7fff84000d50, eliminated_tables=0, str=0x7fffe5249a60, tables=0x7fff840053b8, query_type=QT_EXPLAIN_EXTENDED) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:25994
#2  0x00000000007d88e9 in st_select_lex::print (this=0x7fff84005220, thd=0x7fff84000d50, str=0x7fffe5249a60, query_type=QT_EXPLAIN_EXTENDED) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:26280
#3  0x000000000071c5d7 in st_select_lex_unit::print (this=0x7fff84004ac0, str=0x7fffe5249a60, query_type=QT_EXPLAIN_EXTENDED) at /home/midenok/src/mariadb/trunk/src/sql/sql_lex.cc:2855
#4  0x000000000074d976 in execute_sqlcom_select (thd=0x7fff84000d50, all_tables=0x7fff84013e50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6469
#5  0x0000000000742bf8 in mysql_execute_command (thd=0x7fff84000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#6  0x000000000073d81f in mysql_parse (thd=0x7fff84000d50, rawbuf=0x7fff840137e8 "explain extended select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x", length=111, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

25860       if (curr->outer_join & (JOIN_TYPE_LEFT|JOIN_TYPE_RIGHT))
25861       {
25862         /* MySQL converts right to left joins */
25863         str->append(STRING_WITH_LEN(" left join "));
25864       }

#define JOIN_TYPE_LEFT	1U
#define JOIN_TYPE_RIGHT	2U
#define JOIN_TYPE_OUTER 4U	/* Marker that this is an outer join */

midenok · 2017-12-11T16:57:39Z

`outer_join` assigned

#0  make_join_statistics (join=0x7fff84015e08, tables_list=..., keyuse_array=0x7fff840160f8) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4433
#1  0x0000000000798c53 in JOIN::optimize_inner (this=0x7fff84015e08) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1912
#2  0x000000000079448c in JOIN::optimize (this=0x7fff84015e08) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1497
#3  0x000000000078d11f in mysql_select (thd=0x7fff84000d50, tables=0x7fff84013e50, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff840156d8, unit=0x7fff84004ac0, select_lex=0x7fff84005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4181
#4  0x00000000007d73d3 in mysql_explain_union (thd=0x7fff84000d50, unit=0x7fff84004ac0, result=0x7fff840156d8) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:25816
#5  0x000000000074d85a in execute_sqlcom_select (thd=0x7fff84000d50, all_tables=0x7fff84013e50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6442
#6  0x0000000000742bf8 in mysql_execute_command (thd=0x7fff84000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#7  0x000000000073d81f in mysql_parse (thd=0x7fff84000d50, rawbuf=0x7fff840137e8 "explain extended select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x", length=111, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959
``
4376          outer_join|= table->map;

4363        if (*s->on_expr_ref)
4364        {
....
4376          outer_join|= table->map;
....
4381        }
4382        if (embedding)
4383        {
....
4387          do
4388          {
....
4399            NESTED_JOIN *nested_join= embedding->nested_join;
....
4402            embedding= embedding->embedding;
....
4403            outer_join|= nested_join->used_tables;
4404          }
4405          while (embedding);
....
4431      join->outer_join=outer_join;
4432

midenok · 2017-12-11T20:15:58Z

Good: right join, not derived

4376          outer_join|= table->map;

(gdb) p table->alias.Ptr
$6 = 0x7fff84094950 "t1"
(gdb) p table->map
$13 = 1

Good: left join, derived or not derived

4376          outer_join|= table->map

(gdb) p table->alias.Ptr
$11 = 0x7fff840974a0 "t2"
(gdb) p table->map
$12 = 2

Bad: 4376 is not executed

4363        if (*s->on_expr_ref)

(gdb) p s->table->alias.Ptr
$21 = 0x7fff84094950 "t1"
(gdb) p *s->on_expr_ref
$22 = (Item *) 0x0

    s->on_expr_ref= &tables->on_expr;

Cause

on_expr is zero for t1.

midenok · 2017-12-11T21:41:42Z

Bad: `on_expr` cleared

#0  0x000000000079a5b5 in simplify_joins (join=0x7fff84016a48, join_list=0x7fff840c7678, conds=0x7fff840c7700, top=true, in_sj=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:15404
#1  0x000000000079a277 in simplify_joins (join=0x7fff84016a48, join_list=0x7fff840053b8, conds=0x7fff84018b58, top=true, in_sj=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:15356
#2  0x00000000007979ad in JOIN::optimize_inner (this=0x7fff84016a48) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1673
#3  0x000000000079448c in JOIN::optimize (this=0x7fff84016a48) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1497
#4  0x000000000078d11f in mysql_select (thd=0x7fff84000d50, tables=0x7fff84016318, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff84016a28, unit=0x7fff84004ac0, select_lex=0x7fff84005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4181
#5  0x000000000078ca63 in handle_select (thd=0x7fff84000d50, lex=0x7fff840049f8, result=0x7fff84016a28, setup_tables_done_option=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:368
#6  0x000000000074dd7f in execute_sqlcom_select (thd=0x7fff84000d50, all_tables=0x7fff84016318) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6503
#7  0x0000000000742bf8 in mysql_execute_command (thd=0x7fff84000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#8  0x000000000073d81f in mysql_parse (thd=0x7fff84000d50, rawbuf=0x7fff840137e8 "select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) as t", length=115, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

(gdb) p table
$41 = (TABLE_LIST *) 0x7fff84014b18
(gdb) p table->alias
$43 = 0x7fff84014b10 "t1"
(gdb) p table->outer_join
$42 = 0

15378       if (!(table->outer_join & (JOIN_TYPE_LEFT | JOIN_TYPE_RIGHT)) ||
15379           (used_tables & not_null_tables))
15380       {
.....
15391         if (table->on_expr)
15392         {
.....
15404           table->prep_on_expr= table->on_expr= 0;
15405         }

Cause

outer_join is zero for t1.

midenok · 2017-12-11T21:46:57Z

Bad: `outer_join` is cleared

15378       if (!(table->outer_join & (JOIN_TYPE_LEFT | JOIN_TYPE_RIGHT)) ||
15379           (used_tables & not_null_tables))
15380       {
.....
15387         table->outer_join= 0;

(gdb) p used_tables & not_null_tables
$59 = 1

Good

(gdb) p used_tables
$78 = 2
(gdb) p not_null_tables 
$79 = 1

midenok · 2017-12-11T21:54:52Z

Bad: `used_tables`, `not_null_tables` set to 3

15358         used_tables= nested_join->used_tables;
15359         not_null_tables= nested_join->not_null_tables;

(gdb) p ((TABLE_LIST *)(nested_join->join_list->first->info))->alias
$74 = 0x7fff84014b10 "t1"
(gdb) p ((TABLE_LIST *)(nested_join->join_list->first->next->info))->alias
$75 = 0x7fff84015198 "t2"

midenok · 2017-12-11T23:14:49Z

break sql_select.cc:15369
  commands
    p table->alias
    p table->get_map()
    p dbug_print_item(conds)
    p conds->not_null_tables()
  end

LJ, no derived

$81 = 0x7fff840144b0 "t2"
$82 = 2
$83 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615"
$84 = 1

$85 = 0x7fff84013e28 "t1"
$86 = 1
$87 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615"
$88 = 1

RJ, no derived

$73 = 0x7fff84013e28 "t1"
$74 = 1
$75 = 0x2012580 <dbug_item_print_buf> "t2.sys_trx_end = 18446744073709551615"
$76 = 2

$77 = 0x7fff840144b0 "t2"
$78 = 2
$79 = 0x2012580 <dbug_item_print_buf> "t2.sys_trx_end = 18446744073709551615"
$80 = 2

LJ, derived

break sql_select.cc:15369
  commands
    p table->alias
    p table->get_map()
    p dbug_print_item(conds)
    p conds->not_null_tables()
    p conds
    p table
    p table->on_expr
  end

call 1

$85 = 0x7fff84015198 "t2"
$86 = 2
$87 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615"
$88 = 1
$89 = (COND *) 0x7fff84017ab0
$90 = (TABLE_LIST *) 0x7fff840151a0
$91 = (Item *) 0x7fff84018020

$92 = 0x7fff84014b10 "t1"
$93 = 1
$94 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615"
$95 = 1
$96 = (COND *) 0x7fff84017ab0
$97 = (TABLE_LIST *) 0x7fff84014b18
$98 = (Item *) 0x0

call 2

$99 = 0x7fff84015198 "t2"
$100 = 2
$101 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615"
$102 = 1
$103 = (COND *) 0x7fff84018b38
$104 = (TABLE_LIST *) 0x7fff840151a0
$105 = (Item *) 0x7fff84018020

$106 = 0x7fff84014b10 "t1"
$107 = 1
$108 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615"
$109 = 1
$110 = (COND *) 0x7fff84018b38
$111 = (TABLE_LIST *) 0x7fff84014b18
$112 = (Item *) 0x0

RJ, derived

call 1

$21 = 0x7fff84014b10 "t1"
$22 = 1
$23 = 0x2012580 <dbug_item_print_buf> "t2.sys_trx_end = 18446744073709551615"
$24 = 2
$25 = (COND *) 0x7fff84018008

$26 = 0x7fff84015198 "t2"
$27 = 2
$28 = 0x2012580 <dbug_item_print_buf> "t2.sys_trx_end = 18446744073709551615"
$29 = 2
$30 = (COND *) 0x7fff84018008

call 2

$31 = 0x7fff84014b10 "t1"
$32 = 1
$33 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615"
$34 = 1
$35 = (COND *) 0x7fff84018b58

$36 = 0x7fff84015198 "t2"
$37 = 2
$38 = 0x2012580 <dbug_item_print_buf> "t1.sys_trx_end = 18446744073709551615 and t1.x = t2.x and t1.sys_trx_end = 18446744073709551615"
$39 = 3
$40 = (COND *) 0x7fff840bc8e0

In call 2 cond is changed for RJ while for LJ not. $33 is unexpected, expected: "t2.sys_trx_end = 18446744073709551615".

midenok · 2017-12-11T23:42:04Z

Bad: `conds` changed

15391         if (table->on_expr)
15392         {
15393           /* Add ON expression to the WHERE or upper-level ON condition. */
15394           if (conds)
15395           {
15396             conds= and_conds(join->thd, conds, table->on_expr);
15397             conds->top_level_item();

(gdb) p table->alias
$42 = 0x7fff84014b10 "t1"
(gdb) p dbug_print_item(table->on_expr)
$43 = 0x2012580 <dbug_item_print_buf> "t1.x = t2.x and t1.sys_trx_end = 18446744073709551615"

midenok · 2017-12-12T10:23:20Z

call 1
Good	Bad
$85 = 0x7fff84015198 "t2" $86 = 2 $87 = 0x2012580 "t1.sys_trx_end = 18446744073709551615" $88 = 1 $89 = (COND ) 0x7fff84017ab0 $90 = (TABLE_LIST ) 0x7fff840151a0 $91 = (Item *) 0x7fff84018020	$113 = 0x7fff84014b10 "t1" $114 = 1 $115 = 0x2012580 "t2.sys_trx_end = 18446744073709551615" $116 = 2 $117 = (COND ) 0x7fff84018008 $118 = (TABLE_LIST ) 0x7fff84014b18 $119 = (Item *) 0x7fff84017c38
$92 = 0x7fff84014b10 "t1" $93 = 1 $94 = 0x2012580 "t1.sys_trx_end = 18446744073709551615" $95 = 1 $96 = (COND ) 0x7fff84017ab0 $97 = (TABLE_LIST ) 0x7fff84014b18 $98 = (Item *) 0x0	$120 = 0x7fff84015198 "t2" $121 = 2 $122 = 0x2012580 "t2.sys_trx_end = 18446744073709551615" $123 = 2 $124 = (COND ) 0x7fff84018008 $125 = (TABLE_LIST ) 0x7fff840151a0 $126 = (Item *) 0x0
call 2
$99 = 0x7fff84015198 "t2" $100 = 2 $101 = 0x2012580 "t1.sys_trx_end = 18446744073709551615" $102 = 1 $103 = (COND ) 0x7fff84018b38 $104 = (TABLE_LIST ) 0x7fff840151a0 $105 = (Item *) 0x7fff84018020	$127 = 0x7fff84014b10 "t1" $128 = 1 $129 = 0x2012580 "t1.sys_trx_end = 18446744073709551615" $130 = 1 $131 = (COND ) 0x7fff84018b58 $132 = (TABLE_LIST ) 0x7fff84014b18 $133 = (Item *) 0x7fff84017c38
$106 = 0x7fff84014b10 "t1" $107 = 1 $108 = 0x2012580 "t1.sys_trx_end = 18446744073709551615" $109 = 1 $110 = (COND ) 0x7fff84018b38 $111 = (TABLE_LIST ) 0x7fff84014b18 $112 = (Item *) 0x0	$134 = 0x7fff84015198 "t2" $135 = 2 $136 = 0x2012580 "t1.sys_trx_end = 18446744073709551615 and t1.x = t2.x and t1.sys_trx_end = 18446744073709551615" $137 = 3 $138 = (COND ) 0x7fff840bda80 $139 = (TABLE_LIST ) 0x7fff840151a0 $140 = (Item *) 0x0

midenok · 2017-12-12T10:29:14Z

Bad: `conds` (0x7fff84018b58) constructed

#9  0x00000000006b6a92 in Item_func_eq::Item_func_eq (this=0x7fff84018b58, thd=0x7fff84000d50, a=0x7fff840189c0, b=0x7fff84018ac8) at /home/midenok/src/mariadb/trunk/src/sql/item_cmpfunc.h:692
#10 0x000000000078f053 in st_select_lex::vers_setup_conds (this=0x7fff84005220, thd=0x7fff84000d50, tables=0x7fff84016318, where_expr=0x7fff84016e50) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:932
#11 0x0000000000790b2c in JOIN::prepare (this=0x7fff84016a48, tables_init=0x7fff84016318, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff84005220, unit_arg=0x7fff84004ac0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1108
#12 0x000000000078d0e9 in mysql_select (thd=0x7fff84000d50, tables=0x7fff84016318, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff84016a28, unit=0x7fff84004ac0, select_lex=0x7fff84005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4173
#13 0x000000000078ca63 in handle_select (thd=0x7fff84000d50, lex=0x7fff840049f8, result=0x7fff84016a28, setup_tables_done_option=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:368
#14 0x000000000074dd7f in execute_sqlcom_select (thd=0x7fff84000d50, all_tables=0x7fff84016318) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6503
#15 0x0000000000742bf8 in mysql_execute_command (thd=0x7fff84000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#16 0x000000000073d81f in mysql_parse (thd=0x7fff84000d50, rawbuf=0x7fff840137e8 "select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) as t", length=115, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

Good: `conds` (0x7fff84018b38) constructed

#9  0x00000000006b6a92 in Item_func_eq::Item_func_eq (this=0x7fff84018b38, thd=0x7fff84000d50, a=0x7fff840189a0, b=0x7fff84018aa8) at /home/midenok/src/mariadb/trunk/src/sql/item_cmpfunc.h:692
#10 0x000000000078f053 in st_select_lex::vers_setup_conds (this=0x7fff84005220, thd=0x7fff84000d50, tables=0x7fff840162f8, where_expr=0x7fff84016e30) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:932
#11 0x0000000000790b2c in JOIN::prepare (this=0x7fff84016a28, tables_init=0x7fff840162f8, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff84005220, unit_arg=0x7fff84004ac0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1108
#12 0x000000000078d0e9 in mysql_select (thd=0x7fff84000d50, tables=0x7fff840162f8, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff84016a08, unit=0x7fff84004ac0, select_lex=0x7fff84005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4173
#13 0x000000000078ca63 in handle_select (thd=0x7fff84000d50, lex=0x7fff840049f8, result=0x7fff84016a08, setup_tables_done_option=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:368
#14 0x000000000074dd7f in execute_sqlcom_select (thd=0x7fff84000d50, all_tables=0x7fff840162f8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6503
#15 0x0000000000742bf8 in mysql_execute_command (thd=0x7fff84000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#16 0x000000000073d81f in mysql_parse (thd=0x7fff84000d50, rawbuf=0x7fff840137e8 "select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) as t", length=114, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

(gdb) p dbug_print_item(cond1)
$146 = 0x2012580 <dbug_item_print_buf> "t.sys_trx_end = 18446744073709551615"
(gdb) p cond1
$147 = (Item *) 0x7fff84018b38
(gdb) p table->alias
$143 = 0x7fff840162b8 "t"
(gdb) p ((Item_field *)((Item_func_eq *)cond1)->args[0])->table_name
$156 = 0x7fff840162b8 "t"

In `simplify_joins()`

(gdb) f 0
#0  simplify_joins (join=0x7fff84016a28, join_list=0x7fff840bdfb8, conds=0x7fff84018b38, top=true, in_sj=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:15369
15369           not_null_tables= conds->not_null_tables();

(gdb) p ((Item_field *)((Item_ref *)((Item_func_eq *)conds)->args[0])->real_item())->table_name
$195 = 0x7fff84094950 "t1"

midenok · 2017-12-12T10:53:53Z

Good: `t` replaced with `t1`

#0  THD::change_item_tree (this=0x7fff84000d50, place=0x7fff84018bc8, new_value=0x7fff840196a0) at /home/midenok/src/mariadb/trunk/src/sql/sql_class.h:3749
#1  0x00000000006a8be8 in find_field_in_view (thd=0x7fff84000d50, table_list=0x7fff840162f8, name=0x7fff840919d3 "sys_trx_end", length=11, item_name=0x7fff840919d3 "sys_trx_end", ref=0x7fff84018bc8, register_tree_change=true) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:5305
#2  0x00000000006a8232 in find_field_in_table_ref (thd=0x7fff84000d50, table_list=0x7fff840162f8, name=0x7fff840919d3 "sys_trx_end", length=11, item_name=0x7fff840919d3 "sys_trx_end", db_name=0x1fefe30 <empty_c_string> "", table_name=0x7fff840162b8 "t", ref=0x7fff84018bc8, check_privileges=true, allow_rowid=true, cached_field_index_ptr=0x7fff84018a74, register_tree_change=true, actual_table=0x7fffe5248ac0) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:5631
#3  0x00000000006a9e0b in find_field_in_tables (thd=0x7fff84000d50, item=0x7fff840189a0, first_table=0x7fff840162f8, last_table=0x0, ref=0x7fff84018bc8, report_error=IGNORE_EXCEPT_NON_UNIQUE, check_privileges=true, register_tree_change=true) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:5932
#4  0x0000000000ab9d80 in Item_field::fix_fields (this=0x7fff840189a0, thd=0x7fff84000d50, reference=0x7fff84018bc8) at /home/midenok/src/mariadb/trunk/src/sql/item.cc:5819
#5  0x0000000000b269fd in Item_func::fix_fields (this=0x7fff84018b38, thd=0x7fff84000d50, ref=0x7fff84016e30) at /home/midenok/src/mariadb/trunk/src/sql/item_func.cc:354
#6  0x00000000006ae688 in setup_conds (thd=0x7fff84000d50, tables=0x7fff840162f8, leaves=..., conds=0x7fff84016e30) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:7928
#7  0x0000000000792acc in setup_without_group (thd=0x7fff84000d50, ref_pointer_array=..., tables=0x7fff840162f8, leaves=..., fields=..., all_fields=..., conds=0x7fff84016e30, order=0x0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7fff84016d0f, reserved=0x7fff840054f4) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:644
#8  0x00000000007910d2 in JOIN::prepare (this=0x7fff84016a28, tables_init=0x7fff840162f8, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff84005220, unit_arg=0x7fff84004ac0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1179
#9  0x000000000078d0e9 in mysql_select (thd=0x7fff84000d50, tables=0x7fff840162f8, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff84016a08, unit=0x7fff84004ac0, select_lex=0x7fff84005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4173
#10 0x000000000078ca63 in handle_select (thd=0x7fff84000d50, lex=0x7fff840049f8, result=0x7fff84016a08, setup_tables_done_option=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:368
#11 0x000000000074dd7f in execute_sqlcom_select (thd=0x7fff84000d50, all_tables=0x7fff840162f8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6503
#12 0x0000000000742bf8 in mysql_execute_command (thd=0x7fff84000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#13 0x000000000073d81f in mysql_parse (thd=0x7fff84000d50, rawbuf=0x7fff840137e8 "select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) as t", length=114, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

(gdb) p ((Item_field *)((Item_ref *)((Item_func_eq *)0x7fff84018b38)->args[0])->real_item())->table_name
$206 = 0x7fff84094950 "t1

midenok · 2017-12-12T12:31:26Z

Fields are translated via TABLE_LIST::field_translation:

void Field_iterator_view::set(TABLE_LIST *table)
{
  DBUG_ASSERT(table->field_translation);
  view= table;
  ptr= table->field_translation;
  array_end= table->field_translation_end;
}

struct Field_translator
{
  Item *item;
  LEX_CSTRING name;
};

#1  0x000000000088fe38 in Field_iterator_view::create_item (this=0x7fffe5248530, thd=0x7fff7c000d50) at /home/midenok/src/mariadb/trunk/src/sql/table.cc:5870
5870      return create_view_field(thd, view, &ptr->item, &ptr->name);

(gdb) p ((Item_field *)(ptr->item)->real_item())->field->table->alias.Ptr
$24 = 0x7fff7c094950 "t1"
(gdb) p view
$25 = (TABLE_LIST *) 0x7fff7c0162f8
(gdb) p view->alias
$26 = 0x7fff7c0162b8 "t"

midenok · 2017-12-12T12:56:40Z

Field translation created

#0  TABLE_LIST::create_field_translation (this=0x7fff7c0162f8, thd=0x7fff7c000d50) at /home/midenok/src/mariadb/trunk/src/sql/table.cc:4728
#1  0x00000000008966e2 in TABLE_LIST::init_derived (this=0x7fff7c0162f8, thd=0x7fff7c000d50, init_view=false) at /home/midenok/src/mariadb/trunk/src/sql/table.cc:8132
#2  0x00000000006f6e60 in mysql_derived_prepare (thd=0x7fff7c000d50, lex=0x7fff7c0049f8, derived=0x7fff7c0162f8) at /home/midenok/src/mariadb/trunk/src/sql/sql_derived.cc:882
#3  0x00000000006f9607 in mysql_handle_single_derived (lex=0x7fff7c0049f8, derived=0x7fff7c0162f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/sql_derived.cc:197
#4  0x00000000008963af in TABLE_LIST::handle_derived (this=0x7fff7c0162f8, lex=0x7fff7c0049f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/table.cc:7994
#5  0x000000000071f8a3 in st_select_lex::handle_derived (this=0x7fff7c005220, lex=0x7fff7c0049f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/sql_lex.cc:4033
#6  0x0000000000790912 in JOIN::prepare (this=0x7fff7c016a28, tables_init=0x7fff7c0162f8, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff7c005220, unit_arg=0x7fff7c004ac0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1074
#7  0x000000000078d0e9 in mysql_select (thd=0x7fff7c000d50, tables=0x7fff7c0162f8, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c016a08, unit=0x7fff7c004ac0, select_lex=0x7fff7c005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4173
#8  0x000000000078ca63 in handle_select (thd=0x7fff7c000d50, lex=0x7fff7c0049f8, result=0x7fff7c016a08, setup_tables_done_option=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:368
#9  0x000000000074dd7f in execute_sqlcom_select (thd=0x7fff7c000d50, all_tables=0x7fff7c0162f8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6503
#10 0x0000000000742bf8 in mysql_execute_command (thd=0x7fff7c000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#11 0x000000000073d81f in mysql_parse (thd=0x7fff7c000d50, rawbuf=0x7fff7c0137e8 "select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) as t", length=114, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

midenok · 2017-12-12T13:58:35Z

System field is added to `SELECT_LEX::item_list`

#1  0x0000000000663238 in List<Item>::push_back (this=0x7fff7c013b80, a=0x7fff7c016f80) at /home/midenok/src/mariadb/trunk/src/sql/sql_list.h:485
#2  0x000000000072d52c in st_select_lex::vers_push_field (this=0x7fff7c013a60, thd=0x7fff7c000d50, table=0x7fff7c014b18, field_name=...) at /home/midenok/src/mariadb/trunk/src/sql/sql_lex.cc:7453
#3  0x00000000006f6a41 in mysql_derived_prepare (thd=0x7fff7c000d50, lex=0x7fff7c0049f8, derived=0x7fff7c0162f8) at /home/midenok/src/mariadb/trunk/src/sql/sql_derived.cc:844
#4  0x00000000006f9607 in mysql_handle_single_derived (lex=0x7fff7c0049f8, derived=0x7fff7c0162f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/sql_derived.cc:197
#5  0x00000000008963af in TABLE_LIST::handle_derived (this=0x7fff7c0162f8, lex=0x7fff7c0049f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/table.cc:7994
#6  0x000000000071f8a3 in st_select_lex::handle_derived (this=0x7fff7c005220, lex=0x7fff7c0049f8, phases=2) at /home/midenok/src/mariadb/trunk/src/sql/sql_lex.cc:4033
#7  0x0000000000790912 in JOIN::prepare (this=0x7fff7c016a28, tables_init=0x7fff7c0162f8, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff7c005220, unit_arg=0x7fff7c004ac0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1074
#8  0x000000000078d0e9 in mysql_select (thd=0x7fff7c000d50, tables=0x7fff7c0162f8, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c016a08, unit=0x7fff7c004ac0, select_lex=0x7fff7c005220) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4173
#9  0x000000000078ca63 in handle_select (thd=0x7fff7c000d50, lex=0x7fff7c0049f8, result=0x7fff7c016a08, setup_tables_done_option=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:368
#10 0x000000000074dd7f in execute_sqlcom_select (thd=0x7fff7c000d50, all_tables=0x7fff7c0162f8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6503
#11 0x0000000000742bf8 in mysql_execute_command (thd=0x7fff7c000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3746
#12 0x000000000073d81f in mysql_parse (thd=0x7fff7c000d50, rawbuf=0x7fff7c0137e8 "select * from (select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) as t", length=114, parser_state=0x7fffe524d650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7959

Cause

Currently implicit system fields of derived table are system fields of first versioned table:

        if (!impli_table)
        {
          impli_table= table;
          impli_start= table_start;
          impli_end= table_end;
        }

Fix

Detect RIGHT JOIN and make second table as impli_table.

TABLE_LIST *st_select_lex::convert_right_join()
{
  TABLE_LIST *tab2= join_list->pop();
  TABLE_LIST *tab1= join_list->pop();
  DBUG_ENTER("convert_right_join");

  join_list->push_front(tab2, parent_lex->thd->mem_root);
  join_list->push_front(tab1, parent_lex->thd->mem_root);
  tab1->outer_join|= JOIN_TYPE_RIGHT;

  DBUG_RETURN(tab1);
}

This fix is not trivial as there might be multiple joins and we don't know which one will be longer. Shorter joins will have NULL in sys_trx_end.

midenok · 2017-12-12T16:44:51Z

Trivial sample where z represents sys_trx_end:

select * from (select p1.x as RJ1_x1, p1.y as y1, p2.x as x2, p2.y as y2, p1.z from p1 right join p2 on p1.x = p2.x) as t where z = 1;
+--------+------+------+------+------+
| RJ1_x1 | y1   | x2   | y2   | z    |
+--------+------+------+------+------+
|      1 |    1 |    1 |    2 |    1 |
|      1 |    2 |    1 |    2 |    1 |
|      1 |    3 |    1 |    2 |    1 |
+--------+------+------+------+------+

Correct result will be when we add z is null:

select * from (select p1.x as RJ1_x1, p1.y as y1, p2.x as x2, p2.y as y2, p1.z from p1 right join p2 on p1.x = p2.x) as t where z = 1 or z is null;
+--------+------+------+------+------+
| RJ1_x1 | y1   | x2   | y2   | z    |
+--------+------+------+------+------+
|      1 |    1 |    1 |    2 |    1 |
|      1 |    2 |    1 |    2 |    1 |
|      1 |    3 |    1 |    2 |    1 |
|   NULL | NULL |    2 |    1 | NULL |
|   NULL | NULL |    3 |    1 | NULL |
+--------+------+------+------+------+

Fix (more general)

In vers_setup_conds() if the table is derived add to each versioning cond OR IS NULL.

midenok · 2017-12-13T08:52:13Z

breaks.gdb

break /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1013
  commands
    echo cond1:
    p dbug_print_item(cond1)
    echo dst_cond:
    p dbug_print_item(*dst_cond)
    echo where_expr:
    p dbug_print_item(*where_expr)
    echo is_where:
    p is_where
  end
disable $bpnum
break /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:15326
disable $bpnum
break /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:15378
disable $bpnum
break /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:15365
  commands
    p table->alias
    p table->get_map()
    p dbug_print_item(conds)
    p conds->not_null_tables()
    p conds
    p table
    p table->on_expr
  end
break Item::Item
  condition $bpnum this == 0x7fff84018b38
  commands
    bt
  end
disable $bpnum
break /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:5288
disable $bpnum
break TABLE_LIST::create_field_translation
disable $bpnum
break /home/midenok/src/mariadb/trunk/src/sql/table.cc:4789
  commands
    p dbug_print_item(item)
  end
disable $bpnum
break st_select_lex::st_select_lex
disable $bpnum
break /home/midenok/src/mariadb/trunk/src/sql/sql_derived.cc:762
disable $bpnum

#161 is deprecated by #383

SQL: reverted NATURAL JOIN fix #161 (deprecated by #383)

non-standard, redundant, potentially risky in the future, hides bugs. See #383, #384, #385 Fixed a parser bug where SELECT * FROM (t1 join t2) FOR SYSTEM_TIME ... was not an error.

midenok · 2018-01-05T18:29:36Z

Deprecates #160.

midenok changed the title ~~derived + right join~~ Derived: right join is broken Dec 11, 2017

midenok added the bug label Dec 11, 2017

midenok added this to the 1.0: Review fixes and merge into 10.3 milestone Dec 11, 2017

midenok self-assigned this Dec 11, 2017

midenok added the info label Dec 11, 2017

midenok mentioned this issue Dec 12, 2017

tempesta-tech#383 midenok/mariadb#30

Open

midenok closed this as completed in a0e137c Dec 12, 2017

midenok mentioned this issue Dec 13, 2017

Refactor system_time for VIEW, derived #391

Closed

2 tasks

midenok added the refactoring label Dec 14, 2017

midenok mentioned this issue Dec 14, 2017

NATURAL JOIN of versioned VIEW fails #405

Closed

midenok added a commit that referenced this issue Dec 14, 2017

SQL: reverted NATURAL JOIN fix #161 [fixes #405]

97b56b4

#161 is deprecated by #383

kevgs pushed a commit that referenced this issue Dec 14, 2017

SQL: reverted NATURAL JOIN fix #161 [fixes #405]

5aff2c2

#161 is deprecated by #383

midenok added a commit that referenced this issue Dec 14, 2017

MDEV-14652 NATURAL JOIN crash in mark_common_columns() [fixes #405]

2ae2876

SQL: reverted NATURAL JOIN fix #161 (deprecated by #383)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Derived: right join is broken #383

Derived: right join is broken #383

vuvova commented Dec 11, 2017 •

edited by midenok

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 12, 2017

midenok commented Dec 12, 2017 •

edited

Loading

midenok commented Dec 12, 2017

midenok commented Dec 12, 2017 •

edited

Loading

midenok commented Dec 12, 2017

midenok commented Dec 12, 2017 •

edited

Loading

midenok commented Dec 12, 2017 •

edited

Loading

midenok commented Dec 13, 2017

midenok commented Jan 5, 2018

Derived: right join is broken #383

Derived: right join is broken #383

Comments

vuvova commented Dec 11, 2017 • edited by midenok Loading

Reproduce

Result

Expected

Notes

midenok commented Dec 11, 2017 • edited Loading

midenok commented Dec 11, 2017 • edited Loading

LEFT JOIN

RIGHT JOIN

1.

LEFT

RIGHT

2.

LEFT

RIGHT

3.

LEFT

RIGHT

midenok commented Dec 11, 2017 • edited Loading

JOIN printed

midenok commented Dec 11, 2017 • edited Loading

outer_join assigned

midenok commented Dec 11, 2017 • edited Loading

Good: right join, not derived

Good: left join, derived or not derived

Bad: 4376 is not executed

Cause

midenok commented Dec 11, 2017 • edited Loading

Bad: on_expr cleared

Cause

midenok commented Dec 11, 2017 • edited Loading

Bad: outer_join is cleared

Good

midenok commented Dec 11, 2017

Bad: used_tables, not_null_tables set to 3

midenok commented Dec 11, 2017 • edited Loading

LJ, no derived

RJ, no derived

LJ, derived

call 1

call 2

RJ, derived

call 1

call 2

midenok commented Dec 11, 2017 • edited Loading

Bad: conds changed

midenok commented Dec 12, 2017

midenok commented Dec 12, 2017 • edited Loading

Bad: conds (0x7fff84018b58) constructed

Good: conds (0x7fff84018b38) constructed

In simplify_joins()

midenok commented Dec 12, 2017

Good: t replaced with t1

midenok commented Dec 12, 2017 • edited Loading

midenok commented Dec 12, 2017

Field translation created

midenok commented Dec 12, 2017 • edited Loading

System field is added to SELECT_LEX::item_list

Cause

Fix

midenok commented Dec 12, 2017 • edited Loading

Fix (more general)

midenok commented Dec 13, 2017

breaks.gdb

midenok commented Jan 5, 2018

vuvova commented Dec 11, 2017 •

edited by midenok

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

`outer_join` assigned

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

Bad: `on_expr` cleared

midenok commented Dec 11, 2017 •

edited

Loading

Bad: `outer_join` is cleared

Bad: `used_tables`, `not_null_tables` set to 3

midenok commented Dec 11, 2017 •

edited

Loading

midenok commented Dec 11, 2017 •

edited

Loading

Bad: `conds` changed

midenok commented Dec 12, 2017 •

edited

Loading

Bad: `conds` (0x7fff84018b58) constructed

Good: `conds` (0x7fff84018b38) constructed

In `simplify_joins()`

Good: `t` replaced with `t1`

midenok commented Dec 12, 2017 •

edited

Loading

midenok commented Dec 12, 2017 •

edited

Loading

System field is added to `SELECT_LEX::item_list`

midenok commented Dec 12, 2017 •

edited

Loading