[MDEV-18307] Incorrect query result after upgrade from 10.2.17 to 10.2.21 - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.19, 10.2.20, 10.2.21, 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.23
Component/s: Optimizer, Parser
Labels:
- regression
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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

bug_schema.sql
4 kB
2019-01-18 20:56

Activity

People

Assignee:: Igor Babaev

Reporter:: Bryce Lowe

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2019-01-18 21:08

Updated:: 2019-04-06 17:11

Resolved:: 2019-04-06 17:11

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.