The query entity throws an exception when a boolean property exists in the entity

[Saisimon]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response more than 7 days and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

4.0.0-RC2-SNAPSHOT，4.0.0-RC1 version work well

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-JDBC

Expected behavior

Work well

Actual behavior

throw java.lang.ClassCastException: [B cannot be cast to java.lang.Boolean

Reason analyze (If you can)

MySQL column type is BIT, MergeResultSet value is an byte array, can not convert to boolean.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

pom.xml

        <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <java.version>1.8</java.version>
        <!-- <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version> -->
        <sharding-sphere.version>4.0.0-RC2-SNAPSHOT</sharding-sphere.version>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.1.RELEASE</version>
        <relativePath />
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>

application.yml

spring:
  jpa:
    show-sql: true
    generate-ddl: true
  shardingsphere:
    datasource:
      names: d0,d1
      d0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://dbserver:3306/d0?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password:
      d1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://dbserver:3306/d1?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password:
    props:
      sql:
        show: true
    sharding:
      default-data-source-name: d0
      tables:
        demo:
          actual-data-nodes: d$->{0..1}.demo
          database-strategy:
            inline:
              sharding-column: id
              algorithm-expression: d$->{id % 2}
          key-generator:
            column: id
            type: SNOWFLAKE

Demo.java

@Entity
@Table(name="demo")
@Setter
@Getter
@ToString
public class Demo {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;

    @Column
    private boolean test;

}

DemoRepository.java

@Repository
public interface DemoRepository extends JpaRepository<Demo, Long> {

}

DemoApplication.java

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        try (ConfigurableApplicationContext applicationContext = SpringApplication.run(DemoApplication.class, args)) {
            DemoRepository demoRepository = applicationContext.getBean(DemoRepository.class);
            Demo demo = new Demo();
            demo.setTest(true);
            demoRepository.saveAndFlush(demo);
            demoRepository.findById(demo.getId());
            demoRepository.deleteAll();
        }
    }

}

Log

Exception in thread "main" org.springframework.dao.InvalidDataAccessApiUsageException: [B cannot be cast to java.lang.Boolean; nested exception is java.lang.IllegalArgumentException: [B cannot be cast to java.lang.Boolean
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:367)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:227)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy83.findById(Unknown Source)
    at net.saisimon.demo.DemoApplication.main(DemoApplication.java:19)
Caused by: java.lang.IllegalArgumentException: [B cannot be cast to java.lang.Boolean
    at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3427)
    at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3375)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:304)
    at com.sun.proxy.$Proxy81.find(Unknown Source)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findById(SimpleJpaRepository.java:235)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:377)
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:629)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:593)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    ... 10 more
Caused by: java.lang.ClassCastException: [B cannot be cast to java.lang.Boolean
    at org.apache.shardingsphere.shardingjdbc.jdbc.core.resultset.ShardingResultSet.getBoolean(ShardingResultSet.java:71)
    at org.hibernate.type.descriptor.sql.BooleanTypeDescriptor$2.doExtract(BooleanTypeDescriptor.java:59)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:261)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:247)
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:333)
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2854)
    at org.hibernate.loader.plan.exec.process.internal.EntityReferenceInitializerImpl.loadFromResultSet(EntityReferenceInitializerImpl.java:305)
    at org.hibernate.loader.plan.exec.process.internal.EntityReferenceInitializerImpl.hydrateEntityState(EntityReferenceInitializerImpl.java:233)
    at org.hibernate.loader.plan.exec.process.internal.AbstractRowReader.readRow(AbstractRowReader.java:103)
    at org.hibernate.loader.plan.exec.internal.EntityLoadQueryDetails$EntityLoaderRowReader.readRow(EntityLoadQueryDetails.java:241)
    at org.hibernate.loader.plan.exec.process.internal.ResultSetProcessorImpl.extractResults(ResultSetProcessorImpl.java:122)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:122)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
    at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:167)
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4107)
    at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:508)
    at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:478)
    at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:219)
    at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:278)
    at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:121)
    at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:89)
    at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1239)
    at org.hibernate.internal.SessionImpl.access$1900(SessionImpl.java:203)
    at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.doLoad(SessionImpl.java:2803)
    at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2777)
    at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3406)
    ... 35 more

Example codes for reproduce this issue (such as a github link).

[terrymanu]

How to deal with if byte[]' size > 1?

[Saisimon]

