Bug 127657 - FIREBIRD: Query input parameter throws error when referred field is integer
Summary: FIREBIRD: Query input parameter throws error when referred field is integer
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.3.0.4 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.4.0 target:6.3.3
Keywords:
Depends on:
Blocks: Database-HSQLDB-Removal
  Show dependency treegraph
 
Reported: 2019-09-20 07:56 UTC by herman.viaene
Modified: 2020-02-11 16:44 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
testcase for input parameter in query firebird embedded (97.98 KB, application/vnd.oasis.opendocument.database)
2019-09-20 12:45 UTC, herman.viaene
Details
bt with debug symbols (13.54 KB, text/plain)
2019-09-20 22:08 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description herman.viaene 2019-09-20 07:56:53 UTC
Table "Gezinnen"
3 columns:
gezinsid primary key autonummer
gezinsnaam varchar
huwelijksdatum date (in english marriage date)

Now I want to make a query with a parameter  which should refer to the month of huwelijksdatum,
the query reads as

select "gezinsnaam", extract(month from "Gezinnen"."huwelijksdatum") from "Gezinnen";
that works OK

now adding the parameter:
select "gezinsnaam", extract(month from "Gezinnen"."huwelijksdatum") from "Gezinnen" where extract(month from "Gezinnen"."huwelijksdatum")=:huwmaand;
When I run this, I get the dialogue to ask for the value of huwmaand, but whatever I tried, just a digit, or # around it as a date or " or ', I always get "Incorrect type for setString".
I'm at loss as I read in the Firebird FAQ (or whatever it is from Firebird) that extract(month......) returns a smallint, so where do I go wrong??

I tried to get around this by saving the first select as a query, works OK and then opening a second query which takes the first as its source, and add the parameter there, but no success.

BTW: I do this on Win10, but using the the same odb on Linux Mageia 6 or Mageia 7 with the same version of LibreOffice gives the same result.
Casting the month expression to varchar(2) , then the query works. 
I couldn't find anything which indicates that input paramaters are limited to strings, and numeric is not accepted. So I consider this a bug.
Comment 1 Alex Thurgood 2019-09-20 11:01:09 UTC
Let me guess, the code for paramater substitution for Firebird only accepts char string literals, so that when an INT is returned from extract, you get the error message about setString having an incorrect type...
Comment 2 Alex Thurgood 2019-09-20 11:02:41 UTC
@herman : does this apply to embedded hsqldb database as well, or is it just Firebird embedded ?

Ideally, we would need a sample ODB file to be able to try and reproduce.
Comment 3 herman.viaene 2019-09-20 12:29:18 UTC
Imported the same table in a hsqldb database and there the input works OK without casting to varchar.
I'll make a copy of the offending firebird based database with just the needed table and attach it here.
Comment 4 herman.viaene 2019-09-20 12:45:38 UTC
Created attachment 154321 [details]
testcase for input parameter in query firebird embedded
Comment 5 Robert Großkopf 2019-09-20 13:17:39 UTC
Could confirm the buggy behaviour with LO 6.3.2.1 on OpenSUSE 15.

It's a special Firebird-bug which throws "Incorrect type for setString" when putting an integer in the parameter-dialog. Directly writing this integer into the query will work.
Comment 6 Alex Thurgood 2019-09-20 14:01:56 UTC
@herman : thanks! and thanks to Robert for confirming.
Comment 7 Alex Thurgood 2019-09-20 14:07:34 UTC
Also confirmed on 

Version: 6.3.1.2
Build ID: b79626edf0065ac373bd1df5c28bd630b4424273
Threads CPU : 8; OS : Mac OS X 10.14.6; UI Render : par défaut; VCL: osx; 
Locale : fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR
Calc: threaded
Comment 8 Alex Thurgood 2019-09-20 14:08:50 UTC
And also :

Version: 6.4.0.0.alpha0+
Build ID: f0c832acb53326ccc9a8c1a47401fbc9e1081feb
CPU threads: 8; OS: Mac OS X 10.14.6; UI render: GL; VCL: osx; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2019-09-11_05:45:47
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded
Comment 9 Julien Nabet 2019-09-20 22:08:53 UTC
Created attachment 154335 [details]
bt with debug symbols

On pc Debian x86-64 with master sources updated today, I could reproduce this.

I attached a bt from setString error.

debugging a bit indicates that dtype =  SQL_SHORT
so it goes to default case:
    235     default:
    236         ::dbtools::throwSQLException(
    237             "Incorrect type for setString",
    238             ::dbtools::StandardSQLState::INVALID_SQL_DATA_TYPE,
    239             *this);

I got no idea how to fix this.
I tried to call setShort but it expects a short value and I'm not sure that converting OUString sInput into a short is the right way.
Comment 10 Julien Nabet 2019-09-20 22:09:19 UTC
uncc myself since I can't help here.
Comment 11 Lionel Elie Mamane 2019-09-21 05:17:37 UTC
(In reply to Julien Nabet from comment #9)
> I'm not sure that converting OUString sInput
> into a short is the right way.

That sounds like exactly the right way.
Comment 12 Julien Nabet 2019-09-21 10:01:21 UTC
(In reply to Lionel Elie Mamane from comment #11)
> (In reply to Julien Nabet from comment #9)
> > I'm not sure that converting OUString sInput
> > into a short is the right way.
> 
> That sounds like exactly the right way.

I don't know if we should convert OUString "sInput" or OString "str", in both cases, I don't know too how to convert OUString to short or OString to short.
Finally, I suppose we should do the same kind of thing with Date, int, etc.
Comment 13 Julien Nabet 2019-09-21 13:23:18 UTC
Finally found toInt32() method.
I submitted https://gerrit.libreoffice.org/#/c/79314/ for review. It deals only with SQL_SHORT
Comment 14 Commit Notification 2019-09-23 06:51:13 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

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

tdf#127657: FIREBIRD error in query input param when referred field is integer

It will be available in 6.4.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 15 Commit Notification 2019-09-23 08:00:06 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/commit/3a70b4a1be0e790579cc15e9917bd575de8d5315

tdf#127657: FIREBIRD error in query input param when referred field is integer

It will be available in 6.3.3.

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.