208 lines
6.0 KiB
PHP
208 lines
6.0 KiB
PHP
|
|
--write_file $MYSQL_TMP_DIR/resultset.xpl
|
|
-->import assert_messages.macro
|
|
-->import assert_notices.macro
|
|
|
|
##
|
|
## Purpose of this test is:
|
|
#
|
|
# * validating that resultsets are in align to the UML flow:
|
|
#
|
|
# ...
|
|
# loop has more resultsets or not at end of fetch
|
|
# group resultset
|
|
# loop has more columns
|
|
# server --> client: ColumnMetaData
|
|
# end
|
|
# loop has more rows
|
|
# server --> client: Row
|
|
# end
|
|
# end
|
|
# alt has more resultsets
|
|
# server --> client: FetchDoneMoreResultsets
|
|
# end
|
|
# end
|
|
# loop has more OUT-paramsets or not at end of fetch
|
|
# server --> client: FetchDoneMoreOutParams
|
|
# group resultset
|
|
# loop has more columns
|
|
# server --> client: ColumnMetaData
|
|
# end
|
|
# loop has more rows
|
|
# server --> client: Row
|
|
# end
|
|
# end
|
|
# end
|
|
# alt at end of all resultsets
|
|
# server --> client: FetchDone
|
|
# else cursor is opened
|
|
# server --> client: FetchSuspended
|
|
# end
|
|
# ...
|
|
#
|
|
# * expecting following notices for SQL statement:
|
|
#
|
|
# |Notice\Stmt |Select |Insert |Update |Delete |
|
|
# |-----------------------|-------|-------|-------|-------|
|
|
# |ROWS_AFFECTED |X(0) |X |X |X |
|
|
# |PRODUCED_MESSAGE | | |X | |
|
|
# |GENERATED_INSERT_ID | |X(PK) | | |
|
|
# |GENERATED_DOCUMENT_IDS | | | | |
|
|
#
|
|
-->echo
|
|
-->echo
|
|
-->echo ## I. Validate simple resultsets
|
|
-->echo #
|
|
-->echo # 1. Assert resultset for delete queries
|
|
-->echo # 2. Assert resultset for update queries
|
|
-->echo # 3. Assert resultset for insert queries (no auto_inc column)
|
|
-->echo # 4. Assert resultset for insert queries (has auto_inc column)
|
|
-->echo # 5. Assert resultset for select queries
|
|
-->echo
|
|
-->echo ## II. Validate simple multi-resultsets
|
|
-->echo #
|
|
-->echo # 1. Assert two resultsets for select queries
|
|
-->echo # 2. Assert three resultsets for select queries
|
|
-->echo
|
|
-->echo ## III. Validate simple multi/resultsets with warnings
|
|
-->echo #
|
|
-->echo # 1. Assert one resultsets for select queries
|
|
-->echo # 2. Assert two resultsets for select queries
|
|
|
|
|
|
-->echo
|
|
-->echo #
|
|
-->echo # I.1
|
|
stmtsql DELETE FROM xtest.xtable WHERE prio=4 or prio=3;
|
|
callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:2;
|
|
callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
|
|
|
|
|
|
-->echo
|
|
-->echo #
|
|
-->echo # I.2
|
|
stmtsql UPDATE xtest.xtable SET prio=prio-1;
|
|
callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:3;
|
|
callmacro Assert_notice_session_state PRODUCED_MESSAGE type: V_STRING;
|
|
callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
|
|
|
|
|
|
-->echo
|
|
-->echo #
|
|
-->echo # I.3
|
|
stmtsql INSERT INTO xtest.xtable(phrase, prio) VALUE('Bob has a dog 2', 3);
|
|
callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1;
|
|
callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
|
|
|
|
|
|
-->echo
|
|
-->echo #
|
|
-->echo # I.4
|
|
stmtsql INSERT INTO xtest.xtable_with_pk(description) VALUE('MyCustomRow');
|
|
callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1;
|
|
callmacro Assert_notice_session_state GENERATED_INSERT_ID v_unsigned_int: 2;
|
|
callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
|
|
|
|
|
|
-->echo
|
|
-->echo #
|
|
-->echo # I.5
|
|
stmtsql SELECT phrase FROM xtest.xtable;
|
|
callmacro Assert_metadata BYTES "phrase";
|
|
callmacro Assert_rows 4;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDone;
|
|
callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
|
|
callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
|
|
|
|
|
|
-->echo
|
|
-->echo #
|
|
-->echo # II.1
|
|
stmtsql CALL two_resultsets;
|
|
callmacro Assert_metadata BYTES "phrase";
|
|
callmacro Assert_rows 3;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
|
|
callmacro Assert_metadata SINT "prio";
|
|
callmacro Assert_rows 2;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDone;
|
|
callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
|
|
callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
|
|
|
|
|
|
-->echo
|
|
-->echo #
|
|
-->echo # II.2
|
|
stmtsql CALL three_resultsets;
|
|
callmacro Assert_metadata BYTES "phrase";
|
|
callmacro Assert_rows 3;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
|
|
callmacro Assert_metadata SINT "prio";
|
|
callmacro Assert_rows 2;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
|
|
callmacro Assert_metadata SINT "prio";
|
|
callmacro Assert_rows 3;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDone;
|
|
callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
|
|
callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
|
|
|
|
|
|
-->echo
|
|
-->echo #
|
|
-->echo # III.1
|
|
stmtsql SELECT 1/prio as col1 FROM xtest.xtable;
|
|
callmacro Assert_metadata DECIMAL "col1";
|
|
callmacro Assert_rows 4;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDone;
|
|
## ER_DIVISION_BY_ZERO == 1365
|
|
callmacro Assert_notice_warning WARNING 1365;
|
|
callmacro Assert_notice_warning WARNING 1365;
|
|
callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
|
|
callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
|
|
|
|
|
|
-->echo
|
|
-->echo #
|
|
-->echo # III.2
|
|
stmtsql CALL two_resultsets_with_errors;
|
|
callmacro Assert_metadata DECIMAL "col1";
|
|
callmacro Assert_rows 4;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
|
|
callmacro Assert_metadata DECIMAL "col1";
|
|
callmacro Assert_rows 4;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
|
|
callmacro Assert_message Mysqlx.Resultset.FetchDone;
|
|
## ER_DIVISION_BY_ZERO == 1365
|
|
callmacro Assert_notice_warning WARNING 1365;
|
|
callmacro Assert_notice_warning WARNING 1365;
|
|
callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
|
|
callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
|
|
|
|
EOF
|
|
|
|
source sample_tables.inc;
|
|
|
|
DELIMITER //;
|
|
|
|
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 //
|
|
|
|
CREATE PROCEDURE three_resultsets()
|
|
BEGIN
|
|
SELECT phrase FROM xtable limit 3;
|
|
SELECT prio FROM xtable limit 2;
|
|
SELECT prio FROM xtable ORDER BY prio limit 3;
|
|
END //
|
|
|
|
DELIMITER ;//
|