PHOENIX-1756 Add Month() and Second() buildin functions(Alicia Ying Shu)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/6cb6a376 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/6cb6a376 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/6cb6a376 Branch: refs/heads/4.x-HBase-1.x Commit: 6cb6a3766ca3b37ea6d410979d97d6daf7bdd10a Parents: a8b27e3 Author: Rajeshbabu Chintaguntla <[email protected]> Authored: Thu Mar 26 00:40:50 2015 +0530 Committer: Rajeshbabu Chintaguntla <[email protected]> Committed: Thu Mar 26 00:40:50 2015 +0530 ---------------------------------------------------------------------- .../end2end/YearMonthSecondFunctionIT.java | 48 ++++++++++- .../phoenix/expression/ExpressionType.java | 8 +- .../expression/function/MonthFunction.java | 83 ++++++++++++++++++++ .../expression/function/SecondFunction.java | 81 +++++++++++++++++++ 4 files changed, 217 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/6cb6a376/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java index d19314a..da745fe 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java @@ -107,7 +107,7 @@ public class YearMonthSecondFunctionIT extends BaseHBaseManagedTimeIT { "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))"; conn.createStatement().execute(ddl); String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " + - "TO_DATE('2010-03-01 00:00:00'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))"; + "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))"; conn.createStatement().execute(dml); conn.commit(); @@ -122,4 +122,50 @@ public class YearMonthSecondFunctionIT extends BaseHBaseManagedTimeIT { assertEquals(2015, rs.getInt(6)); assertFalse(rs.next()); } + + @Test + public void testMonthFuncAgainstColumns() throws Exception { + String ddl = + "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " + + "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))"; + conn.createStatement().execute(ddl); + String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " + + "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))"; + conn.createStatement().execute(dml); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery("SELECT k1, MONTH(timestamps), MONTH(times), MONTH(unsignedDates), MONTH(unsignedTimestamps), " + + "MONTH(unsignedTimes) FROM T1 where MONTH(dates) = 3"); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + assertEquals(4, rs.getInt(2)); + assertEquals(5, rs.getInt(3)); + assertEquals(6, rs.getInt(4)); + assertEquals(7, rs.getInt(5)); + assertEquals(12, rs.getInt(6)); + assertFalse(rs.next()); + } + + @Test + public void testSecondFuncAgainstColumns() throws Exception { + String ddl = + "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " + + "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))"; + conn.createStatement().execute(ddl); + String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:00:20'), TO_TIME('2008-05-16 10:00:30'), " + + "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))"; + conn.createStatement().execute(dml); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery("SELECT k1, SECOND(dates), SECOND(times), SECOND(unsignedDates), SECOND(unsignedTimestamps), " + + "SECOND(unsignedTimes) FROM T1 where SECOND(timestamps)=20"); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + assertEquals(10, rs.getInt(2)); + assertEquals(30, rs.getInt(3)); + assertEquals(40, rs.getInt(4)); + assertEquals(0, rs.getInt(5)); + assertEquals(50, rs.getInt(6)); + assertFalse(rs.next()); + } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/phoenix/blob/6cb6a376/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java index c871bc5..3f4fea7 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java @@ -50,6 +50,7 @@ import org.apache.phoenix.expression.function.LpadFunction; import org.apache.phoenix.expression.function.MD5Function; import org.apache.phoenix.expression.function.MaxAggregateFunction; import org.apache.phoenix.expression.function.MinAggregateFunction; +import org.apache.phoenix.expression.function.MonthFunction; import org.apache.phoenix.expression.function.NthValueFunction; import org.apache.phoenix.expression.function.PercentRankAggregateFunction; import org.apache.phoenix.expression.function.PercentileContAggregateFunction; @@ -67,6 +68,7 @@ import org.apache.phoenix.expression.function.RoundTimestampExpression; import org.apache.phoenix.expression.function.SQLIndexTypeFunction; import org.apache.phoenix.expression.function.SQLTableTypeFunction; import org.apache.phoenix.expression.function.SQLViewTypeFunction; +import org.apache.phoenix.expression.function.SecondFunction; import org.apache.phoenix.expression.function.SignFunction; import org.apache.phoenix.expression.function.SqlTypeNameFunction; import org.apache.phoenix.expression.function.StddevPopFunction; @@ -194,9 +196,11 @@ public enum ExpressionType { ToTimeFunction(ToTimeFunction.class), ToTimestampFunction(ToTimestampFunction.class), SignFunction(SignFunction.class), - YearFunction(YearFunction.class) + YearFunction(YearFunction.class), + MonthFunction(MonthFunction.class), + SecondFunction(SecondFunction.class) ; - + ExpressionType(Class<? extends Expression> clazz) { this.clazz = clazz; } http://git-wip-us.apache.org/repos/asf/phoenix/blob/6cb6a376/phoenix-core/src/main/java/org/apache/phoenix/expression/function/MonthFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/MonthFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/MonthFunction.java new file mode 100644 index 0000000..5ad6c34 --- /dev/null +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/MonthFunction.java @@ -0,0 +1,83 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.phoenix.expression.function; + +import java.sql.SQLException; +import java.util.List; + +import org.apache.hadoop.hbase.io.ImmutableBytesWritable; +import org.apache.phoenix.expression.Expression; +import org.apache.phoenix.parse.FunctionParseNode.Argument; +import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunction; +import org.apache.phoenix.schema.tuple.Tuple; +import org.apache.phoenix.schema.types.PDataType; +import org.apache.phoenix.schema.types.PInteger; +import org.apache.phoenix.schema.types.PTimestamp; +import org.joda.time.DateTime; + +/** + * + * Implementation of the Month() buildin. Input Date/Timestamp/Time. + * Returns an integer from 1 to 12 representing the month omponent of date + * + */ +@BuiltInFunction(name=MonthFunction.NAME, +args={@Argument(allowedTypes={PTimestamp.class})}) +public class MonthFunction extends ScalarFunction { + public static final String NAME = "MONTH"; + + public MonthFunction() { + } + + public MonthFunction(List<Expression> children) throws SQLException { + super(children); + } + + @Override + public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) { + Expression expression = getChildExpression(); + if (!expression.evaluate(tuple, ptr)) { + return false; + } + if ( ptr.getLength() == 0) { + return true; //means null + } + long dateTime = expression.getDataType().getCodec().decodeLong(ptr, expression.getSortOrder()); + DateTime dt = new DateTime(dateTime); + int month = dt.getMonthOfYear(); + PDataType returnType = getDataType(); + byte[] byteValue = new byte[returnType.getByteSize()]; + returnType.getCodec().encodeInt(month, byteValue, 0); + ptr.set(byteValue); + return true; + } + + @Override + public PDataType getDataType() { + return PInteger.INSTANCE; + } + + @Override + public String getName() { + return NAME; + } + + private Expression getChildExpression() { + return children.get(0); + } +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/6cb6a376/phoenix-core/src/main/java/org/apache/phoenix/expression/function/SecondFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/SecondFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/SecondFunction.java new file mode 100644 index 0000000..5f39786 --- /dev/null +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/SecondFunction.java @@ -0,0 +1,81 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.phoenix.expression.function; + +import java.sql.SQLException; +import java.util.List; + +import org.apache.hadoop.hbase.io.ImmutableBytesWritable; +import org.apache.phoenix.expression.Expression; +import org.apache.phoenix.parse.FunctionParseNode.Argument; +import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunction; +import org.apache.phoenix.schema.tuple.Tuple; +import org.apache.phoenix.schema.types.PDataType; +import org.apache.phoenix.schema.types.PInteger; +import org.apache.phoenix.schema.types.PTimestamp; + +/** + * + * Implementation of the Second() buildin. Input Date/Timestamp/Time. + * Returns an integer from 0 to 59 representing the second component of time + * + */ +@BuiltInFunction(name=SecondFunction.NAME, +args={@Argument(allowedTypes={PTimestamp.class})}) +public class SecondFunction extends ScalarFunction { + public static final String NAME = "SECOND"; + + public SecondFunction() { + } + + public SecondFunction(List<Expression> children) throws SQLException { + super(children); + } + + @Override + public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) { + Expression expression = getChildExpression(); + if (!expression.evaluate(tuple, ptr)) { + return false; + } + if ( ptr.getLength() == 0) { + return true; //means null + } + long dateTime = expression.getDataType().getCodec().decodeLong(ptr, expression.getSortOrder()); + int sec = (int)((dateTime/1000) % 60); + PDataType returnType = getDataType(); + byte[] byteValue = new byte[returnType.getByteSize()]; + returnType.getCodec().encodeInt(sec, byteValue, 0); + ptr.set(byteValue); + return true; + } + + @Override + public PDataType getDataType() { + return PInteger.INSTANCE; + } + + @Override + public String getName() { + return NAME; + } + + private Expression getChildExpression() { + return children.get(0); + } +}
