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

Left joined subquery gives wrong result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • 5.3.12, 5.5.40, 10.0.14
    • N/A
    • Views
    • Windows Server 2012

    Description

      The result of the query stated below gives always an amount of 1.

      CREATE TABLE `table1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) DEFAULT NULL,                                      
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      CREATE TABLE `table2` (
        `user_id` int(11) NOT NULL,
        `table1_id` int(11) NOT NULL,
        PRIMARY KEY (`user_id`,`table1_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      INSERT INTO table1 (`id`,`name`) 
      	VALUES
      		(1,'test1'),
      		(2,'test2'),
      		(3,'test3'),
      		(4,'test4'),
      		(5,'test5'),
      		(6,'test6'),
      		(7,'test7'),
      		(8,'test8'),
      		(9,'test9'),
      		(10,'test10');
      INSERT INTO table2 (`table1_id`,`user_id`)
      	VALUES
      	(1,1), 
      	(2,2),
      	(3,1),
      	(4,2),
      	(5,1),
      	(6,2), 
      	(7,1),
      	(8,2),
      	(9,1),
      	(10,2);
      SELECT t1.id, COALESCE(t2.amount, 0) AS amount 
      FROM table1 t1 
        LEFT JOIN (SELECT  1 AS amount, table1_id, user_id 
        FROM    table2) t2 
          ON t2.table1_id = t1.id AND t2.user_id = 1;

      When this query was run on MySQL 5.6, the result was as expected;
      Result MariaDB:

      1	1
      2	1
      3	1
      4	1
      5	1
      6	1
      7	1
      8	1
      9	1
      10	1

      Result MySQL:

      1	1
      2	0
      3	1
      4	0
      5	1
      6	0
      7	1
      8	0
      9	1
      10	0

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              dmi Dennis Minderhoud
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.