Repository: phoenix Updated Branches: refs/heads/4.x-HBase-0.98 012db0a0b -> a7da7e387
PHOENIX-2475 Date arithmetic broken for time and timestamp (Ram) Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/a7da7e38 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/a7da7e38 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/a7da7e38 Branch: refs/heads/4.x-HBase-0.98 Commit: a7da7e38769dd8f6daf11229c35c62bb00ca14a2 Parents: 012db0a Author: ramkrishna <ramkrishna.s.vasude...@gmail.com> Authored: Mon Dec 28 13:12:21 2015 +0530 Committer: ramkrishna <ramkrishna.s.vasude...@gmail.com> Committed: Mon Dec 28 13:14:18 2015 +0530 ---------------------------------------------------------------------- .../end2end/ClientTimeArithmeticQueryIT.java | 169 +++++++++++++++++++ .../phoenix/expression/DateAddExpression.java | 2 +- .../expression/DateSubtractExpression.java | 2 +- .../expression/TimestampAddExpression.java | 2 +- .../expression/TimestampSubtractExpression.java | 2 +- 5 files changed, 173 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/a7da7e38/phoenix-core/src/it/java/org/apache/phoenix/end2end/ClientTimeArithmeticQueryIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ClientTimeArithmeticQueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ClientTimeArithmeticQueryIT.java index 00d835c..e617673 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ClientTimeArithmeticQueryIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ClientTimeArithmeticQueryIT.java @@ -42,6 +42,8 @@ import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; +import java.sql.Time; +import java.sql.Timestamp; import java.util.Arrays; import java.util.Collection; import java.util.List; @@ -638,5 +640,172 @@ public class ClientTimeArithmeticQueryIT extends BaseQueryIT { conn.close(); } } + + @Test + public void testAddTimeStamp() throws Exception { + Connection conn; + PreparedStatement stmt; + ResultSet rs; + + long ts = nextTimestamp(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10)); + conn = DriverManager.getConnection(getUrl(), props); + conn.createStatement() + .execute( + "create table timestamp_table (ts timestamp primary key)"); + conn.close(); + + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30)); + conn = DriverManager.getConnection(getUrl(), props); + stmt = conn.prepareStatement("upsert into timestamp_table values (?)"); + stmt.setTimestamp(1, new Timestamp(1995 - 1900, 4, 2, 1, 1, 1, 1)); + stmt.execute(); + conn.commit(); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 60)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts FROM timestamp_table"); + assertTrue(rs.next()); + assertEquals("1995-05-02 01:01:01.000000001",rs.getTimestamp(1).toString()); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 70)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts + 1 FROM timestamp_table"); + assertTrue(rs.next()); + assertEquals("1995-05-03 01:01:01.000000001",rs.getTimestamp(1).toString()); + } + + @Test + public void testSubtractTimeStamp() throws Exception { + Connection conn; + PreparedStatement stmt; + ResultSet rs; + + long ts = nextTimestamp(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10)); + conn = DriverManager.getConnection(getUrl(), props); + conn.createStatement() + .execute( + "create table timestamp_table (ts timestamp primary key)"); + conn.close(); + + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30)); + conn = DriverManager.getConnection(getUrl(), props); + stmt = conn.prepareStatement("upsert into timestamp_table values (?)"); + stmt.setTimestamp(1, new Timestamp(1995 - 1900, 4, 2, 1, 1, 1, 1)); + stmt.execute(); + conn.commit(); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 60)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts FROM timestamp_table"); + assertTrue(rs.next()); + assertEquals("1995-05-02 01:01:01.000000001",rs.getTimestamp(1).toString()); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 70)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts - 1 FROM timestamp_table"); + assertTrue(rs.next()); + assertEquals("1995-05-01 01:01:01.000000001",rs.getTimestamp(1).toString()); + } + + @Test + public void testAddTime() throws Exception { + Connection conn; + PreparedStatement stmt; + ResultSet rs; + + long ts = nextTimestamp(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10)); + conn = DriverManager.getConnection(getUrl(), props); + conn.createStatement() + .execute( + "create table time_table (ts time primary key)"); + conn.close(); + + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30)); + conn = DriverManager.getConnection(getUrl(), props); + stmt = conn.prepareStatement("upsert into time_table values (?)"); + Time time = new Time(1995 - 1900, 4, 2); + stmt.setTime(1, time); + stmt.execute(); + conn.commit(); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 60)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts FROM time_table"); + assertTrue(rs.next()); + assertEquals(time.getTime(),rs.getTimestamp(1).getTime()); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 70)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts + 1 FROM time_table"); + assertTrue(rs.next()); + assertEquals(time.getTime() + TestUtil.MILLIS_IN_DAY,rs.getTimestamp(1).getTime()); + } + + @Test + public void testSubtractTime() throws Exception { + Connection conn; + PreparedStatement stmt; + ResultSet rs; + + long ts = nextTimestamp(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10)); + conn = DriverManager.getConnection(getUrl(), props); + conn.createStatement() + .execute( + "create table time_table (ts time primary key)"); + conn.close(); + + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30)); + conn = DriverManager.getConnection(getUrl(), props); + stmt = conn.prepareStatement("upsert into time_table values (?)"); + Time time = new Time(1995 - 1900, 4, 2); + stmt.setTime(1, time); + stmt.execute(); + conn.commit(); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 60)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts FROM time_table"); + assertTrue(rs.next()); + assertEquals(time.getTime(),rs.getTimestamp(1).getTime()); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 70)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts - 1 FROM time_table"); + assertTrue(rs.next()); + assertEquals(time.getTime() - TestUtil.MILLIS_IN_DAY,rs.getTimestamp(1).getTime()); + } + + @Test + public void testSubtractDate() throws Exception { + Connection conn; + PreparedStatement stmt; + ResultSet rs; + + long ts = nextTimestamp(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10)); + conn = DriverManager.getConnection(getUrl(), props); + conn.createStatement() + .execute( + "create table date_table (ts date primary key)"); + conn.close(); + + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30)); + conn = DriverManager.getConnection(getUrl(), props); + stmt = conn.prepareStatement("upsert into date_table values (?)"); + stmt.setDate(1, new Date(1995 - 1900, 4, 2)); + stmt.execute(); + conn.commit(); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 60)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts FROM date_table"); + assertTrue(rs.next()); + assertEquals("1995-05-02",rs.getDate(1).toString()); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 70)); + conn = DriverManager.getConnection(getUrl(), props); + rs = conn.createStatement().executeQuery("SELECT ts - 1 FROM date_table"); + assertTrue(rs.next()); + assertEquals("1995-05-01",rs.getDate(1).toString()); + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/a7da7e38/phoenix-core/src/main/java/org/apache/phoenix/expression/DateAddExpression.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/DateAddExpression.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/DateAddExpression.java index 1174086..e9ffe90 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/DateAddExpression.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/DateAddExpression.java @@ -56,7 +56,7 @@ public class DateAddExpression extends AddExpression { PDataType type = children.get(i).getDataType(); SortOrder sortOrder = children.get(i).getSortOrder(); if (type == PDecimal.INSTANCE) { - BigDecimal bd = (BigDecimal) PDecimal.INSTANCE.toObject(ptr, sortOrder); + BigDecimal bd = (BigDecimal) PDecimal.INSTANCE.toObject(ptr, type, sortOrder); value = bd.multiply(BD_MILLIS_IN_DAY).longValue(); } else if (type.isCoercibleTo(PLong.INSTANCE)) { value = type.getCodec().decodeLong(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY; http://git-wip-us.apache.org/repos/asf/phoenix/blob/a7da7e38/phoenix-core/src/main/java/org/apache/phoenix/expression/DateSubtractExpression.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/DateSubtractExpression.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/DateSubtractExpression.java index 1d50c6a..2c0bec9 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/DateSubtractExpression.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/DateSubtractExpression.java @@ -52,7 +52,7 @@ public class DateSubtractExpression extends SubtractExpression { PDataType type = children.get(i).getDataType(); SortOrder sortOrder = children.get(i).getSortOrder(); if (type == PDecimal.INSTANCE) { - BigDecimal bd = (BigDecimal) PDecimal.INSTANCE.toObject(ptr, sortOrder); + BigDecimal bd = (BigDecimal) PDecimal.INSTANCE.toObject(ptr, type, sortOrder); value = bd.multiply(BD_MILLIS_IN_DAY).longValue(); } else if (type.isCoercibleTo(PLong.INSTANCE)) { value = type.getCodec().decodeLong(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY; http://git-wip-us.apache.org/repos/asf/phoenix/blob/a7da7e38/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampAddExpression.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampAddExpression.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampAddExpression.java index f4c629b..6d1f214 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampAddExpression.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampAddExpression.java @@ -67,7 +67,7 @@ public class TimestampAddExpression extends AddExpression { if(type == PTimestamp.INSTANCE || type == PUnsignedTimestamp.INSTANCE) { value = (BigDecimal)(PDecimal.INSTANCE.toObject(ptr, type, sortOrder)); } else if (type.isCoercibleTo(PDecimal.INSTANCE)) { - value = (((BigDecimal) PDecimal.INSTANCE.toObject(ptr, sortOrder)).multiply(QueryConstants.BD_MILLIS_IN_DAY)).setScale(6, RoundingMode.HALF_UP); + value = (((BigDecimal) PDecimal.INSTANCE.toObject(ptr, type, sortOrder)).multiply(QueryConstants.BD_MILLIS_IN_DAY)).setScale(6, RoundingMode.HALF_UP); } else if (type.isCoercibleTo(PDouble.INSTANCE)) { value = ((BigDecimal.valueOf(type.getCodec().decodeDouble(ptr, sortOrder))).multiply(QueryConstants.BD_MILLIS_IN_DAY)).setScale(6, RoundingMode.HALF_UP); } else { http://git-wip-us.apache.org/repos/asf/phoenix/blob/a7da7e38/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampSubtractExpression.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampSubtractExpression.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampSubtractExpression.java index 87d634c..115bc16 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampSubtractExpression.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/TimestampSubtractExpression.java @@ -64,7 +64,7 @@ public class TimestampSubtractExpression extends SubtractExpression { if(type == PTimestamp.INSTANCE || type == PUnsignedTimestamp.INSTANCE) { value = (BigDecimal)(PDecimal.INSTANCE.toObject(ptr, type, sortOrder)); } else if (type.isCoercibleTo(PDecimal.INSTANCE)) { - value = (((BigDecimal) PDecimal.INSTANCE.toObject(ptr, sortOrder)).multiply(BD_MILLIS_IN_DAY)).setScale(6, RoundingMode.HALF_UP); + value = (((BigDecimal) PDecimal.INSTANCE.toObject(ptr, type, sortOrder)).multiply(BD_MILLIS_IN_DAY)).setScale(6, RoundingMode.HALF_UP); } else if (type.isCoercibleTo(PDouble.INSTANCE)) { value = ((BigDecimal.valueOf(type.getCodec().decodeDouble(ptr, sortOrder))).multiply(BD_MILLIS_IN_DAY)).setScale(6, RoundingMode.HALF_UP); } else {