[
https://issues.apache.org/jira/browse/CALCITE-7123?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18013880#comment-18013880
]
Priyanka commented on CALCITE-7123:
-----------------------------------
Thank you for your input [~julianhyde] and [~mbudiu] and apologies for the
delay.
I tried to reproduce the issue on the built-in tables, and I wasn't able to see
the same behaviour. However, there are a couple more things that I should
mention about my implementation:
# My implementation is in Java, and I use the Calcite dependency version
1.39.0.
# I have my data in two databases - Postgres and Druid.
# I have two data sources for my NamedParameterJdbcTemplate - a Calcite
datasource when I require data from both DBs combined, and an Avatica
datasource when I needed data from only one of the two databases.
# I ran the same minimal Druid query mentioned above using both the Avatica
and Calcite datasources. The Avatica query returns the correct results (i.e.
parentName or stockName depending on whether parentName exists), but the
Calcite query returns incorrect results (i.e. it always returns the stockName
even if the parentName exists). The query I need is actually merged data from
both Postgres and Druid. But it's only the Druid part that is returning the
wrong results with the Caclite data source.
!image-2025-08-14-11-56-16-650.png!
Please let me know if there's anything else you'd like me to try.
> 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)
