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;