Cast with Alias is not recognized in group by

Repository metrics

Stars

4,937 stars (4,937 stars)

PR merge metrics

Avg merge 6d 7h (Avg merge 6d 7h)186 merged PRs in 30d (186 merged PRs in 30d)

Description

SELECT 
    CAST(DATE_TRUNC('day', CAST(DATETIMECONVERT(ts, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:MILLISECONDS') AS TIMESTAMP)) AS TIMESTAMP) AS day_epoch_ms,
    sum(met) AS "sumMet"
FROM mytable
GROUP BY 
    CAST(DATE_TRUNC('day', CAST(DATETIMECONVERT(ts, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:MILLISECONDS') AS TIMESTAMP)) AS TIMESTAMP)
ORDER BY 
    sum(met) DESC
LIMIT 10000;

Error: {'errorCode': 150,
 'message': 'SQLParsingError:\n'
            'org.apache.pinot.sql.parsers.SqlCompilationException: '
            "'as(cast(datetrunc('day', cast(datetimeconvert(ts, "
            "'1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', "
            "'1:MILLISECONDS'), 'TIMESTAMP')), 'TIMESTAMP'), day_epoch_ms)' should "
            'appear in GROUP BY clause.\n'

Contributor guide

Research direction

Investigate the error message showing that the alias 'day epoch ms' is not recognized in GROUP BY. The issue likely lies in Pinot's SQL parser (likely using Apache Calcite). Start by examining files in pinot core/src/main/java/org/apache/pinot/sql/parsers/ directory. Look for how aliased expressions are processed in GROUP BY clauses. Check if there is existing test coverage for similar CAST with alias in GROUP BY. Consider adding a test case and then fixing the parsing logic.

Tech stack

javasql

Domain

backenddatabaseapi

Issue type

Bug

DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.

3

Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.

Half day

Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.

Fresh

ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.

Mostly clear

Prerequisites

SQLJavaApache Pinot basics

Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.

40