H2 database error for SQL Server prod DB "BOOLEAN" and "CHARACTER VARYING(5)" are not comparable

Question

SQL Server has bit type column to store true and false.

In where clause we check column_name = 'false' it works fine on SQL Server (dev and prod env) but it fails in test case using H2 in memory database.

Error is:

Values of types "BOOLEAN" and "CHARACTER VARYING(5)" are not comparable

H2 database connection related properties I use are:

spring.datasource.url=jdbc:h2:mem:TESTDB;\
  DB_CLOSE_DELAY=-1;\
  MODE=MSSQLServer;\
  INIT=CREATE SCHEMA IF NOT EXISTS dbo\\;SET SCHEMA dbo
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

As a workaround it works fine when I use column_name=0, but it is hard to force everyone to use this convention in SQL.

Answer 1

Comments are very helpful to understand bit in SQL server, I would like to accept comment from - Jeroen Mostert as an answer.

The fact that T-SQL permits implicit conversions for BIT to string types and back for the specific values 'TRUE' and 'FALSE' is a rather obscure feature -- obscure enough that the SQL Server docs on conversion don't even cover it themselves, it's only mentioned in the docs for BIT. H2 can't really be blamed for not mimicking the behavior exactly, here, even though it is a shortcoming. Since TRUE and FALSE are not actually keywords, and T-SQL does not support true boolean expressions, there's little incentive to use this as opposed to treating BIT as the integer type it is.