Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.3, 5.5.32, 5.1.67, 5.2.14, 5.3.12
-
None
-
None
Description
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
|