[tomato.git] / release / src / router / mysql / mysql-test / suite / engines / funcs / t / in_multicolumn_number_unique_constraint_duplicate_update.test
| blob | e6248c48f4bb9195b93d1f2971ddd6f6835084c5 |
1 --disable_warnings
2 DROP TABLE IF EXISTS t1;
3 --enable_warnings
4 CREATE TABLE t1(c1 TINYINT NULL, c2 TINYINT NULL, c3 TINYINT NULL, UNIQUE(c1,c2,c3));
5 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
6 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
7 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
8 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
9 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
10 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
11 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
12 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
13 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
14 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
15 --sorted_result
16 SELECT * FROM t1;
17 DROP TABLE t1;
18 CREATE TABLE t1(c1 SMALLINT NULL, c2 SMALLINT NULL, c3 SMALLINT NULL, UNIQUE(c1,c2,c3));
19 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
20 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
21 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
22 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
23 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
24 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
25 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
26 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
27 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
28 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
29 --sorted_result
30 SELECT * FROM t1;
31 DROP TABLE t1;
32 CREATE TABLE t1(c1 MEDIUMINT NULL, c2 MEDIUMINT NULL, c3 MEDIUMINT NULL, UNIQUE(c1,c2,c3));
33 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
34 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
35 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
36 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
37 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
38 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
39 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
40 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
41 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
42 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
43 --sorted_result
44 SELECT * FROM t1;
45 DROP TABLE t1;
46 CREATE TABLE t1(c1 INT NULL, c2 INT NULL, c3 INT NULL, UNIQUE(c1,c2,c3));
47 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
48 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
49 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
50 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
51 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
52 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
53 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
54 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
55 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
56 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
57 --sorted_result
58 SELECT * FROM t1;
59 DROP TABLE t1;
60 CREATE TABLE t1(c1 INTEGER NULL, c2 INTEGER NULL, c3 INTEGER NULL, UNIQUE(c1,c2,c3));
61 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
62 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
63 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
64 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
65 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
66 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
67 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
68 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
69 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
70 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
71 --sorted_result
72 SELECT * FROM t1;
73 DROP TABLE t1;
74 CREATE TABLE t1(c1 BIGINT NULL, c2 BIGINT NULL, c3 BIGINT NULL, UNIQUE(c1,c2,c3));
75 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
76 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
77 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
78 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
79 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
80 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
81 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
82 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
83 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
84 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
85 --sorted_result
86 SELECT * FROM t1;
87 DROP TABLE t1;
88 CREATE TABLE t1(c1 DECIMAL NULL, c2 DECIMAL NULL, c3 DECIMAL NULL, UNIQUE(c1,c2,c3));
89 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
90 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
91 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
92 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
93 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
94 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
95 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
96 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
97 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
98 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
99 --sorted_result
100 SELECT * FROM t1;
101 DROP TABLE t1;
102 CREATE TABLE t1(c1 DEC NULL, c2 DEC NULL, c3 DEC NULL, UNIQUE(c1,c2,c3));
103 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
104 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
105 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
106 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
107 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
108 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
109 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
110 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
111 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
112 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
113 --sorted_result
114 SELECT * FROM t1;
115 DROP TABLE t1;
116 CREATE TABLE t1(c1 FIXED NULL, c2 FIXED NULL, c3 FIXED NULL, UNIQUE(c1,c2,c3));
117 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
118 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
119 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
120 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
121 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
122 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
123 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
124 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
125 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
126 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
127 --sorted_result
128 SELECT * FROM t1;
129 DROP TABLE t1;
130 CREATE TABLE t1(c1 NUMERIC NULL, c2 NUMERIC NULL, c3 NUMERIC NULL, UNIQUE(c1,c2,c3));
131 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
132 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
133 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
134 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
135 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
136 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
137 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
138 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
139 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
140 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
141 --sorted_result
142 SELECT * FROM t1;
143 DROP TABLE t1;
144 CREATE TABLE t1(c1 DOUBLE NULL, c2 DOUBLE NULL, c3 DOUBLE NULL, UNIQUE(c1,c2,c3));
145 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
146 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
147 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
148 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
149 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
150 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
151 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
152 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
153 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
154 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
155 --sorted_result
156 SELECT * FROM t1;
157 DROP TABLE t1;
158 CREATE TABLE t1(c1 REAL NULL, c2 REAL NULL, c3 REAL NULL, UNIQUE(c1,c2,c3));
159 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
160 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
161 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
162 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
163 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
164 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
165 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
166 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
167 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
168 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
169 --sorted_result
170 SELECT * FROM t1;
171 DROP TABLE t1;
172 CREATE TABLE t1(c1 DOUBLE PRECISION NULL, c2 DOUBLE PRECISION NULL, c3 DOUBLE PRECISION NULL, UNIQUE(c1,c2,c3));
173 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
174 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
175 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
176 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
177 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
178 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
179 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
180 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
181 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
182 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
183 --sorted_result
184 SELECT * FROM t1;
185 DROP TABLE t1;
186 CREATE TABLE t1(c1 FLOAT NULL, c2 FLOAT NULL, c3 FLOAT NULL, UNIQUE(c1,c2,c3));
187 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10);
188 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11);
189 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,10);
190 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,10);
191 INSERT INTO t1 (c1,c2,c3) VALUES(11,11,10);
192 INSERT INTO t1 (c1,c2,c3) VALUES(11,10,11);
193 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11);
194 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c1=100,c2=100,c3=100;
195 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,11) ON DUPLICATE KEY UPDATE c1=100,c2=100;
196 INSERT INTO t1 (c1,c2,c3) VALUES(10,11,11) ON DUPLICATE KEY UPDATE c2=100,c3=100;
197 --sorted_result
198 SELECT * FROM t1;
199 DROP TABLE t1;