Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.3.7
Description
after migrating from 10.2.xx to 10.3.7, the result produced by subselect queries with multiple joins appears sometimes to depends on the order of the join
like in:
select * from (
select *
from A
join B on A.xx=B.xx
join C on A.xx=C.xx
) t1
join (select ...) t2 on ...
group by ...
producing a different result from:
select * from (
select *
from A
join C on A.xx=C.xx
join B on A.xx=B.xx
) t1
join (select ...) t2 on ...
group by ...
in the more detailed example below, the first query correctly returns rows and the second does not (but no error is produced):
SELECT SQL_NO_CACHE |
`c_1001` AS `s_559c1`, |
SUM(`c_1003`) AS `s_559c2` |
FROM
|
(
|
SELECT |
`s_5162c5` AS `c_1001`, |
`c_1010` AS `c_1005`, |
`c_1011` AS `c_1007`, |
SUM(`s_5162c8`) AS `c_1003` |
FROM |
(
|
SELECT |
PAY.`DESCRIPTION` AS `s_5162c5`, |
TRA_PAY.`AMOUNT` AS `s_5162c8`, |
TRA.DATABASE_ID AS `c_1010`, |
TRA.SHOP_ID AS `c_1011` |
FROM `my_db`.`TRANS_PAYMENTS` TRA_PAY |
JOIN `my_db`.`PAYMENTS` PAY ON TRA_PAY.STD_PAYMENT_ID = PAY.ID |
JOIN `my_db`.`TRANSACTIONS` TRA ON TRA_PAY.DATABASE_ID = TRA.DATABASE_ID AND TRA_PAY.TRANSACTION_ID = TRA.ID |
WHERE (TRA_PAY.`BOOKKEEPING_DATE` = DATE_SUB('2018-06-12', INTERVAL 1 DAY)) |
) _t_s_5162
|
GROUP BY `c_1001` , `c_1005` , `c_1007` |
) _t_s_1000
|
JOIN ( |
SELECT |
SHO.DATABASE_ID AS `c_1006`, |
SHO.ID AS `c_1008` |
FROM `eldora_data`.`SHOPS` SHO |
) _t_c_1004 ON `_t_s_1000`.`c_1005` = `_t_c_1004`.`c_1006` AND `_t_s_1000`.`c_1007` = `_t_c_1004`.`c_1008` |
GROUP BY `s_559c1` |
SELECT SQL_NO_CACHE |
`c_1001` AS `s_559c1`, |
SUM(`c_1003`) AS `s_559c2` |
FROM
|
(
|
SELECT |
`s_5162c5` AS `c_1001`, |
`c_1010` AS `c_1005`, |
`c_1011` AS `c_1007`, |
SUM(`s_5162c8`) AS `c_1003` |
FROM |
(
|
SELECT |
PAY.`DESCRIPTION` AS `s_5162c5`, |
TRA_PAY.`AMOUNT` AS `s_5162c8`, |
TRA.DATABASE_ID AS `c_1010`, |
TRA.SHOP_ID AS `c_1011` |
FROM `my_db`.`TRANS_PAYMENTS` TRA_PAY |
JOIN `my_db`.`TRANSACTIONS` TRA ON TRA_PAY.DATABASE_ID = TRA.DATABASE_ID AND TRA_PAY.TRANSACTION_ID = TRA.ID |
JOIN `my_db`.`PAYMENTS` PAY ON TRA_PAY.STD_PAYMENT_ID = PAY.ID |
WHERE (TRA_PAY.`BOOKKEEPING_DATE` = DATE_SUB('2018-06-12', INTERVAL 1 DAY)) |
) _t_s_5162
|
GROUP BY `c_1001` , `c_1005` , `c_1007` |
) _t_s_1000
|
JOIN ( |
SELECT |
SHO.DATABASE_ID AS `c_1006`, |
SHO.ID AS `c_1008` |
FROM `eldora_data`.`SHOPS` SHO |
) _t_c_1004 ON `_t_s_1000`.`c_1005` = `_t_c_1004`.`c_1006` AND `_t_s_1000`.`c_1007` = `_t_c_1004`.`c_1008` |
GROUP BY `s_559c1` |