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 "group by" 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 is equal 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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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]/messages/by-id/503.1455219352@sss.pgh.pa.us. But I can give you a link to the
documentation[2]http://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-GROUPBY (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 columns
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 condition.

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

postgres=# CREATE TABLE a(ai int, payload_a text, CONSTRAINT a_pk
PRIMARY KEY(ai));
CREATE TABLE
postgres=# CREATE TABLE b(bi int, payload_b text, CONSTRAINT b_pk
PRIMARY KEY(bi));
CREATE TABLE
postgres=# SELECT payload_a, payload_b, ai, bi, count(*) FROM a JOIN b
ON (ai=bi) 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=# SELECT payload_a, payload_b, ai, bi, count(*) FROM a JOIN b
ON (ai=bi) 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]: /messages/by-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: четверг, 11 фев 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 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.

--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs