This is an automated email from the ASF dual-hosted git repository.
krisztiankasa pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new 38dc6b61a69 HIVE-28792: Wrong results when query has function call
with char parameter type in case expression (Krisztian Kasa, reviewed by
Stamatis Zampetakis)
38dc6b61a69 is described below
commit 38dc6b61a695a799c447d6f90503742dcc22e7bb
Author: Krisztian Kasa <[email protected]>
AuthorDate: Wed Mar 12 05:52:02 2025 +0100
HIVE-28792: Wrong results when query has function call with char parameter
type in case expression (Krisztian Kasa, reviewed by Stamatis Zampetakis)
---
.../hadoop/hive/ql/parse/IdentifiersParser.g | 41 ++++-
.../apache/hadoop/hive/ql/parse/TestParseCase.java | 91 ++++++++++
.../hive/ql/exec/ExprNodeGenericFuncEvaluator.java | 6 +-
.../hadoop/hive/ql/exec/FunctionRegistry.java | 1 -
.../hive/ql/exec/vector/VectorizationContext.java | 7 +-
.../ql/optimizer/ConstantPropagateProcFactory.java | 73 +-------
.../ql/optimizer/calcite/HiveRexExecutorImpl.java | 2 +-
.../calcite/translator/RexNodeConverter.java | 72 +++-----
.../hive/ql/optimizer/physical/Vectorizer.java | 1 -
.../hive/ql/parse/type/HiveFunctionHelper.java | 2 +-
.../hadoop/hive/ql/udf/generic/GenericUDFCase.java | 167 ------------------
.../calcite/translator/TestRexNodeConverter.java | 186 --------------------
.../clientpositive/cbo_case_when_type_conversion.q | 40 +++++
ql/src/test/queries/clientpositive/udf_case.q | 3 -
.../llap/cbo_case_when_type_conversion.q.out | 189 +++++++++++++++++++++
.../clientpositive/llap/order_by_expr_2.q.out | 4 +-
.../clientpositive/llap/show_functions.q.out | 4 -
.../results/clientpositive/llap/udf_case.q.out | 24 ---
.../clientpositive/llap/udf_case_thrift.q.out | 2 +-
.../perf/tpcds30tb/tez/cbo_query39.q.out | 8 +-
20 files changed, 387 insertions(+), 536 deletions(-)
diff --git
a/parser/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g
b/parser/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g
index 7dad9dcdc31..3d219dbe294 100644
--- a/parser/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g
+++ b/parser/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g
@@ -341,24 +341,47 @@ castExpression
-> ^(TOK_FUNCTION {adaptor.create(Identifier, "cast_format")}
NumberLiteral[Integer.toString(((CommonTree)toType.getTree()).token.getType())]
expression StringLiteral
NumberLiteral[((CommonTree)toType.getTree()).getChild(0).getText()])
;
-caseExpression
+whenExpression
@init { gParent.pushMsg("case expression", state); }
@after { gParent.popMsg(state); }
:
- KW_CASE expression
- (KW_WHEN expression KW_THEN expression)+
+ KW_CASE
+ ( KW_WHEN expression KW_THEN expression)+
(KW_ELSE expression)?
- KW_END -> ^(TOK_FUNCTION KW_CASE expression*)
+ KW_END -> ^(TOK_FUNCTION KW_WHEN expression*)
;
-whenExpression
+// Make caseExpression to build a whenExpression tree
+// Rewrite
+// CASE a
+// WHEN b THEN c
+// [WHEN d THEN e]* [ELSE f]
+// END
+// to
+// CASE
+// WHEN a=b THEN c
+// [WHEN a=d THEN e]* [ELSE f]
+// END
+caseExpression
@init { gParent.pushMsg("case expression", state); }
@after { gParent.popMsg(state); }
:
- KW_CASE
- ( KW_WHEN expression KW_THEN expression)+
- (KW_ELSE expression)?
- KW_END -> ^(TOK_FUNCTION KW_WHEN expression*)
+ KW_CASE caseOperand=expression
+ // Pass the case operand to the rule parses the when branches
+ whenBranches[$caseOperand.tree]
+ (KW_ELSE elseResult=expression)?
+ KW_END -> ^(TOK_FUNCTION Identifier["when"] whenBranches $elseResult?)
+ ;
+
+whenBranches[CommonTree caseOperand]
+ :
+ (whenExpressionBranch[caseOperand] KW_THEN! expression)+
+ ;
+
+whenExpressionBranch[CommonTree caseOperand]
+ :
+ KW_WHEN when=expression
+ -> ^(EQUAL["="] {$caseOperand} $when)
;
floorExpression
diff --git a/parser/src/test/org/apache/hadoop/hive/ql/parse/TestParseCase.java
b/parser/src/test/org/apache/hadoop/hive/ql/parse/TestParseCase.java
new file mode 100644
index 00000000000..0bf5c781dc0
--- /dev/null
+++ b/parser/src/test/org/apache/hadoop/hive/ql/parse/TestParseCase.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.parse;
+
+import org.junit.Assert;
+import org.junit.Test;
+
+public class TestParseCase {
+ ParseDriver parseDriver = new ParseDriver();
+
+ @Test
+ public void testParseCaseWithOperandAndOneBranch() throws Exception {
+ ASTNode tree = parseDriver.parseSelect("select case upper(col1) when 'A'
then 'OK' else 'N/A' end from t1", null).getTree();
+
+ String result = "\n" +
+ "TOK_SELECT\n" +
+ " TOK_SELEXPR\n" +
+ " TOK_FUNCTION\n" +
+ " when\n" +
+ " =\n" +
+ " TOK_FUNCTION\n" +
+ " upper\n" +
+ " TOK_TABLE_OR_COL\n" +
+ " col1\n" +
+ " 'A'\n" +
+ " 'OK'\n" +
+ " 'N/A'\n";
+
+ Assert.assertEquals(result, tree.dump());
+ }
+
+ @Test
+ public void testParseCaseWithOperandAndMultipleBranches() throws Exception {
+ ASTNode tree = parseDriver.parseSelect(
+ "select case a" +
+ " when 'B' then 'bean'" +
+ " when 'A' then 'apple' else 'N/A' end from t1",
null).getTree();
+
+ String result = "\n" +
+ "TOK_SELECT\n" +
+ " TOK_SELEXPR\n" +
+ " TOK_FUNCTION\n" +
+ " when\n" +
+ " =\n" +
+ " TOK_TABLE_OR_COL\n" +
+ " a\n" +
+ " 'B'\n" +
+ " 'bean'\n" +
+ " =\n" +
+ " TOK_TABLE_OR_COL\n" +
+ " a\n" +
+ " 'A'\n" +
+ " 'apple'\n" +
+ " 'N/A'\n";
+
+ Assert.assertEquals(result, tree.dump());
+ }
+
+ @Test
+ public void testParseCaseWithOperandAndNoElse() throws Exception {
+ ASTNode tree = parseDriver.parseSelect("select case a when 'A' then 'OK'
end from t1", null).getTree();
+
+ String result = "\n" +
+ "TOK_SELECT\n" +
+ " TOK_SELEXPR\n" +
+ " TOK_FUNCTION\n" +
+ " when\n" +
+ " =\n" +
+ " TOK_TABLE_OR_COL\n" +
+ " a\n" +
+ " 'A'\n" +
+ " 'OK'\n";
+
+ Assert.assertEquals(result, tree.dump());
+ }
+}
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/exec/ExprNodeGenericFuncEvaluator.java
b/ql/src/java/org/apache/hadoop/hive/ql/exec/ExprNodeGenericFuncEvaluator.java
index cdf3cf35154..41ede68c925 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/exec/ExprNodeGenericFuncEvaluator.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/exec/ExprNodeGenericFuncEvaluator.java
@@ -18,8 +18,6 @@
package org.apache.hadoop.hive.ql.exec;
-import com.google.common.base.Preconditions;
-
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.hadoop.conf.Configuration;
@@ -30,7 +28,6 @@
import org.apache.hadoop.hive.ql.session.SessionState;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFBaseCompare;
-import org.apache.hadoop.hive.ql.udf.generic.GenericUDFCase;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFWhen;
import org.apache.hadoop.hive.serde2.objectinspector.ConstantObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
@@ -118,8 +115,7 @@ public ExprNodeGenericFuncEvaluator(ExprNodeGenericFuncDesc
expr, Configuration
}
}
genericUDF = expr.getGenericUDF();
- if (isEager &&
- (genericUDF instanceof GenericUDFCase || genericUDF instanceof
GenericUDFWhen)) {
+ if (isEager && genericUDF instanceof GenericUDFWhen) {
throw new HiveException("Stateful expressions cannot be used inside of
CASE");
}
}
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 c54a59f9516..f644bd81c9d 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
@@ -590,7 +590,6 @@ public final class FunctionRegistry {
system.registerGenericUDF("create_union", GenericUDFUnion.class);
system.registerGenericUDF("extract_union", GenericUDFExtractUnion.class);
- system.registerGenericUDF("case", GenericUDFCase.class);
system.registerGenericUDF("when", GenericUDFWhen.class);
system.registerGenericUDF("nullif", GenericUDFNullif.class);
system.registerGenericUDF("hash", GenericUDFHash.class);
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorizationContext.java
b/ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorizationContext.java
index e49cd5bf3f9..a20d400894a 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorizationContext.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorizationContext.java
@@ -1474,17 +1474,16 @@ private static boolean
isNonVectorizedPathUDF(ExprNodeGenericFuncDesc expr,
} else if (gudf instanceof GenericUDFFromUnixTime &&
isIntFamily(arg0Type(expr))
|| (gudf instanceof GenericUDFTimestamp &&
isStringFamily(arg0Type(expr)))
- /* GenericUDFCase and GenericUDFWhen are implemented with the UDF
Adaptor because
- * of their complexity and generality. In the future, variations
of these
+ /* GenericUDFWhen is implemented with the UDF Adaptor because
+ * of its complexity and generality. In the future, variations of
this
* can be optimized to run faster for the vectorized code path.
For example,
* CASE col WHEN 1 then "one" WHEN 2 THEN "two" ELSE "other" END
- * is an example of a GenericUDFCase that has all constant
arguments
+ * is an example when all constant arguments
* except for the first argument. This is probably a common case
and a
* good candidate for a fast, special-purpose VectorExpression.
Then
* the UDF Adaptor code path could be used as a catch-all for
* non-optimized general cases.
*/
- || gudf instanceof GenericUDFCase
|| gudf instanceof GenericUDFWhen) {
return true;
} else // between has 4 args here, but can be vectorized like this
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/ConstantPropagateProcFactory.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/ConstantPropagateProcFactory.java
index 01285f8f553..6f107798bae 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/ConstantPropagateProcFactory.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/ConstantPropagateProcFactory.java
@@ -67,7 +67,6 @@
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF.DeferredJavaObject;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFBaseCompare;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge;
-import org.apache.hadoop.hive.ql.udf.generic.GenericUDFCase;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFCoalesce;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPAnd;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqual;
@@ -564,13 +563,13 @@ private static ExprNodeDesc shortcutFunction(GenericUDF
udf, List<ExprNodeDesc>
ExprNodeGenericFuncDesc caseOrWhenexpr = null;
if (newExprs.get(0) instanceof ExprNodeGenericFuncDesc) {
caseOrWhenexpr = (ExprNodeGenericFuncDesc) newExprs.get(0);
- if (caseOrWhenexpr.getGenericUDF() instanceof GenericUDFWhen ||
caseOrWhenexpr.getGenericUDF() instanceof GenericUDFCase) {
+ if (caseOrWhenexpr.getGenericUDF() instanceof GenericUDFWhen) {
foundUDFInFirst = true;
}
}
if (!foundUDFInFirst && newExprs.get(1) instanceof
ExprNodeGenericFuncDesc) {
caseOrWhenexpr = (ExprNodeGenericFuncDesc) newExprs.get(1);
- if (!(caseOrWhenexpr.getGenericUDF() instanceof GenericUDFWhen ||
caseOrWhenexpr.getGenericUDF() instanceof GenericUDFCase)) {
+ if (!(caseOrWhenexpr.getGenericUDF() instanceof GenericUDFWhen)) {
return null;
}
}
@@ -596,21 +595,6 @@ private static ExprNodeDesc shortcutFunction(GenericUDF
udf, List<ExprNodeDesc>
ExprNodeGenericFuncDesc newCaseOrWhenExpr =
ExprNodeGenericFuncDesc.newInstance(childUDF,
caseOrWhenexpr.getFuncText(), children);
return newCaseOrWhenExpr;
- } else if (childUDF instanceof GenericUDFCase) {
- for (i = 2; i < children.size(); i+=2) {
- children.set(i, ExprNodeGenericFuncDesc.newInstance(new
GenericUDFOPEqual(),
- Lists.newArrayList(children.get(i),newExprs.get(foundUDFInFirst ?
1 : 0))));
- }
- if(children.size() % 2 == 0) {
- i = children.size()-1;
- children.set(i, ExprNodeGenericFuncDesc.newInstance(new
GenericUDFOPEqual(),
- Lists.newArrayList(children.get(i),newExprs.get(foundUDFInFirst
? 1 : 0))));
- }
- // after constant folding of child expression the return type of
UDFCase might have changed,
- // so recreate the expression
- ExprNodeGenericFuncDesc newCaseOrWhenExpr =
ExprNodeGenericFuncDesc.newInstance(childUDF,
- caseOrWhenexpr.getFuncText(), children);
- return newCaseOrWhenExpr;
} else {
// cant happen
return null;
@@ -769,59 +753,6 @@ private static ExprNodeDesc shortcutFunction(GenericUDF
udf, List<ExprNodeDesc>
}
}
- if (udf instanceof GenericUDFCase) {
- // HIVE-9644 Attempt to fold expression like :
- // where (case ss_sold_date when '1998-01-01' then 1=1 else null=1 end);
- // where ss_sold_date= '1998-01-01' ;
- if (!(newExprs.size() == 3 || newExprs.size() == 4)) {
- // In general case can have unlimited # of branches,
- // we currently only handle either 1 or 2 branch.
- return null;
- }
- ExprNodeDesc thenExpr = newExprs.get(2);
- ExprNodeDesc elseExpr = newExprs.size() == 4 ? newExprs.get(3) :
- new ExprNodeConstantDesc(newExprs.get(2).getTypeInfo(),null);
-
- if (thenExpr instanceof ExprNodeConstantDesc && elseExpr instanceof
ExprNodeConstantDesc) {
- ExprNodeConstantDesc constThen = (ExprNodeConstantDesc) thenExpr;
- ExprNodeConstantDesc constElse = (ExprNodeConstantDesc) elseExpr;
- Object thenVal = constThen.getValue();
- Object elseVal = constElse.getValue();
- if (thenVal == null) {
- if (null == elseVal) {
- return thenExpr;
- } else if (op instanceof FilterOperator) {
- return Boolean.TRUE.equals(elseVal) ?
ExprNodeGenericFuncDesc.newInstance(new GenericUDFOPNotEqual(),
newExprs.subList(0, 2)) :
- Boolean.FALSE.equals(elseVal) ? elseExpr : null;
- } else {
- return null;
- }
- } else if (null == elseVal && op instanceof FilterOperator) {
- return Boolean.TRUE.equals(thenVal) ?
ExprNodeGenericFuncDesc.newInstance(new GenericUDFOPEqual(),
newExprs.subList(0, 2)) :
- Boolean.FALSE.equals(thenVal) ? thenExpr : null;
- } else if(thenVal.equals(elseVal)){
- return thenExpr;
- } else if (thenVal instanceof Boolean && elseVal instanceof Boolean) {
- ExprNodeGenericFuncDesc equal = ExprNodeGenericFuncDesc.newInstance(
- new GenericUDFOPEqual(), newExprs.subList(0, 2));
- List<ExprNodeDesc> children = new ArrayList<>();
- children.add(equal);
- children.add(new ExprNodeConstantDesc(false));
- ExprNodeGenericFuncDesc func =
ExprNodeGenericFuncDesc.newInstance(new GenericUDFCoalesce(),
- children);
- if (Boolean.TRUE.equals(thenVal)) {
- return func;
- } else {
- List<ExprNodeDesc> exprs = new ArrayList<>();
- exprs.add(func);
- return ExprNodeGenericFuncDesc.newInstance(new GenericUDFOPNot(),
exprs);
- }
- } else {
- return null;
- }
- }
- }
-
if (udf instanceof GenericUDFUnixTimeStamp) {
if (newExprs.size() >= 1) {
// unix_timestamp(args) -> to_unix_timestamp(args)
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveRexExecutorImpl.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveRexExecutorImpl.java
index 47ee70f8ad6..66984a3ebde 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveRexExecutorImpl.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveRexExecutorImpl.java
@@ -47,7 +47,7 @@ public HiveRexExecutorImpl() {
@Override
public void reduce(RexBuilder rexBuilder, List<RexNode> constExps,
List<RexNode> reducedValues) {
- RexNodeConverter rexNodeConverter = new RexNodeConverter(rexBuilder,
rexBuilder.getTypeFactory());
+ RexNodeConverter rexNodeConverter = new RexNodeConverter(rexBuilder);
for (RexNode rexNode : constExps) {
// initialize the converter
ExprNodeConverter converter = new ExprNodeConverter("", null, null, null,
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/RexNodeConverter.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/RexNodeConverter.java
index 31e0d4dee57..cddc7cb9ca2 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/RexNodeConverter.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/RexNodeConverter.java
@@ -39,7 +39,6 @@
import org.apache.calcite.sql.fun.SqlCastFunction;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
-import org.apache.calcite.sql.type.ArraySqlType;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.util.ConversionUtil;
@@ -75,7 +74,6 @@
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFBaseCompare;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFBetween;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge;
-import org.apache.hadoop.hive.ql.udf.generic.GenericUDFCase;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFIn;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFTimestamp;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDFToArray;
@@ -121,9 +119,9 @@ public class RexNodeConverter {
/**
* Constructor used by HiveRexExecutorImpl.
*/
- public RexNodeConverter(RexBuilder rexBuilder, RelDataTypeFactory
typeFactory) {
+ public RexNodeConverter(RexBuilder rexBuilder) {
this.rexBuilder = rexBuilder;
- this.typeFactory = typeFactory;
+ this.typeFactory = rexBuilder.getTypeFactory();
}
public RexNode convert(ExprNodeDesc expr) throws SemanticException {
@@ -168,7 +166,7 @@ private RexNode convert(ExprNodeGenericFuncDesc func)
throws SemanticException {
&& (PrimitiveGrouping.NUMERIC_GROUP ==
PrimitiveObjectInspectorUtils.getPrimitiveGrouping(
((PrimitiveTypeInfo) func.getTypeInfo()).getPrimitiveCategory())));
boolean isCompare = !isNumeric && tgtUdf instanceof GenericUDFBaseCompare;
- boolean isWhenCase = tgtUdf instanceof GenericUDFWhen || tgtUdf instanceof
GenericUDFCase;
+ boolean isWhenCase = tgtUdf instanceof GenericUDFWhen;
boolean isTransformableTimeStamp = func.getGenericUDF() instanceof
GenericUDFUnixTimeStamp &&
!func.getChildren().isEmpty();
boolean isBetween = !isNumeric && tgtUdf instanceof GenericUDFBetween;
@@ -249,7 +247,7 @@ private RexNode convert(ExprNodeGenericFuncDesc func)
throws SemanticException {
func.getGenericUDF(), argTypeBldr.build(), retType);
if (calciteOp.getKind() == SqlKind.CASE) {
// If it is a case operator, we need to rewrite it
- childRexNodeLst = rewriteCaseChildren(func.getFuncText(),
childRexNodeLst, rexBuilder);
+ childRexNodeLst = rewriteCaseChildren(childRexNodeLst, rexBuilder);
// Adjust branch types by inserting explicit casts if the actual is
ambiguous
childRexNodeLst = adjustCaseBranchTypes(childRexNodeLst, retType,
rexBuilder);
} else if (HiveExtractDate.ALL_FUNCTIONS.contains(calciteOp)) {
@@ -357,60 +355,30 @@ public static RexNode handleExplicitCast(GenericUDF udf,
RelDataType returnType,
* "case" function, ELSE clause is optional)
* - CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END (translated into the
* "when" function, ELSE clause is optional)
- * However, Calcite only has the equivalent to the "when" Hive function.
Thus,
- * we need to transform the "case" function into "when". Further, ELSE
clause is
- * not optional in Calcite.
+ * The first type is transformed to the second one at parsing time.
+ * Calcite only has the equivalent to the "when" Hive function and ELSE
clause is
+ * not optional.
*
- * Example. Consider the following statement:
- * CASE x + y WHEN 1 THEN 'fee' WHEN 2 THEN 'fie' END
- * It will be transformed into:
- * CASE WHEN =(x + y, 1) THEN 'fee' WHEN =(x + y, 2) THEN 'fie' ELSE null END
+ * See parser rule caseExpression in IdentifiersParser.g
*/
- public static List<RexNode> rewriteCaseChildren(String funcText,
List<RexNode> childRexNodeLst,
- RexBuilder rexBuilder) throws SemanticException {
+ public static List<RexNode> rewriteCaseChildren(List<RexNode>
childRexNodeLst, RexBuilder rexBuilder) {
List<RexNode> newChildRexNodeLst = new ArrayList<>();
- if (FunctionRegistry.getNormalizedFunctionName(funcText).equals("case")) {
- RexNode firstPred = childRexNodeLst.get(0);
- int length = childRexNodeLst.size() % 2 == 1 ?
- childRexNodeLst.size() : childRexNodeLst.size() - 1;
- for (int i = 1; i < length; i++) {
- if (i % 2 == 1) {
- // We rewrite it
- RexNode node = childRexNodeLst.get(i);
- if (node.isA(SqlKind.LITERAL) &&
!node.getType().equals(firstPred.getType())) {
- // this effectively changes the type of the literal to that of the
predicate
- // to which it is anyway going to be compared with
- // ex: CASE WHEN =($0:SMALLINT, 1:INTEGER) ... => CASE WHEN
=($0:SMALLINT, 1:SMALLINT)
- node = rexBuilder.makeCast(firstPred.getType(), node);
+ for (int i = 0; i < childRexNodeLst.size(); i++) {
+ RexNode child = childRexNodeLst.get(i);
+ if (RexUtil.isNull(child)) {
+ if (i % 2 == 0 && i != childRexNodeLst.size() - 1) {
+ if (SqlTypeName.NULL.equals(child.getType().getSqlTypeName())) {
+ child =
rexBuilder.makeNullLiteral(rexBuilder.getTypeFactory().createSqlType(SqlTypeName.BOOLEAN));
}
- newChildRexNodeLst.add(
- rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, firstPred,
node));
} else {
- newChildRexNodeLst.add(childRexNodeLst.get(i));
- }
- }
- // The else clause
- if (length != childRexNodeLst.size()) {
- newChildRexNodeLst.add(childRexNodeLst.get(childRexNodeLst.size()-1));
- }
- } else {
- for (int i = 0; i < childRexNodeLst.size(); i++) {
- RexNode child = childRexNodeLst.get(i);
- if (RexUtil.isNull(child)) {
- if (i % 2 == 0 && i != childRexNodeLst.size() - 1) {
- if (SqlTypeName.NULL.equals(child.getType().getSqlTypeName())) {
- child =
rexBuilder.makeNullLiteral(rexBuilder.getTypeFactory().createSqlType(SqlTypeName.BOOLEAN));
- }
- } else {
- // this is needed to provide typed NULLs which were working before
- // example: IF(false, array(1,2,3), NULL)
- if (!RexUtil.isNull(childRexNodeLst.get(1))) {
- child = rexBuilder.makeCast(childRexNodeLst.get(1).getType(),
child);
- }
+ // this is needed to provide typed NULLs which were working before
+ // example: IF(false, array(1,2,3), NULL)
+ if (!RexUtil.isNull(childRexNodeLst.get(1))) {
+ child = rexBuilder.makeCast(childRexNodeLst.get(1).getType(),
child);
}
}
- newChildRexNodeLst.add(child);
}
+ newChildRexNodeLst.add(child);
}
// Calcite always needs the else clause to be defined explicitly
if (newChildRexNodeLst.size() % 2 == 0) {
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/physical/Vectorizer.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/physical/Vectorizer.java
index f3dadc46011..4eeb19dfe7b 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/physical/Vectorizer.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/physical/Vectorizer.java
@@ -525,7 +525,6 @@ public Vectorizer() {
supportedGenericUDFs.add(GenericUDFAbs.class);
supportedGenericUDFs.add(GenericUDFBetween.class);
supportedGenericUDFs.add(GenericUDFIn.class);
- supportedGenericUDFs.add(GenericUDFCase.class);
supportedGenericUDFs.add(GenericUDFWhen.class);
supportedGenericUDFs.add(GenericUDFCoalesce.class);
supportedGenericUDFs.add(GenericUDFElt.class);
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/parse/type/HiveFunctionHelper.java
b/ql/src/java/org/apache/hadoop/hive/ql/parse/type/HiveFunctionHelper.java
index 7ebbb95efb9..32c1f0d8c46 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/type/HiveFunctionHelper.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/type/HiveFunctionHelper.java
@@ -255,7 +255,7 @@ public RexNode getExpression(String functionText,
FunctionInfo fi,
fi.getGenericUDF(), argsTypes.build(), returnType);
if (calciteOp.getKind() == SqlKind.CASE) {
// If it is a case operator, we need to rewrite it
- inputs = RexNodeConverter.rewriteCaseChildren(functionText, inputs,
rexBuilder);
+ inputs = RexNodeConverter.rewriteCaseChildren(inputs, rexBuilder);
// Adjust branch types by inserting explicit casts if the actual is
ambiguous
inputs = RexNodeConverter.adjustCaseBranchTypes(inputs, returnType,
rexBuilder);
checkForStatefulFunctions(inputs);
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFCase.java
b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFCase.java
deleted file mode 100644
index 641188434af..00000000000
--- a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFCase.java
+++ /dev/null
@@ -1,167 +0,0 @@
-/*
- * 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 java.util.List;
-import java.util.Optional;
-
-import org.apache.hadoop.hive.ql.exec.Description;
-import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
-import org.apache.hadoop.hive.ql.metadata.HiveException;
-import org.apache.hadoop.hive.ql.plan.ColStatistics;
-import org.apache.hadoop.hive.ql.stats.estimator.StatEstimator;
-import org.apache.hadoop.hive.ql.stats.estimator.StatEstimatorProvider;
-import org.apache.hadoop.hive.ql.stats.estimator.PessimisticStatCombiner;
-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;
-
-/**
- * GenericUDF Class for SQL construct "CASE a WHEN b THEN c [ELSE f] END".
- *
- * NOTES: 1. a and b should be compatible, or an exception will be
- * thrown. 2. c and f should be compatible types, or an exception will be
- * thrown.
- */
-@Description(
- name = "case",
- value = "CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END - "
- + "When a = b, returns c; when a = d, return e; else return f",
- extended = "Example:\n "
- + "SELECT\n"
- + " CASE deptno\n"
- + " WHEN 1 THEN Engineering\n"
- + " WHEN 2 THEN Finance\n"
- + " ELSE admin\n"
- + " END,\n"
- + " CASE zone\n"
- + " WHEN 7 THEN Americas\n"
- + " ELSE Asia-Pac\n"
- + " END\n"
- + " FROM emp_details")
-
-public class GenericUDFCase extends GenericUDF implements
StatEstimatorProvider {
- private transient ObjectInspector[] argumentOIs;
- private transient GenericUDFUtils.ReturnObjectInspectorResolver
returnOIResolver;
- private transient GenericUDFUtils.ReturnObjectInspectorResolver
caseOIResolver;
-
- @Override
- public ObjectInspector initialize(ObjectInspector[] arguments) throws
UDFArgumentTypeException {
-
- argumentOIs = arguments;
- caseOIResolver = new GenericUDFUtils.ReturnObjectInspectorResolver(true);
- returnOIResolver = new GenericUDFUtils.ReturnObjectInspectorResolver(true);
-
- boolean r = caseOIResolver.update(arguments[0]);
- assert (r);
- for (int i = 1; i + 1 < arguments.length; i += 2) {
- if (!caseOIResolver.update(arguments[i])) {
- throw new UDFArgumentTypeException(i,
- "The expressions after WHEN should have the same type with that
after CASE: \""
- + caseOIResolver.get().getTypeName() + "\" is expected but \""
- + arguments[i].getTypeName() + "\" is found");
- }
- if (!returnOIResolver.update(arguments[i + 1])) {
- throw new UDFArgumentTypeException(i + 1,
- "The expressions after THEN should have the same type: \""
- + returnOIResolver.get().getTypeName()
- + "\" is expected but \"" + arguments[i + 1].getTypeName()
- + "\" is found");
- }
- }
- if (arguments.length % 2 == 0) {
- int i = arguments.length - 2;
- if (!returnOIResolver.update(arguments[i + 1])) {
- throw new UDFArgumentTypeException(i + 1,
- "The expression after ELSE should have the same type as those
after THEN: \""
- + returnOIResolver.get().getTypeName()
- + "\" is expected but \"" + arguments[i + 1].getTypeName()
- + "\" is found");
- }
- }
-
- return returnOIResolver.get();
- }
-
- @Override
- public Object evaluate(DeferredObject[] arguments) throws HiveException {
- Object exprValue = arguments[0].get();
- for (int i = 1; i + 1 < arguments.length; i += 2) {
- Object caseKey = arguments[i].get();
- // May need to convert to common type to compare
- PrimitiveObjectInspector caseOI = (PrimitiveObjectInspector)
caseOIResolver.get();
- if (PrimitiveObjectInspectorUtils.comparePrimitiveObjects(
- caseOIResolver.convertIfNecessary(exprValue, argumentOIs[0]),
caseOI,
- caseOIResolver.convertIfNecessary(caseKey, argumentOIs[i], false),
caseOI)) {
- Object caseValue = arguments[i + 1].get();
- return returnOIResolver.convertIfNecessary(caseValue, argumentOIs[i +
1]);
- }
- }
- // Process else statement
- if (arguments.length % 2 == 0) {
- int i = arguments.length - 2;
- Object elseValue = arguments[i + 1].get();
- return returnOIResolver.convertIfNecessary(elseValue, argumentOIs[i +
1]);
- }
- return null;
- }
-
- @Override
- public String getDisplayString(String[] children) {
- assert (children.length >= 3);
- StringBuilder sb = new StringBuilder();
- sb.append("CASE (");
- sb.append(children[0]);
- sb.append(")");
- for (int i = 1; i + 1 < children.length; i += 2) {
- sb.append(" WHEN (");
- sb.append(children[i]);
- sb.append(") THEN (");
- sb.append(children[i + 1]);
- sb.append(")");
- }
- if (children.length % 2 == 0) {
- sb.append(" ELSE (");
- sb.append(children[children.length - 1]);
- sb.append(")");
- }
- sb.append(" END");
- return sb.toString();
- }
-
- @Override
- public StatEstimator getStatEstimator() {
- return new CaseStatEstimator();
- }
-
- static class CaseStatEstimator implements StatEstimator {
-
- @Override
- public Optional<ColStatistics> estimate(List<ColStatistics> argStats) {
- PessimisticStatCombiner combiner = new PessimisticStatCombiner();
- for (int i = 2; i < argStats.size(); i += 2) {
- combiner.add(argStats.get(i));
- }
- if (argStats.size() % 2 == 0) {
- combiner.add(argStats.get(argStats.size() - 1));
- }
- return combiner.getResult();
- }
- }
-}
diff --git
a/ql/src/test/org/apache/hadoop/hive/ql/optimizer/calcite/translator/TestRexNodeConverter.java
b/ql/src/test/org/apache/hadoop/hive/ql/optimizer/calcite/translator/TestRexNodeConverter.java
deleted file mode 100644
index 341097b8dd7..00000000000
---
a/ql/src/test/org/apache/hadoop/hive/ql/optimizer/calcite/translator/TestRexNodeConverter.java
+++ /dev/null
@@ -1,186 +0,0 @@
-/*
- * 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.optimizer.calcite.translator;
-
-import com.google.common.collect.ImmutableList;
-import org.apache.calcite.jdbc.JavaTypeFactoryImpl;
-import org.apache.calcite.plan.RelOptCluster;
-import org.apache.calcite.plan.RelOptPlanner;
-import org.apache.calcite.plan.RelOptSchema;
-import org.apache.calcite.rel.RelNode;
-import org.apache.calcite.rel.logical.LogicalTableScan;
-import org.apache.calcite.rel.type.RelDataType;
-import org.apache.calcite.rel.type.RelDataTypeFactory;
-import org.apache.calcite.rex.RexBuilder;
-import org.apache.calcite.rex.RexNode;
-import org.apache.calcite.sql.fun.SqlStdOperatorTable;
-import org.apache.calcite.sql.type.SqlTypeName;
-import org.apache.calcite.tools.RelBuilder;
-import org.apache.hadoop.hive.conf.HiveConf;
-import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelFactories;
-import org.apache.hadoop.hive.ql.optimizer.calcite.HiveTypeSystemImpl;
-import org.apache.hadoop.hive.ql.optimizer.calcite.RelOptHiveTable;
-import org.apache.hadoop.hive.ql.parse.CalcitePlanner;
-import org.apache.hadoop.hive.ql.parse.SemanticException;
-import org.junit.Assert;
-import org.junit.Before;
-import org.junit.BeforeClass;
-import org.junit.Test;
-import org.junit.runner.RunWith;
-import org.mockito.Mock;
-import org.mockito.junit.MockitoJUnitRunner;
-
-import java.util.Collections;
-import java.util.List;
-
-import static org.mockito.Mockito.doReturn;
-
-@RunWith(MockitoJUnitRunner.class)
-public class TestRexNodeConverter {
-
- private static final String CASE_FUNC_TEST = "case";
- private static final RexBuilder REX_BUILDER = new RexBuilder(
- new JavaTypeFactoryImpl(new HiveTypeSystemImpl()));
- private static final RelDataTypeFactory TYPE_FACTORY =
REX_BUILDER.getTypeFactory();
-
- private static RelDataType smallIntegerType;
- private static RelDataType integerType;
- @SuppressWarnings("FieldCanBeLocal")
- private static RelDataType nullableSmallIntegerType;
-
- private static RexNode varChar34;
- private static RexNode varChar35;
- private static RexNode varCharNull;
-
- private static RelOptCluster relOptCluster;
- private static RelBuilder relBuilder;
- private static RelDataType tableType;
-
- @Mock
- private RelOptSchema schemaMock;
- @Mock
- private RelOptHiveTable tableMock;
-
- private LogicalTableScan tableScan;
-
- @BeforeClass
- public static void beforeClass() {
- smallIntegerType = TYPE_FACTORY.createSqlType(SqlTypeName.SMALLINT);
- integerType = TYPE_FACTORY.createSqlType(SqlTypeName.INTEGER);
- nullableSmallIntegerType =
TYPE_FACTORY.createTypeWithNullability(smallIntegerType, true);
-
- RelDataType varcharType = TYPE_FACTORY.createSqlType(SqlTypeName.VARCHAR,
20);
- varChar34 = REX_BUILDER.makeLiteral("34", varcharType, true);
- varChar35 = REX_BUILDER.makeLiteral("35", varcharType, true);
- varCharNull = REX_BUILDER.makeLiteral(null, varcharType, true);
-
- tableType = TYPE_FACTORY.createStructType(
- ImmutableList.of(smallIntegerType, nullableSmallIntegerType),
- ImmutableList.of("f1", "f2")
- );
-
- RelOptPlanner planner = CalcitePlanner.createPlanner(new HiveConf());
- relOptCluster = RelOptCluster.create(planner, REX_BUILDER);
- }
-
- @Before
- public void before() {
- doReturn(tableType).when(tableMock).getRowType();
- tableScan = LogicalTableScan.create(relOptCluster, tableMock,
Collections.emptyList());
- relBuilder = HiveRelFactories.HIVE_BUILDER.create(relOptCluster,
schemaMock);
- }
-
- @Test public void testRewriteCaseChildren() throws SemanticException {
- RelNode scan = relBuilder.push(tableScan).build();
- RexNode inputRef = REX_BUILDER.makeInputRef(scan, 0);
-
- List<RexNode> childrenNodeList = ImmutableList.of(
- inputRef,
- REX_BUILDER.makeLiteral(1, integerType, true),
- varChar34,
- REX_BUILDER.makeLiteral(6, integerType, true),
- varChar35);
-
- List<RexNode> expected = ImmutableList.of(
- REX_BUILDER.makeCall(SqlStdOperatorTable.EQUALS,
- inputRef, REX_BUILDER.makeLiteral(1, smallIntegerType, true)),
- varChar34,
- REX_BUILDER.makeCall(SqlStdOperatorTable.EQUALS,
- inputRef, REX_BUILDER.makeLiteral(6, smallIntegerType, true)),
- varChar35,
- varCharNull);
-
- List<RexNode> computed = RexNodeConverter.rewriteCaseChildren(
- CASE_FUNC_TEST, childrenNodeList, REX_BUILDER);
-
- Assert.assertEquals(expected, computed);
- }
-
- @Test public void testRewriteCaseChildrenNullChild() throws
SemanticException {
- RelNode scan = relBuilder.push(tableScan).build();
- RexNode inputRef = REX_BUILDER.makeInputRef(scan, 0);
-
- List<RexNode> childrenNodeList = ImmutableList.of(
- inputRef,
- REX_BUILDER.makeLiteral(1, integerType, true),
- varChar34,
- REX_BUILDER.makeLiteral(null, integerType, true),
- varChar35);
-
- List<RexNode> expected = ImmutableList.of(
- REX_BUILDER.makeCall(SqlStdOperatorTable.EQUALS,
- inputRef, REX_BUILDER.makeLiteral(1, smallIntegerType, true)),
- varChar34,
- REX_BUILDER.makeCall(SqlStdOperatorTable.EQUALS,
- inputRef, REX_BUILDER.makeLiteral(null, smallIntegerType, true)),
- varChar35,
- varCharNull);
-
- List<RexNode> computed = RexNodeConverter.rewriteCaseChildren(
- CASE_FUNC_TEST, childrenNodeList, REX_BUILDER);
-
- Assert.assertEquals(expected, computed);
- }
-
- @Test public void testRewriteCaseChildrenNullChildAndNullableType() throws
SemanticException {
- RelNode scan = relBuilder.push(tableScan).build();
- RexNode inputRef = REX_BUILDER.makeInputRef(scan, 1);
-
- List<RexNode> childrenNodeList = ImmutableList.of(
- inputRef,
- REX_BUILDER.makeLiteral(1, integerType, true),
- varChar34,
- REX_BUILDER.makeLiteral(null, integerType, true),
- varChar35);
-
- List<RexNode> expected = ImmutableList.of(
- REX_BUILDER.makeCall(SqlStdOperatorTable.EQUALS,
- inputRef, REX_BUILDER.makeLiteral(1, smallIntegerType, true)),
- varChar34,
- REX_BUILDER.makeCall(SqlStdOperatorTable.EQUALS,
- inputRef, REX_BUILDER.makeLiteral(null, smallIntegerType, true)),
- varChar35,
- varCharNull);
-
- List<RexNode> computed = RexNodeConverter.rewriteCaseChildren(
- CASE_FUNC_TEST, childrenNodeList, REX_BUILDER);
-
- Assert.assertEquals(expected, computed);
- }
-}
diff --git a/ql/src/test/queries/clientpositive/cbo_case_when_type_conversion.q
b/ql/src/test/queries/clientpositive/cbo_case_when_type_conversion.q
new file mode 100644
index 00000000000..fe2439e18f5
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/cbo_case_when_type_conversion.q
@@ -0,0 +1,40 @@
+CREATE TABLE t1 (col1 char(3));
+
+INSERT INTO t1 VALUES ('A'),('b'),('c'),(null);
+
+explain cbo
+select col1, case upper(col1) when 'A' then 'OK' else 'N/A' end as col2 from
t1;
+explain
+select col1, case upper(col1) when 'A' then 'OK' else 'N/A' end as col2 from
t1;
+
+select col1, case upper(col1) when 'A' then 'OK' else 'N/A' end as col2 from
t1;
+
+explain cbo
+select col1, case when upper(col1)='A' then 'OK' else 'N/A' end as col2 from
t1;
+explain
+select col1, case when upper(col1)='A' then 'OK' else 'N/A' end as col2 from
t1;
+
+select col1, case when upper(col1)='A' then 'OK' else 'N/A' end as col2 from
t1;
+
+explain cbo
+select col1,
+ case lower(col1)
+ when 'a' then 'OK a'
+ when 'b' then 'OK b'
+ else 'N/A'
+ end as col2 from t1;
+
+explain cbo
+select col1,
+ case
+ when lower(col1) = 'a' then 'OK a'
+ when lower(col1) = 'b' then 'OK b'
+ else 'N/A'
+ end as col2 from t1;
+
+select col1,
+ case lower(col1)
+ when 'a' then 'OK a'
+ when 'b' then 'OK b'
+ else 'N/A'
+ end as col2 from t1;
diff --git a/ql/src/test/queries/clientpositive/udf_case.q
b/ql/src/test/queries/clientpositive/udf_case.q
index 28d87a85451..f3810e71c6b 100644
--- a/ql/src/test/queries/clientpositive/udf_case.q
+++ b/ql/src/test/queries/clientpositive/udf_case.q
@@ -1,9 +1,6 @@
--! qt:dataset:src
set hive.fetch.task.conversion=more;
-DESCRIBE FUNCTION case;
-DESCRIBE FUNCTION EXTENDED case;
-
EXPLAIN
SELECT CASE 1
WHEN 1 THEN 2
diff --git
a/ql/src/test/results/clientpositive/llap/cbo_case_when_type_conversion.q.out
b/ql/src/test/results/clientpositive/llap/cbo_case_when_type_conversion.q.out
new file mode 100644
index 00000000000..5f22106d856
--- /dev/null
+++
b/ql/src/test/results/clientpositive/llap/cbo_case_when_type_conversion.q.out
@@ -0,0 +1,189 @@
+PREHOOK: query: CREATE TABLE t1 (col1 char(3))
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@t1
+POSTHOOK: query: CREATE TABLE t1 (col1 char(3))
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@t1
+PREHOOK: query: INSERT INTO t1 VALUES ('A'),('b'),('c'),(null)
+PREHOOK: type: QUERY
+PREHOOK: Input: _dummy_database@_dummy_table
+PREHOOK: Output: default@t1
+POSTHOOK: query: INSERT INTO t1 VALUES ('A'),('b'),('c'),(null)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: _dummy_database@_dummy_table
+POSTHOOK: Output: default@t1
+POSTHOOK: Lineage: t1.col1 SCRIPT []
+PREHOOK: query: explain cbo
+select col1, case upper(col1) when 'A' then 'OK' else 'N/A' end as col2 from t1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t1
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo
+select col1, case upper(col1) when 'A' then 'OK' else 'N/A' end as col2 from t1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t1
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(col1=[$0], col2=[CASE(=(CAST(UPPER($0)):VARCHAR(2147483647)
CHARACTER SET "UTF-16LE", _UTF-16LE'A'), _UTF-16LE'OK':VARCHAR(2147483647)
CHARACTER SET "UTF-16LE", _UTF-16LE'N/A':VARCHAR(2147483647) CHARACTER SET
"UTF-16LE")])
+ HiveTableScan(table=[[default, t1]], table:alias=[t1])
+
+PREHOOK: query: explain
+select col1, case upper(col1) when 'A' then 'OK' else 'N/A' end as col2 from t1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t1
+#### A masked pattern was here ####
+POSTHOOK: query: explain
+select col1, case upper(col1) when 'A' then 'OK' else 'N/A' end as col2 from t1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t1
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-0
+ Fetch Operator
+ limit: -1
+ Processor Tree:
+ TableScan
+ alias: t1
+ Select Operator
+ expressions: col1 (type: char(3)), if((CAST( upper(col1) AS
STRING) = 'A'), 'OK', 'N/A') (type: string)
+ outputColumnNames: _col0, _col1
+ ListSink
+
+PREHOOK: query: select col1, case upper(col1) when 'A' then 'OK' else 'N/A'
end as col2 from t1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t1
+#### A masked pattern was here ####
+POSTHOOK: query: select col1, case upper(col1) when 'A' then 'OK' else 'N/A'
end as col2 from t1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t1
+#### A masked pattern was here ####
+A OK
+b N/A
+c N/A
+NULL N/A
+PREHOOK: query: explain cbo
+select col1, case when upper(col1)='A' then 'OK' else 'N/A' end as col2 from t1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t1
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo
+select col1, case when upper(col1)='A' then 'OK' else 'N/A' end as col2 from t1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t1
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(col1=[$0], col2=[CASE(=(CAST(UPPER($0)):VARCHAR(2147483647)
CHARACTER SET "UTF-16LE", _UTF-16LE'A'), _UTF-16LE'OK':VARCHAR(2147483647)
CHARACTER SET "UTF-16LE", _UTF-16LE'N/A':VARCHAR(2147483647) CHARACTER SET
"UTF-16LE")])
+ HiveTableScan(table=[[default, t1]], table:alias=[t1])
+
+PREHOOK: query: explain
+select col1, case when upper(col1)='A' then 'OK' else 'N/A' end as col2 from t1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t1
+#### A masked pattern was here ####
+POSTHOOK: query: explain
+select col1, case when upper(col1)='A' then 'OK' else 'N/A' end as col2 from t1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t1
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-0
+ Fetch Operator
+ limit: -1
+ Processor Tree:
+ TableScan
+ alias: t1
+ Select Operator
+ expressions: col1 (type: char(3)), if((CAST( upper(col1) AS
STRING) = 'A'), 'OK', 'N/A') (type: string)
+ outputColumnNames: _col0, _col1
+ ListSink
+
+PREHOOK: query: select col1, case when upper(col1)='A' then 'OK' else 'N/A'
end as col2 from t1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t1
+#### A masked pattern was here ####
+POSTHOOK: query: select col1, case when upper(col1)='A' then 'OK' else 'N/A'
end as col2 from t1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t1
+#### A masked pattern was here ####
+A OK
+b N/A
+c N/A
+NULL N/A
+PREHOOK: query: explain cbo
+select col1,
+ case lower(col1)
+ when 'a' then 'OK a'
+ when 'b' then 'OK b'
+ else 'N/A'
+ end as col2 from t1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t1
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo
+select col1,
+ case lower(col1)
+ when 'a' then 'OK a'
+ when 'b' then 'OK b'
+ else 'N/A'
+ end as col2 from t1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t1
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(col1=[$0], col2=[CASE(=(CAST(LOWER($0)):VARCHAR(2147483647)
CHARACTER SET "UTF-16LE", _UTF-16LE'a'), _UTF-16LE'OK a':VARCHAR(2147483647)
CHARACTER SET "UTF-16LE", =(CAST(LOWER($0)):VARCHAR(2147483647) CHARACTER SET
"UTF-16LE", _UTF-16LE'b'), _UTF-16LE'OK b':VARCHAR(2147483647) CHARACTER SET
"UTF-16LE", _UTF-16LE'N/A':VARCHAR(2147483647) CHARACTER SET "UTF-16LE")])
+ HiveTableScan(table=[[default, t1]], table:alias=[t1])
+
+PREHOOK: query: explain cbo
+select col1,
+ case
+ when lower(col1) = 'a' then 'OK a'
+ when lower(col1) = 'b' then 'OK b'
+ else 'N/A'
+ end as col2 from t1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t1
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo
+select col1,
+ case
+ when lower(col1) = 'a' then 'OK a'
+ when lower(col1) = 'b' then 'OK b'
+ else 'N/A'
+ end as col2 from t1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t1
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(col1=[$0], col2=[CASE(=(CAST(LOWER($0)):VARCHAR(2147483647)
CHARACTER SET "UTF-16LE", _UTF-16LE'a'), _UTF-16LE'OK a':VARCHAR(2147483647)
CHARACTER SET "UTF-16LE", =(CAST(LOWER($0)):VARCHAR(2147483647) CHARACTER SET
"UTF-16LE", _UTF-16LE'b'), _UTF-16LE'OK b':VARCHAR(2147483647) CHARACTER SET
"UTF-16LE", _UTF-16LE'N/A':VARCHAR(2147483647) CHARACTER SET "UTF-16LE")])
+ HiveTableScan(table=[[default, t1]], table:alias=[t1])
+
+PREHOOK: query: select col1,
+ case lower(col1)
+ when 'a' then 'OK a'
+ when 'b' then 'OK b'
+ else 'N/A'
+ end as col2 from t1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t1
+#### A masked pattern was here ####
+POSTHOOK: query: select col1,
+ case lower(col1)
+ when 'a' then 'OK a'
+ when 'b' then 'OK b'
+ else 'N/A'
+ end as col2 from t1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t1
+#### A masked pattern was here ####
+A OK a
+b OK b
+c N/A
+NULL N/A
diff --git a/ql/src/test/results/clientpositive/llap/order_by_expr_2.q.out
b/ql/src/test/results/clientpositive/llap/order_by_expr_2.q.out
index 78bd0459bc3..04bc76156a3 100644
--- a/ql/src/test/results/clientpositive/llap/order_by_expr_2.q.out
+++ b/ql/src/test/results/clientpositive/llap/order_by_expr_2.q.out
@@ -139,7 +139,7 @@ POSTHOOK: Input: default@store
CBO PLAN:
HiveProject(c0=[$0], c1=[$1], c2=[$2])
HiveSortLimit(sort0=[$3], dir0=[ASC])
- HiveProject(c0=[$0], c1=[$1], c2=[$2], (tok_function case
(tok_table_or_col store_name) 'HQ' tok_null (tok_table_or_col store_name))=[$1])
+ HiveProject(c0=[$0], c1=[$1], c2=[$2], (tok_function when (=
(tok_table_or_col store_name) 'HQ') tok_null (tok_table_or_col
store_name))=[$1])
HiveAggregate(group=[{0, 1, 2}])
HiveProject($f0=[$0], $f1=[CASE(=($0, _UTF-16LE'HQ'),
null:VARCHAR(2147483647) CHARACTER SET "UTF-16LE", $0)], $f2=[$1])
HiveTableScan(table=[[default, store]], table:alias=[store])
@@ -183,7 +183,7 @@ POSTHOOK: Input: default@store
CBO PLAN:
HiveProject(c0=[$0], c1=[$1], c2=[$2])
HiveSortLimit(sort0=[$3], dir0=[ASC])
- HiveProject(c0=[$0], c1=[$1], c2=[$2], (tok_function case
(tok_table_or_col store_name) 'hq' tok_null (tok_table_or_col
store_name))0=[CASE(=($0, _UTF-16LE'hq'), null:VARCHAR(2147483647) CHARACTER
SET "UTF-16LE", $0)])
+ HiveProject(c0=[$0], c1=[$1], c2=[$2], (tok_function when (=
(tok_table_or_col store_name) 'hq') tok_null (tok_table_or_col
store_name))0=[CASE(=($0, _UTF-16LE'hq'), null:VARCHAR(2147483647) CHARACTER
SET "UTF-16LE", $0)])
HiveAggregate(group=[{0, 1, 2}])
HiveProject($f0=[$0], $f1=[CASE(=($0, _UTF-16LE'HQ'),
null:VARCHAR(2147483647) CHARACTER SET "UTF-16LE", $0)], $f2=[$1])
HiveTableScan(table=[[default, store]], table:alias=[store])
diff --git a/ql/src/test/results/clientpositive/llap/show_functions.q.out
b/ql/src/test/results/clientpositive/llap/show_functions.q.out
index 1de66c3def6..9150f13015b 100644
--- a/ql/src/test/results/clientpositive/llap/show_functions.q.out
+++ b/ql/src/test/results/clientpositive/llap/show_functions.q.out
@@ -73,7 +73,6 @@ bround
bucket_number
buildversion
cardinality_violation
-case
cast_format
cbrt
ceil
@@ -528,7 +527,6 @@ PREHOOK: type: SHOWFUNCTIONS
POSTHOOK: query: SHOW FUNCTIONS LIKE 'c%'
POSTHOOK: type: SHOWFUNCTIONS
cardinality_violation
-case
cast_format
cbrt
ceil
@@ -569,7 +567,6 @@ POSTHOOK: type: SHOWFUNCTIONS
array_remove
array_slice
assert_true
-case
coalesce
current_database
current_date
@@ -711,7 +708,6 @@ bround
bucket_number
buildversion
cardinality_violation
-case
cast_format
cbrt
ceil
diff --git a/ql/src/test/results/clientpositive/llap/udf_case.q.out
b/ql/src/test/results/clientpositive/llap/udf_case.q.out
index 80c0ec52e09..bfc8880f00e 100644
--- a/ql/src/test/results/clientpositive/llap/udf_case.q.out
+++ b/ql/src/test/results/clientpositive/llap/udf_case.q.out
@@ -1,27 +1,3 @@
-PREHOOK: query: DESCRIBE FUNCTION case
-PREHOOK: type: DESCFUNCTION
-POSTHOOK: query: DESCRIBE FUNCTION case
-POSTHOOK: type: DESCFUNCTION
-CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END - When a = b, returns c;
when a = d, return e; else return f
-PREHOOK: query: DESCRIBE FUNCTION EXTENDED case
-PREHOOK: type: DESCFUNCTION
-POSTHOOK: query: DESCRIBE FUNCTION EXTENDED case
-POSTHOOK: type: DESCFUNCTION
-CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END - When a = b, returns c;
when a = d, return e; else return f
-Example:
- SELECT
- CASE deptno
- WHEN 1 THEN Engineering
- WHEN 2 THEN Finance
- ELSE admin
- END,
- CASE zone
- WHEN 7 THEN Americas
- ELSE Asia-Pac
- END
- FROM emp_details
-Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFCase
-Function type:BUILTIN
PREHOOK: query: EXPLAIN
SELECT CASE 1
WHEN 1 THEN 2
diff --git a/ql/src/test/results/clientpositive/llap/udf_case_thrift.q.out
b/ql/src/test/results/clientpositive/llap/udf_case_thrift.q.out
index 3a70d8b4d60..6f7322821d0 100644
--- a/ql/src/test/results/clientpositive/llap/udf_case_thrift.q.out
+++ b/ql/src/test/results/clientpositive/llap/udf_case_thrift.q.out
@@ -50,7 +50,7 @@ STAGE PLANS:
alias: src_thrift
Row Limit Per Split: 3
Select Operator
- expressions: CASE (lint[0]) WHEN (0) THEN ((lint[0] + 1)) WHEN (1)
THEN ((lint[0] + 2)) WHEN (2) THEN (100) ELSE (5) END (type: int), CASE
(lstring[0]) WHEN ('0') THEN ('zero') WHEN ('10') THEN (concat(lstring[0], ' is
ten')) ELSE ('default') END (type: string), CASE (lstring[0]) WHEN ('0') THEN
(lstring) ELSE (null) END[0] (type: string)
+ expressions: CASE WHEN ((lint[0] = 0)) THEN ((lint[0] + 1)) WHEN
((lint[0] = 1)) THEN ((lint[0] + 2)) WHEN ((lint[0] = 2)) THEN (100) ELSE (5)
END (type: int), CASE WHEN ((lstring[0] = '0')) THEN ('zero') WHEN ((lstring[0]
= '10')) THEN (concat(lstring[0], ' is ten')) ELSE ('default') END (type:
string), CASE WHEN ((lstring[0] = '0')) THEN (lstring) ELSE (null) END[0]
(type: string)
outputColumnNames: _col0, _col1, _col2
ListSink
diff --git
a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query39.q.out
b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query39.q.out
index 1ef18c2019d..5e6bcc5f689 100644
--- a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query39.q.out
+++ b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query39.q.out
@@ -3,8 +3,8 @@ HiveProject(w_warehouse_sk=[$0], i_item_sk=[$1],
d_moy=[CAST(4):INTEGER], mean=[
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$6],
sort5=[$7], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC],
dir5=[ASC])
HiveProject(w_warehouse_sk=[$0], i_item_sk=[$1], mean=[$6], cov=[$7],
w_warehouse_sk0=[$0], i_item_sk0=[$1], mean0=[$2], cov0=[$3])
HiveJoin(condition=[AND(=($5, $1), =($4, $0))], joinType=[inner],
algorithm=[none], cost=[not available])
- HiveProject(w_warehouse_sk=[$0], i_item_sk=[$1],
mean=[/(CAST($5):DOUBLE, $6)], cov=[CASE(=(/(CAST($5):DOUBLE, $6), 0),
null:DOUBLE, /(POWER(/(-($2, /(*($3, $3), $4)), CASE(=($4, 1), null:BIGINT,
-($4, 1))), 0.5:DECIMAL(2, 1)), /(CAST($5):DOUBLE, $6)))])
- HiveFilter(condition=[CASE(=(/(CAST($5):DOUBLE, $6), 0), false,
>(/(POWER(/(-($2, /(*($3, $3), $4)), CASE(=($4, 1), null:BIGINT, -($4, 1))),
0.5:DECIMAL(2, 1)), /(CAST($5):DOUBLE, $6)), 1))])
+ HiveProject(w_warehouse_sk=[$0], i_item_sk=[$1],
mean=[/(CAST($5):DOUBLE, $6)], cov=[CASE(=(/(CAST($5):DOUBLE, $6), 0E0),
null:DOUBLE, /(POWER(/(-($2, /(*($3, $3), $4)), CASE(=($4, 1), null:BIGINT,
-($4, 1))), 0.5:DECIMAL(2, 1)), /(CAST($5):DOUBLE, $6)))])
+ HiveFilter(condition=[CASE(=(/(CAST($5):DOUBLE, $6), 0E0), false,
>(/(POWER(/(-($2, /(*($3, $3), $4)), CASE(=($4, 1), null:BIGINT, -($4, 1))),
0.5:DECIMAL(2, 1)), /(CAST($5):DOUBLE, $6)), 1))])
HiveAggregate(group=[{1, 2}], agg#0=[sum($5)], agg#1=[sum($4)],
agg#2=[count($4)], agg#3=[sum($3)], agg#4=[count($3)])
HiveProject($f0=[$6], $f1=[$5], $f2=[$3], $f4=[$2],
$f40=[CAST($2):DOUBLE], $f6=[*(CAST($2):DOUBLE, CAST($2):DOUBLE)])
HiveJoin(condition=[=($1, $5)], joinType=[inner],
algorithm=[none], cost=[not available])
@@ -16,8 +16,8 @@ HiveProject(w_warehouse_sk=[$0], i_item_sk=[$1],
d_moy=[CAST(4):INTEGER], mean=[
HiveTableScan(table=[[default, date_dim]],
table:alias=[date_dim])
HiveProject(w_warehouse_sk=[$0], w_warehouse_name=[$2])
HiveTableScan(table=[[default, warehouse]],
table:alias=[warehouse])
- HiveProject(w_warehouse_sk=[$0], i_item_sk=[$1],
mean=[/(CAST($5):DOUBLE, $6)], cov=[CASE(=(/(CAST($5):DOUBLE, $6), 0),
null:DOUBLE, /(POWER(/(-($2, /(*($3, $3), $4)), CASE(=($4, 1), null:BIGINT,
-($4, 1))), 0.5:DECIMAL(2, 1)), /(CAST($5):DOUBLE, $6)))])
- HiveFilter(condition=[CASE(=(/(CAST($5):DOUBLE, $6), 0), false,
>(/(POWER(/(-($2, /(*($3, $3), $4)), CASE(=($4, 1), null:BIGINT, -($4, 1))),
0.5:DECIMAL(2, 1)), /(CAST($5):DOUBLE, $6)), 1))])
+ HiveProject(w_warehouse_sk=[$0], i_item_sk=[$1],
mean=[/(CAST($5):DOUBLE, $6)], cov=[CASE(=(/(CAST($5):DOUBLE, $6), 0E0),
null:DOUBLE, /(POWER(/(-($2, /(*($3, $3), $4)), CASE(=($4, 1), null:BIGINT,
-($4, 1))), 0.5:DECIMAL(2, 1)), /(CAST($5):DOUBLE, $6)))])
+ HiveFilter(condition=[CASE(=(/(CAST($5):DOUBLE, $6), 0E0), false,
>(/(POWER(/(-($2, /(*($3, $3), $4)), CASE(=($4, 1), null:BIGINT, -($4, 1))),
0.5:DECIMAL(2, 1)), /(CAST($5):DOUBLE, $6)), 1))])
HiveAggregate(group=[{1, 2}], agg#0=[sum($5)], agg#1=[sum($4)],
agg#2=[count($4)], agg#3=[sum($3)], agg#4=[count($3)])
HiveProject($f0=[$6], $f1=[$5], $f2=[$3], $f4=[$2],
$f40=[CAST($2):DOUBLE], $f6=[*(CAST($2):DOUBLE, CAST($2):DOUBLE)])
HiveJoin(condition=[=($1, $5)], joinType=[inner],
algorithm=[none], cost=[not available])
