This is an automated email from the ASF dual-hosted git repository. yiguolei pushed a commit to branch 2.1-tmp in repository https://gitbox.apache.org/repos/asf/doris.git
commit b696909775edd63302eb9cfc72521833ec1c6b9c Author: Xinyi Zou <[email protected]> AuthorDate: Wed Apr 3 16:35:17 2024 +0800 [fix](plsql) Fix plsql variable initialization (#33186) --- .../antlr4/org/apache/doris/nereids/PLLexer.g4 | 4 - .../antlr4/org/apache/doris/nereids/PLParser.g4 | 2 +- .../src/main/java/org/apache/doris/plsql/Exec.java | 6 -- .../doris/plsql/executor/DorisRowResult.java | 2 +- .../data/plsql_p0/test_plsql_variable.out | 13 +++ .../suites/plsql_p0/test_plsql_loop_cursor.groovy | 7 ++ .../suites/plsql_p0/test_plsql_variable.groovy | 110 +++++++++++++++++++++ 7 files changed, 132 insertions(+), 12 deletions(-) diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/PLLexer.g4 b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/PLLexer.g4 index 9a0c060aa73..8ee19ae851b 100644 --- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/PLLexer.g4 +++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/PLLexer.g4 @@ -208,7 +208,3 @@ SYSDATE: 'SYSDATE'; VARIANCE: 'VARIANCE'; DOT2: '..'; - -LABEL_PL - : ([a-zA-Z] | DIGIT | '_')* ':' - ; diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/PLParser.g4 b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/PLParser.g4 index b052818c29e..f8dc6039145 100644 --- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/PLParser.g4 +++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/PLParser.g4 @@ -522,7 +522,7 @@ for_range_stmt : // FOR (Integer range) statement ; label_stmt : - LABEL_PL + IDENTIFIER COLON | LT LT IDENTIFIER GT GT ; diff --git a/fe/fe-core/src/main/java/org/apache/doris/plsql/Exec.java b/fe/fe-core/src/main/java/org/apache/doris/plsql/Exec.java index 3caf8181ff0..87a108299d9 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/plsql/Exec.java +++ b/fe/fe-core/src/main/java/org/apache/doris/plsql/Exec.java @@ -2085,12 +2085,6 @@ public class Exec extends org.apache.doris.nereids.PLParserBaseVisitor<Integer> public Integer visitLabel_stmt(Label_stmtContext ctx) { if (ctx.IDENTIFIER() != null) { exec.labels.push(ctx.IDENTIFIER().toString()); - } else { - String label = ctx.LABEL_PL().getText(); - if (label.endsWith(":")) { - label = label.substring(0, label.length() - 1); - } - exec.labels.push(label); } return 0; } diff --git a/fe/fe-core/src/main/java/org/apache/doris/plsql/executor/DorisRowResult.java b/fe/fe-core/src/main/java/org/apache/doris/plsql/executor/DorisRowResult.java index f087a7e17cc..e286485fd42 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/plsql/executor/DorisRowResult.java +++ b/fe/fe-core/src/main/java/org/apache/doris/plsql/executor/DorisRowResult.java @@ -59,7 +59,7 @@ public class DorisRowResult implements RowResult { @Override public boolean next() { - if (eof) { + if (eof || coord == null) { return false; } try { diff --git a/regression-test/data/plsql_p0/test_plsql_variable.out b/regression-test/data/plsql_p0/test_plsql_variable.out new file mode 100644 index 00000000000..2fc878d6fcf --- /dev/null +++ b/regression-test/data/plsql_p0/test_plsql_variable.out @@ -0,0 +1,13 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !select -- +0 + +-- !select -- +777 4 + +-- !select -- +0 + +-- !select -- +0 + diff --git a/regression-test/suites/plsql_p0/test_plsql_loop_cursor.groovy b/regression-test/suites/plsql_p0/test_plsql_loop_cursor.groovy index 72297888aaa..85cd7f1858a 100644 --- a/regression-test/suites/plsql_p0/test_plsql_loop_cursor.groovy +++ b/regression-test/suites/plsql_p0/test_plsql_loop_cursor.groovy @@ -15,6 +15,13 @@ // specific language governing permissions and limitations // under the License. +// Add PL-SQL regression test notice: +// 1. JDBC does not support the execution of stored procedures that return results. You can only Into the execution +// results into a variable or write them into a table, because when multiple result sets are returned, JDBC needs +// to use the prepareCall statement to execute, otherwise the Statemnt of the returned result executes Finalize. +// Send EOF Packet will report an error; +// 2. The format of the result returned by Doris Statement is xxxx\n, xxxx\n, 2 rows affected (0.03 sec). +// PL-SQL uses Print to print variable values in an unformatted format, and JDBC cannot easily obtain them. Real results. suite("test_plsql_loop_cursor") { def tableName = "plsql_tbl" sql "DROP TABLE IF EXISTS ${tableName}" diff --git a/regression-test/suites/plsql_p0/test_plsql_variable.groovy b/regression-test/suites/plsql_p0/test_plsql_variable.groovy new file mode 100644 index 00000000000..8738524f08c --- /dev/null +++ b/regression-test/suites/plsql_p0/test_plsql_variable.groovy @@ -0,0 +1,110 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +suite("test_plsql_variable") { + def tableName = "plsql_variable" + sql """ + CREATE OR REPLACE PROCEDURE plsql_variable1() + BEGIN + DECLARE a STRING; + a:="hello world!"; + PRINT a; + PRINT upper(a); + + DECLARE b = 10; + PRINT b; + b = length(a); + PRINT b; + + DECLARE c = a; + PRINT c; + c:=b; + PRINT c; + c = "hello kudo!" + PRINT c; + + DECLARE d STRING; + PRINT d; + d = NOW(); + PRINT d; + END; + """ + qt_select """call plsql_variable1()""" + + sql "DROP TABLE IF EXISTS ${tableName}" + sql """ + create table ${tableName} (id int, name varchar(20)) DUPLICATE key(`id`) distributed by hash (`id`) buckets 4 + properties ("replication_num"="1"); + """ + + sql """ + CREATE OR REPLACE PROCEDURE procedure_insert(IN id int, IN name STRING) + BEGIN + INSERT INTO ${tableName} VALUES(id, name); + END; + """ + sql """call procedure_insert(111, "plsql111")""" + sql """call procedure_insert(222, "plsql222")""" + sql """call procedure_insert(333, "plsql333")""" + sql """call procedure_insert(111, "plsql333")""" + qt_select "select sum(id), count(1) from ${tableName}" + + sql """ + CREATE OR REPLACE PROCEDURE plsql_variable2() + BEGIN + DECLARE a int = 2; + DECLARE b string = " plsql111 "; + print a; + print b; + print trim(b); + + DECLARE c string; + select name into c from ${tableName} where 2=a and name=trim(b); + print c; + + DECLARE d int; + select count(1) into d from ${tableName} where 2=a; + print d; + END; + """ + qt_select """call plsql_variable2()""" + + // TODO, currently, variable take priority over column, Oracle column priority. + sql """ + CREATE OR REPLACE PROCEDURE plsql_variable3() + BEGIN + DECLARE a int = 999; + print a; + + DECLARE b int; + select id into b from ${tableName} where 999=a limit 1; + print b; + + DECLARE id int = 999; + print id; + + DECLARE c string; + select id into c from ${tableName} where 999=id limit 1; + print c; + + DECLARE d string; + select count(1) into d from ${tableName} where 999=id; + print d; + END; + """ + qt_select """call plsql_variable3()""" +} --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
