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

missing rows with condition on subselect

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.3, 10.3.8
    • Fix Version/s: 10.2.18
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      debian/jessie

      Description

      I got some strange behaviour with subselect and missing rows.

      following select produces 2 rows, and is_blacklisted equals 0 in both:

      SELECT anon_1.shop_id AS shop_id,
             anon_1.is_blacklisted AS is_blacklisted
      FROM
        (SELECT anon_2.shop_id AS shop_id,
                CASE
                    WHEN (
                              (
                                   (
                                       anon_2.shop_white_flag IS NOT NULL
                                       AND (anon_2.white_flags IS NULL
                                            OR anon_2.white_flags NOT LIKE concat(concat('%|', anon_2.shop_white_flag), '|%')))
                                   )
                          ) THEN 1
                    ELSE 0
                END AS is_blacklisted
         FROM
           (SELECT
                   customer_data.shop_id AS shop_id,
                   syndication_white_flags_1.code AS shop_white_flag,
                   group_concat(DISTINCT concat('|', syndication_white_flags.code, '|')) AS white_flags
            FROM customer_data
            INNER JOIN syndication_partners
            LEFT OUTER JOIN syndication_partner_white_flags ON syndication_partner_white_flags.partner_id = syndication_partners.id
            LEFT OUTER JOIN syndication_white_flags ON syndication_white_flags.id = syndication_partner_white_flags.flag_id
            LEFT OUTER JOIN syndication_shop_white_flags ON syndication_shop_white_flags.customer_data_id = customer_data.id
            LEFT OUTER JOIN syndication_white_flags AS syndication_white_flags_1 ON syndication_white_flags_1.id = syndication_shop_white_flags.white_flag_id
            WHERE
              syndication_partners.id = 427
            GROUP BY customer_data.shop_id,
                     syndication_white_flags_1.code
            ) AS anon_2
          ) AS anon_1
      ;
      

      Now I add a condition on is_blacklisted and I only get 1 row. (I tried several versions, "IS false", "= 0", "IS NOT true", all with the same behaviour)

      SELECT anon_1.shop_id AS shop_id,
             anon_1.is_blacklisted AS is_blacklisted
      FROM
        (SELECT anon_2.shop_id AS shop_id,
                CASE
                    WHEN (
                              (
                                   (
                                       anon_2.shop_white_flag IS NOT NULL
                                       AND (anon_2.white_flags IS NULL
                                            OR anon_2.white_flags NOT LIKE concat(concat('%|', anon_2.shop_white_flag), '|%')))
                                   )
                          ) THEN 1
                    ELSE 0
                END AS is_blacklisted
         FROM
           (SELECT
                   customer_data.shop_id AS shop_id,
                   syndication_white_flags_1.code AS shop_white_flag,
                   group_concat(DISTINCT concat('|', syndication_white_flags.code, '|')) AS white_flags
            FROM customer_data
            INNER JOIN syndication_partners
            LEFT OUTER JOIN syndication_partner_white_flags ON syndication_partner_white_flags.partner_id = syndication_partners.id
            LEFT OUTER JOIN syndication_white_flags ON syndication_white_flags.id = syndication_partner_white_flags.flag_id
            LEFT OUTER JOIN syndication_shop_white_flags ON syndication_shop_white_flags.customer_data_id = customer_data.id
            LEFT OUTER JOIN syndication_white_flags AS syndication_white_flags_1 ON syndication_white_flags_1.id = syndication_shop_white_flags.white_flag_id
            WHERE
              syndication_partners.id = 427
            GROUP BY customer_data.shop_id,
                     syndication_white_flags_1.code
            ) AS anon_2
          ) AS anon_1
      WHERE anon_1.is_blacklisted IS false
      ;
      

      When I select into a temporary table, I get 2 rows again.

      CREATE TEMPORARY TABLE foo SELECT anon_1.shop_id AS shop_id,
             anon_1.is_blacklisted AS is_blacklisted
      FROM
        (SELECT anon_2.shop_id AS shop_id,
                CASE
                    WHEN (
                              (
                                   (
                                       anon_2.shop_white_flag IS NOT NULL
                                       AND (anon_2.white_flags IS NULL
                                            OR anon_2.white_flags NOT LIKE concat(concat('%|', anon_2.shop_white_flag), '|%')))
                                   )
                          ) THEN 1
                    ELSE 0
                END AS is_blacklisted
         FROM
           (SELECT
                   customer_data.shop_id AS shop_id,
                   syndication_white_flags_1.code AS shop_white_flag,
                   group_concat(DISTINCT concat('|', syndication_white_flags.code, '|')) AS white_flags
            FROM customer_data
            INNER JOIN syndication_partners
            LEFT OUTER JOIN syndication_partner_white_flags ON syndication_partner_white_flags.partner_id = syndication_partners.id
            LEFT OUTER JOIN syndication_white_flags ON syndication_white_flags.id = syndication_partner_white_flags.flag_id
            LEFT OUTER JOIN syndication_shop_white_flags ON syndication_shop_white_flags.customer_data_id = customer_data.id
            LEFT OUTER JOIN syndication_white_flags AS syndication_white_flags_1 ON syndication_white_flags_1.id = syndication_shop_white_flags.white_flag_id
            WHERE
              syndication_partners.id = 427
            GROUP BY customer_data.shop_id,
                     syndication_white_flags_1.code
            ) AS anon_2
          ) AS anon_1
      WHERE anon_1.is_blacklisted IS false
      ;
      

      If I remove the syndication_white_flags_1.code in the group_by, it works too.
      This bug didn't exist in 10.0.35.
      We skipped the versions in between, so I can't say if this bug already existed in 10.1 or 10.2

      Possibly related to: https://bugs.mysql.com/bug.php?id=88300
      I don't think it's the same bug, but it's the only thing I found related to subqueries and incorrect returnsets

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              shagalla Galina Shalygina (Inactive)
              Reporter:
              Benjamin Setzer Benjamin Setzer
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

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