Got a error: UnknownColumnException: Unknown column. Maybe a bug ?

[banalite]

Question

My have a SQL kind of like this:

select SUM(IF(temp.sub_type = 2 AND temp.id IS NOT NULL, 1, 0)) as re
from (select *
      from A a
               join B b on a.s_id = b.s_id
      where a.is_del = 0
        and not EXISTS(select null from C c  join D d on c.r_id = d.r_id and c.s_id = a.s_id)
) temp

The SQL can be run successfully with normal JDBC, but after using Shardingsphere JDBC, I got one error bellow:

Caused by: org.apache.shardingsphere.infra.exception.UnknownColumnException: Unknown column 'a.s_id' in 'on clause'.
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.ColumnSegmentBinder.lambda$findInputColumnSegment$2(ColumnSegmentBinder.java:160)
        at org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.ColumnSegmentBinder.findInputColumnSegment(ColumnSegmentBinder.java:159)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.ColumnSegmentBinder.bind(ColumnSegmentBinder.java:82)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:81)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:49)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:49)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:49)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.from.impl.JoinTableSegmentBinder.bind(JoinTableSegmentBinder.java:74)
        at org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:57)
        at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:56)
        at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bindCorrelateSubquery(SelectStatementBinder.java:89)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.SubquerySegmentBinder.bind(SubquerySegmentBinder.java:45)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.ExistsSubqueryExpressionBinder.bind(ExistsSubqueryExpressionBinder.java:45)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:66)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:49)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:49)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:49)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:49)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.expression.impl.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:49)
        at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:63)
        at org.apache.shardingsphere.infra.binder.segment.where.WhereSegmentBinder.bind(WhereSegmentBinder.java:48)
        at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.lambda$bind$0(SelectStatementBinder.java:59)
        at java.util.Optional.ifPresent(Optional.java:159)
        at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:59)
        at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bindCorrelateSubquery(SelectStatementBinder.java:89)
        at org.apache.shardingsphere.infra.binder.segment.from.impl.SubqueryTableSegmentBinder.bind(SubqueryTableSegmentBinder.java:61)
        at org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:60)
        at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:56)
        at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:46)
        at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindDMLStatement(SQLBindEngine.java:100)
        at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:80)
        at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:71)
        at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:202)
        at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:167)
        at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:83)
        at sun.reflect.GeneratedMethodAccessor545.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
        at com.sun.proxy.$Proxy744.prepareStatement(Unknown Source)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87)
        at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)

Then I debugged the code, and found something maybe wrong in the red circle bellow :

And I don't understand why passing 'Collections.emptyMap()' into method 'result.setCondition' here:

result.setCondition(ExpressionSegmentBinder.bind(segment.getCondition(), SegmentType.JOIN_ON, statementBinderContext, tableBinderContexts, Collections.emptyMap()));

Is this a bug or my SQL is wrong ?

[strongduanmu]

Hi @banalite, thank you for your feedback. Can you provide your version?

[banalite]

Hi @banalite, thank you for your feedback. Can you provide your version?

The version is 5.4.1

[strongduanmu]

Can you try the latest 5.5.0?

[banalite]

Can you try the latest 5.5.0?

Ok, I'll try later, and feedback then.

[banalite]

Hi @strongduanmu , I’ve upgraded to 5.5.0, but still got same problem.

[strongduanmu]

@banalite Thank you for your feedback, I will take a look for this exception.