[MDEV-2700] LP:784723 - Wrong result with semijoin + nested subqueries in maria-5.3 Created: 2011-05-18  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
Reporter: Philip Stoev (Inactive) Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug784723.xml    

 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.

 Comments   
Comment by Sergei Petrunia [ 2011-05-19 ]

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)

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

Launchpad bug id: 784723

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