sharding sphere 5.1version not support this SQL query statements

[yuwentao]

version:shardingsphere 5.1.0

SQL: SELECT old.id AS id, old.name, new.id AS quoteId, new.name FROM s_field old INNER JOIN (SELECT id, relate_field, name FROM s_field WHERE show_type = 'quote' AND verify_type = 'customer_addr' AND function_id = 1415965658951864322 AND tenant_id = 1 AND tenant_id = 1) new ON new.relate_field = old.copy_field_id WHERE old.tenant_id = 1 UNION SELECT old.id AS id, old.name, new.id AS quoteId, new.name FROM s_field old INNER JOIN (SELECT id, relate_field, name FROM s_field WHERE show_type = 'quote' AND verify_type != 'customer_addr' AND function_id = 1415965658951864322 AND tenant_id = 1 AND tenant_id = 1) new ON new.relate_field = old.id WHERE old.tenant_id = 1

Caused by: org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: java.lang.NullPointerException

The error may exist in file [E:\CoderWork\Dolphins\oa_service\dolphins-core\target\classes\mappers\FieldMapper.xml]

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: SELECT old.id AS id, old.name, new.id AS quoteId, new.name FROM s_field old INNER JOIN (SELECT id, relate_field, name FROM s_field WHERE show_type = 'quote' AND verify_type = 'customer_addr' AND function_id = ? AND tenant_id = 1 AND tenant_id = 1) new ON new.relate_field = old.copy_field_id WHERE old.tenant_id = 1 UNION SELECT old.id AS id, old.name, new.id AS quoteId, new.name FROM s_field old INNER JOIN (SELECT id, relate_field, name FROM s_field WHERE show_type = 'quote' AND verify_type != 'customer_addr' AND function_id = ? AND tenant_id = 1 AND tenant_id = 1) new ON new.relate_field = old.id WHERE old.tenant_id = 1

Cause: java.lang.NullPointerException

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at sun.reflect.GeneratedMethodAccessor171.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
    ... 171 more

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:85) at org.apache.shardingsphere.infra.binder.segment.table.TablesContext.(TablesContext.java:78) 2022-02-24 17:19:05.059 at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.(SelectStatementContext.java:105) at org.apache.shardingsphere.infra.binder.SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:133)

[strongduanmu]

@yuwentao Thank you for your feedback, can you provide your sharding configuration and table init sql?

[yuwentao]

have opened sql-federation =true: Properties props = new Properties(); props.setProperty(ConfigurationPropertyKey.SQL_FEDERATION_ENABLED.getKey(), String.valueOf(Boolean.TRUE)); props.setProperty(ConfigurationPropertyKey.SQL_SHOW.getKey(), Boolean.TRUE.toString());

[yuwentao]

@yuwentao Thank you for your feedback, can you provide your sharding configuration and table init sql?

if I need to modify my sql Statment about "union"?

[yuwentao]

https://shardingsphere.apache.org/document/5.1.0/cn/features/sharding/use-norms/sql/ Experimental supported SQL: SELECT col1, col2 FROM tbl_name UNION SELECT col1, col2 FROM tbl_name |   SELECT col1, col2 FROM tbl_name UNION ALL SELECT col1, col2 FROM tbl_name

[anonymous-lj]

I also encountered similar problems, which should be caused by union

[pengzhizhuo]

I also encountered similar problems

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.

[RaigorJiang]

This issue has been inactive for a long time, and ShardingSphere has released several new versions, so I will close it. Please try the latest version, and if the problem reproduces, please reopen it or submit a new one.