ShardingSphere Proxy failed to execute the Postgresql \l command

[xuxiang-hub]

Bug Report

When making a client connection to the proxy database, almost all clients will send queries to query all data, but Proxy does not support such queries

proxy also cannot execute postgresql command “\l”

For Instance：
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
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?

ShardingSphere Proxy v5.5.0

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

ShardingSphere-Proxy

Expected behavior

pgsql=> \l
ERROR: SQL federation does not support SQL 'SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;'.

Actual behavior

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+---------------------
postgres | xuxiang | UTF8 | C | C |
template0 | xuxiang | UTF8 | C | C | =c/xuxiang +
| | | | | xuxiang=CTc/xuxiang
template1 | xuxiang | UTF8 | C | C | =c/xuxiang +
| | | | | xuxiang=CTc/xuxiang
test_db | postgres | UTF8 | C | C |
(4 rows

Reason analyze (If you can)

prxoy is unable to perform queries similar to d.datdba

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

1、Create a pgsql proxy database
2、Enter the pgsql proxy database
3、Execute commands “\l”
4、Report Failure
pgsql=> \l
ERROR: SQL federation does not support SQL 'SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;'.

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

[zzyReal666]

\l not a sql.It is used only on the psql command line.

[zzyReal666]

This SQL can achieve a similar effect:
SELECT datname AS "Database Name", pg_catalog.pg_get_userbyid(datdba) AS "Owner", pg_catalog.pg_encoding_to_char(encoding) AS "Encoding", datcollate AS "Collate", datctype AS "Ctype", datacl AS "Access Privileges" FROM pg_catalog.pg_database ORDER BY datname;