duerwuyi opened a new issue, #37439:
URL: https://github.com/apache/shardingsphere/issues/37439
## Bug Report
### Which version of ShardingSphere did you use?
shardingsphere-proxy 5.5.2
postgres 17.6 as backend
### Expected behavior
The following two query should all succeed.
### Actual behavior
```sql
testdb=> select
*
from
t0 as ref_0
where (ref_0.c2) in (select
ref_0.c6 as c_0
from t23 as ref_3
);
ERROR: SQL federation does not support SQL 'select
*
from
t0 as ref_0
where (ref_0.c2) in (select
ref_0.c6 as c_0
from t23 as ref_3
);'.
More details: java.lang.RuntimeException: Error while applying rule
EnumerableScanConverterRule(in:NONE,out:ENUMERABLE), args
[rel#7036:LogicalScan.NONE.[](table=[public,
t23],pushDownRelBuilder=LogicalProject(c_0=[$cor0.c6])
LogicalTableScan(table=[[public, t23]])
)]
testdb=> select
*
from
t0 as ref_0
where (ref_0.c2) in (select
ref_0.c6 as c_0
from
(t9 as ref_1
full outer join t23 as ref_3
on (ref_1.vkey = ref_3.vkey ))
);
vkey | pkey | c0 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
------+------+----+----+----+----+----+----+----+----+----
(0 rows)
```
The second query is similar to the first one, but it is more complex (it has
a join in subquery). We consider the second query need a more complex
optimization to execute. If the second query is valid, the first one should be
valid as well.
We consider it a **vulnerability** of SQL Federation Planner.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
[ss_postgres.zip](https://github.com/user-attachments/files/24253131/ss_postgres.zip)
run `docker compose up -d` in this file.
run following query to initialize db.
```sql
SET DEFAULT SINGLE TABLE STORAGE UNIT = ds_0;
DROP TABLE IF EXISTS t0;
create table t0 (
vkey int4 ,
pkey int4 ,
c0 text ,
c1 int4 ,
c2 text ,
c3 text ,
c4 int4 ,
c5 text ,
c6 text ,
c7 text ,
c8 numeric
);
CREATE BROADCAST TABLE RULE t9;
DROP TABLE IF EXISTS t9;
create table t9 (
vkey int4 ,
pkey int4 ,
c17 text ,
c18 numeric ,
c19 text ,
c20 int4 ,
c21 text ,
c22 numeric ,
c23 numeric ,
c24 numeric
);
SET DEFAULT SINGLE TABLE STORAGE UNIT = ds_1;
DROP TABLE IF EXISTS t23;
create table t23 (
vkey int4 ,
pkey int4 ,
c29 int4 ,
c30 numeric
);
```
SQL to execute are shown above.
--
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]
