Error converting value

[Silvio]

I have a database table CUSTOMERS with a column CUSTOMER_CODE VARCHAR(20). This column used to be filled with integral codes. Executing

SELECT COUNT(*) FROM CUSTOMERS WHERE CUSTOMER_CODE = 12345

always used to work fine on the table. Now for some reason a CUSTOMER_CODE value 'C' has been inserted in the table. Since then, the same SELECT statements generates Error converting 'C' or something similar.

This occurs with both versions 1.4.200 and 2.1.214

It may be that this is conform SQL standards but it seems counter-intuitive to me. Why is the INTEGER literal 12345 not converted to VARCHAR but is the VARCHAR value in the column converted to INTEGER to evaluate the condition?

[Evgenij Ryazanov]

Hello!

In the SQL Standard all character strings with universal character set (H2 doesn't have any other character sets) are comparable with each other and all numbers are comparable with each other. Comparison operations between different groups of data types aren't described, so there is no required behavior and you cannot assume anything about them, for example, database may reject them all.

De-facto database systems usually allow this comparison and convert character strings to numbers, so there is nothing special in behavior of H2. This behavior is actually useful for the most of cases.

In your case you need to use '12345' instead of 12345 to avoid failures and to allow usage of index (if this column has an index or a primary key or unique constraint).

[Silvio]

Thanks Evgenij,

That is clear enough. Intuitively I would have expected a conversion to the column type because apart from only having one conversion that would have been the only way to use an index on the column. But that is just me.

Lacking standard behavior rejecting the comparison sounds plausible. At least that would have failed consistently while this error only occurred after non-numerical content ended up in that column and brought down a system that had been running for three years.

I guess we will have to explicitly check the column type and do the parameter conversion upfront.

Cheers, Silvio