[MDEV-3459] LP:833600 - Wrong result with view + outer join + uncorrelated subquery (non-semijoin) Created: 2011-08-25  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug833600.xml    

 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

 Comments   
Comment by Sergei Petrunia [ 2011-09-08 ]

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

  • 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)

Comment by Sergei Petrunia [ 2011-09-08 ]

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))

Comment by Sergei Petrunia [ 2011-09-08 ]

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.

Comment by Sergei Petrunia [ 2011-09-08 ]

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

Comment by Sergei Petrunia [ 2011-09-08 ]

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

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 833600

Generated at Thu Feb 08 06:48:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.