Bug 118112 - Parameter index out of range (1 > number of parameters, which is 0).
Summary: Parameter index out of range (1 > number of parameters, which is 0).
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.beta1+
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.2.0 target:6.1.0.1 target:6.0.6
Keywords:
Depends on:
Blocks:
 
Reported: 2018-06-11 14:10 UTC by Alex Kempshall
Modified: 2018-07-30 14:10 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
git bisect log (2.56 KB, text/x-log)
2018-06-11 14:16 UTC, Alex Kempshall
Details
Base application and mysql data (23.78 KB, application/gzip)
2018-06-11 14:52 UTC, Alex Kempshall
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Kempshall 2018-06-11 14:10:12 UTC
When opening a form, in LibreOffice Base, I get the following error box displayed

The data content could not be loaded.
SQL Status: S1009

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


The form has worked on all versions of LibreOffice up to and including 6.0.4

I'm using

Slackware 14.2
Mariadb 10.0.35
mysql-connector-java-5.1.39
Comment 1 Alex Kempshall 2018-06-11 14:16:00 UTC
Created attachment 142656 [details]
git bisect log

Attached is a git bisect log. The first bad commit is described as -

a29d97e6ddab8ec002ba9827bd5fc874117712e0 is the first bad commit
commit a29d97e6ddab8ec002ba9827bd5fc874117712e0
Author: Lionel Elie Mamane <lionel@mamane.lu>
Date:   Tue Jan 2 22:49:31 2018 +0100

    tdf#104986 move named parameters substitution into generic layer
    
    Previously, drivers were responsible for making the substitution themselves.
    In practice they all (Firebird, ODBC and JDBC) used the LibreOffice SQL
    parser to parse the SQL statement and do the substitution.
    
    This had a few negative consequences:
     * The substitition was applied to _all_ SQL commands, including
       queries having the "execute SQL directly" bit set. Which means
       that the SQL was _not_ sent to the DBMS exactly as typed by
       the user. Even if there was no substitution to be made, since
       the SQL command was always round-tripped through the parser,
       thus "normalising" it (which is what led to tdf#104986).
     * "execute SQL directly" queries "magically" behaved slightly
       differently depending on whether the LibreOffice SQL parser
       succeeded in parsing them or not.
    
    Change-Id: Ieedc643f33467435a139e2565a01e6f398af041f
    Reviewed-on: https://gerrit.libreoffice.org/47283
    Tested-by: Jenkins <ci@libreoffice.org>
    Reviewed-by: Lionel Elie Mamane <lionel@mamane.lu>

:040000 040000 21498e671d4de80e991c7b51dec9934941e385a2 9ad610a6a942238862876f30a23e287fdbbda9b4 M      connectivity
:040000 040000 e9abefeaa3966bf120917e674b181db122593c34 7e986739f2e48cc68966a085f241f46fd2ec80ae M      include
Comment 2 Xisco Faulí 2018-06-11 14:20:03 UTC
Hello Alex,
Could you please attach the document you're using to reproduce this problem ?
Comment 3 Alex Kempshall 2018-06-11 14:52:12 UTC
Created attachment 142660 [details]
Base application and mysql data

tar gz file with database document and sql data for mariadb/mysql

Steps to recreate problem in LibreOffice 6.1

1.
untar the attached btuc.mysql.tar.gz

2.
Create the btuc database using the attached mysqldump file

3.
Open attached btuc_mysql.odb file with libreoffice

4.
Register btuc database in LibreOffice

5.
Open the form FMBTUC_DATA_ENTRY

6.
Error Box is displayed.
Comment 4 Alex Kempshall 2018-06-11 14:55:06 UTC
I've created an equivalent application using the embedded hsqldb database. The application behaves as expected - no error box.
Comment 5 Alex Kempshall 2018-06-13 15:46:40 UTC
The commit that caused this regression was to fix bug tdf#104986.

In that bug report there's a discussion

(In reply to Lionel Elie Mamane from comment #18)
> There is probably a much more robust way to do it... One only has to grossly
> lex the SQL, just keeping track of whether on is inside a '-delimited string
> or a "-delimited identifier. And outside of that, replace any word that
> starts with ":" with "?".


In GDB of tdf#118112 I see this 

Thread 7 "UNO AffineBridg" hit Breakpoint 1, connectivity::java_sql_Connection::prepareStatement (this=0x35f5350, sql="SELECT * FROM `btuc`.`tblBTUC_details` WHERE ( `btuc`.`tblBTUC_details`.`Contact_ID` = :link_from_Contact_ID )")
    at /home/alex/master/connectivity/source/drivers/jdbc/JConnection.cxx:474
474         java_sql_PreparedStatement* pStatement = new java_sql_PreparedStatement( t.pEnv, *this, sql );


Does this mean that  :link_from_Contact_ID  is being changed to ? and causing the bug that I'm seeing.
Comment 6 Lionel Elie Mamane 2018-06-13 16:16:05 UTC
Either:
1) The data source of the form is configured as "execute directly: yes" / "analyse SQL command: no", and then, well, it was kinda based on a bug that it worked before.
Or:
2) Looks like on the contrary :link_from_Contact_ID is _not_ being replaced by "?" anymore, and that's your problem. Assuming my guess is correct, then the reason is probably that ParameterNameSubstitution is not set to "true", and the solution is to set ParameterNameSubstitution to "true". It needs to be set in existing files manually / by macro (sorry....), and to set it in new files, connectivity/registry/mysql/org/openoffice/Office/DataAccess/Drivers.xcu needs the same change as

