[jira] [Commented] (CALCITE-1578) Druid adapter: wrong semantics of topN query limit with granularity

["Jesus Camacho Rodriguez (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15824159#comment-15824159 ] 

Jesus Camacho Rodriguez commented on CALCITE-1578:
--------------------------------------------------

[~julianhyde], could you double-check the alternative SQL formulation? Thanks

> Druid adapter: wrong semantics of topN query limit with granularity
> -------------------------------------------------------------------
>
>                 Key: CALCITE-1578
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1578
>             Project: Calcite
>          Issue Type: Bug
>          Components: druid
>    Affects Versions: 1.11.0
>            Reporter: Jesus Camacho Rodriguez
>            Assignee: Jesus Camacho Rodriguez
>            Priority: Critical
>
> Semantics of Druid topN query with limit and granularity is not equivalent to input SQL. In particular, limit is applied on each granularity value, not on the overall query.
> Currently, the following query will be transformed into a topN query:
> {code:sql}
> SELECT i_brand_id, floor_day(`__time`), max(ss_quantity), sum(ss_wholesale_cost) as s
> FROM store_sales_sold_time_subset
> GROUP BY i_brand_id, floor_day(`__time`)
> ORDER BY s DESC
> LIMIT 10;
> {code}
> Previous query outputs at most 10 rows. In turn, the equivalent SQL query for a Druid topN query should be expressed as:
> {code:sql}
> SELECT rs.i_brand_id, rs.d, rs.m, rs.s
> FROM (
>     SELECT i_brand_id, floor_day(`__time`) as d, max(ss_quantity) as m, sum(ss_wholesale_cost) as s,
>            ROW_NUMBER() OVER (PARTITION BY floor_day(`__time`) ORDER BY sum(ss_wholesale_cost) DESC ) AS rownum
>     FROM store_sales_sold_time_subset
>     GROUP BY i_brand_id, floor_day(`__time`)
> ) rs
> WHERE rownum <= 10;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)