PHOENIX-2018 Implement math build-in function SQRT (Shuxiong Ye)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/e54c99d8 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/e54c99d8 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/e54c99d8 Branch: refs/heads/json Commit: e54c99d8b1ce7bd6118df46209e102e9a86c3782 Parents: 47466e3 Author: James Taylor <jamestay...@apache.org> Authored: Thu Jun 4 14:26:27 2015 -0700 Committer: James Taylor <jamestay...@apache.org> Committed: Thu Jun 4 14:26:27 2015 -0700 ---------------------------------------------------------------------- .../phoenix/end2end/SqrtFunctionEnd2EndIT.java | 143 ++++++++++++++++++ .../phoenix/expression/ExpressionType.java | 4 +- .../function/JavaMathOneArgumentFunction.java | 77 ++++++++++ .../expression/function/SqrtFunction.java | 49 ++++++ .../phoenix/expression/SqrtFunctionTest.java | 150 +++++++++++++++++++ 5 files changed, 422 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/e54c99d8/phoenix-core/src/it/java/org/apache/phoenix/end2end/SqrtFunctionEnd2EndIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SqrtFunctionEnd2EndIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SqrtFunctionEnd2EndIT.java new file mode 100644 index 0000000..50fdd4f --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SqrtFunctionEnd2EndIT.java @@ -0,0 +1,143 @@ +/* + * 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.end2end; + +import static org.apache.phoenix.util.TestUtil.closeStmtAndConn; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; + +import org.apache.phoenix.expression.function.SqrtFunction; +import org.junit.Before; +import org.junit.Test; + +/** + * End to end tests for {@link SqrtFunction} + */ +public class SqrtFunctionEnd2EndIT extends BaseHBaseManagedTimeIT { + + private static final String KEY = "key"; + private static final double ZERO = 1e-8; + + @Before + public void initTable() throws Exception { + Connection conn = null; + PreparedStatement stmt = null; + try { + conn = DriverManager.getConnection(getUrl()); + String ddl; + ddl = "CREATE TABLE testSigned (k VARCHAR NOT NULL PRIMARY KEY, doub DOUBLE, fl FLOAT, inte INTEGER, lon BIGINT, smalli SMALLINT, tinyi TINYINT)"; + conn.createStatement().execute(ddl); + ddl = "CREATE TABLE testUnsigned (k VARCHAR NOT NULL PRIMARY KEY, doub UNSIGNED_DOUBLE, fl UNSIGNED_FLOAT, inte UNSIGNED_INT, lon UNSIGNED_LONG, smalli UNSIGNED_SMALLINT, tinyi UNSIGNED_TINYINT)"; + conn.createStatement().execute(ddl); + conn.commit(); + } finally { + closeStmtAndConn(stmt, conn); + } + } + + private void updateSignedTable(Connection conn, double data) throws Exception { + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO testSigned VALUES (?, ?, ?, ?, ?, ?, ?)"); + stmt.setString(1, KEY); + Double d = Double.valueOf(data); + stmt.setDouble(2, d.doubleValue()); + stmt.setFloat(3, d.floatValue()); + stmt.setInt(4, d.intValue()); + stmt.setLong(5, d.longValue()); + stmt.setShort(6, d.shortValue()); + stmt.setByte(7, d.byteValue()); + stmt.executeUpdate(); + conn.commit(); + } + + private void updateUnsignedTable(Connection conn, double data) throws Exception { + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO testUnsigned VALUES (?, ?, ?, ?, ?, ?, ?)"); + stmt.setString(1, KEY); + Double d = Double.valueOf(data); + stmt.setDouble(2, d.doubleValue()); + stmt.setFloat(3, d.floatValue()); + stmt.setInt(4, d.intValue()); + stmt.setLong(5, d.longValue()); + stmt.setShort(6, d.shortValue()); + stmt.setByte(7, d.byteValue()); + stmt.executeUpdate(); + conn.commit(); + } + + private void testSignedNumberSpec(Connection conn, double data) throws Exception { + updateSignedTable(conn, data); + ResultSet rs = conn.createStatement().executeQuery("SELECT SQRT(doub),SQRT(fl),SQRT(inte),SQRT(lon),SQRT(smalli),SQRT(tinyi) FROM testSigned"); + assertTrue(rs.next()); + Double d = Double.valueOf(data); + assertTrue(Math.abs(rs.getDouble(1) - Math.sqrt(d.doubleValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(2) - Math.sqrt(d.floatValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(3) - Math.sqrt(d.intValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(4) - Math.sqrt(d.longValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(5) - Math.sqrt(d.shortValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(6) - Math.sqrt(d.byteValue())) < ZERO); + assertTrue(!rs.next()); + PreparedStatement stmt = conn.prepareStatement("SELECT k FROM testSigned WHERE SQRT(doub)>0 AND SQRT(fl)>0 AND SQRT(inte)>0 AND SQRT(lon)>0 AND SQRT(smalli)>0 AND SQRT(tinyi)>0"); + rs = stmt.executeQuery(); + if (data > 0) { + assertTrue(rs.next()); + assertEquals(KEY, rs.getString(1)); + } + assertTrue(!rs.next()); + } + + private void testUnsignedNumberSpec(Connection conn, double data) throws Exception { + updateUnsignedTable(conn, data); + ResultSet rs = conn.createStatement().executeQuery("SELECT SQRT(doub),SQRT(fl),SQRT(inte),SQRT(lon),SQRT(smalli),SQRT(tinyi) FROM testUnsigned"); + assertTrue(rs.next()); + Double d = Double.valueOf(data); + assertTrue(Math.abs(rs.getDouble(1) - Math.sqrt(d.doubleValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(2) - Math.sqrt(d.floatValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(3) - Math.sqrt(d.intValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(4) - Math.sqrt(d.longValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(5) - Math.sqrt(d.shortValue())) < ZERO); + assertTrue(Math.abs(rs.getDouble(6) - Math.sqrt(d.byteValue())) < ZERO); + assertTrue(!rs.next()); + PreparedStatement stmt = conn.prepareStatement("SELECT k FROM testUnsigned WHERE SQRT(doub)>0 AND SQRT(fl)>0 AND SQRT(inte)>0 AND SQRT(lon)>0 AND SQRT(smalli)>0 AND SQRT(tinyi)>0"); + rs = stmt.executeQuery(); + if (data > 0) { + assertTrue(rs.next()); + assertEquals(KEY, rs.getString(1)); + } + assertTrue(!rs.next()); + } + + @Test + public void testSignedNumber() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + for (double d : new double[] { 0.0, 1.0, 123.1234}) { + testSignedNumberSpec(conn, d); + } + } + + @Test + public void testUnsignedNumber() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + for (double d : new double[] { 0.0, 1.0, 123.1234 }) { + testUnsignedNumberSpec(conn, d); + } + } +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/e54c99d8/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 d7142e7..684e620 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 @@ -81,6 +81,7 @@ 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.SqrtFunction; import org.apache.phoenix.expression.function.StddevPopFunction; import org.apache.phoenix.expression.function.StddevSampFunction; import org.apache.phoenix.expression.function.StringBasedRegexpReplaceFunction; @@ -231,7 +232,8 @@ public enum ExpressionType { DayOfMonthFunction(DayOfMonthFunction.class), ArrayAppendFunction(ArrayAppendFunction.class), UDFExpression(UDFExpression.class), - ArrayPrependFunction(ArrayPrependFunction.class) + ArrayPrependFunction(ArrayPrependFunction.class), + SqrtFunction(SqrtFunction.class) ; ExpressionType(Class<? extends Expression> clazz) { http://git-wip-us.apache.org/repos/asf/phoenix/blob/e54c99d8/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JavaMathOneArgumentFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JavaMathOneArgumentFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JavaMathOneArgumentFunction.java new file mode 100644 index 0000000..4ea5367 --- /dev/null +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JavaMathOneArgumentFunction.java @@ -0,0 +1,77 @@ +/* + * 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.math.BigDecimal; +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.schema.tuple.Tuple; +import org.apache.phoenix.schema.types.PDataType; +import org.apache.phoenix.schema.types.PDecimal; +import org.apache.phoenix.schema.types.PDouble; + +public abstract class JavaMathOneArgumentFunction extends ScalarFunction { + + public JavaMathOneArgumentFunction() { + } + + public JavaMathOneArgumentFunction(List<Expression> children) throws SQLException { + super(children); + } + + protected abstract double compute(double firstArg); + + @Override + public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) { + Expression childExpr = children.get(0); + PDataType returnType = getDataType(); + if (childExpr.evaluate(tuple, ptr)) { + if (ptr.getLength() == 0) { + return true; + } + double result; + if (childExpr.getDataType() == PDecimal.INSTANCE) { + result = + ((BigDecimal) childExpr.getDataType().toObject(ptr, + childExpr.getSortOrder())).doubleValue(); + } else { + result = + childExpr.getDataType().getCodec() + .decodeDouble(ptr, childExpr.getSortOrder()); + } + ptr.set(new byte[returnType.getByteSize()]); + returnType.getCodec().encodeDouble(compute(result), ptr); + return true; + } else { + return false; + } + } + + @Override + public PDataType getDataType() { + return PDouble.INSTANCE; + } + + @Override + public OrderPreserving preservesOrder() { + return OrderPreserving.YES; + } +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/e54c99d8/phoenix-core/src/main/java/org/apache/phoenix/expression/function/SqrtFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/SqrtFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/SqrtFunction.java new file mode 100644 index 0000000..bb5376e --- /dev/null +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/SqrtFunction.java @@ -0,0 +1,49 @@ +/* + * 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.phoenix.expression.Expression; +import org.apache.phoenix.parse.FunctionParseNode.Argument; +import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunction; +import org.apache.phoenix.schema.types.PDecimal; + +@BuiltInFunction(name = SqrtFunction.NAME, args = { @Argument(allowedTypes = { PDecimal.class }) }) +public class SqrtFunction extends JavaMathOneArgumentFunction { + + public static final String NAME = "SQRT"; + + public SqrtFunction() { + } + + public SqrtFunction(List<Expression> children) throws SQLException { + super(children); + } + + @Override + public String getName() { + return NAME; + } + + @Override + protected double compute(double firstArg) { + return Math.sqrt(firstArg); + } +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/e54c99d8/phoenix-core/src/test/java/org/apache/phoenix/expression/SqrtFunctionTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/expression/SqrtFunctionTest.java b/phoenix-core/src/test/java/org/apache/phoenix/expression/SqrtFunctionTest.java new file mode 100644 index 0000000..6b19ad8 --- /dev/null +++ b/phoenix-core/src/test/java/org/apache/phoenix/expression/SqrtFunctionTest.java @@ -0,0 +1,150 @@ +/* + * 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; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; + +import java.math.BigDecimal; +import java.sql.SQLException; +import java.util.List; +import java.util.Random; + +import org.apache.hadoop.hbase.io.ImmutableBytesWritable; +import org.apache.phoenix.expression.function.SqrtFunction; +import org.apache.phoenix.schema.SortOrder; +import org.apache.phoenix.schema.types.PDecimal; +import org.apache.phoenix.schema.types.PDouble; +import org.apache.phoenix.schema.types.PFloat; +import org.apache.phoenix.schema.types.PInteger; +import org.apache.phoenix.schema.types.PLong; +import org.apache.phoenix.schema.types.PNumericType; +import org.apache.phoenix.schema.types.PSmallint; +import org.apache.phoenix.schema.types.PTinyint; +import org.apache.phoenix.schema.types.PUnsignedDouble; +import org.apache.phoenix.schema.types.PUnsignedFloat; +import org.apache.phoenix.schema.types.PUnsignedInt; +import org.apache.phoenix.schema.types.PUnsignedLong; +import org.junit.Test; + +import com.google.common.collect.Lists; + +/** + * Unit tests for {@link SqrtFunction} + */ +public class SqrtFunctionTest { + private static final double ZERO = 1e-9; + + private static boolean twoDoubleEquals(double a, double b) { + if (Double.isNaN(a) ^ Double.isNaN(b)) return false; + if (Double.isNaN(a)) return true; + if (Double.isInfinite(a) ^ Double.isInfinite(b)) return false; + if (Double.isInfinite(a)) { + if ((a > 0) ^ (b > 0)) return false; + else return true; + } + if (Math.abs(a - b) <= ZERO) { + return true; + } else { + return false; + } + } + + private static boolean testExpression(LiteralExpression literal, double expected) + throws SQLException { + List<Expression> expressions = Lists.newArrayList((Expression) literal); + Expression sqrtFunction = new SqrtFunction(expressions); + ImmutableBytesWritable ptr = new ImmutableBytesWritable(); + boolean ret = sqrtFunction.evaluate(null, ptr); + if (ret) { + Double result = + (Double) sqrtFunction.getDataType().toObject(ptr, sqrtFunction.getSortOrder()); + assertTrue(twoDoubleEquals(result.doubleValue(), expected)); + } + return ret; + } + + private static void test(Number value, PNumericType dataType, double expected) + throws SQLException { + LiteralExpression literal; + literal = LiteralExpression.newConstant(value, dataType, SortOrder.ASC); + boolean ret1 = testExpression(literal, expected); + literal = LiteralExpression.newConstant(value, dataType, SortOrder.DESC); + boolean ret2 = testExpression(literal, expected); + assertEquals(ret1, ret2); + } + + private static void testBatch(Number[] value, PNumericType dataType) throws SQLException { + double[] expected = new double[value.length]; + for (int i = 0; i < expected.length; ++i) { + expected[i] = Math.sqrt(value[i].doubleValue()); + } + assertEquals(value.length, expected.length); + for (int i = 0; i < value.length; ++i) { + test(value[i], dataType, expected[i]); + } + } + + @Test + public void testSqrtFunction() throws Exception { + Random random = new Random(); + + testBatch( + new BigDecimal[] { BigDecimal.valueOf(1.0), BigDecimal.valueOf(0.0), + BigDecimal.valueOf(-1.0), BigDecimal.valueOf(123.1234), + BigDecimal.valueOf(-123.1234), BigDecimal.valueOf(random.nextDouble()), + BigDecimal.valueOf(random.nextDouble()) }, PDecimal.INSTANCE); + + testBatch(new Float[] { 1.0f, 0.0f, -1.0f, 123.1234f, -123.1234f, random.nextFloat(), + random.nextFloat() }, PFloat.INSTANCE); + + testBatch(new Float[] { 1.0f, 0.0f, -1.0f, 123.1234f, -123.1234f, random.nextFloat(), + random.nextFloat() }, PFloat.INSTANCE); + + testBatch(new Float[] { 1.0f, 0.0f, 123.1234f, }, PUnsignedFloat.INSTANCE); + + testBatch( + new Double[] { 1.0, 0.0, -1.0, 123.1234, -123.1234, random.nextDouble(), + random.nextDouble() }, PDouble.INSTANCE); + + testBatch(new Double[] { 1.0, 0.0, 123.1234, }, PUnsignedDouble.INSTANCE); + + testBatch( + new Long[] { 1L, 0L, -1L, Long.MAX_VALUE, Long.MIN_VALUE, 123L, -123L, + random.nextLong(), random.nextLong() }, PLong.INSTANCE); + + testBatch(new Long[] { 1L, 0L, Long.MAX_VALUE, 123L }, PUnsignedLong.INSTANCE); + + testBatch( + new Integer[] { 1, 0, -1, Integer.MAX_VALUE, Integer.MIN_VALUE, 123, -123, + random.nextInt(), random.nextInt() }, PInteger.INSTANCE); + + testBatch(new Integer[] { 1, 0, Integer.MAX_VALUE, 123 }, PUnsignedInt.INSTANCE); + + testBatch(new Short[] { (short) 1, (short) 0, (short) -1, Short.MAX_VALUE, Short.MIN_VALUE, + (short) 123, (short) -123 }, PSmallint.INSTANCE); + + testBatch(new Short[] { (short) 1, (short) 0, Short.MAX_VALUE, (short) 123 }, + PSmallint.INSTANCE); + + testBatch(new Byte[] { (byte) 1, (byte) 0, (byte) -1, Byte.MAX_VALUE, Byte.MIN_VALUE, + (byte) 123, (byte) -123 }, PTinyint.INSTANCE); + + testBatch(new Byte[] { (byte) 1, (byte) 0, Byte.MAX_VALUE, (byte) 123 }, PTinyint.INSTANCE); + } +}