How do I query a single table

peixingzhe commented 1 year ago

Bug Report

Which version of ShardingSphere did you use?

5.3.2

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

ShardingSphere-Proxy

Expected behavior

proxy routing data source error when I query single table.

Actual behavior

The proxy routes correctly to the data source where the single table exist.

Reason analyze (If you can)

I don't known.

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

single table cofig

> SHOW DEFAULT SINGLE TABLE STORAGE UNIT;
 storage_unit_name 
-------------------
 RANDOM

> SHOW SINGLE TABLES LIKE 't_app_mon%';
    table_name     | storage_unit_name 
-------------------+-------------------
 t_app_mon_p202112 | ds_8
 t_app_mon_p203507 | ds_8
 t_app_mon_p203602 | ds_8
(3 rows)

query a single table

> select * from t_app_mon;
ERROR:  relation "t_app_mon" does not exist
LINE 1: select * from t_app_mon;

> select * from xxx.t_app_mon;
ERROR:  relation "xxx.t_app_mon" does not exist
LINE 1: select * from xxx.t_app_mon;

> select * from t_app_mon_p202112;
ERROR:  relation "t_app_mon_p202112" does not exist
LINE 1: select * from t_app_mon_p202112;

> select * from xxx.t_app_mon_p202112;
 id | name
-------+------------
(0 rows)

proxy log

2023-06-25 15:36:20,927 [Connection-2-ThreadExecutor] INFO  ShardingSphere-SQL - Logic SQL: select * from t_app_mon; 
2023-06-25 15:36:20,927 [Connection-2-ThreadExecutor] INFO  ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_app_mon; 
2023-06-25 15:36:20,928 [Connection-2-ThreadExecutor] ERROR o.a.s.p.f.c.CommandExecutorTask - Exception occur:  
org.postgresql.util.PSQLException: ERROR: relation "t_app_mon" does not exist
...

2023-06-25 15:36:55,165 [Connection-2-ThreadExecutor] INFO  ShardingSphere-SQL - Logic SQL: select * from xxx.t_app_mon; 
2023-06-25 15:36:55,165 [Connection-2-ThreadExecutor] INFO  ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from xxx.t_app_mon; 
2023-06-25 15:36:55,166 [Connection-2-ThreadExecutor] ERROR o.a.s.p.f.c.CommandExecutorTask - Exception occur:  
org.postgresql.util.PSQLException: ERROR: relation "xxx.t_app_mon" does not exist
...

2023-06-25 15:37:55,747 [Connection-2-ThreadExecutor] INFO  ShardingSphere-SQL - Logic SQL: select * from t_app_mon_p202112; 
2023-06-25 15:37:55,747 [Connection-2-ThreadExecutor] INFO  ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_app_mon_p202112; 
2023-06-25 15:37:55,748 [Connection-2-ThreadExecutor] ERROR o.a.s.p.f.c.CommandExecutorTask - Exception occur:  
org.postgresql.util.PSQLException: ERROR: relation "t_app_mon_p202112" does not exist
...

2023-06-25 15:38:19,606 [Connection-2-ThreadExecutor] INFO  ShardingSphere-SQL - Logic SQL: select * from xxx.t_app_mon_p202112; 
2023-06-25 15:38:19,606 [Connection-2-ThreadExecutor] INFO  ShardingSphere-SQL - Actual SQL: ds_8 ::: select * from xxx.t_app_mon_p202112;

The datasource is PostgreSQL.
As shown above, the proxy did not route to the correct data source when querying a single table.
As you can see, t_app_mon is a partitioned table, I can't query this table directly, I have to query some partition of this table directly, and I have to prefix it with a schema.

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

RaigorJiang commented 1 year ago

Hi @peixingzhe ShardingSphere currently does not support partition tables, and public schema is used by default in PG.

peixingzhe commented 1 year ago

Hi @peixingzhe ShardingSphere currently does not support partition tables, and public schema is used by default in PG.

But why can I query a single table(in custom schema and is a partitioned table) without specifying a schema when I put it in the first (ds_0) data source?

RaigorJiang commented 1 year ago

I think it needs to be investigated. Hi @strongduanmu , do you have some info?

apache / shardingsphere