mariadb/mysql-test/main/ps_missed_cmds_bin_prot.test

--echo #
--echo # MDEV-16708: Unsupported commands for prepared statements
--echo #

if (`SELECT $PS_PROTOCOL = 0`)
{
   --skip Need ps-protocol
}

--source include/have_innodb.inc

SET @save_storage_engine= @@default_storage_engine;
SET default_storage_engine= InnoDB;

--echo # Test case 1: Check that the statement 'LOAD DATA' is supported
--echo # by prepared statements

--echo # First, set up environment for use by the statement 'LOAD DATA'
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
COMMIT;
--disable_ps2_protocol
SELECT * INTO OUTFILE 'load.data' FROM t1;
--enable_ps2_protocol

LOAD DATA INFILE 'load.data' INTO TABLE t1;
SELECT * FROM t1;
--echo # Clean up
DROP TABLE t1;
--let $datadir= `select @@datadir`
--remove_file $datadir/test/load.data

--echo # Test case 2: Check that the statements 'LOCK TABLE', 'UNLOCK TABLES'
--echo # are supported by prepared statements
CREATE TABLE t1 (a INT);

LOCK TABLE t1 READ;
UNLOCK TABLE;

LOCK TABLE t1 WRITE;
--echo # Clean up
UNLOCK TABLE;
DROP TABLE t1;

--echo # Test case 3: Check that the statement 'USE' is supported by
--echo # prepared statements

CREATE DATABASE mdev_16708_db;
USE mdev_16708_db;

--echo # Check that the current database has been changed
--disable_service_connection
SELECT DATABASE();
--enable_service_connection

--echo # Clean up
USE test;
DROP DATABASE mdev_16708_db;

--echo # Test case 4: Check that the statement 'ALTER DATABASE' is supported
--echo # by prepared statements
CREATE DATABASE mdev_16708_db;
ALTER DATABASE mdev_16708_db COMMENT 'New comment on database';

--echo # Clean up
DROP DATABASE mdev_16708_db;

--echo # Test case 5: Check that the statements 'CREATE FUNCTION/ALTER FUNCTION/
--echo # DROP FUNCTION' are supported by prepared statements
CREATE FUNCTION f1() RETURNS INT RETURN 1;

ALTER FUNCTION f1 SQL SECURITY INVOKER;

DROP FUNCTION f1;

--echo # Test case 6: Check that the statements 'CHECK TABLE' is supported
--echo # by prepared statements
CREATE TABLE t1 (a INT);
CHECK TABLE t1;
--echo # Clean up
DROP TABLE t1;

--echo # Test case 7: Check that the statements BEGIN/SAVEPOINT/
--echo # RELEASE SAVEPOINT is supported by prepared statements

--echo # Set up environmentr for the test case
CREATE TABLE t1 (a INT);

--disable_view_protocol
BEGIN;

INSERT INTO t1 VALUES (1);

SAVEPOINT s1;

INSERT INTO t1 VALUES (2);
--echo # Expected rows: '1' and '2'
SELECT * FROM t1;
--echo # Rollback the last row inserted ('2')
ROLLBACK TO SAVEPOINT s1;
--echo # Expected output from t1 after transaction was rolled back
--echo # to the savepoint is '1'. If it is case then the statement SAVEPOINT
--echo # was handled successfully with prepared statement
SELECT * FROM t1;

RELEASE SAVEPOINT s1;
--enable_view_protocol

--echo # Clean up
DROP TABLE t1;

--echo # Test case 8: Check that the statements 'PURGE BINARY LOGS BEFORE'
--echo # is supported by prepared statements
PURGE BINARY LOGS BEFORE '2020-11-17';

--echo # Check that the statements 'PURGE BINARY LOGS TO' is supported by
--echo # prepared statements
PURGE BINARY LOGS TO 'mariadb-bin.000063';

--echo # Test case 9: Check that the statements 'HANDLER OPEN/HANDLER READ/
--echo # 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;
HANDLER t1 READ NEXT;
HANDLER t1 CLOSE;

--echo # Clean up
DROP TABLE t1;

