Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
5.3.12, 5.5.40, 10.0.14
-
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
|