From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- .../suite/compat/oracle/t/anonymous_derived.test | 56 + .../oracle/t/binlog_ptr_mysqlbinlog-master.opt | 1 + .../compat/oracle/t/binlog_ptr_mysqlbinlog.test | 117 + .../suite/compat/oracle/t/binlog_stm_ps.test | 57 + .../suite/compat/oracle/t/binlog_stm_sp.test | 219 ++ .../compat/oracle/t/binlog_stm_sp_package.test | 158 + .../suite/compat/oracle/t/column_compression.test | 85 + .../oracle/t/custom_aggregate_functions.test | 170 ++ .../compat/oracle/t/empty_string_literal.test | 8 + mysql-test/suite/compat/oracle/t/events.test | 37 + mysql-test/suite/compat/oracle/t/exception.test | 457 +++ .../suite/compat/oracle/t/func_add_months.test | 38 + mysql-test/suite/compat/oracle/t/func_case.test | 9 + mysql-test/suite/compat/oracle/t/func_concat.test | 184 ++ mysql-test/suite/compat/oracle/t/func_decode.test | 98 + mysql-test/suite/compat/oracle/t/func_length.test | 18 + mysql-test/suite/compat/oracle/t/func_misc.test | 346 +++ mysql-test/suite/compat/oracle/t/func_pad.test | 31 + mysql-test/suite/compat/oracle/t/func_replace.test | 22 + mysql-test/suite/compat/oracle/t/func_substr.test | 47 + mysql-test/suite/compat/oracle/t/func_time.test | 25 + mysql-test/suite/compat/oracle/t/func_to_char.test | 236 ++ mysql-test/suite/compat/oracle/t/func_trim.test | 77 + mysql-test/suite/compat/oracle/t/gis-debug.test | 31 + mysql-test/suite/compat/oracle/t/gis.test | 76 + .../oracle/t/information_schema_parameters.test | 127 + mysql-test/suite/compat/oracle/t/keywords.test | 29 + mysql-test/suite/compat/oracle/t/minus.test | 44 + mysql-test/suite/compat/oracle/t/misc.test | 38 + .../suite/compat/oracle/t/mysqldump_restore.test | 30 + mysql-test/suite/compat/oracle/t/parser.test | 727 +++++ mysql-test/suite/compat/oracle/t/plugin.test | 3 + mysql-test/suite/compat/oracle/t/ps.test | 290 ++ .../suite/compat/oracle/t/rpl_mariadb_date.test | 38 + .../suite/compat/oracle/t/rpl_sp_package.test | 134 + .../compat/oracle/t/rpl_sp_package_variables.test | 36 + mysql-test/suite/compat/oracle/t/sequence.test | 48 + .../compat/oracle/t/sp-anchor-row-type-table.test | 124 + mysql-test/suite/compat/oracle/t/sp-anonymous.test | 244 ++ .../suite/compat/oracle/t/sp-cache-invalidate.inc | 11 + mysql-test/suite/compat/oracle/t/sp-code.test | 1088 +++++++ .../suite/compat/oracle/t/sp-cursor-decl.test | 295 ++ .../suite/compat/oracle/t/sp-cursor-rowtype.test | 1597 ++++++++++ mysql-test/suite/compat/oracle/t/sp-cursor.test | 1044 +++++++ mysql-test/suite/compat/oracle/t/sp-expr.test | 165 ++ .../suite/compat/oracle/t/sp-goto-debug.test | 178 ++ mysql-test/suite/compat/oracle/t/sp-goto.test | 968 ++++++ mysql-test/suite/compat/oracle/t/sp-inout.test | 2501 ++++++++++++++++ .../suite/compat/oracle/t/sp-memory-leak.test | 35 + .../suite/compat/oracle/t/sp-package-code.test | 182 ++ .../oracle/t/sp-package-concurrent-dml-db.test | 6 + .../t/sp-package-concurrent-dml-package.test | 10 + .../t/sp-package-concurrent-dml-trigger.test | 6 + .../oracle/t/sp-package-concurrent-dml-view.test | 6 + .../compat/oracle/t/sp-package-concurrent-dml.inc | 107 + .../suite/compat/oracle/t/sp-package-i_s.test | 69 + .../suite/compat/oracle/t/sp-package-innodb.test | 66 + .../suite/compat/oracle/t/sp-package-mdl.test | 110 + .../compat/oracle/t/sp-package-mysqldump.test | 94 + .../suite/compat/oracle/t/sp-package-security.test | 332 +++ mysql-test/suite/compat/oracle/t/sp-package.test | 3092 ++++++++++++++++++++ mysql-test/suite/compat/oracle/t/sp-param.inc | 9 + mysql-test/suite/compat/oracle/t/sp-param.test | 363 +++ mysql-test/suite/compat/oracle/t/sp-row-vs-var.inc | 6 + mysql-test/suite/compat/oracle/t/sp-row.test | 2418 +++++++++++++++ mysql-test/suite/compat/oracle/t/sp-security.test | 345 +++ mysql-test/suite/compat/oracle/t/sp.test | 2430 +++++++++++++++ .../suite/compat/oracle/t/statement-expr.test | 86 + .../suite/compat/oracle/t/table_value_constr.test | 1287 ++++++++ mysql-test/suite/compat/oracle/t/trigger.test | 106 + mysql-test/suite/compat/oracle/t/truncate.test | 16 + mysql-test/suite/compat/oracle/t/type_blob.test | 17 + mysql-test/suite/compat/oracle/t/type_clob.test | 10 + mysql-test/suite/compat/oracle/t/type_date.test | 101 + mysql-test/suite/compat/oracle/t/type_number.test | 9 + mysql-test/suite/compat/oracle/t/type_raw.test | 10 + mysql-test/suite/compat/oracle/t/type_varchar.test | 9 + .../suite/compat/oracle/t/type_varchar2.test | 19 + .../suite/compat/oracle/t/update_innodb.test | 31 + mysql-test/suite/compat/oracle/t/variables.test | 38 + mysql-test/suite/compat/oracle/t/vcol.test | 16 + mysql-test/suite/compat/oracle/t/versioning.test | 23 + mysql-test/suite/compat/oracle/t/win.test | 22 + 83 files changed, 23848 insertions(+) create mode 100644 mysql-test/suite/compat/oracle/t/anonymous_derived.test create mode 100644 mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt create mode 100644 mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test create mode 100644 mysql-test/suite/compat/oracle/t/binlog_stm_ps.test create mode 100644 mysql-test/suite/compat/oracle/t/binlog_stm_sp.test create mode 100644 mysql-test/suite/compat/oracle/t/binlog_stm_sp_package.test create mode 100644 mysql-test/suite/compat/oracle/t/column_compression.test create mode 100644 mysql-test/suite/compat/oracle/t/custom_aggregate_functions.test create mode 100644 mysql-test/suite/compat/oracle/t/empty_string_literal.test create mode 100644 mysql-test/suite/compat/oracle/t/events.test create mode 100644 mysql-test/suite/compat/oracle/t/exception.test create mode 100644 mysql-test/suite/compat/oracle/t/func_add_months.test create mode 100644 mysql-test/suite/compat/oracle/t/func_case.test create mode 100644 mysql-test/suite/compat/oracle/t/func_concat.test create mode 100644 mysql-test/suite/compat/oracle/t/func_decode.test create mode 100644 mysql-test/suite/compat/oracle/t/func_length.test create mode 100644 mysql-test/suite/compat/oracle/t/func_misc.test create mode 100644 mysql-test/suite/compat/oracle/t/func_pad.test create mode 100644 mysql-test/suite/compat/oracle/t/func_replace.test create mode 100644 mysql-test/suite/compat/oracle/t/func_substr.test create mode 100644 mysql-test/suite/compat/oracle/t/func_time.test create mode 100644 mysql-test/suite/compat/oracle/t/func_to_char.test create mode 100644 mysql-test/suite/compat/oracle/t/func_trim.test create mode 100644 mysql-test/suite/compat/oracle/t/gis-debug.test create mode 100644 mysql-test/suite/compat/oracle/t/gis.test create mode 100644 mysql-test/suite/compat/oracle/t/information_schema_parameters.test create mode 100644 mysql-test/suite/compat/oracle/t/keywords.test create mode 100644 mysql-test/suite/compat/oracle/t/minus.test create mode 100644 mysql-test/suite/compat/oracle/t/misc.test create mode 100644 mysql-test/suite/compat/oracle/t/mysqldump_restore.test create mode 100644 mysql-test/suite/compat/oracle/t/parser.test create mode 100644 mysql-test/suite/compat/oracle/t/plugin.test create mode 100644 mysql-test/suite/compat/oracle/t/ps.test create mode 100644 mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test create mode 100644 mysql-test/suite/compat/oracle/t/rpl_sp_package.test create mode 100644 mysql-test/suite/compat/oracle/t/rpl_sp_package_variables.test create mode 100644 mysql-test/suite/compat/oracle/t/sequence.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-anchor-row-type-table.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-anonymous.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-cache-invalidate.inc create mode 100644 mysql-test/suite/compat/oracle/t/sp-code.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-cursor-decl.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-cursor.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-expr.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-goto-debug.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-goto.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-inout.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-memory-leak.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-code.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-db.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-package.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-trigger.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-view.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml.inc create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-i_s.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-innodb.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-mdl.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-mysqldump.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package-security.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-package.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-param.inc create mode 100644 mysql-test/suite/compat/oracle/t/sp-param.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-row-vs-var.inc create mode 100644 mysql-test/suite/compat/oracle/t/sp-row.test create mode 100644 mysql-test/suite/compat/oracle/t/sp-security.test create mode 100644 mysql-test/suite/compat/oracle/t/sp.test create mode 100644 mysql-test/suite/compat/oracle/t/statement-expr.test create mode 100644 mysql-test/suite/compat/oracle/t/table_value_constr.test create mode 100644 mysql-test/suite/compat/oracle/t/trigger.test create mode 100644 mysql-test/suite/compat/oracle/t/truncate.test create mode 100644 mysql-test/suite/compat/oracle/t/type_blob.test create mode 100644 mysql-test/suite/compat/oracle/t/type_clob.test create mode 100644 mysql-test/suite/compat/oracle/t/type_date.test create mode 100644 mysql-test/suite/compat/oracle/t/type_number.test create mode 100644 mysql-test/suite/compat/oracle/t/type_raw.test create mode 100644 mysql-test/suite/compat/oracle/t/type_varchar.test create mode 100644 mysql-test/suite/compat/oracle/t/type_varchar2.test create mode 100644 mysql-test/suite/compat/oracle/t/update_innodb.test create mode 100644 mysql-test/suite/compat/oracle/t/variables.test create mode 100644 mysql-test/suite/compat/oracle/t/vcol.test create mode 100644 mysql-test/suite/compat/oracle/t/versioning.test create mode 100644 mysql-test/suite/compat/oracle/t/win.test (limited to 'mysql-test/suite/compat/oracle/t') diff --git a/mysql-test/suite/compat/oracle/t/anonymous_derived.test b/mysql-test/suite/compat/oracle/t/anonymous_derived.test new file mode 100644 index 00000000..7a9ee216 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/anonymous_derived.test @@ -0,0 +1,56 @@ +--echo # +--echo # MDEV-19162: anonymous derived tables part +--echo # + +set @save_sql_mode=@@sql_mode; +set session sql_mode=ORACLE; + +--disable_ps_protocol +--enable_metadata +SELECT * FROM (SELECT 1 FROM DUAL), (SELECT 2 FROM DUAL); +--disable_metadata +--enable_ps_protocol +create table t1 (a int); +insert into t1 values (2),(3); +create table t2 (a int); +insert into t2 values (2),(3); +--disable_ps_protocol +--enable_metadata +select t1.a from t1, (select * from t2 where t2.a<= 2); +select t1.a, b from t1, (select a as b from t2 where t2.a<= 2); +select t1.a, b from t1, (select max(a) as b from t2); +--disable_metadata +--enable_ps_protocol +explain extended +select t1.a, b from t1, (select max(a) as b from t2); +--error ER_BAD_FIELD_ERROR +select * from (select tt.* from (select * from t1) as tt) where tt.a > 0; +select * from (select tt.* from (select * from t1) as tt) where a > 0; + +create view v1 as select t1.a, b from t1, (select max(a) as b from t2); + +select * from v1; + + +DELIMITER /; +create procedure p1 +as +begin + select t1.a, b from t1, (select max(a) as b from t2); +end/ +DELIMITER ;/ + +call p1; + +SET sql_mode=default; + +select * from v1; +show create view v1; + +call p1; +show create procedure p1; + +drop view v1; +drop procedure p1; +drop table t1,t2; +set session sql_mode=@save_sql_mode; diff --git a/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt new file mode 100644 index 00000000..8f0cc182 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog-master.opt @@ -0,0 +1 @@ +--flashback diff --git a/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test new file mode 100644 index 00000000..bda32af5 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/binlog_ptr_mysqlbinlog.test @@ -0,0 +1,117 @@ +# ==== Purpose ==== +# +# Test verifies that point in time recovery of binary log works when +# sql_mode='ORACLE'. +# +# BEGIN statement is printed in three places +# 1) "Gtid_log_event::print" +# 2) "Xid_log_event::print" if flashback is enabled +# 3) "Query_log_event::print" if flashback is enabled and engine is +# non-transacional. +# +# Test verifies all these cases. +# +# ==== References ==== +# +# MDEV-23108: Point in time recovery of binary log fails when sql_mode=ORACLE +# +--source include/have_log_bin.inc +--source include/have_innodb.inc + +let $MYSQLD_DATADIR= `select @@datadir`; +SET @@SQL_MODE = 'ORACLE'; + +--echo ########################################################################## +--echo # Test verifies Gtid_log_event/Xid_log_event specific print # +--echo ########################################################################## +CREATE TABLE tm (f INT) ENGINE=MYISAM; +INSERT INTO tm VALUES (10); + +CREATE TABLE t(f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); + +DELIMITER /; +CREATE OR REPLACE PROCEDURE simpleproc (param1 OUT INT) AS + BEGIN + SELECT COUNT(*) INTO param1 FROM t; + END; +/ +CREATE FUNCTION f1 RETURN INT +AS +BEGIN + RETURN 10; +END; +/ +DELIMITER ;/ + +FLUSH LOGS; +--echo ########################################################################## +--echo # Delete data from master so that it can be restored from binlog # +--echo ########################################################################## +DROP FUNCTION f1; +DROP PROCEDURE simpleproc; +DROP TABLE tm; +DROP TABLE t; + +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/test.sql +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/test.sql + +--echo ########################################################################## +--echo # Post recovery using mysqlbinlog # +--echo ########################################################################## +SHOW TABLES; +SELECT * FROM tm; +SELECT * FROM t; +--horizontal_results +SELECT f1(); +CALL simpleproc(@a); +SELECT @a; + +--echo "***** Clean Up *****" +DROP TABLE t,tm; +DROP PROCEDURE simpleproc; +DROP FUNCTION f1; +--remove_file $MYSQLTEST_VARDIR/tmp/test.sql +RESET MASTER; + +--echo ########################################################################## +--echo # Test verifies Gtid_log_event/Xid_log_event/Qery_log_event # +--echo # specific print along with flashback option # +--echo ########################################################################## +CREATE TABLE tm(f INT) ENGINE=MYISAM; +INSERT INTO tm VALUES (10); +INSERT INTO tm VALUES (20); +CREATE TABLE t(f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); +INSERT INTO t VALUES (20); +--echo ########################################################################## +--echo # Initial data # +--echo ########################################################################## +SELECT * FROM tm; +SELECT * FROM t; +FLUSH LOGS; +DELETE FROM tm WHERE f=20; +DELETE FROM t WHERE f=20; +FLUSH LOGS; + +--echo ########################################################################## +--echo # Data after deletion # +--echo ########################################################################## +SELECT * FROM tm; +SELECT * FROM t; +--exec $MYSQL_BINLOG --flashback $MYSQLD_DATADIR/master-bin.000002 > $MYSQLTEST_VARDIR/tmp/test.sql + +--let SEARCH_FILE=$MYSQLTEST_VARDIR/tmp/test.sql +--let SEARCH_PATTERN=START TRANSACTION +--source include/search_pattern_in_file.inc +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/test.sql + +--echo ########################################################################## +--echo # Data after recovery using flashback # +--echo ########################################################################## +SELECT * FROM tm; +SELECT * FROM t; + +--echo "***** Clean Up *****" +DROP TABLE t,tm; +--remove_file $MYSQLTEST_VARDIR/tmp/test.sql diff --git a/mysql-test/suite/compat/oracle/t/binlog_stm_ps.test b/mysql-test/suite/compat/oracle/t/binlog_stm_ps.test new file mode 100644 index 00000000..f305f611 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/binlog_stm_ps.test @@ -0,0 +1,57 @@ +--source include/not_embedded.inc +--source include/have_binlog_format_statement.inc + +--disable_query_log +reset master; # get rid of previous tests binlog +--enable_query_log + +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-10801 sql_mode: dynamic SQL placeholders +--echo # + +CREATE TABLE t1 (a INT, b INT); +SET @a=10, @b=20; +PREPARE stmt FROM 'INSERT INTO t1 VALUES (?,?)'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'INSERT INTO t1 VALUES (:a,:b)'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'INSERT INTO t1 VALUES (:aaa,:bbb)'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'INSERT INTO t1 VALUES (:"a",:"b")'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'INSERT INTO t1 VALUES (:"aaa",:"bbb")'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'INSERT INTO t1 VALUES (:1,:2)'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'INSERT INTO t1 VALUES (:222,:111)'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'INSERT INTO t1 VALUES (:0,:65535)'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'INSERT INTO t1 VALUES (:65535,:0)'; +EXECUTE stmt USING @a, @b; +SELECT * FROM t1; +--let $binlog_file = LAST +source include/show_binlog_events.inc; +DROP TABLE t1; + + +--echo # +--echo # MDEV-16095 Oracle-style placeholder inside GROUP BY..WITH ROLLUP breaks replication +--echo # + +FLUSH LOGS; +CREATE TABLE t1 (d DATE); +INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24'); +CREATE TABLE t2 (d DATE, c BIGINT); +DELIMITER $$; +BEGIN + EXECUTE IMMEDIATE 'INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, :param' USING 1; + EXECUTE IMMEDIATE 'INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, :param WITH ROLLUP' USING 1; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1,t2; +--let $binlog_file = LAST +source include/show_binlog_events.inc; diff --git a/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test b/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test new file mode 100644 index 00000000..e6f33cb1 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test @@ -0,0 +1,219 @@ +--source include/not_embedded.inc +--source include/have_binlog_format_statement.inc + +--disable_query_log +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +reset master; # get rid of previous tests binlog +--enable_query_log + + +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-10914 ROW data type for stored routine variables +--echo # + +CREATE TABLE t1 (a INT, b INT); +DELIMITER $$; +CREATE PROCEDURE p1 +AS + rec ROW(a INT,b INT); +BEGIN + rec.a:=100; + rec.b:=200; + INSERT INTO t1 VALUES (rec.a,rec.b); + INSERT INTO t1 VALUES (10, rec=ROW(100,200)); + INSERT INTO t1 VALUES (10, ROW(100,200)=rec); + INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200); + INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec; + rec.a:=NULL; + INSERT INTO t1 VALUES (11, rec=ROW(100,200)); + INSERT INTO t1 VALUES (11, rec=ROW(100,201)); + INSERT INTO t1 VALUES (11, ROW(100,200)=rec); + INSERT INTO t1 VALUES (11, ROW(100,201)=rec); + INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200); + INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec; + rec.b:=NULL; + INSERT INTO t1 VALUES (12, rec=ROW(100,200)); + INSERT INTO t1 VALUES (12, ROW(100,200)=rec); + INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200); + INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec; +END; +$$ +DELIMITER ;$$ +CALL p1(); +SELECT * FROM t1; +DROP TABLE t1; +DROP PROCEDURE p1; +--let $binlog_file = LAST +source include/show_binlog_events.inc; + + +--echo # +--echo # Testing ROW fields in LIMIT +--echo # + +FLUSH LOGS; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(10); +CREATE TABLE t2 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1() +AS + a INT:= 1; + rec ROW(a INT); +BEGIN + rec.a:= 1; + INSERT INTO t2 SELECT 1 FROM t1 LIMIT a; + INSERT INTO t2 SELECT 2 FROM t1 LIMIT rec.a; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1,t2; +DROP PROCEDURE p1; +--let $binlog_file = LAST +source include/show_binlog_events.inc; + + +--echo # +--echo # End of MDEV-10914 ROW data type for stored routine variables +--echo # + + +--echo # +--echo # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations +--echo # + +CREATE TABLE t1 (a INT, b INT); +DELIMITER $$; +CREATE PROCEDURE p1 +AS + rec t1%ROWTYPE; +BEGIN + rec.a:=100; + rec.b:=200; + SELECT rec=ROW(100,200) AS true1, ROW(100,200)=rec AS true2; + INSERT INTO t1 VALUES (rec.a,rec.b); + INSERT INTO t1 VALUES (10, rec=ROW(100,200)); + INSERT INTO t1 VALUES (10, ROW(100,200)=rec); + INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200); + INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec; + rec.a:=NULL; + INSERT INTO t1 VALUES (11, rec=ROW(100,200)); + INSERT INTO t1 VALUES (11, rec=ROW(100,201)); + INSERT INTO t1 VALUES (11, ROW(100,200)=rec); + INSERT INTO t1 VALUES (11, ROW(100,201)=rec); + INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200); + INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec; + rec.b:=NULL; + INSERT INTO t1 VALUES (12, rec=ROW(100,200)); + INSERT INTO t1 VALUES (12, ROW(100,200)=rec); + INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200); + INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec; +END; +$$ +DELIMITER ;$$ +CALL p1(); +SELECT * FROM t1; +DROP TABLE t1; +DROP PROCEDURE p1; +--let $binlog_file = LAST +source include/show_binlog_events.inc; + + +--echo # +--echo # MDEV-12291 Allow ROW variables as SELECT INTO targets +--echo # + +FLUSH LOGS; +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10, 'b10'); +CREATE TABLE t2 LIKE t1; +DELIMITER $$; +CREATE PROCEDURE p1 +AS + rec1 ROW(a INT, b VARCHAR(32)); +BEGIN + SELECT * INTO rec1 FROM t1; + INSERT INTO t2 VALUES (rec1.a, rec1.b); +END; +$$ +DELIMITER ;$$ +CALL p1(); +SELECT * FROM t1; +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; +--let $binlog_file = LAST +source include/show_binlog_events.inc; + + +FLUSH LOGS; +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10, 'b10'); +CREATE TABLE t2 LIKE t1; +DELIMITER $$; +CREATE PROCEDURE p1 +AS + rec1 t1%ROWTYPE; +BEGIN + SELECT * INTO rec1 FROM t1; + INSERT INTO t2 VALUES (rec1.a, rec1.b); +END; +$$ +DELIMITER ;$$ +CALL p1(); +SELECT * FROM t1; +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; +--let $binlog_file = LAST +source include/show_binlog_events.inc; + + +FLUSH LOGS; +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10, 'b10'); +CREATE TABLE t2 LIKE t1; +DELIMITER $$; +CREATE PROCEDURE p1 +AS + CURSOR cur1 IS SELECT * FROM t1; + rec1 cur1%ROWTYPE; +BEGIN + SELECT * INTO rec1 FROM t1; + INSERT INTO t2 VALUES (rec1.a, rec1.b); +END; +$$ +DELIMITER ;$$ +CALL p1(); +SELECT * FROM t1; +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; +--let $binlog_file = LAST +source include/show_binlog_events.inc; + + +--echo # +--echo # MDEV-16020 SP variables inside GROUP BY..WITH ROLLUP break replication +--echo # + +FLUSH LOGS; +CREATE TABLE t1 (d DATE); +INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24'); +CREATE TABLE t2 (d DATE, c BIGINT); +DELIMITER $$; +DECLARE + var INT; +BEGIN + INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var; + INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1,t2; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; diff --git a/mysql-test/suite/compat/oracle/t/binlog_stm_sp_package.test b/mysql-test/suite/compat/oracle/t/binlog_stm_sp_package.test new file mode 100644 index 00000000..577ff58d --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/binlog_stm_sp_package.test @@ -0,0 +1,158 @@ +--source include/not_embedded.inc +--source include/have_binlog_format_statement.inc + +--disable_query_log +reset master; # get rid of previous tests binlog +--enable_query_log + +SET sql_mode=ORACLE; + +DELIMITER $$; +CREATE PACKAGE p1 AS + PROCEDURE p1; + FUNCTION f1 RETURN INT; +END; +$$ +CREATE PACKAGE IF NOT EXISTS p1 AS + PROCEDURE p1; + FUNCTION f1 RETURN INT; +END; +$$ +CREATE PACKAGE BODY p1 AS + PROCEDURE p1 AS + BEGIN + NULL; + END; + FUNCTION f1 RETURN INT AS + BEGIN + RETURN 10; + END; +END; +$$ +DELIMITER ;$$ + +DROP PACKAGE BODY p1; +DROP PACKAGE p1; +DROP PACKAGE IF EXISTS p1; + +--echo # +--echo # Creating a package with a COMMENT clause +--echo # + +DELIMITER $$; +CREATE PACKAGE p1 COMMENT 'package-p1-comment' AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 COMMENT 'package-body-p1-comment' AS + PROCEDURE p1 AS + BEGIN + NULL; + END; +END; +$$ +DELIMITER ;$$ +DROP PACKAGE p1; + +--echo # +--echo # Creating a package with a different DEFINER +--echo # + +DELIMITER $$; +CREATE DEFINER=xxx@localhost PACKAGE p1 AS + PROCEDURE p1; +END; +$$ +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS + PROCEDURE p1 AS + BEGIN + NULL; + END; +END; +$$ +DELIMITER ;$$ +DROP PACKAGE p1; + + +--echo # +--echo # Creating a package with a different DEFINER, with SQL SECURITY INVOKER +--echo # + +DELIMITER $$; +CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS + PROCEDURE p1; +END; +$$ +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS + PROCEDURE p1 AS + BEGIN + NULL; + END; +END; +$$ +DELIMITER ;$$ +DROP PACKAGE p1; + + +--echo # +--echo # Creating a new package in a remote database +--echo # + +CREATE DATABASE test2; + +DELIMITER $$; +CREATE PACKAGE test2.test2 COMMENT 'package-test2-comment' AS + FUNCTION f1 RETURN INT; + PROCEDURE p1; +END +$$ +DELIMITER ;$$ + +DELIMITER $$; +CREATE PACKAGE BODY test2.test2 COMMENT 'package-body-test2-comment' AS + FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END; + PROCEDURE p1 AS BEGIN SELECT f1(); END; +END; +$$ +DELIMITER ;$$ + +DROP PACKAGE BODY test2.test2; +DROP PACKAGE test2.test2; +DROP DATABASE test2; + + +--echo # +--echo # MDEV-13139 Package-wide variables in CREATE PACKAGE +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PACKAGE p1 AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 AS + a INT:=0; + PROCEDURE p1 AS + BEGIN + INSERT INTO t1 VALUES (a); + a:=a+1; + END; +BEGIN + a:=10; +END; +$$ +DELIMITER ;$$ +CALL p1.p1(); +CALL p1.p1(); +SELECT * FROM t1; +--source sp-cache-invalidate.inc +CALL p1.p1(); +CALL p1.p1(); +SELECT * FROM t1; +DROP PACKAGE p1; +DROP TABLE t1; + + +--let $binlog_file = LAST +source include/show_binlog_events.inc; diff --git a/mysql-test/suite/compat/oracle/t/column_compression.test b/mysql-test/suite/compat/oracle/t/column_compression.test new file mode 100644 index 00000000..01d4977b --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/column_compression.test @@ -0,0 +1,85 @@ +--source include/have_innodb.inc +--source include/have_csv.inc +--source include/have_normal_bzip.inc + +SET sql_mode=ORACLE; + +SET column_compression_zlib_wrap=true; +CREATE TABLE t1 (a BLOB COMPRESSED); +INSERT INTO t1 VALUES (REPEAT('a',10000)); +SELECT DATA_LENGTH<100 AS c FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +DROP TABLE t1; + +--echo # +--echo # MDEV-17363 - Compressed columns cannot be restored from dump +--echo # + +--error ER_WRONG_FIELD_SPEC +CREATE TABLE t1(a INT NOT NULL COMPRESSED); +SHOW WARNINGS; + +CREATE TABLE t1( + a JSON COMPRESSED, + b VARCHAR(1000) COMPRESSED BINARY, + c NVARCHAR(1000) COMPRESSED BINARY, + d TINYTEXT COMPRESSED BINARY +); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # VARCHAR and TEXT variants +--echo # + +--let type=VARCHAR(10) +--source include/column_compression_syntax_varchar.inc + +--let type=VARCHAR2(10) +--source include/column_compression_syntax_varchar.inc + +--let type=TINYTEXT +--source include/column_compression_syntax_varchar.inc + +--let type=TEXT +--source include/column_compression_syntax_varchar.inc + +--let type=MEDIUMTEXT +--source include/column_compression_syntax_varchar.inc + +--let type=LONGTEXT +--source include/column_compression_syntax_varchar.inc + + +--echo # +--echo # VARBINARY and BLOB variables +--echo # + +--let type=VARCHAR(10) +--source include/column_compression_syntax_varbinary.inc + +--let type=TINYBLOB +--source include/column_compression_syntax_varbinary.inc + +--let type=BLOB +--source include/column_compression_syntax_varbinary.inc + +--let type=MEDIUMBLOB +--source include/column_compression_syntax_varbinary.inc + +--let type=LONGBLOB +--source include/column_compression_syntax_varbinary.inc + + +--echo # +--echo # NVARCHAR +--echo # + +CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED); +SHOW CREATE TABLE t1; +DROP TABLE t1; +--error ER_PARSE_ERROR +CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED BINARY COMPRESSED); +--error ER_PARSE_ERROR +CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED DEFAULT '' COMPRESSED); diff --git a/mysql-test/suite/compat/oracle/t/custom_aggregate_functions.test b/mysql-test/suite/compat/oracle/t/custom_aggregate_functions.test new file mode 100644 index 00000000..0affc4ef --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/custom_aggregate_functions.test @@ -0,0 +1,170 @@ +SET sql_mode=ORACLE; + +delimiter |; +--error ER_INVALID_AGGREGATE_FUNCTION +create aggregate function f1(x INT) return INT AS +begin + insert into t1(sal) values (x); + return x; +end| + +--error ER_NOT_AGGREGATE_FUNCTION +create function f1(x INT) return INT AS +begin + set x=5; + fetch group next row; +return x+1; +end | + +DELIMITER ;| + + +CREATE TABLE marks(stud_id INT, grade_count INT); +INSERT INTO marks VALUES (1,6), (2,4), (3,7), (4,5), (5,8); +SELECT * FROM marks; + +--echo # Using PL/SQL syntax: EXCEPTION WHEN NO_DATA_FOUND + +DELIMITER //; +CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURN INT AS + count_students INT DEFAULT 0; +BEGIN + LOOP + FETCH GROUP NEXT ROW; + IF x THEN + count_students:= count_students + 1; + END IF; + END LOOP; +EXCEPTION + WHEN NO_DATA_FOUND THEN + RETURN count_students; +END aggregate_count // +DELIMITER ;// +SELECT aggregate_count(stud_id) FROM marks; +DROP FUNCTION IF EXISTS aggregate_count; + + +--echo # Using SQL/PSM systax: CONTINUE HANDLER + +DELIMITER //; +CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURN INT AS + count_students INT DEFAULT 0; + CONTINUE HANDLER FOR NOT FOUND RETURN count_students; +BEGIN + LOOP + FETCH GROUP NEXT ROW; + IF x THEN + SET count_students= count_students + 1; + END IF; + END LOOP; +END // +DELIMITER ;// +SELECT aggregate_count(stud_id) FROM marks; +DROP FUNCTION IF EXISTS aggregate_count; + + +DROP TABLE marks; + + +--echo # +--echo # MDEV-18813 PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW +--echo # + + +DELIMITER $$; +--error ER_NOT_AGGREGATE_FUNCTION +CREATE PROCEDURE p1 AS +BEGIN + FETCH GROUP NEXT ROW; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_NOT_AGGREGATE_FUNCTION +BEGIN NOT ATOMIC + FETCH GROUP NEXT ROW; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_NOT_AGGREGATE_FUNCTION +CREATE DEFINER=root@localhost FUNCTION f1 RETURN INT AS +BEGIN + FETCH GROUP NEXT ROW; + RETURN 0; +END; +$$ +DELIMITER ;$$ + + +CREATE TABLE t1 (a INT); +--error ER_NOT_AGGREGATE_FUNCTION +CREATE TRIGGER tr1 + AFTER INSERT ON t1 FOR EACH ROW + FETCH GROUP NEXT ROW; +DROP TABLE t1; + + +--error ER_NOT_AGGREGATE_FUNCTION +CREATE EVENT ev1 + ON SCHEDULE EVERY 1 HOUR + STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK +DO FETCH GROUP NEXT ROW; + + +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; + FUNCTION f1 RETURN INT; +END; +$$ + +--error ER_NOT_AGGREGATE_FUNCTION +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS + BEGIN + FETCH GROUP NEXT ROW; -- In a package procedure + END; + FUNCTION f1 RETURN INT AS + BEGIN + RETURN 0; + END; +END; +$$ + +--error ER_NOT_AGGREGATE_FUNCTION +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS + BEGIN + NULL; + END; + FUNCTION f1 RETURN INT AS + BEGIN + FETCH GROUP NEXT ROW; -- In a package function + RETURN 0; + END; +END; +$$ + +--error ER_NOT_AGGREGATE_FUNCTION +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS + BEGIN + NULL; + END; + FUNCTION f1 RETURN INT AS + BEGIN + RETURN 0; + END; +BEGIN + FETCH GROUP NEXT ROW; -- In a package executable section +END; +$$ + +DELIMITER ;$$ +DROP PACKAGE pkg1; diff --git a/mysql-test/suite/compat/oracle/t/empty_string_literal.test b/mysql-test/suite/compat/oracle/t/empty_string_literal.test new file mode 100644 index 00000000..3c612f72 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/empty_string_literal.test @@ -0,0 +1,8 @@ +USE test; +--echo # +--echo # MDEV-14013 : sql_mode=EMPTY_STRING_IS_NULL +--echo # + +set @mode='ORACLE,EMPTY_STRING_IS_NULL'; + +--source include/empty_string_literal.inc diff --git a/mysql-test/suite/compat/oracle/t/events.test b/mysql-test/suite/compat/oracle/t/events.test new file mode 100644 index 00000000..fb56af51 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/events.test @@ -0,0 +1,37 @@ +-- source include/not_embedded.inc + +set sql_mode='ORACLE'; + +--echo # +--echo # MDEV-16891 EVENTs created with SQL_MODE=ORACLE fail to execute +--echo # + +SET GLOBAL event_scheduler=off; + +SET sql_mode='ORACLE'; +CREATE TABLE t1 (a TIMESTAMP); +CREATE EVENT e1 + ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MICROSECOND + DO INSERT INTO t1 VALUES(NOW()); +SET GLOBAL event_scheduler=on; + +let $wait_timeout = 10; +let $wait_condition = + SELECT COUNT(*) = 0 + FROM INFORMATION_SCHEMA.EVENTS + WHERE event_schema = 'test' AND event_name = 'e1'; +--source include/wait_condition.inc + +SELECT COUNT(*) FROM t1; +DROP TABLE t1; + +SET GLOBAL event_scheduler=off; + +--echo # +--echo # MDEV-28588 SIGSEGV in __memmove_avx_unaligned_erms, strmake_root +--echo # +CREATE EVENT ev ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO BEGIN END; +--vertical_results +SELECT EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='test' AND EVENT_NAME='ev'; +--horizontal_results +DROP EVENT ev; diff --git a/mysql-test/suite/compat/oracle/t/exception.test b/mysql-test/suite/compat/oracle/t/exception.test new file mode 100644 index 00000000..6448a6ef --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/exception.test @@ -0,0 +1,457 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # sql_mode=ORACLE: Predefined exceptions: TOO_MANY_ROWS, NO_DATA_FOUND, DUP_VAL_ON_INDEX +--echo # + +--echo # +--echo # Testing NO_DATA_FOUND and TOO_MANY_ROWS +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20); +DELIMITER $$; +CREATE PROCEDURE p1(lim INT, res OUT VARCHAR) +AS + a INT; +BEGIN + SELECT a INTO a FROM t1 LIMIT lim; +EXCEPTION + WHEN TOO_MANY_ROWS THEN res:='--- too_many_rows cought ---'; + WHEN NO_DATA_FOUND THEN res:='--- no_data_found cought ---'; +END; +$$ +DELIMITER ;$$ +SET @res=''; +CALL p1(0, @res); +SELECT @res; +CALL p1(2, @res); +SELECT @res; +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # +--echo # Testing DUP_VAL_ON_INDEX +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY); +DELIMITER $$; +CREATE PROCEDURE p1(res OUT VARCHAR) +AS +BEGIN + INSERT INTO t1 VALUES (10); + INSERT INTO t1 VALUES (10); +EXCEPTION + WHEN DUP_VAL_ON_INDEX THEN res:='--- dup_val_on_index cought ---'; +END; +$$ +DELIMITER ;$$ +SET @res=''; +CALL p1(@res); +SELECT @res; +SELECT * FROM t1; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-10840 sql_mode=ORACLE: RAISE statement for predefined exceptions +--echo # + +--echo # +--echo # RAISE outside of an SP context +--echo # + +--error ER_SP_COND_MISMATCH +RAISE NO_DATA_FOUND; +--error ER_SP_COND_MISMATCH +RAISE INVALID_CURSOR; +--error ER_SP_COND_MISMATCH +RAISE DUP_VAL_ON_INDEX; +--error ER_SP_COND_MISMATCH +RAISE TOO_MANY_ROWS; + +--error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER +RAISE; + + +--echo # +--echo # RAISE for an undefinite exception +--echo # + +DELIMITER $$; +--error ER_SP_COND_MISMATCH +CREATE PROCEDURE p1 +AS +BEGIN + RAISE xxx; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # RAISE for predefined exceptions +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + RAISE no_data_found; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + RAISE invalid_cursor; +END; +$$ +DELIMITER ;$$ +--error ER_SP_CURSOR_NOT_OPEN +CALL p1(); +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + RAISE dup_val_on_index; +END; +$$ +DELIMITER ;$$ +--error ER_DUP_ENTRY +CALL p1(); +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + raise too_many_rows; +END; +$$ +DELIMITER ;$$ +--error ER_TOO_MANY_ROWS +CALL p1(); +DROP PROCEDURE p1; + + +--echo # +--echo # RAISE with no exception name (resignal) +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +AS +BEGIN + RAISE; +END; +$$ +DELIMITER ;$$ +--error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER +CALL p1(); +DROP PROCEDURE p1; + + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20); +DELIMITER $$; +CREATE PROCEDURE p1(lim INT) +AS + a INT; +BEGIN + SELECT a INTO a FROM t1 LIMIT lim; +EXCEPTION + WHEN TOO_MANY_ROWS THEN RAISE; + WHEN NO_DATA_FOUND THEN RAISE; +END; +$$ +DELIMITER ;$$ +CALL p1(0); +--error ER_TOO_MANY_ROWS +CALL p1(2); +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20); +DELIMITER $$; +CREATE PROCEDURE p1(lim INT) +AS + a INT; +BEGIN + SELECT a INTO a FROM t1 LIMIT lim; +EXCEPTION + WHEN OTHERS THEN RAISE; +END; +$$ +DELIMITER ;$$ +CALL p1(0); +--error ER_TOO_MANY_ROWS +CALL p1(2); +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20); +DELIMITER $$; +CREATE PROCEDURE p1() +AS + a INT; + CURSOR c IS SELECT a FROM t1; +BEGIN + FETCH c INTO a; +EXCEPTION + WHEN INVALID_CURSOR THEN RAISE; +END; +$$ +DELIMITER ;$$ +--error ER_SP_CURSOR_NOT_OPEN +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20); +DELIMITER $$; +CREATE PROCEDURE p1() +AS + a INT; + CURSOR c IS SELECT a FROM t1; +BEGIN + FETCH c INTO a; +EXCEPTION + WHEN OTHERS THEN RAISE; +END; +$$ +DELIMITER ;$$ +--error ER_SP_CURSOR_NOT_OPEN +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # +--echo # Testing that warning-alike errors are caught by OTHERS +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE FUNCTION f1 RETURN VARCHAR +AS + a INT:=10; +BEGIN + SELECT a INTO a FROM t1; + RETURN 'OK'; +EXCEPTION + WHEN OTHERS THEN RETURN 'Exception'; +END; +$$ +DELIMITER ;$$ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; +DROP TABLE t1; + + +--echo # +--echo # End of MDEV-10840 sql_mode=ORACLE: RAISE statement for predefined exceptions +--echo # + + +--echo # +--echo # MDEV-10587 sql_mode=ORACLE: User defined exceptions +--echo # + +--echo # +--echo # Checking that duplicate WHEN clause is not allowed +--echo # + +DELIMITER $$; +--error ER_SP_DUP_HANDLER +CREATE FUNCTION f1() RETURN VARCHAR +AS + e EXCEPTION; +BEGIN + RETURN 'Got no exceptions'; +EXCEPTION + WHEN e THEN RETURN 'Got exception e'; + WHEN e THEN RETURN 'Got exception e'; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Checking that raised user exceptions are further caught by name +--echo # + +DELIMITER $$; +CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR +AS + e EXCEPTION; + f EXCEPTION; +BEGIN + IF c = 'e' THEN RAISE e; END IF; + IF c = 'f' THEN RAISE f; END IF; + RETURN 'Got no exceptions'; +EXCEPTION + WHEN e THEN RETURN 'Got exception e'; +END; +$$ +DELIMITER ;$$ +SELECT f1(''); +SELECT f1('e'); +--error ER_SIGNAL_EXCEPTION +SELECT f1('f'); +DROP FUNCTION f1; + + +--echo # +--echo # Checking that raised user exceptions are further caught by OTHERS +--echo # + +DELIMITER $$; +CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR +AS + e EXCEPTION; + f EXCEPTION; +BEGIN + IF c = 'e' THEN RAISE e; END IF; + IF c = 'f' THEN RAISE f; END IF; + RETURN 'Got no exceptions'; +EXCEPTION + WHEN OTHERS THEN RETURN 'Got some exception'; +END; +$$ +DELIMITER ;$$ +SELECT f1(''); +SELECT f1('e'); +SELECT f1('f'); +DROP FUNCTION f1; + + +--echo # +--echo # Checking that 'WHEN e .. WHEN f' does not produce ER_SP_DUP_HANDLER +--echo # + +DELIMITER $$; +CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR +AS + e EXCEPTION; + f EXCEPTION; + a VARCHAR(64):=''; +BEGIN + BEGIN + IF c = 'e' THEN RAISE e; END IF; + IF c = 'f' THEN RAISE f; END IF; + EXCEPTION + WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE e; END; + WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE f; END; + END; + RETURN 'Got no exceptions'; +EXCEPTION + WHEN OTHERS THEN RETURN a || 'Got EXCEPTION2/OTHERS;'; +END; +$$ +DELIMITER ;$$ +SELECT f1(''); +SELECT f1('e'); +SELECT f1('f'); +DROP FUNCTION f1; + + +--echo # +--echo # Checking that resignaled user exceptions are further caught by name +--echo # +DELIMITER $$; +CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR +AS + e EXCEPTION; + f EXCEPTION; + a VARCHAR(64):=''; +BEGIN + BEGIN + IF c = 'e' THEN RAISE e; END IF; + IF c = 'f' THEN RAISE f; END IF; + EXCEPTION + WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE; END; + WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE; END; + END; + RETURN 'Got no exceptions'; +EXCEPTION + WHEN e THEN RETURN a || 'Got EXCEPTION2/e;'; +END; +$$ +DELIMITER ;$$ +SELECT f1(''); +SELECT f1('e'); +--error ER_SIGNAL_EXCEPTION +SELECT f1('f'); +DROP FUNCTION f1; + + +--echo # +--echo # Checking that resignaled user exceptions are further caught by OTHERS +--echo # + +DELIMITER $$; +CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR +AS + e EXCEPTION; + f EXCEPTION; + a VARCHAR(64):=''; +BEGIN + BEGIN + IF c = 'e' THEN RAISE e; END IF; + IF c = 'f' THEN RAISE f; END IF; + EXCEPTION + WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE; END; + WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE; END; + END; + RETURN 'Got no exceptions'; +EXCEPTION + WHEN OTHERS THEN RETURN a || 'Got EXCEPTION2/OTHERS;'; +END; +$$ +DELIMITER ;$$ +SELECT f1(''); +SELECT f1('e'); +SELECT f1('f'); +DROP FUNCTION f1; + + +--echo # +--echo # End of MDEV-10587 sql_mode=ORACLE: User defined exceptions +--echo # + +--echo # +--echo # MDEV-12088 sql_mode=ORACLE: Do not require BEGIN..END in multi-statement exception handlers in THEN clause +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (10),(20),(30); +DELIMITER $$; +CREATE PROCEDURE p1(a INT) AS +BEGIN + INSERT INTO t1 (a) VALUES (a); +EXCEPTION + WHEN DUP_VAL_ON_INDEX THEN + a:= a+1; + INSERT INTO t1 VALUES (a); + WHEN OTHERS THEN + NULL; + NULL; +END; +$$ +DELIMITER ;$$ +CALL p1(30); +SELECT * FROM t1; +DROP PROCEDURE p1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/func_add_months.test b/mysql-test/suite/compat/oracle/t/func_add_months.test new file mode 100644 index 00000000..ca9391ef --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_add_months.test @@ -0,0 +1,38 @@ +--echo Test for ADD_MONTHS + +CREATE TABLE t1(c1 int, c2 datetime, c3 date, c4 time, c5 timestamp); + +INSERT INTO t1 VALUES (1, '2011-11-12 12:10:11', '2011-11-12', '12:10:11', '2011-11-12 12:10:11'); +INSERT INTO t1 VALUES (2, '2021-11-12 00:23:12', '2021-11-12', '00:23:12', '2021-11-12 00:23:12'); +INSERT INTO t1 VALUES (3, '2011-01-22 16:45:45', '2011-01-22', '16:45:45', '2011-01-22 16:45:45'); +INSERT INTO t1 VALUES (4, '2031-05-12 04:11:34', '2031-05-12', '04:11:34', '2031-05-12 04:11:34'); +INSERT INTO t1 VALUES (5, '2031-09-02 08:15:22', '2031-09-02', '08:15:22', '2031-09-02 08:15:22'); +INSERT INTO t1 VALUES (6, '0000-09-02 00:00:00', '0000-09-02', '00:00:00', '1980-09-02 00:00:00'); +INSERT INTO t1 VALUES (7, '9999-09-02', '9999-09-02', '00:00:00', '1980-09-02'); + +# some normal case +SELECT c1, ADD_MONTHS(c2, 2), ADD_MONTHS(c3, 2), ADD_MONTHS(c5, 2) FROM t1; +SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(c5, 2000) FROM t1; +SELECT c1, ADD_MONTHS(c2, 0), ADD_MONTHS(c3, -200), ADD_MONTHS(c5, -2) FROM t1; +SELECT c1, ADD_MONTHS(c2, -15), ADD_MONTHS(c3, -111), ADD_MONTHS(c5, 2) FROM t1; + +# for time type, it will be overflow +SELECT ADD_MONTHS(c4, 11) FROM t1 WHERE c1 = 1; + +UPDATE t1 SET c2=ADD_MONTHS(c2, 2); +SELECT c2 FROM t1; + +EXPLAIN EXTENDED SELECT c1, ADD_MONTHS(c2, -15) FROM t1 WHERE c1 = 1; + +# string type can be convert to datetime type +SELECT ADD_MONTHS("2000-10-10", 12); +SELECT ADD_MONTHS("2000:10:10", 12); + +# number type can not be convert datetime type +SELECT ADD_MONTHS(2000, 12); + +# last day of the month +SELECT ADD_MONTHS('2011-01-31', 1), ADD_MONTHS('2012-01-31', 1), ADD_MONTHS('2012-01-31', 2), ADD_MONTHS('2012-01-31', 3); +SELECT ADD_MONTHS('2011-01-30', 1), ADD_MONTHS('2012-01-30', 1), ADD_MONTHS('2012-01-30', 2), ADD_MONTHS('2012-01-30', 3); + +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/func_case.test b/mysql-test/suite/compat/oracle/t/func_case.test new file mode 100644 index 00000000..d5e0d650 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_case.test @@ -0,0 +1,9 @@ +# +# Testing CASE and its abbreviations +# + +SET sql_mode=ORACLE; + +SELECT NVL(NULL, 'a'), NVL('a', 'b'); + +SELECT NVL2(NULL, 'a', 'b'), NVL2('a', 'b', 'c'); diff --git a/mysql-test/suite/compat/oracle/t/func_concat.test b/mysql-test/suite/compat/oracle/t/func_concat.test new file mode 100644 index 00000000..5a613242 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_concat.test @@ -0,0 +1,184 @@ +# +# Testing CONCAT with null values +# + +SET sql_mode=ORACLE; + +EXPLAIN EXTENDED SELECT 'a'||'b'||'c'; +EXPLAIN EXTENDED SELECT CONCAT('a'||'b'||'c'); + +SELECT '' || ''; +SELECT '' || 'b'; +SELECT '' || NULL; +SELECT 'a' || ''; +SELECT 'a' || 'b'; +SELECT 'a' || NULL; +SELECT NULL || ''; +SELECT NULL || 'b'; +SELECT NULL || NULL; + +SELECT '' || '' || ''; +SELECT '' || '' || 'c'; +SELECT '' || '' || NULL; +SELECT '' || 'b' || ''; +SELECT '' || 'b' || 'c'; +SELECT '' || 'b' || NULL; +SELECT '' || NULL || ''; +SELECT '' || NULL || 'c'; +SELECT '' || NULL || NULL; + +SELECT 'a' || '' || ''; +SELECT 'a' || '' || 'c'; +SELECT 'a' || '' || NULL; +SELECT 'a' || 'b' || ''; +SELECT 'a' || 'b' || 'c'; +SELECT 'a' || 'b' || NULL; +SELECT 'a' || NULL || ''; +SELECT 'a' || NULL || 'c'; +SELECT 'a' || NULL || NULL; + +SELECT NULL || '' || ''; +SELECT NULL || '' || 'c'; +SELECT NULL || '' || NULL; +SELECT NULL || 'b' || ''; +SELECT NULL || 'b' || 'c'; +SELECT NULL || 'b' || NULL; +SELECT NULL || NULL || ''; +SELECT NULL || NULL || 'c'; +SELECT NULL || NULL || NULL; + +CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(10)); + +INSERT INTO t1 VALUES ('', '', ''); +INSERT INTO t1 VALUES ('', '', 'c'); +INSERT INTO t1 VALUES ('', '', NULL); +INSERT INTO t1 VALUES ('', 'b', ''); +INSERT INTO t1 VALUES ('', 'b', 'c'); +INSERT INTO t1 VALUES ('', 'b', NULL); +INSERT INTO t1 VALUES ('', NULL, ''); +INSERT INTO t1 VALUES ('', NULL, 'c'); +INSERT INTO t1 VALUES ('', NULL, NULL); + +INSERT INTO t1 VALUES ('a', '', ''); +INSERT INTO t1 VALUES ('a', '', 'c'); +INSERT INTO t1 VALUES ('a', '', NULL); +INSERT INTO t1 VALUES ('a', 'b', ''); +INSERT INTO t1 VALUES ('a', 'b', 'c'); +INSERT INTO t1 VALUES ('a', 'b', NULL); +INSERT INTO t1 VALUES ('a', NULL, ''); +INSERT INTO t1 VALUES ('a', NULL, 'c'); +INSERT INTO t1 VALUES ('a', NULL, NULL); + +INSERT INTO t1 VALUES (NULL, '', ''); +INSERT INTO t1 VALUES (NULL, '', 'c'); +INSERT INTO t1 VALUES (NULL, '', NULL); +INSERT INTO t1 VALUES (NULL, 'b', ''); +INSERT INTO t1 VALUES (NULL, 'b', 'c'); +INSERT INTO t1 VALUES (NULL, 'b', NULL); +INSERT INTO t1 VALUES (NULL, NULL, ''); +INSERT INTO t1 VALUES (NULL, NULL, 'c'); +INSERT INTO t1 VALUES (NULL, NULL, NULL); + +SELECT LENGTH(a||b||c), a||b||c FROM t1 ORDER BY a,b,c; +SELECT LENGTH(CONCAT(a||b||c)), CONCAT(a||b||c) FROM t1 ORDER BY a,b,c; + +DROP TABLE t1; + +--echo # +--echo # MDEV-12478 CONCAT function inside view casts values incorrectly with Oracle sql_mode +--echo # + +SET sql_mode=ORACLE; +CREATE VIEW v1 AS SELECT 'foo'||NULL||'bar' AS test; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +SET sql_mode=DEFAULT; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +DROP VIEW v1; + +SET sql_mode=DEFAULT; +CREATE VIEW v1 AS SELECT CONCAT('foo',NULL,'bar') AS test; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +SET sql_mode=ORACLE; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +DROP VIEW v1; + +SET sql_mode=DEFAULT; +CREATE VIEW v1 AS SELECT '0'||'1' AS test; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +SET sql_mode=ORACLE; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +DROP VIEW v1; + + +--echo # +--echo # MDEV-16186 Concatenation operator || returns wrong results in sql_mode=ORACLE +--echo # + +# Concatenation operator || has the same precedence with + +# (stronger than << and weaker than * ^) + +SELECT -1<<1||1 AS a FROM DUAL; +SELECT -1||0<<1 AS a FROM DUAL; + +EXPLAIN EXTENDED SELECT -1<<1||1 AS a FROM DUAL; +EXPLAIN EXTENDED SELECT -1||0<<1 AS a FROM DUAL; + +SELECT -1+1||1 AS a FROM DUAL; +SELECT -1||0+1 AS a FROM DUAL; + +EXPLAIN EXTENDED SELECT -1+1||1 AS a FROM DUAL; +EXPLAIN EXTENDED SELECT -1||0+1 AS a FROM DUAL; + +SELECT 1*1||-1 AS a FROM DUAL; +SELECT 1||1*-1 AS a FROM DUAL; + +EXPLAIN EXTENDED SELECT 1*1||-1 AS a FROM DUAL; +EXPLAIN EXTENDED SELECT 1||1*-1 AS a FROM DUAL; + +SELECT -1^1||1 AS a FROM DUAL; +SELECT -1||0^1 AS a FROM DUAL; + +EXPLAIN EXTENDED SELECT -1^1||1 AS a FROM DUAL; +EXPLAIN EXTENDED SELECT -1||0^1 AS a FROM DUAL; + + +--echo # +--echo # MDEV-17359 Concatenation operator || in like expression failed in sql_mode=ORACLE +--echo # + +SELECT 'abc' LIKE 'a'||'%'; +EXPLAIN EXTENDED SELECT 'abc' LIKE 'a'||'%'; + +SELECT 'x' FROM DUAL WHERE 11 LIKE 1||1; +SELECT 'x' FROM DUAL WHERE 1||1 LIKE 11; +SELECT 'x' FROM DUAL WHERE 1||1 LIKE 1||1; + +CREATE TABLE t1 (c1 VARCHAR(10),c2 VARCHAR(10), ord INTEGER); +INSERT INTO t1 VALUES ('a', 'ab' ,1); +INSERT INTO t1 VALUES ('ab', 'ab', 2); +INSERT INTO t1 VALUES ('abc', 'ab', 3); + +SELECT c1 FROM t1 WHERE c1 LIKE '%'||'b' ORDER BY ord; +EXPLAIN EXTENDED SELECT c1 FROM t1 WHERE c1 LIKE '%'||'b' ORDER BY ord; + +SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord; +EXPLAIN EXTENDED SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord; + +SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%'; +EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%'; + +SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1; +EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1; + +CREATE VIEW v1 AS SELECT c1, c2, c1 LIKE c2||'_' FROM t1 ORDER BY ord; +SELECT * FROM v1; +EXPLAIN EXTENDED SELECT * FROM v1; + +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/func_decode.test b/mysql-test/suite/compat/oracle/t/func_decode.test new file mode 100644 index 00000000..b8be7178 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_decode.test @@ -0,0 +1,98 @@ +SET sql_mode=ORACLE; + +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE(10); +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE(10,10); + +SELECT DECODE(10,10,'x10'); +SELECT DECODE(11,10,'x10'); + +SELECT DECODE(10,10,'x10','def'); +SELECT DECODE(11,10,'x10','def'); + +SELECT DECODE(10,10,'x10',11,'x11','def'); +SELECT DECODE(11,10,'x10',11,'x11','def'); +SELECT DECODE(12,10,'x10',11,'x11','def'); + +EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def'); + +CREATE TABLE decode (decode int); +DROP TABLE decode; + + +--echo # +--echo # MDEV-13863 sql_mode=ORACLE: DECODE does not treat two NULLs as equivalent +--echo # + +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE(10); +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE(10,10); + +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE_ORACLE(10); +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE_ORACLE(10,10); + + +EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11'); +EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def'); +EXPLAIN EXTENDED SELECT DECODE_ORACLE(12,10,'x10',11,'x11'); +EXPLAIN EXTENDED SELECT DECODE_ORACLE(12,10,'x10',11,'x11','def'); + +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS + SELECT + DECODE(a,1,'x1',NULL,'xNULL') AS d1, + DECODE(a,1,'x1',NULL,'xNULL','xELSE') AS d2, + DECODE_ORACLE(a,1,'x1',NULL,'xNULL') AS d3, + DECODE_ORACLE(a,1,'x1',NULL,'xNULL','xELSE') AS d4 + FROM t1; +SHOW CREATE VIEW v1; +DROP VIEW v1; +DROP TABLE t1; + +SELECT DECODE(TIME'10:20:31','10:20:31','then1','10:20:32','then2','def'); +SELECT DECODE(TIME'10:20:32','10:20:31','then1','10:20:32','then2','def'); +SELECT DECODE(TIME'10:20:33','10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def'); +SELECT DECODE(NULL,TIME'10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def'); + +SELECT DECODE(TIMESTAMP'2001-01-01 10:20:31','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def'); +SELECT DECODE(TIMESTAMP'2001-01-01 10:20:32','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def'); +SELECT DECODE(TIMESTAMP'2001-01-01 10:20:33','2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def'); +SELECT DECODE(NULL,TIMESTAMP'2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def'); + +SELECT DECODE('w1','w1','then1','w2','then2','def'); +SELECT DECODE('w2','w1','then1','w2','then2','def'); +SELECT DECODE('w3','w1','then1',NULL,'then2NULL','w3','then3','def'); +SELECT DECODE(NULL,'w1','then1',NULL,'then2NULL','w3','then3','def'); + +SELECT DECODE(1,1,'then1',2,'then2','def'); +SELECT DECODE(2,1,'then1',2,'then2','def'); +SELECT DECODE(3,1,'then1',NULL,'then2NULL',3,'then3','def'); +SELECT DECODE(NULL,1,'then1',NULL,'then2NULL',3,'then3','def'); +SELECT DECODE(CAST(NULL AS SIGNED),1,'then1',NULL,'then2NULL',3,'then3','def'); + +SELECT DECODE(1.0,1.0,'then1',2.0,'then2','def'); +SELECT DECODE(2.0,1.0,'then1',2.0,'then2','def'); +SELECT DECODE(3.0,1.0,'then1',NULL,'then2NULL',3.0,'then3','def'); +SELECT DECODE(NULL,1.0,'then1',NULL,'then2NULL',3.0,'then3','def'); +SELECT DECODE(CAST(NULL AS DECIMAL),1.0,'then1',NULL,'then2NULL',3.0,'then3','def'); + +SELECT DECODE(1e0,1e0,'then1',2e0,'then2','def'); +SELECT DECODE(2e0,1e0,'then1',2e0,'then2','def'); +SELECT DECODE(3e0,1e0,'then1',NULL,'then2NULL',3e0,'then3','def'); +SELECT DECODE(NULL,1e0,'then1',NULL,'then2NULL',3e0,'then3','def'); +SELECT DECODE(CAST(NULL AS DOUBLE),1e0,'then1',NULL,'then2NULL',3e0,'then3','def'); + +SELECT DECODE(NULL,NULL,1,2) FROM DUAL; +SELECT DECODE(NULL,10,10,NULL,1,2) FROM DUAL; + +SELECT DECODE_ORACLE(NULL,NULL,1,2) FROM DUAL; +SELECT DECODE_ORACLE(NULL,10,10,NULL,1,2) FROM DUAL; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(10) DEFAULT NULL); +INSERT INTO t1 VALUES (NULL),(1); +SELECT a, DECODE(a,NULL,1,2) FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/func_length.test b/mysql-test/suite/compat/oracle/t/func_length.test new file mode 100644 index 00000000..7b76d33a --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_length.test @@ -0,0 +1,18 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-12783 sql_mode=ORACLE: Functions LENGTH() and LENGTHB() +--echo # +# +# Testing LENGTH / LENGTHB +# +# LENGTH : return the length of char +# LENGTHB : return the length of byte + + +SELECT LENGTH(null), LENGTH('a'), LENGTH(123); +SELECT LENGTHB(null), LENGTHB('a'), LENGTHB(123); + +SELECT LENGTH(_utf8 0xC39F), LENGTH(CHAR(14844588 USING utf8)); +SELECT LENGTHB(_utf8 0xC39F), LENGTHB(CHAR(14844588 USING utf8)); +EXPLAIN EXTENDED SELECT LENGTH('a'), LENGTHB('a'); diff --git a/mysql-test/suite/compat/oracle/t/func_misc.test b/mysql-test/suite/compat/oracle/t/func_misc.test new file mode 100644 index 00000000..c5b42134 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_misc.test @@ -0,0 +1,346 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-10578 sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM +--echo # + +--echo # +--echo # Using SQLCODE and SQLERRM outside of an SP +--echo # + +--error ER_BAD_FIELD_ERROR +SELECT SQLCODE; + +--error ER_BAD_FIELD_ERROR +SELECT SQLERRM; + +CREATE TABLE t1 (SQLCODE INT, SQLERRM VARCHAR(10)); +INSERT INTO t1 VALUES (10, 'test'); +SELECT SQLCODE, SQLERRM FROM t1; +DROP TABLE t1; + +--echo # +--echo # Normal SQLCODE and SQLERRM usage +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(stmt VARCHAR) +AS +BEGIN + EXECUTE IMMEDIATE stmt; + SELECT 'Error1: ' || SQLCODE || ' ' || SQLERRM; +EXCEPTION + WHEN OTHERS THEN + SELECT 'Error2: ' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +CALL p1('SELECT 1'); +CALL p1('xxx'); +CALL p1('SELECT 1'); +DROP PROCEDURE p1; + +--echo # +--echo # SQLCODE and SQLERRM hidden by local variables +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +AS + sqlcode INT:= 10; + sqlerrm VARCHAR(64) := 'test'; +BEGIN + SELECT 'Error: ' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1() +AS + sqlcode INT; + sqlerrm VARCHAR(64); +BEGIN + SQLCODE:= 10; + sqlerrm:= 'test'; + SELECT 'Error: ' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; + + +--echo # +--echo # SQLCODE and SQLERRM hidden by parameters +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(sqlcode INT, sqlerrm VARCHAR) +AS +BEGIN + SELECT 'Error: ' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +CALL p1(10, 'test'); +DROP PROCEDURE p1; + + +--echo # +--echo # SQLCODE and SQLERRM in CREATE..SELECT +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + CREATE TABLE t1 AS SELECT SQLCODE, SQLERRM; +END; +$$ +DELIMITER ;$$ +CALL p1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # SQLCODE and SQLERRM in EXPLAIN EXTENDED SELECT +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + EXPLAIN EXTENDED SELECT SQLCode, SQLErrm; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; + + + +--echo # +--echo # Warning-alike errors in stored functions +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE FUNCTION f1 RETURN VARCHAR +AS + a INT; +BEGIN + SELECT a INTO a FROM t1; + RETURN 'No exception ' || SQLCODE || ' ' || SQLERRM; +EXCEPTION + WHEN NO_DATA_FOUND THEN + RETURN 'Exception ' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE FUNCTION f1 RETURN VARCHAR +AS + a INT; +BEGIN + SELECT a INTO a FROM t1; + RETURN 'No exception ' || SQLCODE || ' ' || SQLERRM; +EXCEPTION + WHEN OTHERS THEN + RETURN 'Exception ' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; +DROP TABLE t1; + + +--echo # +--echo # Warning-alike errors in stored procedures +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1(res OUT VARCHAR) +AS + a INT; +BEGIN + SELECT a INTO a FROM t1; + res:= 'No exception ' || SQLCODE || ' ' || SQLERRM; +EXCEPTION + WHEN NO_DATA_FOUND THEN + res:= 'Exception ' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +CALL p1(@a); +SELECT @a; +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1(res OUT VARCHAR) +AS + a INT; +BEGIN + SELECT a INTO a FROM t1; + res:= 'No exception ' || SQLCODE || ' ' || SQLERRM; +EXCEPTION + WHEN OTHERS THEN + res:= 'Exception ' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +CALL p1(@a); +SELECT @a; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # SQLCODE and SQLERRM are cleared on RETURN +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE FUNCTION f1 RETURN VARCHAR +AS + a INT:=10; +BEGIN + SELECT a INTO a FROM t1; + RETURN 'Value=' || a; +EXCEPTION + WHEN NO_DATA_FOUND THEN RETURN 'Exception|' || SQLCODE || ' ' || SQLERRM; +END; +$$ +CREATE FUNCTION f2 RETURN VARCHAR +AS + a VARCHAR(128); +BEGIN + RETURN f1() || '|' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +SELECT f1() FROM DUAL; +SELECT f2() FROM DUAL; +DROP TABLE t1; +DROP FUNCTION f2; +DROP FUNCTION f1; + + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE FUNCTION f1 RETURN VARCHAR +AS + a INT:=10; +BEGIN + SELECT a INTO a FROM t1; + RETURN 'Value=' || a; +EXCEPTION + WHEN OTHERS THEN RETURN 'Exception|' || SQLCODE || ' ' || SQLERRM; +END; +$$ +CREATE FUNCTION f2 RETURN VARCHAR +AS + a VARCHAR(128); +BEGIN + RETURN f1() || '|' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +SELECT f1() FROM DUAL; +SELECT f2() FROM DUAL; +DROP TABLE t1; +DROP FUNCTION f2; +DROP FUNCTION f1; + + +--echo # +--echo # SQLCODE and SQLERRM are cleared on a return from a PROCEDURE +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1(res OUT VARCHAR) +AS + a INT:=10; +BEGIN + SELECT a INTO a FROM t1; + res:='Value=' || a; +EXCEPTION + WHEN NO_DATA_FOUND THEN res:='Exception|' || SQLCODE || ' ' || SQLERRM; +END; +$$ +CREATE FUNCTION f2 RETURN VARCHAR +AS + res VARCHAR(128); +BEGIN + CALL p1(res); + RETURN res || '|' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +SELECT f2() FROM DUAL; +DROP FUNCTION f2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1(res OUT VARCHAR) +AS + a INT:=10; +BEGIN + SELECT a INTO a FROM t1; + res:='Value=' || a; +EXCEPTION + WHEN OTHERS THEN res:='Exception|' || SQLCODE || ' ' || SQLERRM; +END; +$$ +CREATE FUNCTION f2 RETURN VARCHAR +AS + res VARCHAR(128); +BEGIN + CALL p1(res); + RETURN res || '|' || SQLCODE || ' ' || SQLERRM; +END; +$$ +DELIMITER ;$$ +SELECT f2() FROM DUAL; +DROP FUNCTION f2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # End of MDEV-10578 sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM +--echo # + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +DELIMITER $$; +BEGIN + SELECT SQLCODE; +END +$$ +DELIMITER ;$$ +--enable_ps_protocol +--disable_metadata diff --git a/mysql-test/suite/compat/oracle/t/func_pad.test b/mysql-test/suite/compat/oracle/t/func_pad.test new file mode 100644 index 00000000..bc33a9fa --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_pad.test @@ -0,0 +1,31 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-15739 - sql_mode=ORACLE: Make LPAD and RPAD return NULL instead of empty string +--echo # + +SELECT RPAD('a',0), RPAD('abc',1), RPAD('abc',2) ; +SELECT RPAD('a',0,'.'), RPAD('abc',1,'.'), RPAD('abc',2,'.') ; +SELECT LPAD('a',0), LPAD('abc',1), LPAD('abc',2) ; +SELECT LPAD('a',0,'.'), LPAD('abc',1,'.'), LPAD('abc',2,'.') ; + +CREATE TABLE t1 (c1 VARCHAR(10),c2 INTEGER, c3 VARCHAR(10), ord INTEGER); +INSERT INTO t1 VALUES ('a',1,null,1); +INSERT INTO t1 VALUES ('a',null,'.',2); +INSERT INTO t1 VALUES (null,1,'.',3); +INSERT INTO t1 VALUES ('a',-1,'.',4); +INSERT INTO t1 VALUES ('a',0,'.',5); +INSERT INTO t1 VALUES ('a',1,'.',6); +INSERT INTO t1 VALUES ('a',2,'.',7); + +SELECT LPAD(c1,c2,c3), LPAD(c1,c2) FROM t1 ORDER BY ord; +SELECT RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord; + +EXPLAIN EXTENDED SELECT RPAD('a',0,'.'), LPAD('a',0,'.'), LPAD(c1,c2,c3), LPAD(c1,c2), RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord; + +CREATE VIEW v1 AS SELECT RPAD('a',0,'.') AS "C1", LPAD('a',0,'.') AS "C2", LPAD(c1,c2,c3) AS "C3", LPAD(c1,c2) AS "C4", RPAD(c1,c2,c3) AS "C5", RPAD(c1,c2) AS "C6" FROM t1 ORDER BY ord; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +SELECT c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6 FROM v1; +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/func_replace.test b/mysql-test/suite/compat/oracle/t/func_replace.test new file mode 100644 index 00000000..0028f7d2 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_replace.test @@ -0,0 +1,22 @@ +# +# Testing replace with null args +# + +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-13003 - Oracle compatibility : Replace function +--echo # + +SELECT REPLACE(null,'a','b') ; +SELECT REPLACE('ab',null,'b') ; +SELECT REPLACE('ab','a',null) ; +SELECT REPLACE('ab',null,null) ; +SELECT REPLACE('aaa','a',null) ; + +EXPLAIN EXTENDED SELECT REPLACE('ab','a',null) ; + +CREATE VIEW v1 AS SELECT REPLACE('ab','a',null) ; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +DROP VIEW v1; diff --git a/mysql-test/suite/compat/oracle/t/func_substr.test b/mysql-test/suite/compat/oracle/t/func_substr.test new file mode 100644 index 00000000..b661dfd3 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_substr.test @@ -0,0 +1,47 @@ +--echo # +--echo # MDEV-14012 - sql_mode=Oracle: substr(): treat position 0 as position 1 +--echo # MDEV-10574 - sql_mode=Oracle: return null instead of empty string +--echo # + +SET sql_mode=ORACLE; +SELECT SUBSTR('abc',2,1),SUBSTR('abc',1,1), SUBSTR('abc',0,1) FROM dual; +SELECT SUBSTR('abc',2),SUBSTR('abc',1), SUBSTR('abc',0) FROM dual; +SELECT SUBSTR(null,2,1),SUBSTR(null,1), SUBSTR(null,0) FROM dual; +SELECT SUBSTR('abc',-2),SUBSTR('abc',-1), SUBSTR('abc',-0) FROM dual; +SELECT SUBSTR('abc',-2,1),SUBSTR('abc',-1,1), SUBSTR('abc',-0,1) FROM dual; +SELECT SUBSTR('abc',null) FROM dual; +SELECT SUBSTR('abc',2,null),SUBSTR('abc',1,null), SUBSTR('abc',0,null) FROM dual; +SELECT SUBSTR('abc',2,0),SUBSTR('abc',1,0), SUBSTR('abc',0,0) FROM dual; +SELECT SUBSTR('abc',2,-1),SUBSTR('abc',1,-1), SUBSTR('abc',0,-1) FROM dual; +SELECT SUBSTR(SPACE(0),1) FROM DUAL; + +CREATE TABLE t1 (c1 VARCHAR(10),start INTEGER, length INTEGER); +INSERT INTO t1 VALUES ('abc', 1, 1); +INSERT INTO t1 VALUES ('abc', 0, 1); +INSERT INTO t1 VALUES (null, 1, 1); +INSERT INTO t1 VALUES (null, 0, 1); +INSERT INTO t1 VALUES ('abc', 1, 0); +INSERT INTO t1 VALUES ('abc', 0, 0); +INSERT INTO t1 VALUES (null, 1, 0); +INSERT INTO t1 VALUES (null, 0, 0); +INSERT INTO t1 VALUES ('abc', 1, -1); +INSERT INTO t1 VALUES ('abc', 0, -1); +INSERT INTO t1 VALUES (null, 1, -1); +INSERT INTO t1 VALUES (null, 0, -1); +INSERT INTO t1 VALUES (SPACE(0), 0, 1); + +SELECT SUBSTR(c1,start,length) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL); +CREATE TABLE t2 AS SELECT SUBSTR(C1,1,1) AS C1 from t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +EXPLAIN EXTENDED SELECT SUBSTR('abc',2,1) ; + +CREATE VIEW v1 AS SELECT SUBSTR('abc',2,1) ; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +DROP VIEW v1; diff --git a/mysql-test/suite/compat/oracle/t/func_time.test b/mysql-test/suite/compat/oracle/t/func_time.test new file mode 100644 index 00000000..c1174f7f --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_time.test @@ -0,0 +1,25 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-16152 Expressions with INTERVAL return bad results in some cases +--echo # + +SELECT TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1, + -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2; + +SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1, + INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2, + +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3; + +EXPLAIN EXTENDED SELECT + TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1, + -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2; + +EXPLAIN EXTENDED SELECT + TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1, + INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2, + +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3; diff --git a/mysql-test/suite/compat/oracle/t/func_to_char.test b/mysql-test/suite/compat/oracle/t/func_to_char.test new file mode 100644 index 00000000..7a403215 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_to_char.test @@ -0,0 +1,236 @@ +############################################################## +# testcase for TO_CHAR() function for oracle +# Part of MDEV-20017 Implement TO_CHAR() Oracle compatible function +############################################################## + +# Save sql_mode +set @save_sql_mode=@@sql_mode; + +--echo # +--echo # test for datetime +--echo # + +CREATE TABLE t_to_char1(c0 int, c1 date, c2 time, c3 datetime); + +INSERT INTO t_to_char1 VALUES (1, '1000-1-1', '00:00:00', '1000-1-1 00:00:00'); +INSERT INTO t_to_char1 VALUES (2, '9999-12-31', '23:59:59', '9999-12-31 23:59:59'); +INSERT INTO t_to_char1 VALUES (3, '2021-01-03', '08:30:00', '2021-01-03 08:30:00'); +INSERT INTO t_to_char1 VALUES (4, '2021-07-03', '18:30:00', '2021-07-03 18:30:00'); + +CREATE TABLE t_to_char2(c1 timestamp); +INSERT INTO t_to_char2 VALUES ('1980-01-11 04:50:39'); +INSERT INTO t_to_char2 VALUES ('2000-11-11 12:50:00'); +INSERT INTO t_to_char2 VALUES ('2030-11-11 18:20:10'); + +# test for timestamp +SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char2; +SELECT TO_CHAR(c1, 'HH24-MI-SS') FROM t_to_char2; + +# test full output format +--echo # +--echo # test YYYY/YY/MM/DD/HH/HH24/MI/SS +--echo # +SELECT TO_CHAR(c1, 'YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'yyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'yy-mm-dd hh24:mi:ss') AS C3 FROM t_to_char1; + +--echo # +--echo # test YYY/Y/MON/DD/DY/HH/HH12/MI/SS +--echo # +SELECT TO_CHAR(c1, 'YYY-MON-DD') AS C1, TO_CHAR(c2, 'HH12:MI:SS') AS C2, TO_CHAR(c3, 'Y-MONTH-DY HH:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'yyy-Mon-Dd') AS C1, TO_CHAR(c2, 'Hh12:mi:Ss') AS C2, TO_CHAR(c3, 'y-Month-Dy Hh:Mi:Ss') AS C3 FROM t_to_char1; + +--echo # +--echo # test RRRR/RR/DAY +--echo # +SELECT TO_CHAR(c1, 'RRRR-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'RRRR-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'RR-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'Rrrr-Mm-Dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'Rrrr-mm-dd Hh24:mi:ss') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'rr-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'yy-mm-dd hh24:Mi:ss') AS C3 FROM t_to_char1; + +--echo # +--echo # test AD/A.D./BC/B.C./AM/A.M./PM/P.M. +--echo # +SELECT TO_CHAR(c1, 'ADYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'AD.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'ADYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'AD.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'BCYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'BCYYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'B.C.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'B.C.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'bcyyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'BcYYyy-MM-DD Hh24:mi:sS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'b.c.yyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mI:Ss') AS C2, TO_CHAR(c3, 'b.C.Yyyy-Mm-dd hH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'PMHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD P.M.HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'pmHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD p.m.HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'AMHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD A.m.HH24:MI:SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'amHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD a.M.HH24:MI:SS') AS C3 FROM t_to_char1; + +--echo # +--echo # test format without order +--echo # +SELECT TO_CHAR(c1, 'MM-YYYY-DD') AS C1, TO_CHAR(c2, 'HH:SS:MI') AS C2, TO_CHAR(c3, 'DD-YY-MM MI:SS:HH24') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'yyy-Dd-Mon') AS C1, TO_CHAR(c2, 'mi:Hh12:Ss') AS C2, TO_CHAR(c3, 'Ss:Hh:Mi Dy-y-Month') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'Dd-Mm-Rrrr') AS C1, TO_CHAR(c2, 'ss:hh:mi') AS C2, TO_CHAR(c3, 'ss:Rrrr-hh24-dd mon:mi') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'YYYYA.D.-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1; + +--echo # +--echo # test for special characters +--echo # +SELECT TO_CHAR(c1, 'YYYYMMDD') AS C1, TO_CHAR(c2, 'HHMISS') AS C2, TO_CHAR(c3, 'YYMMDDHH24MISS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'YYYY!!MM@DD') AS C1, TO_CHAR(c2, 'HH#MI$SS') AS C2, TO_CHAR(c3, 'YY%MM^DD*HH24(MI)SS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'YYYY_MM+DD') AS C1, TO_CHAR(c2, 'HH=MI{SS') AS C2, TO_CHAR(c3, 'YY}MMDDHH24MISS') AS C3 FROM t_to_char1; +SELECT TO_CHAR(c1, 'YYYY,MM.DD') AS C1, TO_CHAR(c2, 'HH/MI;SS') AS C2, TO_CHAR(c3, 'YY>MM> + BEGIN + SELECT 1 AS a; + END; +END +$$ +DELIMITER ;$$ + +DELIMITER $$; +DECLARE compressed INT DEFAULT 1; +BEGIN + SELECT compressed; +END +$$ +DELIMITER ;$$ diff --git a/mysql-test/suite/compat/oracle/t/minus.test b/mysql-test/suite/compat/oracle/t/minus.test new file mode 100644 index 00000000..cbaf4227 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/minus.test @@ -0,0 +1,44 @@ +CREATE TABLE tx1 (c1 int, c2 varchar(30)); +CREATE TABLE tx2 (c1 int, c2 varchar(30)); +CREATE TABLE tx3 (c1 int, c2 varchar(30)); +INSERT INTO tx1 VALUES (1, 'jim'); +INSERT INTO tx1 VALUES (2, 'menny'); +INSERT INTO tx1 VALUES (3, 'linda'); + +INSERT INTO tx2 VALUES (1, 'jim'); +INSERT INTO tx2 VALUES (2, 'kris'); +INSERT INTO tx2 VALUES (3, 'shory'); + +INSERT INTO tx3 VALUES (1, 'jim'); +INSERT INTO tx3 VALUES (2, 'kris'); +INSERT INTO tx3 VALUES (3, 'linda'); + +--echo # +--echo # test when sql_mode is not oracle +--echo # + +SELECT c2 FROM tx1 EXCEPT SELECT c2 from tx2; +--error 1064 +SELECT c2 FROM tx1 MINUS SELECT c2 from tx2; + +# MINUS should not be a reserved word +create table MINUS (a int); +drop table MINUS; + +--echo # +--echo # test when sql_mode is oracle +--echo # + +SET sql_mode=ORACLE; +SELECT c2 FROM tx1 MINUS SELECT c2 from tx2; +SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 MINUS SELECT c2 from tx3; +SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 EXCEPT SELECT c2 from tx3; +SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 UNION SELECT c2 from tx3; + +# MINUS should be a reserved word +--error ER_PARSE_ERROR +create table MINUS (a int); + +DROP TABLE tx1; +DROP TABLE tx2; +DROP TABLE tx3; \ No newline at end of file diff --git a/mysql-test/suite/compat/oracle/t/misc.test b/mysql-test/suite/compat/oracle/t/misc.test new file mode 100644 index 00000000..b3fdfdf3 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/misc.test @@ -0,0 +1,38 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-27690 Crash on `CHARACTER SET csname COLLATE DEFAULT` in column definition +--echo # + +CREATE TABLE t1 (a CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT); +DROP TABLE t1; +SELECT CAST('a' AS CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT); +SELECT COLUMN_GET(COLUMN_CREATE(0, 'string'),0 AS CHAR CHARACTER SET latin1 COLLATE DEFAULT) AS c1; + +--echo # +--echo # End of 10.2 tests +--echo # + + + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12086 sql_mode=ORACLE: allow SELECT UNIQUE as a synonym for SELECT DISTINCT +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(20),(30),(30),(30); +SELECT UNIQUE a FROM t1; +DROP TABLE t1; + + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/suite/compat/oracle/t/mysqldump_restore.test b/mysql-test/suite/compat/oracle/t/mysqldump_restore.test new file mode 100644 index 00000000..dd54c2b5 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/mysqldump_restore.test @@ -0,0 +1,30 @@ +# See comments in mysql-test/main/mysqldump_restore.test +--source include/not_embedded.inc + +SET sql_mode=ORACLE; + +let $mysqldumpfile = $MYSQLTEST_VARDIR/tmp/mysqldumpfile.sql; + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-17363 Compressed columns cannot be restored from dump +--echo # + +CREATE TABLE t1 (a VARCHAR(1000) COMPRESSED CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL); +INSERT INTO `t1` VALUES (REPEAT('a', 256)); +--exec $MYSQL_DUMP --skip-extended-insert test --skip-comments t1 > $mysqldumpfile +let $table_name = test.t1; +--source include/mysqldump.inc + +CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL); +INSERT INTO `t1` VALUES (REPEAT('a', 256)); +--exec $MYSQL_DUMP --skip-extended-insert test --skip-comments t1 > $mysqldumpfile +let $table_name = test.t1; +--source include/mysqldump.inc + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/suite/compat/oracle/t/parser.test b/mysql-test/suite/compat/oracle/t/parser.test new file mode 100644 index 00000000..40b4e297 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/parser.test @@ -0,0 +1,727 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-15620 Crash when using "SET @@NEW.a=expr" inside a trigger +--echo # + +CREATE TABLE t1 (a INT); +--error ER_UNKNOWN_STRUCTURED_VARIABLE +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @@NEW.a=0; +DROP TABLE t1; + +--echo # +--echo # MDEV-15615 Unexpected syntax error instead of "Unknown system variable" inside an SP +--echo # + +DELIMITER $$; +--error ER_UNKNOWN_SYSTEM_VARIABLE +DECLARE + a INT; +BEGIN + SET GLOBAL a=10; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # MDEV-16202 Latest changes made erroneously some keywords reserved in sql_mode=ORACLE +--echo # + + +DELIMITER $$; +CREATE PROCEDURE p1(name VARCHAR(64), pattern TEXT) AS + query TEXT DEFAULT REPLACE(pattern, 'name', name); +BEGIN + SELECT query AS ''; + EXECUTE IMMEDIATE query; +EXCEPTION + WHEN OTHERS THEN + BEGIN + SHOW ERRORS; + END; +END; +$$ + +CREATE PROCEDURE p2(name VARCHAR(64)) AS +BEGIN + CALL p1(name, 'DECLARE name INT; BEGIN name:=10; SELECT name; END'); + EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (name INT)', 'name', name); + CALL p1(name, 'SELECT name FROM t1'); + CALL p1(name, 'SELECT name ''alias'' FROM t1'); + CALL p1(name, 'SELECT name()'); + CALL p1(name, 'SELECT name.name()'); + CALL p1(name, 'SELECT name DATE FROM t1'); + CALL p1(name, 'SELECT name HISTORY FROM t1'); + CALL p1(name, 'SELECT name NEXT FROM t1'); + CALL p1(name, 'SELECT name PERIOD FROM t1'); + CALL p1(name, 'SELECT name PREVIOUS FROM t1'); + CALL p1(name, 'SELECT name SYSTEM FROM t1'); + CALL p1(name, 'SELECT name SYSTEM_TIME FROM t1'); + CALL p1(name, 'SELECT name TIME FROM t1'); + CALL p1(name, 'SELECT name TIMESTAMP FROM t1'); + CALL p1(name, 'SELECT name TRANSACTION FROM t1'); + CALL p1(name, 'SELECT name VALUE FROM t1'); + CALL p1(name, 'SELECT name VERSIONING FROM t1'); + CALL p1(name, 'SELECT name WITHOUT FROM t1'); + DROP TABLE t1; +END; +$$ +DELIMITER ;$$ + +--disable_column_names +CALL p2('date'); +CALL p2('history'); +CALL p2('next'); +CALL p2('period'); +CALL p2('previous'); +CALL p2('system'); +CALL p2('system_time'); +CALL p2('time'); +CALL p2('timestamp'); +CALL p2('transaction'); +CALL p2('value'); +CALL p2('versioning'); +CALL p2('without'); +--enable_column_names + +DROP PROCEDURE p2; +DROP PROCEDURE p1; + + +--echo # +--echo # MDEV-16244 sql_mode=ORACLE: Some keywords do not work in variable declarations +--echo # + +SET sql_mode=ORACLE; +DELIMITER /; + +DECLARE + do INT; +BEGIN + SELECT do INTO do FROM DUAL; +END; +/ + +DECLARE + handler INT; +BEGIN + SELECT handler INTO handler FROM DUAL; +END; +/ + +DECLARE + repair INT; +BEGIN + SELECT repair INTO repair FROM DUAL; +END; +/ + +DECLARE + shutdown INT; +BEGIN + SELECT shutdown INTO shutdown FROM DUAL; +END; +/ + +DECLARE + truncate INT; +BEGIN + SELECT truncate INTO truncate FROM DUAL; +END; +/ + +DECLARE + close INT; +BEGIN + SELECT close INTO close FROM DUAL; +END; +/ + +DECLARE + commit INT; +BEGIN + SELECT commit INTO commit FROM DUAL; +END; +/ + +DECLARE + open INT; +BEGIN + SELECT open INTO open FROM DUAL; +END; +/ + +DECLARE + rollback INT; +BEGIN + SELECT rollback INTO rollback FROM DUAL; +END; +/ + +DECLARE + savepoint INT; +BEGIN + SELECT savepoint INTO savepoint FROM DUAL; +END; +/ + +DECLARE + contains INT; +BEGIN + SELECT contains INTO contains FROM DUAL; +END; +/ + +DECLARE + language INT; +BEGIN + SELECT language INTO language FROM DUAL; +END; +/ + +DECLARE + no INT; +BEGIN + SELECT no INTO no FROM DUAL; +END; +/ + +DECLARE + charset INT; +BEGIN + SELECT charset INTO charset FROM DUAL; +END; +/ +DECLARE + follows INT; +BEGIN + SELECT follows INTO follows FROM DUAL; +END; +/ + +DECLARE + precedes INT; +BEGIN + SELECT precedes INTO precedes FROM DUAL; +END; +/ + +DELIMITER ;/ + + +--echo # +--echo # MDEV-16464 Oracle Comp.: Sql-Error on "SELECT name, comment FROM mysql.proc" +--echo # + +SET sql_mode=ORACLE; +SELECT name, comment FROM mysql.proc WHERE db='test'; + +CREATE TABLE comment (comment INT); +SELECT comment FROM comment; +SELECT comment comment FROM comment comment; +SELECT comment AS comment FROM comment AS comment; +DROP TABLE comment; + +DELIMITER /; +DECLARE + comment INT; +BEGIN + SELECT comment INTO comment FROM DUAL; +END; +/ +DELIMITER ;/ + +DELIMITER /; +CREATE PROCEDURE comment COMMENT 'test' AS +BEGIN + SELECT 1; +END; +/ +BEGIN + comment; +END; +/ +DELIMITER ;/ +CALL comment(); +CALL comment; +DROP PROCEDURE comment; + +enable_prepare_warnings; +DELIMITER /; +CREATE FUNCTION comment RETURN INT COMMENT 'test' AS +BEGIN + RETURN 1; +END; +/ +DELIMITER ;/ +SELECT test.comment() FROM DUAL; +disable_prepare_warnings; +DROP FUNCTION comment; + + +--echo # +--echo # MDEV-17660 sql_mode=ORACLE: Some keywords do not work as label names: history, system, versioning, without +--echo # + +DELIMITER /; +BEGIN +<> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<> + NULL; +END; +/ +DELIMITER ;/ + + +--echo # +--echo # MDEV-17666 sql_mode=ORACLE: Keyword ELSEIF should not be reserved +--echo # + +DELIMITER /; +DECLARE + ELSEIF INT; +BEGIN + ELSEIF:=1; +END; +/ +DELIMITER ;/ + +DELIMITER /; +BEGIN +<> + NULL; +END; +/ +DELIMITER ;/ + + +--echo # +--echo # MDEV-17693 Shift/reduce conflicts for NAMES,ROLE,PASSWORD in the option_value_no_option_type grammar +--echo # + +CREATE TABLE names (names INT); +SELECT names FROM names AS names; +DROP TABLE names; + +CREATE TABLE password (password INT); +SELECT password FROM password AS password; +DROP TABLE password; + +CREATE TABLE role (role INT); +SELECT role FROM role AS role; +DROP TABLE role; + +DELIMITER $$; +DECLARE + names VARCHAR(32) DEFAULT '[names]'; + password VARCHAR(32) DEFAULT '[password]'; + role VARCHAR(32) DEFAULT '[role]'; +BEGIN +<> + SELECT names; +<> + SELECT password; +<> + SELECT role; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_BAD_VAR_SHADOW +DECLARE + names VARCHAR(32); +BEGIN + SET names='[names]'; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_BAD_VAR_SHADOW +DECLARE + password VARCHAR(32); +BEGIN + SET password='[password]'; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +DECLARE + role VARCHAR(32); +BEGIN + SET role='[role]'; +END; +$$ +DELIMITER ;$$ + +--error ER_UNKNOWN_SYSTEM_VARIABLE +SELECT @@GLOBAL.names; +--error ER_UNKNOWN_SYSTEM_VARIABLE +SELECT @@GLOBAL.password; +--error ER_UNKNOWN_SYSTEM_VARIABLE +SELECT @@GLOBAL.role; + + +--echo # +--echo # MDEV-22822 sql_mode="oracle" cannot declare without variable errors +--echo # +--echo # It's OK to have no declarations between DECLARE and BEGIN. +--echo # + +DELIMITER //; +BEGIN + DECLARE + BEGIN + NULL; + END; +EXCEPTION +WHEN OTHERS THEN + NULL; +END; +// +DELIMITER ;// + + +DELIMITER //; +DECLARE +BEGIN + NULL; +EXCEPTION +WHEN OTHERS THEN + NULL; +END; +// +DELIMITER ;// + + +DELIMITER //; +BEGIN +<> + DECLARE + BEGIN + NULL; + END; +EXCEPTION +WHEN OTHERS THEN + NULL; +END; +// +DELIMITER ;// + + +--echo # +--echo # End of 10.3 tests +--echo # + + +--echo # +--echo # MDEV-21998: Server crashes in st_select_lex::add_table_to_list +--echo # upon mix of KILL and sequences +--echo # + +--error ER_SUBQUERIES_NOT_SUPPORTED +KILL ( SELECT 1 ) + LASTVAL(s); +--error ER_SUBQUERIES_NOT_SUPPORTED +KILL LASTVAL(s); + +--echo # +--echo # MDEV-23094: Multiple calls to a Stored Procedure from another +--echo # Stored Procedure crashes server +--echo # + +create table t1 (id1 int primary key, data1 int); +create table t2 (id2 int primary key, data2 int); + +delimiter //; +create procedure p1(id int,dt int) as +begin + if (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) + then + select 1; + end if; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(id int, dt int) as +begin +case (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) +when 1 then + select 1; +else + select 0; +end case; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(id int, dt int) as +begin +declare wcont int default 1; +begin + while (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) and wcont + loop + select 1; + set wcont=0; + end loop; +end; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(id int, dt int) as +begin +declare count int default 1; +begin + repeat + select 1; + set count=count+1; + until (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) and + count < 3 + end repeat; +end; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(id int, dt int) as +begin +for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) +loop + select 1; +end loop; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +set sql_mode=ORACLE; +delimiter //; +create or replace procedure p1(id int, dt int) as +begin + while (1) + loop + exit when (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)); + end loop; +end; +// +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +drop table t1,t2; + +--echo # End of 10.4 tests + +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-20734 Allow reserved keywords as user defined type names +--echo # + +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a DUAL); +--error ER_UNKNOWN_DATA_TYPE +SELECT CAST(1 AS DUAL); + + +--echo # +--echo # MDEV-20735 Allow non-reserved keywords as user defined type names +--echo # + +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a ASCII); +--error ER_UNKNOWN_DATA_TYPE +SELECT CAST(1 AS ASCII); + +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a LANGUAGE); +--error ER_UNKNOWN_DATA_TYPE +SELECT CAST(1 AS LANGUAGE); + +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a CLOSE); +--error ER_UNKNOWN_DATA_TYPE +SELECT CAST(1 AS CLOSE); + +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a NAMES); +--error ER_UNKNOWN_DATA_TYPE +SELECT CAST(1 AS NAMES); + +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a END); +--error ER_UNKNOWN_DATA_TYPE +SELECT CAST(1 AS END); + +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a GLOBAL); +--error ER_UNKNOWN_DATA_TYPE +SELECT CAST(1 AS GLOBAL); + +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a ACTION); +--error ER_UNKNOWN_DATA_TYPE +SELECT CAST(1 AS ACTION); + +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a BEGIN); +--error ER_UNKNOWN_DATA_TYPE +SELECT CAST(1 AS BEGIN); + +--echo # +--echo # End of 10.5 tests +--echo # + + +--echo # +--echo # Start of 10.6 tests +--echo # + +--echo # +--echo # MDEV-19682 sql_mode="oracle" does not support sysdate +--echo # + +# SYSDATE is not deterministic. Let's use LIKE and equality. +# The main point here is only to check that SYSDATE +# gets parsed without parentheses. The actial value is not important. +SELECT sysdate LIKE '____-__-__ __:__:__'; +SELECT sysdate = sysdate(); +SELECT sysdate = sysdate(0); + +--error ER_PARSE_ERROR +CREATE DATABASE sysdate; + +--error ER_PARSE_ERROR +CREATE TABLE sysdate (a INT); + +--error ER_PARSE_ERROR +CREATE TABLE t1 (sysdate INT); + +--error ER_PARSE_ERROR +CREATE TABLE t1 (a sysdate); + +DELIMITER $$; +--error ER_PARSE_ERROR +CREATE FUNCTION sysdate RETURN INT AS +BEGIN + RETURN 1; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_PARSE_ERROR +CREATE FUNCTION sysdate() RETURN INT AS +BEGIN + RETURN 1; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_PARSE_ERROR +DECLARE + sysdate INT := 10; +BEGIN + NULL; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_PARSE_ERROR +BEGIN +<> + NULL; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # End of 10.6 tests +--echo # diff --git a/mysql-test/suite/compat/oracle/t/plugin.test b/mysql-test/suite/compat/oracle/t/plugin.test new file mode 100644 index 00000000..a84c4ae7 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/plugin.test @@ -0,0 +1,3 @@ +SET sql_mode=ORACLE; + +--source include/install_plugin_if_exists.inc diff --git a/mysql-test/suite/compat/oracle/t/ps.test b/mysql-test/suite/compat/oracle/t/ps.test new file mode 100644 index 00000000..39770b48 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/ps.test @@ -0,0 +1,290 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-10801 sql_mode: dynamic SQL placeholders +--echo # + +SET @a=10, @b=20; +PREPARE stmt FROM 'SELECT ?,?'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'SELECT :a,:b'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'SELECT :aaa,:bbb'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'SELECT :"a",:"b"'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'SELECT :"aaa",:"bbb"'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'SELECT :1,:2'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'SELECT :222,:111'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'SELECT :0,:65535'; +EXECUTE stmt USING @a, @b; +PREPARE stmt FROM 'SELECT :65535,:0'; +EXECUTE stmt USING @a, @b; + +--echo # +--echo # MDEV-10709 Expressions as parameters to Dynamic SQL +--echo # + +--echo # +--echo # Testing disallowed expressions in USING +--echo # + +PREPARE stmt FROM 'SELECT :1 FROM DUAL'; +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE stmt USING (SELECT 1); +DEALLOCATE PREPARE stmt; + +DELIMITER $$; +CREATE FUNCTION f1() RETURN VARCHAR +AS +BEGIN + RETURN 'test'; +END; +$$ +DELIMITER ;$$ +PREPARE stmt FROM 'SELECT ? FROM DUAL'; +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE stmt USING f1(); +DEALLOCATE PREPARE stmt; +DROP FUNCTION f1; + +--echo # +--echo # Using a user variable as a EXECUTE..USING out parameter +--echo # + +DELIMITER /; +CREATE PROCEDURE p1(a OUT INT) +AS +BEGIN + a:= 10; +END; +/ +DELIMITER ;/ +SET @a=1; +CALL p1(@a); +SELECT @a; +SET @a=2; +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @a; +SELECT @a; +DROP PROCEDURE p1; + + +--echo # +--echo # Using an SP variable as a EXECUTE..USING out parameter +--echo # + +DELIMITER /; +CREATE PROCEDURE p1 (a OUT INT) +AS +BEGIN + a:=10; +END; +/ +CREATE PROCEDURE p2 (a OUT INT) +AS +BEGIN + PREPARE stmt FROM 'CALL p1(?)'; + EXECUTE stmt USING a; +END; +/ +DELIMITER ;/ +SET @a= 1; +CALL p2(@a); +SELECT @a; +DROP PROCEDURE p2; +DROP PROCEDURE p1; + + +--echo # +--echo # Using a trigger field as a EXECUTE..USING out parameter +--echo # +DELIMITER /; +CREATE PROCEDURE p1 (a OUT INT) +AS +BEGIN + a:= 10; +END; +/ +DELIMITER ;/ +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1(:NEW.a); +INSERT INTO t1 VALUES (1); +SELECT * FROM t1; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Testing re-prepare on a table metadata update between PREPARE and EXECUTE +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER /; +CREATE PROCEDURE p1(a IN INT) +AS +BEGIN + INSERT INTO t1 VALUES (a); +END; +/ +DELIMITER ;/ +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING 10; +SELECT * FROM t1; +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW NEW.a:=NEW.a+1; +EXECUTE stmt USING 20; +SELECT * FROM t1; +DEALLOCATE PREPARE stmt; +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # +--echo # End of MDEV-10709 Expressions as parameters to Dynamic SQL +--echo # + +--echo # +--echo # MDEV-10585 EXECUTE IMMEDIATE statement +--echo # + +--echo # +--echo # Testing disallowed expressions in USING +--echo # + +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE IMMEDIATE 'SELECT :1 FROM DUAL' USING (SELECT 1); + +DELIMITER $$; +CREATE FUNCTION f1() RETURN VARCHAR +AS +BEGIN + RETURN 'test'; +END; +$$ +DELIMITER ;$$ +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1(); +DROP FUNCTION f1; + + +--echo # +--echo # Testing simple expressions +--echo # + +EXECUTE IMMEDIATE 'SELECT :1 FROM DUAL' USING 10; + + +--echo # +--echo # MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions +--echo # + +--echo # +--echo # Testing erroneous and diallowed prepare source +--echo # + +--error ER_CANT_AGGREGATE_2COLLATIONS +EXECUTE IMMEDIATE _latin1'SELECT 1 AS c FROM ' || _latin2 'DUAL'; +--error ER_CANT_AGGREGATE_2COLLATIONS +PREPARE stmt FROM _latin1'SELECT 1 AS c FROM ' || _latin2 'DUAL'; + +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE IMMEDIATE (SELECT 'SELECT 1'); +--error ER_SUBQUERIES_NOT_SUPPORTED +PREPARE stmt FROM (SELECT 'SELECT 1'); + +--error ER_BAD_FIELD_ERROR +EXECUTE IMMEDIATE a; +--error ER_BAD_FIELD_ERROR +PREPARE stmt FROM a; + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE NULL; +--error ER_PARSE_ERROR +PREPARE stmt FROM NULL; + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE COALESCE(NULL); +--error ER_PARSE_ERROR +PREPARE stmt FROM COALESCE(NULL); + +DELIMITER $$; +CREATE FUNCTION f1() RETURN VARCHAR +AS +BEGIN + RETURN 't1'; +END; +$$ +DELIMITER ;$$ +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE IMMEDIATE f1(); +--error ER_SUBQUERIES_NOT_SUPPORTED +PREPARE stmt FROM f1(); +DROP FUNCTION f1; + +--echo # +--echo # Testing user variables in prepare source +--echo # + +SET @table_name='DUAL'; +EXECUTE IMMEDIATE 'SELECT 1 AS a FROM ' || @table_name; +PREPARE stmt FROM 'SELECT 1 AS a FROM ' || @table_name; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--echo # +--echo # Testing SP parameters and variables in prepare source +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(table_name VARCHAR) +AS +BEGIN + EXECUTE IMMEDIATE 'SELECT 1 AS c FROM '|| table_name; +END; +$$ +DELIMITER ;$$ +CALL p1('DUAL'); +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1() +AS + table_name VARCHAR(64):='DUAL'; +BEGIN + EXECUTE IMMEDIATE 'SELECT 1 AS c FROM ' || table_name; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; + + +--echo # +--echo # End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions +--echo # + + +--echo # +--echo # MDEV-12846 sql_mode=ORACLE: using Oracle-style placeholders in direct query execution makes the server crash +--echo # + +# When running with --ps, the below queries return +# CR_PARAMS_NOT_BOUND instead of ER_PARSE_ERROR + +--disable_ps_protocol +--error ER_PARSE_ERROR +SELECT ? FROM DUAL; +--error ER_PARSE_ERROR +SELECT :a FROM DUAL; +--error ER_PARSE_ERROR +SELECT :1 FROM DUAL; + +--error ER_PARSE_ERROR +SELECT 1+? FROM DUAL; +--error ER_PARSE_ERROR +SELECT 1+:a FROM DUAL; +--error ER_PARSE_ERROR +SELECT 1+:1 FROM DUAL; +--enable_ps_protocol diff --git a/mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test b/mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test new file mode 100644 index 00000000..b2aff233 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test @@ -0,0 +1,38 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +SET SQL_MODE=DEFAULT; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES ('2001-01-01'); + +SET SQL_MODE= ORACLE; +CREATE TABLE t2 SELECT * FROM t1; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; + +SET SQL_MODE= DEFAULT; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +SET SQL_MODE= ORACLE; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +--sync_slave_with_master +SELECT * FROM t1; +SELECT * FROM t2; + +SET SQL_MODE= DEFAULT; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +SET SQL_MODE= ORACLE; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +# Cleanup +--connection master +DROP TABLE t1, t2; +--source include/rpl_end.inc diff --git a/mysql-test/suite/compat/oracle/t/rpl_sp_package.test b/mysql-test/suite/compat/oracle/t/rpl_sp_package.test new file mode 100644 index 00000000..40bb0b0d --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/rpl_sp_package.test @@ -0,0 +1,134 @@ +--source include/master-slave.inc + +connection master; + +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE PACKAGE pack AS + FUNCTION f1 RETURN INT; + PROCEDURE p1; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +CREATE PACKAGE BODY pack AS + FUNCTION f1 RETURN INT AS + BEGIN + RETURN 10; + END; + PROCEDURE p1 AS + BEGIN + SELECT f1(); + END; +END pack; +$$ +DELIMITER ;$$ + +sync_slave_with_master; +connection slave; +--vertical_results +--replace_column 13 # 14 # +SELECT * FROM mysql.proc WHERE db='test' AND name='pack'; +--replace_column 13 # 14 # +SELECT * FROM mysql.proc WHERE db='test' AND name LIKE 'pack.%'; +--horizontal_results + +SET @@sql_mode=ORACLE; +SELECT pack.f1(); +CALL pack.p1(); +SET @@sql_mode=DEFAULT; + +connection master; +DROP PACKAGE pack; + +sync_slave_with_master; +connection slave; +SELECT COUNT(*) FROM mysql.proc WHERE db='test' AND name='pack'; + +--echo # +--echo # Creating a package with a COMMENT +--echo # + +connection master; + +DELIMITER $$; +CREATE PACKAGE p1 COMMENT 'package-p1-comment' AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 COMMENT 'package-body-p1-comment' AS + PROCEDURE p1 AS + BEGIN + NULL; + END; +END; +$$ +DELIMITER ;$$ + +SELECT definer, name, security_type, type, `comment` FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; +sync_slave_with_master; +SELECT definer, name, security_type, type, `comment` FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; + +connection master; +DROP PACKAGE p1; +sync_slave_with_master; + + +--echo # +--echo # Creating a package with a different DEFINER +--echo # + +connection master; + +DELIMITER $$; +CREATE DEFINER=xxx@localhost PACKAGE p1 AS + PROCEDURE p1; +END; +$$ +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS + PROCEDURE p1 AS + BEGIN + NULL; + END; +END; +$$ +DELIMITER ;$$ + +SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; +sync_slave_with_master; +SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; + +connection master; +DROP PACKAGE p1; +sync_slave_with_master; + +--echo # +--echo # Creating a package with a different DEFINER + SQL SECURITY INVOKER +--echo # + +connection master; + +DELIMITER $$; +CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS + PROCEDURE p1; +END; +$$ +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS + PROCEDURE p1 AS + BEGIN + NULL; + END; +END; +$$ +DELIMITER ;$$ + +SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; +sync_slave_with_master; +SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; + +connection master; +DROP PACKAGE p1; +sync_slave_with_master; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/compat/oracle/t/rpl_sp_package_variables.test b/mysql-test/suite/compat/oracle/t/rpl_sp_package_variables.test new file mode 100644 index 00000000..fca61124 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/rpl_sp_package_variables.test @@ -0,0 +1,36 @@ +--source include/master-slave.inc + +connection master; +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-13139 Package-wide variables in CREATE PACKAGE +--echo # +connection master; +DELIMITER $$; +CREATE PACKAGE p1 AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 AS + va INT:=10; + PROCEDURE p1 AS + BEGIN + INSERT INTO t1 VALUES (va); + END; +BEGIN + CREATE OR REPLACE TABLE t1 (a INT); +END; +$$ +DELIMITER ;$$ +CALL p1.p1(); +CALL p1.p1(); +SELECT * FROM t1; +sync_slave_with_master; +SELECT * FROM t1; +connection master; +DROP PACKAGE p1; +DROP TABLE t1; +sync_slave_with_master; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/compat/oracle/t/sequence.test b/mysql-test/suite/compat/oracle/t/sequence.test new file mode 100644 index 00000000..9bd8cec5 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sequence.test @@ -0,0 +1,48 @@ +--source include/have_binlog_format_row.inc + +SET sql_mode=ORACLE; + +--disable_ps2_protocol +CREATE SEQUENCE s1; +SHOW CREATE SEQUENCE s1; +SELECT s1.currval; +SELECT s1.nextval; +SELECT s1.nextval; +SELECT s1.nextval; +EXPLAIN EXTENDED SELECT s1.nextval; +SELECT nextval(s1); +EXPLAIN EXTENDED SELECT s1.currval; +SELECT lastval(s1); +DROP SEQUENCE s1; +--enable_ps2_protocol + +CREATE SEQUENCE s1; +CREATE VIEW v1 AS SELECT s1.nextval AS a; +SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1'; +--disable_ps2_protocol +SELECT * FROM v1; +--enable_ps2_protocol +SHOW CREATE VIEW v1; +DROP VIEW v1; +DROP SEQUENCE s1; + + +CREATE SEQUENCE s1; +CREATE VIEW v1 AS SELECT s1.currval AS a; +SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1'; +SELECT * FROM v1; +SHOW CREATE VIEW v1; +DROP VIEW v1; +DROP SEQUENCE s1; + +--echo # +--echo # MDEV-12533 sql_mode=ORACLE: Add support for database qualified sequence names in NEXTVAL and CURRVAL +--echo # +--disable_ps2_protocol +CREATE SEQUENCE s1; +SELECT test.s1.nextval; +SELECT test.s1.currval; +SELECT .s1.nextval; +SELECT .s1.currval; +DROP SEQUENCE s1; +--enable_ps2_protocol diff --git a/mysql-test/suite/compat/oracle/t/sp-anchor-row-type-table.test b/mysql-test/suite/compat/oracle/t/sp-anchor-row-type-table.test new file mode 100644 index 00000000..6e13a616 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-anchor-row-type-table.test @@ -0,0 +1,124 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-13581 ROW TYPE OF t1 and t1%ROWTYPE for routine parameters +--echo # + +CREATE TABLE t1 (a INT, b TEXT, c ENUM('a','b','c')); +DELIMITER $$; +CREATE PROCEDURE p1 (a t1%ROWTYPE) AS +BEGIN + CREATE TABLE t2 AS SELECT a.a AS a, a.b AS b, a.c AS c; + SHOW CREATE TABLE t2; + DROP TABLE t2; +END; +$$ +CREATE PROCEDURE p2 AS + a t1%ROWTYPE; +BEGIN + CALL p1(a); +END; +$$ +DELIMITER ;$$ +CALL p2(); +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT, b TEXT); +DELIMITER $$; +CREATE PROCEDURE p1 (a OUT t1%ROWTYPE) AS +BEGIN + SET a.a=10; + SET a.b='text'; +END; +$$ +CREATE PROCEDURE p2 AS + a t1%ROWTYPE; +BEGIN + CALL p1(a); + SELECT a.a, a.b; +END; +$$ +CREATE FUNCTION f1(a t1%ROWTYPE) RETURN TEXT AS +BEGIN + RETURN CONCAT(a.a, ' ', a.b); +END; +$$ +CREATE FUNCTION f2 RETURN TEXT AS + a t1%ROWTYPE; +BEGIN + CALL p1(a); + RETURN f1(a); +END; +$$ +DELIMITER ;$$ +CALL p2(); +SELECT f2(); +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP FUNCTION f2; +DROP FUNCTION f1; +DROP TABLE t1; + + +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT, b TEXT); +DELIMITER $$; +CREATE PROCEDURE p1 (a OUT db1.t1%ROWTYPE) AS +BEGIN + SET a.a=10; + SET a.b='text'; +END; +$$ +CREATE PROCEDURE p2 AS + a db1.t1%ROWTYPE; +BEGIN + CALL p1(a); + SELECT a.a, a.b; +END; +$$ +CREATE FUNCTION f1(a db1.t1%ROWTYPE) RETURN TEXT AS +BEGIN + RETURN CONCAT(a.a, ' ', a.b); +END; +$$ +CREATE FUNCTION f2() RETURN TEXT AS + a db1.t1%ROWTYPE; +BEGIN + CALL p1(a); + RETURN f1(a); +END; +$$ +DELIMITER ;$$ +CALL p2(); +SELECT f2(); +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP FUNCTION f2; +DROP FUNCTION f1; +DROP DATABASE db1; + + +--echo # +--echo # MDEV-14139 Anchored data types for variables +--echo # + +CREATE TABLE t1 (int11 INT, text0 TEXT); +DELIMITER $$; +DECLARE + row1 t1%ROWTYPE; + a_row1 row1%TYPE; + aa_row1 a_row1%TYPE; +BEGIN + CREATE TABLE t2 AS SELECT a_row1.int11 AS int11, a_row1.text0 AS text0; + SHOW CREATE TABLE t2; + DROP TABLE t2; + CREATE TABLE t2 AS SELECT aa_row1.int11 AS int11, aa_row1.text0 AS text0; + SHOW CREATE TABLE t2; + DROP TABLE t2; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/sp-anonymous.test b/mysql-test/suite/compat/oracle/t/sp-anonymous.test new file mode 100644 index 00000000..ac61e8ac --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-anonymous.test @@ -0,0 +1,244 @@ +--source include/have_innodb.inc + +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-10655 Anonymous blocks +--echo # + +--echo # Testing BEGIN NOT ATOMIC with no declarations +DELIMITER /; +BEGIN NOT ATOMIC + SELECT 1 AS a; +END +/ +DELIMITER ;/ + +--echo # Testing BEGIN NOT ATOMIC with declarations +--echo # DECLARE starts a new block and thus must be followed by BEGIN .. END +DELIMITER /; +BEGIN NOT ATOMIC + DECLARE + i INT DEFAULT 5; + x INT DEFAULT 10; + BEGIN + <