Обсуждение: SQL parser ubnormal behaviour

"Zakharov, Andrey" <AZakharov@luxoft.com> writes:
> 1)

> SELECT COUNT(app.paapl_application_id) OVER() AS totalCount,
> app.edcit_citizenship_id, app.paapl_application_id, app.paapl_blocker_resolution_check,
> app.paapl_dob, app.paapl_gender, app.paapl_inn, app.paapl_is_fprint_pkg_completed,
> app.paapl_is_migreg_pkg_completed, app.paapl_last_name, app.paapl_last_name_latin,
> app.paapl_name, app.paapl_name_latin, app.paapl_note, app.paapl_patent_decision_comment,
> app.paapl_phase_start_ts, app.paapl_processing_start, app.paapl_second_name, app.paapl_second_name_latin,
> app.paapl_sf_is_ext_wait_complete, app.paapl_status_ts, app.paapl_type, app.pablk_application_blocker_id,
> doc.padoc_number, doc.padoc_series, app.paopr_last_name_locked, app.paopr_login_locked, app.paopr_name_locked,
> app.paopr_second_name_locked, passport.pascn_document_scan_id_trans, app.pasts_status_id
> FROM pa_application AS app
> LEFT JOIN pa_document AS doc
>             ON app.paapl_application_id = doc.paapl_application_id
>             AND (doc.padtp_document_type_id = 5)
> LEFT JOIN pa_passport AS passport
>             ON doc.padoc_document_id = passport.padoc_document_id
> Inner join pa_application_indicator as ind
> on ind.paapl_application_id=app.paapl_application_id
> WHERE (app.pasts_status_id = 3) and ind.paidc_ppot_sent='Y' and ind.paidc_ppot_decision_made='N'
> GROUP BY app.paapl_application_id, doc.padoc_number, doc.padoc_series, passport.pascn_document_scan_id_trans
> ORDER BY paapl_application_id LIMIT 10
> ;

> Expected result: an SQL parser error because at least the set columns in the resultset must be the same as in the
"groupby" section. 

Probably, the reason the parser accepts this query is that the GROUP BY
columns include the primary keys of all three tables.  If so, that is
not a bug, it's a feature --- one required by the SQL standard, in fact.

> 2)

>        select *--count(*)
>        from pa_application_event ae
>        where ae.paeve_event_id = 23
>              and ae.paevt_event_time between '2016-02-10 08:00:00' and '2016-02-10 19:59:59'
>              and ae.paapl_application_id =
>              (
>                     select paapl_application_id
>                     where paevt_parameters like '%murzinaaa%'
>              )

> Expected result: an SQL parser error because FROM keyword is missing in the subquery and run-time error because there
isequal sign after "paapl_application_id" for the subquery that returns many rows. 

This is not a bug either.  Postgres does not require a FROM clause.
(If you're used to Oracle, you can imagine that there's an implicit
"FROM DUAL" in there.)  paapl_application_id and paevt_parameters are
being taken as outer references, so you get either the current value of
paapl_application_id or NULL depending on whether the LIKE condition is
satisfied.  In no case would you get multiple rows out of the sub-select,
so there is no reason for a run-time error.

            regards, tom lane

