[
https://issues.apache.org/jira/browse/CALCITE-7123?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18022500#comment-18022500
]
Mihai Budiu commented on CALCITE-7123:
--------------------------------------
I added the following test in the babel postgres.iq file, and it passed:
{code}
create table druid_events ("__time" TIMESTAMP, "uuid" VARCHAR, "stockName" INT,
"parentName" INT);
(0 rows modified)
!update
INSERT INTO DRUID_EVENTS VALUES
('2025-06-02 03:02:14.995', '88', 22308, 22008),
('2025-06-02 03:04:39.048', '89', 22211, 22011),
('2025-06-02 03:08:42.116', 'a4', 22409, 22009),
('2025-06-02 03:08:43.116', '37', 22413, 22013),
('2025-06-02 03:21:05.290', 'e6', 22311, 22011);
(5 rows modified)
!update
SELECT
FLOOR("__time" TO DAY) AS "createtime.day",
COALESCE("parentName", "stockName") AS "unitName",
COUNT(*) AS "count"
FROM druid_events
WHERE
"__time" >= TIMESTAMP '2025-05-31 22:00:00.000' AND "__time" <= TIMESTAMP
'2025-06-12 21:59:59.000'
GROUP BY FLOOR("__time" TO DAY), COALESCE("parentName", "stockName");
createtime.day, unitName, count
2025-06-02 00:00:00, 22008, 1
2025-06-02 00:00:00, 22009, 1
2025-06-02 00:00:00, 22011, 2
2025-06-02 00:00:00, 22013, 1
!ok
{code}
These results look as expected to me. Is my reproduction of your code correct?
> 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)
