<h2 dir="auto">Bug Report</h2>
<p dir="auto">I got below error when I use a multi-level nested join, like this</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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"><pre class="notranslate"><code class="notranslate">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
</code></pre></div>
<p dir="auto">It should be noted that  'order_item_2019 is not a shard table, just single table.</p>
<p dir="auto">System error - Throwable org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: java.lang.NullPointerException<br>
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)<br>
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)<br>
at com.sun.proxy.$Proxy377.selectOne(Unknown Source)<br>
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)<br>
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)<br>
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:93)<br>
at com.sun.proxy.$Proxy434.selectOrderByUserId(Unknown Source)<br>
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)<br>
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:750)<br>
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)<br>
at org.springframework.cache.interceptor.CacheInterceptor.lambda$invoke$0(CacheInterceptor.java:53)<br>
at org.springframework.cache.interceptor.CacheAspectSupport.invokeOperation(CacheAspectSupport.java:365)<br>
at org.springframework.cache.interceptor.CacheAspectSupport.execute(CacheAspectSupport.java:420)<br>
at org.springframework.cache.interceptor.CacheAspectSupport.execute(CacheAspectSupport.java:345)<br>
at org.springframework.cache.interceptor.CacheInterceptor.invoke(CacheInterceptor.java:61)<br>
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)<br>
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)<br>
at java.base/java.lang.Thread.run(Thread.java:829)<br>
Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: java.lang.NullPointerException<br>
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:97)<br>
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)<br>
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)<br>
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)<br>
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)<br>
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)<br>
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)<br>
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)<br>
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)<br>
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)<br>
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)<br>
at jdk.internal.reflect.GeneratedMethodAccessor755.invoke(Unknown Source)<br>
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)<br>
at java.base/java.lang.reflect.Method.invoke(Method.java:566)<br>
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)<br>
Caused by: java.lang.NullPointerException<br>
at org.apache.shardingsphere.infra.binder.segment.select.subquery.engine.SubqueryTableContextEngine.createSubqueryTableContexts(SubqueryTableContextEngine.java:43)<br>
at org.apache.shardingsphere.infra.binder.segment.table.TablesContext.createSubqueryTables(TablesContext.java:99)<br>
at org.apache.shardingsphere.infra.binder.segment.table.TablesContext.(TablesContext.java:86)<br>
at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.(SelectStatementContext.java:110)<br>
at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.createSubqueryContexts(SelectStatementContext.java:124)<br>
at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.(SelectStatementContext.java:109)<br>
at org.apache.shardingsphere.infra.binder.SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:146)<br>
at org.apache.shardingsphere.infra.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:129)<br>
at org.apache.shardingsphere.infra.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:114)<br>
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:181)<br>
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:149)<br>
at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:80)<br>
at jdk.internal.reflect.GeneratedMethodAccessor745.invoke(Unknown Source)<br>
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)<br>
at java.base/java.lang.reflect.Method.invoke(Method.java:566)<br>
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)<br>
at com.sun.proxy.$Proxy236.prepareStatement(Unknown Source)<br>
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)<br>
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)</p>
<h3 dir="auto">Which version of ShardingSphere did you use?</h3>
<p dir="auto">5.1.1</p>
<h3 dir="auto">Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?</h3>
<p dir="auto">ShardingSphere-JDBC</p>
<h3 dir="auto">Expected behavior</h3>
<p dir="auto">No NullPointerException</p>
<h3 dir="auto">Actual behavior</h3>
<p dir="auto">throw NullPointerException</p>
<h3 dir="auto">Reason analyze (If you can)</h3>
<h3 dir="auto">Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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"><pre class="notranslate"><code class="notranslate">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
</code></pre></div>
<p dir="auto">Execute above sql with mybatis.  You will get NullPointerException.'order_item_2019' is not shard table, just single table.</p>
<h3 dir="auto">Example codes for reproduce this issue (such as a github link).</h3>
<p dir="auto">Hi  community,</p>
<p dir="auto">As you know, ShardingSphere has made a lot of efforts on SQL  parser and <strong>provided a great independent SQL parser</strong> to help users parse SQL.</p>
<p dir="auto">Based on this substantial work, we <strong>plan to do query optimization</strong> to optimize the input SQLs from users and produce an optimized SQL query plan to improve query efficiency. Plus, <strong>the federated SQL query feature</strong> (Like join query from different instances)  is another essential highlight for our next release. : )</p>
<p dir="auto">We will leverage <a href="https://calcite.apache.org" rel="nofollow">Apache Calcite</a>, an excellent framework to implement two of the features.  Currently, three main work focus are presented here.</p>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> The investigation of <strong>RBO</strong> (Rule-Based Optimization) and <strong>CBO</strong>  (Cost-Based optimization) ,i.e.,  Hep planner engine and Volcano planner engine.   (<strong>Mechanism, usage, pros and cons</strong>).</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> The investigation of Calcite adaptor, especially <code class="notranslate">TranslatableTable</code> API.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> How to transform the parsed result of ShardingSphere to the algebra of Calcite</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Call some of the optimization rules to process the relational expression</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Implement Calcite adaptor to join SQLs from different instances</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Combine sharding tables with Calcite adaptors.</li>
</ul>
<p dir="auto">Actually, there are plenty of works to do on this issue. We are <strong>in the investigation phase</strong> now and will seek contributors for this issue later. If you are interested in this one, please give it a watch. <g-emoji class="g-emoji" alias="wink" fallback-src="https://github.githubassets.com/images/icons/emoji/unicode/1f609.png">😉</g-emoji></p>
<hr>
<h3 dir="auto">10th January 2021 Task Update</h3>
<p dir="auto">Hi, community,</p>
<p dir="auto">Here is the progress update so far.</p>
<h3 dir="auto">Functions</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> The <strong>research</strong> on the source code and API of Apache Calcite</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> The <strong>design plan</strong> on Apache ShardingSphere and  Apache Calcite</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> The implementation of <code class="notranslate">CalciteLogicSchemaFactory</code>, <code class="notranslate">CalciteLogicSchema</code>, and <code class="notranslate">CalciteFilterableTable</code></li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> The Calcite JDBC executor</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> The <strong>work embedding with</strong> initialization, router, and executor with ShardingSphere</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> Use ShardingSphere <strong>parser</strong> instead of the Calcite parser to parse SQL (SqlNodeConverter, <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/guimingyue/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/guimingyue">@guimingyue</a>)  (Q2)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> Custom <strong>planner</strong> for SQL Optimization(PlannerInitializer, <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/guimingyue/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/guimingyue">@guimingyue</a> )  (Q2)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> SQL Optimization for <code class="notranslate">CalciteFilterableTable</code> (CalciteExecutionSQLGenerator)  (Q2)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> The implement for <code class="notranslate">CalciteRawExecutor</code> (Especially <code class="notranslate">ResultSetMetadata</code>)  (Q2)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> The <strong>SQLRouter</strong> of ShardingSphere to route SQL to <code class="notranslate">CalciteExecutor</code>  (Q2)</li>
</ul>
<h3 dir="auto">Unit test (Q2)</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> CalciteContextFactory</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> CalciteLogicSchemaFactory(Doing, <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="782754238" data-permission-text="Title is private" data-url="https://github.com/apache/shardingsphere/issues/8965" data-hovercard-type="issue" data-hovercard-url="/apache/shardingsphere/issues/8965/hovercard" href="https://github.com/apache/shardingsphere/issues/8965">#8965</a>)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> CalciteJDBCExecutor(Doing, <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="777994344" data-permission-text="Title is private" data-url="https://github.com/apache/shardingsphere/issues/8883" data-hovercard-type="issue" data-hovercard-url="/apache/shardingsphere/issues/8883/hovercard" href="https://github.com/apache/shardingsphere/issues/8883">#8883</a>)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> CalciteRawExecutor(TODO)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> CalciteExecutionSQLGenerator(TODO)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> CalciteExecutionContextGenerator</li>
</ul>
<h3 dir="auto">SQL Federation</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Binding tables (Native)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Single table and Broadcast table  (Native)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Sharding table and Broadcast table  (Native)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Broadcast table and Broadcast table  (Native)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Single table and Single table + UT</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Single table and Sharding table  + UT(Q2)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Sharding table and Sharding table  + UT (Q2)</li>
</ul>
<h3 dir="auto">Scenario</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Sharding</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> Sharding + ReplicaQuery  (Q2)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> Sharding + Encryption  (Q2)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Sharding + Encryption + ReplicaQuery  (Q2)</li>
</ul>