Created attachment 144519 [details]
updated odb file

Believe this is a problem with the db:parameter-name-substitution property having been erroneously set to 'false' with this version of the application; 

Went ahead and hand change that in the content.xml and attached the updated file here. Give it a try and see if that clears your problem and let us know. 

Thanks

Thanks a lot for your fast reply!

(In reply to Drew Jensen from comment #1)
> Went ahead and hand change that in the content.xml and attached the updated
> file here. Give it a try and see if that clears your problem and let us
> know. 

Unfortunately, this doesn't help.  I can confirm content.xml now contains “<db:driver-settings […] db:parameter-name-substitution="true"/>”, but opening the form still throws the same PostgreSQL error as above.

(In reply to Albrecht Dreß from comment #2)
> Thanks a lot for your fast reply!
> 
> (In reply to Drew Jensen from comment #1)
> > Went ahead and hand change that in the content.xml and attached the updated
> > file here. Give it a try and see if that clears your problem and let us
> > know. 
> 
PostgreSQL: the connections could be set in
Edit > Databases > Advanced Settings
You have to switch named Parameter to '?'.

(In reply to robert from comment #3)
> PostgreSQL: the connections could be set in
> Edit > Databases > Advanced Settings
> You have to switch named Parameter to '?'.

As I mentioned in my original post, this option is inactive (greyed out) for the PostgreSQL driver.  Or did I miss your point?

(In reply to Albrecht Dreß from comment #4)
> (In reply to robert from comment #3)
> > PostgreSQL: the connections could be set in
> > Edit > Databases > Advanced Settings
> > You have to switch named Parameter to '?'.
> 
> As I mentioned in my original post, this option is inactive (greyed out) for
> the PostgreSQL driver.  Or did I miss your point?

You are right. Have set up PostgreSQL here with direct connection. The options aren't available there. But: I can't confirm the buggy behaviour. 
Have created two tables, connected with foreignkey. 
Created a form with subform.
Could input data in form and also in subform without any problems.
Data were saved.

All tests with OpenSUSE 15, 64bit rpm Linux, LO Version: 6.1.0.3, Build ID: efb621ed25068d70781dc026f7e9c5187a4decd1

Could be you are working with LO directly distributed by Ubuntu or Debian? What's the Build ID from your versions?

... and also tested with LO 6.0.5.2 and LO 6.0.0.1 on OpenSUSE 15, 64bit rpm Linux. Form with subform works.

When I see the error of the description and the statement here one question:
Do you know where this comes from - "WHERE ( 0 = 1 )"?

(In reply to robert from comment #5)
> Could input data in form and also in subform without any problems.
> Data were saved.

Yes.  The form itself works for me, too.  It's just the initial query when opening the form which is broken, triggering the error message in the Postgresql server log.  The latter is monitored by Nagios, which is somewhat annoying…

> All tests with OpenSUSE 15, 64bit rpm Linux, LO Version: 6.1.0.3, Build ID:
> efb621ed25068d70781dc026f7e9c5187a4decd1
> 
> Could be you are working with LO directly distributed by Ubuntu or Debian?
> What's the Build ID from your versions?

I installed a fresh VM with Debian Stretch/64 plus all updates, and made a full install of the package from <https://de.libreoffice.org/download/download/> which is actually “Version: 6.1.0.3 Build ID: efb621ed25068d70781dc026f7e9c5187a4decd1” (i.e. the same as yours).

I /still/ get the server error message when I open the form, even when I created a completely new LibreOffice database document (German locale of PGSql; log file /var/log/postgresql/postgresql-9.6-main.log):

FEHLER:  Syntaxfehler bei »:« bei Zeichen 82
ANWEISUNG:  SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( ( "public"."tab2"."ref_id" = :link_from_id ) )

I have no idea why this statement is submitted at all, as it will never (due to “0=1”) return any data.

In addition to Libreoffice, I installed openjdk-9-jre-headless:amd64 ver. 9~b181-4~bpo9+1 and postgresql ver. 9.6+181+deb9u2, if that is of any importance.  The version on Ubuntu is “Version: 6.0.4.2 Build-ID: 1:6.0.4~rc2-0ubuntu0.16.04.1”, btw.

I changed the (In reply to robert from comment #6)
> ... and also tested with LO 6.0.5.2 and LO 6.0.0.1 on OpenSUSE 15, 64bit rpm
> Linux. Form with subform works.
> 
> When I see the error of the description and the statement here one question:
> Do you know where this comes from - "WHERE ( 0 = 1 )"?

Good question…  I changed the PGSql server setting to “log_statement = 'all'”.  Opening the form (using my db example) triggers the following statements (only those to my db; in addition, there is a bunch of queries to the PGSql control tables):

LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1"
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
FEHLER:  Syntaxfehler bei »:« bei Zeichen 82
ANWEISUNG:  SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( ( "public"."tab2"."ref_id" = :link_from_id ) )
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( ( "public"."tab2"."ref_id" = NULL ) )
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( "public"."tab2"."ref_id" = '2' )

The last statement actually loads the data from tab2 referenced by the 1st row in tab1.

Lionel:
do you think it would be ok to add 
        <node oor:name="ParameterNameSubstitution" oor:op="replace">
          <prop oor:name="Value" oor:type="xs:boolean">
            <value>true</value>
          </prop>
        </node>
in registry/postgresql/org/openoffice/Office/DataAccess/Drivers.xcu or is this following comment "        <!-- These entries enable the "Advanced Settings" UI to change the settings
             whose default is set in node "Properties" above;
             as this is guaranteed not to work with PostgreSQL,
             we don't let people shoot themselves in the foot and don't enable the UI. -->
" from 2011 you had put is still relevant?
Unless it applied only to "EscapeDateTime" and "UseBracketedOuterJoinSyntax" therefore other settings may be added?

Could confirm this buggy behaviour. Have found the logfiles for PostgreSQL where alls this syntax-errors were logged. Every time I open a form with subform one error appears. When I add a new row in the mainform there appear two syntax-errors.

The forms work as expected. Seems the queries will be repeated in another (right) way so you couldn't recognize this error in Base.

Error appears also in earlier versions. Haven't installed much of the but could reproduce with
Version: 5.4.6.2
Build-ID: 4014ce260a04f1026ba855d3b8d91541c224eab8
on OpenSUSE 15, 64bit rpm Linux.

I am getting much the same error on Windows version 6.1.0.3 and 

ubuntu Version: 6.1.1.2
Build ID: 1:6.1.1~rc2-0ubuntu0.18.04.1~lo3
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-GB (en_GB.UTF-8); Calc: group threaded

ubuntu version 6.0.5.2 is OK on my system.

I'm using base with mysql/mariadb sql.  When I open a form with a subform I get (at least) one of these for each subform:

"The data content could not be loaded.

Parameter index out of range (1 > number of parameters, which is 0)."

And the detail: "SQL Status: S1009

Parameter index out of range (1 > number of parameters, which is 0)."

I have checked that it is the queries used by the subforms that are giving the errors (not the subform code itself), but opening the query and supplying parameters.  On 6.0.5.2 it works.


I have not yet tried to produce a demo form but will try to do so if asked.

This is critical for me.  I use Base a lot, and all my applications makes extensive use of subforms.  I have had to revert back to 6.0.5.2 on my main system.

(In reply to tim from comment #12)
> I am getting much the same error on Windows version 6.1.0.3 and 

> I'm using base with mysql/mariadb sql.  When I open a form with a subform I
> get (at least) one of these for each subform:
> 
> "The data content could not be loaded.
> 
> Parameter index out of range (1 > number of parameters, which is 0)."
> 
> And the detail: "SQL Status: S1009
> 
> Parameter index out of range (1 > number of parameters, which is 0)."

Tim, this bug is about the error showing in the PostgreSQL log, but otherwise LibreOffice Base works fine. LibreOffice Base does not show the error. It seems you get the error in LibreOffice Base, and it breaks usage of your forms. Please fork this in a separate bug report. Please also specify which MySQL driver you are using (ODBC? JDBC? "native"?).

(In reply to Albrecht Dreß from comment #0)
> Side note: there are some similar but closed issues regarding the JDBC
> driver where setting an advanced connection option should solve the bug. The
> PostgreSQL driver apparently doesn't support these options, though (the
> “Advanced Settings…” menu entry is inactive).

For clarity, that setting is supposed to be _hardcoded_ to the correct value for PostgreSQL. That is why it is not available to the user. It should be the correct one, automatically, punto basta.

Sorry Lionel, having done more searching I found #118112 which is the same issue as mine, and has a form of resolution for existing databases.

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.

(In reply to Lionel Elie Mamane from comment #16)
Thanks a lot for the detailed description of the bug!

> 3) If that fails, try to execute the SQL _unchanged_ as a statement (without
> normal LibreOffice processing)
[…]
> what you see is the result of step 3, so "it works as designed".

To be honest, I doubt that /any/ halfway SQL-compliant server will ever accept the broken statement.  Would be interesting what Oracle/Mysql/… report in this case.

> Unless, Albrecht, you explain to me why it is a problem?

I fully agree with you that from the users pov Libreoffice behaves as expected, and that PostgreSQL can deal with broken input properly.  In a single-user environment, I guess ≥99% of the users will even never notice the PostgreSQL error messages.

However, from the IT operations pov, it *is* a problem.

In our use case, the database is running on a central server with high security requirements.  We run several monitoring components, inter alia (using CheckMK) monitoring the PostgreSQL log file for issues.  Every “ERROR” there is of course brought to the admin's attention – remember that it may be a serious server issue, or an indicator for attack or compromise.

With several users using the Libreoffice frontend, the admin gets bombarded with issues, of which /almost/ all are caused by the Libreoffice bug.  Needles to say that there is a considerable risk that real issues just slip through.

So, apart from a fixed Libreoffice version, my options are:
(1) Disable monitoring for PostgreSQL (I actually did this temporarily) or error reporting from PostgreSQL.  No long-term solution as security auditing is mandatory in our use case, and a /very/ bad idea anyway.
(2) Replace Libreoffice by an other solution.  Drawback: loose all amenities of Libreoffice, time/money for implementation.
(3) Create a new monitoring component which ignores *only* the broken queries, iff coming from Libreoffice (but not from other sources).  Probably less effort than (2), but adds complexity to the setup (maintenance, security auditing of the new component, etc.)

In short, it's neither “RESOLVED” nor “NOTABUG” for me…

Apart from that, IMHO it's not good engineering practice to issue “something” which is broken by design and hope that the recipient will deal with it properly, but this may be philosophical question.

(In reply to Albrecht Dreß from comment #17)
> (In reply to Lionel Elie Mamane from comment #16)

>> 3) If that fails, try to execute the SQL _unchanged_ as a statement (without
>> normal LibreOffice processing)
> […]
>> what you see is the result of step 3, so "it works as designed".

> To be honest, I doubt that /any/ halfway SQL-compliant server will ever
> accept the broken statement.

That algorithm is used on _all_ queries / SQL commands executed by LibreOffice (when column information is needed). So it is "broken" when there are parameters, but it is 100% legit when there are no parameters, and actually corresponds to trying to send the SQL command/query as unchanged as possible to the DB engine. Subforms just happen to use a parametrised query, which is why you see it with subforms.

>> Unless, Albrecht, you explain to me why it is a problem?

> I fully agree with you that from the users pov Libreoffice behaves as
> expected, and that PostgreSQL can deal with broken input properly.
 
> However, from the IT operations pov, it *is* a problem.

> In our use case, the database is running on a central server with high
> security requirements.  We run several monitoring components, inter alia
> (using CheckMK) monitoring the PostgreSQL log file for issues.  Every
> “ERROR” there is of course brought to the admin's attention – remember that
> it may be a serious server issue, or an indicator for attack or compromise.

I see.

Well, let's give it a try. I'll change the algorithm to do only step 4, or maybe turn the algorithm around: first step 4, if that fails step 2 or 3, then step 3 or 2. I'll sleep on it to take a decision on the alternatives. Then in the next months let's see what regressions this brings, or not. Maybe I'll do:

step 4
if that fails, step2&3 or 3&2 with, if those succeed, a debug message "if you see this, please file a bug and inform Lionel, should not happen"

(In reply to Lionel Elie Mamane from comment #18)
> That algorithm is used on _all_ queries / SQL commands executed by
> LibreOffice (when column information is needed).
Ooops, didn't understand that correctly from your previous comment, sorry!  Yes, I agree that a modification may be risky.

> Well, let's give it a try. I'll change the algorithm to do only step 4, or
> maybe turn the algorithm around: first step 4, if that fails step 2 or 3,
> then step 3 or 2. I'll sleep on it to take a decision on the alternatives.
> Then in the next months let's see what regressions this brings, or not.
Thanks a lot!  I look forward to testing it…

Sorry, the LibreOffice git repo was down last week-end, so I could not commit the change I made, and I had a super-busy week, so couldn't do it until now.

The submitted patches are at https://gerrit.libreoffice.org/60885 and https://gerrit.libreoffice.org/60884

Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6bebb05bd4b338e1ed3dac2c02b8b4186dc79675

tdf#119569 try most likely to succeed method to get column metadata _before_

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

Backport to 6.1 series (for 6.1.3) waiting for 2nd opinion.

I tested
- Version: 6.2.0.0.alpha0+
- Build ID: 9927a3ab9edec7ae9f41ecaa8025a2a920321997
on Debian Stretch/64 and can confirm the PostgreSQL errors are now gone.  Compared to the PostgreSQL log in https://bugs.documentfoundation.org/show_bug.cgi?id=119569#c8, opening the form the messages now read

LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1"
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( ( "public"."tab2"."ref_id" = NULL ) )
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( "public"."tab2"."ref_id" = '2' )

Thanks a lot for your help!

Best, Albrecht.

Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f19cba204552ecb11b97c8047320733c498cf518&h=libreoffice-6-1

tdf#119569 try most likely to succeed method to get column metadata _before_

It will be available in 6.1.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

(In reply to Commit Notification from comment #24)
> tdf#119569 try most likely to succeed method to get column metadata _before_
> 
> It will be available in 6.1.3.
> 
> The patch should be included in the daily builds available at
> http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours.

I can confirm that the issue has been fixed in
* Version: 6.1.3.0.0+
* Build ID: 8ef25505303dcd744d20abf7e328ce1f0eda4dbf
running on Debian Stretch/64 Bit.

Again, thanks a lot for your help!

Forgive for asking this. But Please build a standalone sdbc-postgresql driver with this patch. 

I am stuck with OO 3.1.1.Application in production with many standalone forms with 5 levels of sub-forms filtering. We use over 10 computers daily for work. And postgresql log become pretty un-manageable within a short period of time, as it fills with this syntax error on each form load/reload.

We cannot change to LO as its file filecker with preview is modified in such a way that the file-preview pane is not enlargeable any more. We use the OO file picker with preview image function to look through the report/images scanned in as .jpg, and is a much needed function for us.

Thank you
Ansari

(In reply to ansari.afcs from comment #26)
> Forgive for asking this. But Please build a standalone sdbc-postgresql
> driver with this patch. 
> ...

Rather try the filepicker on recent LO version with different rendering if necessary. If there's a problem, feel free to submit a bugtracker if there's none corresponding to your problem.
Also, I don't know if it's possible to build a standalone sdbc-postgresql but you may also retrieve the code and give it a try.

(In reply to Julien Nabet from comment #27)
> (In reply to ansari.afcs from comment #26)
> > Forgive for asking this. But Please build a standalone sdbc-postgresql
> > driver with this patch. 
> > ...
> 
> Rather try the filepicker on recent LO version with different rendering if
> necessary. If there's a problem, feel free to submit a bugtracker if there's
> none corresponding to your problem.
> Also, I don't know if it's possible to build a standalone sdbc-postgresql
> but you may also retrieve the code and give it a try.

Adding ansari to CC. The filepicker is dependent on the operating system. Indeed, try with 6.2 and create a report, if necessary. Include all the relevant information.

On Windows 10, the preview ("Show the preview pane") in filepicker is certainly resizeable.

(In reply to Buovjaga from comment #28)
> (In reply to Julien Nabet from comment #27)
> > (In reply to ansari.afcs from comment #26)
> > > Forgive for asking this. But Please build a standalone sdbc-postgresql
> > > driver with this patch. 
> > > ...
> > 
> > Rather try the filepicker on recent LO version with different rendering if
> > necessary. If there's a problem, feel free to submit a bugtracker if there's
> > none corresponding to your problem.
> > Also, I don't know if it's possible to build a standalone sdbc-postgresql
> > but you may also retrieve the code and give it a try.

 I wish I could but am not a programmer. My journey with openoffice, basic, macro programming started many years back with free CD of StarOffice 5.2 and the need to change the dbase4 management system  we were using then. I managed to cook up something which works for us. I lurked and read through the forum pages and learned from many people. Many many people contrirbuted in my learning. And wait many years for this PG-log syntax error to be fixed. Since a solution is working, I asked for the possibility of a new  extension, the last sdbc-extension available in sdbc-openoffice.org is many years old. Well forgive me, I know this is not OpenOffice bugzilla. Should I make a request directed to 'Lionel'
> 
> Adding ansari to CC. The filepicker is dependent on the operating system.
> Indeed, try with 6.2 and create a report, if necessary. Include all the
> relevant information.
> 
> On Windows 10, the preview ("Show the preview pane") in filepicker is
> certainly resizeable.

I was not referring to windows 10 file preview as I need to start the file preview with specific path selected from the basic macro. Application users are not expected to browse. The 'SetDefaultDirectory' method does not work with "com.sun.star.ui.dialogs.SystemFilePicker" or  "com.sun.star.ui.dialogs.FilePicker".

I did test with LO 6.2.0.2, same behaviour as 6.1.4. Filepicker started with basic macro using "com.sun.star.ui.dialogs.OfficeFilePicker", shows 3 panes and is re-sizeable. Can change sizes for 'Myplaces and or file list panes but the preview area remain the same.

This behaviour is different in OO/AOO which shows only 2 panes, file list and preview and can be enlarged together making it functional for me.

Thank you
Best Regards
Ansari

(In reply to ansari.afcs from comment #29)
>...
> I was not referring to windows 10 file preview as I need to start the file
> preview with specific path selected from the basic macro. Application users
> are not expected to browse. The 'SetDefaultDirectory' method does not work
> with "com.sun.star.ui.dialogs.SystemFilePicker" or 
> "com.sun.star.ui.dialogs.FilePicker".
I noticed setDisplayDirectory in https://opengrok.libreoffice.org/xref/core/offapi/com/sun/star/ui/dialogs/XFilePicker.idl?r=c657740b
Perhaps it may help.

About resizeable panes, I don't know.

Anyway, feel free so submit a bug or an enhancement.
You may also require some help on IRC or dev mailing list, see https://wiki.documentfoundation.org/Development

About building LO, nothing complicated (at least on Linux) so you can let you guided by this same page.

(In reply to ansari.afcs from comment #29)
> I was not referring to windows 10 file preview as I need to start the file
> preview with specific path selected from the basic macro. Application users
> are not expected to browse. The 'SetDefaultDirectory' method does not work
> with "com.sun.star.ui.dialogs.SystemFilePicker" or 
> "com.sun.star.ui.dialogs.FilePicker".
> 
> I did test with LO 6.2.0.2, same behaviour as 6.1.4. Filepicker started with
> basic macro using "com.sun.star.ui.dialogs.OfficeFilePicker", shows 3 panes
> and is re-sizeable. Can change sizes for 'Myplaces and or file list panes
> but the preview area remain the same.

Maybe open a report about the problem with SetDefaultDirectory.

The internal file picker is going away, so you should not rely on it: bug 114484