Correlated subquery in HAVING cannot reference outer query column, ColumnNotFoundException in PG

[duerwuyi]

Bug Report

Which version of ShardingSphere did you use?

shardingsphere-proxy 5.5.2
postgres 17.2 as backend

Expected behavior

The query should execute successfully (same as PostgreSQL behavior), because the subquery is correlated and should be able to reference outer query columns in WHERE.

Actual behavior

The following query fails with:
ERROR: Unknown column 'ref_0.c13' in 'where clause'

testdb=> SET DEFAULT SINGLE TABLE STORAGE UNIT = ds_0;
SUCCESS
testdb=> DROP TABLE IF EXISTS t3;
DROP TABLE
testdb=> DROP TABLE IF EXISTS t2;
DROP TABLE
testdb=> CREATE TABLE t3 (c13 INT);
CREATE TABLE
testdb=> CREATE TABLE t2 (vkey INT, c8 TEXT);
CREATE TABLE
---sql
testdb=> select 
  ref_0.c13 as c_0
from 
  t3 as ref_0
group by ref_0.c13
having 'a' = (
    select ref_2.c8 as c_0
    from t2 as ref_2
    where (ref_0.c13) between (ref_2.vkey) and (ref_2.vkey)
    limit 1
);
ERROR:  Unknown column 'ref_0.c13' in 'where clause'.

If I comment out the WHERE clause in the subquery (remove the correlated reference), the query runs successfully.
We consider that the tables are all single tables stored in the same storage unit, so it may not be a problem of federation engine (We did not enable it).

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.

SQL to execute are shown above

[terrymanu]

Problem Understanding

• Scenario: single database, single table (SET DEFAULT SINGLE TABLE STORAGE UNIT = ds_0), no SQL Federation or sharding enabled. SQL is a standard GROUP BY + HAVING with a correlated subquery whose WHERE references the outer grouping column.
• Expected: behave like native PostgreSQL and push down successfully.
• Actual: in Proxy 5.5.2 (and ShardingSphere-JDBC, which shares the same binder) binding fails with Unknown column 'ref_0.c13' in 'where clause', so it never reaches the backend.

Root Cause

• In infra/binder/core, HAVING binding passes only the projection-based currentTableBinderContexts into the subquery’s outer context and omits the FROM-generated tableBinderContexts (which contain outer aliases such as ref_0).
• When binding the subquery, the outer alias is invisible; ColumnSegmentBinder then treats ref_0.c13 in the subquery WHERE as missing and throws ColumnNotFoundException.

Analysis

• The SQL is within ShardingSphere’s supported scope: single-table direct pushdown should work, and the database itself executes it fine.
• Correlated subqueries in WHERE work because that path includes the FROM context; the HAVING path misses it.
• Impact scope: all dialects (MySQL/PG/openGauss, etc.) in both Proxy and JDBC modes can hit this binding defect.

Conclusion (Fix Proposal & Invitation)

• This is a general binder gap for correlated subqueries in HAVING. Proposal: when binding HAVING, merge the FROM tableBinderContexts together with currentTableBinderContexts into the subquery’s outer context (align with the WHERE path) so the subquery can resolve outer
  aliases.
• Add regression tests in infra/binder for HAVING with correlated subqueries referencing outer columns to ensure no ColumnNotFound and correct pushdown.
• We warmly welcome community contributors to submit a PR to implement the fix and the tests—your help to make this scenario solid is greatly appreciated!