[
https://issues.apache.org/jira/browse/CALCITE-1589?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jesus Camacho Rodriguez updated CALCITE-1589:
---------------------------------------------
Description:
Following query is transformed into timeseries Druid query which yields
different results in Calcite vs Druid, since it will show all values for the
given time granularity, even if there is no data for the given _i\_brand\_id_.
{code:sql}
SELECT floor_day(`__time`) as `granularity`, max(ss_quantity),
sum(ss_wholesale_cost)
FROM store_sales_sold_time_subset
WHERE i_brand_id = 10001009
GROUP BY floor_day(`__time`)
ORDER BY `granularity`;
OK
1999-11-01 00:00:00 45 37.47
1999-11-02 00:00:00 -9223372036854775808 0.0
1999-11-03 00:00:00 -9223372036854775808 0.0
1999-11-04 00:00:00 39 61.52
1999-11-05 00:00:00 74 145.84
1999-11-06 00:00:00 62 14.5
1999-11-07 00:00:00 -9223372036854775808 0.0
1999-11-08 00:00:00 5 34.08
1999-11-09 00:00:00 -9223372036854775808 0.0
1999-11-10 00:00:00 -9223372036854775808 0.0
1999-11-11 00:00:00 -9223372036854775808 0.0
1999-11-12 00:00:00 66 67.22
1999-11-13 00:00:00 -9223372036854775808 0.0
1999-11-14 00:00:00 -9223372036854775808 0.0
1999-11-15 00:00:00 -9223372036854775808 0.0
1999-11-16 00:00:00 60 96.37
1999-11-17 00:00:00 50 79.11
1999-11-18 00:00:00 -9223372036854775808 0.0
1999-11-19 00:00:00 -9223372036854775808 0.0
1999-11-20 00:00:00 -9223372036854775808 0.0
1999-11-21 00:00:00 -9223372036854775808 0.0
1999-11-22 00:00:00 -9223372036854775808 0.0
1999-11-23 00:00:00 57 17.69
1999-11-24 00:00:00 -9223372036854775808 0.0
1999-11-25 00:00:00 -9223372036854775808 0.0
1999-11-26 00:00:00 -9223372036854775808 0.0
1999-11-27 00:00:00 86 91.59
1999-11-28 00:00:00 -9223372036854775808 0.0
1999-11-29 00:00:00 93 136.48
1999-11-30 00:00:00 -9223372036854775808 0.0
SELECT floor_day(`__time`) as `granularity`, max(ss_quantity),
sum(ss_wholesale_cost)
FROM store_sales_sold_time_subset_calcite
WHERE i_brand_id = 10001009
GROUP BY floor_day(`__time`)
ORDER BY `granularity`;
OK
1999-11-01 00:00:00 45 37.47
1999-11-04 00:00:00 39 61.52
1999-11-05 00:00:00 74 145.84
1999-11-06 00:00:00 62 14.5
1999-11-08 00:00:00 5 34.08
1999-11-12 00:00:00 66 67.22
1999-11-16 00:00:00 60 96.36999999999999
1999-11-17 00:00:00 50 79.11
1999-11-23 00:00:00 57 17.689999999999998
1999-11-27 00:00:00 86 91.59
1999-11-29 00:00:00 93 136.48
{code}
was:
Following query is transformed into timeseries Druid query which yields
different results in Calcite vs Druid, since it will show all values for the
given time granularity, even if there is no data for the given _i\_brand\_id_.
In Druid:
{code:sql}
SELECT floor_day(`__time`) as `granularity`, max(ss_quantity),
sum(ss_wholesale_cost)
FROM store_sales_sold_time_subset
WHERE i_brand_id = 10001009
GROUP BY floor_day(`__time`)
ORDER BY `granularity`;
OK
1999-11-01 00:00:00 45 37.47
1999-11-02 00:00:00 -9223372036854775808 0.0
1999-11-03 00:00:00 -9223372036854775808 0.0
1999-11-04 00:00:00 39 61.52
1999-11-05 00:00:00 74 145.84
1999-11-06 00:00:00 62 14.5
1999-11-07 00:00:00 -9223372036854775808 0.0
1999-11-08 00:00:00 5 34.08
1999-11-09 00:00:00 -9223372036854775808 0.0
1999-11-10 00:00:00 -9223372036854775808 0.0
1999-11-11 00:00:00 -9223372036854775808 0.0
1999-11-12 00:00:00 66 67.22
1999-11-13 00:00:00 -9223372036854775808 0.0
1999-11-14 00:00:00 -9223372036854775808 0.0
1999-11-15 00:00:00 -9223372036854775808 0.0
1999-11-16 00:00:00 60 96.37
1999-11-17 00:00:00 50 79.11
1999-11-18 00:00:00 -9223372036854775808 0.0
1999-11-19 00:00:00 -9223372036854775808 0.0
1999-11-20 00:00:00 -9223372036854775808 0.0
1999-11-21 00:00:00 -9223372036854775808 0.0
1999-11-22 00:00:00 -9223372036854775808 0.0
1999-11-23 00:00:00 57 17.69
1999-11-24 00:00:00 -9223372036854775808 0.0
1999-11-25 00:00:00 -9223372036854775808 0.0
1999-11-26 00:00:00 -9223372036854775808 0.0
1999-11-27 00:00:00 86 91.59
1999-11-28 00:00:00 -9223372036854775808 0.0
1999-11-29 00:00:00 93 136.48
1999-11-30 00:00:00 -9223372036854775808 0.0
{code:sql}
SELECT floor_day(`__time`) as `granularity`, max(ss_quantity),
sum(ss_wholesale_cost)
FROM store_sales_sold_time_subset_calcite
WHERE i_brand_id = 10001009
GROUP BY floor_day(`__time`)
ORDER BY `granularity`;
OK
1999-11-01 00:00:00 45 37.47
1999-11-04 00:00:00 39 61.52
1999-11-05 00:00:00 74 145.84
1999-11-06 00:00:00 62 14.5
1999-11-08 00:00:00 5 34.08
1999-11-12 00:00:00 66 67.22
1999-11-16 00:00:00 60 96.36999999999999
1999-11-17 00:00:00 50 79.11
1999-11-23 00:00:00 57 17.689999999999998
1999-11-27 00:00:00 86 91.59
1999-11-29 00:00:00 93 136.48
{code}
> Druid adapter: timeseries query shows all days, even if no data
> ---------------------------------------------------------------
>
> Key: CALCITE-1589
> URL: https://issues.apache.org/jira/browse/CALCITE-1589
> Project: Calcite
> Issue Type: Bug
> Components: druid
> Reporter: Jesus Camacho Rodriguez
> Assignee: Julian Hyde
> Priority: Critical
> Fix For: 1.12.0
>
>
> Following query is transformed into timeseries Druid query which yields
> different results in Calcite vs Druid, since it will show all values for the
> given time granularity, even if there is no data for the given _i\_brand\_id_.
> {code:sql}
> SELECT floor_day(`__time`) as `granularity`, max(ss_quantity),
> sum(ss_wholesale_cost)
> FROM store_sales_sold_time_subset
> WHERE i_brand_id = 10001009
> GROUP BY floor_day(`__time`)
> ORDER BY `granularity`;
> OK
> 1999-11-01 00:00:00 45 37.47
> 1999-11-02 00:00:00 -9223372036854775808 0.0
> 1999-11-03 00:00:00 -9223372036854775808 0.0
> 1999-11-04 00:00:00 39 61.52
> 1999-11-05 00:00:00 74 145.84
> 1999-11-06 00:00:00 62 14.5
> 1999-11-07 00:00:00 -9223372036854775808 0.0
> 1999-11-08 00:00:00 5 34.08
> 1999-11-09 00:00:00 -9223372036854775808 0.0
> 1999-11-10 00:00:00 -9223372036854775808 0.0
> 1999-11-11 00:00:00 -9223372036854775808 0.0
> 1999-11-12 00:00:00 66 67.22
> 1999-11-13 00:00:00 -9223372036854775808 0.0
> 1999-11-14 00:00:00 -9223372036854775808 0.0
> 1999-11-15 00:00:00 -9223372036854775808 0.0
> 1999-11-16 00:00:00 60 96.37
> 1999-11-17 00:00:00 50 79.11
> 1999-11-18 00:00:00 -9223372036854775808 0.0
> 1999-11-19 00:00:00 -9223372036854775808 0.0
> 1999-11-20 00:00:00 -9223372036854775808 0.0
> 1999-11-21 00:00:00 -9223372036854775808 0.0
> 1999-11-22 00:00:00 -9223372036854775808 0.0
> 1999-11-23 00:00:00 57 17.69
> 1999-11-24 00:00:00 -9223372036854775808 0.0
> 1999-11-25 00:00:00 -9223372036854775808 0.0
> 1999-11-26 00:00:00 -9223372036854775808 0.0
> 1999-11-27 00:00:00 86 91.59
> 1999-11-28 00:00:00 -9223372036854775808 0.0
> 1999-11-29 00:00:00 93 136.48
> 1999-11-30 00:00:00 -9223372036854775808 0.0
> SELECT floor_day(`__time`) as `granularity`, max(ss_quantity),
> sum(ss_wholesale_cost)
> FROM store_sales_sold_time_subset_calcite
> WHERE i_brand_id = 10001009
> GROUP BY floor_day(`__time`)
> ORDER BY `granularity`;
> OK
> 1999-11-01 00:00:00 45 37.47
> 1999-11-04 00:00:00 39 61.52
> 1999-11-05 00:00:00 74 145.84
> 1999-11-06 00:00:00 62 14.5
> 1999-11-08 00:00:00 5 34.08
> 1999-11-12 00:00:00 66 67.22
> 1999-11-16 00:00:00 60 96.36999999999999
> 1999-11-17 00:00:00 50 79.11
> 1999-11-23 00:00:00 57 17.689999999999998
> 1999-11-27 00:00:00 86 91.59
> 1999-11-29 00:00:00 93 136.48
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
