Wrong result of information_schema.tables when sharding a postgreSQL/mySQL table.

[duerwuyi]

Bug Report

Which version of ShardingSphere did you use?

Proxy 5.5.2 with Postgres 17.2 as backend.

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

ShardingSphere-Proxy Standlone

Expected behavior

init state

CREATE SHARDING TABLE RULE t_order(
STORAGE_UNITS(ds_0,ds_1),
SHARDING_COLUMN=order_id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);

CREATE TABLE t_order (
    order_id INT NOT NULL,
    user_id INT,
    status VARCHAR(50),
    PRIMARY KEY (order_id)
);

query

select * from information_schema.tables where table_schema = 'public';

should return 4 tables, beacuse of "sharding-count"="4", and the table name should be t_order_0 to t_order_3

Actual behavior

table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action 
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------
 postgres      | public       | t_order_0  | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | public       | t_order_2  | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
(2 rows)

Reason analyze (If you can)

The query is only pushed down to 1 node(that is ds_0), so the other 2 sharding tables on ds_1 are missed.

The PREVIEW of the query:

postgres=> PREVIEW select * from information_schema.tables;
 data_source_name | actual_sql 
------------------+------------
(0 rows)

The EXPLAIN of the query:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=34.38..70.67 rows=37 width=608)
   Hash Cond: (c.reloftype = t.oid)
   ->  Hash Join  (cost=1.07..34.55 rows=37 width=141)
         Hash Cond: (c.relnamespace = nc.oid)
         ->  Seq Scan on pg_class c  (cost=0.00..32.67 rows=148 width=77)
               Filter: ((relkind = ANY ('{r,v,f,p}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
         ->  Hash  (cost=1.06..1.06 rows=1 width=68)
               ->  Seq Scan on pg_namespace nc  (cost=0.00..1.06 rows=1 width=68)
                     Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'public'::name))
   ->  Hash  (cost=25.59..25.59 rows=617 width=132)
         ->  Hash Join  (cost=1.09..25.59 rows=617 width=132)
               Hash Cond: (t.typnamespace = nt.oid)
               ->  Seq Scan on pg_type t  (cost=0.00..21.17 rows=617 width=72)
               ->  Hash  (cost=1.04..1.04 rows=4 width=68)
                     ->  Seq Scan on pg_namespace nt  (cost=0.00..1.04 rows=4 width=68)
(15 rows)

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

global.yaml:

authority:
 users:
   - user: postgres@%
     password: 123abc
     admin: true
   - user: sharding
     password: sharding
 privilege:
   type: ALL_PERMITTED
logging:
 loggers:
 - loggerName: ShardingSphere-SQL
   additivity: true
   level: INFO
   props:
     enable: true
props:
  proxy-frontend-database-protocol-type: PostgreSQL

database-sharding.yaml:

databaseName: postgres
#
dataSources:
  ds_0:
   url: jdbc:postgresql://host.docker.internal:5443/postgres
   username: postgres
   password: postgres
  ds_1:
   url: jdbc:postgresql://host.docker.internal:5444/postgres
   username: postgres
   password: postgres
  ds_2:
   url: jdbc:postgresql://host.docker.internal:5445/postgres
   username: postgres
   password: postgres
  ds_3:
   url: jdbc:postgresql://host.docker.internal:5446/postgres
   username: postgres
   password: postgres
  ds_4:
   url: jdbc:postgresql://host.docker.internal:5447/postgres
   username: postgres
   password: postgres

docker-compose.yaml(to create cluster):

version: '3.8'
services:
  shardingsphere-proxy:
    image: apache/shardingsphere-proxy:5.5.2
    container_name: shardingsphere-proxy
    environment:
      - PORT=5432
    ports:
      - "3308:5432" 
    volumes:
      - ./conf:/opt/shardingsphere-proxy/conf
      - ./logs:/opt/shardingsphere-proxy/logs
      - ./ext-lib:/opt/shardingsphere-proxy/ext-lib
    depends_on:
      - pg1
      - pg2
      - pg3
      - pg4
      - pg5

  pg1:
    image: postgres:17
    container_name: pg1
    environment:
      POSTGRES_USER: postgres
      POSTGRES_HOST_AUTH_METHOD: "trust"
    ports:
      - "5443:5432"
    volumes:
      - pg1_data:/var/lib/postgresql/data

  pg2:
    image: postgres:17
    container_name: pg2
    environment:
      POSTGRES_USER: postgres
      POSTGRES_HOST_AUTH_METHOD: "trust"
    ports:
      - "5444:5432"
    volumes:
      - pg2_data:/var/lib/postgresql/data

  pg3:
    image: postgres:17
    container_name: pg3
    environment:
      POSTGRES_USER: postgres
      POSTGRES_HOST_AUTH_METHOD: "trust"
    ports:
      - "5445:5432"
    volumes:
      - pg3_data:/var/lib/postgresql/data

  pg4:
    image: postgres:17
    container_name: pg4
    environment:
      POSTGRES_USER: postgres
      POSTGRES_HOST_AUTH_METHOD: "trust"
    ports:
      - "5446:5432"
    volumes:
      - pg4_data:/var/lib/postgresql/data

  pg5:
    image: postgres:17
    container_name: pg5
    environment:
      POSTGRES_USER: postgres
      POSTGRES_HOST_AUTH_METHOD: "trust"
    ports:
      - "5447:5432"
    volumes:
      - pg5_data:/var/lib/postgresql/data

volumes:
  pg1_data:
  pg2_data:
  pg3_data:
  pg4_data:
  pg5_data:

init state reproduction & query is shown above.

[duerwuyi]

btw, I wonder if shardingsphere-proxy provides any method to query all the names of logic tables and row names they have in a database, e.g. t_order above? Thx a lot if someone could answer!

[duerwuyi]

NOTE： \d and \dt also produced wrong results:

postgres=> \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | t_order_0 | table | postgres
 public | t_order_2 | table | postgres
(2 rows)

[duerwuyi]

the bug also exists in MySQL. Follow the document to create 4 shards of a table.

https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-proxy/distsql/usage/sharding-rule/

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();
+------------+
| TABLE_NAME |
+------------+
| t_order_0  |
| t_order_2  |
+------------+
2 rows in set (0.02 sec)

But SHOW TABLES is correct:

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| t_order              |
+----------------------+
1 row in set (0.00 sec)