[CALCITE-5711] Implement the SINGLE_VALUE aggregation in PostgreSQL Dialect

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0
• Fix Version/s: 1.35.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: xiong duan
• Due: 25/May/23
• Created: 18/May/23 13:16
• Updated: 07/Jun/24 12:41
• Resolved: 03/Jul/23 08:04

Description

When we use the SQL including the scalar query, converting this SQL Rel to PostgreSQL will include the SINGLE_VALUE aggregation function(PostgreSQL can't handle it) in the dialect SQL.

Input SQL:

select "product_class_id" as c
from "product" where  "brand_name" = (select "product_name" from "product") 

PostgreSQL Dialect now generates SQL:

SELECT \"product\".\"product_class_id\" AS \"C\"
FROM \"foodmart\".\"product\"
LEFT JOIN (SELECT SINGLE_VALUE(\"product_name\") AS \"$f0\"
FROM \"foodmart\".\"product\") AS \"t0\" ON TRUE
WHERE \"product\".\"brand_name\" = \"t0\".\"$f0\" 

PostgreSQL Dialect will generate SQL:

SELECT "product"."product_class_id" AS "C"
FROM "foodmart"."product"
LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN MIN("product_name") ELSE (SELECT CAST(NULL AS VARCHAR(60))
UNION ALL
SELECT CAST(NULL AS VARCHAR(60))) END AS "$f0"
FROM "foodmart"."product") AS "t0" ON TRUE
WHERE "product"."brand_name" = "t0"."$f0" 

Issue Links

• relates to: CALCITE-6431 Implement the SINGLE_VALUE aggregation in HiveSqlDialect And SparkSQLDialect
• links to: GitHub Pull Request #3219