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 -&gt; '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);

Reply via email to