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

Incorrect query result after upgrade from 10.2.17 to 10.2.21

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.3, 10.4, 10.2.19, 10.2.20, 10.2.21
    • Fix Version/s: 10.2.23
    • Component/s: Optimizer, Parser
    • Labels:
    • Environment:
      AWS EC2 Instance (for testing)

      Description

      When I upgraded from MariaDB 10.2.17 to 10.2.21 we have observed a change in results of SAME SQL statement.

      In my data set, the following SQL statement returns 45 rows in MariaDB 10.2.15, 10.2.16, 10.2.17:

      SELECT (event.Name LIKE '%test%'
                             OR IFNULL(eventtype_def.Name,'') LIKE '%test%'
                             ) Test1,
      event.Event_No,event.Name,DATE(event_sched.StartDateTime) StartDate
      FROM calendar.event_sched
      JOIN calendar.event ON event.Event_No = event_sched.Event_No
      JOIN calendar.eventtype_def ON eventtype_def.EventTypeDef_No = event.EventTypeDef_No
      JOIN (SELECT Event_No, MIN(StartDateTime) MinStartDateTime,
                          MAX(StartDateTime) MaxStartDateTime
                          FROM calendar.event_sched
                          WHERE Active = 1
                          GROUP BY Event_No) esFirstLast ON esFirstLast.Event_No = event.Event_No
      LEFT   JOIN (SELECT event_subtype.Event_No, GROUP_CONCAT(eventtype_def.Name) SubTypes,
                              GROUP_CONCAT(eventtype_def.PublicColor) SubTypePublicColors,
                              GROUP_CONCAT(eventtype_def.PrivateColor) SubTypePrivateColors
                          FROM calendar.event_subtype
                          JOIN calendar.eventtype_def ON eventtype_def.EventTypeDef_No = event_subtype.EventTypeDef_No
                          WHERE event_subtype.Active = 1
                          GROUP BY event_subtype.Event_No) esubtypes ON esubtypes.Event_No = event.Event_No
      WHERE     IF(1, 
                           (event.Name LIKE '%test%'
                           OR IFNULL(eventtype_def.Name,'') LIKE '%test%'
                             )
                             ,0)
      GROUP BY event.Event_No
      ORDER BY StartDate ASC;
      

      However in MariaDB 10.2.18 all the way up to 10.2.21, the same statement returns 0 rows.

      While researching the issue I do see that between MariaDB 10.2.17 and 10.2.18 the EXPLAIN EXTENDED shows that the optimizer changed how the SQL statement was interpreted between the versions. Here are the results of my tests:

      MariaDB 10.2.16
      Result: Correct 45 rows
      Optimized SQL (From EXPLAIN EXTENDED):

      SELECT 
          `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%' AS `Test1`,
          `test`.`event`.`Event_No` AS `Event_No`,
          `test`.`event`.`Name` AS `Name`,
          CAST(`test`.`event_sched`.`StartDateTime` AS DATE) AS `StartDate`
      FROM
          `test`.`event_sched`
              JOIN
          `test`.`event`
              JOIN
          `test`.`eventtype_def`
              JOIN
          (SELECT 
              `test`.`event_sched`.`Event_No` AS `Event_No`,
                  MIN(`test`.`event_sched`.`StartDateTime`) AS `MinStartDateTime`,
                  MAX(`test`.`event_sched`.`StartDateTime`) AS `MaxStartDateTime`
          FROM
              `test`.`event_sched`
          WHERE
              `test`.`event_sched`.`Active` = 1
                  AND IF(1, `test`.`event`.`Name` LIKE '%test%'
                  OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%', 0)
          GROUP BY `test`.`event_sched`.`Event_No`) `esFirstLast`
              LEFT JOIN
          (SELECT 
              `test`.`event_subtype`.`Event_No` AS `Event_No`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`Name`
                      SEPARATOR ',') AS `SubTypes`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PublicColor`
                      SEPARATOR ',') AS `SubTypePublicColors`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PrivateColor`
                      SEPARATOR ',') AS `SubTypePrivateColors`
          FROM
              `test`.`event_subtype`
          JOIN `test`.`eventtype_def`
          WHERE
              `test`.`event_subtype`.`Active` = 1
                  AND `test`.`event_subtype`.`EventTypeDef_No` = `test`.`eventtype_def`.`EventTypeDef_No`
          GROUP BY `test`.`event_subtype`.`Event_No`) `esubtypes` ON (`esubtypes`.`Event_No` = `esFirstLast`.`Event_No`)
      WHERE
          `test`.`event`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`event_sched`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event`.`EventTypeDef_No`
              AND IF(1,
              `test`.`event`.`Name` LIKE '%test%'
                  OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%',
              0)
      GROUP BY `test`.`event`.`Event_No`
      ORDER BY CAST(`test`.`event_sched`.`StartDateTime` AS DATE)
      

      MariaDB 10.2.17
      Result: Correct 45 rows
      Optimized SQL (From EXPLAIN EXTENDED):

      SELECT 
          `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%' AS `Test1`,
          `test`.`event`.`Event_No` AS `Event_No`,
          `test`.`event`.`Name` AS `Name`,
          CAST(`test`.`event_sched`.`StartDateTime` AS DATE) AS `StartDate`
      FROM
          `test`.`event_sched`
              JOIN
          `test`.`event`
              JOIN
          `test`.`eventtype_def`
              JOIN
          (SELECT 
              `test`.`event_sched`.`Event_No` AS `Event_No`,
                  MIN(`test`.`event_sched`.`StartDateTime`) AS `MinStartDateTime`,
                  MAX(`test`.`event_sched`.`StartDateTime`) AS `MaxStartDateTime`
          FROM
              `test`.`event_sched`
          WHERE
              `test`.`event_sched`.`Active` = 1
                  AND IF(1, `test`.`event`.`Name` LIKE '%test%'
                  OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%', 0)
          GROUP BY `test`.`event_sched`.`Event_No`) `esFirstLast`
              LEFT JOIN
          (SELECT 
              `test`.`event_subtype`.`Event_No` AS `Event_No`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`Name`
                      SEPARATOR ',') AS `SubTypes`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PublicColor`
                      SEPARATOR ',') AS `SubTypePublicColors`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PrivateColor`
                      SEPARATOR ',') AS `SubTypePrivateColors`
          FROM
              `test`.`event_subtype`
          JOIN `test`.`eventtype_def`
          WHERE
              `test`.`event_subtype`.`Active` = 1
                  AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event_subtype`.`EventTypeDef_No`
          GROUP BY `test`.`event_subtype`.`Event_No`) `esubtypes` ON (`esubtypes`.`Event_No` = `esFirstLast`.`Event_No`)
      WHERE
          `test`.`event`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`event_sched`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event`.`EventTypeDef_No`
              AND IF(1,
              `test`.`event`.`Name` LIKE '%test%'
                  OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%',
              0)
      GROUP BY `test`.`event`.`Event_No`
      ORDER BY CAST(`test`.`event_sched`.`StartDateTime` AS DATE);
      

      MariaDB 10.2.18
      Result: INCORRECT 0 rows
      Optimized SQL (From EXPLAIN EXTENDED):

      SELECT 
          `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%' AS `Test1`,
          `test`.`event`.`Event_No` AS `Event_No`,
          `test`.`event`.`Name` AS `Name`,
          CAST(`test`.`event_sched`.`StartDateTime` AS DATE) AS `StartDate`
      FROM
          `test`.`event_sched`
              JOIN
          `test`.`event`
              JOIN
          `test`.`eventtype_def`
              JOIN
          (SELECT 
              `test`.`event_sched`.`Event_No` AS `Event_No`,
                  MIN(`test`.`event_sched`.`StartDateTime`) AS `MinStartDateTime`,
                  MAX(`test`.`event_sched`.`StartDateTime`) AS `MaxStartDateTime`
          FROM
              `test`.`event_sched`
          WHERE
              `test`.`event_sched`.`Active` = 1
          GROUP BY `test`.`event_sched`.`Event_No`
          HAVING IF(1, `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%', 0)) `esFirstLast`
              LEFT JOIN
          (SELECT 
              `test`.`event_subtype`.`Event_No` AS `Event_No`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`Name`
                      SEPARATOR ',') AS `SubTypes`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PublicColor`
                      SEPARATOR ',') AS `SubTypePublicColors`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PrivateColor`
                      SEPARATOR ',') AS `SubTypePrivateColors`
          FROM
              `test`.`event_subtype`
          JOIN `test`.`eventtype_def`
          WHERE
              `test`.`event_subtype`.`Active` = 1
                  AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event_subtype`.`EventTypeDef_No`
          GROUP BY `test`.`event_subtype`.`Event_No`) `esubtypes` ON (`esubtypes`.`Event_No` = `esFirstLast`.`Event_No`)
      WHERE
          `test`.`event`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`event_sched`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event`.`EventTypeDef_No`
              AND IF(1,
              `test`.`event`.`Name` LIKE '%test%'
                  OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%',
              0)
      GROUP BY `test`.`event`.`Event_No`
      ORDER BY CAST(`test`.`event_sched`.`StartDateTime` AS DATE)
      

      MariaDB 10.2.19
      Result: INCORRECT 0 rows
      Optimized SQL (From EXPLAIN EXTENDED):

      SELECT 
          `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%' AS `Test1`,
          `test`.`event`.`Event_No` AS `Event_No`,
          `test`.`event`.`Name` AS `Name`,
          CAST(`test`.`event_sched`.`StartDateTime` AS DATE) AS `StartDate`
      FROM
          `test`.`event_sched`
              JOIN
          `test`.`event`
              JOIN
          `test`.`eventtype_def`
              JOIN
          (SELECT 
              `test`.`event_sched`.`Event_No` AS `Event_No`,
                  MIN(`test`.`event_sched`.`StartDateTime`) AS `MinStartDateTime`,
                  MAX(`test`.`event_sched`.`StartDateTime`) AS `MaxStartDateTime`
          FROM
              `test`.`event_sched`
          WHERE
              `test`.`event_sched`.`Active` = 1
          GROUP BY `test`.`event_sched`.`Event_No`
          HAVING IF(1, `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%', 0)) `esFirstLast`
              LEFT JOIN
          (SELECT 
              `test`.`event_subtype`.`Event_No` AS `Event_No`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`Name`
                      SEPARATOR ',') AS `SubTypes`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PublicColor`
                      SEPARATOR ',') AS `SubTypePublicColors`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PrivateColor`
                      SEPARATOR ',') AS `SubTypePrivateColors`
          FROM
              `test`.`event_subtype`
          JOIN `test`.`eventtype_def`
          WHERE
              `test`.`event_subtype`.`Active` = 1
                  AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event_subtype`.`EventTypeDef_No`
          GROUP BY `test`.`event_subtype`.`Event_No`) `esubtypes` ON (`esubtypes`.`Event_No` = `esFirstLast`.`Event_No`)
      WHERE
          `test`.`event`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`event_sched`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event`.`EventTypeDef_No`
              AND IF(1,
              `test`.`event`.`Name` LIKE '%test%'
                  OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%',
              0)
      GROUP BY `test`.`event`.`Event_No`
      ORDER BY CAST(`test`.`event_sched`.`StartDateTime` AS DATE);
      

      MariaDB 10.2.20
      Result: INCORRECT 0 rows
      Optimized SQL (From EXPLAIN EXTENDED):

      SELECT 
          `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%' AS `Test1`,
          `test`.`event`.`Event_No` AS `Event_No`,
          `test`.`event`.`Name` AS `Name`,
          CAST(`test`.`event_sched`.`StartDateTime` AS DATE) AS `StartDate`
      FROM
          `test`.`event_sched`
              JOIN
          `test`.`event`
              JOIN
          `test`.`eventtype_def`
              JOIN
          (SELECT 
              `test`.`event_sched`.`Event_No` AS `Event_No`,
                  MIN(`test`.`event_sched`.`StartDateTime`) AS `MinStartDateTime`,
                  MAX(`test`.`event_sched`.`StartDateTime`) AS `MaxStartDateTime`
          FROM
              `test`.`event_sched`
          WHERE
              `test`.`event_sched`.`Active` = 1
          GROUP BY `test`.`event_sched`.`Event_No`
          HAVING IF(1, `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%', 0)) `esFirstLast`
              LEFT JOIN
          (SELECT 
              `test`.`event_subtype`.`Event_No` AS `Event_No`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`Name`
                      SEPARATOR ',') AS `SubTypes`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PublicColor`
                      SEPARATOR ',') AS `SubTypePublicColors`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PrivateColor`
                      SEPARATOR ',') AS `SubTypePrivateColors`
          FROM
              `test`.`event_subtype`
          JOIN `test`.`eventtype_def`
          WHERE
              `test`.`event_subtype`.`Active` = 1
                  AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event_subtype`.`EventTypeDef_No`
          GROUP BY `test`.`event_subtype`.`Event_No`) `esubtypes` ON (`esubtypes`.`Event_No` = `esFirstLast`.`Event_No`)
      WHERE
          `test`.`event`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`event_sched`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event`.`EventTypeDef_No`
              AND IF(1,
              `test`.`event`.`Name` LIKE '%test%'
                  OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%',
              0)
      GROUP BY `test`.`event`.`Event_No`
      ORDER BY CAST(`test`.`event_sched`.`StartDateTime` AS DATE);
      

      MariaDB 10.2.21
      Result: INCORRECT 0 rows
      Optimized SQL (From EXPLAIN EXTENDED):

      SELECT 
          `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%' AS `Test1`,
          `test`.`event`.`Event_No` AS `Event_No`,
          `test`.`event`.`Name` AS `Name`,
          CAST(`test`.`event_sched`.`StartDateTime` AS DATE) AS `StartDate`
      FROM
          `test`.`event_sched`
              JOIN
          `test`.`event`
              JOIN
          `test`.`eventtype_def`
              JOIN
          (SELECT 
              `test`.`event_sched`.`Event_No` AS `Event_No`,
                  MIN(`test`.`event_sched`.`StartDateTime`) AS `MinStartDateTime`,
                  MAX(`test`.`event_sched`.`StartDateTime`) AS `MaxStartDateTime`
          FROM
              `test`.`event_sched`
          WHERE
              `test`.`event_sched`.`Active` = 1
          GROUP BY `test`.`event_sched`.`Event_No`
          HAVING IF(1, `test`.`event`.`Name` LIKE '%test%'
              OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%', 0)) `esFirstLast`
              LEFT JOIN
          (SELECT 
              `test`.`event_subtype`.`Event_No` AS `Event_No`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`Name`
                      SEPARATOR ',') AS `SubTypes`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PublicColor`
                      SEPARATOR ',') AS `SubTypePublicColors`,
                  GROUP_CONCAT(`test`.`eventtype_def`.`PrivateColor`
                      SEPARATOR ',') AS `SubTypePrivateColors`
          FROM
              `test`.`event_subtype`
          JOIN `test`.`eventtype_def`
          WHERE
              `test`.`event_subtype`.`Active` = 1
                  AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event_subtype`.`EventTypeDef_No`
          GROUP BY `test`.`event_subtype`.`Event_No`) `esubtypes` ON (`esubtypes`.`Event_No` = `esFirstLast`.`Event_No`)
      WHERE
          `test`.`event`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`event_sched`.`Event_No` = `esFirstLast`.`Event_No`
              AND `test`.`eventtype_def`.`EventTypeDef_No` = `test`.`event`.`EventTypeDef_No`
              AND IF(1,
              `test`.`event`.`Name` LIKE '%test%'
                  OR IFNULL(`test`.`eventtype_def`.`Name`, '') LIKE '%test%',
              0)
      GROUP BY `test`.`event`.`Event_No`
      ORDER BY CAST(`test`.`event_sched`.`StartDateTime` AS DATE)
      

      Please let me know if I can provide any additional data to help. I have attached the schema definitions of the tables that are involved in the queries to this ticket.

      It may be worth knowing that the following query returns 45 rows consistently between 10.2.17 and 10.2.18 (the join on the first derived table is changed from an INNER to an LEFT):

      SELECT (event.Name LIKE '%test%'
                             OR IFNULL(eventtype_def.Name,'') LIKE '%test%'
                             ) Test1,
      event.Event_No,event.Name,DATE(event_sched.StartDateTime) StartDate
      FROM test.event_sched
      JOIN test.event ON event.Event_No = event_sched.Event_No
      JOIN test.eventtype_def ON eventtype_def.EventTypeDef_No = event.EventTypeDef_No
      LEFT JOIN (SELECT Event_No, MIN(StartDateTime) MinStartDateTime,
                          MAX(StartDateTime) MaxStartDateTime
                          FROM test.event_sched
                          WHERE Active = 1
                          GROUP BY Event_No) esFirstLast ON esFirstLast.Event_No = event.Event_No
      LEFT   JOIN (SELECT event_subtype.Event_No, GROUP_CONCAT(eventtype_def.Name) SubTypes,
                              GROUP_CONCAT(eventtype_def.PublicColor) SubTypePublicColors,
                              GROUP_CONCAT(eventtype_def.PrivateColor) SubTypePrivateColors
                          FROM test.event_subtype
                          JOIN test.eventtype_def ON eventtype_def.EventTypeDef_No = event_subtype.EventTypeDef_No
                          WHERE event_subtype.Active = 1
                          GROUP BY event_subtype.Event_No) esubtypes ON esubtypes.Event_No = event.Event_No
      WHERE     IF(1, 
                           (event.Name LIKE '%test%'
                           OR IFNULL(eventtype_def.Name,'') LIKE '%test%'
                             )
                             ,0)
      GROUP BY event.Event_No;
      

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              brycejlowe Bryce Lowe
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: