Firebird returns truncation error for valid LIKE patterns that are longer than
the field length
-----------------------------------------------------------------------------------------------
Key: CORE-3559
URL: http://tracker.firebirdsql.org/browse/CORE-3559
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.1.4, 2.5.0
Reporter: Mark Rotteveel
Priority: Critical
When executing parametrized queries with a LIKE condition that includes a
pattern which is longer than the lefthand side field, Firebird returns a string
truncation error.
For example:
CREATE TABLE testtable (
field1 VARCHAR(10)
)
insert into testtable(field1) values ('abcdefghij')
insert into testtable(field1) values ('a_c_e_g_ij')
Executing the following query should work, but actually returns an error:
set term !;
execute block
as
declare field1 varchar(50);
begin
execute statement ('select field1 from testtab where field1 like ? ESCAPE
''#''') ('a#_c_e_g_ij') INTO :field1;
end!
set term ;!
The error (in FlameRobin):
Error: *** IBPP::SQLException ***
Context: Statement::Execute(
execute block
as
declare field1 varchar(50);
begin
execute statement ('select field1 from testtab where field1 like ? ESCAPE
''#''') ('a#_c_e_g_ij') INTO :field1;
end )
Message: isc_dsql_execute2 failed
SQL Message : -802
Arithmetic overflow or division by zero has occurred.
Engine Code : 335544321
Engine Message :
arithmetic exception, numeric overflow, or string truncation
string right truncation
Firebird is limiting the parameter length to the field length when doing
comparisons, it should not do that, especially not when LIKE patterns are
involved. But even in general it should not do this:
* in the case of equality comparison, a string with different length is simply
not the same,
* and in the case of a not equals comparison currently an error is returned,
instead of the expected value(s) that are all not equal.
This problem currently makes a Jaybird test
(TestFBPreparedStatement#testLikeFullLength()) fail (which seems to indicate it
might have worked in the past), it is also responsible for a similar error in
kinterbasdb 3.3, and caused DNET-124 (which was fixed with an incorrect
workaround), DNET-178, DNET-377, DNET-328, JDBC-132 (maybe?), CORE-1217
and probably some more.
I would say that a parameter involved in a comparison with CHAR or VARCHAR,
should be allowed to have an unlimited length (or at least: the maximum valid
length).
A (unsatisfying) workaround is to put a CAST around the field or the parameter
for a longer length: eg in the above example (and the failing test in Jaybird)
this does work:
execute statement ('select field1 from testtab where field1 like CAST(? AS
VARCHAR(50)) ESCAPE ''#''') ('a#_c_e_g_ij') INTO :field1;
or
execute statement ('select field1 from testtab where CAST(field1 AS
VARCHAR(50)) like? ESCAPE ''#''') ('a#_c_e_g_ij') INTO :field1;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
AppSumo Presents a FREE Video for the SourceForge Community by Eric
Ries, the creator of the Lean Startup Methodology on "Lean Startup
Secrets Revealed." This video shows you how to validate your ideas,
optimize your ideas and identify your business strategy.
http://p.sf.net/sfu/appsumosfdev2dev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
