Debian Codegraph @

mysql-test/suite/storage_engine/update_with_keys.test

raw
1 #
2 # UPDATE statements for tables with keys
3 #
4 --source have_engine.inc
5 --source have_default_index.inc
6
7
8 --disable_warnings
9 DROP TABLE IF EXISTS t1;
10 --enable_warnings
11
12 --let $create_definition = a $int_col, b $char_col, INDEX(b)
13 --source create_table.inc
14 if ($mysql_errname)
15 {
16 --let $my_last_stmt = $create_statement
17 --let $functionality = Non-unique keys
18 --source unexpected_result.inc
19 }
20 if (!$mysql_errname)
21 {
22 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'x'),(7,'y'),(8,'z');
23 UPDATE t1 SET a=100, b='f' WHERE b IN ('b','c');
24 if ($mysql_errname)
25 {
26 --let $functionality = UPDATE
27 --source unexpected_result.inc
28 }
29 if (!$mysql_errname)
30 {
31 UPDATE t1 SET b='m' WHERE b = 'f';
32 UPDATE t1 SET b='z' WHERE a < 2;
33 UPDATE t1 SET b='';
34 --sorted_result
35 SELECT a,b FROM t1;
36 }
37 DROP TABLE t1;
38 }
39
40 --let $create_definition = a $int_col, b $char_col, UNIQUE INDEX(a)
41 --source create_table.inc
42 if ($mysql_errname)
43 {
44 --let $my_last_stmt = $create_statement
45 --let $functionality = Unique keys
46 --source unexpected_result.inc
47 }
48 if (!$mysql_errname)
49 {
50 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(0,'f'),(100,'a');
51 UPDATE t1 SET a=a+200;
52 if ($mysql_errname)
53 {
54 --let $functionality = UPDATE
55 --source unexpected_result.inc
56 }
57 if (!$mysql_errname)
58 {
59 UPDATE t1 SET a=0 WHERE a > 250;
60 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
61 UPDATE t1 SET a=205 WHERE a=200;
62 --source check_errors.inc
63 UPDATE t1 SET a=12345 ORDER BY a, b LIMIT 1;
64 --sorted_result
65 SELECT a,b FROM t1;
66
67 # We'll check that the next update causes an error,
68 # but won't check the result because it might be different depending
69 # on whether the engine is transactional or not
70 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
71 UPDATE t1 SET a=80 WHERE a IN (202,203);
72 --source check_errors.inc
73 }
74 DROP TABLE t1;
75 }
76
77 --let $create_definition = a $int_col, b $char_col, UNIQUE INDEX(a,b)
78 --source create_table.inc
79 if ($mysql_errname)
80 {
81 --let $my_last_stmt = $create_statement
82 --let $functionality = Unique keys or multi-part keys
83 --source unexpected_result.inc
84 }
85 if (!$mysql_errname)
86 {
87 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(100,'a'),(6,'f');
88 UPDATE t1 SET a=6 WHERE a=3;
89 if ($mysql_errname)
90 {
91 --let $functionality = UPDATE
92 --source unexpected_result.inc
93 }
94 if (!$mysql_errname)
95 {
96 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
97 UPDATE t1 SET a=100 WHERE a=1;
98 --source check_errors.inc
99 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
100 UPDATE t1 SET a=4, b='d' WHERE b='f';
101 --source check_errors.inc
102 UPDATE t1 SET a=a+1;
103 --sorted_result
104 SELECT a,b FROM t1;
105 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
106 UPDATE t1 SET b='z';
107 --source check_errors.inc
108 }
109 DROP TABLE t1;
110 }
111
112 --let $create_definition = a $int_col PRIMARY KEY, b $char_col
113 --source create_table.inc
114 if ($mysql_errname)
115 {
116 --let $my_last_stmt = $create_statement
117 --let $functionality = Primary key
118 --source unexpected_result.inc
119 }
120 if (!$mysql_errname)
121 {
122 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(0,'f'),(100,'a');
123 UPDATE t1 SET a=a+200;
124 if ($mysql_errname)
125 {
126 --let $functionality = UPDATE
127 --source unexpected_result.inc
128 }
129 if (!$mysql_errname)
130 {
131 UPDATE t1 SET a=0 WHERE a > 250;
132 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
133 UPDATE t1 SET a=205 WHERE a=200;
134 --source check_errors.inc
135 UPDATE t1 SET a=12345 ORDER BY a DESC, b LIMIT 1;
136
137 --sorted_result
138 SELECT a,b FROM t1;
139
140 # We'll check that the next update causes an error,
141 # but won't check the result because it might be different depending
142 # on whether the engine is transactional or not
143
144 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
145 UPDATE t1 SET a=80 WHERE a IN (202,203);
146 --source check_errors.inc
147 }
148 DROP TABLE t1;
149 }
150
151 --source cleanup_engine.inc
152