You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Mihai Budiu <mb...@gmail.com> on 2025/10/14 16:09:08 UTC
Re: [jira] [Commented] (CALCITE-7123) Query doesn't work as expected when using COALESCE or CASE in the group by query
So the problem is in the handling of empty strings, which shold be considered NULL? Indeed, in Calcite these are different.
Mihai
________________________________
From: Priyanka (Jira) <ji...@apache.org>
Sent: Tuesday, October 14, 2025 8:23 AM
To: issues@calcite.apache.org <is...@calcite.apache.org>
Subject: [jira] [Commented] (CALCITE-7123) Query doesn't work as expected when using COALESCE or CASE in the group by query
[ https://issues.apache.org/jira/browse/CALCITE-7123?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18029810#comment-18029810 ]
Priyanka commented on CALCITE-7123:
-----------------------------------
Hi [~mbudiu] , I analyzed the query plan and this is what I found:
* *Avatica (native Druid)* evaluates {{v1 = nvl("parentName","stockName")}} with the {*}Druid expression engine{*}.
* *Calcite* evaluates {{CASE(IS NOT NULL(parentName), parentName, stockName)}} with {*}SQL null semantics{*}.
The issue I am facing seems to be because of the rows with parentName as null. I changed the condition from
{code:java}
COALESCE("parentName", "stockName"){code}
to
{code:java}
CASE
WHEN "parentName" IS NOT NULL AND "parentName" <> '' THEN "parentName"
ELSE "stockName"{code}
and with that change I'm getting the expected results now.
I don't know if this needs any fix on the Calcite side. If not, I'm happy to close this issue.
> Query doesn't work as expected when using COALESCE or CASE in the group by query
> --------------------------------------------------------------------------------
>
> Key: CALCITE-7123
> URL: https://issues.apache.org/jira/browse/CALCITE-7123
> Project: Calcite
> Issue Type: Bug
> Components: avatica
> Affects Versions: 1.39.0
> Reporter: Priyanka
> Priority: Major
> Attachments: image-2025-08-03-21-12-40-517.png, image-2025-08-03-21-13-52-179.png, image-2025-08-03-21-18-10-506.png, image-2025-08-14-11-56-16-650.png
>
>
> Running the following query directly on the Druid UI returns the expected results:
> {code:java}
> SELECT
> FLOOR("__time" TO DAY) AS "createtime.day",
> COALESCE("parentName", "stockName") AS "unitName",
> COUNT(*) AS "count"
> FROM
> "druid"."events"
> WHERE "customerName" = 'x' AND "__time" >= TIMESTAMP '2025-05-31 22:00:00.000' AND "__time" <= TIMESTAMP '2025-06-12 21:59:59.000'
> AND (FALSE = true OR "fleetName" IN ('y'))
> AND (FALSE = false OR "eventTemplateName" IN (null))
> AND (FALSE = false OR "consistNumber" IN (null))
> AND (FALSE = false OR ("parentName" IS NOT NULL AND "stockName" IN (null)))
> AND (FALSE = false OR (COALESCE("parentName", "stockName") IN (null)))
> AND (FALSE = false OR "locationName" IN (null))
> AND (FALSE = false OR "eventCategory" IN (null))
> AND (FALSE = false OR "eventPriorityName" IN (null))
> AND (FALSE = false OR null)
> AND (FALSE = false OR "active" = null)
> GROUP BY FLOOR("__time" TO DAY) , COALESCE("parentName", "stockName") {code}
> But when it goes through Calcite, it returns the stockName always as the unitName, even if the parentName is available.
> Testing with CASE instead of COALESCE or using a CAST for the type also didn't have any effect with Calcite.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)