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 feda35389dc HIVE-27491: HPL/SQL does not allow variables in update
statements (Dayakar M, reviewed by Krisztian Kasa)
feda35389dc is described below
commit feda35389dc28c8c9bf3c8a3d39de53ba90e41c0
Author: Dayakar M <[email protected]>
AuthorDate: Mon Nov 20 10:22:43 2023 +0530
HIVE-27491: HPL/SQL does not allow variables in update statements (Dayakar
M, reviewed by Krisztian Kasa)
---
.../src/main/java/org/apache/hive/hplsql/Exec.java | 46 +++++++++--
.../src/main/java/org/apache/hive/hplsql/Stmt.java | 91 ++++++++++++++++------
hplsql/src/test/queries/offline/update.sql | 15 ++--
hplsql/src/test/results/offline/update.out.txt | 40 ++++++----
4 files changed, 141 insertions(+), 51 deletions(-)
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 12690126374..8d5cab92e13 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
@@ -206,7 +206,7 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
else {
var = new Var(value);
var.setName(name);
- if(exec.currentScope != null) {
+ if(exec.currentScope != null && !exec.buildSql) {
exec.currentScope.addVariable(var);
}
}
@@ -216,7 +216,7 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
public Var setVariable(String name) {
return setVariable(name, Var.Empty);
}
-
+
public Var setVariable(String name, String value) {
return setVariable(name, new Var(value));
}
@@ -1630,6 +1630,18 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
String name = ctx.ident().getText();
visit(ctx.expr());
Var var = setVariable(name);
+ StringBuilder assignments = new StringBuilder();
+ String previousAssignment = stackPop().toString();
+ if (previousAssignment != null) {
+ assignments.append(previousAssignment);
+ assignments.append(", ");
+ }
+ assignments.append(name);
+ assignments.append(" = ");
+ assignments.append(var.toString());
+ if (exec.buildSql) {
+ stackPush(assignments);
+ }
if (trace) {
trace(ctx, "SET " + name + " = " + var.toSqlString());
}
@@ -1642,17 +1654,30 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
@Override
public Integer
visitAssignment_stmt_multiple_item(HplsqlParser.Assignment_stmt_multiple_itemContext
ctx) {
int cnt = ctx.ident().size();
- int ecnt = ctx.expr().size();
+ int ecnt = ctx.expr().size();
+ StringBuilder identifiers = new StringBuilder("(");
+ StringBuilder expressions = new StringBuilder("(");
for (int i = 0; i < cnt; i++) {
String name = ctx.ident(i).getText();
if (i < ecnt) {
visit(ctx.expr(i));
- Var var = setVariable(name);
+ Var var = setVariable(name);
+ if (i > 0) {
+ identifiers.append(", ");
+ expressions.append(", ");
+ }
+ identifiers.append(name);
+ expressions.append(var.toString());
if (trace) {
trace(ctx, "SET " + name + " = " + var.toString());
}
}
- }
+ }
+ identifiers.append(")");
+ expressions.append(")");
+ if (exec.buildSql) {
+ stackPush(identifiers.toString() + " = " + expressions.toString());
+ }
return 0;
}
@@ -2775,9 +2800,16 @@ public class Exec extends HplsqlBaseVisitor<Integer>
implements Closeable {
/**
* Get formatted text between 2 tokens
*/
- public static String getFormattedText(ParserRuleContext ctx) {
+ public static String getFormattedText(ParserRuleContext ctx, int startIndex,
int endIndex) {
return ctx.start.getInputStream().getText(
- new org.antlr.v4.runtime.misc.Interval(ctx.start.getStartIndex(),
ctx.stop.getStopIndex()));
+ new org.antlr.v4.runtime.misc.Interval(startIndex, endIndex));
+ }
+
+ /**
+ * Get formatted text between 2 tokens
+ */
+ public static String getFormattedText(ParserRuleContext ctx) {
+ return getFormattedText(ctx, ctx.start.getStartIndex(),
ctx.stop.getStopIndex());
}
/**
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 17bf0d08928..eac3a6d0d2d 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java
@@ -35,6 +35,7 @@ import org.apache.hive.hplsql.executor.QueryException;
import org.apache.hive.hplsql.executor.QueryExecutor;
import org.apache.hive.hplsql.executor.QueryResult;
import org.apache.hive.hplsql.objects.Table;
+import org.jetbrains.annotations.NotNull;
/**
* HPL/SQL statements execution
@@ -665,7 +666,16 @@ public class Stmt {
/**
* Assignment from SELECT statement
*/
- public Integer
assignFromSelect(HplsqlParser.Assignment_stmt_select_itemContext ctx) {
+ public Integer
assignFromSelect(HplsqlParser.Assignment_stmt_select_itemContext ctx) {
+ if (exec.buildSql) {
+ StringBuilder sb = new StringBuilder();
+ sb.append(Exec.getFormattedText(ctx, ctx.start.getStartIndex(),
ctx.select_stmt().getStart().getStartIndex()-1));
+ sb.append(evalPop(ctx.select_stmt()).toString());
+ sb.append(")");
+ exec.stackPush(sb);
+ return 0;
+ }
+
String sql = evalPop(ctx.select_stmt()).toString();
if (trace) {
trace(ctx, sql);
@@ -677,27 +687,7 @@ public class Stmt {
}
exec.setSqlSuccess();
try {
- int cnt = ctx.ident().size();
- if (query.next()) {
- for (int i = 0; i < cnt; i++) {
- Var var = exec.findVariable(ctx.ident(i).getText());
- if (var != null) {
- var.setValue(query, i);
- if (trace) {
- trace(ctx, "COLUMN: " + query.metadata().columnName(i) + ", " +
query.metadata().columnTypeName(i));
- trace(ctx, "SET " + var.getName() + " = " + var.toString());
- }
- }
- else if(trace) {
- trace(ctx, "Variable not found: " + ctx.ident(i).getText());
- }
- }
- exec.incRowCount();
- exec.setSqlSuccess();
- } else {
- exec.setSqlCode(SqlCodes.NO_DATA_FOUND);
- exec.signal(Signal.Type.NOTFOUND);
- }
+ processQueryResult(ctx, query);
} catch (QueryException e) {
exec.signal(query);
return 1;
@@ -706,7 +696,31 @@ public class Stmt {
}
return 0;
}
-
+
+ private void
processQueryResult(HplsqlParser.Assignment_stmt_select_itemContext ctx,
QueryResult query) {
+ int cnt = ctx.ident().size();
+ if (query.next()) {
+ for (int i = 0; i < cnt; i++) {
+ Var var = exec.findVariable(ctx.ident(i).getText());
+ if (var != null) {
+ var.setValue(query, i);
+ if (trace) {
+ trace(ctx, "COLUMN: " + query.metadata().columnName(i) + ", " +
query.metadata().columnTypeName(i));
+ trace(ctx, "SET " + var.getName() + " = " + var.toString());
+ }
+ }
+ else if(trace) {
+ trace(ctx, "Variable not found: " + ctx.ident(i).getText());
+ }
+ }
+ exec.incRowCount();
+ exec.setSqlSuccess();
+ } else {
+ exec.setSqlCode(SqlCodes.NO_DATA_FOUND);
+ exec.signal(Signal.Type.NOTFOUND);
+ }
+ }
+
/**
* SQL INSERT statement
*/
@@ -1152,7 +1166,14 @@ public class Stmt {
*/
public Integer update(HplsqlParser.Update_stmtContext ctx) {
trace(ctx, "UPDATE");
- String sql = exec.getFormattedText(ctx);
+ boolean oldBuildSql = exec.buildSql;
+ String sql = null;
+ try {
+ exec.buildSql = true;
+ sql = generateUpdateQuery(ctx);
+ } finally {
+ exec.buildSql = oldBuildSql;
+ }
trace(ctx, sql);
QueryResult query = queryExecutor.executeQuery(sql, ctx);
if (query.error()) {
@@ -1163,7 +1184,27 @@ public class Stmt {
query.close();
return 0;
}
-
+
+ @NotNull
+ private String generateUpdateQuery(HplsqlParser.Update_stmtContext ctx) {
+ HplsqlParser.Update_assignmentContext updateAssignmentContext =
ctx.update_assignment();
+ StringBuilder sql = new StringBuilder(
+ Exec.getFormattedText(ctx, ctx.start.getStartIndex(),
(updateAssignmentContext.start.getStartIndex() - 1)));
+ sql.append(evalPop(updateAssignmentContext).toString());
+ Token last = updateAssignmentContext.getStop();
+ HplsqlParser.Where_clauseContext whereClauseContext = ctx.where_clause();
+ if (whereClauseContext != null) {
+ exec.append(sql, evalPop(whereClauseContext).toString(), last,
whereClauseContext.getStart());
+ last = whereClauseContext.getStop();
+ }
+ HplsqlParser.Update_upsertContext updateUpsertContext =
ctx.update_upsert();
+ if (updateUpsertContext != null) {
+ exec.append(sql, Exec.getFormattedText(updateUpsertContext,
updateUpsertContext.start.getStartIndex(),
+ updateUpsertContext.stop.getStopIndex()), last,
updateUpsertContext.getStart());
+ }
+ return sql.toString();
+ }
+
/**
* DELETE statement
*/
diff --git a/hplsql/src/test/queries/offline/update.sql
b/hplsql/src/test/queries/offline/update.sql
index f40fb0fe247..0c72c1875ff 100644
--- a/hplsql/src/test/queries/offline/update.sql
+++ b/hplsql/src/test/queries/offline/update.sql
@@ -1,15 +1,15 @@
UPDATE tab T SET (c1) = TRIM(c1) WHERE T.c2 = 'A';
-UPDATE tab T
- SET c1 = TRIM(c1)
+UPDATE tab T
+ SET c1 = TRIM(c1)
WHERE T.c2 = 'A';
-
+
UPDATE tab SET c1 = '0011' WHERE c1 = '0021';
UPDATE tab T SET c1 = TRIM(c1), c3 = TRIM(c3) WHERE T.col2 = 'A';
-UPDATE tab T
- SET (c1, c3) = (TRIM(c1), TRIM(c3))
+UPDATE tab T
+ SET (c1, c3) = (TRIM(c1), TRIM(c3))
WHERE T.col2 = 'A';
UPDATE tab T
@@ -31,3 +31,8 @@ UPDATE tab T
SET c1 =
(SELECT c1 FROM tab2 C WHERE C.c1 = T.c1)
WHERE T.c2 = 'a';
+
+DECLARE var1 STRING;
+var1 := 'var1_value';
+UPDATE tab SET c2 = 'update_value' WHERE c1 = var1;
+UPDATE tab SET c2 = var1 WHERE c1 = 'var1_value';
diff --git a/hplsql/src/test/results/offline/update.out.txt
b/hplsql/src/test/results/offline/update.out.txt
index fadaac043ab..3f053b0dd9f 100644
--- a/hplsql/src/test/results/offline/update.out.txt
+++ b/hplsql/src/test/results/offline/update.out.txt
@@ -1,34 +1,46 @@
Ln:1 UPDATE
-Ln:1 UPDATE tab T SET (c1) = TRIM(c1) WHERE T.c2 = 'A'
+Ln:1 SET c1 = 'TRIM(c1)'
+Ln:1 UPDATE tab T SET c1 = TRIM(c1) WHERE T.c2 = 'A'
Ln:3 UPDATE
-Ln:3 UPDATE tab T
- SET c1 = TRIM(c1)
+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:11 UPDATE tab T
- SET (c1, c3) = (TRIM(c1), TRIM(c3))
+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)
- WHERE T.c2 = 'a'
\ No newline at end of file
+ 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: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
