Incompatible column/host variable data type with latest FB 3.0 version

[Kevin Morizur]

Hello,

I'm currenlty having error when using latest FB 3.0 binary. 

From my application 

• request is update or insert into table(LABEL) values(coalesce(?,''))
• array of params sent : ['my label']

LABEL is Column of type VARCHAR 100

With version WI-V6.3.10.33601 Firebird 3.0 I get following error :

Incompatible column/host variable data type.
Dynamic SQL Error.
SQL error code = -303.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
expected length 0, actual 5.

With oldest version WI-V6.3.2.32703 Firebird 3.0 there is no error

NB: I get similar error in IBexpert using following request 

"update or insert into table(LABEL) values(coalesce(:param,''))"'

Do you know if this error is a bug or it is expected behavior ?

Thanks in advance.

Kevin M

[Dimitry Sibiryakov]

Kevin Morizur wrote 12.05.2023 15:35:
> Do you know if this error is a bug or it is expected behavior ?

Kinda expected. Datatype of the parameter cannot be derived from any rule so
it is set to SQL_NULL with length zero.
Use CAST(? AS <whatever>) to workaround it.

--
WBR, SD.

[Mark Rotteveel]

On 12-05-2023 15:35, Kevin Morizur wrote:
> I'm currenlty having error when using latest FB 3.0 binary.
>
> From my application
>

> * request is *update or insert into table(LABEL) values(coalesce(?,''))*
> * array of params sent : *['my label']*

>
>
> LABEL is Column of type VARCHAR 100
>

> With version *WI-V6.3.10.33601 Firebird 3.0 *I get following error :

This is Firebird 3.0.10.33601, it seems your application uses an old API
for obtaining Firebird version numbers.

> Incompatible column/host variable data type.
> Dynamic SQL Error.
> SQL error code = -303.
> arithmetic exception, numeric overflow, or string truncation.
> string right truncation.
> expected length 0, actual 5.
>

> With oldest version WI-V6.3.2.32703 Firebird 3.0 *there is no error*

That is 3.0.2.32703.

> NB: I get similar error in IBexpert using following request
> "update or insert into table(LABEL) values(coalesce(:param,''))"'
>
> Do you know if this error is a bug or it is expected behavior ?

It looks like a bug with the type inference of the parameter. It seems
that it infers it from the empty string in the second parameter of
COALESCE, instead of from the type of the column the value will be
assigned to.

I'm not sure if this is a know bug or not. I suggest reporting it on
https://github.com/FirebirdSQL/firebird/issues

Mark
--
Mark Rotteveel

[Mark Rotteveel]

The type should be inferrable, because the type of the assignment site
is known, namely the type of column TABLE.LABEL. That means should be
possible to type COALESCE, and thus the parameters of COALESCE (maybe
Firebird currently doesn't implement it that way, but it is not as if
that information doesn't exist).

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

Mark Rotteveel wrote 13.05.2023 17:01:
> The type should be inferrable, because the type of the assignment site is known,
> namely the type of column TABLE.LABEL. That means should be possible to type
> COALESCE, and thus the parameters of COALESCE (maybe Firebird currently doesn't
> implement it that way, but it is not as if that information doesn't exist).

That's true but still using of COALESCE here looks like an application
developer's laziness. How it is possible that a value of a primary key can be NULL?

--
WBR, SD.