New h2 does not support continuously umber comparisons!

[Ruan Xin]

I found in one of my oldder program which supplied to other people/customers, I can use continous number comparisons like this:

300 < num < 900

with the h2 database version: 1.4.196.

While the new(not newest) h2 database with version 2.0.206, it comes to a error:

[90110-206]

Values of types "BOOLEAN" and "INTEGER" are not comparable;

The smallest reproduction case:

 CREATE TABLE rxt1_ABI35149 (
id_ varchar(32),
num_ decimal(12,2)
)
INSERT INTO rxt1_ABI35149 VALUES ('a', 230);
INSERT INTO rxt1_ABI35149 VALUES ('b', 500);

SELECT * FROM rxt1_ABI35149

SELECT id_, CASE WHEN a.NUM_ < 300 THEN '5分钟以下' ELSE (CASE WHEN 300 <= a.NUM_<900 THEN '5分钟（含）~15分钟' ELSE (CASE WHEN a.NUM_ >= 1800 THEN '30分钟及以上' ELSE '15分钟（含）~30分钟' END) END) END
FROM rxt1_ABI35149 a   

Why the act changed ? How can I fix it ?

[Evgenij Ryazanov]

Hello!

Such expression is not valid in SQL. You need to write it as 300 < num AND num < 900.

In historic versions of H2 boolean result of 300 < num (TRUE, FALSE, or NULL/UNKNOWN) was treated as 1, 0, or NULL and compared with 900. The whole expression returned NULL if num is a null value and TRUE otherwise.

Modern versions of H2 protect you from that type of mistakes.

[Ruan Xin]

I now understand and agree with you, thanks for your reply!

--
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...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/h2-database/ea5c8b06-27e8-47e8-929a-43f05fcd5216n%40googlegroups.com.