Bug 130596 - FIREBIRD (internal): Parameter doesn't work with COALESCE and numeric fieldtypes
Summary: FIREBIRD (internal): Parameter doesn't work with COALESCE and numeric fieldtypes
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.5.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Julien Nabet
URL:
Whiteboard: target:7.1.0 target:7.0.4
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2020-02-11 16:43 UTC by Robert Großkopf
Modified: 2020-11-09 18:28 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2020-02-11 16:43:52 UTC
Open the database attached here:
https://bugs.documentfoundation.org/attachment.cgi?id=157803
Open the query 
Query_parameter_Coalesce_String
Input a value for paramter "name", for example 'Rob'.
No error appears.
Refresh the query and input nothing for the parameter.
All content appears.

Now open 
Query_parameter_Coalesce_Integer (or ...Decimal)
Input a number like 1.
An error appears:

SQL Status: HY004
Incorrect type for setString

Here is the query:
SELECT * FROM "Table1" WHERE "Integer" = COALESCE ( :testInt, "Integer" )

This query works well with the internal HSQLDB.

All tests made with OpenSUSE 15.1 64bit rpm Linux. Fails with LO 6.1.5.2, also with LO 6.4.0.3
Comment 1 Drew Jensen 2020-02-11 17:54:30 UTC
Tested with Ubuntu 18.04 and LO 6.2.4 and 6.4

I can confirm that the named parameter function is not working properly in the COALESCE function.  

It may be for all functions. I also tried using a parameter in the SUBSTRING function with this query:
SELECT substring( "Name" from 1 for :testDec ) FROM "Table1" 

and received a slightly different error:

Error code: 1

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -804
*Data type unknown
caused by
'isc_dsql_prepare'

while this query:
SELECT cast( :testInt as integer) AS "Int" FROM "Table1" 

returns the original error (from the coalesce) of: 
SQL Status: HY004
Incorrect type for setString

I don't think the data type listed in the subject is important but will try a few other functions before I change that.
Comment 2 Robert Großkopf 2020-02-12 15:55:41 UTC
Have tested this a little bit more.
SELECT * FROM "Table1" WHERE "Integer" = COALESCE ( :testInt, "Integer" )
gives
SQL Status: HY004
Incorrect type for setString

SELECT * FROM "Table1" WHERE CAST( "Integer" AS VARCHAR ( 10 ) ) = COALESCE ( :testInt, CAST( "Integer" AS VARCHAR ( 10 ) ) )
works.

There is something going wrong with the datatypes. I have set all INTEGER to strings and it will work.
Comment 3 Julien Nabet 2020-11-07 15:13:52 UTC
I gave a try with:
https://gerrit.libreoffice.org/c/core/+/105434
Comment 4 Commit Notification 2020-11-08 11:07:16 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/344e26ace0a65c23cb1020b5907639fa9a67255e

tdf#130596 FIREBIRD error in query input param when referred field is SQL_LONG

It will be available in 7.1.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 5 Commit Notification 2020-11-09 11:01:02 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-7-0":

https://git.libreoffice.org/core/commit/a5f5a404caa813a1748df5d2372fb1659fcaf3a9

tdf#130596 FIREBIRD error in query input param when referred field is SQL_LONG

It will be available in 7.0.4.

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

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