Repository: phoenix Updated Branches: refs/heads/calcite 2ee0293c3 -> 50c797ffa
PHOENIX-3122 Date/Time/Timestamp bidirectional conversion between Avatica and Phoenix Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/50c797ff Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/50c797ff Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/50c797ff Branch: refs/heads/calcite Commit: 50c797ffadff281e52375128f5e273eae85815da Parents: 2ee0293 Author: maryannxue <[email protected]> Authored: Wed Jul 27 16:23:49 2016 -0400 Committer: maryannxue <[email protected]> Committed: Wed Jul 27 16:23:49 2016 -0400 ---------------------------------------------------------------------- .../apache/phoenix/calcite/BaseCalciteIT.java | 31 +++++++++-- .../apache/phoenix/calcite/CalciteDMLIT.java | 28 ++++++++++ .../org/apache/phoenix/calcite/CalciteIT.java | 20 ++++--- .../calcite/jdbc/PhoenixCalciteFactory.java | 4 +- .../apache/phoenix/calcite/CalciteRuntime.java | 56 +++++++++++++++++--- 5 files changed, 120 insertions(+), 19 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/50c797ff/phoenix-core/src/it/java/org/apache/phoenix/calcite/BaseCalciteIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/calcite/BaseCalciteIT.java b/phoenix-core/src/it/java/org/apache/phoenix/calcite/BaseCalciteIT.java index 98a3b01..7a16ed1 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/calcite/BaseCalciteIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/calcite/BaseCalciteIT.java @@ -27,13 +27,15 @@ import java.io.File; import java.io.FileWriter; import java.io.PrintWriter; import java.sql.Connection; +import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.sql.Time; +import java.sql.Timestamp; import java.text.DecimalFormat; -import java.util.Arrays; import java.util.List; import java.util.Map; import java.util.Properties; @@ -305,7 +307,19 @@ public class BaseCalciteIT extends BaseClientManagedTimeIT { } if (obj.getClass().isArray()) { - return Arrays.toString((Object[]) obj); + final StringBuilder buf = new StringBuilder("["); + for (Object o : (Object[]) obj) { + if (buf.length() > 1) { + buf.append(", "); + } + String s = o.toString(); + // Remove nano suffix + if (o instanceof Timestamp) { + s = s.substring(0, s.lastIndexOf('.')); + } + buf.append(s); + } + return buf.append("]").toString(); } return obj; @@ -399,21 +413,30 @@ public class BaseCalciteIT extends BaseClientManagedTimeIT { try { conn.createStatement().execute( "CREATE TABLE " + SCORES_TABLE_NAME - + "(student_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, scores INTEGER[] CONSTRAINT pk PRIMARY KEY (student_id, subject_id))"); + + "(student_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, scores INTEGER[], exam_date DATE[], exam_time TIME[], exam_timestamp TIMESTAMP[] CONSTRAINT pk PRIMARY KEY (student_id, subject_id))"); PreparedStatement stmt = conn.prepareStatement( "UPSERT INTO " + SCORES_TABLE_NAME - + " VALUES(?, ?, ?)"); + + " VALUES(?, ?, ?, ?, ?, ?)"); stmt.setInt(1, 1); stmt.setInt(2, 1); stmt.setArray(3, conn.createArrayOf("INTEGER", new Integer[] {85, 80, 82})); + stmt.setArray(4, conn.createArrayOf("DATE", new Date[] {Date.valueOf("2016-3-22"), Date.valueOf("2016-5-23"), Date.valueOf("2016-7-24")})); + stmt.setArray(5, conn.createArrayOf("TIME", new Time[] {Time.valueOf("15:30:28"), Time.valueOf("13:26:50"), Time.valueOf("16:20:00")})); + stmt.setArray(6, conn.createArrayOf("TIMESTAMP", new Timestamp[] {Timestamp.valueOf("2016-3-22 15:30:28"), Timestamp.valueOf("2016-5-23 13:26:50"), Timestamp.valueOf("2016-7-24 16:20:00")})); stmt.execute(); stmt.setInt(1, 2); stmt.setInt(2, 1); stmt.setArray(3, null); + stmt.setArray(4, null); + stmt.setArray(5, null); + stmt.setArray(6, null); stmt.execute(); stmt.setInt(1, 3); stmt.setInt(2, 2); stmt.setArray(3, conn.createArrayOf("INTEGER", new Integer[] {87, 88, 80})); + stmt.setArray(4, conn.createArrayOf("DATE", new Date[] {Date.valueOf("2016-3-22"), Date.valueOf("2016-5-23"), Date.valueOf("2016-7-24")})); + stmt.setArray(5, conn.createArrayOf("TIME", new Time[] {Time.valueOf("15:30:16"), Time.valueOf("13:26:52"), Time.valueOf("16:20:40")})); + stmt.setArray(6, conn.createArrayOf("TIMESTAMP", new Timestamp[] {Timestamp.valueOf("2016-3-22 15:30:16"), Timestamp.valueOf("2016-5-23 13:26:52"), Timestamp.valueOf("2016-7-24 16:20:40")})); stmt.execute(); conn.commit(); } catch (TableAlreadyExistsException e) { http://git-wip-us.apache.org/repos/asf/phoenix/blob/50c797ff/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteDMLIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteDMLIT.java b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteDMLIT.java index 94cd763..da60b15 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteDMLIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteDMLIT.java @@ -5,8 +5,11 @@ import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertTrue; +import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; +import java.sql.Time; +import java.sql.Timestamp; import java.util.Properties; import org.junit.Before; @@ -122,4 +125,29 @@ public class CalciteDMLIT extends BaseCalciteIT { .resultIs(new Object[][] {}) .close(); } + + @Test public void testDateTimeTimestampAsBindVariables() throws Exception { + start(PROPS).sql("create table t0(a date not null, b time not null, c timestamp constraint pk primary key(a, b))").execute().close(); + Sql sql = start(PROPS).sql("upsert into t0 values(?, ?, ?)"); + PreparedStatement stmt = sql.prepareStatement(); + stmt.setDate(1, Date.valueOf("2016-07-12")); + stmt.setTime(2, Time.valueOf("12:30:28")); + stmt.setTimestamp(3, Timestamp.valueOf("2016-7-12 12:30:28")); + stmt.executeUpdate(); + stmt.setDate(1, Date.valueOf("2016-07-12")); + stmt.setTime(2, Time.valueOf("12:34:09")); + stmt.setTimestamp(3, Timestamp.valueOf("2016-7-12 12:34:09")); + stmt.executeUpdate(); + stmt.setDate(1, Date.valueOf("2016-07-16")); + stmt.setTime(2, Time.valueOf("09:20:08")); + stmt.setTimestamp(3, Timestamp.valueOf("2016-7-16 09:20:08")); + stmt.executeUpdate(); + sql.close(); + start(PROPS).sql("select * from t0") + .resultIs(new Object[][]{ + {Date.valueOf("2016-07-12"), Time.valueOf("12:30:28"), Timestamp.valueOf("2016-7-12 12:30:28")}, + {Date.valueOf("2016-07-12"), Time.valueOf("12:34:09"), Timestamp.valueOf("2016-7-12 12:34:09")}, + {Date.valueOf("2016-07-16"), Time.valueOf("09:20:08"), Timestamp.valueOf("2016-7-16 09:20:08")}}) + .close(); + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/50c797ff/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java index a786c06..e65d386 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java @@ -23,7 +23,9 @@ import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME; import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME; import java.sql.Connection; +import java.sql.Date; import java.sql.DriverManager; +import java.sql.Time; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.Properties; @@ -72,17 +74,21 @@ public class CalciteIT extends BaseCalciteIT { {"00D300000000XHP", "00A423122312312", "a"}}) .close(); - // FIXME: Should be 14:22:56 instead. Wrong due to time zone. start(false, 1000f).sql("select \"DATE\" from " + JOIN_ORDER_TABLE_FULL_NAME + " where \"order_id\" = '000000000000001'") .resultIs(0, new Object[][]{ - {new Timestamp(format.parse("2013-11-22 19:22:56").getTime())}}) + {new Timestamp(format.parse("2013-11-22 14:22:56").getTime())}}) .close(); - start(false, 1000f).sql("select student_id, scores from " + SCORES_TABLE_NAME) + start(false, 1000f).sql("select \"DATE\" from " + JOIN_CUSTOMER_TABLE_FULL_NAME + " where \"customer_id\" = '0000000001'") + .resultIs(0, new Object[][]{ + {Date.valueOf("2013-11-01")}}) + .close(); + + start(false, 1000f).sql("select student_id, scores, exam_date, exam_time, exam_timestamp from " + SCORES_TABLE_NAME) .resultIs(0, new Object[][] { - {1, new Integer[] {85, 80, 82}}, - {2, null}, - {3, new Integer[] {87, 88, 80}}}) + {1, new Integer[] {85, 80, 82}, new Date[] {Date.valueOf("2016-3-22"), Date.valueOf("2016-5-23"), Date.valueOf("2016-7-24")}, new Time[] {Time.valueOf("15:30:28"), Time.valueOf("13:26:50"), Time.valueOf("16:20:00")}, new Timestamp[] {Timestamp.valueOf("2016-3-22 15:30:28"), Timestamp.valueOf("2016-5-23 13:26:50"), Timestamp.valueOf("2016-7-24 16:20:00")}}, + {2, null, null, null, null}, + {3, new Integer[] {87, 88, 80}, new Date[] {Date.valueOf("2016-3-22"), Date.valueOf("2016-5-23"), Date.valueOf("2016-7-24")}, new Time[] {Time.valueOf("15:30:16"), Time.valueOf("13:26:52"), Time.valueOf("16:20:40")}, new Timestamp[] {Timestamp.valueOf("2016-3-22 15:30:16"), Timestamp.valueOf("2016-5-23 13:26:52"), Timestamp.valueOf("2016-7-24 16:20:40")}}}) .close(); } @@ -933,7 +939,7 @@ public class CalciteIT extends BaseCalciteIT { .close(); start(false, 1000f).sql("SELECT s.student_id, t.score FROM " + SCORES_TABLE_NAME + " s, UNNEST((SELECT scores FROM " + SCORES_TABLE_NAME + " s2 where s.student_id = s2.student_id)) AS t(score)") .explainIs("PhoenixToEnumerableConverter\n" + - " PhoenixClientProject(STUDENT_ID=[$0], SCORE=[$3])\n" + + " PhoenixClientProject(STUDENT_ID=[$0], SCORE=[$6])\n" + " PhoenixCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{0}])\n" + " PhoenixTableScan(table=[[phoenix, SCORES]])\n" + " PhoenixUncollect\n" + http://git-wip-us.apache.org/repos/asf/phoenix/blob/50c797ff/phoenix-core/src/main/java/org/apache/calcite/jdbc/PhoenixCalciteFactory.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/calcite/jdbc/PhoenixCalciteFactory.java b/phoenix-core/src/main/java/org/apache/calcite/jdbc/PhoenixCalciteFactory.java index a1e1b08..b0557b7 100644 --- a/phoenix-core/src/main/java/org/apache/calcite/jdbc/PhoenixCalciteFactory.java +++ b/phoenix-core/src/main/java/org/apache/calcite/jdbc/PhoenixCalciteFactory.java @@ -7,6 +7,7 @@ import java.sql.ResultSetMetaData; import java.sql.RowId; import java.sql.SQLException; import java.sql.SQLXML; +import java.util.Calendar; import java.util.List; import java.util.Map; import java.util.Properties; @@ -117,8 +118,9 @@ public class PhoenixCalciteFactory extends CalciteFactory { AvaticaStatement statement = lookupStatement(handle); final List<TypedValue> parameterValues = TROJAN.getParameterValues(statement); + final Calendar calendar = Calendar.getInstance(); for (Ord<TypedValue> o : Ord.zip(parameterValues)) { - map.put("?" + o.i, o.e.toLocal()); + map.put("?" + o.i, o.e.toJdbc(calendar)); } ImmutableList<RuntimeContext> ctxList = runtimeContextMap.get(handle); if (ctxList == null) { http://git-wip-us.apache.org/repos/asf/phoenix/blob/50c797ff/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteRuntime.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteRuntime.java b/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteRuntime.java index df036a9..8d89cca 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteRuntime.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteRuntime.java @@ -37,7 +37,9 @@ import org.apache.phoenix.schema.types.PhoenixArray; import java.sql.SQLException; import java.sql.Timestamp; +import java.util.Calendar; import java.util.Iterator; +import java.util.concurrent.TimeUnit; /** * Methods used by code generated by Calcite. @@ -71,6 +73,7 @@ public class CalciteRuntime { ResultIterator iterator = null; Object current; final ImmutableBytesWritable ptr = new ImmutableBytesWritable(); + final Calendar calendar = Calendar.getInstance(); void init() throws SQLException { final StatementContext context = plan.getContext(); @@ -120,28 +123,32 @@ public class CalciteRuntime { private Object project(Tuple tuple, ColumnProjector projector) throws SQLException { @SuppressWarnings("rawtypes") - PDataType type = projector.getExpression().getDataType(); + final PDataType type = projector.getExpression().getDataType(); + @SuppressWarnings("rawtypes") + final PDataType targetType; if (PDataType.equalsAny( type, PUnsignedFloat.INSTANCE, PFloat.INSTANCE)) { - type = PDouble.INSTANCE; + targetType = PDouble.INSTANCE; } else if (PDataType.equalsAny( type, PUnsignedDate.INSTANCE, PDate.INSTANCE, PUnsignedTime.INSTANCE, PTime.INSTANCE)) { - type = PLong.INSTANCE; + targetType = PLong.INSTANCE; } else if (PDataType.equalsAny( type, PUnsignedDateArray.INSTANCE, PDateArray.INSTANCE, PUnsignedTimeArray.INSTANCE, PTimeArray.INSTANCE)) { - type = PLongArray.INSTANCE; + targetType = PLongArray.INSTANCE; + } else { + targetType = type; } - Object value = projector.getValue(tuple, type, ptr); + Object value = projector.getValue(tuple, targetType, ptr); if (value != null) { if (type.isArrayType()) { value = ((PhoenixArray) value).getArray(); @@ -150,7 +157,7 @@ public class CalciteRuntime { type, PUnsignedTimestamp.INSTANCE, PTimestamp.INSTANCE)) { - value = ((Timestamp) value).getTime(); + value = getAdjustedTime(((Timestamp) value).getTime()); } else if (PDataType.equalsAny( type, PUnsignedTimestampArray.INSTANCE, @@ -158,7 +165,38 @@ public class CalciteRuntime { Timestamp[] array = (Timestamp[]) value; long[] newArray = new long[array.length]; for (int i = 0; i < array.length; i++) { - newArray[i] = array[i].getTime(); + newArray[i] = getAdjustedTime(array[i].getTime()); + } + value = newArray; + // TODO to be removed after calcite changes Date/Time representation. + } else if (PDataType.equalsAny( + type, + PUnsignedDate.INSTANCE, + PDate.INSTANCE)) { + value = TimeUnit.MILLISECONDS.toDays(getAdjustedTime((Long) value)); + } else if (PDataType.equalsAny( + type, + PUnsignedTime.INSTANCE, + PTime.INSTANCE)) { + value = (int) getAdjustedTime((Long) value); + } else if (PDataType.equalsAny( + type, + PUnsignedDateArray.INSTANCE, + PDateArray.INSTANCE)) { + long[] array = (long[]) value; + int[] newArray = new int[array.length]; + for (int i = 0; i < array.length; i++) { + newArray[i] = (int) TimeUnit.MILLISECONDS.toDays(getAdjustedTime(array[i])); + } + value = newArray; + } else if (PDataType.equalsAny( + type, + PUnsignedTimeArray.INSTANCE, + PTimeArray.INSTANCE)) { + long[] array = (long[]) value; + int[] newArray = new int[array.length]; + for (int i = 0; i < array.length; i++) { + newArray[i] = (int) getAdjustedTime(array[i]); } value = newArray; } @@ -166,6 +204,10 @@ public class CalciteRuntime { return value; } + + private long getAdjustedTime(long v) { + return (v + calendar.getTimeZone().getOffset(v)); + } @Override public void reset() {
