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 On Friday, 3 February 2023 at 16:06:11 UTC+1 Evgenij Ryazanov wrote: > 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). > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ba39277c-f293-4ae3-b6b3-35040c107f99n%40googlegroups.com.
