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 <[email protected]> Authored: Wed May 4 03:13:18 2016 -0700 Committer: Dmitry Tolpeko <[email protected]> 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
