[regression] Syntax error on valid query: expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"

[php-coder]

The following query worked on H2 1.4.200 (and MySQL, PostgreSQL as well) but fails after update to 2.2.224:

SELECT * FROM params WHERE 'http://127.0.0.1:8080' LIKE CONCAT(value, '%')

The error message:

Syntax error in SQL statement "SELECT * FROM params WHERE 'http://127.0.0.1:8080' LIKE CONCAT([*]value, '%')"; expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
SELECT * FROM params WHERE 'http://127.0.0.1:8080' LIKE CONCAT(value, '%') [42001-224] 42001/42001

[katzyn]

This query is not valid.

1. VALUE is a reserved word in the SQL Standard (even in archaic SQL-92) and is a keyword in H2. It may not and cannot be used as unquoted identifier. You need to quote it as "value" (if you use DATABASE_TO_LOWER=TRUE setting) or you can use NON_KEYWORDS=VALUE setting as a workaround if you cannot fix your query.
2. There is no CONCAT function in SQL. SQL has only || operator for concatenation of character strings, binary strings or arrays. H2 has such function, however, for a limited compatibility with own historic versions and some other database systems.

[php-coder]

or you can use NON_KEYWORDS=VALUE setting as a workaround if you cannot fix your query

I wasn't able to make it work, by the way. Is it possible that it's broken? I used the following connection string:

spring.datasource.url: jdbc:h2:mem:mystamps;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;DATABASE_TO_UPPER=false;NON_KEYWORDS=VALUE,value

The aforementioned SQL fails with: Column "value" not found

[katzyn]

With DATABASE_TO_UPPER=FALSE all identifiers are case sensitive in all modern versions of H2, don't use this setting.

[katzyn]

And you don't need to specify value twice here: NON_KEYWORDS=VALUE,value

[php-coder]

Thanks!

Anyway I still couldn't make it working. I got the same error with configuration:

spring.datasource.url: jdbc:h2:mem:mystamps;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;NON_KEYWORDS=value

[katzyn]

It means your table doesn't have this column, you need to check its definition.

[php-coder]

It means your table doesn't have this column, you need to check its definition.

No, the column is in place. You can check this out:

$ git clone https://github.com/php-coder/mystamps.git
$ cd mystamps
$ git checkout -t origin/h2_bug_repro
$ ./mvnw spring-boot:run &
$ open http://127.0.0.1:8080/console

And log in with username sa and empty password. Any queries to site_parser_params.value don't work:

(Need to mention, that the project won't compile on recent JDK, 8th version is required.)

[manticore-projects]

@php-coder: Please provide a simplified, self-containing test case. All in one java class. I doubt that someone here will run anything with spring and webserver (although I may be wrong.)

[katzyn]

expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS";

This exception appears if NON_KEYWORDS=VALUE is not specified. This setting isn't really required, but without it you must use quoted name of your column ("value" or "VALUE" depending on its definition).

Column "value" not found

This exception appears when you definitely don't have a column with such name.

I don't think we have a bug here, most likely something is wrong in your project.

Anyway, we don't accept test cases with third-party dependencies, please use SQL, Java 11 and H2 only.