The ResultSetUtil#convertByteArrayValue method references to MySQL JDBC driver 5.1 version, it just checks first of byte array.In 8.0 version, it changes to byte array -> long -> boolean, you can find the implement in here, I’m not sure which way is better, what do you think? BTW, this method has only been verified in the MySQL database, I don't have an environment for other databases.

[terrymanu]

For other database it is a problem, could you verify for other database too?

[Saisimon]

OK. I'll verify it, when I have time.

[terrymanu]

Because the pr #2607 is not sure for all databases of jdbc, it is a risk here, so may I revert this pr until this issue solve totally?

[Saisimon]

it`s ok

[terrymanu]

ok, revert it and hope you solve totally later.

[Saisimon]

@terrymanu this issue should assigns to @tuohai666 , because #2467 causes this issue.

[tuohai666]

If you can't fix, just unassign this issue from you.

[tuohai666]

As we know, MySQL does not have built-in Boolean type. However, it uses TINYINT(1) instead. To make it more convenient, MySQL provides BOOLEAN or BOOL as the synonym of TINYINT(1) . In MySQL, zero is considered as false, and non-zero value is considered as true.

Convert BIT to Boolean is abuse, BIT is for binary value, not boolean value. I suggest that you change the column type from BIT to TINYINT.

[Saisimon]

Convert BIT to Boolean is abuse, BIT is for binary value, not boolean value. I suggest that you change the column type from BIT to TINYINT.

change to TINYINT(1) also throws java.lang.ClassCastException: [B cannot be cast to java.lang.Boolean change to TINYINT(2) throws org.apache.shardingsphere.core.exception.ShardingException: Unsupported data type:boolean

[tuohai666]

Get it. Can you help to test whether this change works?

[tuohai666]

If there's no problem, you can submit a pull request instead of the previous one. This change will not lead to side effect.

[Saisimon]

Compilation failure.

case "boolean":
    return 0 != number.longValue()

it only fixes the case of tinyint(2).

[tuohai666]

Sorry, codes should like this:

case "boolean":
    return 0 != number.byteValue()

Don't need no support tinyint(2), only support tinyint(1).

[Saisimon]

no fix, because mysql driver treats tinyint(1) as bit type, the value always returns a byte array, never jumps to convertNumberValue method.

[tuohai666]

The previous pull requst convert byte[] to boolean, but have been reverted. Are there any other solutions? What do you think? If the value can't converted in sharding-jdbc finally, you may convert it in your service codes without getBoolean().

[Saisimon]

It was restored because it only works with mysql and sql server and does not support postgresql. In postgresql, 116 is true and the other numbers are false. I think there are two ways to solve this problem:

1. QueryResultUtil#getValueByColumnType() adds a convertType class param, which is converted directly to the correct field type here.
2. Provide different conversion methods according to different databases.

But both ways will make a lot of changes.

[sunbufu]

In my opinion, the purpose of JDBC is resolve different databases's compatibility. so ResultSet defined some method like getBytes(), getBoolean() and etc to help us get object with expected java type. ShardingSphere work on dataSource, and defined ResultSet too, like ShardingResultSet and EncryptResultSet. but ShardingSphere use database column type in place of expected java type. There are some codes in StreamQueryResult, we can see getValue() do nothing with final Class<?> type.

@Override
public Object getValue(final int columnIndex, final Class<?> type) throws SQLException {
    return decrypt(columnIndex, QueryResultUtil.getValue(resultSet, columnIndex));
}

And in QueryResultUtil, getValueByColumnType() get database column type from resultSet. metaData and use it for getXXX() selection.

public static Object getValueByColumnType(final ResultSet resultSet, final int columnIndex) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        switch (metaData.getColumnType(columnIndex)) {
            case Types.BIT:
                return resultSet.getBytes(columnIndex);
            case Types.BOOLEAN:
                return resultSet.getBoolean(columnIndex);
            case Types.TINYINT:
                return resultSet.getByte(columnIndex);
            ...
        }
    }

Usually it's okay, but when we use mysql and call some special methods such as getBoolean(), our expected java type is boolean, database column type is not boolean. so I think we should pass on the expected java type, and use it for getXXX() selection. Can I send a PR ?

[tuohai666]

@Saisimon Do you want to have a try to resolve this problem completely?

It was restored because it only works with mysql and sql server and does not support postgresql. In postgresql, 116 is true and the other numbers are false. I think there are two ways to solve this problem:

1. QueryResultUtil#getValueByColumnType() adds a convertType class param, which is converted directly to the correct field type here.
2. Provide different conversion methods according to different databases.

But both ways will make a lot of changes.

[Saisimon]

OK