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

result of join incorrectly depends on their order

    Details

      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` 
      

        Attachments

          Activity

            People

            • Assignee:
              alice Alice Sherepa
              Reporter:
              alebacq antoine
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: