Serhiy Bilousov created PHOENIX-1750:
----------------------------------------
Summary: 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
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)
