[MDEV-4840] Wrong result (missing rows) on LEFT JOIN with InnoDB tables - Jira

XML

Word

Printable

With the test case below, the first query produces 3 rows, which I believe to be the correct result:

SELECT * FROM iso_code LEFT JOIN ( city LEFT JOIN country ON city.country_code  = country.code ) ON iso_code.alpha3 = country.code ;

alpha3	country_code	name	code	name

USA	USA	Austin	USA	United States

USA	USA	Boston	USA	United States

CAN	NULL	NULL	NULL	NULL

But the second query, which only differs from the previous one by the select list, produces two rows:

SELECT iso_code.alpha3 FROM iso_code LEFT JOIN ( city LEFT JOIN country ON city.country_code  = country.code ) ON iso_code.alpha3 = country.code ;

alpha3

USA

CAN

Reproducible on MariaDB 5.1 from the beginning of time (tried 5.1.42), 5.2, 5.3, 5.5, 10.0.
Not reproducible on MySQL 5.1, 5.5, 5.6.

--source include/have_innodb.inc

CREATE TABLE iso_code (alpha3 VARCHAR(3)) ENGINE=InnoDB;

INSERT INTO iso_code VALUES ('USA'),('CAN');

CREATE TABLE city ( country_code VARCHAR(3), name VARCHAR(64)) ENGINE=InnoDB;

INSERT INTO city VALUES ('USA','Austin'),('USA','Boston');

CREATE TABLE country ( code VARCHAR(3),  name VARCHAR(64),  PRIMARY KEY (code),  UNIQUE KEY (name)) ENGINE=InnoDB;

INSERT INTO country VALUES ('CAN','Canada'),('USA','United States');

SELECT * FROM iso_code LEFT JOIN ( city LEFT JOIN country ON city.country_code  = country.code ) ON iso_code.alpha3 = country.code ;

SELECT iso_code.alpha3 FROM iso_code LEFT JOIN ( city LEFT JOIN country ON city.country_code  = country.code ) ON iso_code.alpha3 = country.code ;

DROP TABLE iso_code, city, country;

EXPLAIN:

EXPLAIN EXTENDED

SELECT iso_code.alpha3 FROM iso_code LEFT JOIN ( city LEFT JOIN country ON city.country_code  = country.code ) ON iso_code.alpha3 = country.code ;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	iso_code	ALL	NULL	NULL	NULL	NULL	2	100.00

Warnings:

Note	1003	select `test`.`iso_code`.`alpha3` AS `alpha3` from `test`.`iso_code` where 1