Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4840

Wrong result (missing rows) on LEFT JOIN with InnoDB tables

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.3, 5.5.32, 5.1.67, 5.2.14, 5.3.12
    • Fix Version/s: 10.0.5, 5.5.33
    • Component/s: None
    • Labels:
      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

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: