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

Left joined subquery gives wrong result

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Duplicate
    • Affects Version/s: 5.3.12, 5.5.40, 10.0.14
    • Fix Version/s: N/A
    • Component/s: Views
    • Labels:
    • Environment:
      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

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

                Dates

                • Created:
                  Updated:
                  Resolved: