Index: ql/src/test/results/clientpositive/show_functions.q.out =================================================================== --- ql/src/test/results/clientpositive/show_functions.q.out (revision 1328164) +++ ql/src/test/results/clientpositive/show_functions.q.out (working copy) @@ -102,6 +102,7 @@ negative ngrams not +nvl or parse_url parse_url_tuple Index: ql/src/test/results/clientpositive/udf_nvl.q.out =================================================================== --- ql/src/test/results/clientpositive/udf_nvl.q.out (revision 0) +++ ql/src/test/results/clientpositive/udf_nvl.q.out (revision 0) @@ -0,0 +1,70 @@ +PREHOOK: query: DESCRIBE FUNCTION nvl +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESCRIBE FUNCTION nvl +POSTHOOK: type: DESCFUNCTION +nvl(value,default_value) - Returns default value if value is null else returns value +PREHOOK: query: DESCRIBE FUNCTION EXTENDED nvl +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESCRIBE FUNCTION EXTENDED nvl +POSTHOOK: type: DESCFUNCTION +nvl(value,default_value) - Returns default value if value is null else returns value +Example: + > SELECT nvl(null,'bla') FROM src LIMIT 1; + bla +PREHOOK: query: EXPLAIN +SELECT NVL( 1 , 2 ) AS COL1, + NVL( NULL, 5 ) AS COL2 +FROM src LIMIT 1 +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT NVL( 1 , 2 ) AS COL1, + NVL( NULL, 5 ) AS COL2 +FROM src LIMIT 1 +POSTHOOK: type: QUERY +ABSTRACT SYNTAX TREE: + (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION NVL 1 2) COL1) (TOK_SELEXPR (TOK_FUNCTION NVL TOK_NULL 5) COL2)) (TOK_LIMIT 1))) + +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 is a root stage + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Alias -> Map Operator Tree: + src + TableScan + alias: src + Select Operator + expressions: + expr: if 1 is null returns2 + type: int + expr: if null is null returns5 + type: int + outputColumnNames: _col0, _col1 + Limit + File Output Operator + compressed: false + GlobalTableId: 0 + table: + input format: org.apache.hadoop.mapred.TextInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat + + Stage: Stage-0 + Fetch Operator + limit: 1 + + +PREHOOK: query: SELECT NVL( 1 , 2 ) AS COL1, + NVL( NULL, 5 ) AS COL2 +FROM src LIMIT 1 +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: SELECT NVL( 1 , 2 ) AS COL1, + NVL( NULL, 5 ) AS COL2 +FROM src LIMIT 1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +1 5 Index: ql/src/test/queries/clientpositive/udf_nvl.q =================================================================== --- ql/src/test/queries/clientpositive/udf_nvl.q (revision 0) +++ ql/src/test/queries/clientpositive/udf_nvl.q (revision 0) @@ -0,0 +1,13 @@ + +DESCRIBE FUNCTION nvl; +DESCRIBE FUNCTION EXTENDED nvl; + +EXPLAIN +SELECT NVL( 1 , 2 ) AS COL1, + NVL( NULL, 5 ) AS COL2 +FROM src LIMIT 1; + +SELECT NVL( 1 , 2 ) AS COL1, + NVL( NULL, 5 ) AS COL2 +FROM src LIMIT 1; + Index: ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java =================================================================== --- ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java (revision 1328164) +++ ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java (working copy) @@ -175,6 +175,7 @@ import org.apache.hadoop.hive.ql.udf.generic.GenericUDFMapKeys; import org.apache.hadoop.hive.ql.udf.generic.GenericUDFMapValues; import org.apache.hadoop.hive.ql.udf.generic.GenericUDFNamedStruct; +import org.apache.hadoop.hive.ql.udf.generic.GenericUDFNvl; import org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPAnd; import org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqual; import org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqualNS; @@ -303,6 +304,7 @@ registerUDF("regexp_replace", UDFRegExpReplace.class, false); registerUDF("regexp_extract", UDFRegExpExtract.class, false); registerUDF("parse_url", UDFParseUrl.class, false); + registerGenericUDF("nvl", GenericUDFNvl.class); registerGenericUDF("split", GenericUDFSplit.class); registerGenericUDF("str_to_map", GenericUDFStringToMap.class); Index: ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNvl.java =================================================================== --- ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNvl.java (revision 0) +++ ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFNvl.java (revision 0) @@ -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.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.exec.UDFArgumentTypeException; +import org.apache.hadoop.hive.ql.metadata.HiveException; +import org.apache.hadoop.hive.ql.udf.generic.GenericUDF; +import org.apache.hadoop.hive.ql.udf.generic.GenericUDFUtils; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; + +@Description(name = "nvl", +value = "_FUNC_(value,default_value) - Returns default value if value is null else returns value", +extended = "Example:\n" ++ " > SELECT _FUNC_(null,'bla') FROM src LIMIT 1;\n" + " bla") +public class GenericUDFNvl extends GenericUDF{ + private GenericUDFUtils.ReturnObjectInspectorResolver returnOIResolver; + private ObjectInspector[] argumentOIs; + + @Override + public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException { + argumentOIs = arguments; + if (arguments.length != 2) { + throw new UDFArgumentLengthException( + "The operator 'NVL' accepts 2 arguments."); + } + returnOIResolver = new GenericUDFUtils.ReturnObjectInspectorResolver(true); + if (!(returnOIResolver.update(arguments[0]) && returnOIResolver + .update(arguments[1]))) { + throw new UDFArgumentTypeException(2, + "The first and seconds arguments of function NLV should have the same type, " + + "but they are different: \"" + arguments[0].getTypeName() + + "\" and \"" + arguments[1].getTypeName() + "\""); + } + return returnOIResolver.get(); + } + + @Override + public Object evaluate(DeferredObject[] arguments) throws HiveException { + Object retVal = returnOIResolver.convertIfNecessary(arguments[0].get(), + argumentOIs[0]); + if (retVal == null ){ + retVal = returnOIResolver.convertIfNecessary(arguments[1].get(), + argumentOIs[1]); + } + return retVal; + } + + @Override + public String getDisplayString(String[] children) { + StringBuilder sb = new StringBuilder(); + sb.append("if "); + sb.append(children[0]); + sb.append(" is null "); + sb.append("returns"); + sb.append(children[1]); + return sb.toString() ; + } + +}