I Have a request that produce a bad result. I'm able to rewrite this request in a form that always produce a good result. But I don't understand the real reason of the problem. I have tried to simplify the dataset but the problem doesn't appear with less data. The request produce 16 rows (but only 15 are expected) If I add a filter for the first column like : col1 = 'aaa' I get the 15 expected rows. But if I put the filter with col1 <> 'aaa' I get 0 rows and it's the same with col1 IS NULL col1 is not NULL give 16 rows. Some other strange elements - If I try to isolate the rows used in the different tables for this request in "test" tables and use the test tables the problem doesn't appear. - If I don't use the regexp_split_to_table function (but a simple equality), the problem doesn't appear (but in fact with the problematic dataset, no element contain the split separator). - If I remove the "sub.evnt IS NULL OR" part from the (sub.evnt IS NULL OR p.evnt IN (SELECT regexp_split_to_table(sub.evnt,'#'))) the problem doesn't appear. - The "cnt" can be different even if the input data are the same. - If I remove the "ORDER BY x.id" I can obtain more rows. So the idea is not to find a good way to write the request but just understand why these problem of "virtual" rows appears. Is the problem related to the imbrication of the LATERAL clause and regexp_split_to_table or not ? So the request was : The version of Postgres is "PostgreSQL 9.3.6 on x86 64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19Ubuntu1) 4.8.2, 64-bit" === SELECT DISTINCT col1, date_start, date_end, evt, x.* FROM ( SELECT f.*, c.evt FROM tmp.debug1 f LEFT JOIN tmp.debug2 c ON f.sport=c.sport AND f.compet=c.code ) sub LEFT JOIN LATERAL (SELECT x.id, x.nom, (SELECT count(*) FROM mother_table_with_some_herited_childs p WHERE p.id = x.id AND date_evt::date BETWEEN sub.date_start AND sub.date_end AND (sub.evt IS NULL OR p.evt IN (SELECT regexp_split_to_table(sub.evt,'#'))) ) as cnt FROM tmp.debug3 c LEFT JOIN LATERAL (SELECT * FROM identify i WHERE i.id = c.id) x ON TRUE WHERE c.hash = sub.hash ORDER BY x.id ) x ON true --WHERE col1 = 'aaa' -- <> 'aaa' -- IS NULL -- IS NOT NULL === Thanks for your ideas -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
