Skip to content

MSSQL bit wrongly mapped to boolean value TRUE #908

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
Drezir opened this issue Jan 14, 2021 · 5 comments
Closed

MSSQL bit wrongly mapped to boolean value TRUE #908

Drezir opened this issue Jan 14, 2021 · 5 comments
Assignees
@schauder
Labels
type: bug A general bug
Milestone
2.3 M1 (2021.1.0)

Comments

@Drezir
Copy link

Drezir commented Jan 14, 2021 •
edited
Loading

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
@spring-projects-issues spring-projects-issues added the status: waiting-for-triage An issue we've not yet triaged label Jan 14, 2021
@Drezir
Copy link
Author

Drezir commented Jan 14, 2021

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 schauder self-assigned this Jan 15, 2021
@schauder schauder added type: bug A general bug and removed status: waiting-for-triage An issue we've not yet triaged labels Jan 18, 2021
@schauder
Copy link
Contributor

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

@manousos
Copy link
Contributor

manousos commented Mar 10, 2021 •
edited
Loading

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 ?

@manousos manousos mentioned this issue Mar 10, 2021
Closed
@schauder
Copy link
Contributor

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
Copy link
Contributor

All integration tests are green

schauder added a commit that referenced this issue May 26, 2021
@schauder
Fix derived queries with boolean literals.
46826bc 
`IsTrue` and `IsFalse` queries no longer use a literal in the query, but a bind parameter.
This allows Spring Data JDBC or the JDBC driver to convert the passed boolean value to whatever is required in the database.

For Oracle converter where added to support storing and loading booleans as NUMBER(1,0) where 0 is false and everything else is true.

Closes #908
@schauder schauder mentioned this issue May 26, 2021
Closed
schauder added a commit that referenced this issue May 31, 2021
@schauder
Fix derived queries with boolean literals.
1a581b2 
`IsTrue` and `IsFalse` queries no longer use a literal in the query, but a bind parameter.
This allows Spring Data JDBC or the JDBC driver to convert the passed boolean value to whatever is required in the database.

For Oracle converter where added to support storing and loading booleans as NUMBER(1,0) where 0 is false and everything else is true.

Closes #908
Original pull request #983
schauder added a commit that referenced this issue Jun 2, 2021
@schauder
Fix derived queries with boolean literals.
ea1882d 
`IsTrue` and `IsFalse` queries no longer use a literal in the query, but a bind parameter.
This allows Spring Data JDBC or the JDBC driver to convert the passed boolean value to whatever is required in the database.

For Oracle converter where added to support storing and loading booleans as NUMBER(1,0) where 0 is false and everything else is true.

Closes #908
Original pull request #983
mp911de pushed a commit that referenced this issue Jun 10, 2021
@schauder @mp911de
Improving the documentation.
c3e21cc 
See #908
Original pull request #983
@mp911de mp911de added this to the 2.3 M1 (2021.1.0) milestone Jun 10, 2021
@schauder schauder mentioned this issue Nov 17, 2021
Closed
@mp911de mp911de mentioned this issue Jan 17, 2022
Closed
@mp911de mp911de mentioned this issue Mar 1, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@schauder schauder

Labels
type: bug A general bug
Projects
None yet
Milestone
2.3 M1 (2021.1.0)
5 participants
@schauder @manousos @mp911de @Drezir @spring-projects-issues