Because when you use the LOWER or UPPER function, its result doesn't have CHARACTER(255) data type any more, it has CHARACTER VARYING data type. Character string literals also have CHARACTER VARYING data type in H2. So a comparison between two CHARACTER VARYING values is actually performed and this comparison returns FALSE in H2 when strings have different length.
Without this function a comparison between CHARACTER(255) and CHARACTER VARYING values is performed and this comparison ignores trailing spaces in H2. LOWER and UPPER functions should return result of the same data type as their argument according to the SQL Standard, but H2 currently cannot satisfy that requirement, because H2 doesn't have warnings. Some strings in some locales have different lengths after conversion to upper or lower case. If this length is larger than length of original data type, standard-compliant database should truncate the result to the declared length and raise a warning. But H2 cannot warn you about truncation and it is a bad idea to perform this truncation silently, so it isn't performed at all. But to return values longer than argument H2 declares result of these functions as CHARACTER VARYING with the maximum length. Take a look on length and columnDefinition attributes of @Column annotation. You can adjust SQL data type produced by JPA with them. -- 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 h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/62d83651-b378-49e9-9aed-e0bf4b3bf52en%40googlegroups.com.
