You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by dm...@apache.org on 2016/05/04 10:13:54 UTC
hive git commit: HIVE-13516: Adding BTEQ .IF, .QUIT,
ERRORCODE to HPL/SQL (Dmitry Tolpeko reviewed by Alan Gates
Repository: hive
Updated Branches:
refs/heads/master 872996629 -> 2d33d091b
HIVE-13516: Adding BTEQ .IF, .QUIT, ERRORCODE to HPL/SQL (Dmitry Tolpeko reviewed by Alan Gates
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/2d33d091
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/2d33d091
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/2d33d091
Branch: refs/heads/master
Commit: 2d33d091b61dce092543970e62f41b63af1f32d1
Parents: 8729966
Author: Dmitry Tolpeko <dm...@gmail.com>
Authored: Wed May 4 03:13:18 2016 -0700
Committer: Dmitry Tolpeko <dm...@gmail.com>
Committed: Wed May 4 03:13:18 2016 -0700
----------------------------------------------------------------------
.../antlr4/org/apache/hive/hplsql/Hplsql.g4 | 108 ++++++++++---
.../main/java/org/apache/hive/hplsql/Exec.java | 67 +++++++-
.../java/org/apache/hive/hplsql/Expression.java | 31 ++--
.../java/org/apache/hive/hplsql/Select.java | 31 ++--
.../java/org/apache/hive/hplsql/Signal.java | 2 +-
.../main/java/org/apache/hive/hplsql/Stmt.java | 154 ++++++++++++-------
hplsql/src/main/resources/hplsql-site.xml | 2 -
.../org/apache/hive/hplsql/TestHplsqlLocal.java | 5 +
.../apache/hive/hplsql/TestHplsqlOffline.java | 20 +++
hplsql/src/test/queries/local/if3_bteq.sql | 3 +
.../test/queries/offline/create_table_td.sql | 45 ++++++
hplsql/src/test/queries/offline/delete_all.sql | 1 +
hplsql/src/test/queries/offline/select.sql | 42 +++++
.../test/queries/offline/select_teradata.sql | 12 ++
hplsql/src/test/results/db/select_into.out.txt | 3 +-
hplsql/src/test/results/db/select_into2.out.txt | 4 +-
hplsql/src/test/results/local/if3_bteq.out.txt | 3 +
hplsql/src/test/results/local/lang.out.txt | 10 +-
.../results/offline/create_table_mssql.out.txt | 39 ++---
.../results/offline/create_table_mssql2.out.txt | 13 +-
.../results/offline/create_table_mysql.out.txt | 5 +-
.../results/offline/create_table_ora.out.txt | 65 ++++----
.../results/offline/create_table_ora2.out.txt | 9 +-
.../results/offline/create_table_pg.out.txt | 7 +-
.../results/offline/create_table_td.out.txt | 31 ++++
.../src/test/results/offline/delete_all.out.txt | 2 +
hplsql/src/test/results/offline/select.out.txt | 34 ++++
.../src/test/results/offline/select_db2.out.txt | 3 +-
.../results/offline/select_teradata.out.txt | 10 ++
29 files changed, 589 insertions(+), 172 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4
----------------------------------------------------------------------
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 b84116f..5ce0e23 100644
--- a/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4
+++ b/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4
@@ -30,7 +30,7 @@ single_block_stmt : // Single BEGIN END blo
T_BEGIN block exception_block? block_end
| stmt T_SEMICOLON?
;
-
+
block_end :
{!_input.LT(2).getText().equalsIgnoreCase("TRANSACTION")}? T_END
;
@@ -48,6 +48,7 @@ stmt :
| begin_transaction_stmt
| break_stmt
| call_stmt
+ | collect_stats_stmt
| close_stmt
| cmp_stmt
| copy_from_ftp_stmt
@@ -83,6 +84,7 @@ stmt :
| merge_stmt
| open_stmt
| print_stmt
+ | quit_stmt
| raise_stmt
| resignal_stmt
| return_stmt
@@ -181,9 +183,9 @@ declare_block_inplace :
declare_stmt_item :
declare_cursor_item
- | declare_var_item
| declare_condition_item
| declare_handler_item
+ | declare_var_item
| declare_temporary_table_item
;
@@ -213,15 +215,19 @@ declare_handler_item : // Condition handler declaration
;
declare_temporary_table_item : // DECLARE TEMPORARY TABLE statement
- T_GLOBAL? T_TEMPORARY T_TABLE ident (T_AS? T_OPEN_P select_stmt T_CLOSE_P | T_AS? select_stmt | T_OPEN_P create_table_columns T_CLOSE_P) create_table_options?
+ T_GLOBAL? T_TEMPORARY T_TABLE ident create_table_preoptions? create_table_definition
;
create_table_stmt :
- T_CREATE T_TABLE (T_IF T_NOT T_EXISTS)? table_name T_OPEN_P create_table_columns T_CLOSE_P create_table_options?
+ T_CREATE T_TABLE (T_IF T_NOT T_EXISTS)? table_name create_table_preoptions? create_table_definition
;
create_local_temp_table_stmt :
- T_CREATE (T_LOCAL T_TEMPORARY | (T_SET | T_MULTISET)? T_VOLATILE) T_TABLE ident create_table_preoptions? T_OPEN_P create_table_columns T_CLOSE_P create_table_options?
+ T_CREATE (T_LOCAL T_TEMPORARY | (T_SET | T_MULTISET)? T_VOLATILE) T_TABLE ident create_table_preoptions? create_table_definition
+ ;
+
+create_table_definition :
+ (T_AS? T_OPEN_P select_stmt T_CLOSE_P | T_AS? select_stmt | T_OPEN_P create_table_columns T_CLOSE_P) create_table_options?
;
create_table_columns :
@@ -262,7 +268,7 @@ create_table_preoptions :
;
create_table_preoptions_item :
- T_NO? T_LOG
+ T_NO? (T_LOG | T_FALLBACK)
;
create_table_options :
@@ -273,6 +279,7 @@ create_table_options_item :
T_ON T_COMMIT (T_DELETE | T_PRESERVE) T_ROWS
| create_table_options_ora_item
| create_table_options_db2_item
+ | create_table_options_td_item
| create_table_options_hive_item
| create_table_options_mssql_item
| create_table_options_mysql_item
@@ -296,6 +303,11 @@ create_table_options_db2_item :
| T_DEFINITION T_ONLY
;
+create_table_options_td_item :
+ T_UNIQUE? T_PRIMARY T_INDEX T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P
+ | T_WITH T_DATA
+ ;
+
create_table_options_hive_item :
create_table_hive_row_format
;
@@ -379,7 +391,7 @@ dtype : // Data types
| T_VARCHAR
| T_VARCHAR2
| T_XML
- | L_ID ('%' (T_TYPE | T_ROWTYPE))? // User-defined or derived data type
+ | ident ('%' (T_TYPE | T_ROWTYPE))? // User-defined or derived data type
;
dtype_len : // Data type length or size specification
@@ -450,7 +462,9 @@ create_routine_params :
T_OPEN_P T_CLOSE_P
| T_OPEN_P create_routine_param_item (T_COMMA create_routine_param_item)* T_CLOSE_P
| {!_input.LT(1).getText().equalsIgnoreCase("IS") &&
- !_input.LT(1).getText().equalsIgnoreCase("AS")}?
+ !_input.LT(1).getText().equalsIgnoreCase("AS") &&
+ !(_input.LT(1).getText().equalsIgnoreCase("DYNAMIC") && _input.LT(2).getText().equalsIgnoreCase("RESULT"))
+ }?
create_routine_param_item (T_COMMA create_routine_param_item)*
;
@@ -484,6 +498,7 @@ exec_stmt : // EXEC, EXECUTE IMMEDIATE statement
if_stmt : // IF statement
if_plsql_stmt
| if_tsql_stmt
+ | if_bteq_stmt
;
if_plsql_stmt :
@@ -494,13 +509,17 @@ if_tsql_stmt :
T_IF bool_expr single_block_stmt (T_ELSE single_block_stmt)?
;
+if_bteq_stmt :
+ '.' T_IF bool_expr T_THEN single_block_stmt
+ ;
+
elseif_block :
(T_ELSIF | T_ELSEIF) bool_expr T_THEN block
;
else_block :
T_ELSE block
- ;
+ ;
include_stmt : // INCLUDE statement
T_INCLUDE (file_name | expr)
@@ -571,6 +590,14 @@ fetch_stmt : // FETCH cursor statement
T_FETCH T_FROM? L_ID T_INTO L_ID (T_COMMA L_ID)*
;
+collect_stats_stmt :
+ T_COLLECT (T_STATISTICS | T_STATS) T_ON table_name collect_stats_clause?
+ ;
+
+collect_stats_clause :
+ T_COLUMN T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P
+ ;
+
close_stmt : // CLOSE cursor statement
T_CLOSE L_ID
;
@@ -652,6 +679,10 @@ print_stmt : // PRINT statement
T_PRINT expr
| T_PRINT T_OPEN_P expr T_CLOSE_P
;
+
+quit_stmt :
+ '.'? T_QUIT expr?
+ ;
raise_stmt :
T_RAISE
@@ -761,7 +792,7 @@ fullselect_set_clause :
;
subselect_stmt :
- (T_SELECT | T_SEL) select_list into_clause? from_clause? where_clause? group_by_clause? having_clause? order_by_clause? select_options?
+ (T_SELECT | T_SEL) select_list into_clause? from_clause? where_clause? group_by_clause? (having_clause | qualify_clause)? order_by_clause? select_options?
;
select_list :
@@ -834,6 +865,8 @@ from_table_values_row:
from_alias_clause :
{!_input.LT(1).getText().equalsIgnoreCase("EXEC") &&
!_input.LT(1).getText().equalsIgnoreCase("EXECUTE") &&
+ !_input.LT(1).getText().equalsIgnoreCase("INNER") &&
+ !_input.LT(1).getText().equalsIgnoreCase("LEFT") &&
!_input.LT(1).getText().equalsIgnoreCase("GROUP") &&
!_input.LT(1).getText().equalsIgnoreCase("ORDER") &&
!_input.LT(1).getText().equalsIgnoreCase("LIMIT") &&
@@ -856,6 +889,10 @@ group_by_clause :
having_clause :
T_HAVING bool_expr
;
+
+qualify_clause :
+ T_QUALIFY bool_expr
+ ;
order_by_clause :
T_ORDER T_BY expr (T_ASC | T_DESC)? (T_COMMA expr (T_ASC | T_DESC)?)*
@@ -879,7 +916,7 @@ update_assignment :
;
update_table :
- (table_name | (T_OPEN_P select_stmt T_CLOSE_P)) (T_AS? ident)?
+ (table_name from_clause? | T_OPEN_P select_stmt T_CLOSE_P) (T_AS? ident)?
;
update_upsert :
@@ -905,9 +942,14 @@ merge_action :
| T_DELETE
;
-delete_stmt : // DELETE statement
- T_DELETE T_FROM? table_name (T_AS? ident)? where_clause?
+delete_stmt :
+ T_DELETE T_FROM? table_name delete_alias? (where_clause | T_ALL)?
;
+
+delete_alias :
+ {!_input.LT(1).getText().equalsIgnoreCase("ALL")}?
+ T_AS? ident
+ ;
describe_stmt :
(T_DESCRIBE | T_DESC) T_TABLE? table_name
@@ -928,6 +970,7 @@ bool_expr_atom :
bool_expr_unary :
expr T_IS T_NOT? T_NULL
| expr T_BETWEEN expr T_AND expr
+ | T_NOT? T_EXISTS T_OPEN_P select_stmt T_CLOSE_P
| bool_expr_single_in
| bool_expr_multi_in
;
@@ -967,6 +1010,7 @@ expr :
| expr T_DIV expr
| expr T_ADD expr
| expr T_SUB expr
+ | T_OPEN_P select_stmt T_CLOSE_P
| T_OPEN_P expr T_CLOSE_P
| expr_interval
| expr_concat
@@ -997,6 +1041,8 @@ interval_item :
| T_DAYS
| T_MICROSECOND
| T_MICROSECONDS
+ | T_SECOND
+ | T_SECONDS
;
expr_concat : // String concatenation operator
@@ -1141,8 +1187,7 @@ timestamp_literal : // TIMESTAMP 'YYYY-MM-DD HH:MI:SS.FFF'
;
ident :
- L_ID
- | non_reserved_words
+ (L_ID | non_reserved_words) ('.' (L_ID | non_reserved_words))*
;
string : // String literal (single or double quoted)
@@ -1207,7 +1252,9 @@ non_reserved_words : // Tokens that are not reserved words
| T_CLOSE
| T_CLUSTERED
| T_CMP
+ | T_COLLECT
| T_COLLECTION
+ | T_COLUMN
| T_COMMENT
| T_CONSTANT
| T_COPY
@@ -1229,6 +1276,7 @@ non_reserved_words : // Tokens that are not reserved words
| T_CURRENT_TIMESTAMP
| T_CURRENT_USER
| T_CURSOR
+ | T_DATA
| T_DATABASE
| T_DATE
| T_DATETIME
@@ -1270,12 +1318,13 @@ non_reserved_words : // Tokens that are not reserved words
| T_EXCEPTION
| T_EXCLUSIVE
| T_EXISTS
- | T_EXIT
+ | T_EXIT
+ | T_FALLBACK
| T_FALSE
| T_FETCH
| T_FIELDS
| T_FILE
- | T_FILES
+ | T_FILES
| T_FIRST_VALUE
| T_FLOAT
| T_FOR
@@ -1390,7 +1439,9 @@ non_reserved_words : // Tokens that are not reserved words
| T_PROC
| T_PROCEDURE
| T_PWD
+ | T_QUALIFY
| T_QUERY_BAND
+ | T_QUIT
| T_QUOTED_IDENTIFIER
| T_RAISE
| T_RANK
@@ -1416,6 +1467,8 @@ non_reserved_words : // Tokens that are not reserved words
| T_ROW_COUNT
| T_ROW_NUMBER
| T_SCHEMA
+ | T_SECOND
+ | T_SECONDS
| T_SECURITY
| T_SEGMENT
| T_SEL
@@ -1434,7 +1487,9 @@ non_reserved_words : // Tokens that are not reserved words
| T_SQLEXCEPTION
| T_SQLINSERT
| T_SQLSTATE
- | T_SQLWARNING
+ | T_SQLWARNING
+ | T_STATS
+ | T_STATISTICS
| T_STEP
| T_STDEV
| T_STORAGE
@@ -1523,7 +1578,9 @@ T_CLIENT : C L I E N T ;
T_CLOSE : C L O S E ;
T_CLUSTERED : C L U S T E R E D;
T_CMP : C M P ;
+T_COLLECT : C O L L E C T ;
T_COLLECTION : C O L L E C T I O N ;
+T_COLUMN : C O L U M N ;
T_COMMENT : C O M M E N T;
T_CONSTANT : C O N S T A N T ;
T_COMMIT : C O M M I T ;
@@ -1541,7 +1598,8 @@ T_CS : C S;
T_CURRENT : C U R R E N T ;
T_CURRENT_SCHEMA : C U R R E N T '_' S C H E M A ;
T_CURSOR : C U R S O R ;
-T_DATABASE : D A T A B A S E;
+T_DATABASE : D A T A B A S E ;
+T_DATA : D A T A ;
T_DATE : D A T E ;
T_DATETIME : D A T E T I M E ;
T_DAY : D A Y ;
@@ -1582,6 +1640,7 @@ T_EXCEPTION : E X C E P T I O N ;
T_EXCLUSIVE : E X C L U S I V E ;
T_EXISTS : E X I S T S ;
T_EXIT : E X I T ;
+T_FALLBACK : F A L L B A C K ;
T_FALSE : F A L S E ;
T_FETCH : F E T C H ;
T_FIELDS : F I E L D S ;
@@ -1694,8 +1753,10 @@ T_PRESERVE : P R E S E R V E ;
T_PRIMARY : P R I M A R Y ;
T_PRINT : P R I N T ;
T_PROC : P R O C ;
-T_PROCEDURE : P R O C E D U R E;
+T_PROCEDURE : P R O C E D U R E ;
+T_QUALIFY : Q U A L I F Y ;
T_QUERY_BAND : Q U E R Y '_' B A N D ;
+T_QUIT : Q U I T ;
T_QUOTED_IDENTIFIER : Q U O T E D '_' I D E N T I F I E R ;
T_RAISE : R A I S E ;
T_REAL : R E A L ;
@@ -1722,6 +1783,8 @@ T_RS : R S ;
T_PWD : P W D ;
T_TRIM : T R I M ;
T_SCHEMA : S C H E M A ;
+T_SECOND : S E C O N D ;
+T_SECONDS : S E C O N D S;
T_SECURITY : S E C U R I T Y ;
T_SEGMENT : S E G M E N T ;
T_SEL : S E L ;
@@ -1742,6 +1805,8 @@ T_SQLEXCEPTION : S Q L E X C E P T I O N ;
T_SQLINSERT : S Q L I N S E R T ;
T_SQLSTATE : S Q L S T A T E ;
T_SQLWARNING : S Q L W A R N I N G ;
+T_STATS : S T A T S ;
+T_STATISTICS : S T A T I S T I C S ;
T_STEP : S T E P ;
T_STORAGE : S T O R A G E ;
T_STRING : S T R I N G ;
@@ -1836,7 +1901,7 @@ T_CLOSE_SB : ']' ;
T_SEMICOLON : ';' ;
T_SUB : '-' ;
-L_ID : L_ID_PART (L_BLANK* '.' L_BLANK* L_ID_PART)* // Identifier
+L_ID : L_ID_PART // Identifier
;
L_S_STRING : '\'' (('\'' '\'') | ('\\' '\'') | ~('\''))* '\'' // Single quoted string literal
;
@@ -1859,6 +1924,7 @@ L_LABEL : ([a-zA-Z] | L_DIGIT | '_')* ':'
fragment
L_ID_PART :
[a-zA-Z] ([a-zA-Z] | L_DIGIT | '_')* // Identifier part
+ | '$' '{' .*? '}'
| ('_' | '@' | ':' | '#' | '$') ([a-zA-Z] | L_DIGIT | '_' | '@' | ':' | '#' | '$')+ // (at least one char must follow special char)
| '"' .*? '"' // Quoted identifiers
| '[' .*? ']'
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
----------------------------------------------------------------------
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 02605a8..67cf2ae 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java
@@ -40,6 +40,7 @@ import org.antlr.v4.runtime.ParserRuleContext;
import org.antlr.v4.runtime.Token;
import org.antlr.v4.runtime.misc.NotNull;
import org.antlr.v4.runtime.tree.ParseTree;
+import org.antlr.v4.runtime.tree.TerminalNode;
import org.apache.commons.io.FileUtils;
import org.apache.hive.hplsql.Var.Type;
import org.apache.hive.hplsql.functions.*;
@@ -50,7 +51,8 @@ import org.apache.hive.hplsql.functions.*;
*/
public class Exec extends HplsqlBaseVisitor<Integer> {
- public static final String VERSION = "HPL/SQL 0.3.17";
+ public static final String VERSION = "HPL/SQL 0.3.31";
+ public static final String ERRORCODE = "ERRORCODE";
public static final String SQLCODE = "SQLCODE";
public static final String SQLSTATE = "SQLSTATE";
public static final String HOSTCODE = "HOSTCODE";
@@ -665,9 +667,14 @@ public class Exec extends HplsqlBaseVisitor<Integer> {
* Set SQLCODE
*/
public void setSqlCode(int sqlcode) {
+ Long code = new Long(sqlcode);
Var var = findVariable(SQLCODE);
if (var != null) {
- var.setValue(new Long(sqlcode));
+ var.setValue(code);
+ }
+ var = findVariable(ERRORCODE);
+ if (var != null) {
+ var.setValue(code);
}
}
@@ -783,6 +790,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> {
new FunctionMisc(this).register(function);
new FunctionString(this).register(function);
new FunctionOra(this).register(function);
+ addVariable(new Var(ERRORCODE, Var.Type.BIGINT, 0L));
addVariable(new Var(SQLCODE, Var.Type.BIGINT, 0L));
addVariable(new Var(SQLSTATE, Var.Type.STRING, "00000"));
addVariable(new Var(HOSTCODE, Var.Type.BIGINT, 0L));
@@ -942,9 +950,10 @@ public class Exec extends HplsqlBaseVisitor<Integer> {
*/
Integer getProgramReturnCode() {
Integer rc = 0;
- if(!signals.empty()) {
+ if (!signals.empty()) {
Signal sig = signals.pop();
- if(sig.type == Signal.Type.LEAVE_ROUTINE && sig.value != null) {
+ if ((sig.type == Signal.Type.LEAVE_PROGRAM || sig.type == Signal.Type.LEAVE_ROUTINE) &&
+ sig.value != null) {
try {
rc = Integer.parseInt(sig.value);
}
@@ -1133,7 +1142,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> {
String scale = null;
Var default_ = null;
if (ctx.dtype().T_ROWTYPE() != null) {
- row = meta.getRowDataType(ctx, exec.conf.defaultConnection, ctx.dtype().L_ID().getText());
+ row = meta.getRowDataType(ctx, exec.conf.defaultConnection, ctx.dtype().ident().getText());
if (row == null) {
type = Var.DERIVED_ROWTYPE;
}
@@ -1184,7 +1193,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> {
String getDataType(HplsqlParser.Declare_var_itemContext ctx) {
String type = null;
if (ctx.dtype().T_TYPE() != null) {
- type = meta.getDataType(ctx, exec.conf.defaultConnection, ctx.dtype().L_ID().getText());
+ type = meta.getDataType(ctx, exec.conf.defaultConnection, ctx.dtype().ident().getText());
if (type == null) {
type = Var.DERIVED_TYPE;
}
@@ -1349,6 +1358,11 @@ public class Exec extends HplsqlBaseVisitor<Integer> {
}
@Override
+ public Integer visitCreate_table_options_td_item(HplsqlParser.Create_table_options_td_itemContext ctx) {
+ return 0;
+ }
+
+ @Override
public Integer visitCreate_table_options_mssql_item(HplsqlParser.Create_table_options_mssql_itemContext ctx) {
return 0;
}
@@ -1678,6 +1692,14 @@ public class Exec extends HplsqlBaseVisitor<Integer> {
}
/**
+ * IF statement (BTEQ syntax)
+ */
+ @Override
+ public Integer visitIf_bteq_stmt(HplsqlParser.If_bteq_stmtContext ctx) {
+ return exec.stmt.ifBteq(ctx);
+ }
+
+ /**
* USE statement
*/
@Override
@@ -1786,6 +1808,14 @@ public class Exec extends HplsqlBaseVisitor<Integer> {
return exec.stmt.print(ctx);
}
+ /**
+ * QUIT statement
+ */
+ @Override
+ public Integer visitQuit_stmt(HplsqlParser.Quit_stmtContext ctx) {
+ return exec.stmt.quit(ctx);
+ }
+
/**
* SIGNAL statement
*/
@@ -2290,6 +2320,31 @@ public class Exec extends HplsqlBaseVisitor<Integer> {
}
/**
+ * Append the text preserving the formatting (space symbols) between tokens
+ */
+ void append(StringBuilder str, String appendStr, Token start, Token stop) {
+ String spaces = start.getInputStream().getText(new org.antlr.v4.runtime.misc.Interval(start.getStartIndex(), stop.getStopIndex()));
+ spaces = spaces.substring(start.getText().length(), spaces.length() - stop.getText().length());
+ str.append(spaces);
+ str.append(appendStr);
+ }
+
+ void append(StringBuilder str, TerminalNode start, TerminalNode stop) {
+ String text = start.getSymbol().getInputStream().getText(new org.antlr.v4.runtime.misc.Interval(start.getSymbol().getStartIndex(), stop.getSymbol().getStopIndex()));
+ str.append(text);
+ }
+
+ /**
+ * Get the first non-null node
+ */
+ TerminalNode nvl(TerminalNode t1, TerminalNode t2) {
+ if (t1 != null) {
+ return t1;
+ }
+ return t2;
+ }
+
+ /**
* Evaluate the expression and pop value from the stack
*/
Var evalPop(ParserRuleContext ctx) {
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java
----------------------------------------------------------------------
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 33ef490..c10f702 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java
@@ -74,8 +74,14 @@ public class Expression {
StringBuilder sql = new StringBuilder();
if (ctx.T_OPEN_P() != null) {
sql.append("(");
- sql.append(evalPop(ctx.expr(0)).toString());
- sql.append(")");
+ if (ctx.select_stmt() != null) {
+ exec.append(sql, evalPop(ctx.select_stmt()).toString(), ctx.T_OPEN_P().getSymbol(), ctx.select_stmt().getStart());
+ exec.append(sql, ctx.T_CLOSE_P().getText(), ctx.select_stmt().stop, ctx.T_CLOSE_P().getSymbol());
+ }
+ else {
+ sql.append(evalPop(ctx.expr(0)).toString());
+ sql.append(")");
+ }
}
else if (ctx.T_MUL() != null) {
sql.append(evalPop(ctx.expr(0)).toString());
@@ -232,6 +238,11 @@ public class Expression {
sql.append(" " + ctx.T_AND().getText() + " ");
sql.append(evalPop(ctx.expr(2)).toString());
}
+ else if (ctx.T_EXISTS() != null) {
+ exec.append(sql, exec.nvl(ctx.T_NOT(), ctx.T_EXISTS()), ctx.T_OPEN_P());
+ exec.append(sql, evalPop(ctx.select_stmt()).toString(), ctx.T_OPEN_P().getSymbol(), ctx.select_stmt().getStart());
+ exec.append(sql, ctx.T_CLOSE_P().getText(), ctx.select_stmt().stop, ctx.T_CLOSE_P().getSymbol());
+ }
else if (ctx.bool_expr_single_in() != null) {
singleInClauseSql(ctx.bool_expr_single_in(), sql);
}
@@ -245,14 +256,12 @@ public class Expression {
/**
* Single value IN clause in executable SQL statement
*/
- public void singleInClauseSql(HplsqlParser.Bool_expr_single_inContext ctx, StringBuilder sql) {
- sql.append(evalPop(ctx.expr(0)).toString());
- if (ctx.T_NOT() != null) {
- sql.append(" " + ctx.T_NOT().getText());
- }
- sql.append(" " + ctx.T_IN().getText() + " (");
+ public void singleInClauseSql(HplsqlParser.Bool_expr_single_inContext ctx, StringBuilder sql) {
+ sql.append(evalPop(ctx.expr(0)).toString() + " ");
+ exec.append(sql, exec.nvl(ctx.T_NOT(), ctx.T_IN()), ctx.T_OPEN_P());
if (ctx.select_stmt() != null) {
- sql.append(evalPop(ctx.select_stmt()));
+ exec.append(sql, evalPop(ctx.select_stmt()).toString(), ctx.T_OPEN_P().getSymbol(), ctx.select_stmt().getStart());
+ exec.append(sql, ctx.T_CLOSE_P().getText(), ctx.select_stmt().stop, ctx.T_CLOSE_P().getSymbol());
}
else {
int cnt = ctx.expr().size();
@@ -262,8 +271,8 @@ public class Expression {
sql.append(", ");
}
}
- }
- sql.append(")");
+ sql.append(")");
+ }
}
/**
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/main/java/org/apache/hive/hplsql/Select.java
----------------------------------------------------------------------
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 4bee252..589e984 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Select.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Select.java
@@ -25,6 +25,7 @@ import java.util.List;
import java.util.Stack;
import org.antlr.v4.runtime.ParserRuleContext;
+import org.antlr.v4.runtime.Token;
import org.antlr.v4.runtime.misc.Interval;
public class Select {
@@ -196,26 +197,38 @@ public class Select {
public Integer subselect(HplsqlParser.Subselect_stmtContext ctx) {
StringBuilder sql = new StringBuilder();
- if (ctx.T_SELECT() != null) {
- sql.append(ctx.T_SELECT().getText());
+ sql.append(ctx.start.getText());
+ exec.append(sql, evalPop(ctx.select_list()).toString(), ctx.start, ctx.select_list().getStart());
+ Token last = ctx.select_list().stop;
+ if (ctx.into_clause() != null) {
+ last = ctx.into_clause().stop;
}
- sql.append(" " + evalPop(ctx.select_list()));
if (ctx.from_clause() != null) {
- sql.append(" " + evalPop(ctx.from_clause()));
- } else if (conf.dualTable != null) {
+ exec.append(sql, evalPop(ctx.from_clause()).toString(), last, ctx.from_clause().getStart());
+ last = ctx.from_clause().stop;
+ }
+ else if (conf.dualTable != null) {
sql.append(" FROM " + conf.dualTable);
}
if (ctx.where_clause() != null) {
- sql.append(" " + evalPop(ctx.where_clause()));
+ exec.append(sql, evalPop(ctx.where_clause()).toString(), last, ctx.where_clause().getStart());
+ last = ctx.where_clause().stop;
}
if (ctx.group_by_clause() != null) {
- sql.append(" " + getText(ctx.group_by_clause()));
+ exec.append(sql, getText(ctx.group_by_clause()), last, ctx.group_by_clause().getStart());
+ last = ctx.group_by_clause().stop;
}
if (ctx.having_clause() != null) {
- sql.append(" " + getText(ctx.having_clause()));
+ exec.append(sql, getText(ctx.having_clause()), last, ctx.having_clause().getStart());
+ last = ctx.having_clause().stop;
+ }
+ if (ctx.qualify_clause() != null) {
+ exec.append(sql, getText(ctx.qualify_clause()), last, ctx.qualify_clause().getStart());
+ last = ctx.qualify_clause().stop;
}
if (ctx.order_by_clause() != null) {
- sql.append(" " + getText(ctx.order_by_clause()));
+ exec.append(sql, getText(ctx.order_by_clause()), last, ctx.order_by_clause().getStart());
+ last = ctx.order_by_clause().stop;
}
if (ctx.select_options() != null) {
Var opt = evalPop(ctx.select_options());
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/main/java/org/apache/hive/hplsql/Signal.java
----------------------------------------------------------------------
diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Signal.java b/hplsql/src/main/java/org/apache/hive/hplsql/Signal.java
index 2c8cfc1..ddefcd8 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Signal.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Signal.java
@@ -22,7 +22,7 @@ package org.apache.hive.hplsql;
* Signals and exceptions
*/
public class Signal {
- public enum Type { LEAVE_LOOP, LEAVE_ROUTINE, SQLEXCEPTION, NOTFOUND, UNSUPPORTED_OPERATION, USERDEFINED };
+ public enum Type { LEAVE_LOOP, LEAVE_ROUTINE, LEAVE_PROGRAM, SQLEXCEPTION, NOTFOUND, UNSUPPORTED_OPERATION, USERDEFINED };
Type type;
String value = "";
Exception exception = null;
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java
----------------------------------------------------------------------
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 d35f994..17d2195 100644
--- a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java
+++ b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java
@@ -25,9 +25,8 @@ import java.util.Stack;
import java.util.UUID;
import org.antlr.v4.runtime.ParserRuleContext;
+import org.antlr.v4.runtime.Token;
import org.apache.hive.hplsql.Var.Type;
-import org.apache.hive.hplsql.HplsqlParser.Create_table_columns_itemContext;
-import org.apache.hive.hplsql.HplsqlParser.Create_table_columnsContext;
/**
* HPL/SQL statements execution
@@ -130,30 +129,13 @@ public class Stmt {
public Integer createTable(HplsqlParser.Create_table_stmtContext ctx) {
trace(ctx, "CREATE TABLE");
StringBuilder sql = new StringBuilder();
- sql.append(exec.getText(ctx, ctx.T_CREATE().getSymbol(), ctx.T_TABLE().getSymbol()));
- sql.append(" " + evalPop(ctx.table_name()) + " (");
- int cnt = ctx.create_table_columns().create_table_columns_item().size();
- int cols = 0;
- for (int i = 0; i < cnt; i++) {
- Create_table_columns_itemContext col = ctx.create_table_columns().create_table_columns_item(i);
- if (col.create_table_column_cons() != null) {
- continue;
- }
- if (cols > 0) {
- sql.append(",\n");
- }
- sql.append(evalPop(col.column_name()));
- sql.append(" ");
- sql.append(exec.evalPop(col.dtype(), col.dtype_len()));
- cols++;
- }
- sql.append("\n)");
- if (ctx.create_table_options() != null) {
- String opt = evalPop(ctx.create_table_options()).toString();
- if (opt != null) {
- sql.append(" " + opt);
- }
+ exec.append(sql, ctx.T_CREATE(), ctx.T_TABLE());
+ exec.append(sql, evalPop(ctx.table_name()).toString(), ctx.T_TABLE().getSymbol(), ctx.table_name().getStart());
+ Token last = ctx.table_name().getStop();
+ if (ctx.create_table_preoptions() != null) {
+ last = ctx.create_table_preoptions().stop;
}
+ sql.append(createTableDefinition(ctx.create_table_definition(), last));
trace(ctx, sql.toString());
Query query = exec.executeSql(ctx, sql.toString(), exec.conf.defaultConnection);
if (query.error()) {
@@ -166,6 +148,40 @@ public class Stmt {
}
/**
+ * Get CREATE TABLE definition (columns or query)
+ */
+ String createTableDefinition(HplsqlParser.Create_table_definitionContext ctx, Token last) {
+ StringBuilder sql = new StringBuilder();
+ HplsqlParser.Create_table_columnsContext colCtx = ctx.create_table_columns();
+ if (colCtx != null) {
+ int cnt = colCtx.create_table_columns_item().size();
+ for (int i = 0; i < cnt; i++) {
+ HplsqlParser.Create_table_columns_itemContext col = colCtx.create_table_columns_item(i);
+ if (col.create_table_column_cons() != null) {
+ last = col.getStop();
+ continue;
+ }
+ exec.append(sql, evalPop(col.column_name()).toString(), last, col.column_name().getStop());
+ exec.append(sql, exec.evalPop(col.dtype(), col.dtype_len()), col.column_name().getStop(), col.dtype().getStart());
+ last = col.getStop();
+ }
+ exec.append(sql, ctx.T_CLOSE_P().getText(), last, ctx.T_CLOSE_P().getSymbol());
+ }
+ else {
+ exec.append(sql, evalPop(ctx.select_stmt()).toString(), last, ctx.select_stmt().getStart());
+ exec.append(sql, ctx.T_CLOSE_P().getText(), ctx.select_stmt().stop, ctx.T_CLOSE_P().getSymbol());
+ }
+ HplsqlParser.Create_table_optionsContext options = ctx.create_table_options();
+ if (options != null) {
+ String opt = evalPop(options).toString();
+ if (opt != null) {
+ sql.append(" " + opt);
+ }
+ }
+ return sql.toString();
+ }
+
+ /**
* CREATE TABLE options for Hive
*/
public Integer createTableHiveOptions(HplsqlParser.Create_table_options_hive_itemContext ctx) {
@@ -196,7 +212,6 @@ public class Stmt {
* CREATE TABLE options for MySQL
*/
public Integer createTableMysqlOptions(HplsqlParser.Create_table_options_mysql_itemContext ctx) {
- StringBuilder sql = new StringBuilder();
if (ctx.T_COMMENT() != null) {
evalString(ctx.T_COMMENT().getText() + " " + evalPop(ctx.expr()).toSqlString());
}
@@ -207,11 +222,8 @@ public class Stmt {
* DECLARE TEMPORARY TABLE statement
*/
public Integer declareTemporaryTable(HplsqlParser.Declare_temporary_table_itemContext ctx) {
- String name = ctx.ident().getText();
- if (trace) {
- trace(ctx, "DECLARE TEMPORARY TABLE " + name);
- }
- return createTemporaryTable(ctx, ctx.create_table_columns(), name);
+ trace(ctx, "DECLARE TEMPORARY TABLE");
+ return createTemporaryTable(ctx.ident(), ctx.create_table_definition(), ctx.create_table_preoptions());
}
/**
@@ -256,37 +268,45 @@ public class Stmt {
* CREATE LOCAL TEMPORARY | VOLATILE TABLE statement
*/
public Integer createLocalTemporaryTable(HplsqlParser.Create_local_temp_table_stmtContext ctx) {
- String name = ctx.ident().getText();
- if (trace) {
- trace(ctx, "CREATE LOCAL TEMPORARY TABLE " + name);
- }
- return createTemporaryTable(ctx, ctx.create_table_columns(), name);
+ trace(ctx, "CREATE LOCAL TEMPORARY TABLE");
+ return createTemporaryTable(ctx.ident(), ctx.create_table_definition(), ctx.create_table_preoptions());
}
/**
* Create a temporary table statement
*/
- public Integer createTemporaryTable(ParserRuleContext ctx, Create_table_columnsContext colCtx, String name) {
+ public Integer createTemporaryTable(HplsqlParser.IdentContext identCtx, HplsqlParser.Create_table_definitionContext defCtx,
+ HplsqlParser.Create_table_preoptionsContext optCtx) {
+ StringBuilder sql = new StringBuilder();
+ String name = identCtx.getText();
String managedName = null;
- String sql = null;
- String columns = exec.getFormattedText(colCtx);
+ Token last = identCtx.getStop();
+ if (optCtx != null) {
+ last = optCtx.stop;
+ }
if (conf.tempTables == Conf.TempTables.NATIVE) {
- sql = "CREATE TEMPORARY TABLE " + name + "\n(" + columns + "\n)";
- } else if (conf.tempTables == Conf.TempTables.MANAGED) {
+ sql.append("CREATE TEMPORARY TABLE " + name);
+ sql.append(createTableDefinition(defCtx, last));
+ }
+ else if (conf.tempTables == Conf.TempTables.MANAGED) {
managedName = name + "_" + UUID.randomUUID().toString().replace("-","");
if (!conf.tempTablesSchema.isEmpty()) {
managedName = conf.tempTablesSchema + "." + managedName;
}
- sql = "CREATE TABLE " + managedName + "\n(" + columns + "\n)";
+ sql.append("CREATE TABLE " + managedName);
+ sql.append(createTableDefinition(defCtx, last));
if (!conf.tempTablesLocation.isEmpty()) {
- sql += "\nLOCATION '" + conf.tempTablesLocation + "/" + managedName + "'";
+ sql.append("\nLOCATION '" + conf.tempTablesLocation + "/" + managedName + "'");
}
if (trace) {
- trace(ctx, "Managed table name: " + managedName);
+ trace(null, "Managed table name: " + managedName);
}
}
+ if (trace) {
+ trace(null, sql.toString());
+ }
if (sql != null) {
- Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection);
+ Query query = exec.executeSql(null, sql.toString(), exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
@@ -606,6 +626,19 @@ public class Stmt {
}
/**
+ * IF statement (BTEQ syntax)
+ */
+ public Integer ifBteq(HplsqlParser.If_bteq_stmtContext ctx) {
+ trace(ctx, "IF");
+ visit(ctx.bool_expr());
+ if (exec.stackPop().isTrue()) {
+ trace(ctx, "IF TRUE executed");
+ visit(ctx.single_block_stmt());
+ }
+ return 0;
+ }
+
+ /**
* Assignment from SELECT statement
*/
public Integer assignFromSelect(HplsqlParser.Assignment_stmt_select_itemContext ctx) {
@@ -1103,13 +1136,17 @@ public class Stmt {
trace(ctx, "DELETE");
String table = evalPop(ctx.table_name()).toString();
StringBuilder sql = new StringBuilder();
- sql.append("DELETE FROM ");
- sql.append(table);
- if (ctx.where_clause() != null) {
- boolean oldBuildSql = exec.buildSql;
- exec.buildSql = true;
- sql.append(" " + evalPop(ctx.where_clause()).toString());
- exec.buildSql = oldBuildSql;
+ if (ctx.T_ALL() == null) {
+ sql.append("DELETE FROM " + table);
+ if (ctx.where_clause() != null) {
+ boolean oldBuildSql = exec.buildSql;
+ exec.buildSql = true;
+ sql.append(" " + evalPop(ctx.where_clause()).toString());
+ exec.buildSql = oldBuildSql;
+ }
+ }
+ else {
+ sql.append("TRUNCATE TABLE " + table);
}
trace(ctx, sql.toString());
Query query = exec.executeSql(ctx, sql.toString(), exec.conf.defaultConnection);
@@ -1150,6 +1187,19 @@ public class Stmt {
return 0;
}
+ /**
+ * QUIT Statement
+ */
+ public Integer quit(HplsqlParser.Quit_stmtContext ctx) {
+ trace(ctx, "QUIT");
+ String rc = null;
+ if (ctx.expr() != null) {
+ rc = evalPop(ctx.expr()).toString();
+ }
+ exec.signal(Signal.Type.LEAVE_PROGRAM, rc);
+ return 0;
+ }
+
/**
* SET current schema
*/
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/main/resources/hplsql-site.xml
----------------------------------------------------------------------
diff --git a/hplsql/src/main/resources/hplsql-site.xml b/hplsql/src/main/resources/hplsql-site.xml
index 7e2d92d..05fe857 100644
--- a/hplsql/src/main/resources/hplsql-site.xml
+++ b/hplsql/src/main/resources/hplsql-site.xml
@@ -12,7 +12,6 @@
<property>
<name>hplsql.conn.init.hiveconn</name>
<value>
- set mapred.job.queue.name=default;
set hive.execution.engine=mr;
use default;
</value>
@@ -36,7 +35,6 @@
<property>
<name>hplsql.conn.init.hive2conn</name>
<value>
- set mapred.job.queue.name=default;
set hive.execution.engine=mr;
use default;
</value>
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java
----------------------------------------------------------------------
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 80915ea..9b5a956 100644
--- a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java
+++ b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java
@@ -222,6 +222,11 @@ public class TestHplsqlLocal {
public void testIf2() throws Exception {
run("if2");
}
+
+ @Test
+ public void testIf3Bteq() throws Exception {
+ run("if3_bteq");
+ }
@Test
public void testInclude() throws Exception {
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java
----------------------------------------------------------------------
diff --git a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java
index 59b7bff..3e897be 100644
--- a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java
+++ b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java
@@ -64,16 +64,36 @@ public class TestHplsqlOffline {
}
@Test
+ public void testCreateTableTd() throws Exception {
+ run("create_table_td");
+ }
+
+ @Test
+ public void testDeleteAll() throws Exception {
+ run("delete_all");
+ }
+
+ @Test
public void testInsertMysql() throws Exception {
run("insert_mysql");
}
@Test
+ public void testSelect() throws Exception {
+ run("select");
+ }
+
+ @Test
public void testSelectDb2() throws Exception {
run("select_db2");
}
@Test
+ public void testSelectTeradata() throws Exception {
+ run("select_teradata");
+ }
+
+ @Test
public void testUpdate() throws Exception {
run("update");
}
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/queries/local/if3_bteq.sql
----------------------------------------------------------------------
diff --git a/hplsql/src/test/queries/local/if3_bteq.sql b/hplsql/src/test/queries/local/if3_bteq.sql
new file mode 100644
index 0000000..12a39a9
--- /dev/null
+++ b/hplsql/src/test/queries/local/if3_bteq.sql
@@ -0,0 +1,3 @@
+.if errorcode = 0 then .quit errorcode
+
+print 'Failed: must not be executed';
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/queries/offline/create_table_td.sql
----------------------------------------------------------------------
diff --git a/hplsql/src/test/queries/offline/create_table_td.sql b/hplsql/src/test/queries/offline/create_table_td.sql
new file mode 100644
index 0000000..2572bb6
--- /dev/null
+++ b/hplsql/src/test/queries/offline/create_table_td.sql
@@ -0,0 +1,45 @@
+CREATE TABLE tab, NO LOG, NO FALLBACK
+ (
+ SOURCE_ID INT,
+ RUN_ID INT,
+ STATUS CHAR,
+ LOAD_START timestamp(0),
+ LOAD_END timestamp(0)
+ );
+
+CREATE TABLE ctl, NO LOG, NO FALLBACK
+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'
+ GROUP BY
+ 1,3,4,5
+);
+
+CREATE SET VOLATILE TABLE ctl2, NO LOG, NO FALLBACK
+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'
+ GROUP BY
+ 1,3,4,5
+) WITH DATA PRIMARY INDEX (LOAD_START,LOAD_END)
+ ON COMMIT PRESERVE ROWS ;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/queries/offline/delete_all.sql
----------------------------------------------------------------------
diff --git a/hplsql/src/test/queries/offline/delete_all.sql b/hplsql/src/test/queries/offline/delete_all.sql
new file mode 100644
index 0000000..e89fd48
--- /dev/null
+++ b/hplsql/src/test/queries/offline/delete_all.sql
@@ -0,0 +1 @@
+DELETE FROM TEST1_DB.WK_WRK ALL;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/queries/offline/select.sql
----------------------------------------------------------------------
diff --git a/hplsql/src/test/queries/offline/select.sql b/hplsql/src/test/queries/offline/select.sql
new file mode 100644
index 0000000..0b6912e
--- /dev/null
+++ b/hplsql/src/test/queries/offline/select.sql
@@ -0,0 +1,42 @@
+SELECT * FROM a where 1=1 and not exists (select * from b)--abc;
+
+SELECT *
+ FROM a
+ where not exists
+ (
+ select * from b
+ );
+
+SELECT
+ *
+ 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
+ CAST( LOAD_START AS DATE)
+ FROM
+ tab
+ WHERE
+ SOURCE_ID = 451 AND
+ BATCH = 'R'
+ )
+ );
+
+SELECT
+*
+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 LOC.LOCATION_END_DT = DATE '9999-12-31' ;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/queries/offline/select_teradata.sql
----------------------------------------------------------------------
diff --git a/hplsql/src/test/queries/offline/select_teradata.sql b/hplsql/src/test/queries/offline/select_teradata.sql
new file mode 100644
index 0000000..69522b8
--- /dev/null
+++ b/hplsql/src/test/queries/offline/select_teradata.sql
@@ -0,0 +1,12 @@
+SELECT branch_code,
+ branch_no,
+ c_no,
+ cd_type
+FROM EMPLOYEE
+ WHERE S_CODE = 'C'
+ AND (branch_no) NOT IN (
+ SELECT branch_code
+ FROM DEPARTMENT
+ WHERE branch_code = 'ABC'
+ )
+QUALIFY ROW_NUMBER() OVER (PARTITION BY c_no ORDER BY cd_type) = 1
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/db/select_into.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/db/select_into.out.txt b/hplsql/src/test/results/db/select_into.out.txt
index 3f4ae31..6e4a69c 100644
--- a/hplsql/src/test/results/db/select_into.out.txt
+++ b/hplsql/src/test/results/db/select_into.out.txt
@@ -6,7 +6,8 @@ Ln:5 DECLARE v_dec DECIMAL
Ln:6 DECLARE v_dec0 DECIMAL
Ln:7 DECLARE v_str STRING
Ln:9 SELECT
-Ln:9 SELECT CAST(1 AS BIGINT), CAST(1 AS INT), CAST(1 AS SMALLINT), CAST(1 AS TINYINT), CAST(1.1 AS DECIMAL(18,2)), CAST(1.1 AS DECIMAL(18,0)) FROM src LIMIT 1
+Ln:9 SELECT CAST(1 AS BIGINT), CAST(1 AS INT), CAST(1 AS SMALLINT), CAST(1 AS TINYINT), CAST(1.1 AS DECIMAL(18,2)), CAST(1.1 AS DECIMAL(18,0))
+FROM src LIMIT 1
Ln:9 SELECT completed successfully
Ln:9 SELECT INTO statement executed
Ln:9 COLUMN: _c0, bigint
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/db/select_into2.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/db/select_into2.out.txt b/hplsql/src/test/results/db/select_into2.out.txt
index 03e67ad..582fdfb 100644
--- a/hplsql/src/test/results/db/select_into2.out.txt
+++ b/hplsql/src/test/results/db/select_into2.out.txt
@@ -2,7 +2,9 @@ Ln:1 DECLARE v_float float
Ln:2 DECLARE v_double double
Ln:3 DECLARE v_double2 double precision
Ln:5 SELECT
-Ln:5 select cast(1.1 as float), cast(1.1 as double), cast(1.1 as double) from src LIMIT 1
+Ln:5 select
+ cast(1.1 as float), cast(1.1 as double), cast(1.1 as double)
+from src LIMIT 1
Ln:5 SELECT completed successfully
Ln:5 SELECT INTO statement executed
Ln:5 COLUMN: _c0, float
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/local/if3_bteq.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/local/if3_bteq.out.txt b/hplsql/src/test/results/local/if3_bteq.out.txt
new file mode 100644
index 0000000..47f3010
--- /dev/null
+++ b/hplsql/src/test/results/local/if3_bteq.out.txt
@@ -0,0 +1,3 @@
+Ln:1 IF
+Ln:1 IF TRUE executed
+Ln:1 QUIT
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/local/lang.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/local/lang.out.txt b/hplsql/src/test/results/local/lang.out.txt
index 0047ec4..b3c460a 100644
--- a/hplsql/src/test/results/local/lang.out.txt
+++ b/hplsql/src/test/results/local/lang.out.txt
@@ -7,19 +7,19 @@
-1.0
Ln:19 DECLARE abc int
Ln:20 DECLARE abc.abc int
-Ln:21 DECLARE abc . abc1 int
+Ln:21 DECLARE abc.abc1 int
Ln:22 DECLARE "abc" int
Ln:23 DECLARE "abc".abc int
Ln:24 DECLARE "abc"."abc" int
-Ln:25 DECLARE "abc" . "abc1" int
+Ln:25 DECLARE "abc"."abc1" int
Ln:26 DECLARE [abc] int
Ln:27 DECLARE [abc].abc int
Ln:28 DECLARE [abc].[abc] int
-Ln:29 DECLARE [abc] . [abc1] int
+Ln:29 DECLARE [abc].[abc1] int
Ln:30 DECLARE `abc` int
Ln:31 DECLARE `abc`.abc int
Ln:32 DECLARE `abc`.`abc` int
-Ln:33 DECLARE `abc` . `abc1` int
+Ln:33 DECLARE `abc`.`abc1` int
Ln:34 DECLARE :new.abc int
Ln:35 DECLARE @abc int
Ln:36 DECLARE _abc int
@@ -31,4 +31,4 @@ Ln:40 DECLARE abc_9 int
2
0
-2
-0
+0
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/create_table_mssql.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/create_table_mssql.out.txt b/hplsql/src/test/results/offline/create_table_mssql.out.txt
index 43b0aa7..29d03d6 100644
--- a/hplsql/src/test/results/offline/create_table_mssql.out.txt
+++ b/hplsql/src/test/results/offline/create_table_mssql.out.txt
@@ -1,24 +1,27 @@
Ln:1 CREATE TABLE
-Ln:1 CREATE TABLE mssql_t1 (d1 TIMESTAMP,
-nc1 STRING,
-n1 DECIMAL(3,0),
-n2 DECIMAL(3),
-n3 DECIMAL,
-v1 STRING,
-nv1 STRING,
-nv2 STRING
+Ln:1 CREATE TABLE mssql_t1 (
+ d1 TIMESTAMP,
+ nc1 STRING,
+ n1 DECIMAL(3,0),
+ n2 DECIMAL(3),
+ n3 DECIMAL,
+ v1 STRING,
+ nv1 STRING,
+ nv2 STRING
)
Ln:12 CREATE TABLE
-Ln:12 CREATE TABLE `mssql_t2` (`i1` INT,
-`v1` VARCHAR(350),
-`v2` STRING,
-`b1` TINYINT,
-`d1` TIMESTAMP
+Ln:12 CREATE TABLE `mssql_t2`(
+ `i1` INT,
+ `v1` VARCHAR(350),
+ `v2` STRING,
+ `b1` TINYINT,
+ `d1` TIMESTAMP
)
Ln:31 CREATE TABLE
-Ln:31 CREATE TABLE `default`.`mssql_t3` (`v1` VARCHAR(50),
-`s2` SMALLINT,
-`sd1` TIMESTAMP,
-`i1` INT,
-`v2` VARCHAR(100)
+Ln:31 CREATE TABLE `default`.`mssql_t3`(
+ `v1` VARCHAR(50),
+ `s2` SMALLINT,
+ `sd1` TIMESTAMP,
+ `i1` INT,
+ `v2` VARCHAR(100)
)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/create_table_mssql2.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/create_table_mssql2.out.txt b/hplsql/src/test/results/offline/create_table_mssql2.out.txt
index a765c4a..8341411 100644
--- a/hplsql/src/test/results/offline/create_table_mssql2.out.txt
+++ b/hplsql/src/test/results/offline/create_table_mssql2.out.txt
@@ -1,10 +1,11 @@
Ln:1 USE
Ln:1 SQL statement: USE `mic.gr`
Ln:14 CREATE TABLE
-Ln:14 CREATE TABLE `downloads` (`id` int,
-`fileName` char(255),
-`fileType` char(10),
-`downloads` int,
-`fromDate` char(40),
-`untilDate` char(40)
+Ln:14 CREATE TABLE `downloads`(
+ `id` int,
+ `fileName` char(255),
+ `fileType` char(10),
+ `downloads` int,
+ `fromDate` char(40),
+ `untilDate` char(40)
)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/create_table_mysql.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/create_table_mysql.out.txt b/hplsql/src/test/results/offline/create_table_mysql.out.txt
index b835135..d07796f 100644
--- a/hplsql/src/test/results/offline/create_table_mysql.out.txt
+++ b/hplsql/src/test/results/offline/create_table_mysql.out.txt
@@ -1,4 +1,5 @@
Ln:1 CREATE TABLE
-Ln:1 CREATE TABLE `users` (`id` int,
-`name` STRING
+Ln:1 CREATE TABLE IF NOT EXISTS `users` (
+ `id` int,
+ `name` STRING
) COMMENT 'users table'
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/create_table_ora.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/create_table_ora.out.txt b/hplsql/src/test/results/offline/create_table_ora.out.txt
index cf30c0f..972e00a 100644
--- a/hplsql/src/test/results/offline/create_table_ora.out.txt
+++ b/hplsql/src/test/results/offline/create_table_ora.out.txt
@@ -1,42 +1,49 @@
Ln:1 CREATE TABLE
-Ln:1 CREATE TABLE ora_t1 (n1 DECIMAL(3,0),
-v1 STRING
+Ln:1 CREATE TABLE ora_t1 (
+ n1 DECIMAL(3,0),
+ v1 STRING
)
Ln:6 CREATE TABLE
-Ln:6 CREATE TABLE `USER`.`EMP` (`EMPNO` DECIMAL(4,0),
-`ENAME` STRING,
-`JOB` STRING,
-`MGR` DECIMAL(4,0),
-`HIREDATE` DATE,
-`SAL` DECIMAL(7,2),
-`COMM` DECIMAL(7,2),
-`DEPTNO` DECIMAL(2,0)
-)
+Ln:6 CREATE TABLE `USER`.`EMP`
+ ( `EMPNO` DECIMAL(4,0),
+ `ENAME` STRING,
+ `JOB` STRING,
+ `MGR` DECIMAL(4,0),
+ `HIREDATE` DATE,
+ `SAL` DECIMAL(7,2),
+ `COMM` DECIMAL(7,2),
+ `DEPTNO` DECIMAL(2,0)
+ )
Ln:21 CREATE TABLE
-Ln:21 CREATE TABLE language (id DECIMAL(7),
-cd CHAR(2),
-description STRING
+Ln:21 CREATE TABLE language (
+ id DECIMAL(7),
+ cd CHAR(2),
+ description STRING
)
Ln:26 CREATE TABLE
-Ln:26 CREATE TABLE author (id DECIMAL(7),
-first_name STRING,
-last_name STRING,
-date_of_birth DATE,
-year_of_birth DECIMAL(7),
-distinguished DECIMAL(1)
+Ln:26 CREATE TABLE author (
+ id DECIMAL(7),
+ first_name STRING,
+ last_name STRING,
+ date_of_birth DATE,
+ year_of_birth DECIMAL(7),
+ distinguished DECIMAL(1)
)
Ln:34 CREATE TABLE
-Ln:34 CREATE TABLE book (id DECIMAL(7),
-author_id DECIMAL(7),
-title STRING,
-published_in DECIMAL(7),
-language_id DECIMAL(7)
+Ln:34 CREATE TABLE book (
+ id DECIMAL(7),
+ author_id DECIMAL(7),
+ title STRING,
+ published_in DECIMAL(7),
+ language_id DECIMAL(7)
)
Ln:43 CREATE TABLE
-Ln:43 CREATE TABLE book_store (name STRING
+Ln:43 CREATE TABLE book_store (
+ name STRING
)
Ln:46 CREATE TABLE
-Ln:46 CREATE TABLE book_to_book_store (name STRING,
-book_id INTEGER,
-stock INTEGER
+Ln:46 CREATE TABLE book_to_book_store (
+ name STRING,
+ book_id INTEGER,
+ stock INTEGER
)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/create_table_ora2.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/create_table_ora2.out.txt b/hplsql/src/test/results/offline/create_table_ora2.out.txt
index 5d4e107..03f54e8 100644
--- a/hplsql/src/test/results/offline/create_table_ora2.out.txt
+++ b/hplsql/src/test/results/offline/create_table_ora2.out.txt
@@ -1,5 +1,6 @@
Ln:1 CREATE TABLE
-Ln:1 CREATE TABLE `default`.`AUDIT_LOGS` (`RUN_ID` STRING,
-`FILE_NAME` STRING,
-`RUN_DATE` DATE
-)
\ No newline at end of file
+Ln:1 CREATE TABLE `default`.`AUDIT_LOGS`
+ ( `RUN_ID` STRING,
+ `FILE_NAME` STRING,
+ `RUN_DATE` DATE
+ )
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/create_table_pg.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/create_table_pg.out.txt b/hplsql/src/test/results/offline/create_table_pg.out.txt
index cad5488..095eb12 100644
--- a/hplsql/src/test/results/offline/create_table_pg.out.txt
+++ b/hplsql/src/test/results/offline/create_table_pg.out.txt
@@ -1,5 +1,6 @@
Ln:1 CREATE TABLE
-Ln:1 create table i1 (c1 SMALLINT,
-c2 INT,
-c3 BIGINT
+Ln:1 create table i1 (
+ c1 SMALLINT,
+ c2 INT,
+ c3 BIGINT
)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/create_table_td.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/create_table_td.out.txt b/hplsql/src/test/results/offline/create_table_td.out.txt
new file mode 100644
index 0000000..9b9d561
--- /dev/null
+++ b/hplsql/src/test/results/offline/create_table_td.out.txt
@@ -0,0 +1,31 @@
+Ln:1 CREATE TABLE
+Ln:1 CREATE TABLE tab
+ (
+ SOURCE_ID INT,
+ RUN_ID INT,
+ STATUS CHAR,
+ LOAD_START timestamp(0),
+ LOAD_END timestamp(0)
+ )
+Ln:10 CREATE TABLE
+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'
+ GROUP BY
+ 1,3,4,5
+)
+Ln:28 CREATE LOCAL TEMPORARY TABLE
+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'
+ GROUP BY
+ 1,3,4,5
+)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/delete_all.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/delete_all.out.txt b/hplsql/src/test/results/offline/delete_all.out.txt
new file mode 100644
index 0000000..0cecc95
--- /dev/null
+++ b/hplsql/src/test/results/offline/delete_all.out.txt
@@ -0,0 +1,2 @@
+Ln:1 DELETE
+Ln:1 TRUNCATE TABLE TEST1_DB.WK_WRK
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/select.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/select.out.txt b/hplsql/src/test/results/offline/select.out.txt
new file mode 100644
index 0000000..529f0b5
--- /dev/null
+++ b/hplsql/src/test/results/offline/select.out.txt
@@ -0,0 +1,34 @@
+Ln:1 SELECT
+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 *
+ FROM a
+ where not exists
+ (
+ select * from b
+ )
+Ln:3 Not executed - offline mode set
+Ln:10 SELECT
+Ln:10 SELECT
+ *
+ 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
+ CAST( LOAD_START AS DATE)
+ 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
+ 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 LOC.LOCATION_END_DT = DATE '9999-12-31'
+Ln:31 Not executed - offline mode set
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/select_db2.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/select_db2.out.txt b/hplsql/src/test/results/offline/select_db2.out.txt
index 1d64e8a..bb5b455 100644
--- a/hplsql/src/test/results/offline/select_db2.out.txt
+++ b/hplsql/src/test/results/offline/select_db2.out.txt
@@ -2,5 +2,6 @@ Ln:1 SELECT
Ln:1 select coalesce(max(info_id) + 1, 0) from sproc_info
Ln:1 Not executed - offline mode set
Ln:3 SELECT
-Ln:3 select cd, cd + inc days, cd - inc days + coalesce(inc, 0) days from (select date '2015-09-02' as cd, 3 as inc from sysibm.sysdummy1)
+Ln:3 select cd, cd + inc days, cd - inc days + coalesce(inc, 0) days
+from (select date '2015-09-02' as cd, 3 as inc from sysibm.sysdummy1)
Ln:3 Not executed - offline mode set
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/2d33d091/hplsql/src/test/results/offline/select_teradata.out.txt
----------------------------------------------------------------------
diff --git a/hplsql/src/test/results/offline/select_teradata.out.txt b/hplsql/src/test/results/offline/select_teradata.out.txt
new file mode 100644
index 0000000..34ab433
--- /dev/null
+++ b/hplsql/src/test/results/offline/select_teradata.out.txt
@@ -0,0 +1,10 @@
+Ln:1 SELECT
+Ln:1 SELECT branch_code, branch_no, c_no, cd_type
+FROM EMPLOYEE
+ WHERE S_CODE = 'C' AND (branch_no) NOT IN (
+ SELECT branch_code
+ FROM DEPARTMENT
+ WHERE branch_code = 'ABC'
+ )
+QUALIFY ROW_NUMBER() OVER (PARTITION BY c_no ORDER BY cd_type) = 1
+Ln:1 Not executed - offline mode set
\ No newline at end of file