You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "xiong duan (Jira)" <ji...@apache.org> on 2023/07/03 08:07:00 UTC

[jira] [Commented] (CALCITE-5711) Implement the SINGLE_VALUE aggregation in PostgreSQL Dialect

    [ https://issues.apache.org/jira/browse/CALCITE-5711?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17739487#comment-17739487 ] 

xiong duan commented on CALCITE-5711:
-------------------------------------

Fixed in [d1a12bb6f4d4f61|https://github.com/apache/calcite/commit/d1a12bb6f4d4f617234dfbc32cb0813c473b0029]. Thanks for the review [~VAE] .

> Implement the SINGLE_VALUE aggregation in PostgreSQL Dialect
> ------------------------------------------------------------
>
>                 Key: CALCITE-5711
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5711
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.34.0
>            Reporter: xiong duan
>            Assignee: xiong duan
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.35.0
>
>
> 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:
> {code:java}
> select "product_class_id" as c
> from "product" where  "brand_name" = (select "product_name" from "product") {code}
> PostgreSQL Dialect now generates SQL:
> {code:java}
> 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\" {code}
> PostgreSQL Dialect will generate SQL:
> {code:java}
> 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" {code}



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