On 2/11/16, Zakharov, Andrey <AZakharov@luxoft.com> wrote:
> Dear colleagues -
>
> There are the serious problems found in SQL parser for versions 9.3.1 thru
> newest 9.5. Perhaps they all depends on data distribution but I cannot be
> sure. Such behavior constantly presents and reproducible. FAQs and Tips have
> been looked thru carefully but such stuff is not there.
>
> The problem is: the following types of SQL statements are OK for the SQL
> parser and DB engine returns the resultset.
>
> 1)
>
> SELECT COUNT(app.paapl_application_id) OVER() AS totalCount,
> app.edcit_citizenship_id, app.paapl_application_id,
> app.paapl_blocker_resolution_check,
> app.paapl_dob, app.paapl_gender, app.paapl_inn,
> app.paapl_is_fprint_pkg_completed,
> app.paapl_is_migreg_pkg_completed, app.paapl_last_name,
> app.paapl_last_name_latin,
> app.paapl_name, app.paapl_name_latin, app.paapl_note,
> app.paapl_patent_decision_comment,
> app.paapl_phase_start_ts, app.paapl_processing_start, app.paapl_second_name,
> app.paapl_second_name_latin,
> app.paapl_sf_is_ext_wait_complete, app.paapl_status_ts, app.paapl_type,
> app.pablk_application_blocker_id,
> doc.padoc_number, doc.padoc_series, app.paopr_last_name_locked,
> app.paopr_login_locked, app.paopr_name_locked,
> app.paopr_second_name_locked, passport.pascn_document_scan_id_trans,
> app.pasts_status_id
> FROM pa_application AS app
> LEFT JOIN pa_document AS doc
>             ON app.paapl_application_id = doc.paapl_application_id
>             AND (doc.padtp_document_type_id = 5)
> LEFT JOIN pa_passport AS passport
>             ON doc.padoc_document_id = passport.padoc_document_id
> Inner join pa_application_indicator as ind
> on ind.paapl_application_id=app.paapl_application_id
> WHERE (app.pasts_status_id = 3) and ind.paidc_ppot_sent='Y' and
> ind.paidc_ppot_decision_made='N'
> GROUP BY app.paapl_application_id, doc.padoc_number, doc.padoc_series,
> passport.pascn_document_scan_id_trans
> ORDER BY paapl_application_id LIMIT 10
> ;

I guess you have a typo: you have app.paapl_application_id in an
aggregate function (count) _and_ in the "GROUP BY" clause, but the
other column (app.edcit_citizenship_id) is not in the "GROUP BY"
clause but mentioned "as is", i.e. not in any aggregate function.

Please, read error message in your screenshot. It gives enough information.

> ________________________________
> This e-mail and any attachment(s) are intended only for the recipient(s)

Please, don't use such kind of messages when you send letter to a mailing list.
--
Best regards,
Vitaly Burovoy

On 2/11/16, Zakharov, Andrey <AZakharov@luxoft.com> wrote:
> Vitaly,
> This query doesnt throw the error. It accepts by the parser and returning
> the rows. Thats the problem.

Ough... I'm sorry, I wasn't so attentive reading your letter.

Tom Lane has described it enough[1]. But I can give you a link to the
documentation[2] (emphasizing is mine).

> When GROUP BY is present, or any aggregate functions are present,
> it is not valid for the SELECT list expressions to refer to ungrouped col=
umns
> except within aggregate functions or _when_ the _ungrouped_ _column_
> _is_ functionally_ _dependent_ _on_ the _grouped_ _columns_, ...
> A _functional_ _dependency_ exists _if_ the _grouped_ _columns_
> (or a subset thereof) _are_ the _primary_ _key_ of the table _containing_
> the _ungrouped_ _column_.

If you have all columns mentioned in a PK in the "GROUP BY" clause, it
is enough for the DB to understand there is no way to get more than
one row for such group of values, therefore if you have columns from
the PK in the "GROUP BY", it is the same as if you have all columns of
that table in the "GROUP BY".

> But I have no idea what is the data returned using this group by conditio=
n.

So the other columns of the row which can be found by the PK values:

postgres=3D# CREATE TABLE a(ai int, payload_a text, CONSTRAINT a_pk
PRIMARY KEY(ai));
CREATE TABLE
postgres=3D# CREATE TABLE b(bi int, payload_b text, CONSTRAINT b_pk
PRIMARY KEY(bi));
CREATE TABLE
postgres=3D# SELECT payload_a, payload_b, ai, bi, count(*) FROM a JOIN b
ON (ai=3Dbi) GROUP BY ai, bi;
 payload_a | payload_b | ai | bi | count
-----------+-----------+----+----+-------
(0 rows)

But if you forget PK of a table you get an error (at the column
payload_b, not payload_a):

