Re: SQL parser ubnormal behaviour

On 2/11/16, Zakharov, Andrey <AZakharov(at)luxoft(dot)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 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] 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(dot)burovoy(at)gmail(dot)com]
> Received: четверг, 11 фев 2016, 22:38
> To: Zakharov, Andrey [AZakharov(at)luxoft(dot)com]
> CC: pgsql-bugs(at)postgresql(dot)org [pgsql-bugs(at)postgresql(dot)org]
> Subject: Re: [BUGS] SQL parser ubnormal behaviour
>
> On 2/11/16, Zakharov, Andrey <AZakharov(at)luxoft(dot)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