Debian Codegraph @

mysql-test/suite/x/t/update_sql_o.test

raw
1 ########### ../t/update_sql_o.test #####################
2 ### #
3 ### This test runs aims to run UPDATE sql statements #
4 ### variant with mysqlxtest client. #
5 ### Test covers #
6 ### - update with different operators #
7 ### - update on multiple rows #
8 ### #
9 ########################################################################
10 #
11
12 --echo =============================================
13 --echo SQL UPDATE SCENARIOS
14 --echo =============================================
15 --echo
16
17 --echo ================================================================================
18 --echo PREAMBLE
19 --echo ================================================================================
20 --replace_regex /\.dll/.so/
21 --source include/xplugin_preamble.inc
22 create user user2@localhost identified by 'user2';
23 grant all on *.* to user2@localhost;
24
25 ## TEST STARTS HERE
26 --echo ================================================================================
27 --echo TEST START
28 --echo ================================================================================
29 --write_file $MYSQL_TMP_DIR/mysqlx-update_sql.xpl
30 -->sql
31 DROP SCHEMA if EXISTS mysqlxplugin;
32 CREATE SCHEMA mysqlxplugin DEFAULT CHARSET='utf8';
33 USE mysqlxplugin;
34
35 CREATE TABLE categories (
36 CategoryID int NOT NULL AUTO_INCREMENT,
37 CategoryName varchar(100),
38 CategoryDescription varchar(200),
39 CategoryIMEI tinyint,
40 CategoryDecimal decimal(5,2),
41 PRIMARY key (CategoryID)
42 );
43
44 INSERT INTO categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
45 VALUES
46 (1,'Sports','Sports related category',1,235.15),
47 (2,'Entertaiment','Entertaiment related category',2,235.15),
48 (3, 'Home','Home related category',3,235.15),
49 (4, 'Kitchen','Kitchen related category',4,235.15);
50
51 ## Update varchar
52 UPDATE categories SET CategoryName = 'Sports Update' where CategoryID =1;
53
54 ## Update Tiny Int value
55 UPDATE categories SET CategoryIMEI = 110 where CategoryID = 1;
56
57 ## Update Decimal value
58 UPDATE categories SET CategoryDecimal = 613.47 where CategoryID =1;
59
60 ## Corner Values
61 ## Update with Quotes value
62 UPDATE categories SET
63 CategoryName='"Quotes"',
64 CategoryDescription='Quote Categories',
65 CategoryIMEI = 3,
66 CategoryDecimal = 235.15
67 WHERE CategoryID = 3;
68
69 ## Update with NULL values
70 UPDATE categories SET
71 CategoryName=NULL,
72 CategoryDescription=NULL,
73 CategoryIMEI = NULL,
74 CategoryDecimal = NULL
75 WHERE CategoryID = 4;
76
77 ## Update with Corner decimal values
78 UPDATE categories SET CategoryDecimal = 999.99
79 WHERE CategoryID = 3;
80
81 UPDATE categories SET CategoryDecimal = -999.99
82 WHERE CategoryID = 3;
83
84 ## Update with Corner tiny int value
85 UPDATE categories SET CategoryIMEI = 127
86 WHERE CategoryID = 4;
87
88 UPDATE categories SET CategoryIMEI = -128
89 WHERE CategoryID = 4;
90 -->endsql
91 EOF
92
93 --exec $MYSQLXTEST -u user2 --password='user2' --file=$MYSQL_TMP_DIR/mysqlx-update_sql.xpl 2>&1
94 --remove_file $MYSQL_TMP_DIR/mysqlx-update_sql.xpl
95
96 --echo .
97 --echo #---------- START NEGATIVE TEST ----------#
98 --echo .
99
100 ## ******************************************
101 ## 2. Negative Test starts here |
102 ## |
103 ## ******************************************
104
105 --write_file $MYSQL_TMP_DIR/mysqlx-update_sql_negative.xpl
106 -->sql
107
108 USE mysqlxplugin;
109
110 ## Negative Testing
111 ## With wrong column name
112 ## Error Code: 1054
113 -->endsql
114 -->expecterror 1054
115 -->sql
116 UPDATE categories SET CategoryNoExist = 2 where CategoryID =1;
117 -->endsql
118
119 ## Correspondence between data and field types
120 ## Error Code: 1366
121 -->expecterror 1366
122 -->sql
123 UPDATE categories set CategoryIMEI = 'TinyInt' WHERE CategoryID = 2;
124 -->endsql
125
126 -->expecterror 1366
127 -->sql
128 UPDATE categories set CategoryDecimal = 'Decimal' WHERE CategoryID = 2;
129 -->endsql
130
131 ## Data types corner value plus 1
132 ## Error code: 1264
133 -->expecterror 1264
134 -->sql
135 UPDATE categories SET CategoryDecimal = 1000.00 WHERE CategoryID = 3;
136 -->endsql
137
138 -->expecterror 1264
139 -->sql
140 UPDATE categories SET CategoryDecimal = -1000.00 WHERE CategoryID = 3;
141 -->endsql
142
143 -->expecterror 1264
144 -->sql
145 UPDATE categories SET CategoryIMEI = 128 WHERE CategoryID = 4;
146 -->endsql
147
148 -->expecterror 1264
149 -->sql
150 UPDATE categories SET CategoryIMEI = -129 WHERE CategoryID = 4;
151 -->endsql
152
153 ## Non existing table
154 -->expecterror 1146
155 -->sql
156 UPDATE categories_no_exist set CategoryIMEI = 'TinyInt' WHERE CategoryID = 2;
157 -->endsql
158
159 ## Cleanup
160 -->echo ================================================================================
161 -->echo CLEAN UP
162 -->echo ================================================================================
163 -->sql
164 DROP SCHEMA if EXISTS mysqlxplugin;
165 DROP USER user2@localhost;
166 -->endsql
167 EOF
168
169 --exec $MYSQLXTEST -u user2 --password='user2' --file=$MYSQL_TMP_DIR/mysqlx-update_sql_negative.xpl 2>&1
170
171 ## Cleanup
172 --source ../include/xplugin_cleanup.inc