From: | "benj(dot)dev" <benj(dot)dev(at)laposte(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem with Lateral ? |
Date: | 2017-08-31 21:15:08 |
Message-ID: | 3a161163-8dcc-19ee-3fc2-24306e6ea833@laposte.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-08-31 22:42:45 | Re: Problem with Lateral ? |
Previous Message | George Neuner | 2017-08-31 20:46:17 | Re: pgadmin - import a CSV with nulls? |