#
# MDEV-16708: Unsupported commands for prepared statements
#
SET @save_storage_engine= @@default_storage_engine;
SET default_storage_engine= InnoDB;
# Test case 1: Check that the statement 'LOAD DATA' is supported
# by prepared statements
# First, set up environment for use by the statement 'LOAD DATA'
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
COMMIT;
SELECT * INTO OUTFILE 'load.data' FROM t1;
LOAD DATA INFILE 'load.data' INTO TABLE t1;
SELECT * FROM t1;
a
1
1
# Clean up
DROP TABLE t1;
# Test case 2: Check that the statements 'LOCK TABLE', 'UNLOCK TABLES'
# are supported by prepared statements
CREATE TABLE t1 (a INT);
LOCK TABLE t1 READ;
UNLOCK TABLE;
LOCK TABLE t1 WRITE;
# Clean up
UNLOCK TABLE;
DROP TABLE t1;
# Test case 3: Check that the statement 'USE' is supported by
# prepared statements
CREATE DATABASE mdev_16708_db;
USE mdev_16708_db;
# Check that the current database has been changed
SELECT DATABASE();
DATABASE()
mdev_16708_db
# Clean up
USE test;
DROP DATABASE mdev_16708_db;
# Test case 4: Check that the statement 'ALTER DATABASE' is supported
# by prepared statements
CREATE DATABASE mdev_16708_db;
ALTER DATABASE mdev_16708_db COMMENT 'New comment on database';
# Clean up
DROP DATABASE mdev_16708_db;
# Test case 5: Check that the statements 'CREATE FUNCTION/ALTER FUNCTION/
# DROP FUNCTION' are supported by prepared statements
CREATE FUNCTION f1() RETURNS INT RETURN 1;
ALTER FUNCTION f1 SQL SECURITY INVOKER;
DROP FUNCTION f1;
# Test case 6: Check that the statements 'CHECK TABLE' is supported
# by prepared statements
CREATE TABLE t1 (a INT);
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
# Clean up
DROP TABLE t1;
# Test case 7: Check that the statements BEGIN/SAVEPOINT/
# RELEASE SAVEPOINT is supported by prepared statements
# Set up environmentr for the test case
CREATE TABLE t1 (a INT);
BEGIN;
INSERT INTO t1 VALUES (1);
SAVEPOINT s1;
INSERT INTO t1 VALUES (2);
# Expected rows: '1' and '2'
SELECT * FROM t1;
a
1
2
# Rollback the last row inserted ('2')
ROLLBACK TO SAVEPOINT s1;
# Expected output from t1 after transaction was rolled back
# to the savepoint is '1'. If it is case then the statement SAVEPOINT
# was handled successfully with prepared statement
SELECT * FROM t1;
a
1
RELEASE SAVEPOINT s1;
# Clean up
DROP TABLE t1;
# Test case 8: Check that the statements 'PURGE BINARY LOGS BEFORE'
# is supported by prepared statements
PURGE BINARY LOGS BEFORE '2020-11-17';
# Check that the statements 'PURGE BINARY LOGS TO' is supported by
# prepared statements
PURGE BINARY LOGS TO 'mariadb-bin.000063';
# Test case 9: Check that the statements 'HANDLER OPEN/HANDLER READ/
# HANDLER CLOSE' are supported by prepared statements
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (1);
COMMIT;
HANDLER t1 OPEN;
HANDLER t1 READ FIRST;
a
1
HANDLER t1 READ NEXT;
a
1
HANDLER t1 CLOSE;
# Clean up
DROP TABLE t1;
# Test case 10: Check that the statements 'HELP'
# is supported by prepared statements
INSERT INTO mysql.help_topic VALUES (0, 'Tamagotchi', 0, 'This digital pet is not a KB article', 'no example', 'https://tamagotchi.com/');
HELP `Tamagotchi`;
name	description	example
Tamagotchi	This digital pet is not a KB article	no example
DELETE FROM mysql.help_topic WHERE help_topic_id = 0;
# Test case 11: Check that the statements CREATE/ALTER/DROP PROCEDURE
# are supported by prepared statements
CREATE PROCEDURE p1() SET @a=1;
ALTER PROCEDURE p1 SQL SECURITY INVOKER;
DROP PROCEDURE p1;
# Test case 12: Check that the statement 'CALL' is supported
# by prepared statements.
CREATE PROCEDURE p1() SET @a=1;
CALL p1();
# Check that the @a variable has been set
SELECT @a;
@a
1
DROP PROCEDURE p1;
# Test case 13: Check that the statements PREPARE FROM/EXECUTE/
# DEALLOCAT PREPARE can be executed as prepared statements.
PREPARE stmt_1 FROM 'SELECT 1';
# Now execute the prepared statement with the name stmt_1
# It is expected that output contains the single row '1'
EXECUTE stmt_1;
1
1
DEALLOCATE PREPARE stmt_1;
# Test case 14: Check that the statement 'CREATE VIEW' can be executed
# as a prepared statement.
# Create environment for the test case
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
COMMIT;
CREATE VIEW v1 AS SELECT * FROM t1;
# Query the view. Expected result is the row '1'
SELECT * FROM v1;
a
1
# Clean up
DROP VIEW v1;
DROP TABLE t1;
# Test case 15: Check that the statements CREATE/DROP TRIGGER can be executed
# as prepared statements.
CREATE TABLE t1 (a INT);
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a=1;
DROP TRIGGER trg1;
DROP TABLE t1;
# Test case 16: Check that XA related SQL statements can be executed in
# as prepared statements.
# Create the table t1 used by XA transaction.
CREATE TABLE t1 (a INT);
XA START 'xid1';
INSERT INTO t1 VALUES (1);
XA END 'xid1';
XA PREPARE 'xid1';
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	4	0	xid1
XA COMMIT 'xid1';
# Query the table t1 to check that it contains a record inserted by XA
# transaction just committed.
SELECT * FROM t1;
a
1
# Check that XA ROLLBACK is supported by prepared statements
# First, clean up the table t1 that was filled by just
# committed XA transaction
TRUNCATE TABLE t1;
XA START 'xid1';
INSERT INTO t1 VALUES (1);
XA END 'xid1';
XA PREPARE 'xid1';
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	4	0	xid1
XA ROLLBACK 'xid1';
# Query the table t1 to check that it doesn't contain a record
# inserted by XA transaction just rollbacked.
SELECT * FROM t1;
a
# Clean up
DROP TABLE t1;
# Test case 17: Check that the statements CREATE SERVER/ALTER SERVER/
# DROP SERVER can be executed
# as a prepared statement.
CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'u1', HOST '127.0.0.1');
ALTER SERVER s OPTIONS (USER 'u2');
DROP SERVER s;
# Test Test case 21: Check the statements 'BACKUP'/'BACKUP STAGE'
# can be executed as a prepared statement
CREATE TABLE t1 (a INT);
BACKUP LOCK t1;
BACKUP UNLOCK;
BACKUP STAGE START;
BACKUP STAGE BLOCK_COMMIT;
BACKUP STAGE END;
DROP TABLE t1;
# Test case 22: Check the the statement 'GET DIAGNOSTICS'
# can be executed as a prepared statement
# Query from non existent table to fill the diagnostics area with information
SELECT * FROM non_existent_table_1;
ERROR 42S02: Table 'test.non_existent_table_1' doesn't exist
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
# Check that information from diagnostics area has been retrieved
SELECT @sqlstate, @errno, @text;
@sqlstate	@errno	@text
42S02	1146	Table 'test.non_existent_table_1' doesn't exist
# Clean up
# Test case 23: Check that the statements SIGNAL and RESIGNAL can be executed as
# a prepared statement
SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='Hello, world!';
ERROR 45000: Hello, world!
RESIGNAL SET MESSAGE_TEXT = 'New error message';
ERROR 0K000: RESIGNAL when handler not active
SET default_storage_engine= @save_storage_engine;