You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "duerwuyi (via GitHub)" <gi...@apache.org> on 2025/06/28 14:16:42 UTC
[I] Some queries has query plan but cannot execute because of missing table when using Local tables. [shardingsphere]
duerwuyi opened a new issue, #35832:
URL: https://github.com/apache/shardingsphere/issues/35832
## Bug Report
### Which version of ShardingSphere did you use?
shardingsphere-proxy 5.5.2
postgres 17.2 as backend
### Expected behavior
I will provide the following steps to reproduce this bug.
global.yaml:
```yaml
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: host.docker.internal:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
authority:
users:
- user: postgres@%
password: 123abc
admin: true
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
logging:
loggers:
- loggerName: ShardingSphere-SQL
additivity: true
level: DEBUG
props:
enable: true
sqlFederation:
sqlFederationEnabled: true
allQueryUseSQLFederation: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535
props:
system-log-level: DEBUG
sql-show: true
check-table-metadata-enabled: true
proxy-frontend-database-protocol-type: PostgreSQL
```
database-sharding.yaml
```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.yml
```yml
version: '3.8'
services:
zookeeper:
image: zookeeper:3.8.4
container_name: zookeeper
restart: always
ports:
- "2181:2181"
environment:
ZOO_MY_ID: 1
shardingsphere-proxy:
image: apache/shardingsphere-proxy:5.5.2
container_name: shardingsphere-proxy
environment:
- PORT=5432
ports:
- "5440:5432"
volumes:
- ./conf:/opt/shardingsphere-proxy/conf
- ./logs:/opt/shardingsphere-proxy/logs
- ./ext-lib:/opt/shardingsphere-proxy/ext-lib
depends_on:
- zookeeper
- 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:
```
after starting the cluster, create a new database named `testdb` on all postgres-deatabases and sharding-sphere proxy, then try:
```sql
CREATE SHARDING TABLE RULE t0 (
STORAGE_UNITS(ds_0,ds_1,ds_2,ds_3,ds_4),
SHARDING_COLUMN=vkey,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="5")));
CREATE TABLE t0 (
vkey INT4,
c3 TIMESTAMP
);
CREATE TABLE t3 ( ---on ds_0
vkey INT4,
c26 TIMESTAMP
);
CREATE TABLE t4 ( ---on ds_0
vkey INT4
);
LOAD SINGLE TABLE ds_0.postgres.t3;
LOAD SINGLE TABLE ds_0.postgres.t4;
```
The following behavior is weird. Some queries has query plan but cannot execute because of missing table.
### Actual behavior
```
postgres=> explain select *
from
(t0 full outer join t3
on (t0.c3 = t3.c26 ))
where (
exists (
select *
from
t4
)
);
QUERY PLAN
---------------------------------------------------------------------------
Result (cost=285.10..607.42 rows=20808 width=24)
One-Time Filter: (InitPlan 1).col1
InitPlan 1
-> Seq Scan on t4 (cost=0.00..35.50 rows=2550 width=0)
-> Merge Full Join (cost=285.10..607.42 rows=20808 width=24)
Merge Cond: (t0_0.c3 = t3.c26)
-> Sort (cost=142.54..147.64 rows=2040 width=12)
Sort Key: t0_0.c3
-> Seq Scan on t0_0 (cost=0.00..30.40 rows=2040 width=12)
-> Sort (cost=142.54..147.64 rows=2040 width=12)
Sort Key: t3.c26
-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)
(12 rows)
postgres=> select *
from
(t0 full outer join t3
on (t0.c3 = t3.c26 ))
where (
exists (
select *
from
t4
)
);
ERROR: Table or view 't3' does not exist.
postgres=> explain select * from t3;
ERROR: relation "t3" does not exist
LINE 1: explain select * from t3;
^
```
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Some queries has query plan but cannot execute because of missing table when using Local tables. [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #35832:
URL: https://github.com/apache/shardingsphere/issues/35832#issuecomment-3017820709
@duerwuyi I think the problem is that tables `t_3` and `t_4` are not in `public` schema. Currently ShardingSphere only supports public schema in PostgreSQL.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Some queries has query plan but cannot execute because of missing table when using Local tables. [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang closed issue #35832: Some queries has query plan but cannot execute because of missing table when using Local tables.
URL: https://github.com/apache/shardingsphere/issues/35832
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Some queries has query plan but cannot execute because of missing table when using Local tables. [shardingsphere]
Posted by "duerwuyi (via GitHub)" <gi...@apache.org>.
duerwuyi commented on issue #35832:
URL: https://github.com/apache/shardingsphere/issues/35832#issuecomment-3018042158
```
LOAD SINGLE TABLE ds_0.public.t3;
LOAD SINGLE TABLE ds_0.public.t4;
```
solved.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org