Re: 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.

> ________________________________
> 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