[
https://issues.apache.org/jira/browse/PHOENIX-1750?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14377192#comment-14377192
]
Serhiy Bilousov commented on PHOENIX-1750:
------------------------------------------
Thank you [~jamestaylor] for suggestion. As I noticed query
{noformat}
select cast(trunc(ts,'HOUR') AS TIMESTAMP) AS dt, count(*) AS cnt from t1 group
by trunc(ts,'HOUR');
{noformat}
is working (and can be considered as workaround) but I there is definitely
something going on in there and should be addressed. (best option if you ask me
is make sure expressions and/or alias works in GROUP BY).
I am pretty sure trunc is not only source of the problem here. I think TO_DATE
or CAST AS timestamp become TO_TIMESTAMP or something like that. I probably can
spent couple hours to go over every function but I hope fixing one case would
cover all other cases as it looks like parsing issue (replacing user specified
expression with inside implementation).
> 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)