--echo # Test case 10: Check that the statements 'HELP'
--echo # is supported by prepared statements
# avoid existing articles that may get updated.
INSERT INTO mysql.help_topic VALUES (0, 'Tamagotchi', 0, 'This digital pet is not a KB article', 'no example', 'https://tamagotchi.com/');
HELP `Tamagotchi`;
DELETE FROM mysql.help_topic WHERE help_topic_id = 0;

--echo # Test case 11: Check that the statements CREATE/ALTER/DROP PROCEDURE
--echo # are supported by prepared statements
CREATE PROCEDURE p1() SET @a=1;
ALTER PROCEDURE p1 SQL SECURITY INVOKER;
DROP PROCEDURE p1;

--echo # Test case 12: Check that the statement 'CALL' is supported
--echo # by prepared statements.

CREATE PROCEDURE p1() SET @a=1;
CALL p1();

--echo # Check that the @a variable has been set
SELECT @a;
DROP PROCEDURE p1;

--echo # Test case 13: Check that the statements PREPARE FROM/EXECUTE/
--echo # DEALLOCAT PREPARE can be executed as prepared statements.
PREPARE stmt_1 FROM 'SELECT 1';

--echo # Now execute the prepared statement with the name stmt_1
--echo # It is expected that output contains the single row '1'
EXECUTE stmt_1;

DEALLOCATE PREPARE stmt_1;

--echo # Test case 14: Check that the statement 'CREATE VIEW' can be executed
--echo # as a prepared statement.
--echo # Create environment for the test case
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
COMMIT;

CREATE VIEW v1 AS SELECT * FROM t1;
--echo # Query the view. Expected result is the row '1'
SELECT * FROM v1;
--echo # Clean up
DROP VIEW v1;
DROP TABLE t1;

--echo # Test case 15: Check that the statements CREATE/DROP TRIGGER can be executed
--echo # 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;

--echo # Test case 16: Check that XA related SQL statements can be executed in
--echo # as prepared statements.
--echo # 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;
XA COMMIT 'xid1';
--echo # Query the table t1 to check that it contains a record inserted by XA
--echo # transaction just committed.
SELECT * FROM t1;

--echo # Check that XA ROLLBACK is supported by prepared statements

--echo # First, clean up the table t1 that was filled by just
--echo # committed XA transaction
TRUNCATE TABLE t1;
XA START 'xid1';
INSERT INTO t1 VALUES (1);
XA END 'xid1';
XA PREPARE 'xid1';
XA RECOVER;
XA ROLLBACK 'xid1';

--echo # Query the table t1 to check that it doesn't contain a record
--echo # inserted by XA transaction just rollbacked.
SELECT * FROM t1;

--echo # Clean up
DROP TABLE t1;

--echo # Test case 17: Check that the statements CREATE SERVER/ALTER SERVER/
--echo # DROP SERVER can be executed
--echo # 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;

--echo # Test Test case 21: Check the statements 'BACKUP'/'BACKUP STAGE'
--echo # 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;

--echo # Test case 22: Check the the statement 'GET DIAGNOSTICS'
--echo # can be executed as a prepared statement

--echo # Query from non existent table to fill the diagnostics area with information
--error ER_NO_SUCH_TABLE
SELECT * FROM non_existent_table_1;
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
--echo # Check that information from diagnostics area has been retrieved
SELECT @sqlstate, @errno, @text;
--echo # Clean up

--echo # Test case 23: Check that the statements SIGNAL and RESIGNAL can be executed as
--echo # a prepared statement

--error 30001
SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='Hello, world!';

--error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER
RESIGNAL SET MESSAGE_TEXT = 'New error message';

--echo # Test Test case 29: Check the statements 'CREATE/ALTER/DROP TABLEPSPACE'
--echo # can be executed as a prepared statement

# Since MariaDB supports for tablespaces only on syntax level disable warnings
# before run CREATE/ALTER/DROP TABLESPACE statements in order to exclude
# the following in result output
#   Warning 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE
--disable_warnings

CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;
ALTER TABLESPACE ts1 ADD DATAFILE 'ts1_1.ibd' ENGINE=InnoDB;
DROP TABLESPACE ts1 ENGINE=InnoDB;

--enable_warnings

SET default_storage_engine= @save_storage_engine;