[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

Lionel Elie Mamane <lionel at mamane.lu> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |NOTABUG
             Status|NEW                         |RESOLVED

--- Comment #16 from Lionel Elie Mamane <lionel at mamane.lu> ---
For me, when changing the setting in the file, the error in the PostgreSQL log
changes from:

ERROR:  syntax error at or near ":" at character 82
STATEMENT:  SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( (
"public"."tab2"."ref_id" = :link_from_id ) )

to

ERROR:  syntax error at or near ")" at character 84
STATEMENT:  SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( (
"public"."tab2"."ref_id" = ? ) )

which kinda shows that the setting is taking effect. However, I realised that
this setting has no importance for Postgresql (native/SDBC driver), since the
PostgreSQL-SDBC driver will postprocess any SQL to be executed and will replace
both unnamed and named parameters by their value... when the DB-agnostic part
of LibreOffice sets the parameters to some value, obviously. Which explains why
everything was working even in the face of an incorrect setting. Also,
according to my notes the PostgreSQL parameter syntax is yet another one,
namely $1, $2, etc.



As to this bug. I traced this back to file
dbaccess/source/core/api/SingleSelectQueryComposer.cxx function
OSingleSelectQueryComposer::getColumns() around line 750 to 815, where the
basic algorithm is:

1) Add to the SQL's WHERE clause "0=1" (to get 0 results, but get column
information)
2) Try to execute the SQL as a _prepared_ statement (with normal LibreOffice
processing)
3) If that fails, try to execute the SQL _unchanged_ as a statement (without
normal LibreOffice processing)
4) If that fails, try to execute the SQL as a prepared statement setting all
parameters to NULL

what you see is the result of step 3, so "it works as designed". Frankly, if I
had to write this code from scratch, I would scrap steps 2 and 3, and do only
step 4, which makes the most sense to me. However, given the age of this code
and the amount of work-arounds for exotic "misbehaviours" of obscure (or not so
obscure) database engines it probably contains, I'd rather not touch it and
reintroduce the problems it probably works around. Unless we get better
visibility on the issues, or the current behaviour leads to an actual problem.

My conclusion is: the behaviour is not a problem. LibreOffice tries stuff, some
of which is anticipated that it could fail, and the failure is treated by
LibreOffice, with a sensible fallback. We could rework that part of LibreOffice
so that it does not, in this particular case, try this broken SQL, but who
knows what other problems it will introduce with other DB engines. So I'm going
to close this bug report and not do anything about it, because it is not a
problem. (Unless, Albrecht, you explain to me why it is a problem?)

This being said, if some brave soul wants to change the algorithm to be more
clean (as outlined above) and then watch for regressions, I'll gladly accept
the patch.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20180914/6279ad57/attachment-0001.html>