[
https://issues.apache.org/jira/browse/PHOENIX-1750?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Taylor updated PHOENIX-1750:
----------------------------------
Priority: Trivial (was: Major)
> 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
> Priority: Trivial
>
> 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)
