You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "ramkrishna.s.vasudevan (JIRA)" <ji...@apache.org> on 2015/12/29 13:29:49 UTC
[jira] [Commented] (PHOENIX-2039) ROUND over numeric in GROUP BY
always returns null
[ https://issues.apache.org/jira/browse/PHOENIX-2039?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15073854#comment-15073854 ]
ramkrishna.s.vasudevan commented on PHOENIX-2039:
-------------------------------------------------
Added this test case
{code}
@Test
public void testRoundOffFunction() throws SQLException {
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
Connection conn = DriverManager.getConnection(getUrl(), props);
String ddl = "create table round_test(k bigint primary key)";
conn.createStatement().execute(ddl);
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
PreparedStatement stmt = conn.prepareStatement("upsert into round_test values(1380603308885)");
stmt.execute();
conn.commit();
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
ResultSet rs;
stmt = conn.prepareStatement("select round(k/1000000,0) from round_test");
rs = stmt.executeQuery();
assertTrue(rs.next());
assertEquals(1380603, rs.getLong(1));
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("select round(k/1000000,0) x from round_test group by x");
rs = stmt.executeQuery();
assertTrue(rs.next());
assertEquals(1380603, rs.getLong(1));
}
{code}
It seems to work fine without any issues. Am I missing anything here? The round with GROUP BY does not return null here.
> 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
> Fix For: 4.7.0
>
>
> 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)