Assuming the following setup:
CREATE TABLE "T" ("A" varchar(255));
INSERT INTO "T" VALUES ('foo');
Running the following code returns true in both 1.3.170 and 1.3.154:
SELECT (CAST("A" AS VARCHAR_IGNORECASE) LIKE 'FOO') FROM "T";
And running the following code returns false in both 1.3.170 and 1.3.154:
SELECT (CAST("A" AS VARCHAR_IGNORECASE) LIKE 'BAR') FROM "T";
However, combining the two above expressions via OR, the following code
returns false in 1.3.170 and true in 1.3.154.
SELECT ((CAST("A" AS VARCHAR_IGNORECASE) LIKE 'FOO') OR (CAST("A" AS
VARCHAR_IGNORECASE) LIKE 'BAR')) FROM "T";
true is the correct answer, so this is a regression between 1.3.154 and
1.3.170.
H2 seems to be optimizing the query with OR into "cast(a as
varchar_ignorecase) in('foo', 'bar')", based on the name of the returned
column. H2 seems to be optimizing the first query (without the OR) into
"cast(a as varchar_ignorecase) = 'foo'".
Note that if you use a literal string in the query with OR, 1.3.170 and
1.3.154 both give the correct answer:
SELECT ((CAST('foo' AS VARCHAR_IGNORECASE) LIKE 'FOO') OR (CAST('foo' AS
VARCHAR_IGNORECASE) LIKE 'BAR')) FROM "T";
H2 appears to handle this case differently, since it uses a column name of
"true" instead of "cast('foo' as varchar_ignorecase) in('foo', 'bar')".
This regression was discovered by the regression tests for the Sequel ruby
library, running on JRuby.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/Zn8Kdyisy78J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.