Wrong result with view + outer join + uncorrelated subquery (non-semijoin)

[Philip Stoev]

If the following query:

SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ;

is run without semijoin, it returns no rows whereas if a base table is used insted of the view, the query returns:

+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| NULL | NULL | 1 | 0 |
| NULL | NULL | 1 | 0 |
+------+------+------+------+

explain:

MariaDB [test]> explain SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ;
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer (flat, BNL join) |
| 2 | DEPENDENT SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+

minimal optimizer_switch=semijoin=off;

full optimizer switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

test case:

CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (0,0),(0,0);

CREATE TABLE t2 ( a int, b int );
INSERT IGNORE INTO t2 VALUES (1,0),(1,0);

CREATE TABLE t3 ( b int );
INSERT IGNORE INTO t3 VALUES (0),(0);

CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2;
SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ;

Repeatable in maria-5.3. Not repeatable in maria-5.2,mysql-5.5

[Sergey Petrunia (sergefp)]

* The bug disappears if RIGHT JOIN is changed to equivalent LEFT JOIN.

EXPLAINs are different:
MariaDB [bug833600]> explain extended SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ;
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (flat, BNL join) |
| 2 | DEPENDENT SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
3 rows in set, 1 warning (0.02 sec)

MariaDB [bug833600]> explain extended SELECT * FROM t1 RIGHT JOIN t2 ON ( t2.a = t1.a ) WHERE t2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ;
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)

[Sergey Petrunia (sergefp)]

Processed EXPLAIN EXTENDED for both queries:

select t1.a AS a,t1.b AS b,t2.a AS a,t2.b AS b from t2 join t1 where ((t2.a = t1.a) and <in_optimizer>(t2.b,<exists>(select t3.b from t3 where (<cache>(t2.b) = t3.b))) and isnull(t1.b))
select t1.a AS a,t1.b AS b,t2.a AS a,t2.b AS b from t2 left join t1 on((t1.a = t2.a)) where (<in_optimizer>(t2.b,<exists>(select t3.b from t3 where (<cache>(t2.b) = t3.b))) and isnull(t1.b))

[Sergey Petrunia (sergefp)]

One can see that:
 - the query that uses the VIEW was converted to inner join
 - the query that uses table t2 was not converted

this is not necessarily the cause of the bug, but needs to be investigated.

[Sergey Petrunia (sergefp)]

When we arrive here
  #0 Item_in_optimizer::fix_left (this=0xaecee10, thd=0xade42e8, ref=0xae495cc) at item_cmpfunc.cc:1424
  #1 0x08228c94 in Item_in_optimizer::fix_fields (this=0xaecee10, thd=0xade42e8, ref=0xae495cc) at item_cmpfunc.cc:1463
  #2 0x0826b618 in Item_subselect::fix_fields (this=0xae492b0, thd_param=0xade42e8, ref=0xae495cc) at item_subselect.cc:263
  #3 0x0826b9b5 in Item_in_subselect::fix_fields (this=0xae492b0, thd_arg=0xade42e8, ref=0xae495cc) at item_subselect.cc:2469
  #4 0x0822691e in Item_cond::fix_fields (this=0xae49518, thd=0xade42e8, ref=0xaed7e00) at item_cmpfunc.cc:4122
  #5 0x08321cee in setup_conds (thd=0xade42e8, tables=0xae48000, leaves=@0xade5e18, conds=0xaed7e00) at sql_base.cc:8334
  #6 0x0837d445 in setup_without_group (thd=0xade42e8, ref_pointer_array=0xaecec38, tables=0xae48000, leaves=@0xade5e18, fields=@0xade5dbc, all_fields=@0xaed7d8c, conds=0xaed7e00, order=0x0, group=0x0, hidden_group_fields=0xaed7d6f) at sql_select.cc:449
  #7 0x083734a4 in JOIN::prepare (this=0xaed2c30, rref_pointer_array=0xade5e64, tables_init=0xae48000, wild_num=1, conds_init=0xae49518, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0xade5d28, unit_arg=0xade5a48) at sql_select.cc:572
  #8 0x0837430d in mysql_select (thd=0xade42e8, rref_pointer_array=0xade5e64, tables=0xae48000, wild_num=1, fields=@0xade5dbc, conds=0xae49518, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xae49d60, unit=0xade5a48, select_lex=0xade5d28) at sql_select.cc:2878
  #9 0x08374764 in mysql_explain_union (thd=0xade42e8, unit=0xade5a48, result=0xae49d60) at sql_select.cc:21100
  #10 0x082d0121 in execute_sqlcom_select (thd=0xade42e8, all_tables=0xae48000) at sql_parse.cc:5068
  #11 0x082d2973 in mysql_execute_command (thd=0xade42e8) at sql_parse.cc:2234
  #12 0x082dc6b6 in mysql_parse (thd=0xade42e8, rawbuf=0xae47df8 "explain extended SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL", length=118, found_semicolon=0x9e6a2f6c) at sql_parse.cc:6089

one can see that:
(gdb) p args[0]
  $52 = (Item_direct_view_ref *) 0xaeced68
(gdb) p args[0]->fixed
  $53 = true
(gdb) p args[0]->not_null_tables()
  $54 = 1
(gdb) p args[0]->used_tables()
  $57 = 2

which is a self-contradiction

[Sergey Petrunia (sergefp)]

The problem seems to be in Item_direct_view_ref:
(gdb) p args[0]->ref[0]
  $59 = (Item_field *) 0xae49938
(gdb) p args[0]->ref[0]->used_tables()
  $60 = 1
(gdb) p args[0]->ref[0]->not_null_tables()
  $61 = 1