duerwuyi opened a new issue, #37770:
URL: https://github.com/apache/shardingsphere/issues/37770
### Which version of ShardingSphere did you use?
shardingsphere-proxy 5.5.2
postgres 17.6 as backend
### Expected behavior
The following query should not fail, or at least return an error for out of
capability.
```sql
select
1 as c_0
from
t17 as ref_0 --- can be reproduced by local table t8
where not ((ref_0.c59) < (
select
ref_1.c36 as c_0
from
t23 as ref_1 join t22 as ref_2
on(ref_1.colocated_key = ref_2.colocated_key)
));
```
### Actual behavior
the query returns `relation t23 does not exist`.
### Steps to reproduce the behavior
[ss_postgres.zip](https://github.com/user-attachments/files/24253131/ss_postgres.zip)
run `docker compose up -d` in this file.
init db:
```sql
SET DEFAULT SINGLE TABLE STORAGE UNIT = ds_0;
DROP TABLE IF EXISTS t8;
create table t8 (
vkey int4 ,
pkey int4 ,
c55 int4 ,
c56 int4 ,
c57 int4 ,
c58 int4 ,
c59 int4 ,
c60 int4
);
CREATE SHARDING TABLE RULE t23 (
STORAGE_UNITS(ds_0,ds_1,ds_2,ds_3,ds_4),
SHARDING_COLUMN=colocated_key,TYPE(NAME="MOD",PROPERTIES(
"sharding-count"="16"))
);
DROP TABLE IF EXISTS t23;
create table t23 (
vkey int4 ,
pkey int4 ,
c36 int4 ,
c37 int4 ,
c38 text ,
c39 text ,
c40 numeric ,
c41 int4 ,
c42 int4 ,
colocated_key int4
);
CREATE SHARDING TABLE RULE t22 (
STORAGE_UNITS(ds_0,ds_1,ds_2,ds_3,ds_4),
SHARDING_COLUMN=colocated_key,TYPE(NAME="MOD",PROPERTIES(
"sharding-count"="16"))
);
DROP TABLE IF EXISTS t22;
create table t22 (
vkey int4 ,
pkey int4 ,
c29 numeric ,
c30 numeric ,
c31 text ,
c32 int4 ,
c33 int4 ,
c34 numeric ,
c35 numeric ,
colocated_key int4
);
CREATE SHARDING TABLE REFERENCE RULE ref_0 (t22,t23);
CREATE BROADCAST TABLE RULE t17;
DROP TABLE IF EXISTS t17;
create table t17 (
vkey int4 ,
pkey int4 ,
c55 int4 ,
c56 int4 ,
c57 int4 ,
c58 int4 ,
c59 int4 ,
c60 int4
);
```
, then execute:
```sql
select
1 as c_0
from
t17 as ref_0 --- can be reproduced by local table t8
where not ((ref_0.c59) < (
select
ref_1.c36 as c_0
from
t23 as ref_1 join t22 as ref_2
on(ref_1.colocated_key = ref_2.colocated_key)
));
```
### Log and analyze.
`t8` is a local table, `t17` is a broadcast table. `t22` and `t23` are
sharding tables binded with each other. the join should be pushed down. But we
may also consider this bug is caused by the outer `<` subquery. This bug will
not occur when t22 and t23 are not sharding tables.
Log:
```
[ShardingSphere-Command-11] o.a.s.p.f.c.CommandExecutorTask - Exception
occur:
org.postgresql.util.PSQLException: ERROR: relation "t23" does not exist
Position: 128
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:341)
at
org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:326)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:302)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:1310)
at
com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:102)
at
com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at
org.apache.shardingsphere.proxy.backend.connector.jdbc.executor.callback.impl.ProxyStatementExecutorCallback.execute(ProxyStatementExecutorCallback.java:44)
at
org.apache.shardingsphere.proxy.backend.connector.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:69)
at
org.apache.shardingsphere.proxy.backend.connector.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:46)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:91)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:65)
at
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:99)
at
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:95)
at
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:78)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:66)
at
org.apache.shardingsphere.proxy.backend.connector.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:73)
at
org.apache.shardingsphere.proxy.backend.connector.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:237)
at
org.apache.shardingsphere.proxy.backend.connector.ProxySQLExecutor.execute(ProxySQLExecutor.java:194)
at
org.apache.shardingsphere.proxy.backend.connector.StandardDatabaseConnector.doExecute(StandardDatabaseConnector.java:234)
at
org.apache.shardingsphere.proxy.backend.connector.StandardDatabaseConnector.execute(StandardDatabaseConnector.java:190)
at
org.apache.shardingsphere.proxy.frontend.postgresql.command.query.simple.PostgreSQLComQueryExecutor.execute(PostgreSQLComQueryExecutor.java:79)
at
org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:126)
at
org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:121)
at
org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:60)
at
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
at
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
at java.base/java.lang.Thread.run(Thread.java:1583)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
