[MDEV-2237] LP:869001 - Wrong result with semijoin + materialization + firstmatch + multipart key Created: 2011-10-06  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 LPexportBug869001.xml    

 Description   

The following query:

SELECT *
FROM t1 JOIN t2 ON (t2.f4 = t1.f3 )
WHERE ( 8 ) IN (
SELECT t3.f1 FROM t3 , t4
);

returns 1 row when executed with materialization=on,semijoin=on,firstmatch=on:

--------------

f2 f3 f4

--------------

NULL x x

--------------

even though it should return 2 rows:

--------------

f2 f3 f4

--------------

8 x x
NULL x x

--------------

explain:

id select_type table type possible_keys key key_len ref rows Extra

-----------------------------------------------------------------------------------------+

1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
1 PRIMARY t1 ref f3 f3 4 test.t2.f4 1 Using index
1 PRIMARY t4 ALL NULL NULL NULL NULL 3  
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t2)

minimal switch:
materialization=on,semijoin=on,firstmatch=on
full 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=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=on,in_to_exists=on,semijoin=on,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:

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

CREATE TABLE t1 ( f2 int, f3 varchar(1), KEY (f3,f2)) ;
INSERT INTO t1 VALUES (8,'x'),(NULL,'x'),(8,'c');

CREATE TABLE t2 ( f4 varchar(1)) ;
INSERT INTO t2 VALUES ('x');

CREATE TABLE t3 ( f1 int) ;
INSERT INTO t3 VALUES (8),(6),(2),(9),(6);

CREATE TABLE t4 ( f3 varchar(1)) ;
INSERT INTO t4 VALUES ('p'),('j'),('c');

SELECT *
FROM t1 JOIN t2 ON (t2.f4 = t1.f3 )
WHERE ( 8 ) IN (
SELECT t3.f1 FROM t3 , t4
);

revision-id: <email address hidden>
date: 2011-10-06 01:21:15 +0400
build-date: 2011-10-06 12:04:17 +0300
revno: 3213
branch-nick: maria-5.3

 Comments   
Comment by Sergei Petrunia [ 2011-10-11 ]

Re: Wrong result with semijoin + materialization + firstmatch + multipart key
Not repeatable on work.askmonty.org with tip cset:

revno: 3200
revision-id: wlad@montyprogram.com-20110929105820-czlud9qbisbs3zbl

and debug build.

Comment by Sergei Petrunia [ 2011-10-11 ]

Re: Wrong result with semijoin + materialization + firstmatch + multipart key
s/debug build/valgrind build/

Comment by Sergei Petrunia [ 2011-10-11 ]

Re: Wrong result with semijoin + materialization + firstmatch + multipart key
Not repeatable on my laptop, compile-pentium-debug-max build, tip cset:

revno: 3216 [merge]
revision-id: sanja@askmonty.org-20111010203432-kez10hsrgvgu9f3w

(I did take into account the @@optimizer_switch default value change and set all the flags).

Comment by Sergei Petrunia [ 2011-10-11 ]

Re: Wrong result with semijoin + materialization + firstmatch + multipart key
Also not repeatable on work.askmonty.org with tip cset:

revno: 3216 [merge]
revision-id: sanja@askmonty.org-20111010203432-kez10hsrgvgu9f3w

and debug build.

Comment by Sergei Petrunia [ 2011-10-11 ]

Re: Wrong result with semijoin + materialization + firstmatch + multipart key
As pointed by Philip on IRC: current server produces different EXPLAIN:
-------------------------------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

-------------------------------------------------------------------------------------------------------------------------+

1 PRIMARY t2 system NULL NULL NULL NULL 1  
1 PRIMARY t1 ref f3 f3 4 const 1 Using index; Start temporary
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join)

-------------------------------------------------------------------------------------------------------------------------+

Comment by Sergei Petrunia [ 2011-10-11 ]

Re: Wrong result with semijoin + materialization + firstmatch + multipart key
If I do

set join_cache_level=0

then I get an EXPLAIN that's much closer

MariaDB [bug869001]> explain SELECT * FROM t1 JOIN t2 ON (t2.f4 = t1.f3 ) WHERE ( 8 ) IN ( SELECT t3.f1 FROM t3 , t4 );
+--------------------------------------------------------------------------------------

id select_type table type possible_keys key key_len ref rows Extra

--------------------------------------------------------------------------------------+

1 PRIMARY t2 system NULL NULL NULL NULL 1  
1 PRIMARY t1 ref f3 f3 4 const 1 Using index
1 PRIMARY t4 ALL NULL NULL NULL NULL 3  
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1)

--------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)

but it's still different from the plan in the bug report because in this plan t2 has type=system, while there t2 has type=ALL.

It is interesting that in bug report t2 has type=ALL, rows=1. 1-row MyISAM tables become system tables. did the original report use InnODB?

Comment by Sergei Petrunia [ 2011-10-11 ]

Re: Wrong result with semijoin + materialization + firstmatch + multipart key
MariaDB [bug869001]> alter table t2 engine=innodb;
Query OK, 1 row affected (1.43 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [bug869001]> explain SELECT * FROM t1 JOIN t2 ON (t2.f4 = t1.f3 ) WHERE ( 8 ) IN ( SELECT t3.f1 FROM t3 , t4 );
----------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

----------------------------------------------------------------------------------------------+

1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
1 PRIMARY t1 ref f3 f3 4 bug869001.t2.f4 1 Using index
1 PRIMARY t4 ALL NULL NULL NULL NULL 3  
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1)

----------------------------------------------------------------------------------------------+
4 rows in set (0.06 sec)

Comment by Sergei Petrunia [ 2011-10-11 ]

Re: Wrong result with semijoin + materialization + firstmatch + multipart key
The result is still correct, though:
MariaDB [bug869001]> SELECT * FROM t1 JOIN t2 ON (t2.f4 = t1.f3 ) WHERE ( 8 ) IN ( SELECT t3.f1 FROM t3 , t4 );
--------------

f2 f3 f4

--------------

NULL x x
8 x x

--------------
2 rows in set (0.02 sec)

Comment by Philip Stoev (Inactive) [ 2011-10-11 ]

Re: Wrong result with semijoin + materialization + firstmatch + multipart key
InnoDB is required to reproduce this bug.

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

Launchpad bug id: 869001

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