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 c5c8fe4ed6c HIVE-27492: HPL/SQL built-in functions like sysdate not
working (Dayakar M, reviewed by Krisztian Kasa, Aman Sinha, Attila Turoczy)
c5c8fe4ed6c is described below
commit c5c8fe4ed6c4294298d75caeaf671ba632b2d4c2
Author: Dayakar M <[email protected]>
AuthorDate: Mon Jan 15 07:52:29 2024 +0530
HIVE-27492: HPL/SQL built-in functions like sysdate not working (Dayakar M,
reviewed by Krisztian Kasa, Aman Sinha, Attila Turoczy)
---
.../main/antlr4/org/apache/hive/hplsql/Hplsql.g4 | 6 +-
.../src/main/java/org/apache/hive/hplsql/Exec.java | 2 +-
.../src/main/java/org/apache/hive/hplsql/Var.java | 4 +-
.../hive/hplsql/functions/BuiltinFunctions.java | 7 +-
.../hive/hplsql/functions/FunctionDatetime.java | 47 +---
.../apache/hive/hplsql/functions/FunctionMisc.java | 60 +----
.../hive/hplsql/functions/FunctionString.java | 162 +++----------
.../org/apache/hive/hplsql/TestHplsqlLocal.java | 49 ----
hplsql/src/test/queries/local/cast.sql | 4 -
hplsql/src/test/queries/local/cast2.sql | 10 -
hplsql/src/test/queries/local/coalesce.sql | 4 -
hplsql/src/test/queries/local/concat.sql | 2 +-
hplsql/src/test/queries/local/create_function3.sql | 22 +-
hplsql/src/test/queries/local/create_function4.sql | 4 +-
hplsql/src/test/queries/local/date.sql | 3 +-
hplsql/src/test/queries/local/expr.sql | 5 +-
hplsql/src/test/queries/local/if2.sql | 2 +-
hplsql/src/test/queries/local/instr.sql | 49 ----
hplsql/src/test/queries/local/interval.sql | 8 +-
hplsql/src/test/queries/local/length.sql | 1 -
hplsql/src/test/queries/local/lower.sql | 1 -
hplsql/src/test/queries/local/mult_div.sql | 4 +-
hplsql/src/test/queries/local/nvl.sql | 4 -
hplsql/src/test/queries/local/replace.sql | 1 -
hplsql/src/test/queries/local/timestamp.sql | 4 -
hplsql/src/test/queries/local/timestamp_iso.sql | 3 +-
hplsql/src/test/queries/local/trim.sql | 1 -
hplsql/src/test/results/local/add.out.txt | 4 +-
hplsql/src/test/results/local/cast.out.txt | 8 -
hplsql/src/test/results/local/cast2.out.txt | 15 --
hplsql/src/test/results/local/coalesce.out.txt | 4 -
hplsql/src/test/results/local/date.out.txt | 7 +-
hplsql/src/test/results/local/declare.out.txt | 4 +-
hplsql/src/test/results/local/expr.out.txt | 4 -
hplsql/src/test/results/local/instr.out.txt | 33 ---
hplsql/src/test/results/local/interval.out.txt | 24 +-
.../src/test/results/local/invalid_syntax.out.txt | 2 +-
hplsql/src/test/results/local/length.out.txt | 1 -
hplsql/src/test/results/local/lower.out.txt | 1 -
hplsql/src/test/results/local/nvl.out.txt | 4 -
hplsql/src/test/results/local/replace.out.txt | 1 -
hplsql/src/test/results/local/sub.out.txt | 2 +-
hplsql/src/test/results/local/timestamp.out.txt | 4 -
.../src/test/results/local/timestamp_iso.out.txt | 3 +-
hplsql/src/test/results/local/to_char.out.txt | 2 +-
hplsql/src/test/results/local/to_timestamp.out.txt | 8 +-
hplsql/src/test/results/local/trim.out.txt | 1 -
.../apache/hive/beeline/TestHplSqlViaBeeLine.java | 260 ++++++++++++++++++++-
48 files changed, 368 insertions(+), 493 deletions(-)
diff --git a/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4
b/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4
index 8bec8cd78c3..14cf034ad01 100644
--- a/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4
+++ b/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4
@@ -1049,8 +1049,8 @@ expr :
| expr_case
| expr_cursor_attribute
| expr_agg_window_func
- | expr_spec_func
| expr_func
+ | expr_spec_func
| expr_atom
;
@@ -1324,6 +1324,7 @@ non_reserved_words : // Tokens that
are not reserved words
| T_CURRENT
| T_CURRENT_DATE
| T_CURRENT_SCHEMA
+ | T_CURRENT_TIME_MILLIS
| T_CURRENT_TIMESTAMP
| T_CURRENT_USER
| T_CURSOR
@@ -1770,6 +1771,7 @@ T_MERGE : M E R G E ;
T_MESSAGE_TEXT : M E S S A G E '_' T E X T ;
T_MICROSECOND : M I C R O S E C O N D ;
T_MICROSECONDS : M I C R O S E C O N D S;
+T_MILLIS : M I L L I S ;
T_MIN : M I N ;
T_MULTISET : M U L T I S E T ;
T_NCHAR : N C H A R ;
@@ -1877,6 +1879,7 @@ T_TEMPORARY : T E M P O R A R Y ;
T_TERMINATED : T E R M I N A T E D ;
T_TEXTIMAGE_ON : T E X T I M A G E '_' O N ;
T_THEN : T H E N ;
+T_TIME : T I M E ;
T_TIMESTAMP : T I M E S T A M P ;
T_TINYINT : T I N Y I N T ;
T_TITLE : T I T L E ;
@@ -1913,6 +1916,7 @@ T_YES : Y E S ;
T_ACTIVITY_COUNT : A C T I V I T Y '_' C O U N T ;
T_CUME_DIST : C U M E '_' D I S T ;
T_CURRENT_DATE : C U R R E N T '_' D A T E ;
+T_CURRENT_TIME_MILLIS : C U R R E N T '_' T I M E '_' M I L L I S ;
T_CURRENT_TIMESTAMP : C U R R E N T '_' T I M E S T A M P ;
T_CURRENT_USER : C U R R E N T '_' U S E R ;
T_DENSE_RANK : D E N S E '_' R A N K ;
diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
index 8d5cab92e13..e423452e3da 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
@@ -1801,7 +1801,7 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
private int functionCall(ParserRuleContext ctx, HplsqlParser.IdentContext
ident, HplsqlParser.Expr_func_paramsContext params) {
String name = ident.getText();
- if (exec.buildSql) {
+ if (exec.buildSql && !builtinFunctions.exists(name)) {
exec.execSql(name, params);
} else {
name = name.toUpperCase();
diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Var.java
b/hplsql/src/main/java/org/apache/hive/hplsql/Var.java
index 198a4d0b09a..1c7bb41824c 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Var.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Var.java
@@ -601,7 +601,7 @@ public class Var {
return (String)value;
}
else if (type == Type.DATE) {
- return ((Date)value).toString();
+ return String.format("DATE '%s'", value);
}
else if (type == Type.TIMESTAMP) {
int len = 19;
@@ -612,7 +612,7 @@ public class Var {
if (t.length() > len) {
t = t.substring(0, len);
}
- return t;
+ return String.format("TIMESTAMP '%s'", t);
}
return value.toString();
}
diff --git
a/hplsql/src/main/java/org/apache/hive/hplsql/functions/BuiltinFunctions.java
b/hplsql/src/main/java/org/apache/hive/hplsql/functions/BuiltinFunctions.java
index e5335854d05..100fa19c534 100644
---
a/hplsql/src/main/java/org/apache/hive/hplsql/functions/BuiltinFunctions.java
+++
b/hplsql/src/main/java/org/apache/hive/hplsql/functions/BuiltinFunctions.java
@@ -114,7 +114,10 @@ public class BuiltinFunctions {
execMinPartDate(ctx);
} else if (ctx.T_PART_LOC() != null) {
execPartLoc(ctx);
- } else {
+ } else if (exec.buildSql){
+ exec.stackPush(Exec.getFormattedText(ctx));
+ }
+ else {
evalNull();
}
}
@@ -132,7 +135,7 @@ public class BuiltinFunctions {
func.run(ctx);
}
else {
- exec.stackPush(Exec.getFormattedText(ctx));
+ specExec(ctx);
}
}
diff --git
a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java
b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java
index 4870c14745a..e7e562aca67 100644
---
a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java
+++
b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java
@@ -21,7 +21,6 @@ package org.apache.hive.hplsql.functions;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
-import java.util.Date;
import java.util.TimeZone;
import org.apache.commons.lang3.StringUtils;
@@ -39,15 +38,11 @@ public class FunctionDatetime extends BuiltinFunctions {
@Override
public void register(BuiltinFunctions f) {
f.map.put("DATE", this::date);
- f.map.put("FROM_UNIXTIME", this::fromUnixtime);
f.map.put("NOW", ctx -> now(ctx));
f.map.put("TIMESTAMP_ISO", this::timestampIso);
f.map.put("TO_TIMESTAMP", this::toTimestamp);
- f.map.put("UNIX_TIMESTAMP", this::unixTimestamp);
f.map.put("CURRENT_TIME_MILLIS", this::currentTimeMillis);
- f.specMap.put("CURRENT_DATE", this::currentDate);
- f.specMap.put("CURRENT_TIMESTAMP", this::currentTimestamp);
f.specMap.put("SYSDATE", this::currentTimestamp);
f.specSqlMap.put("CURRENT_DATE", (FuncSpecCommand) this::currentDateSql);
@@ -57,10 +52,6 @@ public class FunctionDatetime extends BuiltinFunctions {
/**
* CURRENT_DATE
*/
- public void currentDate(HplsqlParser.Expr_spec_funcContext ctx) {
- evalVar(currentDate());
- }
-
public static Var currentDate() {
SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
String s = f.format(Calendar.getInstance().getTime());
@@ -86,7 +77,7 @@ public class FunctionDatetime extends BuiltinFunctions {
int precision = evalPop(ctx.expr(0), 3).intValue();
evalVar(currentTimestamp(precision));
}
-
+
public static Var currentTimestamp(int precision) {
String format = "yyyy-MM-dd HH:mm:ss";
if (precision > 0 && precision <= 3) {
@@ -118,7 +109,9 @@ public class FunctionDatetime extends BuiltinFunctions {
return;
}
Var var = new Var(Var.Type.DATE);
- var.cast(evalPop(ctx.func_param(0).expr()));
+ Var date = evalPop(ctx.func_param(0).expr());
+ date.setValue(Utils.unquoteString(date.toString()));
+ var.cast(date);
evalVar(var);
}
@@ -142,7 +135,9 @@ public class FunctionDatetime extends BuiltinFunctions {
return;
}
Var var = new Var(Var.Type.TIMESTAMP);
- var.cast(evalPop(ctx.func_param(0).expr()));
+ Var val = evalPop(ctx.func_param(0).expr());
+ val.setValue(Utils.unquoteString(val.toString()));
+ var.cast(val);
evalVar(var);
}
@@ -154,8 +149,8 @@ public class FunctionDatetime extends BuiltinFunctions {
evalNull();
return;
}
- String value = evalPop(ctx.func_param(0).expr()).toString();
- String sqlFormat = evalPop(ctx.func_param(1).expr()).toString();
+ String value =
Utils.unquoteString(evalPop(ctx.func_param(0).expr()).toString());
+ String sqlFormat =
Utils.unquoteString(evalPop(ctx.func_param(1).expr()).toString());
String format = Utils.convertSqlDatetimeFormat(sqlFormat);
try {
long timeInMs = new SimpleDateFormat(format).parse(value).getTime();
@@ -166,30 +161,6 @@ public class FunctionDatetime extends BuiltinFunctions {
evalNull();
}
}
-
- /**
- * FROM_UNIXTIME() function (convert seconds since 1970-01-01 00:00:00 to
timestamp)
- */
- void fromUnixtime(HplsqlParser.Expr_func_paramsContext ctx) {
- int cnt = BuiltinFunctions.getParamCount(ctx);
- if (cnt == 0) {
- evalNull();
- return;
- }
- long epoch = evalPop(ctx.func_param(0).expr()).longValue();
- String format = "yyyy-MM-dd HH:mm:ss";
- if (cnt > 1) {
- format = evalPop(ctx.func_param(1).expr()).toString();
- }
- evalString(new SimpleDateFormat(format).format(new Date(epoch * 1000)));
- }
-
- /**
- * UNIX_TIMESTAMP() function (current date and time in seconds since
1970-01-01 00:00:00)
- */
- void unixTimestamp(HplsqlParser.Expr_func_paramsContext ctx) {
- evalVar(new Var(System.currentTimeMillis()/1000));
- }
public void currentTimeMillis(HplsqlParser.Expr_func_paramsContext ctx) {
evalVar(new Var(System.currentTimeMillis()));
diff --git
a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionMisc.java
b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionMisc.java
index dba5594d933..18c2ded2e03 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionMisc.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionMisc.java
@@ -40,15 +40,11 @@ public class FunctionMisc extends BuiltinFunctions {
*/
@Override
public void register(BuiltinFunctions f) {
- f.map.put("COALESCE", this::nvl);
f.map.put("DECODE", this::decode);
- f.map.put("NVL", this::nvl);
f.map.put("NVL2", this::nvl2);
f.map.put("PART_COUNT_BY", this::partCountBy);
- f.map.put("MOD", this::modulo);
f.specMap.put("ACTIVITY_COUNT", this::activityCount);
- f.specMap.put("CAST", this::cast);
f.specMap.put("CURRENT", this::current);
f.specMap.put("CURRENT_USER", this::currentUser);
f.specMap.put("PART_COUNT", this::partCount);
@@ -64,28 +60,6 @@ public class FunctionMisc extends BuiltinFunctions {
evalInt(Long.valueOf(exec.getRowCount()));
}
- /**
- * CAST function
- */
- void cast(HplsqlParser.Expr_spec_funcContext ctx) {
- if (ctx.expr().size() != 1) {
- evalNull();
- return;
- }
- String type = ctx.dtype().getText();
- String len = null;
- String scale = null;
- if (ctx.dtype_len() != null) {
- len = ctx.dtype_len().L_INT(0).getText();
- if (ctx.dtype_len().L_INT(1) != null) {
- scale = ctx.dtype_len().L_INT(1).getText();
- }
- }
- Var var = new Var(null, type, len, scale, null);
- var.cast(evalPop(ctx.expr(0)));
- evalVar(var);
- }
-
/**
* CURRENT <VALUE> function
*/
@@ -124,8 +98,9 @@ public class FunctionMisc extends BuiltinFunctions {
else {
evalString("CURRENT_TIMESTAMP");
}
- }
- else {
+ } else if (ctx.T_USER() != null) {
+ evalString("CURRENT_USER()");
+ } else {
evalString(exec.getFormattedText(ctx));
}
}
@@ -138,7 +113,7 @@ public class FunctionMisc extends BuiltinFunctions {
}
public static Var currentUser() {
- return new Var(System.getProperty("user.name"));
+ return new Var("CURRENT_USER()");
}
/**
@@ -168,26 +143,13 @@ public class FunctionMisc extends BuiltinFunctions {
}
}
- /**
- * NVL function - Return first non-NULL expression
- */
- void nvl(HplsqlParser.Expr_func_paramsContext ctx) {
- for (int i=0; i < ctx.func_param().size(); i++) {
- Var v = evalPop(ctx.func_param(i).expr());
- if (v.type != Var.Type.NULL) {
- exec.stackPush(v);
- return;
- }
- }
- evalNull();
- }
-
/**
* NVL2 function - If expr1 is not NULL return expr2, otherwise expr3
*/
void nvl2(HplsqlParser.Expr_func_paramsContext ctx) {
if (ctx.func_param().size() == 3) {
- if (!evalPop(ctx.func_param(0).expr()).isNull()) {
+ Var firstParam = evalPop(ctx.func_param(0).expr());
+ if (!(firstParam.isNull() ||
"null".equalsIgnoreCase((String)firstParam.value))) {
eval(ctx.func_param(1).expr());
}
else {
@@ -249,16 +211,6 @@ public class FunctionMisc extends BuiltinFunctions {
query.close();
}
- public void modulo(HplsqlParser.Expr_func_paramsContext ctx) {
- if (ctx.func_param().size() == 2) {
- int a = evalPop(ctx.func_param(0).expr()).intValue();
- int b = evalPop(ctx.func_param(1).expr()).intValue();
- evalInt(a % b);
- } else {
- evalNull();
- }
- }
-
/**
* PART_COUNT_BY function
*/
diff --git
a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionString.java
b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionString.java
index 702da33908f..0020189a797 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionString.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionString.java
@@ -33,18 +33,12 @@ public class FunctionString extends BuiltinFunctions {
public void register(BuiltinFunctions f) {
f.map.put("CONCAT", this::concat);
f.map.put("CHAR", this::char_);
- f.map.put("INSTR", this::instr);
f.map.put("LEN", this::len);
- f.map.put("LENGTH", this::length);
- f.map.put("LOWER", this::lower);
- f.map.put("REPLACE", this::replace);
f.map.put("SUBSTR", this::substr);
f.map.put("SUBSTRING", this::substr);
f.map.put("TO_CHAR", this::toChar);
f.map.put("UPPER", this::upper);
-
f.specMap.put("SUBSTRING", this::substring);
- f.specMap.put("TRIM", this::trim);
}
/**
@@ -52,12 +46,13 @@ public class FunctionString extends BuiltinFunctions {
*/
void concat(HplsqlParser.Expr_func_paramsContext ctx) {
StringBuilder val = new StringBuilder();
+ appendSingleQuote(val);
int cnt = getParamCount(ctx);
boolean nulls = true;
for (int i = 0; i < cnt; i++) {
Var c = evalPop(ctx.func_param(i).expr());
- if (!c.isNull()) {
- val.append(c.toString());
+ if (!c.isNull() && !"null".equalsIgnoreCase((String)c.value)) {
+ val.append(Utils.unquoteString(c.toString()));
nulls = false;
}
}
@@ -65,6 +60,7 @@ public class FunctionString extends BuiltinFunctions {
evalNull();
}
else {
+ appendSingleQuote(val);
evalString(val);
}
}
@@ -82,67 +78,6 @@ public class FunctionString extends BuiltinFunctions {
evalString(str);
}
- /**
- * INSTR function
- */
- void instr(HplsqlParser.Expr_func_paramsContext ctx) {
- int cnt = getParamCount(ctx);
- if (cnt < 2) {
- evalNull();
- return;
- }
- String str = evalPop(ctx.func_param(0).expr()).toString();
- if (str == null) {
- evalNull();
- return;
- }
- else if(str.isEmpty()) {
- evalInt(0);
- return;
- }
- String substr = evalPop(ctx.func_param(1).expr()).toString();
- int pos = 1;
- int occur = 1;
- int idx = 0;
- if (cnt >= 3) {
- pos = evalPop(ctx.func_param(2).expr()).intValue();
- if (pos == 0) {
- pos = 1;
- }
- }
- if (cnt >= 4) {
- occur = evalPop(ctx.func_param(3).expr()).intValue();
- if (occur < 0) {
- occur = 1;
- }
- }
- for (int i = occur; i > 0; i--) {
- if (pos > 0) {
- idx = str.indexOf(substr, pos - 1);
- }
- else {
- str = str.substring(0, str.length() - pos*(-1));
- idx = str.lastIndexOf(substr);
- }
- if (idx == -1) {
- idx = 0;
- break;
- }
- else {
- idx++;
- }
- if (i > 1) {
- if (pos > 0) {
- pos = idx + 1;
- }
- else {
- pos = (str.length() - idx + 1) * (-1);
- }
- }
- }
- evalInt(idx);
- }
-
/**
* LEN function (excluding trailing spaces)
*/
@@ -151,49 +86,10 @@ public class FunctionString extends BuiltinFunctions {
evalNull();
return;
}
- int len = evalPop(ctx.func_param(0).expr()).toString().trim().length();
+ int len =
Utils.unquoteString(evalPop(ctx.func_param(0).expr()).toString()).trim().length();
evalInt(len);
}
-
- /**
- * LENGTH function
- */
- void length(HplsqlParser.Expr_func_paramsContext ctx) {
- if (ctx.func_param().size() != 1) {
- evalNull();
- return;
- }
- int len = evalPop(ctx.func_param(0).expr()).toString().length();
- evalInt(len);
- }
-
- /**
- * LOWER function
- */
- void lower(HplsqlParser.Expr_func_paramsContext ctx) {
- if (ctx.func_param().size() != 1) {
- evalNull();
- return;
- }
- String str = evalPop(ctx.func_param(0).expr()).toString().toLowerCase();
- evalString(str);
- }
-
- /**
- * REPLACE function
- */
- void replace(HplsqlParser.Expr_func_paramsContext ctx) {
- int cnt = getParamCount(ctx);
- if (cnt < 3) {
- evalNull();
- return;
- }
- String str = evalPop(ctx.func_param(0).expr()).toString();
- String what = evalPop(ctx.func_param(1).expr()).toString();
- String with = evalPop(ctx.func_param(2).expr()).toString();
- evalString(str.replaceAll(what, with));
- }
-
+
/**
* SUBSTR and SUBSTRING function
*/
@@ -203,18 +99,18 @@ public class FunctionString extends BuiltinFunctions {
evalNull();
return;
}
- String str = evalPop(ctx.func_param(0).expr()).toString();
+ String str =
Utils.unquoteString(evalPop(ctx.func_param(0).expr()).toString());
int start = evalPop(ctx.func_param(1).expr()).intValue();
int len = -1;
if (start == 0) {
- start = 1;
+ start = 1;
}
if (cnt > 2) {
len = evalPop(ctx.func_param(2).expr()).intValue();
}
substr(str, start, len);
}
-
+
void substr(String str, int start, int len) {
if (str == null) {
evalNull();
@@ -225,27 +121,42 @@ public class FunctionString extends BuiltinFunctions {
return;
}
if (start == 0) {
- start = 1;
+ start = 1;
}
+ StringBuilder resultStr = new StringBuilder();
if (len == -1) {
if (start > 0) {
- evalString(str.substring(start - 1));
+ String substring = str.substring(start - 1);
+ appendSingleQuote(resultStr);
+ resultStr.append(substring);
+ appendSingleQuote(resultStr);
+ evalString(resultStr);
}
}
else {
- evalString(str.substring(start - 1, start - 1 + len));
+ String substring = str.substring(start - 1, start - 1 + len);
+ appendSingleQuote(resultStr);
+ resultStr.append(substring);
+ appendSingleQuote(resultStr);
+ evalString(resultStr);
}
}
-
+
+ private void appendSingleQuote(StringBuilder resultStr) {
+ if (exec.buildSql) {
+ resultStr.append("'");
+ }
+ }
+
/**
* SUBSTRING FROM FOR function
*/
void substring(HplsqlParser.Expr_spec_funcContext ctx) {
- String str = evalPop(ctx.expr(0)).toString();
+ String str = evalPop(ctx.expr(0)).toString();
int start = evalPop(ctx.expr(1)).intValue();
int len = -1;
if (start == 0) {
- start = 1;
+ start = 1;
}
if (ctx.T_FOR() != null) {
len = evalPop(ctx.expr(2)).intValue();
@@ -253,19 +164,6 @@ public class FunctionString extends BuiltinFunctions {
substr(str, start, len);
}
- /**
- * TRIM function
- */
- void trim(HplsqlParser.Expr_spec_funcContext ctx) {
- int cnt = ctx.expr().size();
- if (cnt != 1) {
- evalNull();
- return;
- }
- String str = evalPop(ctx.expr(0)).toString();
- evalString(str.trim());
- }
-
/**
* TO_CHAR function
*/
diff --git a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java
b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java
index 064ea9116e2..0af1f83be42 100644
--- a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java
+++ b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java
@@ -64,26 +64,11 @@ public class TestHplsqlLocal {
run("case");
}
- @Test
- public void testCast() throws Exception {
- run("cast");
- }
-
- @Test
- public void testCast2() throws Exception {
- run("cast2");
- }
-
@Test
public void testChar() throws Exception {
run("char");
}
- @Test
- public void testCoalesce() throws Exception {
- run("coalesce");
- }
-
@Test
public void testConcat() throws Exception {
run("concat");
@@ -273,10 +258,6 @@ public class TestHplsqlLocal {
public void testInclude() throws Exception {
run("include");
}
- @Test
- public void testInstr() throws Exception {
- run("instr");
- }
@Test
public void testInterval() throws Exception {
@@ -293,31 +274,16 @@ public class TestHplsqlLocal {
run("leave");
}
- @Test
- public void testLength() throws Exception {
- run("length");
- }
-
@Test
public void testLen() throws Exception {
run("len");
}
- @Test
- public void testLower() throws Exception {
- run("lower");
- }
-
@Test
public void testMultDiv() throws Exception {
run("mult_div");
}
- @Test
- public void testNvl() throws Exception {
- run("nvl");
- }
-
@Test
public void testNvl2() throws Exception {
run("nvl2");
@@ -327,11 +293,6 @@ public class TestHplsqlLocal {
public void testPrint() throws Exception {
run("print");
}
-
- @Test
- public void testReplace() throws Exception {
- run("replace");
- }
@Test
public void testReturn() throws Exception {
@@ -363,11 +324,6 @@ public class TestHplsqlLocal {
run("timestamp_iso");
}
- @Test
- public void testTimestamp() throws Exception {
- run("timestamp");
- }
-
@Test
public void testToChar() throws Exception {
run("to_char");
@@ -378,11 +334,6 @@ public class TestHplsqlLocal {
run("to_timestamp");
}
- @Test
- public void testTrim() throws Exception {
- run("trim");
- }
-
@Test
public void testTwoPipes() throws Exception {
run("twopipes");
diff --git a/hplsql/src/test/queries/local/cast.sql
b/hplsql/src/test/queries/local/cast.sql
deleted file mode 100644
index 3adab22823d..00000000000
--- a/hplsql/src/test/queries/local/cast.sql
+++ /dev/null
@@ -1,4 +0,0 @@
-CAST('Abc' AS CHAR(1));
-CAST('Abc' AS VARCHAR(2));
-CAST('Abc' AS CHAR);
-CAST(TIMESTAMP '2015-03-12 10:58:34.111' AS CHAR(10))
diff --git a/hplsql/src/test/queries/local/cast2.sql
b/hplsql/src/test/queries/local/cast2.sql
deleted file mode 100644
index d68db6d1b75..00000000000
--- a/hplsql/src/test/queries/local/cast2.sql
+++ /dev/null
@@ -1,10 +0,0 @@
-temp_int = CAST('1' AS int);
-print temp_int
-temp_float = CAST('1.2' AS float);
-print temp_float
-temp_double = CAST('1.2' AS double);
-print temp_double
-temp_decimal = CAST('1.2' AS decimal(10, 4));
-print temp_decimal
-temp_string = CAST('1.2' AS string);
-print temp_string
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/coalesce.sql
b/hplsql/src/test/queries/local/coalesce.sql
deleted file mode 100644
index 4b65d58b56f..00000000000
--- a/hplsql/src/test/queries/local/coalesce.sql
+++ /dev/null
@@ -1,4 +0,0 @@
-COALESCE('First non-null', 1);
-COALESCE(NULL, 'First non-null');
-COALESCE(NULL, 'First non-null', 1);
-COALESCE(NULL, NULL, 'First non-null', 1);
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/concat.sql
b/hplsql/src/test/queries/local/concat.sql
index b7769bb9824..37b7619ccb5 100644
--- a/hplsql/src/test/queries/local/concat.sql
+++ b/hplsql/src/test/queries/local/concat.sql
@@ -1,2 +1,2 @@
CONCAT('a', 'b', NULL, 'c');
-NVL(CONCAT(NULL, NULL, NULL), 'NULL Value');
\ No newline at end of file
+NVL2(CONCAT(NULL, NULL, NULL), NULL, 'NULL Value');
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/create_function3.sql
b/hplsql/src/test/queries/local/create_function3.sql
index 840c0957356..ec1de72fceb 100644
--- a/hplsql/src/test/queries/local/create_function3.sql
+++ b/hplsql/src/test/queries/local/create_function3.sql
@@ -6,47 +6,47 @@ FUNCTION gettype(tag1 varchar2, srcvalue varchar2) return
varchar2 as
return '@I';
end if;
- if trim(tag1) = 'WHMM' then
+ if (tag1) = 'WHMM' then
return '002';
end if;
- if trim(tag1) = 'TCPJ' and srcvalue = '010105' then
+ if (tag1) = 'TCPJ' and srcvalue = '010105' then
return '010105';
end if;
- if trim(tag1) = 'TCPJ' and srcvalue != '010105' then
+ if (tag1) = 'TCPJ' and srcvalue != '010105' then
return '003';
end if;
- if trim(tag1) = 'TCPJ' and srcvalue != '010105' then
+ if (tag1) = 'TCPJ' and srcvalue != '010105' then
return '003_ticket';
end if;
- if trim(tag1) = 'TCJY' and srcvalue != '010105' then
+ if (tag1) = 'TCJY' and srcvalue != '010105' then
return '003_ticket';
end if;
- if trim(tag1) = 'TCJY' and srcvalue != '010105' then
+ if (tag1) = 'TCJY' and srcvalue != '010105' then
return '003_ticket';
end if;
- if trim(tag1) = 'YHHPD' then
+ if (tag1) = 'YHHPD' then
return '002_foreign';
end if;
- if trim(tag1) = 'WHWZ' then
+ if (tag1) = 'WHWZ' then
return '002_foreign';
end if;
- if trim(tag1) = 'WHLZ' then
+ if (tag1) = 'WHLZ' then
return '002_foreign';
end if;
- if trim(tag1) = 'DEWZ' then
+ if (tag1) = 'DEWZ' then
return '024_out';
end if;
- if trim(tag1) = 'DELZ' then
+ if (tag1) = 'DELZ' then
return '024_out';
end if;
diff --git a/hplsql/src/test/queries/local/create_function4.sql
b/hplsql/src/test/queries/local/create_function4.sql
index 21986e08171..bc373343649 100644
--- a/hplsql/src/test/queries/local/create_function4.sql
+++ b/hplsql/src/test/queries/local/create_function4.sql
@@ -4,10 +4,10 @@ FUNCTION get(CODE VARCHAR2) RETURN VARCHAR2 AS
TMPVAR := '';
- IF TRIM(TMPVAR) = '' THEN
+ IF (TMPVAR) = '' THEN
RETURN '00080000';
ELSE
- RETURN TRIM(TMPVAR);
+ RETURN (TMPVAR);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
diff --git a/hplsql/src/test/queries/local/date.sql
b/hplsql/src/test/queries/local/date.sql
index 2ef474329af..981075ce33d 100644
--- a/hplsql/src/test/queries/local/date.sql
+++ b/hplsql/src/test/queries/local/date.sql
@@ -1,5 +1,4 @@
DATE '2014-12-20'
DATE('2015-03-12');
-DATE('2015' || '-03-' || '12');
-DATE(TIMESTAMP '2015-03-12 10:58:34.111');
\ No newline at end of file
+DATE('2015' || '-03-' || '12');
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/expr.sql
b/hplsql/src/test/queries/local/expr.sql
index 33388a25b12..e65dff98b54 100644
--- a/hplsql/src/test/queries/local/expr.sql
+++ b/hplsql/src/test/queries/local/expr.sql
@@ -15,7 +15,4 @@ PRINT c;
PRINT 'Integer decrement';
c := 3;
c := c - 1;
-PRINT c;
-
-PRINT NVL(null - 3, 'Correct');
-PRINT NVL(null + 3, 'Correct');
\ No newline at end of file
+PRINT c;
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/if2.sql
b/hplsql/src/test/queries/local/if2.sql
index b645b86e72f..05884793971 100644
--- a/hplsql/src/test/queries/local/if2.sql
+++ b/hplsql/src/test/queries/local/if2.sql
@@ -1,4 +1,4 @@
-if not (coalesce(1,0) between 3 and 5) then
+if not (nvl2(1,0) between 3 and 5) then
print 'correct';
else
print 'failed';
diff --git a/hplsql/src/test/queries/local/instr.sql
b/hplsql/src/test/queries/local/instr.sql
deleted file mode 100644
index 9cd8dcacc3c..00000000000
--- a/hplsql/src/test/queries/local/instr.sql
+++ /dev/null
@@ -1,49 +0,0 @@
-IF INSTR('abc', 'b') = 2 THEN
- PRINT 'Correct';
-ELSE
- PRINT 'Failed';
-END IF;
-
-IF INSTR('abcabc', 'b', 3) = 5 THEN
- PRINT 'Correct';
-ELSE
- PRINT 'Failed';
-END IF;
-
-IF INSTR('abcabcabc', 'b', 3, 2) = 8 THEN
- PRINT 'Correct';
-ELSE
- PRINT 'Failed';
-END IF;
-
-IF INSTR('abcabcabc', 'b', -3) = 5 THEN
- PRINT 'Correct';
-ELSE
- PRINT 'Failed';
-END IF;
-
-IF INSTR('abcabcabc', 'b', -3, 2) = 2 THEN
- PRINT 'Correct';
-ELSE
- PRINT 'Failed';
-END IF;
-
-DECLARE c STRING;
-
-IF INSTR(c, 'b') IS NULL THEN
- PRINT 'Correct';
-ELSE
- PRINT 'Failed';
-END IF;
-
-IF INSTR(NULL, 'b') IS NULL THEN
- PRINT 'Correct';
-ELSE
- PRINT 'Failed';
-END IF;
-
-IF INSTR('', 'b') = 0 THEN
- PRINT 'Correct';
-ELSE
- PRINT 'Failed';
-END IF;
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/interval.sql
b/hplsql/src/test/queries/local/interval.sql
index e40fcbd229c..98cf4927c7e 100644
--- a/hplsql/src/test/queries/local/interval.sql
+++ b/hplsql/src/test/queries/local/interval.sql
@@ -2,15 +2,15 @@ DATE '2015-03-12' + 1 DAY;
TIMESTAMP '2015-03-12' + 1 DAY;
TIMESTAMP '2015-03-12 10:10:10.000' + 1 MICROSECOND;
-DATE '2015-03-12' + NVL(NULL, 3) DAYS;
-TIMESTAMP '2015-03-12' + NVL(NULL, 3) DAYS;
+DATE '2015-03-12' + NVL2(NULL, NULL, 3) DAYS;
+TIMESTAMP '2015-03-12' + NVL2(NULL, NULL, 3) DAYS;
DATE '2015-03-12' - 1 DAY;
TIMESTAMP '2015-03-12' - 1 DAY;
TIMESTAMP '2015-03-12 10:10:10.000' - 1 MICROSECOND;
-DATE '2015-03-12' - NVL(NULL, 3) DAYS;
-TIMESTAMP '2015-03-12' - NVL(NULL, 3) DAYS;
+DATE '2015-03-12' - NVL2(NULL, NULL, 3) DAYS;
+TIMESTAMP '2015-03-12' - NVL2(NULL, NULL, 3) DAYS;
TIMESTAMP '2015-03-12' - 1 DAY - 1 MICROSECOND;
diff --git a/hplsql/src/test/queries/local/length.sql
b/hplsql/src/test/queries/local/length.sql
deleted file mode 100644
index 42cf3ccf0a8..00000000000
--- a/hplsql/src/test/queries/local/length.sql
+++ /dev/null
@@ -1 +0,0 @@
-LENGTH('Abc ');
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/lower.sql
b/hplsql/src/test/queries/local/lower.sql
deleted file mode 100644
index f29b0e970ea..00000000000
--- a/hplsql/src/test/queries/local/lower.sql
+++ /dev/null
@@ -1 +0,0 @@
-LOWER('ABC');
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/mult_div.sql
b/hplsql/src/test/queries/local/mult_div.sql
index ebad8f46a15..23fe88f91d2 100644
--- a/hplsql/src/test/queries/local/mult_div.sql
+++ b/hplsql/src/test/queries/local/mult_div.sql
@@ -4,5 +4,5 @@ declare c int default 2;
print a/b/c;
-set a = 4 * 2 / cast(4 as int) /2;
-set b = 4 * 2 /cast(4 as int)/2;
\ No newline at end of file
+set a = 4 * 2 / 4 / 2;
+set b = 4 * 2 / 4 / 2;
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/nvl.sql
b/hplsql/src/test/queries/local/nvl.sql
deleted file mode 100644
index 1a843bcb28a..00000000000
--- a/hplsql/src/test/queries/local/nvl.sql
+++ /dev/null
@@ -1,4 +0,0 @@
-NVL('First non-null', 1);
-NVL(NULL, 'First non-null');
-NVL(NULL, 'First non-null', 1);
-NVL(NULL, NULL, 'First non-null', 1);
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/replace.sql
b/hplsql/src/test/queries/local/replace.sql
deleted file mode 100644
index 820aa067870..00000000000
--- a/hplsql/src/test/queries/local/replace.sql
+++ /dev/null
@@ -1 +0,0 @@
-replace('2016-03-03', '-', '');
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/timestamp.sql
b/hplsql/src/test/queries/local/timestamp.sql
deleted file mode 100644
index 2971ceac888..00000000000
--- a/hplsql/src/test/queries/local/timestamp.sql
+++ /dev/null
@@ -1,4 +0,0 @@
-TIMESTAMP '2015-03-03 11:39:31.123456';
-TIMESTAMP '2015-03-03 11:39:31.123';
-TIMESTAMP '2015-03-03 11:39:31';
-TIMESTAMP '2015-03-03-11.39.31.123';
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/timestamp_iso.sql
b/hplsql/src/test/queries/local/timestamp_iso.sql
index 9bcdfe08904..e6601801fe1 100644
--- a/hplsql/src/test/queries/local/timestamp_iso.sql
+++ b/hplsql/src/test/queries/local/timestamp_iso.sql
@@ -1,2 +1 @@
-TIMESTAMP_ISO('2015-03-12');
-TIMESTAMP_ISO(DATE '2015-03-12');
\ No newline at end of file
+TIMESTAMP_ISO('2015-03-12');
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/trim.sql
b/hplsql/src/test/queries/local/trim.sql
deleted file mode 100644
index f8a2978d650..00000000000
--- a/hplsql/src/test/queries/local/trim.sql
+++ /dev/null
@@ -1 +0,0 @@
-'#' || TRIM(' Hello ') || '#';
\ No newline at end of file
diff --git a/hplsql/src/test/results/local/add.out.txt
b/hplsql/src/test/results/local/add.out.txt
index 37a195bfe18..7cfd6d68a01 100644
--- a/hplsql/src/test/results/local/add.out.txt
+++ b/hplsql/src/test/results/local/add.out.txt
@@ -1,2 +1,2 @@
-2015-01-01
-2015-01-01
+DATE '2015-01-01'
+DATE '2015-01-01'
diff --git a/hplsql/src/test/results/local/cast.out.txt
b/hplsql/src/test/results/local/cast.out.txt
deleted file mode 100644
index f3de493e67c..00000000000
--- a/hplsql/src/test/results/local/cast.out.txt
+++ /dev/null
@@ -1,8 +0,0 @@
-Ln:1 FUNC CAST
-A
-Ln:2 FUNC CAST
-Ab
-Ln:3 FUNC CAST
-Abc
-Ln:4 FUNC CAST
-2015-03-12
diff --git a/hplsql/src/test/results/local/cast2.out.txt
b/hplsql/src/test/results/local/cast2.out.txt
deleted file mode 100644
index fc136b8f3f2..00000000000
--- a/hplsql/src/test/results/local/cast2.out.txt
+++ /dev/null
@@ -1,15 +0,0 @@
-Ln:1 SET temp_int = 1
-Ln:2 PRINT
-1
-Ln:3 SET temp_float = 1.2
-Ln:4 PRINT
-1.2
-Ln:5 SET temp_double = 1.2
-Ln:6 PRINT
-1.2
-Ln:7 SET temp_decimal = 1.2
-Ln:8 PRINT
-1.2
-Ln:9 SET temp_string = '1.2'
-Ln:10 PRINT
-1.2
diff --git a/hplsql/src/test/results/local/coalesce.out.txt
b/hplsql/src/test/results/local/coalesce.out.txt
deleted file mode 100644
index a111c8575fb..00000000000
--- a/hplsql/src/test/results/local/coalesce.out.txt
+++ /dev/null
@@ -1,4 +0,0 @@
-First non-null
-First non-null
-First non-null
-First non-null
diff --git a/hplsql/src/test/results/local/date.out.txt
b/hplsql/src/test/results/local/date.out.txt
index 118bd29a8c2..1e4435a3a24 100644
--- a/hplsql/src/test/results/local/date.out.txt
+++ b/hplsql/src/test/results/local/date.out.txt
@@ -1,4 +1,3 @@
-2014-12-20
-2015-03-12
-2015-03-12
-2015-03-12
+DATE '2014-12-20'
+DATE '2015-03-12'
+DATE '2015-03-12'
diff --git a/hplsql/src/test/results/local/declare.out.txt
b/hplsql/src/test/results/local/declare.out.txt
index 2b6a6d7d671..cb970ea048a 100644
--- a/hplsql/src/test/results/local/declare.out.txt
+++ b/hplsql/src/test/results/local/declare.out.txt
@@ -9,5 +9,5 @@ Ln:12 DECLARE status INT = 1
Ln:12 DECLARE status2 INT = 1
Ln:13 DECLARE count SMALLINT
Ln:13 DECLARE limit INT = 100
-Ln:15 DECLARE dt DATE = 2015-05-13
-Ln:16 DECLARE ts TIMESTAMP = 2015-05-13 11:10:01
+Ln:15 DECLARE dt DATE = DATE '2015-05-13'
+Ln:16 DECLARE ts TIMESTAMP = TIMESTAMP '2015-05-13 11:10:01'
diff --git a/hplsql/src/test/results/local/expr.out.txt
b/hplsql/src/test/results/local/expr.out.txt
index 377f8d14f45..1ae8e373bcd 100644
--- a/hplsql/src/test/results/local/expr.out.txt
+++ b/hplsql/src/test/results/local/expr.out.txt
@@ -23,7 +23,3 @@ Ln:16 SET c = 3
Ln:17 SET c = 2
Ln:18 PRINT
2
-Ln:20 PRINT
-Correct
-Ln:21 PRINT
-Correct
diff --git a/hplsql/src/test/results/local/instr.out.txt
b/hplsql/src/test/results/local/instr.out.txt
deleted file mode 100644
index 9a23e12ae6b..00000000000
--- a/hplsql/src/test/results/local/instr.out.txt
+++ /dev/null
@@ -1,33 +0,0 @@
-Ln:1 IF
-Ln:1 IF TRUE executed
-Ln:2 PRINT
-Correct
-Ln:7 IF
-Ln:7 IF TRUE executed
-Ln:8 PRINT
-Correct
-Ln:13 IF
-Ln:13 IF TRUE executed
-Ln:14 PRINT
-Correct
-Ln:19 IF
-Ln:19 IF TRUE executed
-Ln:20 PRINT
-Correct
-Ln:25 IF
-Ln:25 IF TRUE executed
-Ln:26 PRINT
-Correct
-Ln:31 DECLARE c STRING
-Ln:33 IF
-Ln:33 IF TRUE executed
-Ln:34 PRINT
-Correct
-Ln:39 IF
-Ln:39 IF TRUE executed
-Ln:40 PRINT
-Correct
-Ln:45 IF
-Ln:45 IF TRUE executed
-Ln:46 PRINT
-Correct
diff --git a/hplsql/src/test/results/local/interval.out.txt
b/hplsql/src/test/results/local/interval.out.txt
index d73d95a4961..3e14a4b4524 100644
--- a/hplsql/src/test/results/local/interval.out.txt
+++ b/hplsql/src/test/results/local/interval.out.txt
@@ -1,12 +1,12 @@
-2015-03-13
-2015-03-13 00:00:00
-2015-03-12 10:10:10.001
-2015-03-15
-2015-03-15 00:00:00
-2015-03-11
-2015-03-11 00:00:00
-2015-03-12 10:10:09.999
-2015-03-09
-2015-03-09 00:00:00
-2015-03-10 23:59:59
-2016-01-24
+DATE '2015-03-13'
+TIMESTAMP '2015-03-13 00:00:00'
+TIMESTAMP '2015-03-12 10:10:10.001'
+DATE '2015-03-15'
+TIMESTAMP '2015-03-15 00:00:00'
+DATE '2015-03-11'
+TIMESTAMP '2015-03-11 00:00:00'
+TIMESTAMP '2015-03-12 10:10:09.999'
+DATE '2015-03-09'
+TIMESTAMP '2015-03-09 00:00:00'
+TIMESTAMP '2015-03-10 23:59:59'
+DATE '2016-01-24'
diff --git a/hplsql/src/test/results/local/invalid_syntax.out.txt
b/hplsql/src/test/results/local/invalid_syntax.out.txt
index 56206511ecb..26eba40e305 100644
--- a/hplsql/src/test/results/local/invalid_syntax.out.txt
+++ b/hplsql/src/test/results/local/invalid_syntax.out.txt
@@ -1,3 +1,3 @@
Syntax error at line 1:27 no viable alternative at input 'b,'
-Syntax error at line 1:27 mismatched input ',' expecting {<EOF>, '@', '#',
'!', T_ACTION, T_ADD2, T_ALL, T_ALLOCATE, T_ALTER, T_AND, T_ANSI_NULLS,
T_ANSI_PADDING, T_AS, T_ASC, T_ASSOCIATE, T_AT, T_AUTO_INCREMENT, T_AVG,
T_BATCHSIZE, T_BEGIN, T_BETWEEN, T_BIGINT, T_BINARY_DOUBLE, T_BINARY_FLOAT,
T_BIT, T_BODY, T_BREAK, T_BULK, T_BY, T_BYTE, T_CALL, T_CALLER, T_CASCADE,
T_CASE, T_CASESPECIFIC, T_CAST, T_CHAR, T_CHARACTER, T_CHARSET, T_CLIENT,
T_CLOSE, T_CLUSTERED, T_CMP, T_COLLECT, T_COLLE [...]
+Syntax error at line 1:27 mismatched input ',' expecting {<EOF>, '@', '#',
'!', T_ACTION, T_ADD2, T_ALL, T_ALLOCATE, T_ALTER, T_AND, T_ANSI_NULLS,
T_ANSI_PADDING, T_AS, T_ASC, T_ASSOCIATE, T_AT, T_AUTO_INCREMENT, T_AVG,
T_BATCHSIZE, T_BEGIN, T_BETWEEN, T_BIGINT, T_BINARY_DOUBLE, T_BINARY_FLOAT,
T_BIT, T_BODY, T_BREAK, T_BULK, T_BY, T_BYTE, T_CALL, T_CALLER, T_CASCADE,
T_CASE, T_CASESPECIFIC, T_CAST, T_CHAR, T_CHARACTER, T_CHARSET, T_CLIENT,
T_CLOSE, T_CLUSTERED, T_CMP, T_COLLECT, T_COLLE [...]
Ln:1 identifier 'CREATE' must be declared.
diff --git a/hplsql/src/test/results/local/length.out.txt
b/hplsql/src/test/results/local/length.out.txt
deleted file mode 100644
index b8626c4cff2..00000000000
--- a/hplsql/src/test/results/local/length.out.txt
+++ /dev/null
@@ -1 +0,0 @@
-4
diff --git a/hplsql/src/test/results/local/lower.out.txt
b/hplsql/src/test/results/local/lower.out.txt
deleted file mode 100644
index 8baef1b4abc..00000000000
--- a/hplsql/src/test/results/local/lower.out.txt
+++ /dev/null
@@ -1 +0,0 @@
-abc
diff --git a/hplsql/src/test/results/local/nvl.out.txt
b/hplsql/src/test/results/local/nvl.out.txt
deleted file mode 100644
index a111c8575fb..00000000000
--- a/hplsql/src/test/results/local/nvl.out.txt
+++ /dev/null
@@ -1,4 +0,0 @@
-First non-null
-First non-null
-First non-null
-First non-null
diff --git a/hplsql/src/test/results/local/replace.out.txt
b/hplsql/src/test/results/local/replace.out.txt
deleted file mode 100644
index 2cd3602ff95..00000000000
--- a/hplsql/src/test/results/local/replace.out.txt
+++ /dev/null
@@ -1 +0,0 @@
-20160303
diff --git a/hplsql/src/test/results/local/sub.out.txt
b/hplsql/src/test/results/local/sub.out.txt
index d883fcd7d78..8d3b91922fb 100644
--- a/hplsql/src/test/results/local/sub.out.txt
+++ b/hplsql/src/test/results/local/sub.out.txt
@@ -1 +1 @@
-2014-12-31
+DATE '2014-12-31'
diff --git a/hplsql/src/test/results/local/timestamp.out.txt
b/hplsql/src/test/results/local/timestamp.out.txt
deleted file mode 100644
index 223b5f492cb..00000000000
--- a/hplsql/src/test/results/local/timestamp.out.txt
+++ /dev/null
@@ -1,4 +0,0 @@
-2015-03-03 11:39:31.123
-2015-03-03 11:39:31.123
-2015-03-03 11:39:31
-2015-03-03 11:39:31.123
diff --git a/hplsql/src/test/results/local/timestamp_iso.out.txt
b/hplsql/src/test/results/local/timestamp_iso.out.txt
index 997df7fffca..dc697a127d6 100644
--- a/hplsql/src/test/results/local/timestamp_iso.out.txt
+++ b/hplsql/src/test/results/local/timestamp_iso.out.txt
@@ -1,2 +1 @@
-2015-03-12 00:00:00
-2015-03-12 00:00:00
+TIMESTAMP '2015-03-12 00:00:00'
diff --git a/hplsql/src/test/results/local/to_char.out.txt
b/hplsql/src/test/results/local/to_char.out.txt
index 22e8ceff87b..651eb14fee4 100644
--- a/hplsql/src/test/results/local/to_char.out.txt
+++ b/hplsql/src/test/results/local/to_char.out.txt
@@ -1 +1 @@
-2015-04-02
+DATE '2015-04-02'
diff --git a/hplsql/src/test/results/local/to_timestamp.out.txt
b/hplsql/src/test/results/local/to_timestamp.out.txt
index 1ee727873aa..08884ae0cc5 100644
--- a/hplsql/src/test/results/local/to_timestamp.out.txt
+++ b/hplsql/src/test/results/local/to_timestamp.out.txt
@@ -1,4 +1,4 @@
-2015-04-02 00:00:00
-2015-04-02 00:00:00
-2015-04-02 00:00:00
-2015-04-02 13:51:31
+TIMESTAMP '2015-04-02 00:00:00'
+TIMESTAMP '2015-04-02 00:00:00'
+TIMESTAMP '2015-04-02 00:00:00'
+TIMESTAMP '2015-04-02 13:51:31'
diff --git a/hplsql/src/test/results/local/trim.out.txt
b/hplsql/src/test/results/local/trim.out.txt
deleted file mode 100644
index bbf851d14c6..00000000000
--- a/hplsql/src/test/results/local/trim.out.txt
+++ /dev/null
@@ -1 +0,0 @@
-#Hello#
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 a7a547adb8d..4118013769f 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
@@ -27,6 +27,8 @@ import static org.junit.Assert.fail;
import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
+import java.sql.Date;
+import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
@@ -610,12 +612,268 @@ public class TestHplSqlViaBeeLine {
testScriptFile(SCRIPT_TEXT, args(), "^(.(?!(NullPointerException)))*$",
OutStream.ERR);
}
+ @Test
+ public void testACTIVITY_COUNTHplSqlFunction() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (col1 string);\n" +
+ "INSERT INTO result VALUES('Alice');\n" +
+ "INSERT INTO result VALUES('Bob');\n" +
+ "SELECT * FROM result;\n" +
+ "SELECT ACTIVITY_COUNT;";
+ testScriptFile(SCRIPT_TEXT, args(), "2");
+ }
+
+ @Test
+ public void testCASTHplSqlFunction1() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CAST('Abc' AS CHAR(1));";
+ testScriptFile(SCRIPT_TEXT, args(), "A");
+ }
+
+ @Test
+ public void testCASTHplSqlFunction2() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CAST(TIMESTAMP '2015-03-12 10:58:34.111' AS
CHAR(10));";
+ testScriptFile(SCRIPT_TEXT, args(), "2015-03-12");
+ }
+
+ @Test
+ public void testCHARHplSqlFunction() throws Throwable {
+ String SCRIPT_TEXT = "select CHAR(2023)";
+ testScriptFile(SCRIPT_TEXT, args(), "2023");
+ }
+
+ @Test
+ public void testCOALESCEHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "select COALESCE(null,123,2023)";
+ testScriptFile(SCRIPT_TEXT, args(), "123");
+ }
+
+ @Test
+ public void testCONCATHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "select CONCAT('a', 'b', NULL, 'c')";
+ testScriptFile(SCRIPT_TEXT, args(), "abc");
+ }
+
+ @Test
+ public void testCURRENTHplSQLFunction1() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CURRENT DATE;";
+ testCurrentDate(SCRIPT_TEXT);
+ }
+
+ private void testCurrentDate(String SCRIPT_TEXT) throws Throwable {
+ Date today = new Date(System.currentTimeMillis());
+ testScriptFile(SCRIPT_TEXT, args(), today.toString());
+ }
+
+ @Test
+ public void testCURRENTHplSQLFunction2() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CURRENT TIMESTAMP;";
+ testCurrentTimestamp(SCRIPT_TEXT);
+ }
+
+ private void testCurrentTimestamp(String SCRIPT_TEXT) throws Throwable {
+ Timestamp today = new Timestamp(System.currentTimeMillis());
+ String timestamp = today.toString();
+ testScriptFile(SCRIPT_TEXT, args(), timestamp.substring(0,
timestamp.length() - 9));
+ }
+
+ @Test
+ public void testCURRENTHplSQLFunction3() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CURRENT USER;";
+ testScriptFile(SCRIPT_TEXT, args(), System.getProperty("user.name"));
+ }
+
+ @Test
+ public void testCURRENT_DATEHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CURRENT_DATE;";
+ testCurrentDate(SCRIPT_TEXT);
+ }
+
+ @Test
+ public void testCURRENT_TIME_MILLISHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CURRENT_TIME_MILLIS();";
+ testScriptFile(SCRIPT_TEXT, args(),
String.valueOf(System.currentTimeMillis() / 100000));
+ }
+
+ @Test
+ public void testCURRENT_TIMESTAMPHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CURRENT_TIMESTAMP;";
+ testCurrentTimestamp(SCRIPT_TEXT);
+ }
+
+ @Test
+ public void testCURRENT_USERHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CURRENT_USER;";
+ testScriptFile(SCRIPT_TEXT, args(), System.getProperty("user.name"));
+ }
+
+ @Test
+ public void testDATEHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT DATE('2015-03-12');";
+ testScriptFile(SCRIPT_TEXT, args(), "2015-03-12");
+ }
+
+ @Test
+ public void testDECODEHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "DECLARE var1 INT DEFAULT 3;\n" + "SELECT DECODE
(var1, 1, 'A', 2, 'B', 3, 'C');";
+ testScriptFile(SCRIPT_TEXT, args(), "C");
+ }
+
+ @Test
+ public void testFROM_UNIXTIMEHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT from_unixtime(1447141681, 'yyyy-MM-dd');";
+ testScriptFile(SCRIPT_TEXT, args(), "2015-11-");
+ }
+
+ @Test
+ public void testINSTRHplSQLFunction1() throws Throwable {
+ String SCRIPT_TEXT = "SELECT INSTR('abc', 'b');";
+ testScriptFile(SCRIPT_TEXT, args(), "2");
+ }
+
+ @Test
+ public void testINSTRHplSQLFunction2() throws Throwable {
+ String SCRIPT_TEXT = "SELECT INSTR('abcabcabc', 'b', 3, 2);";
+ testScriptFile(SCRIPT_TEXT, args(), "8");
+ }
+
+ @Test
+ public void testLOWERHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT LOWER('ABC');";
+ testScriptFile(SCRIPT_TEXT, args(), "abc");
+ }
+
+ @Test
+ public void testLENHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT LEN('Abc ');";
+ testScriptFile(SCRIPT_TEXT, args(), "3");
+ }
+
+ @Test
+ public void testLENGTHHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT LENGTH('Abc ');";
+ testScriptFile(SCRIPT_TEXT, args(), "4");
+ }
+
+ @Test
+ public void testMODHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT MOD(5,2);";
+ testScriptFile(SCRIPT_TEXT, args(), "1");
+ }
+
+ @Test
+ public void testNOWHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT NOW();";
+ testCurrentTimestamp(SCRIPT_TEXT);
+ }
+
+ @Test
+ public void testNVLHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT NVL(NULL, 100);";
+ testScriptFile(SCRIPT_TEXT, args(), "100");
+ }
+
+ @Test
+ public void testNVL2HplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT NVL2(NULL, 100, 200);";
+ testScriptFile(SCRIPT_TEXT, args(), "200");
+ }
+
+ @Test
+ public void testREPLACEHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT replace('2016-03-03', '-', '');";
+ testScriptFile(SCRIPT_TEXT, args(), "20160303");
+ }
+
+ @Test
+ public void testSUBSTRHplSQLFunction1() throws Throwable {
+ String SCRIPT_TEXT = "SELECT SUBSTR('Remark', 3);";
+ testScriptFile(SCRIPT_TEXT, args(), "mark");
+ }
+
+ @Test
+ public void testSUBSTRHplSQLFunction2() throws Throwable {
+ String SCRIPT_TEXT = "SELECT SUBSTR('Remark', 3, 3);";
+ testScriptFile(SCRIPT_TEXT, args(), "mar");
+ }
+
+ @Test
+ public void testSUBSTRINGHplSQLFunction1() throws Throwable {
+ String SCRIPT_TEXT = "SELECT SUBSTRING('Remark', 3);";
+ testScriptFile(SCRIPT_TEXT, args(), "mark");
+ }
+
+ @Test
+ public void testSUBSTRINGHplSQLFunction2() throws Throwable {
+ String SCRIPT_TEXT = "SELECT SUBSTRING('Remark', 3, 3);";
+ testScriptFile(SCRIPT_TEXT, args(), "mar");
+ }
+
+ @Test
+ public void testSYSDATEHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT SYSDATE;";
+ testCurrentTimestamp(SCRIPT_TEXT);
+ }
+
+ @Test
+ public void testTIMESTAMP_ISOHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT TIMESTAMP_ISO('2015-03-12');";
+ testScriptFile(SCRIPT_TEXT, args(), "2015-03-12 00:00:00");
+ }
+
+ @Test
+ public void testTO_CHARHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT TO_CHAR(CURRENT_DATE);";
+ testCurrentDate(SCRIPT_TEXT);
+ }
+
+ @Test
+ public void testTO_TIMESTAMPHplSQLFunction1() throws Throwable {
+ String SCRIPT_TEXT = "SELECT TO_TIMESTAMP('2015-04-02', 'YYYY-MM-DD');";
+ testScriptFile(SCRIPT_TEXT, args(), "2015-04-02 00:00:00.0");
+ }
+
+ @Test
+ public void testTO_TIMESTAMPHplSQLFunction2() throws Throwable {
+ String SCRIPT_TEXT = "SELECT TO_TIMESTAMP('04/02/2015', 'mm/dd/yyyy');";
+ testScriptFile(SCRIPT_TEXT, args(), "2015-04-02 00:00:00.0");
+ }
+
+ @Test
+ public void testTO_TIMESTAMPHplSQLFunction3() throws Throwable {
+ String SCRIPT_TEXT = "SELECT TO_TIMESTAMP('2015-04-02 13:51:31',
'YYYY-MM-DD HH24:MI:SS');";
+ testScriptFile(SCRIPT_TEXT, args(), "2015-04-02 13:51:31.0");
+ }
+
+ @Test
+ public void testTRIMHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT '#' || TRIM(' Hello ') || '#';";
+ testScriptFile(SCRIPT_TEXT, args(), "#Hello#");
+ }
+
+ @Test
+ public void testUNIX_TIMESTAMPHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT UNIX_TIMESTAMP()";
+ testScriptFile(SCRIPT_TEXT, args(),
String.valueOf(System.currentTimeMillis()/10000));
+ }
+
+ @Test
+ public void testUPPERHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT UPPER('abc');";
+ testScriptFile(SCRIPT_TEXT, args(), "ABC");
+ }
+
+ @Test
+ public void testUSERHplSQLFunction() throws Throwable {
+ String SCRIPT_TEXT = "SELECT USER;";
+ testScriptFile(SCRIPT_TEXT, args(), System.getProperty("user.name"));
+ }
+
private static List<String> args() {
return Arrays.asList("-d", BeeLine.BEELINE_DEFAULT_JDBC_DRIVER,
"-u", miniHS2.getBaseJdbcURL() + ";mode=hplsql", "-n", userName);
}
-
private void testScriptFile(String scriptText, List<String> argList, String
expectedPattern)
throws Throwable {
testScriptFile(scriptText, argList, expectedPattern, OutStream.OUT);
