diff --git ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java index a80feb9..f833118 100644 --- ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java +++ ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java @@ -401,6 +401,7 @@ registerGenericUDF("when", GenericUDFWhen.class); registerGenericUDF("hash", GenericUDFHash.class); registerGenericUDF("coalesce", GenericUDFCoalesce.class); + registerGenericUDF("nullif", GenericUDFNullIf.class); registerGenericUDF("index", GenericUDFIndex.class); registerGenericUDF("in_file", GenericUDFInFile.class); registerGenericUDF("instr", GenericUDFInstr.class); diff --git ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNullIf.java ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNullIf.java new file mode 100644 index 0000000..e185b02 --- /dev/null +++ ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNullIf.java @@ -0,0 +1,91 @@ +/** + * 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.UDFArgumentLengthException; +import org.apache.hadoop.hive.ql.metadata.HiveException; +import org.apache.hadoop.hive.serde2.objectinspector.ConstantObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils; + +/** + * GenericUDF Class for SQL construct "NULLIF(a, b)". + */ +@Description(name = "nullif", + value = "_FUNC_(a1, a2) - Returns null if two expressions are equivalent. Returns first argument if it's not", + extended = "Example:\n" + + " > SELECT _FUNC_(1, 1) FROM src LIMIT 1;\n" + "null") +public class GenericUDFNullIf extends GenericUDF { + + private transient ObjectInspector oi1; + private transient ObjectInspector oi2; + + private transient Object constant; + private transient boolean useConstant; + + @Override + public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException { + if (arguments.length != 2) { + throw new UDFArgumentLengthException("nullif takes two arguments"); + } + oi1 = arguments[0]; + oi2 = arguments[1]; + + // If we return constant object inspector, hive will collapse whole UDF into a constant + if (ObjectInspectorUtils.isConstantObjectInspector(oi1)) { + Object value = ((ConstantObjectInspector)oi1).getWritableConstantValue(); + ObjectInspector standard = ObjectInspectorUtils.getStandardObjectInspector(oi1); + constant = ObjectInspectorUtils.copyToStandardObject(value, standard); + useConstant = true; + return standard; + } + return oi1; + } + + @Override + public Object evaluate(DeferredObject[] arguments) throws HiveException { + Object v1 = arguments[0].get(); + if (v1 == null) { + return useConstant ? constant : v1; + } + Object v2 = arguments[1].get(); + if (v2 != null && ObjectInspectorUtils.compare(v1, oi1, v2, oi2) == 0) { + return null; + } + return useConstant ? constant : v1; + } + + @Override + public String getDisplayString(String[] children) { + StringBuilder sb = new StringBuilder(); + sb.append("NULLIF("); + if (children.length > 0) { + sb.append(children[0]); + for (int i = 1; i < children.length; i++) { + sb.append(","); + sb.append(children[i]); + } + } + sb.append(")"); + return sb.toString(); + } + +} diff --git ql/src/test/queries/clientpositive/udf_nullif.q ql/src/test/queries/clientpositive/udf_nullif.q new file mode 100644 index 0000000..09433cc --- /dev/null +++ ql/src/test/queries/clientpositive/udf_nullif.q @@ -0,0 +1,37 @@ +set hive.fetch.task.conversion=more; + +DESCRIBE FUNCTION NULLIF; +DESCRIBE FUNCTION EXTENDED NULLIF; + +EXPLAIN +SELECT NULLIF(1, 1), + NULLIF(1, 2), + NULLIF(NULL, 2), + NULLIF(1, NULL), + NULLIF('1', '1'), + NULLIF('1', '2'), + NULLIF(NULL, '2'), + NULLIF('1', NULL), + NULLIF(1.0, 1.0), + NULLIF(1.0, 2.0), + NULLIF(NULL, 2.0), + NULLIF(1.0, NULL), + NULLIF(NULL, NULL), + 2 / NULLIF(0.0, 0.0) +FROM src tablesample (1 rows); + +SELECT NULLIF(1, 1), + NULLIF(1, 2), + NULLIF(NULL, 2), + NULLIF(1, NULL), + NULLIF('1', '1'), + NULLIF('1', '2'), + NULLIF(NULL, '2'), + NULLIF('1', NULL), + NULLIF(1.0, 1.0), + NULLIF(1.0, 2.0), + NULLIF(NULL, 2.0), + NULLIF(1.0, NULL), + NULLIF(NULL, NULL), + 2 / NULLIF(0.0, 0.0) +FROM src tablesample (1 rows); diff --git ql/src/test/results/clientpositive/show_functions.q.out ql/src/test/results/clientpositive/show_functions.q.out index 775cc74..7af8926 100644 --- ql/src/test/results/clientpositive/show_functions.q.out +++ ql/src/test/results/clientpositive/show_functions.q.out @@ -123,6 +123,7 @@ noopwithmap noopwithmapstreaming not ntile +nullif nvl or parse_url diff --git ql/src/test/results/clientpositive/udf_nullif.q.out ql/src/test/results/clientpositive/udf_nullif.q.out new file mode 100644 index 0000000..8cb6d63 --- /dev/null +++ ql/src/test/results/clientpositive/udf_nullif.q.out @@ -0,0 +1,103 @@ +PREHOOK: query: DESCRIBE FUNCTION NULLIF +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESCRIBE FUNCTION NULLIF +POSTHOOK: type: DESCFUNCTION +NULLIF(a1, a2) - Returns null if two expressions are equivalent. Returns first argument if it's not +PREHOOK: query: DESCRIBE FUNCTION EXTENDED NULLIF +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESCRIBE FUNCTION EXTENDED NULLIF +POSTHOOK: type: DESCFUNCTION +NULLIF(a1, a2) - Returns null if two expressions are equivalent. Returns first argument if it's not +Synonyms: nullif +Example: + > SELECT NULLIF(1, 1) FROM src LIMIT 1; +null +PREHOOK: query: EXPLAIN +SELECT NULLIF(1, 1), + NULLIF(1, 2), + NULLIF(NULL, 2), + NULLIF(1, NULL), + NULLIF('1', '1'), + NULLIF('1', '2'), + NULLIF(NULL, '2'), + NULLIF('1', NULL), + NULLIF(1.0, 1.0), + NULLIF(1.0, 2.0), + NULLIF(NULL, 2.0), + NULLIF(1.0, NULL), + NULLIF(NULL, NULL), + 2 / NULLIF(0.0, 0.0) +FROM src tablesample (1 rows) +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT NULLIF(1, 1), + NULLIF(1, 2), + NULLIF(NULL, 2), + NULLIF(1, NULL), + NULLIF('1', '1'), + NULLIF('1', '2'), + NULLIF(NULL, '2'), + NULLIF('1', NULL), + NULLIF(1.0, 1.0), + NULLIF(1.0, 2.0), + NULLIF(NULL, 2.0), + NULLIF(1.0, NULL), + NULLIF(NULL, NULL), + 2 / NULLIF(0.0, 0.0) +FROM src tablesample (1 rows) +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-0 is a root stage + +STAGE PLANS: + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + TableScan + alias: src + Row Limit Per Split: 1 + Statistics: Num rows: 0 Data size: 5812 Basic stats: PARTIAL Column stats: COMPLETE + Select Operator + expressions: NULLIF(1,1) (type: int), NULLIF(1,2) (type: int), NULLIF(null,2) (type: void), NULLIF(1,null) (type: int), NULLIF('1','1') (type: string), NULLIF('1','2') (type: string), NULLIF(null,'2') (type: void), NULLIF('1',null) (type: string), NULLIF(1.0,1.0) (type: double), NULLIF(1.0,2.0) (type: double), NULLIF(null,2.0) (type: void), NULLIF(1.0,null) (type: double), NULLIF(null,null) (type: void), (2 / NULLIF(0.0,0.0)) (type: double) + outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13 + Statistics: Num rows: 0 Data size: 5812 Basic stats: PARTIAL Column stats: COMPLETE + ListSink + +PREHOOK: query: SELECT NULLIF(1, 1), + NULLIF(1, 2), + NULLIF(NULL, 2), + NULLIF(1, NULL), + NULLIF('1', '1'), + NULLIF('1', '2'), + NULLIF(NULL, '2'), + NULLIF('1', NULL), + NULLIF(1.0, 1.0), + NULLIF(1.0, 2.0), + NULLIF(NULL, 2.0), + NULLIF(1.0, NULL), + NULLIF(NULL, NULL), + 2 / NULLIF(0.0, 0.0) +FROM src tablesample (1 rows) +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: SELECT NULLIF(1, 1), + NULLIF(1, 2), + NULLIF(NULL, 2), + NULLIF(1, NULL), + NULLIF('1', '1'), + NULLIF('1', '2'), + NULLIF(NULL, '2'), + NULLIF('1', NULL), + NULLIF(1.0, 1.0), + NULLIF(1.0, 2.0), + NULLIF(NULL, 2.0), + NULLIF(1.0, NULL), + NULLIF(NULL, NULL), + 2 / NULLIF(0.0, 0.0) +FROM src tablesample (1 rows) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +NULL 1 NULL 1 NULL 1 NULL 1 NULL 1.0 NULL 1.0 NULL NULL