[GENERAL] Problem with Lateral ?

Поиск
Список
Период
Сортировка
От benj.dev
Тема [GENERAL] Problem with Lateral ?
Дата
Msg-id 3a161163-8dcc-19ee-3fc2-24306e6ea833@laposte.net
обсуждение исходный текст
Ответы Re: [GENERAL] Problem with Lateral ?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: George Neuner
Дата:
Сообщение: Re: [GENERAL] pgadmin - import a CSV with nulls?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] Problem with Lateral ?