Issue 1:
Bug Report

startup fail when using mysql-connector-java 8.0.16

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'encryptDataSource' defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'encryptDataSource' threw exception; nested exception is java.lang.NumberFormatException: For input string: "YES"
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:587) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1250) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1099) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:251) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1138) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1065) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:815) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:721) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
... 41 common frames omitted
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'encryptDataSource' threw exception; nested exception is java.lang.NumberFormatException: For input string: "YES"
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:579) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
... 54 common frames omitted
Caused by: java.lang.NumberFormatException: For input string: "YES"
at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2043) ~[na:1.8.0_171]
at sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110) ~[na:1.8.0_171]
at java.lang.Double.parseDouble(Double.java:538) ~[na:1.8.0_171]
at com.mysql.cj.protocol.a.MysqlTextValueDecoder.getDouble(MysqlTextValueDecoder.java:238) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.result.BooleanValueFactory.createFromBytes(BooleanValueFactory.java:113) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.result.BooleanValueFactory.createFromBytes(BooleanValueFactory.java:46) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeByteArray(MysqlTextValueDecoder.java:132) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:196) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:241) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.jdbc.result.ResultSetImpl.getBoolean(ResultSetImpl.java:678) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.jdbc.result.ResultSetImpl.getBoolean(ResultSetImpl.java:683) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at org.apache.commons.dbcp2.DelegatingResultSet.getBoolean(DelegatingResultSet.java:368) ~[commons-dbcp2-2.7.0.jar:2.7.0]
at org.apache.commons.dbcp2.DelegatingResultSet.getBoolean(DelegatingResultSet.java:368) ~[commons-dbcp2-2.7.0.jar:2.7.0]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.EncryptRuntimeContext.getColumnMetaDataList(EncryptRuntimeContext.java:95) ~[sharding-jdbc-core-4.0.0-RC3-SNAPSHOT.jar:4.0.0-RC3-SNAPSHOT]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.EncryptRuntimeContext.createEncryptTableMetas(EncryptRuntimeContext.java:72) ~[sharding-jdbc-core-4.0.0-RC3-SNAPSHOT.jar:4.0.0-RC3-SNAPSHOT]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.context.EncryptRuntimeContext.<init>(EncryptRuntimeContext.java:64) ~[sharding-jdbc-core-4.0.0-RC3-SNAPSHOT.jar:4.0.0-RC3-SNAPSHOT]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.EncryptDataSource.<init>(EncryptDataSource.java:42) ~[sharding-jdbc-core-4.0.0-RC3-SNAPSHOT.jar:4.0.0-RC3-SNAPSHOT]
at org.apache.shardingsphere.shardingjdbc.api.EncryptDataSourceFactory.createDataSource(EncryptDataSourceFactory.java:48) ~[sharding-jdbc-core-4.0.0-RC3-SNAPSHOT.jar:4.0.0-RC3-SNAPSHOT]
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration.encryptDataSource(SpringBootConfiguration.java:123) ~[sharding-jdbc-spring-boot-starter-4.0.0-RC3-SNAPSHOT.jar:4.0.0-RC3-SNAPSHOT]
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$2b2b3348.CGLIB$encryptDataSource$1(<generated>) ~[sharding-jdbc-spring-boot-starter-4.0.0-RC3-SNAPSHOT.jar:4.0.0-RC3-SNAPSHOT]
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$2b2b3348$$FastClassBySpringCGLIB$$842d7709.invoke(<generated>) ~[sharding-jdbc-spring-boot-starter-4.0.0-RC3-SNAPSHOT.jar:4.0.0-RC3-SNAPSHOT]
at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228) ~[spring-core-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:361) ~[spring-context-5.0.4.RELEASE.jar:5.0.4.RELEASE]
at org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$2b2b3348.encryptDataSource(<generated>) ~[sharding-jdbc-spring-boot-starter-4.0.0-RC3-SNAPSHOT.jar:4.0.0-RC3-SNAPSHOT]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
... 55 common frames omitted

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-RC3-SNAPSHOT

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

Sharding-JDBC

Expected behavior

startup successfully

Actual behavior

startup fail

Reason analyze (If you can)

different implementation on resultSet.getBoolean

in mysql 5.1.40

com.mysql.jdbc.ResultSetImpl#getBooleanFromString

private final boolean getBooleanFromString(String stringVal) throws SQLException {
if (stringVal != null && stringVal.length() > 0) {
int c = Character.toLowerCase(stringVal.charAt(0));
return c == 't' || c == 'y' || c == '1' || stringVal.equals("-1");
} else {
return false;
}
}

in mysql 8.0.16

com.mysql.cj.result.BooleanValueFactory#createFromBytes

@Override
public Boolean createFromBytes(byte[] bytes, int offset, int length, Field f) {
...

if (s.equalsIgnoreCase("Y") || s.equalsIgnoreCase("true")) {
return createFromLong(1);
} else if (s.equalsIgnoreCase("N") || s.equalsIgnoreCase("false")) {
return createFromLong(0);
} else if (s.contains("e") || s.contains("E") || s.matches("-?(\\d+)?\\.\\d+")) {
// floating point
return createFromDouble(MysqlTextValueDecoder.getDouble(newBytes, 0, newBytes.length));
} else if (s.matches("-?\\d+")) {
// integer
if (s.charAt(0) == '-' // TODO shouldn't we check the length as well?
|| length <= (MysqlTextValueDecoder.MAX_SIGNED_LONG_LEN - 1) && newBytes[0] >= '0' && newBytes[0] <= '8') {
return createFromLong(MysqlTextValueDecoder.getLong(newBytes, 0, newBytes.length));
}
return createFromBigInteger(MysqlTextValueDecoder.getBigInteger(newBytes, 0, newBytes.length));
}
throw new DataConversionException(Messages.getString("ResultSet.UnableToInterpretString", new Object[] { s }));
}

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

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

Issue 2:
Bug Report

Which version of Sharding-Sphere did you use?

3.0.0

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

Sharding-JDBC

Expected behavior

SELECT

*

FROM

fin_material m

WHERE m.PROJ_ID = '10'

AND m.ID IN

(SELECT

c.MATERIAL_ID

FROM

fin_material_customer c

WHERE c.PROJ_ID = '10'

AND c.CUSTOMER_ID = '1');

Actual behavior

Cause: io.shardingsphere.core.parsing.parser.exception.SQLParsingException: SQL syntax error, expected token is 'RIGHT_PAREN', actual token is 'IDENTIFIER', literals is 'c'.

Reason analyze (If you can)

Subquery sub-database error

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

SQL to execute

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