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

result of join incorrectly depends on their order

    XMLWordPrintable

    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: