PHOENIX-1237 COALESCE Function - type of second parameter (Vaclav Loffelmann)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/797af91a Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/797af91a Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/797af91a Branch: refs/heads/master Commit: 797af91a6b77e3cbea51ec94ba5a01762efa293c Parents: bba2eee Author: James Taylor <jtay...@salesforce.com> Authored: Thu Sep 18 15:41:27 2014 -0700 Committer: James Taylor <jtay...@salesforce.com> Committed: Thu Sep 18 15:46:29 2014 -0700 ---------------------------------------------------------------------- .../end2end/ClientTimeArithmeticQueryIT.java | 2 +- .../phoenix/end2end/CoalesceFunctionIT.java | 273 ++++++++++++++++++- .../expression/function/CoalesceFunction.java | 40 ++- 3 files changed, 293 insertions(+), 22 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/797af91a/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 9be6fc1..98b233c 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 @@ -537,7 +537,7 @@ public class ClientTimeArithmeticQueryIT extends BaseQueryIT { @Test public void testCoalesceFunction() throws Exception { - String query = "SELECT entity_id FROM aTable WHERE a_integer > 0 and coalesce(X_DECIMAL,0.0) = 0.0"; + String query = "SELECT entity_id FROM aTable WHERE coalesce(X_DECIMAL,0.0) = 0.0"; Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10)); // Execute at timestamp 2 Connection conn = DriverManager.getConnection(getUrl(), props); http://git-wip-us.apache.org/repos/asf/phoenix/blob/797af91a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CoalesceFunctionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CoalesceFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CoalesceFunctionIT.java index 48808ab..57599e6 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CoalesceFunctionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CoalesceFunctionIT.java @@ -19,6 +19,7 @@ package org.apache.phoenix.end2end; import static org.apache.phoenix.util.TestUtil.ROW6; import static org.apache.phoenix.util.TestUtil.ROW7; +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertTrue; @@ -27,37 +28,285 @@ import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; +import java.sql.SQLException; import java.util.Properties; -import org.apache.phoenix.util.PhoenixRuntime; +import org.apache.phoenix.schema.IllegalDataException; +import org.apache.phoenix.util.PropertiesUtil; +import org.junit.Assert; import org.junit.Test; import org.junit.experimental.categories.Category; -@Category(ClientManagedTimeTest.class) -public class CoalesceFunctionIT extends BaseClientManagedTimeIT { +@Category(HBaseManagedTimeTest.class) +public class CoalesceFunctionIT extends BaseHBaseManagedTimeIT { + @Test public void testCoalesce() throws Exception { - long ts = nextTimestamp(); String tenantId = getOrganizationId(); - initATableValues(tenantId, getDefaultSplits(tenantId), null, ts); - Properties props = new Properties(); - props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1 + initATableValues(tenantId, getDefaultSplits(tenantId), getUrl()); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); String query = "SELECT entity_id, a_integer + COALESCE(x_integer,1) FROM ATABLE WHERE organization_id = ? AND a_integer >= 6 AND a_integer <= 7"; PreparedStatement statement = conn.prepareStatement(query); statement.setString(1, tenantId); ResultSet rs = statement.executeQuery(); - - assertTrue (rs.next()); + + assertTrue(rs.next()); assertEquals(ROW6, rs.getString(1)); assertEquals(7, rs.getInt(2)); - - assertTrue (rs.next()); + + assertTrue(rs.next()); assertEquals(ROW7, rs.getString(1)); assertEquals(12, rs.getInt(2)); - + assertFalse(rs.next()); conn.close(); } + @Test + public void coalesceWithSumExplicitLong() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + + String ddl = "CREATE TABLE IF NOT EXISTS TEST_COALESCE(" + + " ID BIGINT NOT NULL, " + + " COUNT BIGINT " + + " CONSTRAINT pk PRIMARY KEY(ID))"; + conn.createStatement().execute(ddl); + + conn.createStatement().execute("UPSERT INTO TEST_COALESCE(ID, COUNT) VALUES(2, null)"); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery( + "SELECT " + + "COALESCE(SUM(COUNT), 0L) " //explicitly def long + + "FROM TEST_COALESCE " + + "GROUP BY ID"); + + assertTrue(rs.next()); + assertEquals(0, rs.getLong(1)); + assertFalse(rs.wasNull()); + } + + @Test + public void coalesceWithSumImplicitLong() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + + String ddl = "CREATE TABLE IF NOT EXISTS TEST_COALESCE(" + + " ID BIGINT NOT NULL, " + + " COUNT BIGINT " + + " CONSTRAINT pk PRIMARY KEY(ID))"; + conn.createStatement().execute(ddl); + + conn.createStatement().execute("UPSERT INTO TEST_COALESCE(ID, COUNT) VALUES(2, null)"); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery( + "SELECT " + + "COALESCE(SUM(COUNT), 0) " // no long def + + "FROM TEST_COALESCE " + + "GROUP BY ID"); + + assertTrue(rs.next()); + assertEquals(0, rs.getLong(1)); + assertFalse(rs.wasNull()); + } + + @Test + public void coalesceWithSecondParamAsExpression() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + + String ddl = "CREATE TABLE IF NOT EXISTS TEST_COALESCE(" + + " ID BIGINT NOT NULL, " + + " COUNT BIGINT " + + " CONSTRAINT pk PRIMARY KEY(ID))"; + conn.createStatement().execute(ddl); + + conn.createStatement().execute("UPSERT INTO TEST_COALESCE(ID, COUNT) VALUES(2, null)"); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery( + "SELECT " + + "COALESCE(SUM(COUNT), SUM(ID)) " // second param as expression + + "FROM TEST_COALESCE " + + "GROUP BY ID"); + + assertTrue(rs.next()); + assertEquals(2, rs.getLong(1)); + assertFalse(rs.wasNull()); + } + + @Test + public void nonTypedSecondParameterLong() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + + String ddl = "CREATE TABLE IF NOT EXISTS TEST_COALESCE(" + + " ID BIGINT NOT NULL, " + + " COUNT BIGINT " //first parameter to coalesce + + " CONSTRAINT pk PRIMARY KEY(ID))"; + conn.createStatement().execute(ddl); + + conn.createStatement().execute("UPSERT INTO TEST_COALESCE(ID, COUNT) VALUES(2, null)"); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery( + "SELECT " + + "COALESCE(NTH_VALUE(COUNT, 100) WITHIN GROUP (ORDER BY COUNT DESC), 0) " //second param is int + + "FROM TEST_COALESCE " + + "GROUP BY ID"); + + assertTrue(rs.next()); + assertEquals(0, rs.getLong(1)); + assertFalse(rs.wasNull()); + } + + @Test + public void nonTypedSecondParameterUnsignedDataTypes() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + + String[] dataTypes = { + "UNSIGNED_INT", + "UNSIGNED_LONG", + "UNSIGNED_TINYINT", + "UNSIGNED_SMALLINT", + "UNSIGNED_FLOAT", + "UNSIGNED_DOUBLE", + "UNSIGNED_TIME", + "UNSIGNED_DATE", + "UNSIGNED_TIMESTAMP" + }; + + for (String dataType : dataTypes) { + + String ddl = "CREATE TABLE IF NOT EXISTS TEST_COALESCE(" + + " ID BIGINT NOT NULL, " + + " COUNT " + dataType //first parameter to coalesce + + " CONSTRAINT pk PRIMARY KEY(ID))"; + conn.createStatement().execute(ddl); + + conn.createStatement().execute("UPSERT INTO TEST_COALESCE(ID, COUNT) VALUES(2, null)"); + conn.commit(); + + //second param to coalesce is signed int + ResultSet rs = conn.createStatement().executeQuery( + "SELECT " + + "COALESCE(NTH_VALUE(COUNT, 100) WITHIN GROUP (ORDER BY COUNT DESC), 1) " + + "FROM TEST_COALESCE " + + "GROUP BY ID"); + + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + assertFalse(rs.wasNull()); + } + } + + @Test + public void testWithNthValueAggregationFunction() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + + String ddl = "CREATE TABLE IF NOT EXISTS TEST_NTH(" + + " ID BIGINT NOT NULL, " + + " DATE TIMESTAMP NOT NULL, " + + " COUNT BIGINT " + + " CONSTRAINT pk PRIMARY KEY(ID, DATE))"; + conn.createStatement().execute(ddl); + + conn.createStatement().execute("UPSERT INTO TEST_NTH(ID, DATE, COUNT) VALUES(1, CURRENT_TIME(), 1)"); + conn.createStatement().execute("UPSERT INTO TEST_NTH(ID, DATE, COUNT) VALUES(1, CURRENT_TIME(), 2)"); + conn.createStatement().execute("UPSERT INTO TEST_NTH(ID, DATE, COUNT) VALUES(2, CURRENT_TIME(), 1)"); + conn.commit(); + + //second param to coalesce is signed int + ResultSet rs = conn.createStatement().executeQuery( + "SELECT " + + " COALESCE(" + + " NTH_VALUE(COUNT, 2000)" // should evaluate null + + " WITHIN GROUP (ORDER BY COUNT DESC)," + + " 0)" + + "FROM TEST_NTH " + + "GROUP BY ID"); + + assertTrue(rs.next()); + assertEquals(0, rs.getLong(1)); + assertFalse(rs.wasNull()); + } + + @Test + public void wrongDataTypeOfSecondParameter() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + + String ddl = "CREATE TABLE IF NOT EXISTS TEST_COALESCE(" + + " ID UNSIGNED_INT NOT NULL, " + + " COUNT UNSIGNED_INT " + + " CONSTRAINT pk PRIMARY KEY(ID))"; + conn.createStatement().execute(ddl); + + conn.createStatement().execute("UPSERT INTO TEST_COALESCE(ID, COUNT) VALUES(2, null)"); + conn.commit(); + + try { + conn.createStatement().executeQuery( + "SELECT " + + "COALESCE(MIN(COUNT), -1) " // invalid value for UNSIGNED_INT + + "FROM TEST_COALESCE " + + "GROUP BY ID"); + + Assert.fail("CANNOT CONVERT TYPE exception expected"); + } catch (SQLException e) { + + } + } + + @Test + public void testImplicitSecondArgCastingException() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + + String ddl = "CREATE TABLE IF NOT EXISTS TEST_COALESCE(" + + " ID INTEGER NOT NULL, " + + " COUNT UNSIGNED_INT " //first parameter to coalesce + + " CONSTRAINT pk PRIMARY KEY(ID))"; + conn.createStatement().execute(ddl); + + conn.createStatement().execute("UPSERT INTO TEST_COALESCE(ID, COUNT) VALUES(-2, null)"); + conn.commit(); + + try { + ResultSet rs = conn.createStatement().executeQuery( + "SELECT " + + "COALESCE(MIN(COUNT), ID) " + + "FROM TEST_COALESCE " + + "GROUP BY ID"); + + assertTrue(rs.next()); + assertEquals(0, rs.getLong(1)); + Assert.fail("Should not cast -2 to UNSIGNED_INT"); + } catch (IllegalDataException e) { + + } + } + + @Test + public void testImplicitSecondArgCasting() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + + String ddl = "CREATE TABLE IF NOT EXISTS TEST_COALESCE(" + + " ID DOUBLE NOT NULL, " + + " COUNT INTEGER " //first parameter to coalesce + + " CONSTRAINT pk PRIMARY KEY(ID))"; + conn.createStatement().execute(ddl); + + conn.createStatement().execute("UPSERT INTO TEST_COALESCE(ID, COUNT) VALUES(2.0, null)"); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery( + "SELECT " + + "COALESCE(MIN(COUNT), ID) " + + "FROM TEST_COALESCE " + + "GROUP BY ID"); + + assertTrue(rs.next()); + assertEquals(2, rs.getLong(1)); + assertFalse(rs.wasNull()); + } + + } http://git-wip-us.apache.org/repos/asf/phoenix/blob/797af91a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/CoalesceFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/CoalesceFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/CoalesceFunction.java index c95d05d..87fc908 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/CoalesceFunction.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/CoalesceFunction.java @@ -23,7 +23,9 @@ import java.util.List; import org.apache.hadoop.hbase.io.ImmutableBytesWritable; import org.apache.phoenix.exception.SQLExceptionCode; import org.apache.phoenix.exception.SQLExceptionInfo; +import org.apache.phoenix.expression.CoerceExpression; import org.apache.phoenix.expression.Expression; +import org.apache.phoenix.hbase.index.util.ImmutableBytesPtr; import org.apache.phoenix.parse.FunctionParseNode.Argument; import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunction; import org.apache.phoenix.schema.PDataType; @@ -31,15 +33,15 @@ import org.apache.phoenix.schema.tuple.Tuple; /** - * + * * Function used to provide an alternative value when the first argument is null. * Usage: * COALESCE(expr1,expr2) - * If expr1 is not null, then it is returned, otherwise expr2 is returned. + * If expr1 is not null, then it is returned, otherwise expr2 is returned. * * TODO: better bind parameter type matching, since arg2 must be coercible * to arg1. consider allowing a common base type? - * + * * @since 0.1 */ @BuiltInFunction(name=CoalesceFunction.NAME, args= { @@ -53,10 +55,25 @@ public class CoalesceFunction extends ScalarFunction { public CoalesceFunction(List<Expression> children) throws SQLException { super(children); - if (!children.get(1).getDataType().isCoercibleTo(children.get(0).getDataType())) { - throw new SQLExceptionInfo.Builder(SQLExceptionCode.CANNOT_CONVERT_TYPE) - .setMessage(getName() + " expected " + children.get(0).getDataType() + ", but got " + children.get(1).getDataType()) - .build().buildException(); + + Expression firstChild = children.get(0); + Expression secondChild = children.get(1); + + if (secondChild.isStateless() && secondChild.isDeterministic()) { // is literal + + ImmutableBytesWritable ptr = new ImmutableBytesPtr(); + secondChild.evaluate(null, ptr); + + if (!secondChild.getDataType().isCoercibleTo(firstChild.getDataType(), secondChild.getDataType().toObject(ptr))) { + throw new SQLExceptionInfo.Builder(SQLExceptionCode.CANNOT_CONVERT_TYPE) + .setMessage(getName() + " expected " + firstChild.getDataType() + ", but got " + secondChild.getDataType()) + .build().buildException(); + } + } else { // second parameter is expression + if (!secondChild.getDataType().isCoercibleTo(getDataType())) { + // cast explicitly + children.add(1, CoerceExpression.create(secondChild, firstChild.getDataType())); + } } } @@ -67,7 +84,12 @@ public class CoalesceFunction extends ScalarFunction { return true; } if (tuple.isImmutable()) { - return children.get(1).evaluate(tuple, ptr); + Expression secondChild = children.get(1); + if (secondChild.evaluate(tuple, ptr)) { + // Coerce the type of the second child to the type of the first child + getDataType().coerceBytes(ptr, secondChild.getDataType(), secondChild.getSortOrder(), getSortOrder()); + return true; + } } return false; } @@ -98,7 +120,7 @@ public class CoalesceFunction extends ScalarFunction { public String getName() { return NAME; } - + @Override public boolean requiresFinalEvaluation() { return true;
