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 76a03b8173e HIVE-28143: After HIVE-27492 fix, some HPLSQL built-in
functions like trim, lower are not working when used in insert statement.
(Dayakar M, reviewed by Krisztian Kasa)
76a03b8173e is described below
commit 76a03b8173e36681d8824bd30920cadff0bfb4fa
Author: Dayakar M <[email protected]>
AuthorDate: Thu Aug 1 17:14:44 2024 +0530
HIVE-28143: After HIVE-27492 fix, some HPLSQL built-in functions like trim,
lower are not working when used in insert statement. (Dayakar M, reviewed by
Krisztian Kasa)
Co-authored-by: mdayakar <[email protected]>
---
.../main/antlr4/org/apache/hive/hplsql/Hplsql.g4 | 2 +-
.../src/main/java/org/apache/hive/hplsql/Exec.java | 20 +-
.../java/org/apache/hive/hplsql/Expression.java | 13 +-
.../main/java/org/apache/hive/hplsql/Select.java | 12 +-
.../src/main/java/org/apache/hive/hplsql/Stmt.java | 21 +-
.../main/java/org/apache/hive/hplsql/Utils.java | 5 +-
.../src/main/java/org/apache/hive/hplsql/Var.java | 25 +-
.../hive/hplsql/functions/BuiltinFunctions.java | 4 +
.../hive/hplsql/functions/FunctionDatetime.java | 82 +++++-
.../apache/hive/hplsql/functions/FunctionMisc.java | 90 ++++++-
.../hive/hplsql/functions/FunctionString.java | 177 +++++++++++--
.../org/apache/hive/hplsql/TestHplsqlLocal.java | 50 ++++
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 | 4 +-
hplsql/src/test/queries/local/create_function3.sql | 26 +-
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/expr.out.txt | 4 +
hplsql/src/test/results/local/instr.out.txt | 33 +++
hplsql/src/test/results/local/interval.out.txt | 24 +-
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 +
.../test/results/offline/create_table_td.out.txt | 26 +-
hplsql/src/test/results/offline/select.out.txt | 27 +-
hplsql/src/test/results/offline/select_db2.out.txt | 2 +-
.../test/results/offline/select_teradata.out.txt | 8 +-
hplsql/src/test/results/offline/update.out.txt | 28 +-
.../apache/hive/beeline/TestHplSqlViaBeeLine.java | 287 ++++++++++++++++++++-
55 files changed, 974 insertions(+), 169 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 43a32984612..f2f97fbd34a 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_func
| expr_spec_func
+ | expr_func
| expr_atom
;
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 ccfe5c5ce09..a8d5187b773 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
@@ -1896,7 +1896,8 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
}
}
sql.append(")");
- exec.stackPush(sql);
+ Var var = new Var(Type.SQL_STRING, sql);
+ exec.stackPush(var);
}
/**
@@ -1931,7 +1932,8 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
sql.append(", \"");
sql.append(getStoredProcedure(name.toUpperCase()));
sql.append("\")");
- exec.stackPush(sql);
+ Var var = new Var(Type.HPL_SQL_UDF, sql);
+ exec.stackPush(var);
exec.registerUdf();
return true;
}
@@ -1957,7 +1959,8 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
*/
@Override
public Integer
visitExpr_agg_window_func(HplsqlParser.Expr_agg_window_funcContext ctx) {
- exec.stackPush(Exec.getFormattedText(ctx));
+ Var var = new Var(Type.SQL_STRING, (Object) Exec.getFormattedText(ctx));
+ exec.stackPush(var);
return 0;
}
@@ -2551,7 +2554,7 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
exec.stackPush(var);
}
} else {
- exec.stackPush(new Var(ident, Var.Type.STRING, var.toSqlString()));
+ exec.stackPush(new Var(ident, Type.VARIABLE, var.toSqlString()));
}
} else {
if (exec.buildSql || exec.inCallStmt) {
@@ -2570,13 +2573,8 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
* Single quoted string literal
*/
@Override
- public Integer
visitSingle_quotedString(HplsqlParser.Single_quotedStringContext ctx) {
- if (exec.buildSql) {
- exec.stackPush(ctx.getText());
- }
- else {
- exec.stackPush(Utils.unquoteString(ctx.getText()));
- }
+ public Integer
visitSingle_quotedString(HplsqlParser.Single_quotedStringContext ctx) {
+ exec.stackPush(Utils.unquoteString(ctx.getText()));
return 0;
}
diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java
b/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java
index 1534247002d..885cf9aa67f 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java
@@ -110,7 +110,13 @@ public class Expression {
}
else {
visitChildren(ctx);
- sql.append(exec.stackPop().toString());
+ Var value = exec.stackPop();
+ if (value.type == Type.NULL && sql.toString().length() == 0) {
+ exec.stackPush(new Var());
+ return;
+ } else {
+ sql.append(value.toSqlString());
+ }
}
exec.stackPush(sql);
}
@@ -565,13 +571,14 @@ public class Expression {
sql.append("CONCAT(");
int cnt = ctx.expr_concat_item().size();
for (int i = 0; i < cnt; i++) {
- sql.append(evalPop(ctx.expr_concat_item(i)).toString());
+ sql.append(evalPop(ctx.expr_concat_item(i)).toSqlString());
if (i + 1 < cnt) {
sql.append(", ");
}
}
sql.append(")");
- exec.stackPush(sql);
+ Var var = new Var(Type.SQL_STRING, sql);
+ exec.stackPush(var);
}
/**
diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Select.java
b/hplsql/src/main/java/org/apache/hive/hplsql/Select.java
index 9883b8d75fa..9a5cde2a57e 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Select.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Select.java
@@ -67,11 +67,15 @@ public class Select {
boolean oldBuildSql = exec.buildSql;
exec.buildSql = true;
StringBuilder sql = new StringBuilder();
- if (ctx.cte_select_stmt() != null) {
- sql.append(evalPop(ctx.cte_select_stmt()).toString());
- sql.append("\n");
+ if (exec.getOffline()) {
+ sql.append(exec.getText(ctx));
+ } else {
+ if (ctx.cte_select_stmt() != null) {
+ sql.append(evalPop(ctx.cte_select_stmt()).toString());
+ sql.append("\n");
+ }
+ sql.append(evalPop(ctx.fullselect_stmt()).toString());
}
- sql.append(evalPop(ctx.fullselect_stmt()).toString());
exec.buildSql = oldBuildSql;
if (!(ctx.parent instanceof HplsqlParser.StmtContext)) { // No
need to execute at this stage
exec.stackPush(sql);
diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java
b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java
index 78a25a0f266..ff70b2b9619 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java
@@ -787,8 +787,9 @@ public class Stmt {
for (int i = 0; i < rows; i++) {
HplsqlParser.Insert_stmt_rowContext row
=ctx.insert_stmt_rows().insert_stmt_row(i);
int cols = row.expr().size();
- for (int j = 0; j < cols; j++) {
- String value = evalPop(row.expr(j)).toSqlString();
+ for (int j = 0; j < cols; j++) {
+ Var var = evalPop(row.expr(j));
+ String value = var.toSqlString();
if (j == 0 && type == Conn.Type.HIVE && conf.insertValues ==
Conf.InsertValues.SELECT ) {
sql.append("SELECT ");
}
@@ -1170,13 +1171,17 @@ public class Stmt {
*/
public Integer update(HplsqlParser.Update_stmtContext ctx) {
trace(ctx, "UPDATE");
- boolean oldBuildSql = exec.buildSql;
String sql = null;
- try {
- exec.buildSql = true;
- sql = generateUpdateQuery(ctx);
- } finally {
- exec.buildSql = oldBuildSql;
+ if (exec.getOffline()) {
+ sql = exec.getFormattedText(ctx);
+ } else {
+ boolean oldBuildSql = exec.buildSql;
+ try {
+ exec.buildSql = true;
+ sql = generateUpdateQuery(ctx);
+ } finally {
+ exec.buildSql = oldBuildSql;
+ }
}
trace(ctx, sql);
QueryResult query = queryExecutor.executeQuery(sql, ctx);
diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java
b/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java
index c7d0f202f11..ea139b68311 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java
@@ -213,8 +213,9 @@ public class Utils {
int len = in.length();
int i = 0;
while (i < len) {
- if (i + 4 <= len && in.substring(i, i + 4).compareTo("YYYY") == 0) {
- out.append("yyyy");
+ if (i + 4 <= len && (in.substring(i, i + 4).compareTo("YYYY") == 0 ||
in.substring(i, i + 4)
+ .compareTo("yyyy") == 0)) {
+ out.append("uuuu");
i += 4;
}
else if (i + 2 <= len && in.substring(i, i + 2).compareTo("mm") == 0) {
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 1c7bb41824c..4a877d3b7f6 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Var.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Var.java
@@ -31,7 +31,7 @@ import org.apache.hive.hplsql.executor.QueryResult;
public class Var {
// Data types
public enum Type {BOOL, CURSOR, DATE, DECIMAL, DERIVED_TYPE,
DERIVED_ROWTYPE, DOUBLE, FILE, IDENT, BIGINT, INTERVAL, ROW,
- RS_LOCATOR, STRING, STRINGLIST, TIMESTAMP, NULL,
HPL_OBJECT}
+ RS_LOCATOR, STRING, STRINGLIST, TIMESTAMP, NULL,
HPL_OBJECT, HPL_SQL_UDF, SQL_STRING, VARIABLE}
public static final String DERIVED_TYPE = "DERIVED%TYPE";
public static final String DERIVED_ROWTYPE = "DERIVED%ROWTYPE";
public static Var Empty = new Var();
@@ -181,6 +181,7 @@ public class Var {
} else if (type == Type.DECIMAL) {
if (val.type == Type.STRING) {
value = new BigDecimal((String) val.value);
+ value = ((BigDecimal)value).setScale(scale);
} else if (val.type == Type.BIGINT) {
value = BigDecimal.valueOf(val.longValue());
} else if (val.type == Type.DOUBLE) {
@@ -601,7 +602,7 @@ public class Var {
return (String)value;
}
else if (type == Type.DATE) {
- return String.format("DATE '%s'", value);
+ return ((Date)value).toString();
}
else if (type == Type.TIMESTAMP) {
int len = 19;
@@ -612,7 +613,7 @@ public class Var {
if (t.length() > len) {
t = t.substring(0, len);
}
- return String.format("TIMESTAMP '%s'", t);
+ return t;
}
return value.toString();
}
@@ -621,12 +622,28 @@ public class Var {
* Convert value to SQL string - string literals are quoted and escaped,
ab'c -> 'ab''c'
*/
public String toSqlString() {
- if (value == null) {
+ if (type == Type.IDENT) {
+ return name;
+ }
+ else if (value == null) {
return "NULL";
}
else if (type == Type.STRING) {
return Utils.quoteString((String)value);
}
+ else if (type == Type.TIMESTAMP) {
+ int len = 19;
+ String t = ((Timestamp) value).toString(); // .0 returned if the
fractional part not set
+ if (scale > 0) {
+ len += scale + 1;
+ }
+ if (t.length() > len) {
+ t = t.substring(0, len);
+ }
+ return String.format("TIMESTAMP '%s'", t);
+ } else if (type == Type.DATE) {
+ return String.format("DATE '%s'", ((Date) value).toString());
+ }
return 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 100fa19c534..d6c4b90c814 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
@@ -377,6 +377,10 @@ public class BuiltinFunctions {
exec.stackPush(new Var(string));
}
+ protected void evalSqlString(String string) {
+ exec.stackPush(new Var(Var.Type.SQL_STRING, (Object) string));
+ }
+
protected Var evalPop(ParserRuleContext ctx) {
exec.visit(ctx);
return exec.stackPop();
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 e7e562aca67..fb7d22aaa73 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
@@ -19,8 +19,10 @@
package org.apache.hive.hplsql.functions;
import java.sql.Timestamp;
-import java.text.SimpleDateFormat;
-import java.util.Calendar;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.format.DateTimeFormatter;
+import java.time.format.ResolverStyle;
import java.util.TimeZone;
import org.apache.commons.lang3.StringUtils;
@@ -38,24 +40,40 @@ 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);
f.specSqlMap.put("CURRENT_TIMESTAMP", (FuncSpecCommand)
this::currentTimestampSql);
}
+
+ private static DateTimeFormatter createDateTimeFormatter(String format) {
+ return
DateTimeFormatter.ofPattern(format).withZone(TimeZone.getTimeZone("UTC").toZoneId())
+ .withResolverStyle(ResolverStyle.STRICT);
+ }
+
+ /**
+ * CURRENT_DATE
+ */
+ public void currentDate(HplsqlParser.Expr_spec_funcContext ctx) {
+ evalVar(currentDate());
+ }
/**
* CURRENT_DATE
*/
public static Var currentDate() {
- SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
- String s = f.format(Calendar.getInstance().getTime());
- return new Var(Var.Type.DATE, Utils.toDate(s));
+ DateTimeFormatter formatter = createDateTimeFormatter("yyyy-MM-dd");
+ String date = formatter.format(new
java.sql.Date(System.currentTimeMillis()).toLocalDate());
+ return new Var(Var.Type.DATE, Utils.toDate(date));
}
/**
@@ -63,10 +81,10 @@ public class FunctionDatetime extends BuiltinFunctions {
*/
public void currentDateSql(HplsqlParser.Expr_spec_funcContext ctx) {
if (exec.getConnectionType() == Conn.Type.HIVE) {
- evalString("TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))");
+ evalSqlString("TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))");
}
else {
- evalString(exec.getFormattedText(ctx));
+ evalSqlString(exec.getFormattedText(ctx));
}
}
@@ -83,9 +101,9 @@ public class FunctionDatetime extends BuiltinFunctions {
if (precision > 0 && precision <= 3) {
format += "." + StringUtils.repeat("S", precision);
}
- SimpleDateFormat f = new SimpleDateFormat(format);
- String s = f.format(Calendar.getInstance(TimeZone.getDefault()).getTime());
- return new Var(Utils.toTimestamp(s), precision);
+ DateTimeFormatter formatter = createDateTimeFormatter(format);
+ String timestamp = formatter.format(new
java.sql.Timestamp(System.currentTimeMillis()).toLocalDateTime());
+ return new Var(Utils.toTimestamp(timestamp), precision);
}
/**
@@ -93,10 +111,10 @@ public class FunctionDatetime extends BuiltinFunctions {
*/
public void currentTimestampSql(HplsqlParser.Expr_spec_funcContext ctx) {
if (exec.getConnectionType() == Conn.Type.HIVE) {
- evalString("FROM_UNIXTIME(UNIX_TIMESTAMP())");
+ evalSqlString("FROM_UNIXTIME(UNIX_TIMESTAMP())");
}
else {
- evalString(Exec.getFormattedText(ctx));
+ evalSqlString(Exec.getFormattedText(ctx));
}
}
@@ -153,8 +171,14 @@ public class FunctionDatetime extends BuiltinFunctions {
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();
- evalVar(new Var(Var.Type.TIMESTAMP, new Timestamp(timeInMs)));
+ DateTimeFormatter formatter = createDateTimeFormatter(format);
+ LocalDateTime val = null;
+ if (format.length() > 10) {
+ val = LocalDateTime.parse(value, formatter);
+ } else {
+ val = LocalDate.parse(value, formatter).atStartOfDay();
+ }
+ evalVar(new Var(Var.Type.TIMESTAMP, Timestamp.valueOf(val)));
}
catch (Exception e) {
exec.signal(e);
@@ -162,6 +186,36 @@ public class FunctionDatetime extends BuiltinFunctions {
}
}
+ /**
+ * 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;
+ }
+ Var value = evalPop(ctx.func_param(0).expr());
+ if (value.type != Var.Type.BIGINT) {
+ Var newVar = new Var(Var.Type.BIGINT);
+ value = newVar.cast(value);
+ }
+ long epoch = value.longValue();
+ String format = "yyyy-MM-dd HH:mm:ss";
+ if (cnt > 1) {
+ format =
Utils.unquoteString(evalPop(ctx.func_param(1).expr()).toString());
+ }
+ DateTimeFormatter formatter = createDateTimeFormatter(format);
+ evalString(formatter.format(new java.sql.Date(epoch *
1000).toLocalDate()));
+ }
+
+ /**
+ * 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 18c2ded2e03..75e3a7b0b58 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
@@ -25,6 +25,7 @@ import java.util.Map;
import org.apache.hive.hplsql.Conn;
import org.apache.hive.hplsql.Exec;
import org.apache.hive.hplsql.HplsqlParser;
+import org.apache.hive.hplsql.Utils;
import org.apache.hive.hplsql.Var;
import org.apache.hive.hplsql.executor.QueryException;
import org.apache.hive.hplsql.executor.QueryExecutor;
@@ -40,11 +41,15 @@ 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);
@@ -59,6 +64,46 @@ public class FunctionMisc extends BuiltinFunctions {
void activityCount(HplsqlParser.Expr_spec_funcContext ctx) {
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 value = evalPop(ctx.expr(0));
+ if (value.type == Var.Type.STRING) {
+ Var newValue = new Var(value.name, value.type,
Utils.unquoteString(value.toString()));
+ value = newValue;
+ }
+ if (value.type != Var.Type.NULL) {
+ String valueUpper = value.toString().toUpperCase();
+ if (valueUpper.startsWith("TIMESTAMP ") || valueUpper.startsWith("DATE
")) {
+ boolean old = exec.buildSql;
+ exec.buildSql = false;
+ value = evalPop(ctx.expr(0));
+ exec.buildSql = old;
+ }
+ }
+ var.cast(value);
+ if (exec.buildSql && var.type == Var.Type.STRING) {
+ evalString(var.toSqlString());
+ } else {
+ evalVar(var);
+ }
+ }
/**
* CURRENT <VALUE> function
@@ -72,7 +117,7 @@ public class FunctionMisc extends BuiltinFunctions {
evalVar(FunctionDatetime.currentTimestamp(precision));
}
else if (ctx.T_USER() != null) {
- evalVar(FunctionMisc.currentUser());
+ evalVar(currentUser());
}
else {
evalNull();
@@ -85,23 +130,23 @@ public class FunctionMisc extends BuiltinFunctions {
void currentSql(HplsqlParser.Expr_spec_funcContext ctx) {
if (ctx.T_DATE() != null) {
if (exec.getConnectionType() == Conn.Type.HIVE) {
- evalString("TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))");
+ evalSqlString("TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))");
}
else {
- evalString("CURRENT_DATE");
+ evalSqlString("CURRENT_DATE");
}
}
else if (ctx.T_TIMESTAMP() != null) {
if (exec.getConnectionType() == Conn.Type.HIVE) {
- evalString("FROM_UNIXTIME(UNIX_TIMESTAMP())");
+ evalSqlString("FROM_UNIXTIME(UNIX_TIMESTAMP())");
}
else {
- evalString("CURRENT_TIMESTAMP");
+ evalSqlString("CURRENT_TIMESTAMP");
}
} else if (ctx.T_USER() != null) {
- evalString("CURRENT_USER()");
+ evalSqlString("CURRENT_USER()");
} else {
- evalString(exec.getFormattedText(ctx));
+ evalSqlString(exec.getFormattedText(ctx));
}
}
@@ -112,8 +157,9 @@ public class FunctionMisc extends BuiltinFunctions {
evalVar(currentUser());
}
- public static Var currentUser() {
- return new Var("CURRENT_USER()");
+ public Var currentUser() {
+ String currentUser = System.getProperty("user.name");
+ return new Var(currentUser);
}
/**
@@ -142,6 +188,20 @@ public class FunctionMisc extends BuiltinFunctions {
evalNull();
}
}
+
+ /**
+ * 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
@@ -149,7 +209,7 @@ public class FunctionMisc extends BuiltinFunctions {
void nvl2(HplsqlParser.Expr_func_paramsContext ctx) {
if (ctx.func_param().size() == 3) {
Var firstParam = evalPop(ctx.func_param(0).expr());
- if (!(firstParam.isNull() ||
"null".equalsIgnoreCase((String)firstParam.value))) {
+ if (!firstParam.isNull()) {
eval(ctx.func_param(1).expr());
}
else {
@@ -211,6 +271,16 @@ 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 0020189a797..70b841d86e9 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,12 +33,18 @@ 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);
}
/**
@@ -46,13 +52,14 @@ 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() && !"null".equalsIgnoreCase((String)c.value)) {
- val.append(Utils.unquoteString(c.toString()));
+ if (!c.isNull()) {
+ String value = c.toString();
+ value = unquoteString(value);
+ val.append(value);
nulls = false;
}
}
@@ -60,11 +67,17 @@ public class FunctionString extends BuiltinFunctions {
evalNull();
}
else {
- appendSingleQuote(val);
evalString(val);
}
}
-
+
+ private String unquoteString(String value) {
+ if (exec.buildSql) {
+ value = Utils.unquoteString(value);
+ }
+ return value;
+ }
+
/**
* CHAR function
*/
@@ -77,6 +90,69 @@ public class FunctionString extends BuiltinFunctions {
String str = evalPop(ctx.func_param(0).expr()).toString();
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();
+ str = unquoteString(str);
+ if (str == null) {
+ evalNull();
+ return;
+ }
+ else if(str.isEmpty()) {
+ evalInt(0);
+ return;
+ }
+ String substr = evalPop(ctx.func_param(1).expr()).toString();
+ substr = unquoteString(substr);
+ 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)
@@ -86,10 +162,56 @@ public class FunctionString extends BuiltinFunctions {
evalNull();
return;
}
- int len =
Utils.unquoteString(evalPop(ctx.func_param(0).expr()).toString()).trim().length();
+ String value = evalPop(ctx.func_param(0).expr()).toString();
+ value = unquoteString(value);
+ int len = value.trim().length();
evalInt(len);
}
+ /**
+ * LENGTH function
+ */
+ void length(HplsqlParser.Expr_func_paramsContext ctx) {
+ if (ctx.func_param().size() != 1) {
+ evalNull();
+ return;
+ }
+ String value = evalPop(ctx.func_param(0).expr()).toString();
+ value = unquoteString(value);
+ int len = value.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();
+ str = unquoteString(str);
+ 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();
+ what = unquoteString(what);
+ String with = evalPop(ctx.func_param(2).expr()).toString();
+ with = unquoteString(with);
+ evalString(str.replaceAll(what, with));
+ }
+
/**
* SUBSTR and SUBSTRING function
*/
@@ -99,7 +221,8 @@ public class FunctionString extends BuiltinFunctions {
evalNull();
return;
}
- String str =
Utils.unquoteString(evalPop(ctx.func_param(0).expr()).toString());
+ String str = evalPop(ctx.func_param(0).expr()).toString();
+ str = unquoteString(str);
int start = evalPop(ctx.func_param(1).expr()).intValue();
int len = -1;
if (start == 0) {
@@ -123,28 +246,13 @@ public class FunctionString extends BuiltinFunctions {
if (start == 0) {
start = 1;
}
- StringBuilder resultStr = new StringBuilder();
if (len == -1) {
if (start > 0) {
- String substring = str.substring(start - 1);
- appendSingleQuote(resultStr);
- resultStr.append(substring);
- appendSingleQuote(resultStr);
- evalString(resultStr);
+ evalString(str.substring(start - 1));
}
}
else {
- 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("'");
+ evalString(str.substring(start - 1, start - 1 + len));
}
}
@@ -163,6 +271,20 @@ 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();
+ str = unquoteString(str);
+ evalString(str.trim());
+ }
/**
* TO_CHAR function
@@ -173,8 +295,8 @@ public class FunctionString extends BuiltinFunctions {
evalNull();
return;
}
- String str = evalPop(ctx.func_param(0).expr()).toString();
- evalString(str);
+ String str = evalPop(ctx.func_param(0).expr()).toString();
+ evalSqlString(str);
}
/**
@@ -185,7 +307,8 @@ public class FunctionString extends BuiltinFunctions {
evalNull();
return;
}
- String str = evalPop(ctx.func_param(0).expr()).toString().toUpperCase();
+ String str = evalPop(ctx.func_param(0).expr()).toString().toUpperCase();
+ str = unquoteString(str);
evalString(str);
}
}
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 0af1f83be42..75a606b1683 100644
--- a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java
+++ b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java
@@ -64,11 +64,26 @@ 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");
@@ -259,6 +274,11 @@ public class TestHplsqlLocal {
run("include");
}
+ @Test
+ public void testInstr() throws Exception {
+ run("instr");
+ }
+
@Test
public void testInterval() throws Exception {
run("interval");
@@ -274,16 +294,31 @@ 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");
@@ -293,6 +328,11 @@ public class TestHplsqlLocal {
public void testPrint() throws Exception {
run("print");
}
+
+ @Test
+ public void testReplace() throws Exception {
+ run("replace");
+ }
@Test
public void testReturn() throws Exception {
@@ -324,6 +364,11 @@ public class TestHplsqlLocal {
run("timestamp_iso");
}
+ @Test
+ public void testTimestamp() throws Exception {
+ run("timestamp");
+ }
+
@Test
public void testToChar() throws Exception {
run("to_char");
@@ -334,6 +379,11 @@ 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
new file mode 100644
index 00000000000..be6f407e740
--- /dev/null
+++ b/hplsql/src/test/queries/local/cast.sql
@@ -0,0 +1,4 @@
+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))
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/cast2.sql
b/hplsql/src/test/queries/local/cast2.sql
new file mode 100644
index 00000000000..d68db6d1b75
--- /dev/null
+++ b/hplsql/src/test/queries/local/cast2.sql
@@ -0,0 +1,10 @@
+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
new file mode 100644
index 00000000000..4b65d58b56f
--- /dev/null
+++ b/hplsql/src/test/queries/local/coalesce.sql
@@ -0,0 +1,4 @@
+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 37b7619ccb5..1dd091501d8 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');
-NVL2(CONCAT(NULL, NULL, NULL), NULL, 'NULL Value');
\ No newline at end of file
+CONCAT('a', 'b', NULL, 'c');
+NVL(CONCAT(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 ec1de72fceb..16bf5262634 100644
--- a/hplsql/src/test/queries/local/create_function3.sql
+++ b/hplsql/src/test/queries/local/create_function3.sql
@@ -6,53 +6,53 @@ FUNCTION gettype(tag1 varchar2, srcvalue varchar2) return
varchar2 as
return '@I';
end if;
- if (tag1) = 'WHMM' then
+ if trim(tag1) = 'WHMM' then
return '002';
end if;
- if (tag1) = 'TCPJ' and srcvalue = '010105' then
+ if trim(tag1) = 'TCPJ' and srcvalue = '010105' then
return '010105';
end if;
- if (tag1) = 'TCPJ' and srcvalue != '010105' then
+ if trim(tag1) = 'TCPJ' and srcvalue != '010105' then
return '003';
end if;
- if (tag1) = 'TCPJ' and srcvalue != '010105' then
+ if trim(tag1) = 'TCPJ' and srcvalue != '010105' then
return '003_ticket';
end if;
- if (tag1) = 'TCJY' and srcvalue != '010105' then
+ if trim(tag1) = 'TCJY' and srcvalue != '010105' then
return '003_ticket';
end if;
- if (tag1) = 'TCJY' and srcvalue != '010105' then
+ if trim(tag1) = 'TCJY' and srcvalue != '010105' then
return '003_ticket';
end if;
- if (tag1) = 'YHHPD' then
+ if trim(tag1) = 'YHHPD' then
return '002_foreign';
end if;
- if (tag1) = 'WHWZ' then
+ if trim(tag1) = 'WHWZ' then
return '002_foreign';
end if;
- if (tag1) = 'WHLZ' then
+ if trim(tag1) = 'WHLZ' then
return '002_foreign';
end if;
- if (tag1) = 'DEWZ' then
+ if trim(tag1) = 'DEWZ' then
return '024_out';
end if;
- if (tag1) = 'DELZ' then
+ if trim(tag1) = 'DELZ' then
return '024_out';
end if;
return srcvalue;
END;
-
+
gettype('YHHPD', 'a');
- gettype('YHHPD', '@I');
+ gettype('YHHPD', '@I');
\ No newline at end of file
diff --git a/hplsql/src/test/queries/local/create_function4.sql
b/hplsql/src/test/queries/local/create_function4.sql
index bc373343649..21986e08171 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 (TMPVAR) = '' THEN
+ IF TRIM(TMPVAR) = '' THEN
RETURN '00080000';
ELSE
- RETURN (TMPVAR);
+ RETURN TRIM(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 981075ce33d..2ef474329af 100644
--- a/hplsql/src/test/queries/local/date.sql
+++ b/hplsql/src/test/queries/local/date.sql
@@ -1,4 +1,5 @@
DATE '2014-12-20'
DATE('2015-03-12');
-DATE('2015' || '-03-' || '12');
\ No newline at end of file
+DATE('2015' || '-03-' || '12');
+DATE(TIMESTAMP '2015-03-12 10:58:34.111');
\ 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 e65dff98b54..705ab5f3166 100644
--- a/hplsql/src/test/queries/local/expr.sql
+++ b/hplsql/src/test/queries/local/expr.sql
@@ -15,4 +15,7 @@ PRINT c;
PRINT 'Integer decrement';
c := 3;
c := c - 1;
-PRINT c;
\ No newline at end of file
+PRINT c;
+
+PRINT NVL(null - 3, 'Correct');
+PRINT NVL(null + 3, 'Correct');
\ 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 05884793971..b645b86e72f 100644
--- a/hplsql/src/test/queries/local/if2.sql
+++ b/hplsql/src/test/queries/local/if2.sql
@@ -1,4 +1,4 @@
-if not (nvl2(1,0) between 3 and 5) then
+if not (coalesce(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
new file mode 100644
index 00000000000..d2eb5005cdc
--- /dev/null
+++ b/hplsql/src/test/queries/local/instr.sql
@@ -0,0 +1,49 @@
+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 98cf4927c7e..e40fcbd229c 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' + NVL2(NULL, NULL, 3) DAYS;
-TIMESTAMP '2015-03-12' + NVL2(NULL, NULL, 3) DAYS;
+DATE '2015-03-12' + NVL(NULL, 3) DAYS;
+TIMESTAMP '2015-03-12' + NVL(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' - NVL2(NULL, NULL, 3) DAYS;
-TIMESTAMP '2015-03-12' - NVL2(NULL, NULL, 3) DAYS;
+DATE '2015-03-12' - NVL(NULL, 3) DAYS;
+TIMESTAMP '2015-03-12' - NVL(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
new file mode 100644
index 00000000000..42cf3ccf0a8
--- /dev/null
+++ b/hplsql/src/test/queries/local/length.sql
@@ -0,0 +1 @@
+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
new file mode 100644
index 00000000000..f29b0e970ea
--- /dev/null
+++ b/hplsql/src/test/queries/local/lower.sql
@@ -0,0 +1 @@
+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 23fe88f91d2..ebad8f46a15 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 / 4 / 2;
-set b = 4 * 2 / 4 / 2;
\ No newline at end of file
+set a = 4 * 2 / cast(4 as int) /2;
+set b = 4 * 2 /cast(4 as int)/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
new file mode 100644
index 00000000000..1a843bcb28a
--- /dev/null
+++ b/hplsql/src/test/queries/local/nvl.sql
@@ -0,0 +1,4 @@
+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
new file mode 100644
index 00000000000..820aa067870
--- /dev/null
+++ b/hplsql/src/test/queries/local/replace.sql
@@ -0,0 +1 @@
+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
new file mode 100644
index 00000000000..2971ceac888
--- /dev/null
+++ b/hplsql/src/test/queries/local/timestamp.sql
@@ -0,0 +1,4 @@
+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 e6601801fe1..9bcdfe08904 100644
--- a/hplsql/src/test/queries/local/timestamp_iso.sql
+++ b/hplsql/src/test/queries/local/timestamp_iso.sql
@@ -1 +1,2 @@
-TIMESTAMP_ISO('2015-03-12');
\ No newline at end of file
+TIMESTAMP_ISO('2015-03-12');
+TIMESTAMP_ISO(DATE '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
new file mode 100644
index 00000000000..f8a2978d650
--- /dev/null
+++ b/hplsql/src/test/queries/local/trim.sql
@@ -0,0 +1 @@
+'#' || 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 7cfd6d68a01..37a195bfe18 100644
--- a/hplsql/src/test/results/local/add.out.txt
+++ b/hplsql/src/test/results/local/add.out.txt
@@ -1,2 +1,2 @@
-DATE '2015-01-01'
-DATE '2015-01-01'
+2015-01-01
+2015-01-01
diff --git a/hplsql/src/test/results/local/cast.out.txt
b/hplsql/src/test/results/local/cast.out.txt
new file mode 100644
index 00000000000..f3de493e67c
--- /dev/null
+++ b/hplsql/src/test/results/local/cast.out.txt
@@ -0,0 +1,8 @@
+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
new file mode 100644
index 00000000000..cd8e92b26a2
--- /dev/null
+++ b/hplsql/src/test/results/local/cast2.out.txt
@@ -0,0 +1,15 @@
+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.2000
+Ln:8 PRINT
+1.2000
+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
new file mode 100644
index 00000000000..a111c8575fb
--- /dev/null
+++ b/hplsql/src/test/results/local/coalesce.out.txt
@@ -0,0 +1,4 @@
+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 1e4435a3a24..118bd29a8c2 100644
--- a/hplsql/src/test/results/local/date.out.txt
+++ b/hplsql/src/test/results/local/date.out.txt
@@ -1,3 +1,4 @@
-DATE '2014-12-20'
-DATE '2015-03-12'
-DATE '2015-03-12'
+2014-12-20
+2015-03-12
+2015-03-12
+2015-03-12
diff --git a/hplsql/src/test/results/local/expr.out.txt
b/hplsql/src/test/results/local/expr.out.txt
index 1ae8e373bcd..377f8d14f45 100644
--- a/hplsql/src/test/results/local/expr.out.txt
+++ b/hplsql/src/test/results/local/expr.out.txt
@@ -23,3 +23,7 @@ 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
new file mode 100644
index 00000000000..9a23e12ae6b
--- /dev/null
+++ b/hplsql/src/test/results/local/instr.out.txt
@@ -0,0 +1,33 @@
+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 3e14a4b4524..d73d95a4961 100644
--- a/hplsql/src/test/results/local/interval.out.txt
+++ b/hplsql/src/test/results/local/interval.out.txt
@@ -1,12 +1,12 @@
-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'
+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
diff --git a/hplsql/src/test/results/local/length.out.txt
b/hplsql/src/test/results/local/length.out.txt
new file mode 100644
index 00000000000..b8626c4cff2
--- /dev/null
+++ b/hplsql/src/test/results/local/length.out.txt
@@ -0,0 +1 @@
+4
diff --git a/hplsql/src/test/results/local/lower.out.txt
b/hplsql/src/test/results/local/lower.out.txt
new file mode 100644
index 00000000000..8baef1b4abc
--- /dev/null
+++ b/hplsql/src/test/results/local/lower.out.txt
@@ -0,0 +1 @@
+abc
diff --git a/hplsql/src/test/results/local/nvl.out.txt
b/hplsql/src/test/results/local/nvl.out.txt
new file mode 100644
index 00000000000..a111c8575fb
--- /dev/null
+++ b/hplsql/src/test/results/local/nvl.out.txt
@@ -0,0 +1,4 @@
+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
new file mode 100644
index 00000000000..2cd3602ff95
--- /dev/null
+++ b/hplsql/src/test/results/local/replace.out.txt
@@ -0,0 +1 @@
+20160303
diff --git a/hplsql/src/test/results/local/sub.out.txt
b/hplsql/src/test/results/local/sub.out.txt
index 8d3b91922fb..d883fcd7d78 100644
--- a/hplsql/src/test/results/local/sub.out.txt
+++ b/hplsql/src/test/results/local/sub.out.txt
@@ -1 +1 @@
-DATE '2014-12-31'
+2014-12-31
diff --git a/hplsql/src/test/results/local/timestamp.out.txt
b/hplsql/src/test/results/local/timestamp.out.txt
new file mode 100644
index 00000000000..223b5f492cb
--- /dev/null
+++ b/hplsql/src/test/results/local/timestamp.out.txt
@@ -0,0 +1,4 @@
+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 dc697a127d6..997df7fffca 100644
--- a/hplsql/src/test/results/local/timestamp_iso.out.txt
+++ b/hplsql/src/test/results/local/timestamp_iso.out.txt
@@ -1 +1,2 @@
-TIMESTAMP '2015-03-12 00:00:00'
+2015-03-12 00:00:00
+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 651eb14fee4..22e8ceff87b 100644
--- a/hplsql/src/test/results/local/to_char.out.txt
+++ b/hplsql/src/test/results/local/to_char.out.txt
@@ -1 +1 @@
-DATE '2015-04-02'
+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 08884ae0cc5..1ee727873aa 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 @@
-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'
+2015-04-02 00:00:00
+2015-04-02 00:00:00
+2015-04-02 00:00:00
+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
new file mode 100644
index 00000000000..bbf851d14c6
--- /dev/null
+++ b/hplsql/src/test/results/local/trim.out.txt
@@ -0,0 +1 @@
+#Hello#
diff --git a/hplsql/src/test/results/offline/create_table_td.out.txt
b/hplsql/src/test/results/offline/create_table_td.out.txt
index 9b9d5617273..514c20e907b 100644
--- a/hplsql/src/test/results/offline/create_table_td.out.txt
+++ b/hplsql/src/test/results/offline/create_table_td.out.txt
@@ -12,9 +12,16 @@ Ln:10 CREATE TABLE ctl
AS
(
SELECT
- EBC.SOURCE_ID, MAX(EBC.RUN_ID) AS RUN_ID, EBC.STATUS,
EBC.LOAD_START, EBC.LOAD_END
- FROM EBC
- WHERE EBC.SOURCE_ID = 451 AND EBC.STATUS = 'R'
+ EBC.SOURCE_ID,
+ MAX(EBC.RUN_ID) AS RUN_ID,
+ EBC.STATUS,
+ EBC.LOAD_START,
+ EBC.LOAD_END
+ FROM
+ EBC
+ WHERE
+ EBC.SOURCE_ID = 451 AND
+ EBC.STATUS = 'R'
GROUP BY
1,3,4,5
)
@@ -23,9 +30,16 @@ CREATE TEMPORARY TABLE ctl2
AS
(
SELECT
- EBC.SOURCE_ID, MAX(EBC.RUN_ID) AS RUN_ID, EBC.STATUS,
EBC.LOAD_START, EBC.LOAD_END
- FROM EBC
- WHERE EBC.SOURCE_ID = 451 AND EBC.STATUS = 'R'
+ EBC.SOURCE_ID,
+ MAX(EBC.RUN_ID) AS RUN_ID,
+ EBC.STATUS,
+ EBC.LOAD_START,
+ EBC.LOAD_END
+ FROM
+ EBC
+ WHERE
+ EBC.SOURCE_ID = 451 AND
+ EBC.STATUS = 'R'
GROUP BY
1,3,4,5
)
\ No newline at end of file
diff --git a/hplsql/src/test/results/offline/select.out.txt
b/hplsql/src/test/results/offline/select.out.txt
index 849a1ead5f2..4b0aa0fbfd0 100644
--- a/hplsql/src/test/results/offline/select.out.txt
+++ b/hplsql/src/test/results/offline/select.out.txt
@@ -1,5 +1,5 @@
Ln:1 SELECT
-Ln:1 SELECT * FROM a where 1 = 1 and not exists (select * from b)
+Ln:1 SELECT * FROM a where 1=1 and not exists (select * from b)
Ln:1 Not executed - offline mode set
Ln:3 SELECT
Ln:3 SELECT *
@@ -12,24 +12,37 @@ Ln:3 Not executed - offline mode set
Ln:10 SELECT
Ln:10 SELECT
*
- FROM tab
+ FROM
+ tab
WHERE FILE_DATE > (
SELECT
MAX(FILE_DATE) AS MX_C_FILE_DT
FROM tab
- WHERE FLAG = 'C' AND IND = 'C' AND FILE_DATE < (
SELECT
+ WHERE FLAG = 'C'
+ AND IND = 'C'
+ AND FILE_DATE <
+ ( SELECT
CAST( LOAD_START AS DATE)
- FROM tab
- WHERE SOURCE_ID = 451 AND BATCH =
'R'
+ FROM
+ tab
+ WHERE
+ SOURCE_ID = 451
AND
+ BATCH = 'R'
)
)
Ln:10 Not executed - offline mode set
Ln:31 SELECT
Ln:31 SELECT
*
-FROM DLTA_POC LEFT OUTER JOIN TEST3_DB.TET ORG ON DLTA_POC.YS_NO =
ORG.EM_CODE_A
+FROM
+ DLTA_POC
+ LEFT OUTER JOIN TEST3_DB.TET ORG
+ ON DLTA_POC.YS_NO = ORG.EM_CODE_A
AND DLTA_POC.AREA_NO = ORG.AREA_CODE_2
- AND DLTA_POC.GNT_POC = ORG.GEN_CD LEFT OUTER JOIN TEST.LOCATION LOC ON
DLTA_POC.SE_KEY_POC = LOC.LOC_ID
+ AND DLTA_POC.GNT_POC = ORG.GEN_CD
+
+ LEFT OUTER JOIN TEST.LOCATION LOC
+ ON DLTA_POC.SE_KEY_POC = LOC.LOC_ID
AND LOC.LOCATION_END_DT = DATE '9999-12-31'
Ln:31 Not executed - offline mode set
Ln:44 SELECT
diff --git a/hplsql/src/test/results/offline/select_db2.out.txt
b/hplsql/src/test/results/offline/select_db2.out.txt
index bb5b4558d1a..80d83e47707 100644
--- a/hplsql/src/test/results/offline/select_db2.out.txt
+++ b/hplsql/src/test/results/offline/select_db2.out.txt
@@ -1,5 +1,5 @@
Ln:1 SELECT
-Ln:1 select coalesce(max(info_id) + 1, 0) from sproc_info
+Ln:1 select coalesce(max(info_id)+1,0) into NextID from sproc_info with rr use
and keep exclusive locks
Ln:1 Not executed - offline mode set
Ln:3 SELECT
Ln:3 select cd, cd + inc days, cd - inc days + coalesce(inc, 0) days
diff --git a/hplsql/src/test/results/offline/select_teradata.out.txt
b/hplsql/src/test/results/offline/select_teradata.out.txt
index 34ab4338d6a..55f62c062ca 100644
--- a/hplsql/src/test/results/offline/select_teradata.out.txt
+++ b/hplsql/src/test/results/offline/select_teradata.out.txt
@@ -1,7 +1,11 @@
Ln:1 SELECT
-Ln:1 SELECT branch_code, branch_no, c_no, cd_type
+Ln:1 SELECT branch_code,
+ branch_no,
+ c_no,
+ cd_type
FROM EMPLOYEE
- WHERE S_CODE = 'C' AND (branch_no) NOT IN (
+ WHERE S_CODE = 'C'
+ AND (branch_no) NOT IN (
SELECT branch_code
FROM DEPARTMENT
WHERE branch_code = 'ABC'
diff --git a/hplsql/src/test/results/offline/update.out.txt
b/hplsql/src/test/results/offline/update.out.txt
index 3f053b0dd9f..47f68c51e0e 100644
--- a/hplsql/src/test/results/offline/update.out.txt
+++ b/hplsql/src/test/results/offline/update.out.txt
@@ -1,46 +1,40 @@
Ln:1 UPDATE
-Ln:1 SET c1 = 'TRIM(c1)'
-Ln:1 UPDATE tab T SET c1 = TRIM(c1) WHERE T.c2 = 'A'
+Ln:1 UPDATE tab T SET (c1) = TRIM(c1) WHERE T.c2 = 'A'
Ln:3 UPDATE
-Ln:4 SET c1 = 'TRIM(c1)'
Ln:3 UPDATE tab T
SET c1 = TRIM(c1)
WHERE T.c2 = 'A'
Ln:7 UPDATE
-Ln:7 SET c1 = '''0011'''
Ln:7 UPDATE tab SET c1 = '0011' WHERE c1 = '0021'
Ln:9 UPDATE
-Ln:9 SET c1 = 'TRIM(c1)'
-Ln:9 SET c3 = 'TRIM(c3)'
Ln:9 UPDATE tab T SET c1 = TRIM(c1), c3 = TRIM(c3) WHERE T.col2 = 'A'
Ln:11 UPDATE
-Ln:12 SET c1 = TRIM(c1)
-Ln:12 SET c3 = TRIM(c3)
Ln:11 UPDATE tab T
SET (c1, c3) = (TRIM(c1), TRIM(c3))
WHERE T.col2 = 'A'
Ln:15 UPDATE
Ln:15 UPDATE tab T
SET (c1, c2, c3, c4) =
- (SELECT c1, c2, TRIM(c3), c4
+ (SELECT c1,
+ c2,
+ TRIM(c3),
+ c4
FROM tab2 C
WHERE C.c1 = T.c1)
WHERE T.c2 = 'A'
Ln:25 UPDATE
-Ln:26 SET c1 = '(SELECT c1 FROM tab2 C WHERE C.c1 = T.c1)'
Ln:25 UPDATE tab T
- SET c1 = (SELECT c1 FROM tab2 C WHERE C.c1 = T.c1)
+ SET (c1) =
+ (SELECT c1 FROM tab2 C WHERE C.c1 = T.c1)
WHERE T.c2 = 'a'
Ln:30 UPDATE
-Ln:31 SET c1 = '(SELECT c1 FROM tab2 C WHERE C.c1 = T.c1)'
Ln:30 UPDATE tab T
- SET c1 = (SELECT c1 FROM tab2 C WHERE C.c1 = T.c1)
+ SET c1 =
+ (SELECT c1 FROM tab2 C WHERE C.c1 = T.c1)
WHERE T.c2 = 'a'
Ln:35 DECLARE var1 STRING
Ln:36 SET var1 = 'var1_value'
Ln:37 UPDATE
-Ln:37 SET c2 = '''update_value'''
-Ln:37 UPDATE tab SET c2 = 'update_value' WHERE c1 = 'var1_value'
+Ln:37 UPDATE tab SET c2 = 'update_value' WHERE c1 = var1
Ln:38 UPDATE
-Ln:38 SET c2 = '''var1_value'''
-Ln:38 UPDATE tab SET c2 = 'var1_value' WHERE c1 = 'var1_value'
\ No newline at end of file
+Ln:38 UPDATE tab SET c2 = var1 WHERE c1 = 'var1_value'
\ No newline at end of file
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 0ae643c5526..a9118dfc4b2 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
@@ -613,7 +613,7 @@ public class TestHplSqlViaBeeLine {
}
@Test
- public void testACTIVITY_COUNTHplSqlFunction() throws Throwable {
+ public void testACTIVITY_COUNTHplSqlFunction1() throws Throwable {
String SCRIPT_TEXT =
"DROP TABLE IF EXISTS result;\n" +
"CREATE TABLE result (col1 string);\n" +
@@ -624,6 +624,21 @@ public class TestHplSqlViaBeeLine {
testScriptFile(SCRIPT_TEXT, args(), "2");
}
+ @Test
+ public void testACTIVITY_COUNTHplSqlFunction2() 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" +
+ "DECLARE var INT;\n" +
+ "SELECT count(*) INTO var FROM result;\n" +
+ "IF ACTIVITY_COUNT = 1 THEN\n" +
+ " PRINT 'id = ' || var;\n" +
+ "END IF;";
+ testScriptFile(SCRIPT_TEXT, args(), "id = 2", OutStream.ERR);
+ }
+
@Test
public void testCASTHplSqlFunction1() throws Throwable {
String SCRIPT_TEXT = "SELECT CAST('Abc' AS CHAR(1));";
@@ -636,6 +651,12 @@ public class TestHplSqlViaBeeLine {
testScriptFile(SCRIPT_TEXT, args(), "2015-03-12");
}
+ @Test
+ public void testCASTHplSqlFunction3() throws Throwable {
+ String SCRIPT_TEXT = "SELECT CAST(DATE '2023-05-29' AS CHAR(4));";
+ testScriptFile(SCRIPT_TEXT, args(), "2023");
+ }
+
@Test
public void testCHARHplSqlFunction() throws Throwable {
String SCRIPT_TEXT = "select CHAR(2023)";
@@ -1089,6 +1110,270 @@ public class TestHplSqlViaBeeLine {
testScriptFile(SCRIPT_TEXT, args(), "^(.(?!(" +
System.getProperty("user.name") + ")))*$");
}
+ @Test
+ public void testHplSqlProcedureWithCast1Udf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (id int, s string);\n" +
+ "CREATE PROCEDURE p1(s1 STRING, s2 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(cast(s1 as int), s2);\n" +
+ "END;\n" +
+ "p1('786', 'Cloud');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "786.*Cloud");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithCast2Udf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (s1 string, s2 string);\n" +
+ "CREATE PROCEDURE p1(s1 STRING, s2 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(cast(s1 as char(1)), s2);\n" +
+ "END;\n" +
+ "p1('Hive', 'Cloud');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "H.*Cloud");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithCast3Udf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (s1 string, s2 string);\n" +
+ "CREATE PROCEDURE p1(s1 STRING, s2 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(CAST(TIMESTAMP '2015-03-12
10:58:34.111' AS CHAR(10)), s2);\n" +
+ "END;\n" +
+ "p1('Hive', 'Cloud');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "2015-03-12.*Cloud");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithCOALESCEUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (id int, s string);\n" +
+ "CREATE PROCEDURE p1(s1 int, s2 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(COALESCE(NULL, s1, 2, 3), s2);\n" +
+ "END;\n" +
+ "p1(786, 'Cloud');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "786.*Cloud");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithCONCATUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (id int, s string);\n" +
+ "CREATE PROCEDURE p1(s1 int, s2 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(s1, CONCAT('M', 'y ', NULL, s2));\n" +
+ "END;\n" +
+ "p1(786, 'Cloud');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "786.*My Cloud");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithSYSDATEUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String, join_date timestamp);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(s1, SYSDATE);\n" +
+ "END;\n" +
+ "p1('Bob');\n" +
+ "SELECT * FROM result;" ;
+ Timestamp today = new Timestamp(System.currentTimeMillis());
+ String timestamp = today.toString();
+ testScriptFile(SCRIPT_TEXT, args(), "Bob.*" + timestamp.substring(0,
timestamp.length() - 9));
+ }
+
+ @Test
+ public void testHplSqlProcedureWithCurrent_DateUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String, join_date date);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(s1, current_date);\n" +
+ "END;\n" +
+ "p1('Bob');\n" +
+ "SELECT * FROM result;" ;
+ Date today = new Date(System.currentTimeMillis());
+ testScriptFile(SCRIPT_TEXT, args(), "Bob.*" + today.toString());
+ }
+
+ @Test
+ public void testHplSqlProcedureWithCurrent_TimestampUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String, join_time timestamp);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(s1, current_timestamp);\n" +
+ "END;\n" +
+ "p1('Bob');\n" +
+ "SELECT * FROM result;" ;
+ Timestamp today = new Timestamp(System.currentTimeMillis());
+ String timestamp = today.toString();
+ testScriptFile(SCRIPT_TEXT, args(), "Bob.*" + timestamp.substring(0,
timestamp.length() - 9));
+ }
+
+ @Test
+ public void testHplSqlProcedureWithCurrent_UserUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(current_user);\n" +
+ "END;\n" +
+ "p1('Bob');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), System.getProperty("user.name"));
+ }
+
+ @Test
+ public void testHplSqlProcedureWithFrom_UnixtimeUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String);\n" +
+ "CREATE PROCEDURE p1(jt int)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(from_unixtime(jt, 'yyyy-MM-dd'));\n" +
+ "END;\n" +
+ "p1(1447141681);\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "2015-11-");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithInstrUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(instr(s1, 'b', -3, 2));\n" +
+ "END;\n" +
+ "p1('abcabcabc');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "2");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithLowerUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(lower(s1));\n" +
+ "END;\n" +
+ "p1('ABCD');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "abcd");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithLengthUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(length(s1));\n" +
+ "END;\n" +
+ "p1('Cloud');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "5");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithNvlUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(nvl(NULL,s1));\n" +
+ "END;\n" +
+ "p1('Cloud');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "Cloud");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithReplaceUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(replace(s1, '-', ''));\n" +
+ "END;\n" +
+ "p1('2024-02-29');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "20240229");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithTrimUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES('#' || TRIM(s1) || '#');\n" +
+ "END;\n" +
+ "p1(' Cloud ');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "#Cloud#");
+ }
+
+ @Test
+ public void testHplSqlProcedureWithUnix_TimestampUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (col_millis int);\n" +
+ "CREATE PROCEDURE p1()\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(UNIX_TIMESTAMP());\n" +
+ "END;\n" +
+ "p1();\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(),
String.valueOf(System.currentTimeMillis()/100000));
+ }
+
+ @Test
+ public void testHplSqlProcedureWithUpperUdf() throws Throwable {
+ String SCRIPT_TEXT =
+ "DROP TABLE IF EXISTS result;\n" +
+ "CREATE TABLE result (name String);\n" +
+ "CREATE PROCEDURE p1(s1 string)\n" +
+ "BEGIN\n" +
+ "INSERT INTO result VALUES(upper(s1));\n" +
+ "END;\n" +
+ "p1('abcd');\n" +
+ "SELECT * FROM result;" ;
+ testScriptFile(SCRIPT_TEXT, args(), "ABCD");
+ }
+
+ @Test
+ public void testCastHplSqlFunction() throws Throwable {
+ String SCRIPT_TEXT =
+ "SELECT CAST('1' AS int);" ;
+ testScriptFile(SCRIPT_TEXT, args(), "1");
+ }
+
private static List<String> args() {
return Arrays.asList("-d", BeeLine.BEELINE_DEFAULT_JDBC_DRIVER,
"-u", miniHS2.getBaseJdbcURL() + ";mode=hplsql", "-n", userName);