Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
As reported by Igor:
This test case:
CREATE TABLE t1 ( t1field integer, primary key (t1field));
CREATE TABLE t2 ( t2field integer, primary key (t2field));
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);
SELECT * FROM t1 A WHERE A.t1field IN (SELECT A.t1field FROM t2 B) AND A.t1field IN (SELECT C.t2field FROM t2 C WHERE C.t2field IN (SELECT D.t2field FROM t2 D));
produces 6 rows with semijoin=on, which is more than the size of the base table that is being filtered by the WHERE.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 784723
Re: Wrong result with semijoin + nested subqueries in maria-5.3
@@Join_cache_level setting has no effect on the problem.
The following patch:
revision-id: psergey@askmonty.org-20110509102051-jtywtgst71h9s0jj
parent: psergey@askmonty.org-20110509093555-9od2mi1lsh9tnyr5
committer: Sergey Petrunya <psergey@askmonty.org>
branch nick: maria-5.3-subqueries-r42
timestamp: Mon 2011-05-09 11:20:51 +0100
message:
Change "disable semi-joins in presense of outer joins" check to actually
check for outer joins (and not for any "t1 SOME_JOIN t2 ON cond" syntax).
also has no relationship to the problem (the bug is repeatable both with it and without)