New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Firebird returns truncation error for valid LIKE / SIMILAR TO patterns that are longer than the field length [CORE3559] #1361
Comments
Modified by: @mrotteveeldescription: 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 ( insert into testtable(field1) values ('abcdefghij') Executing the following query should work, but actually returns an error: The error (in FlameRobin): SQL Message : -802 Engine Code : 335544321 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: 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 DNET124 (which was fixed with an incorrect workaround), DNET178, DNET377, DNET328, JDBC132 (maybe?), CORE1217 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: => 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 ( insert into testtable(field1) values ('abcdefghij') Executing the following query should work, but actually returns an error: The error (in FlameRobin): SQL Message : -802 Engine Code : 335544321 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: 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 DNET124 (which was fixed with an incorrect workaround), DNET178, DNET377, DNET328, JDBC132 (maybe?), CORE1217 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: |
Modified by: @mrotteveeldescription: 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 ( insert into testtable(field1) values ('abcdefghij') Executing the following query should work, but actually returns an error: The error (in FlameRobin): SQL Message : -802 Engine Code : 335544321 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: 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 DNET124 (which was fixed with an incorrect workaround), DNET178, DNET377, DNET328, JDBC132 (maybe?), CORE1217 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: => 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 ( insert into testtable(field1) values ('abcdefghij') Executing the following query should work, but actually returns an error: The error (in FlameRobin): SQL Message : -802 Engine Code : 335544321 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: 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 DNET124 (which was fixed with an incorrect workaround), DNET178, DNET377, DNET328, JDBC132 (maybe?), CORE1217 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: |
Modified by: @mrotteveeldescription: 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 ( insert into testtable(field1) values ('abcdefghij') Executing the following query should work, but actually returns an error: The error (in FlameRobin): SQL Message : -802 Engine Code : 335544321 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: 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 DNET124 (which was fixed with an incorrect workaround), DNET178, DNET377, DNET328, JDBC132 (maybe?), CORE1217 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: => 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 ( insert into testtable(field1) values ('abcdefghij') Executing the following query should work, but actually returns an error: The error (in FlameRobin): SQL Message : -802 Engine Code : 335544321 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: 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 DNET124 (which was fixed with an incorrect workaround), DNET178, DNET377, DNET328, JDBC132 (maybe?), CORE1217 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: |
Modified by: @mrotteveeldescription: 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 ( insert into testtable(field1) values ('abcdefghij') Executing the following query should work, but actually returns an error: The error (in FlameRobin): SQL Message : -802 Engine Code : 335544321 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: 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 DNET124 (which was fixed with an incorrect workaround), DNET178, DNET377, DNET328, JDBC132 (maybe?), CORE1217 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: => 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 ( insert into testtable(field1) values ('abcdefghij') Executing the following query should work, but actually returns an error: The error (in FlameRobin): SQL Message : -802 Engine Code : 335544321 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: 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 DNET124 (which was fixed with an incorrect workaround), DNET178, DNET377, DNET328, JDBC132 (maybe?), CORE1217 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: Similar problems exist for SIMILAR TO: summary: Firebird returns truncation error for valid LIKE patterns that are longer than the field length => Firebird returns truncation error for valid LIKE / SIMILAR TO patterns that are longer than the field length |
Commented by: @dyemanov See also CORE251 which describes the origin of the problem. Unless you insist that LIKE/SIMILAR should be processed differently than the rest of the comparisons, this ticket will be closed as a duplicate. I'm afraid, it's unlikely to be fixed using the current API. There's no such thing as "parameters of unlimited size" in Firebird. Describing the parameter using the maximum supported string length (32KB) is not a good option, as the total size of input parameters is limited by 64KB, so a single LIKE predicate would eat the half of the possible parameter space and possibly break the existing applications, and two LIKEs won't leave any space for other parameters at all. There's a good chance Firebird 3.0 will have an extended API which removes the message size limit (total size of input/output parameters), so it would be safe to describe such parameters as 32KB strings. But I cannot think of any other solution that could be implemented in earlier FB versions. |
Modified by: @dyemanovpriority: Critical [ 2 ] => Major [ 3 ] |
Commented by: @mrotteveel The root cause is indeed the same as CORE251. The problem however is more pronounced for LIKE and - especially - SIMILAR TO, as they are more likely to have parameters with patterns which exceed the field length. So if it is hard to have a generic fix for CORE251 for 2.5, then at least a specific fix for LIKE and SIMILAR TO is needed. |
Commented by: @dyemanov I understand, but what would be that specific fix, provided that we cannot use 32K? field_length + 10 characters? Other ideas? |
Modified by: @mrotteveel |
Commented by: @mrotteveel The problem, especially with SIMILAR TO, is that you can create patterns that are much(!) bigger than the actual field. A contrived, but still simple example: Adding 10 characters, or for example doubling the length will work for the simple cases, but not for the more complex cases as above. Describing the parameter as BLOB SUB_TYPE 1 might work, but I am not sure if all clients/connection libraries are able to support that. |
Commented by: @asfernandes What about if you just construct a SQLVAR greater than the one described by the engine? I guess it will be the same, but I suppose the solution for the problem should be in this direction. Client code may use a length specific for the parameter, but in the engine we should not copy the message to an internal one with the described length, but pass the user message directly to inner layers. Of course, this is easy to speak about but not easy to make it work in all cases (ex: procedure parameters should be as it's now). |
Commented by: @hvlad > I understand, but what would be that specific fix, provided that we cannot use 32K? field_length + 10 characters? Other ideas? |
Commented by: @mrotteveel According to section 20.6 <prepare statement> of the SQL:2011 Foundation, the parameter in a LIKE predicate should have type CHARACTER VARYING with the maximum length of the datatype (actually it looks like it actually says that most parameters in a predicate with CHARACTER or CHARACTER VARYING should be CHARACTER VARYING with the maximum length of the datatype) |
Commented by: @dyemanov Mark, this is not going to work for any application using the legacy API, so we simply cannot support the SQL requirements. |
Commented by: @mrotteveel In what way does the legacy API cause this restriction? Looking at the wire protocol, no such limitation exists in the message format itself. |
Commented by: @dyemanov At the protocol layer, messages are passed as CSTRING with cstr_length transferred using xdr_short. And the fact that shorts are actually transfered as longs doesn't completely fix the problem due to possible signed/unsigned issues when different expecations are used (e.g. 60K transferred as signed long should not become negative). But the wire protocol is not the only problem, the public API definitions also use explicit 16-bit lengths for SQL strings / BLR lengths / message lengths / etc, so all applications that use fbclient are affected. It's not a problem for the new API anymore, but the engine has no idea what API was used by the client. |
Modified by: @mrotteveel |
Commented by: Rafael Dipold (dipold) A traditional example: SELECT T.* Where VARCHAR_10_COLUMN is a color description: 'YELLOW', 'RED', 'BLUE', 'BLACK', etc. And :PARAM are all colors that I want to filter ('YELLOW;WHITE;BLUE;') To around this problem, make a cast: SELECT T.* |
Modified by: @pavel-zotovstatus: Open [ 1 ] => Open [ 1 ] QA Status: Deferred Test Details: Deferred until CORE251 will be state='Resolved'. |
Modified by: @mrotteveel |
Modified by: @mrotteveel |
Submitted by: @mrotteveel
Is related to JDBC132
Is related to CORE251
Relate to JDBC477
Is related to DNET976
Votes: 5
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 testtable 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 (except for CHAR comparison when spaces are involved!),
* 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 DNET124 (which was fixed with an incorrect workaround), DNET178, DNET377, DNET328, JDBC132 (maybe?), CORE1217 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 testtable where field1 like CAST(? AS VARCHAR(50)) ESCAPE ''#''') ('a#_c_e_g_ij') INTO :field1;
or
execute statement ('select field1 from testtable where CAST(field1 AS VARCHAR(50)) like ? ESCAPE ''#''') ('a#_c_e_g_ij') INTO :field1;
Similar problems exist for SIMILAR TO:
execute statement ('select field1 from testtable where field1 similar to ? ESCAPE ''#''') ('a[a-k]cdefghij') INTO :field1
====== Test Details ======
Deferred until CORE251 will be state='Resolved'.
The text was updated successfully, but these errors were encountered: