[
https://issues.apache.org/jira/browse/PHOENIX-1750?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14377041#comment-14377041
]
James Taylor commented on PHOENIX-1750:
---------------------------------------
[~sergey.b] - I noticed your group by is using trunc. Internally, we translate
trunc to floor as they're identical. If you use floor instead, does the problem
go away? I'm just trying to find a workaround for you as I don't want you to be
stuck.
> 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)
