316 lines
9.2 KiB
Plaintext
316 lines
9.2 KiB
Plaintext
CREATE SCHEMA IF NOT EXISTS xtest;
|
|
USE xtest;
|
|
CREATE PROCEDURE recreate_tables()
|
|
BEGIN
|
|
CREATE TABLE IF NOT EXISTS xtable (phrase VARCHAR(30), prio INTEGER);
|
|
TRUNCATE TABLE xtable;
|
|
INSERT INTO xtable (phrase, prio) VALUES ('Alice has a cat', 0);
|
|
INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 1', 1);
|
|
INSERT INTO xtable (phrase, prio) VALUES ('Peppa has a teddy', 0);
|
|
INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 2', 2);
|
|
INSERT INTO xtable (phrase, prio) VALUES ('Row to delete', 3);
|
|
CREATE TABLE IF NOT EXISTS xtable_with_pk (p_id INTEGER AUTO_INCREMENT PRIMARY KEY, description varchar(40));
|
|
TRUNCATE TABLE xtable_with_pk;
|
|
INSERT INTO xtable_with_pk (description) VALUES ('MyRow');
|
|
END //
|
|
CREATE PROCEDURE one_resultsets()
|
|
BEGIN
|
|
SELECT phrase FROM xtable limit 3;
|
|
END //
|
|
CREATE PROCEDURE two_resultsets()
|
|
BEGIN
|
|
SELECT phrase FROM xtable limit 3;
|
|
SELECT prio FROM xtable limit 2;
|
|
END //
|
|
CREATE PROCEDURE two_resultsets_with_errors()
|
|
BEGIN
|
|
SELECT 1/prio as col1 FROM xtable;
|
|
SELECT 1/prio as col1 FROM xtable;
|
|
END //
|
|
CALL recreate_tables();
|
|
|
|
|
|
## I. Validate non dynamic SQL statement where Cursor.Fetch is not needed
|
|
#
|
|
# 1. Assert empty resultset, dont get any rows at Cursor.Open.
|
|
# 2. Assert resultset that has less rows than requested by Cursor.Open.
|
|
|
|
## II. Validate non dynamic SQL statement where Cursor.Fetch is needed to get all data
|
|
#
|
|
# 1. Assert empty resultset, still try to get at last one row at open
|
|
# 2. Assert resultset that has same amount of rows as requested
|
|
# 3. Assert resultset that has rows grater by one than requested
|
|
# 4. Assert resultset that has two times the amount of rows as requested
|
|
# 5. Assert resultset by fetching the resultset one by one row
|
|
# 6. Assert resultset by fetching the resultset and warnings generated by it
|
|
|
|
## III. Validate dynamic SQL statement
|
|
#
|
|
# 1. Assert that cursor open, fetches whole resultset
|
|
# a. fetch_rows in Cursor.Open is not set
|
|
# b. fetch_rows in Cursor.Open is set (value doesn't matter).
|
|
# 2. Assert that cursor open, fetches multiple resultsets
|
|
# 3. Assert that cursor open, fetches multiple resultsets with warnings
|
|
|
|
## IV. Validate notices
|
|
#
|
|
# 1. Insert stmt
|
|
# a. Table with primary key (expect notice GENERATED_INSERT_ID)
|
|
# b. Table without primary key (must not be generate GENERATED_INSERT_ID)
|
|
# 2. Delete stmt
|
|
# 3. Update stmt
|
|
|
|
|
|
#
|
|
# I.1
|
|
[PREPARE - SELECT * FROM xtable LIMIT 0]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# I.2
|
|
[PREPARE - SELECT * FROM xtable]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# II.1
|
|
[PREPARE - SELECT * FROM xtable LIMIT 0]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.FetchSuspended]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
[FETCH]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# II.2
|
|
[PREPARE - SELECT * FROM xtable LIMIT 5]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchSuspended]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
[FETCH]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# II.3
|
|
[PREPARE - SELECT * FROM xtable LIMIT 5]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchSuspended]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
[FETCH]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# II.4
|
|
[PREPARE - SELECT * FROM xtable LIMIT 4]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchSuspended]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
[FETCH]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# II.5
|
|
[PREPARE - SELECT * FROM xtable LIMIT 3]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchSuspended]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
[FETCH]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchSuspended]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
[FETCH]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchSuspended]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
[FETCH]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# II.6
|
|
[PREPARE - SELECT 1/prio as col1 FROM xtable]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.FetchSuspended]
|
|
[ASSERT notice warning - WARNING, 1365]
|
|
[ASSERT notice warning - WARNING, 1365]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
[FETCH]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchSuspended]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
[FETCH]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# III.1.a
|
|
[PREPARE - CALL one_resultsets()]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# III.1.b
|
|
[PREPARE - CALL one_resultsets()]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# III.2
|
|
[PREPARE - CALL two_resultsets()]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# III.3
|
|
[PREPARE - CALL two_resultsets_with_errors]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
|
|
[ASSERT Mysqlx.Resultset.ColumnMetaData]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.Row]
|
|
[ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
|
|
[ASSERT Mysqlx.Resultset.FetchDone]
|
|
[ASSERT notice warning - WARNING, 1365]
|
|
[ASSERT notice warning - WARNING, 1365]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# IV.1.a
|
|
[PREPARE - INSERT INTO xtest.xtable_with_pk(description) VALUE('MyCustomRow')]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
|
|
[ASSERT notice session state change - GENERATED_INSERT_ID, v_unsigned_int: 2]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# IV.1.b
|
|
[PREPARE - INSERT INTO xtest.xtable(phrase, prio) VALUE('MyCustomRow', 100)]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# IV.2
|
|
[PREPARE - DELETE FROM xtest.xtable WHERE prio=100]
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
|
|
#
|
|
# IV.3
|
|
[PREPARE - UPDATE xtest.xtable SET phrase='New']
|
|
RUN recvok
|
|
[EXECUTE]
|
|
[ASSERT notice session state change - PRODUCED_MESSAGE, type: V_STRING]
|
|
[ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:5]
|
|
[ASSERT Mysqlx.Sql.StmtExecuteOk]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
DROP SCHEMA IF EXISTS xtest;
|