HIVE-13555 : Add nullif udf (Zoltan Haindrich via Ashutosh Chauhan) Signed-off-by: Ashutosh Chauhan <hashut...@apache.org>
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/c661e947 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/c661e947 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/c661e947 Branch: refs/heads/hive-14535 Commit: c661e947f78dd685e6842a6a1befa55f30070430 Parents: 22bc78e Author: Zoltan Haindrich <k...@rxd.hu> Authored: Tue Sep 6 10:40:58 2016 -0700 Committer: Ashutosh Chauhan <hashut...@apache.org> Committed: Tue Sep 6 10:41:41 2016 -0700 ---------------------------------------------------------------------- .../hadoop/hive/ql/exec/FunctionRegistry.java | 1 + .../hive/ql/udf/generic/GenericUDFNullif.java | 106 ++++++++++++ .../ql/udf/generic/TestGenericUDFNullif.java | 132 +++++++++++++++ ql/src/test/queries/clientpositive/udf_nullif.q | 18 ++ .../results/clientpositive/show_functions.q.out | 1 + .../results/clientpositive/udf_nullif.q.out | 168 +++++++++++++++++++ 6 files changed, 426 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/c661e947/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java ---------------------------------------------------------------------- diff --git a/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java b/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java index 69a18cd..1333c77 100644 --- a/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java +++ b/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java @@ -421,6 +421,7 @@ public final class FunctionRegistry { system.registerGenericUDF("case", GenericUDFCase.class); system.registerGenericUDF("when", GenericUDFWhen.class); + system.registerGenericUDF("nullif", GenericUDFNullif.class); system.registerGenericUDF("hash", GenericUDFHash.class); system.registerGenericUDF("coalesce", GenericUDFCoalesce.class); system.registerGenericUDF("index", GenericUDFIndex.class); http://git-wip-us.apache.org/repos/asf/hive/blob/c661e947/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNullif.java ---------------------------------------------------------------------- diff --git a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNullif.java b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNullif.java new file mode 100644 index 0000000..452c84e --- /dev/null +++ b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNullif.java @@ -0,0 +1,106 @@ +/** + * 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.hadoop.hive.ql.udf.generic; + +import org.apache.hadoop.hive.ql.exec.Description; +import org.apache.hadoop.hive.ql.exec.UDFArgumentException; +import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException; +import org.apache.hadoop.hive.ql.metadata.HiveException; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorUtils; +import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorUtils.PrimitiveGrouping; + +/** + * GenericUDF Class for SQL construct "nullif(a,b)". + */ +@Description( + name = "nullif", + value = "_FUNC_(a1, a2) - shorthand for: case when a1 = a2 then null else a1", + extended = "Example:\n " + + "SELECT _FUNC_(1,1),_FUNC_(1,2)") + +public class GenericUDFNullif extends GenericUDF { + private transient ObjectInspector[] argumentOIs; + private transient GenericUDFUtils.ReturnObjectInspectorResolver returnOIResolver; + + @Override + public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException { + + argumentOIs = arguments; + checkArgsSize(arguments, 2, 2); + + returnOIResolver = new GenericUDFUtils.ReturnObjectInspectorResolver(true); + returnOIResolver.update(arguments[0]); + + boolean isPrimitive = (arguments[0] instanceof PrimitiveObjectInspector); + if (isPrimitive) + { + PrimitiveObjectInspector primitive0 = (PrimitiveObjectInspector) arguments[0]; + PrimitiveObjectInspector primitive1 = (PrimitiveObjectInspector) arguments[1]; + PrimitiveGrouping pcat0 = + PrimitiveObjectInspectorUtils.getPrimitiveGrouping(primitive0.getPrimitiveCategory()); + PrimitiveGrouping pcat1 = + PrimitiveObjectInspectorUtils.getPrimitiveGrouping(primitive1.getPrimitiveCategory()); + + if (pcat0 == PrimitiveGrouping.VOID_GROUP) { + throw new UDFArgumentTypeException(0, + "NULLIF may not accept types belonging to " + pcat0 + " as first argument"); + } + + if (pcat1 != PrimitiveGrouping.VOID_GROUP && pcat0 != pcat1) { + throw new UDFArgumentTypeException(1, + "The expressions after NULLIF should belong to the same category: \"" + pcat0 + + "\" is expected but \"" + pcat1 + "\" is found"); + } + } else { + String typeName0 = arguments[0].getTypeName(); + String typeName1 = arguments[1].getTypeName(); + if (!typeName0.equals(typeName1)) { + throw new UDFArgumentTypeException(1, + "The expressions after NULLIF should all have the same type: \"" + typeName0 + + "\" is expected but \"" + typeName1 + "\" is found"); + } + } + + return returnOIResolver.get(); + } + + @Override + public Object evaluate(DeferredObject[] arguments) throws HiveException { + Object arg0 = arguments[0].get(); + Object arg1 = arguments[1].get(); + if (arg0 == null || arg1 == null) { + return arg0; + } + PrimitiveObjectInspector compareOI = (PrimitiveObjectInspector) returnOIResolver.get(); + if (PrimitiveObjectInspectorUtils.comparePrimitiveObjects( + arg0, compareOI, + returnOIResolver.convertIfNecessary(arg1, argumentOIs[1], false), compareOI)) { + return null; + } + return arg0; + } + + @Override + public String getDisplayString(String[] children) { + return getStandardDisplayString("NULLIF", children, ","); + } + +} http://git-wip-us.apache.org/repos/asf/hive/blob/c661e947/ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFNullif.java ---------------------------------------------------------------------- diff --git a/ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFNullif.java b/ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFNullif.java new file mode 100644 index 0000000..a66e63e --- /dev/null +++ b/ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFNullif.java @@ -0,0 +1,132 @@ +/** + * 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.hadoop.hive.ql.udf.generic; + +import org.apache.hadoop.hive.ql.exec.UDFArgumentException; +import org.apache.hadoop.hive.ql.metadata.HiveException; +import org.apache.hadoop.hive.ql.udf.generic.GenericUDF.DeferredJavaObject; +import org.apache.hadoop.hive.ql.udf.generic.GenericUDF.DeferredObject; +import org.apache.hadoop.hive.serde2.io.ByteWritable; +import org.apache.hadoop.hive.serde2.io.DateWritable; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory; +import org.apache.hadoop.hive.serde2.typeinfo.TypeInfoFactory; +import org.apache.hadoop.io.DoubleWritable; +import org.apache.hadoop.io.Text; +import org.junit.Assert; +import org.junit.Test; + +public class TestGenericUDFNullif { + + @Test + public void testByteTypeEq() throws HiveException { + GenericUDFNullif udf = new GenericUDFNullif(); + + ObjectInspector[] inputOIs = { PrimitiveObjectInspectorFactory.writableByteObjectInspector, + PrimitiveObjectInspectorFactory.writableByteObjectInspector }; + DeferredObject[] args = { new DeferredJavaObject(new ByteWritable((byte) 4)), + new DeferredJavaObject(new ByteWritable((byte) 4)) }; + + PrimitiveObjectInspector oi = (PrimitiveObjectInspector) udf.initialize(inputOIs); + Assert.assertEquals(TypeInfoFactory.byteTypeInfo, oi.getTypeInfo()); + ByteWritable res = (ByteWritable) udf.evaluate(args); + Assert.assertEquals(null, res); + } + + @Test + public void testByteNeq() throws HiveException { + GenericUDFNullif udf = new GenericUDFNullif(); + + ObjectInspector[] inputOIs = { PrimitiveObjectInspectorFactory.writableByteObjectInspector, + PrimitiveObjectInspectorFactory.writableByteObjectInspector }; + DeferredObject[] args = { new DeferredJavaObject(new ByteWritable((byte) 4)), + new DeferredJavaObject(new ByteWritable((byte) 1)) }; + + PrimitiveObjectInspector oi = (PrimitiveObjectInspector) udf.initialize(inputOIs); + Assert.assertEquals(TypeInfoFactory.byteTypeInfo, oi.getTypeInfo()); + ByteWritable res = (ByteWritable) udf.evaluate(args); + Assert.assertEquals(4, res.get()); + } + + @Test(expected = UDFArgumentException.class) + public void testConversionIsPrevented1() throws HiveException { + GenericUDFNullif udf = new GenericUDFNullif(); + + ObjectInspector[] inputOIs = { PrimitiveObjectInspectorFactory.writableStringObjectInspector, + PrimitiveObjectInspectorFactory.writableByteObjectInspector }; + DeferredObject[] args = { new DeferredJavaObject(new Text("4")), + new DeferredJavaObject(new ByteWritable((byte) 4)) }; + + PrimitiveObjectInspector oi = (PrimitiveObjectInspector) udf.initialize(inputOIs); + } + + @Test + public void testConversionInSameGroup() throws HiveException { + GenericUDFNullif udf = new GenericUDFNullif(); + + ObjectInspector[] inputOIs = { PrimitiveObjectInspectorFactory.writableDoubleObjectInspector, + PrimitiveObjectInspectorFactory.writableByteObjectInspector }; + DeferredObject[] args = { new DeferredJavaObject(new DoubleWritable(4.0)), + new DeferredJavaObject(new ByteWritable((byte) 4)) }; + + PrimitiveObjectInspector oi = (PrimitiveObjectInspector) udf.initialize(inputOIs); + } + + @Test(expected = UDFArgumentException.class) + public void testConversionIsPrevented2() throws HiveException { + GenericUDFNullif udf = new GenericUDFNullif(); + + ObjectInspector[] inputOIs = { PrimitiveObjectInspectorFactory.writableByteObjectInspector, + PrimitiveObjectInspectorFactory.writableStringObjectInspector }; + DeferredObject[] args = { new DeferredJavaObject(new ByteWritable((byte) 4)), + new DeferredJavaObject(new Text("4")) }; + + PrimitiveObjectInspector oi = (PrimitiveObjectInspector) udf.initialize(inputOIs); + } + + @Test(expected = UDFArgumentException.class) + public void testNotSupportedArgumentMix() throws HiveException { + GenericUDFNullif udf = new GenericUDFNullif(); + + ObjectInspector[] inputOIs = { PrimitiveObjectInspectorFactory.writableDateObjectInspector, + PrimitiveObjectInspectorFactory.writableByteObjectInspector }; + DeferredObject[] args = { new DeferredJavaObject(new DateWritable(4)), + new DeferredJavaObject(new ByteWritable((byte) 4)) }; + + udf.initialize(inputOIs); + } + + @Test + public void testDateCompareEq() throws HiveException { + GenericUDFNullif udf = new GenericUDFNullif(); + + ObjectInspector[] inputOIs = { + PrimitiveObjectInspectorFactory.writableDateObjectInspector, + PrimitiveObjectInspectorFactory.writableDateObjectInspector }; + DeferredObject[] args = { + new DeferredJavaObject(new DateWritable(4)), + new DeferredJavaObject(new DateWritable(4)) + }; + + PrimitiveObjectInspector oi = (PrimitiveObjectInspector) udf.initialize(inputOIs); + Assert.assertEquals(TypeInfoFactory.dateTypeInfo, oi.getTypeInfo()); + Assert.assertEquals(null, udf.evaluate(args)); + } +} http://git-wip-us.apache.org/repos/asf/hive/blob/c661e947/ql/src/test/queries/clientpositive/udf_nullif.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/udf_nullif.q b/ql/src/test/queries/clientpositive/udf_nullif.q new file mode 100644 index 0000000..8632837 --- /dev/null +++ b/ql/src/test/queries/clientpositive/udf_nullif.q @@ -0,0 +1,18 @@ +DESCRIBE FUNCTION nullif; +DESC FUNCTION EXTENDED nullif; + +explain select nullif(1,2); +explain select nullif(1.0,2.0); +explain select nullif('y','x'); + +select nullif(1,1); +select nullif(2,1); +select nullif('','x'); +select nullif('x','x'); +select nullif('x',''); +select nullif(1.0,2.0); +select nullif(date('2011-11-11'),date('2011-11-11')); +select nullif(date('2011-11-11'),date('2011-11-22')); +select nullif(1,null); + +select nullif(1.0,1); http://git-wip-us.apache.org/repos/asf/hive/blob/c661e947/ql/src/test/results/clientpositive/show_functions.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/show_functions.q.out b/ql/src/test/results/clientpositive/show_functions.q.out index 68c24a6..ff63570 100644 --- a/ql/src/test/results/clientpositive/show_functions.q.out +++ b/ql/src/test/results/clientpositive/show_functions.q.out @@ -151,6 +151,7 @@ noopwithmap noopwithmapstreaming not ntile +nullif nvl or parse_url http://git-wip-us.apache.org/repos/asf/hive/blob/c661e947/ql/src/test/results/clientpositive/udf_nullif.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/udf_nullif.q.out b/ql/src/test/results/clientpositive/udf_nullif.q.out new file mode 100644 index 0000000..95bd0c2 --- /dev/null +++ b/ql/src/test/results/clientpositive/udf_nullif.q.out @@ -0,0 +1,168 @@ +PREHOOK: query: DESCRIBE FUNCTION nullif +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESCRIBE FUNCTION nullif +POSTHOOK: type: DESCFUNCTION +nullif(a1, a2) - shorthand for: case when a1 = a2 then null else a1 +PREHOOK: query: DESC FUNCTION EXTENDED nullif +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESC FUNCTION EXTENDED nullif +POSTHOOK: type: DESCFUNCTION +nullif(a1, a2) - shorthand for: case when a1 = a2 then null else a1 +Example: + SELECT nullif(1,1),nullif(1,2) +PREHOOK: query: explain select nullif(1,2) +PREHOOK: type: QUERY +POSTHOOK: query: explain select nullif(1,2) +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-0 is a root stage + +STAGE PLANS: + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + TableScan + alias: _dummy_table + Row Limit Per Split: 1 + Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: COMPLETE + Select Operator + expressions: 1 (type: int) + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE Column stats: COMPLETE + ListSink + +PREHOOK: query: explain select nullif(1.0,2.0) +PREHOOK: type: QUERY +POSTHOOK: query: explain select nullif(1.0,2.0) +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-0 is a root stage + +STAGE PLANS: + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + TableScan + alias: _dummy_table + Row Limit Per Split: 1 + Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: COMPLETE + Select Operator + expressions: 1 (type: decimal(1,0)) + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 112 Basic stats: COMPLETE Column stats: COMPLETE + ListSink + +PREHOOK: query: explain select nullif('y','x') +PREHOOK: type: QUERY +POSTHOOK: query: explain select nullif('y','x') +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-0 is a root stage + +STAGE PLANS: + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + TableScan + alias: _dummy_table + Row Limit Per Split: 1 + Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: COMPLETE + Select Operator + expressions: 'y' (type: string) + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 85 Basic stats: COMPLETE Column stats: COMPLETE + ListSink + +PREHOOK: query: select nullif(1,1) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif(1,1) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +NULL +PREHOOK: query: select nullif(2,1) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif(2,1) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +2 +PREHOOK: query: select nullif('','x') +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif('','x') +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### + +PREHOOK: query: select nullif('x','x') +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif('x','x') +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +NULL +PREHOOK: query: select nullif('x','') +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif('x','') +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +x +PREHOOK: query: select nullif(1.0,2.0) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif(1.0,2.0) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +1 +PREHOOK: query: select nullif(date('2011-11-11'),date('2011-11-11')) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif(date('2011-11-11'),date('2011-11-11')) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +NULL +PREHOOK: query: select nullif(date('2011-11-11'),date('2011-11-22')) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif(date('2011-11-11'),date('2011-11-22')) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +2011-11-11 +PREHOOK: query: select nullif(1,null) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif(1,null) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +1 +PREHOOK: query: select nullif(1.0,1) +PREHOOK: type: QUERY +PREHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +POSTHOOK: query: select nullif(1.0,1) +POSTHOOK: type: QUERY +POSTHOOK: Input: _dummy_database@_dummy_table +#### A masked pattern was here #### +NULL
