Bug 86852 - Query: Parameter-dialog should submit NULL instead of empty string, if there is no input.
Summary: Query: Parameter-dialog should submit NULL instead of empty string, if there ...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:4.5.0 target:4.4.0.0.beta2
Keywords:
Depends on:
Blocks:
 
Reported: 2014-11-29 15:39 UTC by Robert Großkopf
Modified: 2014-12-04 06:47 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Look a the difference between query and form - both use the same query (12.37 KB, application/vnd.oasis.opendocument.base)
2014-11-29 15:39 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2014-11-29 15:39:02 UTC
Created attachment 110228 [details]
Look a the difference between query and form - both use the same query

Open the attached database. Have a look to the table. It contains 10 rows with data, but much some of the fields don't contain any value.
There is a query with two parameters. Execute the query whitout filling any content to the parameters: 4 rows would be shown.
Now open the form. The form contains a subform, which works with the same query. The fields in the mainform should be empty. Press the button "Search". The same query in the form shows 10 rows, outside the form it shows 4 rows.

Let's have a look at a part of the code of the query:
SELECT * FROM "Table" WHERE ( LOWER ( "Firstname" ) LIKE LOWER ( :qfirstname ) || '%' OR :qfirstname IS NULL )
The problem is the definition of "OR :qfirstname IS NULL". Fields in a form will give NULL to a field of a table, if there isn't any content written. So the paramter could be NULL, if it is started by a query inside a form.
The dialog for parameter-input won't set NULL to an empty parameter. It will set an empty string instead. So the second condition couldn't be reached.

Queries shouldn't work different inside or outside a form.
Input of values in a form shouldn't work different to input of values through a dialog. So the dialog for paramters should submit NULL instead of an empty string.
Comment 1 Julien Nabet 2014-11-29 23:37:50 UTC
On pc Debian x86-64 with master sources updated today, I could reproduce this.

Lionel: one for you?
Comment 2 Commit Notification 2014-12-01 18:07:43 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=5a96c7cb511d1997d8e5188fbaa028c6ff1b2dd3

fdo#86852 correctly recognise NULL values in query parameter values dialog

It will be available in 4.5.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 3 Commit Notification 2014-12-02 12:50:44 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6254fa3ee4310f2fc421d95294d1f1e902196463&h=libreoffice-4-4

fdo#86852 correctly recognise NULL values in query parameter values dialog

It will be available in 4.4.0.0.beta2.

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 4 Robert Großkopf 2014-12-03 18:55:57 UTC
Could confirm it works right with the daily build from 2014-12-03, LO 4.4.0.0beta1, 64bit rpm Linux.
Thanks for the very fast fix.
Could it also be backported to LO 4.3.6 ?
Should I set this to "Verified" now?
Comment 5 Lionel Elie Mamane 2014-12-04 06:05:24 UTC
(In reply to robert from comment #4)
> Could it also be backported to LO 4.3.6 ?

It is a somewhat invasive fix code-wise, I'd rather not do it that late in the 4.3 cycle. If it were for x.y.1 or x.y.2 I'd do it.

> Should I set this to "Verified" now?

Yes, thanks.