[MDEV-3541] LP:702374 - wrong query result in subselect_sj.test Created: 2011-01-13  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug702374.xml    

 Description   

The testcase for "Bug#46550 Azalea returning duplicate results for some IN subqueries" in subselect_sj.test produces wrong results:

CREATE TABLE t0 (
int_key int(11) DEFAULT NULL,
varchar_key varchar(1) DEFAULT NULL,
varchar_nokey varchar(1) DEFAULT NULL,
KEY int_key (int_key),
KEY varchar_key (varchar_key,int_key)
);

INSERT INTO t0 VALUES
(1,'m','m'),
(40,'h','h'),
(1,'r','r'),
(1,'h','h'),
(9,'x','x'),
(NULL,'q','q'),
(NULL,'k','k'),
(7,'l','l'),
(182,'k','k'),
(202,'a','a'),
(7,'x','x'),
(6,'j','j'),
(119,'z','z'),
(4,'d','d'),
(5,'h','h'),
(1,'u','u'),
(3,'q','q'),
(7,'a','a'),
(3,'e','e'),
(6,'l','l');

CREATE TABLE t1 (
int_key int(11) DEFAULT NULL,
varchar_key varchar(1) DEFAULT NULL,
varchar_nokey varchar(1) DEFAULT NULL,
KEY int_key (int_key),
KEY varchar_key (varchar_key,int_key)
);
INSERT INTO t1 VALUES (7,NULL,NULL),(4,'x','x');

CREATE TABLE t2 (
int_key int(11) DEFAULT NULL,
varchar_key varchar(1) DEFAULT NULL,
varchar_nokey varchar(1) DEFAULT NULL,
KEY int_key (int_key),
KEY varchar_key (varchar_key,int_key)
);
INSERT INTO t2 VALUES (123,NULL,NULL);

MariaDB [j6]> SELECT t0.int_key FROM t0, t2 WHERE t0.varchar_nokey IN (SELECT t1_1 .varchar_key FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key);
---------

int_key

---------

9
9
7
7

---------
4 rows in set (0.00 sec)

The correct result is

{ (9), (7)}

, i.e. we get duplicates.

 Comments   
Comment by Sergei Petrunia [ 2011-01-13 ]

Re: wrong query result with semi-join + FirstMatch (subselect_sj.test)
The EXPLAIN:

MariaDB [j6]> explain SELECT t0.int_key FROM t0, t2 WHERE t0.varchar_nokey IN ( SELECT t1_1 .varchar_key FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key );
----------------------------------------------------------------------------------------------------------------------------------+

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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; LooseScan
1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; FirstMatch(t1_1); Using join buffer (flat, BNL join)
1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join)

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

Comment by Sergei Petrunia [ 2012-05-23 ]

Re: wrong query result in subselect_sj.test
Currently, the testcase has correct results.

Comment by Rasmus Johansson (Inactive) [ 2012-05-23 ]

Launchpad bug id: 702374

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