LOWER function does not work as expected in v 2.1.214

[Mehmet Cakir]

Hi together,

do yo also have the problem that the lower function delivers empty results though it should return results? I am using h2 in version 2.1.214 as an in-memory database with my spring boot application and since the upgrade from v1.x to 2.x the lower function does not work as expected. I have also tried simple queries like

SELECT * FROM XXX WHERE LOWER(XYZ) = 'somevalue';

Is this a bug or what am I missing to do?

[Evgenij Ryazanov]

Hello!

You need to provide a complete test case. Something like

CREATE TABLE XXX(XYZ VARCHAR(100), …);

INSERT INTO XXX(XYZ, …) VALUES (…);

[Mehmet Cakir]

That was a good hint, I realized that my data structure is not optimized.. I am using a JAVA application and the tables are automatically created on startup. Therefore I never looked for the whole TEST CASE..The datatype Character (JAVA) is mapped to CHARACTER(255) and it is filled by blanks. Maybe this could be a temporary fix for my solution to make it to CHARACTER(1).... But the same code worked with h2 in version 1.x.... 

CREATE TABLE TEST (ID BIGINT, SOMEVALUE CHARACTER(255));

INSERT INTO TEST (ID, SOMEVALUE) VALUES(1, 'Y');

SELECT * FROM TEST WHERE LOWER(SOMEVALUE) = 'y';

delivers empty result but 

SELECT * FROM TEST WHERE SOMEVALUE = 'Y';

is fine..

is the function LOWER attended to recognize blanks?

[Evgenij Ryazanov]

CHARACTER is a fixed-width data type. Columns of CHARACTER data type always have exactly one character. Columns of CHARACTER(255) data type always have exactly 255 characters, shorted values are right-padded with spaces. If you need to store strings of different length, you should always use CHARACTER VARYING / VARCHAR data type instead. If you need to store exactly one character, you can use CHARACTER or CHARACTER(1); but don't use CHARACTER(255) for this purpose, such choice is obliviously wrong.

More details are described here:

https://github.com/h2database/h2database/issues/3385

[Mehmet Cakir]

That's completely right. The datastructure is not defined on my own.. It is Spring Boots hibernate / jpa which is defining the datastructure for my unit tests... BUT: I cannot understand why the same query is working without the LOWER() function. 

CREATE TABLE TEST (ID BIGINT, SOMEVALUE CHARACTER(255));

INSERT INTO TEST (ID, SOMEVALUE) VALUES(1, 'Y');

SELECT * FROM TEST WHERE SOMEVALUE = 'Y';

is working fine... The shorted value with one character is also right-padded with spaces but the query is working fine... The point is that I am using h2 as an in-memory database for my unit tests in a spring boot application. The tests are the same but after upgrading to h2 v2.. my unit tests are failing... I fixed my issue by giving the datatype a fixed length of 1 but I just wanted to know if this is an unseen issue or if this is a wanted feature. 

The padding point is completely fine but it is confusing me that just the LOWER() function is caring about the blanks..

[Evgenij Ryazanov]

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.