Debian Codegraph @

mysql-test/suite/x/r/cursor_sundries.result

raw
1 CREATE SCHEMA IF NOT EXISTS xtest;
2 USE xtest;
3 CREATE TABLE xtable (phrase VARCHAR(30));
4 INSERT INTO xtable (phrase) VALUES ('Alice has a cat');
5 INSERT INTO xtable (phrase) VALUES ('Bob has a dog');
6
7 ## 1. Verify prep-stat status variables that are set to zero
8 ## 2. Execute cursor open
9 # a. success for open with one parameter
10 # b. success for open with two parameters (more than needed)
11 # c. fails when no parameter was supplied, try to fetch using failed cursor_id
12 # d. fails when using invalid cursor ID, try to fetch using failed cursor_id
13 ## 3. Close cursor
14 # a. success when using ID that was already opened
15 # b. fails when using ID that was already deallocated
16 # c. fails when using ID that was never opened
17 ## 4. Mixed tests
18 # a. open(curID1), close(curID1), open(curID1) - reuse already closes ID
19 # b. open(curID1), open(curID1) - open implicit closes previous cursor
20 # c. open(curID1, stmtID1), open(curID2, stmtID1) - curID2 closes curID1 because they use same stmtID
21 # d. prep(stmtID1), open(curID1, stmtID1), deallocate(stmtID1), failed_fetch(curID1) - closure of prepared-statement, closes the cursor
22 ## 5. Assert status variables for new session, they should be at zero
23
24 1. Verify cursor status variables
25 ==================================
26 Verify [Status variable "Mysqlx_prep_prepare" needs to have a value of 0]
27 Verify [Status variable "Mysqlx_cursor_open" needs to have a value of 0]
28 Verify [Status variable "Mysqlx_cursor_close" needs to have a value of 0]
29 Verify [Status variable "Mysqlx_cursor_fetch" needs to have a value of 0]
30
31 2.a. Open cursor with valid stmt
32 =================================
33 RUN recvok
34 Verify [Status variable "Mysqlx_prep_prepare" needs to have a value of 1]
35 count('*')
36 command ok
37 Verify [Status variable "Mysqlx_cursor_open" needs to have a value of 1]
38
39 2.b. Open cursor with valid stmt (args more than needed)
40 =========================================================
41 count('*')
42 command ok
43 Variable_name Value
44 Mysqlx_cursor_open 2
45 0 rows affected
46 Verify [Status variable "Mysqlx_cursor_open" needs to have a value of 2]
47
48 2.c. Fails open cursor with valid stmt (missing arg)
49 =====================================================
50 Got expected error: There is no argument for statement placeholder at position: 0 (code 5134)
51 Got expected error: Cursor with ID=102 was not opened. (code 5111)
52 Verify [Status variable "Mysqlx_cursor_open" needs to have a value of 3]
53 Verify [Status variable "Mysqlx_cursor_fetch" needs to have a value of 1]
54
55 2.d. Fails open cursor with invalid stmt
56 =========================================
57 Got expected error: Statement with ID=200 was not prepared. (code 5110)
58 Got expected error: Cursor with ID=103 was not opened. (code 5111)
59 Verify [Status variable "Mysqlx_cursor_open" needs to have a value of 4]
60
61 3.a. Close already opened cursor
62 =================================
63 RUN recvok
64 1
65 command ok
66 RUN recvok
67 Verify [Status variable "Mysqlx_cursor_close" needs to have a value of 1]
68
69 3.b. Fails close cursor (bad id) / try to close second time
70 ============================================================
71 Got expected error:
72 Mysqlx.Error {
73 severity: ERROR
74 code: 5111
75 msg: "Cursor with ID=100 was not opened."
76 sql_state: "HY000"
77 }
78
79 Verify [Status variable "Mysqlx_cursor_close" needs to have a value of 2]
80
81 3.c. Fails close cursor (bad id/never used)
82 =============================================
83 Got expected error:
84 Mysqlx.Error {
85 severity: ERROR
86 code: 5111
87 msg: "Cursor with ID=10000 was not opened."
88 sql_state: "HY000"
89 }
90
91 Verify [Status variable "Mysqlx_cursor_close" needs to have a value of 3]
92
93 4.a. Reuse deallocated cursor ID
94 =================================
95 RUN recvok
96 RUN recvok
97 3
98 command ok
99 RUN recvok
100 4
101 command ok
102
103 4.b. Reuse ID without direct deallocation
104 ==========================================
105 RUN recvok
106 col1
107 command ok
108 col1
109 command ok
110
111 4.c. Same stmt can't be opened by cursor twice
112 ===============================================
113 RUN recvok
114 col1
115 command ok
116 col1
117 command ok
118
119 command ok
120 Got expected error: Cursor with ID=106 was not opened. (code 5111)
121 Verify [Status variable "Mysqlx_cursor_fetch" needs to have a value of 4]
122
123 4.d. Closure of prepared-statement, closes the cursor
124 ======================================================
125 RUN recvok
126 col1
127 command ok
128 Got expected error: Cursor with ID=107 was not opened. (code 5111)
129
130 5. Assert status variables for new session, they should be at zero
131 ===================================================================
132 connecting...
133 active session is now 's1'
134 Verify [Status variable "Mysqlx_prep_prepare" needs to have a value of 0]
135 Verify [Status variable "Mysqlx_cursor_open" needs to have a value of 0]
136 Verify [Status variable "Mysqlx_cursor_close" needs to have a value of 0]
137 Verify [Status variable "Mysqlx_cursor_fetch" needs to have a value of 0]
138 closing session s1
139 Mysqlx.Ok {
140 msg: "bye!"
141 }
142 ok
143 # Syncing with status [ 'Mysqlx_connections_closed' == 2 ]
144 # Syncing with status [ 'Mysqlx_sessions' == 0 ]
145
146 ## Assert global variables
147 include/assert.inc [Global plugin variable Mysqlx_cursor_open must match number of send Mysqlx.Cursor.Open messages]
148 include/assert.inc [Global plugin variable Mysqlx_cursor_close must match number of send Mysqlx.Cursor.Close messages]
149 include/assert.inc [Global plugin variable Mysqlx_cursor_fetch must match number of send Mysqlx.Cursor.Fetch messages]
150 include/assert.inc [Global system status Prepared_stmt_count must be set to zero, all prep-stmt must be deallocated.]