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) <[email protected]> Sent: Tuesday, October 14, 2025 8:23 AM To: [email protected] <[email protected]> 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)
