[MDEV-3470] LP:943543 - A query with LEFT JOIN returns a wrong result in mariadb 5.3 Created: 2012-02-29  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: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug943543.xml    

 Description   

The following slightly modified test case for mysql bug #58456 produces a wrong result in mariadb-5.3.5

CREATE TABLE t1 (
col_int INT,
col_int_key INT,
pk INT NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
) ENGINE=InnoDB;

INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4);
INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5);

CREATE TABLE t2 (
pk INT PRIMARY KEY
) ENGINE=InnoDB;

INSERT INTO t2 VALUES (3), (8), (5);

SELECT t1.pk
FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
WHERE t1.col_int_key BETWEEN 5 AND 6
AND t1.pk IS NULL OR t1.pk IN (5)
ORDER BY pk;

DROP TABLE t1,t2;

The SELECT of the test case returns an empty set though the expected result is:
----

pk

----

5

----



 Comments   
Comment by Igor Babaev [ 2012-03-01 ]

Re: A query with LEFT JOIN returns a wrong result in mariadb 5.3
The problem can be reproduced with myisam tables as well.

Comment by Igor Babaev [ 2012-03-01 ]

Re: A query with LEFT JOIN returns a wrong result in mariadb 5.3
The wrong result on the reported query is caused by a bug in the recently pushed patch for bug #939009.
Actually even the result of the first query in the test case for bug #939009 is incorrect.

Comment by Elena Stepanova [ 2012-04-27 ]

Re: A query with LEFT JOIN returns a wrong result in mariadb 5.3
Fix released in 5.3.6

Comment by Rasmus Johansson (Inactive) [ 2012-04-27 ]

Launchpad bug id: 943543

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