[MDEV-3319] LP:752992 - Wrong results for a subquery with 'semijoin=on' Created: 2011-04-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: Critical
Reporter: Igor Babaev Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug752992.xml    

 Description   

Create and populate tables t1 and t2 with the following commands:

CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
INSERT INTO t1 VALUES (11,0);
INSERT INTO t1 VALUES (12,5);
INSERT INTO t1 VALUES (15,0);
CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
INSERT INTO t2 VALUES (11,1);
INSERT INTO t2 VALUES (12,2);
INSERT INTO t2 VALUES (15,4);

The subquery
SELECT * FROM t1
WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);

returns wrong results with the setting
set optimizer_switch='semijoin=on'; :

MariaDB [test]> set optimizer_switch='semijoin=on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM t1
-> WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
-----+

pk i

-----+

12 5
12 5
12 5

-----+

With the setting:
set optimizer_switch='semijoin=off';
the result is correct:

MariaDB [test]> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM t1
-> WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
-----+

pk i

-----+

12 5

-----+
1 row in set (0.01 sec)

The bug is reproducible on both 5.3 tree and 5.3-subqueries-mwl90



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

Launchpad bug id: 752992

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