|
I have the same problem in version 10.3.32.
We just switch from 10.3.23 to 10.3.32.
I tried all versions between and problem seems to appear in 10.3.29 version. 10.3.28 is still good.
Using `SET optimizer_switch="split_materialized=off";` fixes the problem too.
To reproduce with the joined dataset.sql, try to execute this request :
SELECT m.id AS mission_id, a.applicants_users_id AS applicant_id
|
,sub.sent
|
FROM t_missions m
|
INNER JOIN t_applications a ON a.offers_id = m.offers_id
|
INNER JOIN t_user u ON u.id = a.applicants_users_id
|
LEFT JOIN (
|
SELECT m2.id AS mission_id, a2.applicants_users_id AS applicant_id, TRUE as sent
|
FROM t_missions m2
|
INNER JOIN t_applications a2 ON a2.offers_id = m2.offers_id
|
AND a2.application_date IS NOT NULL
|
WHERE m2.mission_status = 2
|
GROUP BY a2.offers_id, a2.applicants_users_id
|
) sub ON sub.applicant_id = a.applicants_users_id AND sub.mission_id = m.id;
|
It give us :
With JOIN clause in subquery : WRONG RESULT
Without some clauses in subquery, (comment `AND a2.application_date IS NOT NULL` or `WHERE...` or `ORDER BY...`) : GOOD RESULT
(In both case, subquery return same results, so final result should be the same too)
Without indexes in applications table, we got good result too.
Problem appear when LATERAL DERIVED select type is used.
Thanks for help !
| 
dataset.sql
screenshot-1.png