postgres=3D# SELECT payload_a, payload_b, ai, bi, count(*) FROM a JOIN b
ON (ai=3Dbi) GROUP BY ai;
ERROR:  column "b.payload_b" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 1: SELECT payload_a, payload_b, ai, bi, count(*) FROM a JOIN b ...
                          ^

[1] http://www.postgresql.org/message-id/503.1455219352@sss.pgh.pa.us
[2] http://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-GROUPBY

> Yours faithfully,
> Andrew Zakharov

Oops... It seems I hasn't put a mailing list address in the CC field.
Andrew, I apologize for a duplicate in your mailbox.

> -----Original Message-----
> From: Vitaly Burovoy [vitaly.burovoy@gmail.com]
> Received: =D1=87=D0=B5=D1=82=D0=B2=D0=B5=D1=80=D0=B3, 11 =D1=84=D0=B5=D0=
=B2 2016, 22:38
> To: Zakharov, Andrey [AZakharov@luxoft.com]
> CC: pgsql-bugs@postgresql.org [pgsql-bugs@postgresql.org]
> Subject: Re: [BUGS] SQL parser ubnormal behaviour
>
> On 2/11/16, Zakharov, Andrey <AZakharov@luxoft.com> wrote:
>> Dear colleagues -
>>
>> There are the serious problems found in SQL parser for versions 9.3.1 th=
ru
>> newest 9.5. Perhaps they all depends on data distribution but I cannot b=
e
>> sure. Such behavior constantly presents and reproducible. FAQs and Tips
>> have
>> been looked thru carefully but such stuff is not there.
>>
>> The problem is: the following types of SQL statements are OK for the SQL
>> parser and DB engine returns the resultset.
>>
>> 1)
>>
>> SELECT COUNT(app.paapl_application_id) OVER() AS totalCount,
>> app.edcit_citizenship_id, app.paapl_application_id,
>> app.paapl_blocker_resolution_check,
>> app.paapl_dob, app.paapl_gender, app.paapl_inn,
>> app.paapl_is_fprint_pkg_completed,
>> app.paapl_is_migreg_pkg_completed, app.paapl_last_name,
>> app.paapl_last_name_latin,
>> app.paapl_name, app.paapl_name_latin, app.paapl_note,
>> app.paapl_patent_decision_comment,
>> app.paapl_phase_start_ts, app.paapl_processing_start,
>> app.paapl_second_name,
>> app.paapl_second_name_latin,
>> app.paapl_sf_is_ext_wait_complete, app.paapl_status_ts, app.paapl_type,
>> app.pablk_application_blocker_id,
>> doc.padoc_number, doc.padoc_series, app.paopr_last_name_locked,
>> app.paopr_login_locked, app.paopr_name_locked,
>> app.paopr_second_name_locked, passport.pascn_document_scan_id_trans,
>> app.pasts_status_id
>> FROM pa_application AS app
>> LEFT JOIN pa_document AS doc
>>             ON app.paapl_application_id =3D doc.paapl_application_id
>>             AND (doc.padtp_document_type_id =3D 5)
>> LEFT JOIN pa_passport AS passport
>>             ON doc.padoc_document_id =3D passport.padoc_document_id
>> Inner join pa_application_indicator as ind
>> on ind.paapl_application_id=3Dapp.paapl_application_id
>> WHERE (app.pasts_status_id =3D 3) and ind.paidc_ppot_sent=3D'Y' and
>> ind.paidc_ppot_decision_made=3D'N'
>> GROUP BY app.paapl_application_id, doc.padoc_number, doc.padoc_series,
>> passport.pascn_document_scan_id_trans
>> ORDER BY paapl_application_id LIMIT 10
>> ;
>
> I guess you have a typo: you have app.paapl_application_id in an
> aggregate function (count) _and_ in the "GROUP BY" clause, but the
> other column (app.edcit_citizenship_id) is not in the "GROUP BY"
> clause but mentioned "as is", i.e. not in any aggregate function.
>
> Please, read error message in your screenshot. It gives enough informatio=
n.

--=20
Best regards,
Vitaly Burovoy