exception using IN filter with multiple columns, when data contains null

[nzanaga]

If a table contains null value on some records (even if the data is not matched by the filter),

h2 generate exeption with version 1.4.200  (with versione 1.4.197 it's work):

SQL [22018] [22018]: Data conversion error converting "ROW (a3, null)";

Test case:

CREATE TABLE x (

  col1  varchar(10),
  col2  varchar(10)
)

INSERT INTO x (col1, col2) VALUES ('a1', 'b1')
INSERT INTO x (col1, col2) VALUES ('a2', 'b2')
INSERT INTO x (col1, col2) VALUES ('a3', null)


SELECT col1 FROM x
WHERE 
 (col1, col2) IN (('a1', 'b1'),('a2', 'b2'))

 

[Evgenij Ryazanov]

Hello.

Yes, it looks like a bug. IN predicate may have a row value expressions in its right side, but this case is not currently supported. Older version works only because it incorrectly parses them as arrays.

Use can use something like

SELECT col1 FROM x WHERE (col1, col2) IN (VALUES ('a1', 'b1'),('a2', 'b2'));

as a workaround.

Please not that NULL is neither equal nor not equal to NULL, you can't fetch ('a3', null) in that way (and with your original query too in databases that support it correctly).

[Evgenij Ryazanov]

Issue with your original query was fixed. If you need the fix right now, you can build H2 from its current sources.

https://github.com/h2database/h2database

Use the jar target as described here:

https://h2database.com/html/build.html#building

The current sources require JDK 8, 9, 10, 11, or 12.

The compiled jar is also compatible with more recent versions.

[nzanaga]

Thanks, I solved prefiixing IN clause using "VALUES "