mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-30 18:36:12 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/sp_validation.test
Alexander Barkov f11504af51 MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR 
This patch adds support for SYS_REFCURSOR (a weakly typed cursor)
for both sql_mode=ORACLE and sql_mode=DEFAULT.

Works as a regular stored routine variable, parameter and return value:

- can be passed as an IN parameter to stored functions and procedures
- can be passed as an INOUT and OUT parameter to stored procedures
- can be returned from a stored function

Note, strongly typed REF CURSOR will be added separately.

Note, to maintain dependencies easier, some parts of sql_class.h
and item.h were moved to new header files:

- select_results.h:
  class select_result_sink
  class select_result
  class select_result_interceptor

- sp_cursor.h:
  class sp_cursor_statistics
  class sp_cursor

- sp_rcontext_handler.h
  class Sp_rcontext_handler and its descendants

The implementation consists of the following parts:
- A new class sp_cursor_array deriving from Dynamic_array

- A new class Statement_rcontext which contains data shared
  between sub-statements of a compound statement.
  It has a member m_statement_cursors of the sp_cursor_array data type,
  as well as open cursor counter. THD inherits from Statement_rcontext.

- A new data type handler Type_handler_sys_refcursor in plugins/type_cursor/
  It is designed to store uint16 references -
  positions of the cursor in THD::m_statement_cursors.

- Type_handler_sys_refcursor suppresses some derived numeric features.
  When a SYS_REFCURSOR variable is used as an integer an error is raised.

- A new abstract class sp_instr_fetch_cursor. It's needed to share
  the common code between "OPEN cur" (for static cursors) and
  "OPER cur FOR stmt" (for SYS_REFCURSORs).

- New sp_instr classes:
  * sp_instr_copen_by_ref      - OPEN sys_ref_curor FOR stmt;
  * sp_instr_cfetch_by_ref     - FETCH sys_ref_cursor INTO targets;
  * sp_instr_cclose_by_ref     - CLOSE sys_ref_cursor;
  * sp_instr_destruct_variable - to destruct SYS_REFCURSOR variables when
                                 the execution goes out of the BEGIN..END block
                                 where SYS_REFCURSOR variables are declared.
- New methods in LEX:
  * sp_open_cursor_for_stmt   - handles "OPEN sys_ref_cursor FOR stmt".
  * sp_add_instr_fetch_cursor - "FETCH cur INTO targets" for both
                                static cursors and SYS_REFCURSORs.
  * sp_close - handles "CLOSE cur" both for static cursors and SYS_REFCURSORs.

- Changes in cursor functions to handle both static cursors and SYS_REFCURSORs:
  * Item_func_cursor_isopen
  * Item_func_cursor_found
  * Item_func_cursor_notfound
  * Item_func_cursor_rowcount

- A new system variable @@max_open_cursors - to limit the number
  of cursors (static and SYS_REFCURSORs) opened at the same time.
  Its allowed range is [0-65536], with 50 by default.

- A new virtual method Type_handler::can_return_bool() telling
  if calling item->val_bool() is allowed for Items of this data type,
  or if otherwise the "Illegal parameter for operation" error should be raised
  at fix_fields() time.

- New methods in Sp_rcontext_handler:
  * get_cursor()
  * get_cursor_by_ref()

- A new class Sp_rcontext_handler_statement to handle top level statement
  wide cursors which are shared by all substatements.

- A new virtual method expr_event_handler() in classes Item and Field.
  It's needed to close (and make available for a new OPEN)
  unused THD::m_statement_cursors elements which do not have any references
  any more. It can happen in various moments in time, e.g.
  * after evaluation parameters of an SQL routine
  * after assigning a cursor expression into a SYS_REFCURSOR variable
  * when leaving a BEGIN..END block with SYS_REFCURSOR variables
  * after setting OUT/INOUT routine actual parameters from formal
    parameters.
2025-04-19 10:59:58 +04:00

2890 lines
61 KiB
Text
Raw Permalink Blame History

