Debian Codegraph @

mysql-test/suite/storage_engine/insert_with_keys.test

raw
1 #
2 # INSERT statements for tables with keys
3 #
4 --source have_engine.inc
5 --source have_default_index.inc
6
7 --disable_warnings
8 DROP TABLE IF EXISTS t1;
9 --enable_warnings
10
11 --let $create_definition = a $int_col, b $char_indexed_col, $default_index(b)
12 --source create_table.inc
13 if ($mysql_errname)
14 {
15 --let $my_last_stmt = $create_statement
16 --let $functionality = Indexes on CHAR columns
17 --source unexpected_result.inc
18 }
19 if (!$mysql_errname)
20 {
21 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
22 INSERT INTO t1 (a,b) VALUES (100,'a'), (6,'f');
23 INSERT INTO t1 (a,b) VALUES (30,'m'),(29,'n');
24 INSERT INTO t1 (a,b) VALUES (1,'a'),(12345,'z');
25 INSERT INTO t1 (a,b) VALUES (3,'a'),(0,'');
26 --sorted_result
27 SELECT a,b FROM t1;
28 DROP TABLE t1;
29 }
30
31 --let $create_definition = a $int_indexed_col, b $char_col, UNIQUE INDEX(a)
32 --source create_table.inc
33 if ($mysql_errname)
34 {
35 --let $my_last_stmt = $create_statement
36 --let $functionality = Unique indexes
37 --source unexpected_result.inc
38 }
39 if (!$mysql_errname)
40 {
41 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
42 INSERT INTO t1 (a,b) VALUES (100,'a'), (6,'f');
43 INSERT INTO t1 (a,b) VALUES (30,'m'),(29,'n');
44 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
45 INSERT INTO t1 (a,b) VALUES (1,'a'),(12345,'z');
46 --source check_errors.inc
47 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
48 INSERT INTO t1 (a,b) VALUES (3,'a'),(0,'');
49 --source check_errors.inc
50 INSERT INTO t1 (a,b) VALUES (0,'');
51 --sorted_result
52 SELECT a,b FROM t1;
53
54 INSERT IGNORE INTO t1 (a,b) VALUES (1,'a'),(12345,'z');
55 if ($mysql_errname)
56 {
57 --source unexpected_result.inc
58 }
59 INSERT INTO t1 (a,b) VALUES (3,'a'),(4,'d') ON DUPLICATE KEY UPDATE a = a+10;
60 if ($mysql_errname)
61 {
62 --source unexpected_result.inc
63 }
64
65 --sorted_result
66 SELECT a,b FROM t1;
67
68 DROP TABLE t1;
69 }
70
71 --let $create_definition = a $int_indexed_col, b $char_indexed_col, UNIQUE INDEX(a,b)
72 --source create_table.inc
73 if ($mysql_errname)
74 {
75 --let $my_last_stmt = $create_statement
76 --let $functionality = Multi-part indexes
77 --source unexpected_result.inc
78 }
79 if (!$mysql_errname)
80 {
81 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
82 INSERT INTO t1 (a,b) VALUES (100,'a'), (6,'f');
83 INSERT INTO t1 (a,b) VALUES (30,'m'),(29,'n');
84 INSERT INTO t1 (a,b) VALUES (100,'b'), (2,'c');
85 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
86 INSERT INTO t1 (a,b) VALUES (1,'a'),(12345,'z');
87 --source check_errors.inc
88 --sorted_result
89 SELECT a,b FROM t1;
90
91 INSERT IGNORE INTO t1 (a,b) VALUES (1,'a'),(12345,'z');
92 INSERT INTO t1 (a,b) VALUES (1,'a'),(12345,'z') ON DUPLICATE KEY UPDATE a = a+VALUES(a);
93 --sorted_result
94 SELECT a,b FROM t1;
95
96 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
97 INSERT INTO t1 (a,b) VALUES (101,'x'),(101,'x');
98 --source check_errors.inc
99 DROP TABLE t1;
100 }
101
102 --let $create_definition = a $int_indexed_col PRIMARY KEY, b $char_col
103 --source create_table.inc
104 if ($mysql_errname)
105 {
106 --let $my_last_stmt = $create_statement
107 --let $functionality = Primary keys
108 --source unexpected_result.inc
109 }
110 if (!$mysql_errname)
111 {
112 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
113 INSERT INTO t1 (a,b) VALUES (100,'a'), (6,'f');
114 INSERT INTO t1 (a,b) VALUES (30,'m'),(29,'n');
115 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
116 INSERT INTO t1 (a,b) VALUES (1,'a'),(12345,'z');
117 --source check_errors.inc
118 --let $error_codes = ER_DUP_ENTRY,ER_DUP_KEY
119 INSERT INTO t1 (a,b) VALUES (3,'a'),(0,'');
120 --source check_errors.inc
121 INSERT INTO t1 (a,b) VALUES (0,'');
122 --sorted_result
123 SELECT a,b FROM t1;
124
125 INSERT IGNORE INTO t1 (a,b) VALUES (1,'a'),(12345,'z');
126 if ($mysql_errname)
127 {
128 --source unexpected_result.inc
129 }
130
131 INSERT INTO t1 (a,b) VALUES (1,'a'),(12345,'z') ON DUPLICATE KEY UPDATE b = CONCAT(b,b);
132 if ($mysql_errname)
133 {
134 --source unexpected_result.inc
135 }
136 --sorted_result
137 SELECT a,b FROM t1;
138
139 DROP TABLE t1;
140 }
141
142 --let $create_definition = a $int_indexed_col UNIQUE KEY, b $int_indexed_col UNIQUE KEY, c $int_col
143 --source create_table.inc
144 if ($mysql_errname)
145 {
146 --let $my_last_stmt = $create_statement
147 --let $functionality = Multiple unique keys
148 --source unexpected_result.inc
149 }
150 if (!$mysql_errname)
151 {
152 INSERT INTO t1 VALUES(1,1,0);
153 INSERT INTO t1 VALUES(2,3,0);
154 INSERT INTO t1 VALUES(3,2,0);
155
156 INSERT INTO t1 VALUES(1,1,0) ON DUPLICATE KEY UPDATE c=c+1;
157 INSERT INTO t1 VALUES(2,3,0) ON DUPLICATE KEY UPDATE c=c+1;
158 INSERT INTO t1 VALUES(3,2,0) ON DUPLICATE KEY UPDATE c=c+1;
159
160 INSERT INTO t1 VALUES(2,5,0) ON DUPLICATE KEY UPDATE c=c+1;
161 INSERT INTO t1 VALUES(3,5,0) ON DUPLICATE KEY UPDATE c=c+1;
162 INSERT INTO t1 VALUES(5,3,0) ON DUPLICATE KEY UPDATE c=c+1;
163 INSERT INTO t1 VALUES(6,2,0) ON DUPLICATE KEY UPDATE c=c+1;
164 INSERT INTO t1 VALUES(1,3,0) ON DUPLICATE KEY UPDATE c=c+1;
165 INSERT INTO t1 VALUES(2,2,0) ON DUPLICATE KEY UPDATE c=c+1;
166
167 --sorted_result
168 SELECT * FROM t1;
169 DROP TABLE t1;
170 }
171
172 --source cleanup_engine.inc
173