Skip to content
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

Jump to bottom

Firebird returns truncation error for valid LIKE / SIMILAR TO patterns that are longer than the field length [CORE3559] #1361

Open
firebird-automations opened this issue Jul 16, 2011 · 21 comments
Open

Firebird returns truncation error for valid LIKE / SIMILAR TO patterns that are longer than the field length [CORE3559] #1361

firebird-automations opened this issue Jul 16, 2011 · 21 comments
Labels
affect-version: 2.1.4 affect-version: 2.5.0 priority: major qa: deferred type: bug

Comments

@firebird-automations
Copy link
Collaborator

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'.
@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: 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 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 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;

=>

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 (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 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;

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: 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 (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 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;

=>

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 (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 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;

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: 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 (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 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;

=>

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;

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: 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;

=>

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

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Critical [ 2 ] => Major [ 3 ]

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I understand, but what would be that specific fix, provided that we cannot use 32K? field_length + 10 characters? Other ideas?

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Link: This issue is related to JDBC132 [ JDBC132 ]

@firebird-automations
Copy link
Collaborator Author

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:
If I have a 5 character field and I am looking for a pattern with 2 letters, 1 number and 2 letters the pattern is:
[[:ALPHA:]]{2}[[:DIGIT:]][[:ALPHA:]]{2}
which is a pattern of length 39 or almost 8 times as long as the field itself

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.

@firebird-automations
Copy link
Collaborator Author

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).

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

> I understand, but what would be that specific fix, provided that we cannot use 32K? field_length + 10 characters? Other ideas?
Make it large enough for most usages, for example varchar(255)
?

@firebird-automations
Copy link
Collaborator Author

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)

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Link: This issue is related to CORE251 [ CORE251 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Rafael Dipold (dipold)

A traditional example:

SELECT T.*
FROM TEST_TABLE T
WHERE (:PARAM LIKE ('%' || T.VARCHAR_10_COLUMN || '%'))

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.*
FROM TEST_TABLE T
WHERE (:PARAM LIKE ('%' || CAST(T.VARCHAR_10_COLUMN AS VARCHAR(100)) || '%'))

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Open [ 1 ] => Open [ 1 ]

QA Status: Deferred

Test Details: Deferred until CORE251 will be state='Resolved'.

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Link: This issue relate to JDBC477 [ JDBC477 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Link: This issue is related to DNET976 [ DNET976 ]

This was referenced Apr 8, 2023
Closed
Closed
Closed
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 2.1.4 affect-version: 2.5.0 priority: major qa: deferred type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations