Debian Codegraph @

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

raw
1 =============================================
2 SQL UPDATE SCENARIOS
3 =============================================
4
5 ================================================================================
6 PREAMBLE
7 ================================================================================
8 create user user2@localhost identified by 'user2';
9 grant all on *.* to user2@localhost;
10 ================================================================================
11 TEST START
12 ================================================================================
13 RUN DROP SCHEMA if EXISTS mysqlxplugin
14
15 0 rows affected
16 RUN CREATE SCHEMA mysqlxplugin DEFAULT CHARSET='utf8'
17
18 1 rows affected
19 RUN USE mysqlxplugin
20
21 0 rows affected
22 RUN CREATE TABLE categories (
23 CategoryID int NOT NULL AUTO_INCREMENT,
24 CategoryName varchar(100),
25 CategoryDescription varchar(200),
26 CategoryIMEI tinyint,
27 CategoryDecimal decimal(5,2),
28 PRIMARY key (CategoryID)
29 )
30
31 0 rows affected
32 RUN INSERT INTO categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
33 VALUES
34 (1,'Sports','Sports related category',1,235.15),
35 (2,'Entertaiment','Entertaiment related category',2,235.15),
36 (3, 'Home','Home related category',3,235.15),
37 (4, 'Kitchen','Kitchen related category',4,235.15)
38
39 4 rows affected
40 last insert id: 4
41 Records: 4 Duplicates: 0 Warnings: 0
42 RUN UPDATE categories SET CategoryName = 'Sports Update' where CategoryID =1
43
44 1 rows affected
45 Rows matched: 1 Changed: 1 Warnings: 0
46 RUN UPDATE categories SET CategoryIMEI = 110 where CategoryID = 1
47
48 1 rows affected
49 Rows matched: 1 Changed: 1 Warnings: 0
50 RUN UPDATE categories SET CategoryDecimal = 613.47 where CategoryID =1
51
52 1 rows affected
53 Rows matched: 1 Changed: 1 Warnings: 0
54 RUN UPDATE categories SET
55 CategoryName='"Quotes"',
56 CategoryDescription='Quote Categories',
57 CategoryIMEI = 3,
58 CategoryDecimal = 235.15
59 WHERE CategoryID = 3
60
61 1 rows affected
62 Rows matched: 1 Changed: 1 Warnings: 0
63 RUN UPDATE categories SET
64 CategoryName=NULL,
65 CategoryDescription=NULL,
66 CategoryIMEI = NULL,
67 CategoryDecimal = NULL
68 WHERE CategoryID = 4
69
70 1 rows affected
71 Rows matched: 1 Changed: 1 Warnings: 0
72 RUN UPDATE categories SET CategoryDecimal = 999.99
73 WHERE CategoryID = 3
74
75 1 rows affected
76 Rows matched: 1 Changed: 1 Warnings: 0
77 RUN UPDATE categories SET CategoryDecimal = -999.99
78 WHERE CategoryID = 3
79
80 1 rows affected
81 Rows matched: 1 Changed: 1 Warnings: 0
82 RUN UPDATE categories SET CategoryIMEI = 127
83 WHERE CategoryID = 4
84
85 1 rows affected
86 Rows matched: 1 Changed: 1 Warnings: 0
87 RUN UPDATE categories SET CategoryIMEI = -128
88 WHERE CategoryID = 4
89
90 1 rows affected
91 Rows matched: 1 Changed: 1 Warnings: 0
92 Mysqlx.Ok {
93 msg: "bye!"
94 }
95 ok
96 .
97 #---------- START NEGATIVE TEST ----------#
98 .
99 RUN USE mysqlxplugin
100
101 0 rows affected
102 RUN UPDATE categories SET CategoryNoExist = 2 where CategoryID =1
103 While executing UPDATE categories SET CategoryNoExist = 2 where CategoryID =1:
104 Got expected error: Unknown column 'CategoryNoExist' in 'field list' (code 1054)
105 RUN UPDATE categories set CategoryIMEI = 'TinyInt' WHERE CategoryID = 2
106 While executing UPDATE categories set CategoryIMEI = 'TinyInt' WHERE CategoryID = 2:
107 Got expected error: Incorrect integer value: 'TinyInt' for column 'CategoryIMEI' at row 1 (code 1366)
108 RUN UPDATE categories set CategoryDecimal = 'Decimal' WHERE CategoryID = 2
109 While executing UPDATE categories set CategoryDecimal = 'Decimal' WHERE CategoryID = 2:
110 Got expected error: Incorrect decimal value: 'Decimal' for column 'CategoryDecimal' at row 1 (code 1366)
111 RUN UPDATE categories SET CategoryDecimal = 1000.00 WHERE CategoryID = 3
112 While executing UPDATE categories SET CategoryDecimal = 1000.00 WHERE CategoryID = 3:
113 Got expected error: Out of range value for column 'CategoryDecimal' at row 1 (code 1264)
114 RUN UPDATE categories SET CategoryDecimal = -1000.00 WHERE CategoryID = 3
115 While executing UPDATE categories SET CategoryDecimal = -1000.00 WHERE CategoryID = 3:
116 Got expected error: Out of range value for column 'CategoryDecimal' at row 1 (code 1264)
117 RUN UPDATE categories SET CategoryIMEI = 128 WHERE CategoryID = 4
118 While executing UPDATE categories SET CategoryIMEI = 128 WHERE CategoryID = 4:
119 Got expected error: Out of range value for column 'CategoryIMEI' at row 1 (code 1264)
120 RUN UPDATE categories SET CategoryIMEI = -129 WHERE CategoryID = 4
121 While executing UPDATE categories SET CategoryIMEI = -129 WHERE CategoryID = 4:
122 Got expected error: Out of range value for column 'CategoryIMEI' at row 1 (code 1264)
123 RUN UPDATE categories_no_exist set CategoryIMEI = 'TinyInt' WHERE CategoryID = 2
124 While executing UPDATE categories_no_exist set CategoryIMEI = 'TinyInt' WHERE CategoryID = 2:
125 Got expected error: Table 'mysqlxplugin.categories_no_exist' doesn't exist (code 1146)
126 ================================================================================
127 CLEAN UP
128 ================================================================================
129 RUN DROP SCHEMA if EXISTS mysqlxplugin
130
131 1 rows affected
132 RUN DROP USER user2@localhost
133
134 0 rows affected
135 Mysqlx.Ok {
136 msg: "bye!"
137 }
138 ok