phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Serhiy Bilousov (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-1750) Some build-in functions used in expression surface internal implementation as column alias what cause GROUP BY to fail
Date Tue, 24 Mar 2015 04:11:52 GMT

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

Serhiy Bilousov commented on PHOENIX-1750:
------------------------------------------

sorry but - why?

this one works as workaround. 
{noformat}
select cast(trunc(ts,'HOUR') AS TIMESTAMP) AS dt, count(*) AS cnt from t1 group by trunc(ts,'HOUR');
{noformat}

Why do you need to replace TRUNC vs FLOOR and do you want it replace in both in SELECT and
GROUP BY ? 
I am not sure I follow...

> Some build-in functions used in expression surface internal implementation as column
alias what cause GROUP BY to fail
> ----------------------------------------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-1750
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-1750
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: Serhiy Bilousov
>            Assignee: Samarth Jain
>
> Consider query
> {noformat}
> DROP TABLE IF EXISTS t1;
> CREATE TABLE t1 (ts TIMESTAMP not null primary key);
> UPSERT INTO t1 VALUES(to_date('2015-03-17 03:05:45.000'));
> UPSERT INTO t1 VALUES(to_date('2015-03-18 03:05:45.000'));
> UPSERT INTO t1 VALUES(to_date('2015-03-18 03:15:45.000'));
> UPSERT INTO t1 VALUES(to_date('2015-03-16 04:05:45.000'));
> UPSERT INTO t1 VALUES(to_date('2015-03-18 05:25:45.000'));
> UPSERT INTO t1 VALUES(to_date('2015-03-18 05:35:45.000'));
> SELECT * FROM t1;
> +------------------------+
> |           TS           |
> +------------------------+
> | 2015-03-16 04:05:45.0  |
> | 2015-03-17 03:05:45.0  |
> | 2015-03-18 03:05:45.0  |
> | 2015-03-18 03:15:45.0  |
> | 2015-03-18 05:25:45.0  |
> | 2015-03-18 05:35:45.0  |
> +------------------------+
> select cast(trunc(ts,'HOUR') AS TIMESTAMP), count(*) from t1 group by cast(trunc(ts,'HOUR')
AS TIMESTAMP);
> Error: ERROR 1018 (42Y27): Aggregate may not contain columns not in GROUP BY. TO_TIMESTAMP(FLOOR(TO_DATE(TS)))
(state=42Y27,code=1018)
>  select cast(trunc(ts,'HOUR') AS TIMESTAMP) AS dt, count(*) AS cnt from t1 group by cast(trunc(ts,'HOUR')
AS TIMESTAMP);
> Error: ERROR 1018 (42Y27): Aggregate may not contain columns not in GROUP BY. TO_TIMESTAMP(FLOOR(TO_DATE(TS)))
(state=42Y27,code=1018)
> select cast(trunc(ts,'HOUR') AS TIMESTAMP) AS dt, count(*) AS cnt from t1 group by dt;
> Error: ERROR 1018 (42Y27): Aggregate may not contain columns not in GROUP BY. TO_TIMESTAMP(FLOOR(TO_DATE(TS)))
(state=42Y27,code=1018)
> {noformat}
> but than by accident I run
> {noformat}
>  select cast(trunc(ts,'HOUR') AS TIMESTAMP) AS dt, count(*) AS cnt from t1 group by trunc(ts,'HOUR');
> +------------------------+------+
> |           DT           | CNT  |
> +------------------------+------+
> | 2015-03-16 04:00:00.0  | 1    |
> | 2015-03-17 03:00:00.0  | 1    |
> | 2015-03-18 03:00:00.0  | 2    |
> | 2015-03-18 05:00:00.0  | 2    |
> +------------------------+------+
> {noformat}
> So I am not sure how to properly phrase it but still decided to create JIRA 
> since there is definitely something going on there :)



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

Mime
View raw message