MSSQL bit wrongly mapped to boolean value TRUE

[Drezir]

Hi, I have this entity:

@Data
@Table("MasterSeller")
public class MasterSeller {

    @Id
    @Column("Id")
    private UUID id;
    @Column("Name")
    private String sellerName;
    @Column("Code")
    private String sellerCode;
    @Column("IsActive")
    private boolean active;
}

with this dao:
Optional<MasterSeller> findBySellerNameAndSellerCodeAndActiveIsTrue(String sellerName, String sellerCode);

Custom conversions do not work for me.
Exception is:

2021-01-14 13:57:10.618 ERROR 67775 --- [NathanDmsTopic]] route1                                   : PreparedStatementCallback; bad SQL grammar [SELECT "MasterSeller"."Id" AS "Id", "MasterSeller"."IsActive" AS "IsActive", "MasterSeller"."Code" AS "Code", "MasterSeller"."Name" AS "Name" FROM "MasterSeller" WHERE "MasterSeller"."Name" = ? AND ("MasterSeller"."Code" = ?) AND ("MasterSeller"."IsActive" = TRUE)]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'TRUE'.
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT "MasterSeller"."Id" AS "Id", "MasterSeller"."IsActive" AS "IsActive", "MasterSeller"."Code" AS "Code", "MasterSeller"."Name" AS "Name" FROM "MasterSeller" WHERE "MasterSeller"."Name" = ? AND ("MasterSeller"."Code" = ?) AND ("MasterSeller"."IsActive" = TRUE)]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'TRUE'.
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:236)
	at org.springframework.data.jdbc.repository.query.AbstractJdbcQuery.lambda$singleObjectQuery$1(AbstractJdbcQuery.java:115)
	at org.springframework.data.jdbc.repository.query.PartTreeJdbcQuery.execute(PartTreeJdbcQuery.java:98)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy104.findBySellerNameAndSellerCodeAndActiveIsTrue(Unknown Source)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:564)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy104.findBySellerNameAndSellerCodeAndActiveIsTrue(Unknown Source)
	at cz.multima.nathan.integration.consumer.service.MasterSellerService.findMasterSeller(MasterSellerService.java:29)
	at cz.multima.nathan.integration.consumer.service.MasterSellerService.setMasterSeller(MasterSellerService.java:22)
	at cz.multima.nathan.integration.consumer.service.MasterSellerService$$FastClassBySpringCGLIB$$bd0ec4ff.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)
	at cz.multima.nathan.integration.consumer.service.MasterSellerService$$EnhancerBySpringCGLIB$$d5077b1c.setMasterSeller(<generated>)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:564)
	at org.apache.camel.support.ObjectHelper.invokeMethodSafe(ObjectHelper.java:208)
	at org.apache.camel.component.bean.MethodInfo.invoke(MethodInfo.java:425)
	at org.apache.camel.component.bean.MethodInfo$1.doProceed(MethodInfo.java:247)
	at org.apache.camel.component.bean.MethodInfo$1.proceed(MethodInfo.java:217)
	at org.apache.camel.component.bean.AbstractBeanProcessor.process(AbstractBeanProcessor.java:154)
	at org.apache.camel.component.bean.BeanProcessor.process(BeanProcessor.java:56)
	at org.apache.camel.processor.errorhandler.RedeliveryErrorHandler$RedeliveryTask.doRun(RedeliveryErrorHandler.java:723)
	at org.apache.camel.processor.errorhandler.RedeliveryErrorHandler$RedeliveryTask.run(RedeliveryErrorHandler.java:632)
	at org.apache.camel.impl.engine.DefaultReactiveExecutor$Worker.schedule(DefaultReactiveExecutor.java:148)
	at org.apache.camel.impl.engine.DefaultReactiveExecutor.scheduleMain(DefaultReactiveExecutor.java:60)
	at org.apache.camel.processor.Pipeline.process(Pipeline.java:147)
	at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:287)
	at org.apache.camel.impl.engine.DefaultAsyncProcessorAwaitManager.process(DefaultAsyncProcessorAwaitManager.java:83)
	at org.apache.camel.support.AsyncProcessorSupport.process(AsyncProcessorSupport.java:41)
	at org.apache.camel.component.kafka.KafkaConsumer$KafkaFetchRecords.doRun(KafkaConsumer.java:357)
	at org.apache.camel.component.kafka.KafkaConsumer$KafkaFetchRecords.run(KafkaConsumer.java:222)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
	at java.base/java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:264)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
	at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'TRUE'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
	... 62 more

[Drezir]

Update: the conversion actually works but auto generated query does not. I thing that isTrue suffix should be automatically mapped to 0 or 1 in case of Mssql BIT type

[schauder]

When implementing this we should double check that it works consistently with R2DBC

[manousos]

The TRUE or FALSE value comes from org.springframework.data.relational.core.sql.BooleanLiteral#toString().
If replace with value 1 or 0 will be a problem for PostgreSQL.

Any idea ?

[schauder]

You can run all the integration tests and see if changes work for all databases: https://github.com/spring-projects/spring-data-jdbc#running-integration-tests

[manousos]

All integration tests are green