Bug 107457 - Subforms: Connecting to a field created by a function fails - wrong SQL-Code
Summary: Subforms: Connecting to a field created by a function fails - wrong SQL-Code
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.1.0.0.alpha1
Hardware: x86-64 (AMD64) All
: high major
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard: target:5.4.0 target:5.3.4
Keywords: bibisected, bibisectRequest, regression
Depends on:
Blocks:
 
Reported: 2017-04-26 19:32 UTC by robert
Modified: 2017-05-02 13:08 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Open the form and have a look at the SQL-error. Appears since LO 5.* (11.28 KB, application/vnd.oasis.opendocument.database)
2017-04-26 19:32 UTC, robert
Details

Note You need to log in before you can comment on or make changes to this bug.
Description robert 2017-04-26 19:32:18 UTC
Created attachment 132872 [details]
Open the form and have a look at the SQL-error. Appears since LO 5.*

Open the attached database.
Open the form.
An SQL-error appears.
Let you show the details of the error. It gives the following code:

SELECT "ID", "Text", "DateStart", "DateEnd", YEAR( "DateStart" ) AS "YearStart", YEAR( "DateEnd" ) AS "YearEnd" FROM "Table" WHERE ( "YEAR( "DateStart" )" = :link_from_YearStart ) AND ( "YEAR( "DateEnd" )" = :link_from_YearEnd )

The query uses the function YEAR. The code after WHERE is wrong. There are double-quotes added around function and fieldname. Must be YEAR( "DateStart" ), not "YEAR( "DateStart" )"

The form will work right in LO 4.4.7.2
Fails first here with LO 5.0.0.5
Both tested with OpenSUSE 42.1 64bit rpm Linux.
Comment 1 Xisco Faulí 2017-04-26 22:20:01 UTC
Hello,

it seems you're using an old version of LibreOffice.
Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Comment 2 Alex Thurgood 2017-04-27 07:04:05 UTC
Confirming on

Version: 5.3.2.2
Build ID: 6cd4f1ef626f15116896b1d8e1398b56da0d0ee1
Threads CPU : 8; Version de l'OS :Mac OS X 10.12.4; UI Render : par défaut; Moteur de mise en page : nouveau; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 3 Alex Thurgood 2017-04-27 07:11:07 UTC
Also confirming that no error is produced with

Version: 4.4.5.2
Build ID: a22f674fd25a3b6f45bdebf25400ed2adff0ff99
Locale : fr_FR.UTF-8

OSX 10.12.4
Comment 4 Xisco Faulí 2017-04-28 11:53:23 UTC
Regression introduced in range: https://cgit.freedesktop.org/libreoffice/core/log/?qt=range&q=ffc1ffed11dc63a69fc2db04f12b3ea266b580fe..fb6dd2a73074b9695bd8ddf7ba40f1819b03024e
where I can see some changes done by Lionel Elie Mamane.
Comment 5 Alex Thurgood 2017-04-28 12:49:44 UTC
probably, this commit:

https://cgit.freedesktop.org/libreoffice/core/commit/?id=09a490ddf516289b4ce2f70a19603b9590917632

which deals with parameter name handling.
Comment 6 Alex Thurgood 2017-04-28 13:09:41 UTC
And possibly even this block, which seems to deal with quoting around the column name to build the filter parameter string :

+        {
+            OUString colName;
+            xDetailField->getPropertyValue("RealName") >>= colName;
+            sFilter += quoteName( m_sIdentifierQuoteString, colName ) + " = :";
+        }
Comment 7 Lionel Elie Mamane 2017-04-28 15:27:27 UTC
Thanks for the precise code pointer.
Comment 8 Commit Notification 2017-04-28 15:28:25 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

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

tdf#107457 don't quote columns referred by expression (not by name)

It will be available in 5.4.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 9 Commit Notification 2017-05-02 13:08:24 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b78cabc850e0a71873714c892c46c9edb25fd364&h=libreoffice-5-3

tdf#107457 don't quote columns referred by expression (not by name)

It will be available in 5.3.4.

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.