SQL parser ubnormal behaviour

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
;

totalcount

edcit_citizenship_id

paapl_application_id

paapl_blocker_resolution_check

paapl_dob

paapl_gender

paapl_inn

paapl_is_fprint_pkg_completed

paapl_is_migreg_pkg_completed

paapl_last_name

paapl_last_name_latin

paapl_name

paapl_name_latin

paapl_note

paapl_patent_decision_comment

paapl_phase_start_ts

paapl_processing_start

paapl_second_name

paapl_second_name_latin

paapl_sf_is_ext_wait_complete

paapl_status_ts

paapl_type

pablk_application_blocker_id

padoc_number

padoc_series

paopr_last_name_locked

paopr_login_locked

paopr_name_locked

paopr_second_name_locked

pascn_document_scan_id_trans

pasts_status_id

26644

102195

270

[NULL]

22.09.1971

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

21.01.2015 13:01

21.01.2015 0:00

[NULL]

[NULL]

Y

21.01.2015 13:01

R

[NULL]

386690

B

[NULL]

2632

3

26644

102316

286

[NULL]

21.10.1971

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

21.01.2015 14:55

21.01.2015 0:00

[NULL]

[NULL]

Y

21.01.2015 14:55

R

[NULL]

7796893

AA

[NULL]

3755

3

26644

102223

290

[NULL]

05.03.1982

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

21.01.2015 15:34

21.01.2015 0:00

[NULL]

[NULL]

Y

21.01.2015 15:34

R

[NULL]

208668

CB

[NULL]

2827

3

26644

102239

328

[NULL]

18.04.1957

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

21.01.2015 17:37

21.01.2015 0:00

[NULL]

[NULL]

Y

21.01.2015 17:37

R

[NULL]

400404577

[NULL]

[NULL]

3654

3

26644

102239

394

[NULL]

10.07.1976

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

22.01.2015 13:48

22.01.2015 0:00

[NULL]

[NULL]

Y

18.03.2015 13:05

R

[NULL]

400427855

[NULL]

[NULL]

4868

3

26644

102239

469

[NULL]

15.05.1970

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

22.01.2015 19:27

22.01.2015 0:00

[NULL]

[NULL]

Y

22.01.2015 19:27

R

[NULL]

573595

M

[NULL]

4887

3

26644

102195

651

[NULL]

01.08.1965

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

23.01.2015 15:25

23.01.2015 0:00

[NULL]

[NULL]

Y

23.01.2015 15:51

R

[NULL]

3137163

A

[NULL]

6212

3

26644

102316

736

[NULL]

02.06.1991

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

23.01.2015 18:18

23.01.2015 0:00

[NULL]

[NULL]

Y

23.01.2015 21:58

R

[NULL]

1848209

CT

[NULL]

7452

3

26644

102223

790

[NULL]

09.09.1988

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

23.01.2015 20:09

23.01.2015 0:00

[NULL]

[NULL]

Y

18.03.2015 13:05

R

[NULL]

325117

СЮ

[NULL]

7480

3

26644

102195

1473

[NULL]

09.08.1980

m

[NULL]

Y

Y

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

[NULL]

26.01.2015 10:24

26.01.2015 0:00

[NULL]

[NULL]

Y

26.01.2015 10:24

R

[NULL]

503537

BO

[NULL]

18261

3

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.

Physical model:

[cid:image015(dot)png(at)01D164BE(dot)92B319F0]

[cid:image016(dot)png(at)01D164BE(dot)92B319F0]

[cid:image017(dot)png(at)01D164BE(dot)92B319F0]
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%'
)

paevt_application_event_id

paeve_event_id

paapl_application_id

paevt_event_time

paevt_parameters

ntcam_campaign_id

52493836

23

1003554

10.02.2016 8:23

operatorLogin=murzinaaa;operatorFIO=Murzina

[NULL]

52496578

23

1004773

10.02.2016 9:32

operatorLogin=murzinaaa;operatorFIO=Murzina

[NULL]

52497272

23

1000756

10.02.2016 9:43

operatorLogin=murzinaaa;operatorFIO=Murzina

[NULL]

52540993

23

478371

10.02.2016 15:17

operatorLogin=murzinaaa;operatorFIO=Murzina

[NULL]

52544267

23

1003786

10.02.2016 15:36

operatorLogin=murzinaaa;operatorFIO=Murzina

[NULL]

52544408

23

1006694

10.02.2016 15:37

operatorLogin=murzinaaa;operatorFIO=Murzina

[NULL]

52544563

23

1000325

10.02.2016 15:38

operatorLogin=murzinaaa;operatorFIO=Murzina

[NULL]

52544731

23

478358

10.02.2016 15:38

operatorLogin=murzinaaa;operatorFIO=Murzina

[NULL]

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.

Physical model:

[cid:image019(dot)png(at)01D164BE(dot)92B319F0]

Some observations though. I caught the syntax error during uploading the export file into schema and executing the SQL in the same time. The syntax error has disappeared after the export finished.

[Description: cid:image001(dot)png(at)01D1644F(dot)B3CC26F0]

Technical summary:

1. Found in 9.3.1 and latest 9.5. Other versions are not tested
2. All tested DB versions are installed on Windows 7 or CentOS release 6.7 (Final)
3. dBeaver is used for examples preparation.
4. Application developed using Java 1.8 + jetty-9.2.4.v20141103

The obfuscated data can be provided.

Thanks in advance for any ideas.
Andrew Zakharov
Leading Database Architect
Luxoft

Tel: +7 495 967 8030

[cid:image001(dot)gif(at)01D164B7(dot)F156DED0]
Luxoft Holding (NYSE:LXFT) is a leading provider of software development services and innovative IT solutions to a global client base consisting primarily of large multinational corporations. Headquartered in Zug, Switzerland Luxoft is listed on the New York Stock Exchange. For more information, please visit www.luxoft.com<http://www.luxoft.com>

LinkedIn: AZakharov<http://ru.linkedin.com/in/andrewzakharov/>

Follow us on: [cid:image002(dot)gif(at)01D164B7(dot)F156DED0] <http://www.linkedin.com/company/luxoft> [cid:image003(dot)gif(at)01D164B7(dot)F156DED0] <http://twitter.com/Luxoft> [cid:image004(dot)gif(at)01D164B7(dot)F156DED0] <http://www.youtube.com/channel/UCDtOIqWxKHTdtmVi8yr_D7Q> [cid:image005(dot)gif(at)01D164B7(dot)F156DED0] <https://www.facebook.com/Luxoft> [cid:image006(dot)gif(at)01D164B7(dot)F156DED0] <https://plus.google.com/109881160058685562700>

This e-mail and any attachment(s) are intended only for the recipient(s) named above and others who have been specifically authorized to receive them. They may contain confidential information. If you are not the intended recipient, please do not read this email or its attachment(s). Furthermore, you are hereby notified that any dissemination, distribution or copying of this e-mail and any attachment(s) is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender by replying to this e-mail and then delete this e-mail and any attachment(s) or copies thereof from your system. Thank you.

________________________________

This e-mail and any attachment(s) are intended only for the recipient(s) named above and others who have been specifically authorized to receive them. They may contain confidential information. If you are not the intended recipient, please do not read this email or its attachment(s). Furthermore, you are hereby notified that any dissemination, distribution or copying of this e-mail and any attachment(s) is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender by replying to this e-mail and then delete this e-mail and any attachment(s) or copies thereof from your system. Thank you.