Throws TableNotFoundException when query the actual table directly.

[gxgmy521]

Bug Report

For English only, other languages will not accept.

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 anymore 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?

5.5.0

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

ShardingSphere-JDBC

Expected behavior

I use the sharding datasource to direct access the actual table with the help of single table， here is the config example

rules:
- !SHARDING
    tables:
      t_order:
        actualDataNodes: ds_1.t_order_$->{0..1}
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: t_order_inline
- !SINGLE
    tables:
      - ds_1.t_order_1

I use the single table "ds_1.t_order1" In the SQL Due to the Sharding JDBC have some limitations (https://shardingsphere.apache.org/document/current/en/features/sharding/limitation/#sub-query-1)(for example do not support sub query without sharding key)

 SELECT * FROM t_order_1;

In version 5.1.2 it works well , however when I use 5.5.0 version, it throws TableNotFoundException. My problem is:

1. Is this a bug or new feature?
2. What is the suggest solution when encountering unsupported SQL?

Actual behavior

In 5.5.0 version, it throw TableNotFoundException

Caused by: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 't_order_1' does not exist.
	at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.lambda$checkTableExists$4(SimpleTableSegmentBinder.java:151)
	at org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41)
	at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.checkTableExists(SimpleTableSegmentBinder.java:148)
	at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.bind(SimpleTableSegmentBinder.java:84)
	at org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:55)
	at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.lambda$bind$1(SelectStatementBinder.java:60)
	at java.base/java.util.Optional.map(Optional.java:260)
	at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:60)
	at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:48)
	at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindDMLStatement(SQLBindEngine.java:82)
	at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:72)
	at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:63)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:207)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:172)
	at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:94)
	at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:316)
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
	... 23 more

Reason analyze (If you can)

The ShardingSphereDatabase class only save the logic table's metadata not the actual table ,so when I use the actual table directly,It can not find the table metadata and throw the exception.

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).

[strongduanmu]

@gxgmy521 t_order_1 is part of t_order, and ShardingSphere does not allow them to be registered as two tables. If you need to query t_order_1, you can consider using SQL Hint pass-through, for specific reference: https://shardingsphere.apache.org/document/current/en/user-manual/common-config/sql-hint/#datasource-pass-through.

[gxgmy521]

@gxgmy521 t_order_1 is part of t_order, and ShardingSphere does not allow them to be registered as two tables. If you need to query t_order_1, you can consider using SQL Hint pass-through, for specific reference: https://shardingsphere.apache.org/document/current/en/user-manual/common-config/sql-hint/#datasource-pass-through.

My SQL is complex ( include multi-layer sub-query and need table shards) that ShardingSphere can not parse and rewrite, does the SQL Hint pass-through can work in this situation ？

[terrymanu]

What is your database config?

[gxgmy521]

What is your database config?

My real database config is complex, this is a demo config:

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: org.h2.Driver
    jdbcUrl: jdbc:h2:file:~/h2/demo_ds;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MySQL
    username: root
    password: root
    maxPoolSize: 10
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: org.h2.Driver
    jdbcUrl: jdbc:h2:file:~/h2/demo_ds0;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MySQL
    username: root
    password: root
    maxPoolSize: 10
  ds_2:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: org.h2.Driver
    jdbcUrl: jdbc:h2:file:~/h2/demo_ds1;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MySQL
    username: root
    password: root
    maxPoolSize: 10

[gxgmy521]

In vesion 5.1.2, when encountering an SQL that ShardingSphere cannot support, we use the ‘Single Table’ to bypass the problem. However, in the 5.5.0 version, this solution has been prohibited. What should I do?

[terrymanu]

bypass just hides the problem which is not good practice.

[gxgmy521]

bypass just hides the problem which is not good practice.

What is the good practice?

[terrymanu]

Using logic table name