Issue 1:
Bug Report

I got below error when I use a multi-level nested join, like this

select order_item_2019.* from order_item_2019
inner join
(select id from (select min(id) as id from order_item_2019 where user_id = 12345678 and order_status = 0) t1
union (select id from (select min(id) as id from order_item_2019 where abc_user_id = 12345678 and order_status = 0) t2) tmp
on order_item_2019.id = tmp.id order by id asc limit 1

It should be noted that 'order_item_2019 is not a shard table, just single table.

System error - Throwable org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: java.lang.NullPointerException

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)

at com.sun.proxy.$Proxy377.selectOne(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:93)

at com.sun.proxy.$Proxy434.selectOrderByUserId(Unknown Source)

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:750)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.cache.interceptor.CacheInterceptor.lambda$invoke$0(CacheInterceptor.java:53)

at org.springframework.cache.interceptor.CacheAspectSupport.invokeOperation(CacheAspectSupport.java:365)

at org.springframework.cache.interceptor.CacheAspectSupport.execute(CacheAspectSupport.java:420)

at org.springframework.cache.interceptor.CacheAspectSupport.execute(CacheAspectSupport.java:345)

at org.springframework.cache.interceptor.CacheInterceptor.invoke(CacheInterceptor.java:61)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)

at java.base/java.lang.Thread.run(Thread.java:829)

Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: java.lang.NullPointerException

at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:97)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)

at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)

at jdk.internal.reflect.GeneratedMethodAccessor755.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.base/java.lang.reflect.Method.invoke(Method.java:566)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)

Caused by: java.lang.NullPointerException

at org.apache.shardingsphere.infra.binder.segment.select.subquery.engine.SubqueryTableContextEngine.createSubqueryTableContexts(SubqueryTableContextEngine.java:43)

at org.apache.shardingsphere.infra.binder.segment.table.TablesContext.createSubqueryTables(TablesContext.java:99)

at org.apache.shardingsphere.infra.binder.segment.table.TablesContext.(TablesContext.java:86)

at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.(SelectStatementContext.java:110)

at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.createSubqueryContexts(SelectStatementContext.java:124)

at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.(SelectStatementContext.java:109)

at org.apache.shardingsphere.infra.binder.SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:146)

at org.apache.shardingsphere.infra.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:129)

at org.apache.shardingsphere.infra.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:114)

at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:181)

at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:149)

at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:80)

at jdk.internal.reflect.GeneratedMethodAccessor745.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.base/java.lang.reflect.Method.invoke(Method.java:566)

at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)

at com.sun.proxy.$Proxy236.prepareStatement(Unknown Source)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)

at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)

Which version of ShardingSphere did you use?

5.1.1

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

ShardingSphere-JDBC

Expected behavior

No NullPointerException

Actual behavior

throw NullPointerException

Reason analyze (If you can)

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

select order_item_2019.* from order_item_2019
inner join
(select id from (select min(id) as id from order_item_2019 where user_id = 12345678 and order_status = 0) t1
union (select id from (select min(id) as id from order_item_2019 where abc_user_id = 12345678 and order_status = 0) t2) tmp
on order_item_2019.id = tmp.id order by id asc limit 1

Execute above sql with mybatis. You will get NullPointerException.'order_item_2019' is not shard table, just single table.

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

Issue 2:
Hi community,

As you know, ShardingSphere has made a lot of efforts on SQL parser and provided a great independent SQL parser to help users parse SQL.

Based on this substantial work, we plan to do query optimization to optimize the input SQLs from users and produce an optimized SQL query plan to improve query efficiency. Plus, the federated SQL query feature (Like join query from different instances) is another essential highlight for our next release. : )

We will leverage Apache Calcite, an excellent framework to implement two of the features. Currently, three main work focus are presented here.

The investigation of RBO (Rule-Based Optimization) and CBO (Cost-Based optimization) ,i.e., Hep planner engine and Volcano planner engine. (Mechanism, usage, pros and cons).

The investigation of Calcite adaptor, especially TranslatableTable API.

How to transform the parsed result of ShardingSphere to the algebra of Calcite

Call some of the optimization rules to process the relational expression

Implement Calcite adaptor to join SQLs from different instances

Combine sharding tables with Calcite adaptors.

Actually, there are plenty of works to do on this issue. We are in the investigation phase now and will seek contributors for this issue later. If you are interested in this one, please give it a watch. 😉

10th January 2021 Task Update

Hi, community,

Here is the progress update so far.

Functions

The research on the source code and API of Apache Calcite

The design plan on Apache ShardingSphere and Apache Calcite

The implementation of CalciteLogicSchemaFactory, CalciteLogicSchema, and CalciteFilterableTable

The Calcite JDBC executor

The work embedding with initialization, router, and executor with ShardingSphere

Use ShardingSphere parser instead of the Calcite parser to parse SQL (SqlNodeConverter, @guimingyue) (Q2)

Custom planner for SQL Optimization(PlannerInitializer, @guimingyue ) (Q2)

SQL Optimization for CalciteFilterableTable (CalciteExecutionSQLGenerator) (Q2)

The implement for CalciteRawExecutor (Especially ResultSetMetadata) (Q2)

The SQLRouter of ShardingSphere to route SQL to CalciteExecutor (Q2)

Unit test (Q2)

CalciteContextFactory

CalciteLogicSchemaFactory(Doing, #8965)

CalciteJDBCExecutor(Doing, #8883)

CalciteRawExecutor(TODO)

CalciteExecutionSQLGenerator(TODO)

CalciteExecutionContextGenerator

SQL Federation

Binding tables (Native)

Single table and Broadcast table (Native)

Sharding table and Broadcast table (Native)

Broadcast table and Broadcast table (Native)

Single table and Single table + UT

Single table and Sharding table + UT(Q2)

Sharding table and Sharding table + UT (Q2)

Scenario

Sharding

Sharding + ReplicaQuery (Q2)

Sharding + Encryption (Q2)

Sharding + Encryption + ReplicaQuery (Q2)