Invalid parameter type when using it in CHAR_LENGTH function [CORE1379]

[firebird-automations]

Submitted by: Kuznetsov Eugene (eugene)

Is related to QA178

After preparing query

select * from rdb$database
where char_length(:value1) = 1

the type of value1 will be INTEGER

Generally, if parameter is an argument of CHAR_LENGTH, then its type depends on other part's one in equality predicate. No doubt, this behavior is erroneous.

Also, server can't determine parameter type when meeting the single CHAR_LENGTH's call - preparing

select char_length(:value1) from rdb$database

returns ?Data type unknown?.

With best regards, Eugene

Commits: 3ed87ec 64f80d1 FirebirdSQL/fbt-repository@754823e

[firebird-automations]

Commented by: @asfernandes

This is how the engine works, parameters of some expressions assume datatype of the other expression side, i.e., with char_length(?) = 1, the parameter type will be described as integer.

This make sense for substring, for example, but don't for char_length, as you see.

But as also you see, we have problems with parameters alone, i.e., char_length(?) in select list doesn't work, as our API can't describe strings of unknown size.

Given that, I may live with the CHAR_LENGTH bug until we fix the datatype unknown error.

[firebird-automations]

Commented by: @asfernandes

The incorrect parameter type description is fixed, and hence I'm closing this ticket, since we have others about "Data type unknown".

[firebird-automations]

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 2.1 Beta 2 [ 10190 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[firebird-automations]

Modified by: @asfernandes

Link: This issue depends on CORE218 [ CORE218 ]

[firebird-automations]

Commented by: @pcisar

Adriano, how about CORE218 ? This ticket depends on it and it's not resolved, so it looks weird that this one get resolved :) You should also resolve the CORE218 or delete the dependence link.

[firebird-automations]

Modified by: @asfernandes

Link: This issue depends on CORE218 [ CORE218 ] =>

[firebird-automations]

Commented by: @asfernandes

Sorry, I want to say they are related.

But since there is "relate to" and "is related to" and I couldn't differenciate :-), I removed the link now.

[firebird-automations]

Commented by: Kuznetsov Eugene (eugene)

Sorry for pause.

Adriano dos Santos Fernandes wrote
"But as also you see, we have problems with parameters alone, i.e., char_length(?) in select list doesn't work, as our API can't describe strings of unknown size."
Perhaps, does it make sense to desribe this param as VARCHAR(32765)?

With best regards, Eugene

[firebird-automations]

Commented by: @dyemanov

Bad idea, actually, as the input message size is limited to 64K and hence you won't be able to use anything like two CHAR_LENGTH(?) calls.

[firebird-automations]

Commented by: Kuznetsov Eugene (eugene)

To Dmitriy Yemanov

Thanks for info.

With best regards, Eugene

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA178 [ QA178 ]

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 12674 ] => Firebird [ 14247 ]

[firebird-automations]

Commented by: @pavel-zotov

> The incorrect parameter type description is fixed

set term ^;
execute block returns(r int) as
begin
execute statement ('select 1 from rdb$database where char_length(?) = 0') (1) into r;
suspend;
end
^

       R 

============
<null>

execute block returns(r int) as
begin
execute statement ('select 1 from rdb$database where char_length(?) = 0') ('') into r;
suspend;
end
^

       R 

============
Statement failed, SQLSTATE = 22018
conversion error from string ""

execute block returns(r int) as
declare c varchar(1) = '';
begin
execute statement ('select 1 from rdb$database where char_length(?) = 0') (c) into r;
suspend;
end
^

       R 

============
Statement failed, SQLSTATE = 22018
conversion error from string ""

set term ;^

Is it correct ?

PS. WI-T3.0.0.31789

[firebird-automations]

Commented by: @asfernandes

Fixed again in v3, to return "Data type unknown" error.

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

[firebird-automations]

Commented by: @pcisar

Test created.

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Commented by: Arioch (arioch)

DY> as the input message size is limited to 64K

Is it still true for FB3 ?

[firebird-automations]

Commented by: @dyemanov

Arioch> Is it still true for FB3 ?
It depends on the client library version. Older clients won't work with longer messages.