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 5f046e6fb89 HIVE-26768: HPLSQL UDF is not working if it is applied on a column of type varchar/char/decimal in a table. (Dayakar M, reviewed by Krisztian Kasa) 5f046e6fb89 is described below commit 5f046e6fb898a972b0756eb5aa73886c912bfd2e Author: Dayakar M <59791497+mdaya...@users.noreply.github.com> AuthorDate: Tue Nov 29 10:41:26 2022 +0530 HIVE-26768: HPLSQL UDF is not working if it is applied on a column of type varchar/char/decimal in a table. (Dayakar M, reviewed by Krisztian Kasa) --- .../main/java/org/apache/hive/hplsql/udf/Udf.java | 110 +++++++++-- .../apache/hive/beeline/TestHplSqlViaBeeLine.java | 214 ++++++++++++++++++++- 2 files changed, 294 insertions(+), 30 deletions(-) diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/udf/Udf.java b/hplsql/src/main/java/org/apache/hive/hplsql/udf/Udf.java index 552100d777a..8609b080e36 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/udf/Udf.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/udf/Udf.java @@ -18,6 +18,7 @@ package org.apache.hive.hplsql.udf; +import org.apache.hadoop.hive.common.type.*; import org.apache.hadoop.hive.metastore.api.StoredProcedure; import org.apache.hadoop.hive.metastore.api.StoredProcedureRequest; import org.apache.hadoop.hive.ql.exec.Description; @@ -29,10 +30,8 @@ import org.apache.hadoop.hive.ql.udf.UDFType; import org.apache.hadoop.hive.ql.udf.generic.GenericUDF; import org.apache.hadoop.hive.serde2.objectinspector.ConstantObjectInspector; import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; -import org.apache.hadoop.hive.serde2.objectinspector.primitive.IntObjectInspector; -import org.apache.hadoop.hive.serde2.objectinspector.primitive.LongObjectInspector; -import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory; -import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.primitive.*; import org.apache.hive.hplsql.Arguments; import org.apache.hive.hplsql.Exec; import org.apache.hive.hplsql.Scope; @@ -128,27 +127,96 @@ public class Udf extends GenericUDF { void setParameters(DeferredObject[] arguments) throws HiveException { for (int i = 1; i < arguments.length; i++) { String name = ":" + i; - if (argumentsOI[i] instanceof StringObjectInspector) { - String value = ((StringObjectInspector)argumentsOI[i]).getPrimitiveJavaObject(arguments[i].get()); - if (value != null) { - exec.setVariable(name, value); - } + Object inputObject = arguments[i].get(); + ObjectInspector objectInspector = argumentsOI[i]; + if (objectInspector.getCategory() == ObjectInspector.Category.PRIMITIVE) { + setParameterForPrimitiveTypeArgument(name, inputObject, objectInspector); + } else { + exec.setVariableToNull(name); } - else if (argumentsOI[i] instanceof IntObjectInspector) { - Integer value = (Integer)((IntObjectInspector)argumentsOI[i]).getPrimitiveJavaObject(arguments[i].get()); - if (value != null) { - exec.setVariable(name, new Var(new Long(value))); - } + } + } + + private void setParameterForPrimitiveTypeArgument(String name, Object inputObject, ObjectInspector objectInspector) { + PrimitiveObjectInspector.PrimitiveCategory primitiveCategory = + ((PrimitiveObjectInspector) objectInspector).getPrimitiveCategory(); + switch (primitiveCategory) { + case BOOLEAN: + Boolean booleanValue = (Boolean) ((BooleanObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (booleanValue != null) { + exec.setVariable(name, new Var(booleanValue)); } - else if (argumentsOI[i] instanceof LongObjectInspector) { - Long value = (Long)((LongObjectInspector)argumentsOI[i]).getPrimitiveJavaObject(arguments[i].get()); - if (value != null) { - exec.setVariable(name, new Var(value)); - } + break; + case SHORT: + Short shortValue = (Short) ((ShortObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (shortValue != null) { + exec.setVariable(name, new Var(shortValue.longValue())); } - else { - exec.setVariableToNull(name); + break; + case INT: + Integer intValue = (Integer) ((IntObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (intValue != null) { + exec.setVariable(name, new Var(intValue.longValue())); + } + break; + case LONG: + Long longValue = (Long) ((LongObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (longValue != null) { + exec.setVariable(name, new Var(longValue)); + } + break; + case FLOAT: + Float floatValue = (Float) ((FloatObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (floatValue != null) { + exec.setVariable(name, new Var(floatValue.doubleValue())); + } + break; + case DOUBLE: + Double doubleValue = (Double) ((DoubleObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (doubleValue != null) { + exec.setVariable(name, new Var(doubleValue)); + } + break; + case STRING: + String strValue = ((StringObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (strValue != null) { + exec.setVariable(name, new Var(strValue)); + } + break; + case DATE: + Date dateValue = ((DateObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (dateValue != null) { + exec.setVariable(name, new Var(java.sql.Date.valueOf(dateValue.toString()))); + } + break; + case TIMESTAMP: + Timestamp timestampValue = ((TimestampObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (timestampValue != null) { + java.sql.Timestamp timestamp = java.sql.Timestamp.valueOf(timestampValue.toString()); + timestamp.setNanos(timestampValue.getNanos()); + exec.setVariable(name, new Var(timestamp, 0)); + } + break; + case DECIMAL: + HiveDecimal decimalValue = ((HiveDecimalObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (decimalValue != null) { + exec.setVariable(name, new Var(decimalValue.bigDecimalValue())); + } + break; + case VARCHAR: + HiveVarchar varcharValue = ((HiveVarcharObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (varcharValue != null) { + exec.setVariable(name, new Var(varcharValue.getValue())); + } + break; + case CHAR: + HiveChar charValue = ((HiveCharObjectInspector) objectInspector).getPrimitiveJavaObject(inputObject); + if (charValue != null) { + exec.setVariable(name, new Var(charValue.getStrippedValue())); } + break; + default: + exec.setVariableToNull(name); } } diff --git a/itests/hive-unit/src/test/java/org/apache/hive/beeline/TestHplSqlViaBeeLine.java b/itests/hive-unit/src/test/java/org/apache/hive/beeline/TestHplSqlViaBeeLine.java index f8dcaed6c36..21ad3b472f8 100644 --- a/itests/hive-unit/src/test/java/org/apache/hive/beeline/TestHplSqlViaBeeLine.java +++ b/itests/hive-unit/src/test/java/org/apache/hive/beeline/TestHplSqlViaBeeLine.java @@ -171,17 +171,213 @@ public class TestHplSqlViaBeeLine { "SELECT * FROM result;\n"; testScriptFile(SCRIPT_TEXT, args(), "12345"); } - + @Test - public void testUdf() throws Throwable { + public void testUdfBoolean() throws Throwable { String SCRIPT_TEXT = - "DROP TABLE IF EXISTS result;\n" + - "CREATE TABLE result (s string);\n" + - "INSERT INTO result VALUES('alice');\n" + - "INSERT INTO result VALUES('bob');\n" + - "CREATE FUNCTION hello(p STRING) RETURNS STRING BEGIN RETURN 'hello ' || p; END;\n" + - "SELECT hello(s) FROM result;\n"; - testScriptFile(SCRIPT_TEXT, args(), "hello alice.*hello bob"); + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_b boolean);\n" + + "INSERT INTO result VALUES(true);\n" + + "INSERT INTO result VALUES(false);\n" + + "CREATE FUNCTION check(b boolean)\n" + + " RETURNS STRING\n" + + "BEGIN\n" + + " RETURN 'This is ' || b;\n" + + "END;\n" + + "SELECT check(col_b) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "This is true.*This is false"); + } + + @Test + public void testUdfSmallInt() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_s smallint);\n" + + "INSERT INTO result VALUES(123);\n" + + "INSERT INTO result VALUES(321);\n" + + "CREATE FUNCTION dbl(s smallint)\n" + + " RETURNS smallint\n" + + "BEGIN\n" + + " RETURN s + s;\n" + + "END;\n" + + "SELECT dbl(col_s) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "246.*642"); + } + + @Test + public void testUdfInt() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_i int);\n" + + "INSERT INTO result VALUES(12345);\n" + + "INSERT INTO result VALUES(54321);\n" + + "CREATE FUNCTION dbl(i int)\n" + + " RETURNS int\n" + + "BEGIN\n" + + " RETURN i * 2;\n" + + "END;\n" + + "SELECT dbl(col_i) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "24690.*108642"); + } + + @Test + public void testUdfBigInt() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_b bigint);\n" + + "INSERT INTO result VALUES(123456789);\n" + + "INSERT INTO result VALUES(987654321);\n" + + "CREATE FUNCTION dbl(b bigint)\n" + + " RETURNS int8\n" + + "BEGIN\n" + + " RETURN b * 2;\n" + + "END;\n" + + "SELECT dbl(col_b) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "246913578.*1975308642"); + } + + @Test + public void testUdfFloat() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_f float);\n" + + "INSERT INTO result VALUES(12345.6789);\n" + + "INSERT INTO result VALUES(98765.4321);\n" + + "CREATE FUNCTION dbl(f float)\n" + + " RETURNS float\n" + + "BEGIN\n" + + " RETURN f * 2;\n" + + "END;\n" + + "SELECT dbl(col_f) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "24691.357421875.*197530.859375"); + } + + @Test + public void testUdfDouble() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_d double);\n" + + "INSERT INTO result VALUES(123456789.12);\n" + + "INSERT INTO result VALUES(987654321.98);\n" + + "CREATE FUNCTION dbl(d float)\n" + + " RETURNS double\n" + + "BEGIN\n" + + " RETURN d * 2;\n" + + "END;\n" + + "SELECT dbl(col_d) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "2.4691357824E8.*1.97530864396E9"); + } + + @Test + public void testUdfString() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_s string);\n" + + "INSERT INTO result VALUES('Alice');\n" + + "INSERT INTO result VALUES('Smith');\n" + + "CREATE FUNCTION hello(s string)\n" + + " RETURNS string\n" + + "BEGIN\n" + + " RETURN 'Hello ' || s || '!';\n" + + "END;\n" + + "SELECT hello(col_s) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "Hello Alice!.*Hello Smith!"); + } + + @Test + public void testUdfDate() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_d date);\n" + + "INSERT INTO result VALUES('2022-11-24');\n" + + "INSERT INTO result VALUES('2022-12-25');\n" + + "CREATE FUNCTION date_today(d date)\n" + + " RETURNS date\n" + + "BEGIN\n" + + " RETURN d;\n" + + "END;\n" + + "SELECT date_today(col_d) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "2022-11-24.*2022-12-25"); + } + + @Test + public void testUdfTimestamp() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_t timestamp);\n" + + "INSERT INTO result VALUES('2022-11-24 10:20:30');\n" + + "INSERT INTO result VALUES('2022-12-25 06:30:30');\n" + + "CREATE FUNCTION time_today(t timestamp)\n" + + " RETURNS timestamp\n" + + "BEGIN\n" + + " RETURN t;\n" + + "END;\n" + + "SELECT time_today(col_t) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "2022-11-24 10:20:30.*2022-12-25 06:30:30"); + } + + @Test + public void testUdfDecimal() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_d decimal(15,2));\n" + + "INSERT INTO result VALUES(123456789.98);\n" + + "INSERT INTO result VALUES(987654321.12);\n" + + "CREATE FUNCTION triple(d decimal(15,2))\n" + + " RETURNS decimal(15,2)\n" + + "BEGIN\n" + + " RETURN d * 3;\n" + + "END;\n" + + "SELECT triple(col_d) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "370370369.94.*2962962963.36"); + } + + @Test + public void testUdfVarchar() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_v varchar(20));\n" + + "INSERT INTO result VALUES('Smith');\n" + + "INSERT INTO result VALUES('Sachin');\n" + + "CREATE FUNCTION hello(v varchar(20))\n" + + " RETURNS varchar(20)\n" + + "BEGIN\n" + + " RETURN 'Hello ' || v || '!';\n" + + "END;\n" + + "SELECT hello(col_v) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "Hello Smith!.*Hello Sachin!"); + } + + @Test + public void testUdfChar() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_c char(10));\n" + + "INSERT INTO result VALUES('Daya');\n" + + "INSERT INTO result VALUES('Alice');\n" + + "CREATE FUNCTION hello(c char(10))\n" + + " RETURNS char(10)\n" + + "BEGIN\n" + + " RETURN 'Hello ' || c || '!';\n" + + "END;\n" + + "SELECT hello(col_c) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "Hello Daya!.*Hello Alice!"); + } + + @Test + public void testUdfWhenUdfParamerAndActualParamDifferent() throws Throwable { + String SCRIPT_TEXT = + "DROP TABLE IF EXISTS result;\n" + + "CREATE TABLE result (col_d decimal(10,2));\n" + + "INSERT INTO result VALUES(12345.67);\n" + + "INSERT INTO result VALUES(98765.43);\n" + + "CREATE FUNCTION hello(s String)\n" + + " RETURNS String\n" + + "BEGIN\n" + + " RETURN 'Hello ' || s || '!';\n" + + "END;\n" + + "SELECT hello(col_d) FROM result;\n"; + testScriptFile(SCRIPT_TEXT, args(), "Hello 12345.67!.*Hello 98765.43!"); } @Test