[jira] [Created] (CALCITE-7439) RelToSqlConverter emits ambiguous GROUP BY after LEFT JOIN USING with semi-join rewrite

[Bruno Volpato da Cunha (Jira)]

Bruno Volpato da Cunha created CALCITE-7439:
-----------------------------------------------

             Summary: RelToSqlConverter emits ambiguous GROUP BY after LEFT 
JOIN USING with semi-join rewrite
                 Key: CALCITE-7439
                 URL: https://issues.apache.org/jira/browse/CALCITE-7439
             Project: Calcite
          Issue Type: Bug
            Reporter: Bruno Volpato da Cunha


Reproduction query
{code:sql}
WITH product_keys AS (
  SELECT p."product_id",
         (SELECT MAX(p3."product_id")
          FROM "foodmart"."product" p3
          WHERE p3."product_id" = p."product_id") AS "mx"
  FROM "foodmart"."product" p
)
SELECT DISTINCT pk."product_id"
FROM product_keys pk
LEFT JOIN "foodmart"."product" p2 USING ("product_id")
WHERE pk."product_id" IN (
  SELECT p4."product_id"
  FROM "foodmart"."product" p4
)
{code}

Optimizer rules
{code}
CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE
CoreRules.FILTER_SUB_QUERY_TO_CORRELATE
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
CoreRules.PROJECT_SUB_QUERY_TO_MARK_CORRELATE
CoreRules.FILTER_SUB_QUERY_TO_MARK_CORRELATE
CoreRules.MARK_TO_SEMI_OR_ANTI_JOIN_RULE
CoreRules.PROJECT_TO_SEMI_JOIN
{code}

Generated SQL
{code:sql}
SELECT "product_id"
FROM (SELECT "$cor0"."product_id", "t1"."EXPR$0" AS "mx"
FROM "foodmart"."product" AS "$cor0",
LATERAL (SELECT MAX("product_id") AS "EXPR$0"
FROM "foodmart"."product"
WHERE "product_id" = "$cor0"."product_id") AS "t1") AS "t2"
LEFT JOIN "foodmart"."product" AS "product1" ON "t2"."product_id" = 
"product1"."product_id"
WHERE EXISTS (SELECT 1
FROM (SELECT "product_id"
FROM "foodmart"."product") AS "t3"
WHERE "t2"."product_id" = "t3"."product_id")
GROUP BY "product_id"
{code}

Actual behavior
Validation fails with:
{code}
org.apache.calcite.runtime.CalciteContextException:
>From line 12, column 10 to line 12, column 21: Column 'product_id' is ambiguous
{code}

Expected behavior
The generated SQL should qualify GROUP BY with the left-side alias (for 
example, GROUP BY "t2"."product_id"), or otherwise emit SQL that is unambiguous 
and valid under PostgreSQL dialect.

Environment
- Calcite main branch (1.42.0-SNAPSHOT)
- RelToSqlConverterTest with PostgreSQL dialect
- Java 21



--
This message was sent by Atlassian Jira
(v8.20.10#820010)