Re: SQL parser ubnormal behaviour - Mailing list pgsql-bugs

"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