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
Bug
Major
