[
https://issues.apache.org/jira/browse/PHOENIX-2039?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14587252#comment-14587252
]
James Taylor commented on PHOENIX-2039:
---------------------------------------
In Phoenix, an expression in the SELECT clause is executed on the client side
while an expression in the GROUP BY clause is executed on the server side.
Since the {round(k/1000000,0)} expression is correctly evaluated on the client
side, I suspect that some information is not be passed through and/or
maintained on the server side during evaluation. My first guess is that the
scale may not be preserved correctly. I'd start with a unit test with the
following query:
{code}
select round(k/1000000,0) x from round_test group by x;
{code}
and put a break point in RoundDecimalExpression.evaluate() and
DecimalDivideExpression.evaluate(), paying particular attention to the scale as
those are executed.
> ROUND over numeric in GROUP BY always returns null
> --------------------------------------------------
>
> Key: PHOENIX-2039
> URL: https://issues.apache.org/jira/browse/PHOENIX-2039
> Project: Phoenix
> Issue Type: Bug
> Reporter: James Taylor
> Labels: newbie
>
> Here's an easy way to reproduce:
> {code}
> create table round_test(k bigint primary key);
> upsert into round_test values(1380603308885);
> select round(k/1000000,0) from round_test;
> +----------------------------------------+
> | (K / 1000000) |
> +----------------------------------------+
> | 1380603 |
> +----------------------------------------+
> select round(k/1000000,0) x from round_test group by x;
> +----------------------------------------+
> | X |
> +----------------------------------------+
> | null |
> +----------------------------------------+
> {code}
> Here's a work around:
> {code}
> select round(cast(k as date),'MINUTE',30) x from round_test group by x;
> +-------------------------+
> | X |
> +-------------------------+
> | 2013-10-01 05:00:00.000 |
> +-------------------------+
> select cast(round(cast(k as date),'MINUTE',30) as bigint) x
> from round_test group by x;
> +------------------------------------------+
> | X |
> +------------------------------------------+
> | 1380603600000 |
> +------------------------------------------+
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
