Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with view + outer join + uncorrelated subquery (non-semijoin)
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))
Re: Wrong result with view + outer join + uncorrelated subquery (non-semijoin)
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.
Re: Wrong result with view + outer join + uncorrelated subquery (non-semijoin)
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
Re: Wrong result with view + outer join + uncorrelated subquery (non-semijoin)
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
Launchpad bug id: 833600
Re: Wrong result with view + outer join + uncorrelated subquery (non-semijoin)
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 ;
---
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------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 ;
---
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
-------------------------------------------------------------------------------3 rows in set, 1 warning (0.01 sec)