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
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.
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.
I gave a try with: https://gerrit.libreoffice.org/c/core/+/105434
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.
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.