apache / apache/shardingsphere

Proxy + Postgres: unsupported CommonExpressionSegment

Open
#22,830 0 comments 0 reactions 0 assignees View on GitHub
db: PostgreSQL feature: SQL federation in: proxy
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 35m
Merged PRs (30d)
326

Description

## Bug Report
### Which version of ShardingSphere did you use?
master 8e0568de

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

### Expected behavior
The select statement executes successfully.

### Actual behavior
ERROR: `Unsupported SQL operation: unsupported CommonExpressionSegment`

### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
1. Start proxy with PostgreSQL protocol;
```yaml
props:
proxy-frontend-database-protocol-type: PostgreSQL
```

3. Connect to logical database postgres;
4. Execute SQL:
```sql
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' WHEN 'g' THEN 'global index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND (not arraycontains(c.reloptions, '{deletestatus=true}')or c.reloptions is null)
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
```

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the supplied SELECT through ShardingSphere-Proxy with the PostgreSQL protocol enabled, then trace where the unsupported CommonExpressionSegment error is produced. Done means this statement executes successfully through the proxy without the unsupported-operation error.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, postgresql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.