This is an automated email from the ASF dual-hosted git repository. yiguolei pushed a commit to branch branch-2.1 in repository https://gitbox.apache.org/repos/asf/doris.git
commit ee687a43fddfbae880687c2c76f51e0a09ed6348 Author: Xinyi Zou <[email protected]> AuthorDate: Fri Apr 19 18:12:56 2024 +0800 [fix](plsql) Fix regression test for routine select (#33860) fix #33608, more comprehensive test --- .../data/plsql_p0/test_plsql_routine.out | 35 +++++++++- .../suites/plsql_p0/test_plsql_routine.groovy | 77 ++++++++++------------ 2 files changed, 66 insertions(+), 46 deletions(-) diff --git a/regression-test/data/plsql_p0/test_plsql_routine.out b/regression-test/data/plsql_p0/test_plsql_routine.out index 316a4c1ac78..f894bf81ed6 100644 --- a/regression-test/data/plsql_p0/test_plsql_routine.out +++ b/regression-test/data/plsql_p0/test_plsql_routine.out @@ -1,7 +1,38 @@ -- This file is automatically generated. You should know what you did if you want to edit this -- !select -- -777 4 +3 -- !select -- -2 +TEST_PLSQL_ROUTINE1 0 plsql_routine TEST_PLSQL_ROUTINE1 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine1() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE2 0 plsql_routine TEST_PLSQL_ROUTINE2 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine2() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE3 0 plsql_routine TEST_PLSQL_ROUTINE3 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine3() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root + +-- !select -- +5 + +-- !select -- +TEST_PLSQL_ROUTINE1 0 plsql_routine TEST_PLSQL_ROUTINE1 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine1() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE2 0 plsql_routine TEST_PLSQL_ROUTINE2 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine2() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE3 0 plsql_routine TEST_PLSQL_ROUTINE3 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine3() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE4 0 plsql_routine TEST_PLSQL_ROUTINE4 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine4() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE5 0 plsql_routine TEST_PLSQL_ROUTINE5 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine5() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root + +-- !select -- +4 + +-- !select -- +TEST_PLSQL_ROUTINE2 0 plsql_routine TEST_PLSQL_ROUTINE2 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine2() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE3 0 plsql_routine TEST_PLSQL_ROUTINE3 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine3() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE4 0 plsql_routine TEST_PLSQL_ROUTINE4 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine4() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE5 0 plsql_routine TEST_PLSQL_ROUTINE5 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine5() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root + +-- !select -- +5 + +-- !select -- +TEST_PLSQL_ROUTINE1 0 plsql_routine TEST_PLSQL_ROUTINE1 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine1() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE2 0 plsql_routine TEST_PLSQL_ROUTINE2 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine2() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE3 0 plsql_routine TEST_PLSQL_ROUTINE3 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine3() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE4 0 plsql_routine TEST_PLSQL_ROUTINE4 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine4() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root +TEST_PLSQL_ROUTINE5 0 plsql_routine TEST_PLSQL_ROUTINE5 PROCEDURE CREATE OR REPLACE PROCEDURE test_plsql_routine5() BEGIN DECLARE a int = 1; print a; END NULL NULL SQL NULL DEFINER root diff --git a/regression-test/suites/plsql_p0/test_plsql_routine.groovy b/regression-test/suites/plsql_p0/test_plsql_routine.groovy index 2cea9a4dcca..2f349490480 100644 --- a/regression-test/suites/plsql_p0/test_plsql_routine.groovy +++ b/regression-test/suites/plsql_p0/test_plsql_routine.groovy @@ -24,56 +24,45 @@ // PL-SQL uses Print to print variable values in an unformatted format, and JDBC cannot easily obtain them. Real results. suite("test_plsql_routine") { def dbName = "plsql_routine" + sql "drop database if exists ${dbName}" + sql """DROP PROCEDURE test_plsql_routine1""" + sql """DROP PROC test_plsql_routine2""" + sql """DROP PROC test_plsql_routine3""" + sql """DROP PROC test_plsql_routine4""" + sql """DROP PROC test_plsql_routine5""" + sql "CREATE DATABASE IF NOT EXISTS ${dbName}" sql "use ${dbName}" - def tableName = "plsql_tbl_4" - 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 routine_insert(IN id int, IN name STRING) - BEGIN - INSERT INTO ${tableName} VALUES(id, name); - END; - """ - sql """call routine_insert(111, "plsql111")""" - sql """call routine_insert(222, "plsql222")""" - sql """call routine_insert(333, "plsql333")""" - sql """call routine_insert(111, "plsql333")""" - qt_select "select sum(id), count(1) from ${tableName}" + def procedure_body = "BEGIN DECLARE a int = 1; print a; END;" + def select_routines_count = """select count(*) from information_schema.routines where routine_schema=\"${dbName}\";""" + def select_routines_fixed_value_column = """select SPECIFIC_NAME,ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE + ,DTD_IDENTIFIER,ROUTINE_BODY,ROUTINE_DEFINITION,EXTERNAL_NAME,EXTERNAL_NAME,EXTERNAL_LANGUAGE,PARAMETER_STYLE + ,IS_DETERMINISTIC,SQL_DATA_ACCESS,SQL_PATH,SECURITY_TYPE,SQL_MODE,ROUTINE_COMMENT,DEFINER,CHARACTER_SET_CLIENT + ,COLLATION_CONNECTION,DATABASE_COLLATION from information_schema.routines where routine_schema=\"${dbName}\" + order by SPECIFIC_NAME;""" + + sql """ CREATE OR REPLACE PROCEDURE test_plsql_routine1() ${procedure_body} """ + sql """ CREATE OR REPLACE PROCEDURE test_plsql_routine2() ${procedure_body} """ + sql """ CREATE OR REPLACE PROCEDURE test_plsql_routine3() ${procedure_body} """ + + sql """select * from information_schema.routines where routine_schema=\"${dbName}\";""" + qt_select """${select_routines_count}""" + qt_select """${select_routines_fixed_value_column}""" + + sql """ CREATE OR REPLACE PROCEDURE test_plsql_routine4() ${procedure_body} """ + sql """ CREATE OR REPLACE PROCEDURE test_plsql_routine5() ${procedure_body} """ - sql """ - CREATE OR REPLACE PROCEDURE routine_cursor_select(IN id_arg INT, IN name_arg STRING) - BEGIN - DECLARE a INT; - DECLARE b, c STRING; + qt_select """${select_routines_count}""" + qt_select """${select_routines_fixed_value_column}""" - DECLARE cur1 CURSOR FOR select * from ${tableName} where id=id_arg limit 5; - OPEN cur1; - read_loop: LOOP - FETCH cur1 INTO a, b; - IF(SQLCODE != 0) THEN - LEAVE read_loop; - END IF; - print a, b; - END LOOP; + sql """DROP PROCEDURE test_plsql_routine1""" - CLOSE cur1; + qt_select """${select_routines_count}""" + qt_select """${select_routines_fixed_value_column}""" - END; - """ + sql """ CREATE OR REPLACE PROCEDURE test_plsql_routine1() ${procedure_body} """ - qt_select "select count(*) from information_schema.routines where routine_schema=\"${dbName}\";" - sql """select * from information_schema.routines;""" - sql """call routine_cursor_select(111, "plsql111")""" - sql """call routine_cursor_select(111, "plsql333")""" - sql """SHOW CREATE PROCEDURE routine_cursor_select;""" - // TODO call show command before drop - sql """DROP PROCEDURE routine_cursor_select""" - sql """DROP PROC routine_insert""" - sql "DROP DATABASE ${dbName}" - // TODO call show command after drop + qt_select """${select_routines_count}""" + qt_select """${select_routines_fixed_value_column}""" } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
