[
https://issues.apache.org/jira/browse/HIVE-26768?focusedWorklogId=829251&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-829251
]
ASF GitHub Bot logged work on HIVE-26768:
-----------------------------------------
Author: ASF GitHub Bot
Created on: 28/Nov/22 09:56
Start Date: 28/Nov/22 09:56
Worklog Time Spent: 10m
Work Description: mdayakar commented on code in PR #3808:
URL: https://github.com/apache/hive/pull/3808#discussion_r1033335531
##########
itests/hive-unit/src/test/java/org/apache/hive/beeline/TestHplSqlViaBeeLine.java:
##########
@@ -171,17 +171,197 @@ public void testPackage() throws Throwable {
"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!");
}
Review Comment:
The function will be called if the actual parameter type can be converted to
udf parameter type. For example, if UDF is taking String and actual parameter
is decimal then in this case UDF will be called as Decimal can be easily
converted to String. Anyway I will add a testcase for the same.
Issue Time Tracking
-------------------
Worklog Id: (was: 829251)
Time Spent: 1.5h (was: 1h 20m)
> HPLSQL UDF is not working if it is applied on a column of type
> varchar/char/decimal in a table.
> -----------------------------------------------------------------------------------------------
>
> Key: HIVE-26768
> URL: https://issues.apache.org/jira/browse/HIVE-26768
> Project: Hive
> Issue Type: Bug
> Components: Hive
> Reporter: Dayakar M
> Assignee: Dayakar M
> Priority: Major
> Labels: pull-request-available
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> HPLSQL UDF is not working if it is applied on a column of type
> varchar/char/decimal in a table.
> For example:
> {code:java}
> CREATE TABLE result (s varchar(20));
> INSERT INTO result VALUES('alice');
> INSERT INTO result VALUES('bob');
> CREATE FUNCTION hello(p string)
> RETURNS STRING
> BEGIN
> RETURN 'Hello, ' || p;
> END;
> SELECT hello(s) FROM result; {code}
>
> --> It should return below
> {code:java}
> +----------------+
> | _c0 |
> +----------------+
> | Hello, alice |
> | Hello, bob |
> +----------------+
> {code}
>
> But actual result is
> {code:java}
> +----------------+
> | _c0 |
> +----------------+
> | Hello, |
> | Hello, |
> +----------------+
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
