[SQL Federation] Correlated IN-subquery fails in a non-join subquery but succeed in a more complex subquery with join.

[duerwuyi]

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

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

run docker compose up -d in this file.

run following query to initialize db.

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.