Wrong result with semijoin + view + outer join in maria-5.3-subqueries-mwl90

[Philip Stoev]

Not reproducible with maria-5.3 due to different query plan. Reproducible with derived_merge=off, derived_with_keys=off .

The following query:

SELECT *
FROM t1 NATURAL LEFT JOIN ( t2 , t3 )
WHERE t2.f3 IN ( SELECT * FROM v4 );

returns no rows with semijoin in maria-5.3-subqueries-mwl89, even though other execution plans and server versions agree that rows should be returned.

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where
3 DERIVED t4 index NULL f2 5 NULL 2 Using index

minimal optimizer switch required: semijoin=on,firstmatch=off

total optimizer_switch in effect:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,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=on,table_elimination=on

test case:

SET SESSION optimizer_switch='semijoin=on,firstmatch=off';

CREATE TABLE t1 (f1 int, f2 int );
INSERT INTO t1 VALUES (2,0),(4,0),(0,NULL);

CREATE TABLE t2 (f2 int, f3 int );
INSERT INTO t2 VALUES (NULL,NULL),(0,0);

CREATE TABLE t3 ( f1 int, f3 int );
INSERT INTO t3 VALUES (2,0),(4,0),(0,NULL),(4,0),(8,0);

CREATE TABLE t4 ( f2 int, KEY (f2) );
INSERT INTO t4 VALUES (0),(NULL);

CREATE VIEW v4 AS SELECT DISTINCT f2 FROM t4 ;

SELECT *
FROM t1 NATURAL LEFT JOIN ( t2 , t3 )
WHERE t2.f3 IN ( SELECT * FROM v4 );

bzr version-info:

revision-id: <email address hidden>
date: 2011-06-29 15:07:28 +0400
build-date: 2011-06-29 16:46:52 +0300
revno: 3070
branch-nick: maria-5.3-subqueries-mwl90

[Sergey Petrunia (sergefp)]

The problem can be repeated without derived table:

MariaDB [j4]> create table tv4 as select * from v4;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [j4]> explain SELECT * FROM t1 NATURAL LEFT JOIN ( t2 , t3 ) WHERE t2.f3 IN ( SELECT * FROM tv4 );
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Start temporary |
| 1 | PRIMARY | tv4 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; End temporary |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
4 rows in set (0.00 sec)

MariaDB [j4]> SELECT * FROM t1 NATURAL LEFT JOIN ( t2 , t3 ) WHERE t2.f3 IN ( SELECT * FROM tv4 );
Empty set (0.00 sec)