https://gerrit.libreoffice.org/#/c/47283/5/connectivity/registry/firebird/org/openoffice/Office/DataAccess/Drivers.xcu

Julien, you want to take this?
Comment 7 Julien Nabet 2018-06-13 20:20:48 UTC
I submitted this patch to gerrit: https://gerrit.libreoffice.org/#/c/55771/1
but it doesn't seem to work.
See comment in gerrit.

Anyway, I could reproduce this, let's put this to NEW.
Comment 8 Lionel Elie Mamane 2018-06-14 05:27:26 UTC
(In reply to Lionel Elie Mamane from comment #6)

> the reason is probably that ParameterNameSubstitution is not set to "true",
> and the solution is to set ParameterNameSubstitution to "true". It needs to
> be set in existing files manually / by macro (sorry....),

Refer to
https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c11
https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15
for how to do that by editing the files in the odb manually or by macro, respectively.
Comment 9 Commit Notification 2018-06-14 07:49:23 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

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

tdf#118112: Use ParameterNameSubstitution for Mysql

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.
Comment 10 Alex Kempshall 2018-06-14 09:49:51 UTC
Just tested this.

Can see the patch in my git log.


> commit c179e8a7ea273a48713192c3ba7dfea313149cd8
> Author: Julien Nabet <serval2412@yahoo.fr>
> Date:   Wed Jun 13 22:16:44 2018 +0200
> 
>     tdf#118112: Use ParameterNameSubstitution for Mysql
>     
>     Change-Id: Ic48aee378d0a89466c49de85baa96a3bea127d2f
>     Reviewed-on: https://gerrit.libreoffice.org/55771
>     Tested-by: Jenkins <ci@libreoffice.org>
>     Reviewed-by: Julien Nabet <serval2412@yahoo.fr>

Have recompiled.

Still have the same problem. Is there anything else I'm supposed to have done?
Comment 11 Lionel Elie Mamane 2018-06-14 09:51:50 UTC
(In reply to Alex Kempshall from comment #10)
> Just tested this.
>
> Have recompiled.
> 
> Still have the same problem. Is there anything else I'm supposed to have
> done?

See comment 8
Comment 12 Alex Kempshall 2018-06-14 10:18:54 UTC
Thanks for pointing me in the right direction.

Now works as expected.
Comment 13 Commit Notification 2018-06-14 16:26:12 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

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

tdf#118112: Use ParameterNameSubstitution for Mysql

It will be available in 6.1.0.1.

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.
Comment 14 Julien Nabet 2018-06-14 18:21:48 UTC
(In reply to Lionel Elie Mamane from comment #8)
> ...
> Refer to
> https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c11
> https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15
> for how to do that by editing the files in the odb manually or by macro,
> respectively.
Just for information, I tested both methods (even if first one has already been confirmed), I confirm it works.
Comment 15 Julien Nabet 2018-06-15 08:20:44 UTC
Lionel: about your comment https://gerrit.libreoffice.org/#/c/55771/, "force ParameterNameSubstitution to true", I checked YDriver.cxx and there's already the property at true (see https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/mysql/YDriver.cxx#211)

I didn't test but it seems that default value of "ParameterNameSubstitution" is in 
https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/dataaccess/ModelImpl.cxx#992
Thought it might help for tdf#117589 put in see also if the by default value is true.
Comment 16 Lionel Elie Mamane 2018-06-15 11:36:34 UTC
(In reply to Julien Nabet from comment #15)
> Lionel: about your comment https://gerrit.libreoffice.org/#/c/55771/, "force
> ParameterNameSubstitution to true", I checked YDriver.cxx and there's
> already the property at true (see
> https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/mysql/
> YDriver.cxx#211)

Hmm... Maybe the bug then is that if ParamterNameSubstitution is _already_ in info, it is added a second time, and only the first value takes effect. In that case, IMHO it needs to be *changed* in info, not appended to it. Can you check if my guess is correct?

> I didn't test but it seems that default value of "ParameterNameSubstitution"
> is in 
> https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/dataaccess/
> ModelImpl.cxx#992
> Thought it might help for tdf#117589 put in see also if the by default value
> is true.

No, that's a LibreOffice-wide default for all drivers, I wouldn't touch it for the needs of a few drivers.
Comment 17 Commit Notification 2018-06-18 21:20:13 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4de33eb2b5a802e8eda36c5806913efeead68441&h=libreoffice-6-0

tdf#118112: Use ParameterNameSubstitution for Mysql

It will be available in 6.0.6.

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.
Comment 18 Alex Kempshall 2018-06-20 06:51:03 UTC
Is my understanding correct in that this patch -

1.
will stop the problem in new applications;

2.
applications built before this patch will have to be fixed by manually applying one of the work arounds.

If the above is true can we make a comment in the change log and/or release notes to that effect and also provide a macro in LibreOffice Macros & Dialogs that can be manually run to fix the problem in old applications.
Comment 19 Xisco Faulí 2018-07-23 12:00:11 UTC
A polite ping to Julien Nabet:
Is this bug fixed? if so, could you please close it as RESOLVED FIXED ? Otherwise, Could you please explain what's missing?
Thanks
Comment 20 Julien Nabet 2018-07-23 12:25:32 UTC
Xisco: I must recognize I don't know.
We may say it's fixed because the brand new files won't have the pb + we can fix this with a macro.
We may say it's not fixed yet (or partly fixed) because we'd like "force ParameterNameSubstitution to true" and I don't know how to do it.
Comment 21 Alex Kempshall 2018-07-23 12:28:56 UTC
I manually applied one of the work arounds and it fixed the problem for me.

I've not built an application from scratch to see whether the problem never occurs.