Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.8, 10.2(EOL), 10.3(EOL)
-
None
-
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
- relates to
-
-
- Closed
-