# This test file was borrowed from Oracle MySQL to validate that
# observable behaviour on SP's statements re-compilation (MDEV-5816)
# is identical to one implemented at Oracle MySQL (WL#4179).
# This test file contains a collection of tests developed for
# WL#4179 (Stored programs: validation of stored program statements).
#
# The main idea of the tests here is to check that a stored program
# properly handles metadata changes of the objects being used by
# the stored program.
###########################################################################
###########################################################################
--echo
--echo #
--echo # WL#4179: Stored programs: validation of stored program statements.
--echo #
--echo
--source include/have_debug.inc
--disable_ps2_protocol # lots of SELECTs with side effects below
###########################################################################
###########################################################################
SET @orig_debug=@@debug_dbug;
--echo # The test case below demonstrates that meta-data changes are detected
--echo # by triggers.
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 (a INT, b INT);
CREATE TABLE t3 (a INT);
INSERT INTO t2 VALUES (11, 12), (21, 22);
CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t3 (a) VALUES (1);
SELECT * FROM t1;
SELECT * FROM t2;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# trigger's body doesn't lead to eviction of the trigger
# from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
ALTER TABLE t1 ADD COLUMN c INT;
ALTER TABLE t2 ADD COLUMN c INT;
INSERT INTO t2 VALUES (31, 32, 33);
INSERT INTO t3 (a) VALUES (2);
SELECT * FROM t1;
SELECT * FROM t2;
SET @@debug_dbug=@orig_dbug;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
###########################################################################
--echo
--echo # Check that NEW/OLD rows work within triggers.
--echo
CREATE TABLE t1 (a INT);
INSERT INTO t1(a) VALUES (1);
delimiter |;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
BEGIN
SET @a = OLD.a;
SET @b = NEW.a;
SELECT OLD.a INTO @c;
SELECT NEW.a INTO @d;
SET NEW.a = NEW.a * 2;
END|
delimiter ;|
UPDATE t1 SET a = a * 10;
SELECT @a, @c, @b, @d;
SELECT a FROM t1;
DROP TABLE t1;
###########################################################################
--echo
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
CREATE PROCEDURE p1()
SELECT * FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
--echo
--echo # 1.1 Check if added column into table is recognized correctly
--echo # in a stored procedure.
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0;
CALL p1();
--echo
--echo # 1.2 Check if dropped column is not appeared in SELECT query
--echo # executed inside a stored procedure.
ALTER TABLE t1 DROP COLUMN a;
CALL p1();
--echo
--echo # 1.3 Check if changed column is picked up properly.
ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a';
DELETE FROM t1;
INSERT INTO t1 VALUES (b), ('hello');
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
--echo # 1.4 Check if table's recreation is handled correctly
--echo # inside a call of stored procedure.
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
CREATE PROCEDURE p1()
SELECT * FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
DROP TABLE t1;
--error ER_NO_SUCH_TABLE
CALL p1();
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
--echo # 1.5 Recreate table t1 with another set of columns and
--echo # re-call a stored procedure.
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
CREATE PROCEDURE p1()
SELECT * FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
DROP TABLE t1;
--error ER_NO_SUCH_TABLE
CALL p1();
CREATE TABLE t1 (b VARCHAR(10), c VARCHAR(10));
INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd');
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP TABLE t1;
DROP PROCEDURE p1;
###########################################################################
--echo
--echo # 2.1 Stored program that uses query like 'SELECT * FROM v' must be
--echo # re-executed successfully if some columns were added into the view
--echo # definition by ALTER VIEW;
CREATE VIEW v1 AS SELECT 1, 2, 3;
CREATE PROCEDURE p1()
SELECT * FROM v1;
CALL p1();
ALTER VIEW v1 AS SELECT 1, 2, 3, 4, 5;
# In result of dropping/creating a view sp_cache is invalidated.
# It means that the stored procedure p1 be evicted from sp_cache
# and loaded again on opening the routine. So, the statement
# SET @@debug_dbug='+d,check_sp_cache_not_invalidated'
# shouldn't be run before execution of the statement
# 'CALL p1();' since it will hit DBUG_SUICIDE in the function
# sp_cache_flush_obsolete()
CALL p1();
--echo
--echo # 2.2 Stored program that uses query like 'SELECT * FROM v' must be
--echo # re-executed successfully if some columns were removed from the view
--echo # definition by ALTER VIEW;
ALTER VIEW v1 AS SELECT 1, 5;
CALL p1();
--echo
--echo # 2.3 Stored program that uses query like 'SELECT * FROM v' must be
--echo # re-executed successfully if a base table for the view being used was
--echo # extended by new columns (by ALTER TABLE);
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
DROP VIEW v1;
CREATE VIEW v1 AS SELECT * FROM t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
SELECT * FROM v1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3;
# NOTE: this behaviour differs from the one of regular tables -- the thing is
# that "The view definition is “frozen” at creation time, so changes to the
# underlying tables afterward do not affect the view definition."
# (http://dev.mysql.com/doc/refman/5.0/en/create-view.html).
# So, this call should return 2 (not 3) columns.
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
--echo # 2.4 Stored program that uses query like 'SELECT * FROM v' must be
--echo # re-executed successfully if not used columns were removed from the
--echo # base table of this view (by ALTER TABLE);
DROP TABLE t1;
CREATE TABLE t1(a INT, b INT, c INT);
INSERT INTO t1 VALUES (1, 2, 3);
DROP VIEW v1;
CREATE VIEW v1 AS SELECT b, c FROM t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
SELECT * FROM v1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
ALTER TABLE t1 DROP COLUMN a;
CALL p1();
ALTER TABLE t1 DROP COLUMN b;
# NOTE: see the note above about view specific. Must-have column has been
# dropped -- the view has become invalid.
--error ER_VIEW_INVALID
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
--echo # 2.5 Stored program that uses query like 'SELECT * FROM v' must be
--echo # re-executed successfully if a type of some base table's columns were
--echo # changed (by ALTER TABLE);
DROP TABLE t1;
CREATE TABLE t1(a INT, b INT, c INT);
INSERT INTO t1 VALUES (1, 2, 3);
DROP VIEW v1;
CREATE VIEW v1 AS SELECT b, c FROM t1;
DROP PROCEDURE p1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CREATE PROCEDURE p1()
SELECT * FROM v1;
CALL p1();
ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a';
DELETE FROM t1;
INSERT INTO t1(a, c) VALUES (10, 30);
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
--echo # 2.6 Stored program that uses query like 'SELECT * FROM v' must be
--echo # re-executed successfully if the view 'v' was dropped and created again
--echo # with the same definition;
--echo #
--echo # 2.7 Stored program that uses query like 'SELECT * FROM v' must be
--echo # re-executed successfully if the view 'v' was dropped and created again
--echo # with different, but compatible definition.
DROP VIEW v1;
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE VIEW v1 AS SELECT 1, 2, 3;
CREATE PROCEDURE p1()
SELECT * FROM v1;
CALL p1();
DROP VIEW v1;
# In result of dropping/creating a view sp_cache is invalidated.
# It means that the stored procedure p1 be evicted from sp_cache
# and loaded again on opening the routine. So, the statement
# SET @@debug_dbug='+d,check_sp_cache_not_invalidated'
# shouldn't be run before execution of the statement
# 'CALL p1();' since it will hit DBUG_SUICIDE in the function
# sp_cache_flush_obsolete()
--error ER_NO_SUCH_TABLE
CALL p1();
CREATE VIEW v1 AS SELECT 4, 5, 6;
CALL p1();
--echo
--echo # 2.8 Stored program that uses query like 'SELECT * FROM v' must be
--echo # re-executed successfully if the view base tables have been re-created
--echo # using the same or compatible definition.
DROP VIEW v1;
DROP PROCEDURE p1;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE PROCEDURE p1()
SELECT * FROM v1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
DROP TABLE t1;
--error ER_VIEW_INVALID
CALL p1();
CREATE TABLE t1(a VARCHAR(255), b VARCHAR(255));
INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd');
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP VIEW v1;
DROP TABLE t1;
DROP PROCEDURE p1;
--echo
--echo # 3.1 Stored program that uses query like 'SELECT * FROM t' must be
--echo # re-executed successfully if some columns were added into temporary table
--echo # table 't' (by ALTER TABLE);
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE PROCEDURE p1() SELECT * FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3;
CALL p1();
--echo
--echo # 3.2 Stored program that uses query like 'SELECT * FROM t' must be
--echo # re-executed successfully if some columns were removed from temporary
--echo # table 't' (by ALTER TABLE);
ALTER TABLE t1 DROP COLUMN a;
CALL p1();
--echo
--echo # 3.3 Stored program that uses query like 'SELECT * FROM t' must be
--echo # re-executed successfully if a type of some temporary table's columns were
--echo # changed (by ALTER TABLE);
ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a';
INSERT INTO t1(c) VALUES (4);
CALL p1();
--echo
--echo # 3.4 Stored program that uses query like 'SELECT * FROM t' must be
--echo # re-executed successfully if the temporary table 't' was dropped and
--echo # created again with the same definition;
--echo #
--echo # 3.5 Stored program that uses query like 'SELECT * FROM t' must be
--echo # re-executed successfully if the temporary table 't' was dropped and
--echo # created again with different, but compatible definition.
DROP TEMPORARY TABLE t1;
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CALL p1();
DROP TEMPORARY TABLE t1;
CREATE TEMPORARY TABLE t1(a VARCHAR(255), b VARCHAR(255), c VARCHAR(255));
INSERT INTO t1 VALUES ('aa', 'bb', 'cc');
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP TEMPORARY TABLE t1;
DROP PROCEDURE p1;
###########################################################################
--echo
--echo # 4.1 Stored program must fail when it is re-executed after a table's column
--echo # that this program is referenced to has been removed;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE PROCEDURE p1() SELECT a, b FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
ALTER TABLE t1 DROP COLUMN b;
--error ER_BAD_FIELD_ERROR
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP TABLE t1;
--echo
--echo # 4.2 Stored program must fail when it is re-executed after a temporary
--echo # table's column that this program is referenced to has been removed;
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE PROCEDURE p1() SELECT a, b FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
ALTER TABLE t1 DROP COLUMN b;
--error ER_BAD_FIELD_ERROR
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP TEMPORARY TABLE t1;
--echo
--echo # 4.3 Stored program must fail when it is re-executed after a view's
--echo # column that this program is referenced to has been removed;
CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b;
CREATE PROCEDURE p1() SELECT a, b FROM v1;
CALL p1();
# In result of altering a view sp_cache is invalidated.
# It means that the stored procedure p1 be evicted from sp_cache
# and loaded again on opening the routine. So, the statement
# SET @@debug_dbug='+d,check_sp_cache_not_invalidated'
# shouldn't be run here since it will hit DBUG_SUICIDE in the function
# sp_cache_flush_obsolete()
ALTER VIEW v1 AS SELECT 1 AS a;
--error ER_BAD_FIELD_ERROR
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP VIEW v1;
--echo
--echo # 4.4 Stored program must fail when it is re-executed after a regular table
--echo # that this program referenced to was removed;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE PROCEDURE p1() SELECT a, b FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
DROP TABLE t1;
--error ER_NO_SUCH_TABLE
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
--echo
--echo # 4.5 Stored program must fail when it is re-executed after a view that
--echo # this program referenced to was removed;
CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b;
CREATE PROCEDURE p1() SELECT a, b FROM v1;
CALL p1();
DROP VIEW v1;
# In result of dropping/creating a view sp_cache is invalidated.
# It means that the stored procedure p1 be evicted from sp_cache
# and loaded again on opening the routine. So, the statement
# SET @@debug_dbug='+d,check_sp_cache_not_invalidated'
# shouldn't be run here since it will hit DBUG_SUICIDE in the function
# sp_cache_flush_obsolete()
--error ER_NO_SUCH_TABLE
CALL p1();
DROP PROCEDURE p1;
--echo
--echo # 4.6 Stored program must fail when it is re-executed after a temporary
--echo # table that this program referenced to was removed;
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE PROCEDURE p1() SELECT a, b FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
DROP TABLE t1;
--error ER_NO_SUCH_TABLE
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
--echo
--echo # 4.7 Stored program must fail if the program executes some
--echo # SQL-statement and afterwards re-executes it again when some table 't'
--echo # referenced by the statement was dropped in the period between statement
--echo # execution;
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DECLARE CONTINUE HANDLER FOR 1146
SELECT 'Table t1 does not exist anymore' as msg;
SELECT * FROM t1;
INSERT INTO t2 VALUES (1);
SELECT GET_LOCK('m1', 10000);
SELECT * FROM t1;
END|
delimiter ;|
--echo
--echo # -- connection: con1
--connect (con1, localhost, root)
SELECT GET_LOCK('m1', 0);
--echo
--echo # -- connection: default
--connection default
--send CALL p1()
--echo
--echo # -- connection: con1
--connection con1
let $wait_condition = SELECT COUNT(*) = 1 FROM t2;
--source include/wait_condition.inc
DROP TABLE t1;
SELECT RELEASE_LOCK('m1');
--echo
--echo # -- connection: default
--connection default
--reap
--disconnect con1
DROP TABLE t2;
DROP PROCEDURE p1;
###########################################################################
--echo
--echo # 5.1 Regular table -> View
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE PROCEDURE p1() SELECT * FROM t1;
CALL p1();
DROP TABLE t1;
CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b;
# In result of dropping/creating a view sp_cache is invalidated.
# It means that the stored procedure p1 be evicted from sp_cache
# and loaded again on opening the routine. So, the statement
# SET @@debug_dbug='+d,check_sp_cache_not_invalidated'
# shouldn't be run here since it will hit DBUG_SUICIDE in the function
# sp_cache_flush_obsolete()
CALL p1();
DROP PROCEDURE p1;
DROP VIEW t1;
--echo
--echo # 5.2 Regular table -> Temporary table
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE PROCEDURE p1() SELECT * FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
DROP TABLE t1;
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP TEMPORARY TABLE t1;
--echo
--echo # 5.3 View -> Regular table
CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b;
CREATE PROCEDURE p1() SELECT * FROM t1;
CALL p1();
DROP VIEW t1;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP TABLE t1;
--echo
--echo # 5.4 View -> Temporary table
CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b;
CREATE PROCEDURE p1() SELECT * FROM t1;
CALL p1();
DROP VIEW t1;
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
# In result of dropping/creating a view sp_cache is invalidated.
# It means that the stored procedure p1 be evicted from sp_cache
# and loaded again on opening the routine. So, the statement
# SET @@debug_dbug='+d,check_sp_cache_not_invalidated'
# shouldn't be run here since it will hit DBUG_SUICIDE in the function
# sp_cache_flush_obsolete()
CALL p1();
DROP PROCEDURE p1;
DROP TEMPORARY TABLE t1;
--echo
--echo # 5.5 Temporary table -> View
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE PROCEDURE p1() SELECT * FROM t1;
CALL p1();
DROP TEMPORARY TABLE t1;
CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b;
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP VIEW t1;
--echo
--echo # 5.6 Temporary table -> Regular table
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE PROCEDURE p1() SELECT * FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
DROP TEMPORARY TABLE t1;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP TABLE t1;
###########################################################################
--echo
--echo # 6.1 Trigger that uses column 'a' of table 't' via pseudo-variable NEW
--echo # must be re-executed successfully if the table definition has been changed
--echo # in a compatible way. "Compatible way" in this case is that if the table
--echo # 't' still has a column named 'a' and the column type is compatible with
--echo # the operation that NEW.a takes part of.
--echo #
--echo # 6.2 Trigger that uses column 'a' of table 't' via pseudo-variable OLD
--echo # must be re-executed successfully if the table definition has been changed
--echo # in a compatible way. "Compatible way" in this case is that if the table
--echo # 't' still has a column named 'a' and the column type is compatible with
--echo # the operation that OLD.a takes part of.
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
BEGIN
SET @x = OLD.a;
SET @y = NEW.a;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# trigger's body doesn't lead to eviction of the trigger
# from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
SET @x = 0, @y = 0;
UPDATE t1 SET a = 3, b = 4;
SELECT @x, @y;
--echo
ALTER TABLE t1 ADD COLUMN c INT DEFAULT -1;
--echo
SET @x = 0, @y = 0;
UPDATE t1 SET a = 5, b = 6;
SELECT @x, @y;
--echo
ALTER TABLE t1 CHANGE COLUMN a a VARCHAR(255);
--echo
SET @x = 0, @y = 0;
UPDATE t1 SET a = CONCAT('xxx_', a), b = 7;
SELECT @x, @y;
SET @@debug_dbug=@orig_dbug;
--echo
DROP TABLE t1;
--echo
--echo # 6.3 Re-execution of a trigger that uses column 'a' of table 't' via
--echo # pseudo-variable NEW must fail if the table definition has been changed in
--echo # the way that the column 'a' does not exist anymore.
--echo #
--echo # 6.4 Re-execution of a trigger that uses column 'a' of table 't' via
--echo # pseudo-variable OLD must fail if the table definition has been changed in
--echo # the way that the column 'a' does not exist anymore.
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
BEGIN
SET @x = OLD.a;
SET @y = NEW.b;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# trigger's body doesn't lead to eviction of the trigger
# from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
UPDATE t1 SET a = 3, b = 4;
--echo
ALTER TABLE t1 CHANGE COLUMN a a2 INT;
--echo
--error ER_BAD_FIELD_ERROR
UPDATE t1 SET a2 = 5, b = 6;
--echo
ALTER TABLE t1 CHANGE COLUMN a2 a INT;
ALTER TABLE t1 CHANGE COLUMN b b2 INT;
--echo
--error ER_BAD_FIELD_ERROR
UPDATE t1 SET a = 5, b2 = 6;
SET @@debug_dbug=@orig_dbug;
--echo
DROP TABLE t1;
###########################################################################
--echo
--echo # 7.1 Setup:
--echo # - stored program 'a', which alters regular table 't' in a compatible
--echo # way;
--echo # - stored program 'b', which calls 'a' and uses 't' before and after the
--echo # call;
--echo # Stored program 'b' must be executed successfully.
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE PROCEDURE p1()
ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM t1;
CALL p1();
SELECT a, b FROM t1;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
--echo
--echo # 7.2 Setup:
--echo # - stored program 'a', which alters temporary table 't' in a compatible
--echo # way;
--echo # - stored program 'b', which calls 'a' and uses 't' before and after the
--echo # call;
--echo # Stored program 'b' must be executed successfully.
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE PROCEDURE p1()
ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM t1;
CALL p1();
SELECT a, b FROM t1;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TEMPORARY TABLE t1;
--echo
--echo # 7.3 Setup:
--echo # - stored program 'a', which re-creates regular table 't' in a
--echo # compatible way;
--echo # - stored program 'b', which calls 'a' and uses 't' before and after the
--echo # call;
--echo # Stored program 'b' must be executed successfully.
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DROP TABLE t1;
CREATE TABLE t1(a INT, b INT, c INT);
INSERT INTO t1 VALUES (1, 2, 3);
END|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM t1;
CALL p1();
SELECT a, b FROM t1;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
--echo
--echo # 7.4 Setup:
--echo # - stored program 'a', which re-creates temporary table 't' in a
--echo # compatible way;
--echo # - stored program 'b', which calls 'a' and uses 't' before and after the
--echo # call;
--echo # Stored program 'b' must be executed successfully.
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DROP TEMPORARY TABLE t1;
CREATE TEMPORARY TABLE t1(a INT, b INT, c INT);
INSERT INTO t1 VALUES (1, 2, 3);
END|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM t1;
CALL p1();
SELECT a, b FROM t1;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TEMPORARY TABLE t1;
--echo
--echo # 7.5 Setup:
--echo # - stored program 'a', which re-creates view 'v' in a compatible way;
--echo # - stored program 'b', which calls 'a' and uses 'v' before and after the
--echo # call;
--echo # Stored program 'b' must be executed successfully.
CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b;
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DROP VIEW v1;
CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b, 3 AS c;
END|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM v1;
CALL p1();
SELECT a, b FROM v1;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP VIEW v1;
--echo
--echo # 7.6 Setup:
--echo # - stored program 'a', which alters regular table 't' in an incompatible
--echo # way;
--echo # - stored program 'b', which calls 'a' and uses 't' before and after the
--echo # call;
--echo # Stored program 'b' must fail on access to the table after its
--echo # modification.
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE PROCEDURE p1()
ALTER TABLE t1 DROP COLUMN a|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM t1;
CALL p1();
SELECT a, b FROM t1;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
--error ER_BAD_FIELD_ERROR
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
--echo
--echo # 7.7 Setup:
--echo # - stored program 'a', which alters temporary table 't' in an
--echo # incompatible way;
--echo # - stored program 'b', which calls 'a' and uses 't' before and after the
--echo # call;
--echo # Stored program 'b' must fail on access to the table after its
--echo # modification.
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE PROCEDURE p1()
ALTER TABLE t1 DROP COLUMN a|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM t1;
CALL p1();
SELECT a, b FROM t1;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
--error ER_BAD_FIELD_ERROR
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TEMPORARY TABLE t1;
--echo
--echo # 7.8 Setup:
--echo # - stored program 'a', which re-creates regular table 't' in an
--echo # incompatible way;
--echo # - stored program 'b', which calls 'a' and uses 't' before and after the
--echo # call;
--echo # Stored program 'b' must fail on access to the table after its
--echo # modification.
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DROP TABLE t1;
CREATE TABLE t1(b INT, c INT);
INSERT INTO t1 VALUES (2, 3);
END|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM t1;
CALL p1();
SELECT a, b FROM t1;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
--error ER_BAD_FIELD_ERROR
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
--echo
--echo # 7.9 Setup:
--echo # - stored program 'a', which re-creates temporary table 't' in an
--echo # incompatible way;
--echo # - stored program 'b', which calls 'a' and uses 't' before and after the
--echo # call;
--echo # Stored program 'b' must fail on access to the table after its
--echo # modification.
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DROP TEMPORARY TABLE t1;
CREATE TEMPORARY TABLE t1(b INT, c INT);
INSERT INTO t1 VALUES (2, 3);
END|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM t1;
CALL p1();
SELECT a, b FROM t1;
END|
delimiter ;|
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
--error ER_BAD_FIELD_ERROR
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TEMPORARY TABLE t1;
--echo
--echo # 7.10 Setup:
--echo # - stored program 'a', which re-creates view 'v' in an incompatible way;
--echo # - stored program 'b', which calls 'a' and uses 'v' before and after the
--echo # call;
--echo # Stored program 'b' must fail on access to the view after its
--echo # modification.
CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b;
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DROP VIEW v1;
CREATE VIEW v1 AS SELECT 2 AS b, 3 AS c;
END|
CREATE PROCEDURE p2()
BEGIN
SELECT a, b FROM v1;
CALL p1();
SELECT a, b FROM v1;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
--error ER_BAD_FIELD_ERROR
CALL p2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP VIEW v1;
###########################################################################
--echo # 8. Stored program must be executed successfully when:
--echo # a. the program uses a table/view/temporary table that doesn't exist
--echo # at the time of start program execution
--echo # b. failed reference to the missed table/view/temporary table handled
--echo # by stored program
--echo # c. this table/view/temporary table is created as part of the
--echo # program execution
--echo # d. stored program gets access to newly created table/view/temporary
--echo # table from some SQL-statement during subsequent stored program execution.
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'SQLEXCEPTION caught' AS msg;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
END;
SELECT * FROM t1;
SELECT * FROM t1;
DROP TABLE t1;
END|
CREATE PROCEDURE p2()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'SQLEXCEPTION caught' AS msg;
CREATE TEMPORARY TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
END;
SELECT * FROM t1;
SELECT * FROM t1;
DROP TEMPORARY TABLE t1;
END|
CREATE PROCEDURE p3()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'SQLEXCEPTION caught' AS msg;
CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b;
END;
SELECT * FROM v1;
SELECT * FROM v1;
DROP VIEW v1;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
CALL p2();
CALL p3();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
###########################################################################
--echo
--echo # 9. Stored program must be executed successfully when
--echo # - the stored program has an expression in one of the following
--echo # statements
--echo # - RETURN
--echo # - IF
--echo # - CASE
--echo # - WHILE
--echo # - UNTIL
--echo # - SET
--echo # - the expression depends on the meta-data of some table/view/temporary table;
--echo # - the meta-data of dependent object has changed in a compatible way.
--echo #
--echo # Note, that CASE-expression must be evaluated once even if (some)
--echo # CASE-expressions need to be re-parsed.
--echo #
--echo # 10. Subsequent executions of a stored program must fail when
--echo # - the stored program has an expression in one of the following
--echo # statements
--echo # - RETURN
--echo # - IF
--echo # - CASE
--echo # - WHILE
--echo # - UNTIL
--echo # - SET
--echo # - the expression depends on the meta-data of some table/view/temporary table;
--echo # - the meta-data of dependent object has changed in a non-compatible way.
--echo #
--echo # Note, that CASE-expression must be evaluated once even if (some)
--echo # CASE-expressions need to be re-parsed.
###########################################################################
--echo
--echo # Check IF-statement.
--echo
delimiter |;
# NOTE: check also that spaces (no spaces) don't matter.
CREATE PROCEDURE p1()
BEGIN
IF(SELECT * FROM t1)THEN
SELECT 1;
ELSE
SELECT 2;
END IF;
END|
CREATE PROCEDURE p2()
BEGIN
DECLARE v INT DEFAULT 1;
IF v * (SELECT * FROM t1) THEN
SELECT 1;
ELSE
SELECT 2;
END IF;
END|
CREATE FUNCTION f1() RETURNS INT
BEGIN
IF (SELECT * FROM t1) THEN
RETURN 1;
ELSE
RETURN 2;
END IF;
RETURN 3;
END|
CREATE FUNCTION f2() RETURNS INT
BEGIN
DECLARE v INT DEFAULT 1;
IF v * (SELECT * FROM t1) THEN
RETURN 1;
ELSE
RETURN 2;
END IF;
RETURN 3;
END|
delimiter ;|
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1);
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
--disable_view_protocol
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p1();
CALL p2();
SELECT f1();
SELECT f2();
--echo
UPDATE t1 SET a = 0;
--echo
CALL p1();
CALL p2();
SELECT f1();
SELECT f2();
--echo
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1;
--echo
--error ER_OPERAND_COLUMNS
CALL p1();
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
CALL p2();
--error ER_OPERAND_COLUMNS
SELECT f1();
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT f2();
--echo
ALTER TABLE t1 DROP COLUMN a;
--echo
CALL p1();
CALL p2();
SELECT f1();
SELECT f2();
--echo
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP FUNCTION f1;
DROP FUNCTION f2;
DROP TABLE t1;
###########################################################################
--echo
--echo # Check WHILE-statement.
--echo
delimiter |;
# NOTE: check also that spaces (no spaces) don't matter.
CREATE PROCEDURE p1(x INT)
BEGIN
WHILE(SELECT * FROM t1)DO
SELECT x;
UPDATE t1 SET a = x;
SET x = x - 1;
END WHILE;
END|
delimiter ;|
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (0);
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1(3);
UPDATE t1 SET a = 1;
CALL p1(3);
UPDATE t1 SET a = 1;
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1;
--error ER_OPERAND_COLUMNS
CALL p1(3);
ALTER TABLE t1 DROP COLUMN a;
# Column 'a' not found for the UPDATE statememnt.
--error ER_BAD_FIELD_ERROR
CALL p1(3);
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP TABLE t1;
###########################################################################
--echo
--echo # Check REPEAT-statement.
--echo
delimiter |;
# NOTE: check also that spaces (no spaces) don't matter.
CREATE PROCEDURE p1(x INT)
BEGIN
REPEAT
SELECT x;
UPDATE t1 SET a = x;
SET x = x - 1;
UNTIL(NOT (SELECT * FROM t1))END REPEAT;
END|
delimiter ;|
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (0);
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1(3);
UPDATE t1 SET a = 1;
CALL p1(3);
UPDATE t1 SET a = 1;
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
CALL p1(3);
ALTER TABLE t1 DROP COLUMN a;
# Column 'a' not found for the UPDATE statememnt.
--error ER_BAD_FIELD_ERROR
CALL p1(3);
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP TABLE t1;
###########################################################################
--echo
--echo # Check CASE-statement (round #1).
--echo
delimiter |;
# Check that metadata changes in WHEN-expressions are handled properly.
CREATE PROCEDURE p1()
BEGIN
CASE
WHEN (SELECT * FROM t1) = 1 THEN SELECT 'a1';
WHEN (SELECT * FROM t1) = 2 THEN SELECT 'a2';
WHEN (SELECT * FROM t1) = 3 THEN SELECT 'a3';
ELSE SELECT 'a4';
END CASE;
END|
# Check that metadata changes in CASE-expression, are handled properly.
CREATE PROCEDURE p2()
BEGIN
CASE (SELECT * FROM t1)
WHEN 1 THEN SELECT 'a1';
WHEN 2 THEN SELECT 'a2';
WHEN 3 THEN SELECT 'a3';
ELSE SELECT 'a4';
END CASE;
END|
delimiter ;|
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (0);
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p1();
CALL p2();
--echo
UPDATE t1 SET a = 3;
--echo
CALL p1();
CALL p2();
--echo
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2;
--echo
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
CALL p1();
--error ER_OPERAND_COLUMNS
CALL p2();
--echo
ALTER TABLE t1 DROP COLUMN a;
--echo
CALL p1();
CALL p2();
SET @@debug_dbug=@orig_dbug;
--echo
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
###########################################################################
--echo
--echo # Check CASE-statement (round #2).
--echo #
--echo # Check that CASE-expression is executed once even if the metadata, used
--echo # in a WHEN-expression, have changed.
--echo
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
delimiter |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
SET @x = @x + 1;
RETURN (SELECT a FROM t1);
END|
CREATE PROCEDURE p1()
BEGIN
CASE f1()
WHEN 1 THEN SELECT 'a1';
WHEN 2 THEN SELECT 'a2';
WHEN (SELECT * FROM t2) THEN SELECT 'subselect';
ELSE SELECT 'else';
END CASE;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
SET @x = 0;
CALL p1();
SELECT @x;
--echo
UPDATE t1 SET a = 3;
ALTER TABLE t2 ADD COLUMN b INT DEFAULT 3;
--echo
SET @x = 0;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
CALL p1();
SELECT @x;
--echo
ALTER TABLE t2 DROP COLUMN a;
--echo
SET @x = 0;
CALL p1();
SELECT @x;
SET @@debug_dbug=@orig_dbug;
--echo
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP TABLE t1;
DROP TABLE t2;
###########################################################################
--echo
--echo # Check DEFAULT clause.
--echo #
--echo
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DECLARE v INT DEFAULT (SELECT * FROM t1);
SELECT v;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p1();
--echo
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2;
--echo
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
CALL p1();
--echo
ALTER TABLE t1 DROP COLUMN a;
--echo
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
DROP PROCEDURE p1;
DROP TABLE t1;
###########################################################################
--echo
--echo # Check SET.
--echo #
--echo
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2(a INT);
INSERT INTO t2 VALUES (1);
delimiter |;
# Check SET for SP-variable.
CREATE PROCEDURE p1()
BEGIN
DECLARE x INT;
SET x = (SELECT * FROM t1);
SELECT x;
END|
# Check SET for user variable.
CREATE PROCEDURE p2()
BEGIN
SET @x = NULL;
SET @x = (SELECT * FROM t1);
SELECT @x;
END|
# Check SET for triggers.
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW
BEGIN
SET NEW.a = (SELECT * FROM t1) * 2;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p1();
--echo
CALL p2();
--echo
UPDATE t2 SET a = 10;
--echo
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2;
--echo
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
CALL p1();
--echo
--error ER_OPERAND_COLUMNS
CALL p2();
--echo
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
UPDATE t2 SET a = 20;
--echo
ALTER TABLE t1 DROP COLUMN a;
--echo
CALL p1();
--echo
CALL p2();
--echo
UPDATE t2 SET a = 30;
SET @@debug_dbug=@orig_dbug;
--echo
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
DROP TABLE t2;
###########################################################################
--echo
--echo # 11.1 If metadata of the objects (regular tables, temporary tables,
--echo # views), used in SELECT-statement changed between DECLARE CURSOR and
--echo # OPEN statements, the SELECT-statement should be re-parsed to use
--echo # up-to-date metadata.
--echo
--echo
--echo # - Regular table.
--echo
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DECLARE v INT;
DECLARE c CURSOR FOR SELECT * FROM t1;
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2;
ALTER TABLE t1 DROP COLUMN a;
OPEN c;
FETCH c INTO v;
CLOSE c;
SELECT v;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
DROP TABLE t1;
DROP PROCEDURE p1;
--echo
--echo # - Temporary table.
--echo
CREATE TEMPORARY TABLE t1(a INT);
INSERT INTO t1 VALUES (1);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DECLARE v INT;
DECLARE c CURSOR FOR SELECT * FROM t1;
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2;
ALTER TABLE t1 DROP COLUMN a;
OPEN c;
FETCH c INTO v;
CLOSE c;
SELECT v;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
DROP TEMPORARY TABLE t1;
DROP PROCEDURE p1;
# NOTE: ALTER VIEW is not available within Stored Programs.
--echo
--echo # 11.2 If the metadata changed between OPEN and FETCH or CLOSE
--echo # statements, those changes should not be noticed.
--echo
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DECLARE v INT;
DECLARE c CURSOR FOR SELECT * FROM t1;
OPEN c;
ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2;
ALTER TABLE t1 DROP COLUMN a;
FETCH c INTO v;
CLOSE c;
SELECT v;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
DROP TABLE t1;
DROP PROCEDURE p1;
--echo
--echo # 11.3 Re-parsing of the SELECT-statement should be made correctly
--echo # (in the correct parsing context) if the metadata changed between
--echo # DECLARE CURSOR and OPEN statements, and those statements reside in different
--echo # parsing contexts.
--echo
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
DECLARE f1 INT;
DECLARE f2 INT;
DECLARE f3 INT;
DECLARE x INT DEFAULT 1;
DECLARE y INT DEFAULT 2;
DECLARE c CURSOR FOR SELECT x, y, t1.a FROM t1;
ALTER TABLE t1 ADD COLUMN b INT;
BEGIN
DECLARE x INT DEFAULT 10;
DECLARE y INT DEFAULT 20;
OPEN c;
FETCH c INTO f1, f2, f3;
SELECT f1, f2, f3;
CLOSE c;
END;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
CALL p1();
SET @@debug_dbug=@orig_dbug;
--echo
DROP TABLE t1;
DROP PROCEDURE p1;
###########################################################################
## Other tests.
###########################################################################
--echo
--echo # Test procedure behaviour after view recreation.
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
# In result of dropping/creating a view sp_cache is invalidated.
# It means that the stored procedure p1 be evicted from sp_cache
# and loaded again on opening the routine. So, the statement
# SET @@debug_dbug='+d,check_sp_cache_not_invalidated'
# shouldn't be run here since it will hit DBUG_SUICIDE in the function
# sp_cache_flush_obsolete()
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE PROCEDURE p1()
SELECT * FROM v1;
CALL p1();
--echo # Alter underlying table and recreate the view.
ALTER TABLE t1 ADD COLUMN (b INT);
ALTER VIEW v1 AS SELECT * FROM t1;
--echo # And check whether the call of stored procedure handles it correctly.
CALL p1();
DROP VIEW v1;
DROP TABLE t1;
DROP PROCEDURE p1;
--echo # Test if metadata changes for temporary table is handled
--echo # correctly inside a stored procedure.
CREATE TEMPORARY TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
CREATE PROCEDURE p1()
SELECT * FROM t1;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1();
--echo # Test if added temporary table's column is recognized during
--echo # procedure invocation.
ALTER TABLE t1 ADD COLUMN (b INT);
CALL p1();
--echo # Test if dropped temporary table's column is not appeared
--echo # in procedure's result.
ALTER TABLE t1 DROP COLUMN a;
CALL p1();
SET @@debug_dbug=@orig_dbug;
DROP PROCEDURE p1;
DROP TABLE t1;
--echo # Test handle of metadata changes with stored function.
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
delimiter |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1;
RETURN 0;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
SELECT f1();
SELECT * FROM t1_result_set;
DROP TABLE t1_result_set;
--echo # Check if added column is noticed by invocation of stored function.
ALTER TABLE t1 ADD COLUMN (b INT);
SELECT f1();
SELECT * FROM t1_result_set;
DROP TABLE t1_result_set;
--echo # Check if dropped column is noticed by invocation of stored function.
ALTER TABLE t1 DROP COLUMN a;
SELECT f1();
SELECT * FROM t1_result_set;
SET @@debug_dbug=@orig_dbug;
DROP TABLE t1_result_set;
DROP TABLE t1;
DROP FUNCTION f1;
--echo # Test if table's recreation is handled correctly
--echo # inside a stored function.
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
delimiter |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1;
RETURN 0;
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
SELECT f1();
SELECT * FROM t1_result_set;
DROP TABLE t1_result_set;
--echo # Recreate table and check if it is handled correctly
--echo # by function invocation.
DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
SELECT f1();
SELECT * FROM t1_result_set;
SET @@debug_dbug=@orig_dbug;
DROP TABLE t1_result_set;
DROP FUNCTION f1;
DROP TABLE t1;
--echo # Test if changes in the view's metadata is handled
--echo # correctly by function call.
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
CREATE VIEW v1 AS SELECT * FROM t1;
delimiter |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM v1;
RETURN 0;
END|
delimiter ;|
SELECT f1();
SELECT * FROM t1_result_set;
DROP TABLE t1_result_set;
ALTER TABLE t1 ADD COLUMN (b INT);
ALTER VIEW v1 AS SELECT * FROM t1;
# In result of altering a view sp_cache is invalidated.
# It means that the stored function f1 be evicted from sp_cache
# and loaded again on opening the routine. So, the statement
# SET @@debug_dbug='+d,check_sp_cache_not_invalidated'
# shouldn't be run here since it will hit DBUG_SUICIDE in the function
# sp_cache_flush_obsolete()
SELECT f1();
SELECT * FROM t1_result_set;
DROP TABLE t1_result_set;
DROP TABLE t1;
DROP VIEW v1;
DROP FUNCTION f1;
--echo # Check if queried object's type substitution (table->view, view->table,
--echo # table->temp table, etc.) is handled correctly during invocation of
--echo # stored function/procedure.
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
delimiter |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1;
RETURN 0;
END|
CREATE PROCEDURE p1()
SELECT * FROM t1|
delimiter ;|
CALL p1();
SELECT f1();
SELECT * FROM t1_result_set;
DROP TABLE t1_result_set;
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# stored routine's body doesn't lead to eviction of
# the stored routine from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
CALL p1;
SELECT f1();
SELECT * FROM t1_result_set;
DROP TABLE t1_result_set;
SET @@debug_dbug=@orig_dbug;
--enable_view_protocol
DROP TABLE t1;
CREATE TABLE t2 (a INT);
INSERT INTO t2 VALUES (1), (2);
CREATE VIEW t1 AS SELECT * FROM t2;
CALL p1;
SELECT f1();
SELECT * FROM t1_result_set;
DROP TABLE t1_result_set;
DROP TABLE t2;
DROP VIEW t1;
DROP FUNCTION f1;
DROP PROCEDURE p1;
--echo # Test handle of metadata changes with triggers.
CREATE TABLE t1 (a INT);
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
SET new.a = new.a + 100;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# trigger's body doesn't lead to eviction of trigger
# from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
INSERT INTO t1 VALUES (1), (2);
SELECT * FROM t1;
--echo # Check if added table's column is handled correctly inside trigger.
ALTER TABLE t1 ADD COLUMN (b INT);
INSERT INTO t1 VALUES (3, 4);
SELECT * FROM t1;
SET @@debug_dbug=@orig_dbug;
DROP TRIGGER trg1;
DROP TABLE t1;
--echo # Test if deleted column is handled correctly by trigger invocation.
CREATE TABLE t1 (a INT, b INT);
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
SET new.a = new.a + 100;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# trigger's body doesn't lead to eviction of trigger
# from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
INSERT INTO t1 VALUES (1, 2), (3, 4);
SELECT * FROM t1;
ALTER TABLE t1 DROP COLUMN b;
INSERT INTO t1 VALUES (5);
SELECT * FROM t1;
SET @@debug_dbug=@orig_dbug;
DROP TRIGGER trg1;
DROP TABLE t1;
--echo # Check if server returns and error when was dropped a column
--echo # that is used inside a trigger body.
CREATE TABLE t1 (a INT, b INT);
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
SET new.a = new.a + 100;
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# trigger's body doesn't lead to eviction of trigger
# from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
INSERT INTO t1 VALUES (1, 2), (3, 4);
SELECT * FROM t1;
ALTER TABLE t1 DROP COLUMN a;
--error ER_BAD_FIELD_ERROR
INSERT INTO t1 VALUES (5);
SET @@debug_dbug=@orig_dbug;
DROP TRIGGER trg1;
DROP TABLE t1;
--echo
--echo # Check updateable views inside triggers.
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2(a INT);
INSERT INTO t2 VALUES (1);
CREATE VIEW v1 AS SELECT a FROM t1;
delimiter |;
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW
BEGIN
INSERT INTO v1 VALUES (NEW.a);
SET @x = (SELECT CHARSET(a) FROM v1 LIMIT 1);
END|
delimiter ;|
#
# Set the debug keyword 'check_sp_cache_not_invalidated'
# in order to check that re-parsing of statements inside
# trigger's body doesn't lead to eviction of trigger
# from sp_cache
#
SET @@debug_dbug='+d,check_sp_cache_not_invalidated';
--echo
SET @x = NULL;
UPDATE t2 SET a = 10;
SELECT * FROM v1;
SELECT @x;
--echo
ALTER TABLE t1 CHANGE COLUMN a a CHAR(2);
--echo
SET @x = NULL;
UPDATE t2 SET a = 20;
SELECT * FROM v1;
SELECT @x;
SET @@debug_dbug=@orig_dbug;
--echo
DROP TABLE t1;
DROP TABLE t2;
DROP VIEW v1;
--echo #
--echo # MDEV-31661: Assertion `thd->lex == sp_instr_lex' failed in LEX* sp_lex_instr::parse_expr(THD*, sp_head*, LEX*)
--echo #
--delimiter $
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE c CURSOR FOR SELECT * FROM t1;
OPEN c;
CLOSE c;
END;
$
--delimiter ;
--error ER_NO_SUCH_TABLE
CALL p1;
CREATE TABLE t1 (id INT);
CALL p1;
--echo # Second execution of the stored procedure p1() after the dependent
--echo # table t1 has been created resulted in assert failure for server built
--echo # with debug
CALL p1;
--echo # Clean up
DROP PROCEDURE p1;
DROP TABLE t1;
--echo #
--echo # MDEV-31799 Unexpected ER_TRG_NO_SUCH_ROW_IN_TRG and server crash after ALTER TABLE
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
CREATE TABLE t2 (b INT);
--echo # Check that AFTER DELETE trigger is re-compiled on changing table's metadata
CREATE TRIGGER tr AFTER DELETE ON t1 FOR EACH ROW INSERT INTO t2 (b) VALUES (OLD.a);
DELETE FROM t1 LIMIT 1;
SELECT * FROM t2;
ALTER TABLE t2 FORCE;
DELETE FROM t1 LIMIT 1;
SELECT * FROM t2;
DELETE FROM t1 LIMIT 1;
SELECT * FROM t2;
DROP TRIGGER tr;
--echo # Check that AFTER UPDATE trigger is re-compiled on changing table's metadata
CREATE TRIGGER tr AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (b) VALUES (OLD.a);
--echo # Remove records interted by AFTER DELETE trogger
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
INSERT INTO t1 VALUES (1);
UPDATE t1 SET a = 2;
--echo # Above statement should insert the row (1) into the table t2
--echo # Expected output contains one row: (1)
SELECT * FROM t2;
ALTER TABLE t2 FORCE;
--echo # The following statement should insert the row (2) into the table t2
UPDATE t1 SET a = 3;
--echo # Expected output contains two rows: (1), (2)
SELECT * FROM t2;
--echo # The following statement should insert the row (3) into the table t2
UPDATE t1 SET a = 5;
--echo # Expected output contains three rows: (1), (2), (3)
SELECT * FROM t2;
# Cleanup
DROP TABLE t1, t2;
--echo #
--echo # MDEV-33525: Recreate/reuse temporary table
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
--delimiter |
CREATE FUNCTION f1()
RETURNS INT
BEGIN
DECLARE res INT;
DECLARE t1_cur CURSOR FOR SELECT 100 FROM t1, t1_tmp;
CREATE TEMPORARY TABLE t1_tmp SELECT 1 a;
OPEN t1_cur;
CLOSE t1_cur;
DROP TEMPORARY TABLE t1_tmp;
RETURN 0;
END
|
--delimiter ;
SELECT f1();
--echo # Without the patch, the second call of f1 would result in error:
--echo # ER_NO_SUCH_TABLE (1146): Table 'test.t1' doesn't exist
SELECT f1();
--echo # Clean up
DROP FUNCTION f1;
DROP TABLE t1;
--echo #
--echo # MDEV-36390: Minor refactoring of the method get_expr_query at the classes sp_instr_cpush/sp_instr_cursor_copy_struct
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
--delimiter $
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE va INT;
# Check that the TAB character after the clause FOR is skipped and
# the body of cursor is remembered correctly for subsequent re-parsing
DECLARE cur CURSOR FOR SELECT a FROM t1;
OPEN cur;
FETCH cur INTO va;
SELECT va;
CLOSE cur;
END;
$
CREATE OR REPLACE PROCEDURE p2()
BEGIN
DECLARE va INT;
# Check that the newline character after the clause FOR is skipped and
# the body of cursor is remembered correctly for subsequent re-parsing
DECLARE cur CURSOR FOR
SELECT a FROM t1;
OPEN cur;
FETCH cur INTO va;
SELECT va;
CLOSE cur;
END;
$
CREATE OR REPLACE PROCEDURE p3()
BEGIN
DECLARE va INT;
# Check that C-style comment and the newline character after
# the clause FOR is skipped and the body of cursor is remembered
# correctly for subsequent re-parsing
DECLARE cur CURSOR FOR /* Explicit comment */
SELECT a FROM t1;
OPEN cur;
FETCH cur INTO va;
SELECT va;
CLOSE cur;
END;
$
CREATE OR REPLACE PROCEDURE p4()
BEGIN
DECLARE va INT;
# Check that SQL-style comment and the newline character after
# the clause FOR is skipped and the body of cursor is remembered
# correctly for subsequent re-parsing
DECLARE cur CURSOR FOR -- Explicit comment
SELECT a FROM t1;
OPEN cur;
FETCH cur INTO va;
SELECT va;
CLOSE cur;
END;
$
--delimiter ;
CALL p1();
CALL p2();
CALL p3();
CALL p4();
ALTER TABLE t1 COMMENT 'The Comment 1';
--echo # The following statements will run re-parsing of
--echo # cursor declaration statements inside the stored
--echo # procedures p1, p2, p3, p4.
CALL p1();
CALL p2();
CALL p3();
CALL p4();
--echo # Clean up
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;
DROP TABLE t1;
SET sql_mode = default;
--enable_ps2_protocol
Reference in a new issue View git blame Copy permalink