1
by brian
clean slate |
1 |
# Testing of "strict" mode |
2 |
||
3 |
-- source include/have_innodb.inc
|
|
4 |
||
5 |
# Test INSERT with INT |
|
6 |
||
873.2.35
by Monty Taylor
Update tests based on how Toru's latest patch changes create table statements. |
7 |
--disable_warnings
|
8 |
DROP TABLE IF EXISTS t1; |
|
9 |
--enable_warnings
|
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
10 |
CREATE TABLE t1 (col1 INT); |
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
11 |
INSERT INTO t1 VALUES(-2147483648); |
12 |
INSERT INTO t1 VALUES (0); |
|
13 |
INSERT INTO t1 VALUES (2147483647); |
|
14 |
INSERT INTO t1 VALUES ('-2147483648'); |
|
15 |
INSERT INTO t1 VALUES ('2147483647'); |
|
16 |
INSERT INTO t1 VALUES (-2147483648.0); |
|
17 |
INSERT INTO t1 VALUES (2147483647.0); |
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
18 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
19 |
INSERT INTO t1 (col1) VALUES(-2147483649); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
20 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
21 |
INSERT INTO t1 (col1) VALUES(2147643648); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
22 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
23 |
INSERT INTO t1 (col1) VALUES('-2147483649'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
24 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
25 |
INSERT INTO t1 (col1) VALUES('2147643648'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
26 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
27 |
INSERT INTO t1 (col1) VALUES(-2147483649.0); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
28 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
29 |
INSERT INTO t1 (col1) VALUES(2147643648.0); |
30 |
||
784.2.1
by Stewart Smith
fix strict test for drizzle. |
31 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
32 |
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
33 |
--error ER_DIVISION_BY_ZERO
|
1
by brian
clean slate |
34 |
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
35 |
--error ER_DIVISION_BY_ZERO
|
1
by brian
clean slate |
36 |
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
37 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
38 |
INSERT INTO t1 (col1) VALUES (''); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
39 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
40 |
INSERT INTO t1 (col1) VALUES ('a59b'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
41 |
--error ER_WARN_DATA_TRUNCATED
|
1
by brian
clean slate |
42 |
INSERT INTO t1 (col1) VALUES ('1a'); |
43 |
INSERT IGNORE INTO t1 (col1) VALUES ('2a'); |
|
1812.4.2
by Brian Aker
Fix issue with divide by zero not being an error. |
44 |
--error ER_DIVISION_BY_ZERO
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
45 |
INSERT IGNORE INTO t1 values (1/0); |
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
46 |
INSERT IGNORE INTO t1 values (-2147483649); |
47 |
INSERT IGNORE INTO t1 values (2147643648); |
|
48 |
INSERT IGNORE INTO t1 values ('-2147483649'); |
|
49 |
INSERT IGNORE INTO t1 values ('2147643648'); |
|
50 |
INSERT IGNORE INTO t1 values (-2147483649.0); |
|
51 |
INSERT IGNORE INTO t1 values (2147643648.0); |
|
1
by brian
clean slate |
52 |
SELECT * FROM t1; |
53 |
DROP TABLE t1; |
|
54 |
||
55 |
# Test INSERT with BIGINT |
|
56 |
# Note that this doesn't behave 100 % to standard as we rotate |
|
57 |
# integers when it's too big/small (just like C) |
|
58 |
||
784.2.1
by Stewart Smith
fix strict test for drizzle. |
59 |
CREATE TABLE t1 (col1 BIGINT); |
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
60 |
INSERT INTO t1 VALUES (-9223372036854775808); |
61 |
INSERT INTO t1 VALUES (0); |
|
62 |
INSERT INTO t1 VALUES (9223372036854775807); |
|
63 |
INSERT INTO t1 VALUES ('-9223372036854775808'); |
|
64 |
INSERT INTO t1 VALUES ('9223372036854775807'); |
|
65 |
INSERT INTO t1 VALUES (-9223372036854774000.0); |
|
66 |
INSERT INTO t1 VALUES (9223372036854775700.0); |
|
1
by brian
clean slate |
67 |
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
68 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
69 |
INSERT INTO t1 (col1) VALUES(-9223372036854775809); |
2040.5.1
by kalebral at gmail
enable test as bug 316221 is now resolved |
70 |
# https://bugs.launchpad.net/drizzle/+bug/316221 |
71 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
|
72 |
INSERT INTO t1 (col1) VALUES(9223372036854775808); |
|
1
by brian
clean slate |
73 |
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
74 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
75 |
INSERT INTO t1 (col1) VALUES('-9223372036854775809'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
76 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
77 |
INSERT INTO t1 (col1) VALUES('9223372036854775808'); |
78 |
||
79 |
# Note that the following two double numbers are slighty bigger than max/min |
|
80 |
# bigint becasue of rounding errors when converting it to bigint |
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
81 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
82 |
INSERT INTO t1 (col1) VALUES(-9223372036854785809.0); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
83 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
84 |
INSERT INTO t1 (col1) VALUES(9223372036854785808.0); |
85 |
||
86 |
# The following doesn't give an error as it's done in integer context |
|
87 |
# UPDATE t1 SET col1=col1 - 5000 WHERE col1 < 0; |
|
88 |
# UPDATE t1 SET col2 =col2 + 5000 WHERE col2 > 0; |
|
89 |
||
784.2.1
by Stewart Smith
fix strict test for drizzle. |
90 |
--error ER_DIVISION_BY_ZERO
|
1
by brian
clean slate |
91 |
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
92 |
--error ER_DIVISION_BY_ZERO
|
1
by brian
clean slate |
93 |
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
94 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
95 |
INSERT INTO t1 (col1) VALUES (''); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
96 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
97 |
INSERT INTO t1 (col1) VALUES ('a59b'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
98 |
--error ER_WARN_DATA_TRUNCATED
|
1
by brian
clean slate |
99 |
INSERT INTO t1 (col1) VALUES ('1a'); |
100 |
INSERT IGNORE INTO t1 (col1) VALUES ('2a'); |
|
1812.4.2
by Brian Aker
Fix issue with divide by zero not being an error. |
101 |
--error ER_DIVISION_BY_ZERO
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
102 |
INSERT IGNORE INTO t1 values (1/0); |
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
103 |
INSERT IGNORE INTO t1 VALUES (-9223372036854775809); |
104 |
INSERT IGNORE INTO t1 VALUES (9223372036854775808); |
|
105 |
INSERT IGNORE INTO t1 VALUES ('-9223372036854775809'); |
|
106 |
INSERT IGNORE INTO t1 VALUES ('9223372036854775808'); |
|
107 |
INSERT IGNORE INTO t1 VALUES (-9223372036854785809.0); |
|
108 |
INSERT IGNORE INTO t1 VALUES (9223372036854785808.0); |
|
1
by brian
clean slate |
109 |
SELECT * FROM t1; |
110 |
DROP TABLE t1; |
|
111 |
||
112 |
# Test INSERT with NUMERIC |
|
113 |
||
114 |
CREATE TABLE t1 (col1 NUMERIC(4,2)); |
|
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
115 |
# The following INSERT statements used to look as follows before |
116 |
# the fix for bug#337038 was implemented: |
|
117 |
# INSERT INTO t1 |
|
118 |
# VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01); |
|
119 |
# Now that decimal truncation gives an error instead of a warning, we will |
|
120 |
# get an error on certain INSERT statements below about decimal truncation. |
|
121 |
||
122 |
INSERT INTO t1 VALUES (10.55); |
|
123 |
# this statement errors due to decimal truncation. The number |
|
124 |
# used in insertion is chosen to test that this this error does |
|
125 |
# in fact occur |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
126 |
--error ER_WARN_DATA_TRUNCATED
|
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
127 |
INSERT INTO t1 VALUES (10.5555); |
128 |
INSERT INTO t1 VALUES (0); |
|
129 |
INSERT INTO t1 VALUES (-10.55); |
|
130 |
# this statement errors due to decimal truncation. The number |
|
131 |
# used in insertion is chosen to test that this this error does |
|
132 |
# in fact occur |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
133 |
--error ER_WARN_DATA_TRUNCATED
|
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
134 |
INSERT INTO t1 VALUES (-10.5555); |
135 |
INSERT INTO t1 VALUES (11); |
|
136 |
INSERT INTO t1 VALUES (1e+01); |
|
137 |
||
138 |
# The following INSERT statements used to look as follows before |
|
139 |
# the fix for bug#337038 was implemented: |
|
140 |
# INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01'); |
|
141 |
# Now that decimal truncation gives an error instead of a warning, we will |
|
142 |
# get an error on certain INSERT statements below about decimal truncation. |
|
143 |
||
144 |
INSERT INTO t1 VALUES ('10.55'); |
|
145 |
# this statement errors due to decimal truncation. The number |
|
146 |
# used in insertion is chosen to test that this this error does |
|
147 |
# in fact occur |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
148 |
--error ER_WARN_DATA_TRUNCATED
|
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
149 |
INSERT INTO t1 VALUES ('10.5555'); |
150 |
INSERT INTO t1 VALUES ('-10.55'); |
|
151 |
# this statement errors due to decimal truncation. The number |
|
152 |
# used in insertion is chosen to test that this error does |
|
153 |
# in fact occur |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
154 |
--error ER_WARN_DATA_TRUNCATED
|
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
155 |
INSERT INTO t1 VALUES ('-10.5555'); |
156 |
INSERT INTO t1 VALUES ('11'); |
|
157 |
INSERT INTO t1 VALUES ('1e+01'); |
|
1
by brian
clean slate |
158 |
|
159 |
# The 2 following inserts should generate a warning, but doesn't yet |
|
160 |
# because NUMERIC works like DECIMAL
|
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
161 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
162 |
INSERT INTO t1 VALUES (101.55);
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
163 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
164 |
INSERT INTO t1 VALUES (101);
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
165 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
166 |
INSERT INTO t1 VALUES (-101.55);
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
167 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
168 |
INSERT INTO t1 VALUES (1010.55);
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
169 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
170 |
INSERT INTO t1 VALUES (1010);
|
171 |
# The 2 following inserts should generate a warning, but doesn't yet |
|
172 |
# because NUMERIC works like DECIMAL |
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
173 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
174 |
INSERT INTO t1 VALUES ('101.55'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
175 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
176 |
INSERT INTO t1 VALUES ('101'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
177 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
178 |
INSERT INTO t1 VALUES ('-101.55'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
179 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
180 |
INSERT INTO t1 VALUES ('-1010.55'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
181 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
182 |
INSERT INTO t1 VALUES ('-100E+1'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
183 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
184 |
INSERT INTO t1 VALUES ('-100E'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
185 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
186 |
UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11; |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
187 |
--error ER_DIVISION_BY_ZERO
|
1
by brian
clean slate |
188 |
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
189 |
--error ER_DIVISION_BY_ZERO
|
1
by brian
clean slate |
190 |
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
191 |
#--error ER_WARN_DATA_TRUNCATED |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
192 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
193 |
INSERT INTO t1 (col1) VALUES (''); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
194 |
#--error ER_WARN_DATA_TRUNCATED |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
195 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
196 |
INSERT INTO t1 (col1) VALUES ('a59b'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
197 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
198 |
INSERT INTO t1 (col1) VALUES ('1a'); |
199 |
INSERT IGNORE INTO t1 (col1) VALUES ('2a'); |
|
1812.4.2
by Brian Aker
Fix issue with divide by zero not being an error. |
200 |
--error ER_DIVISION_BY_ZERO
|
1
by brian
clean slate |
201 |
INSERT IGNORE INTO t1 values (1/0); |
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
202 |
INSERT IGNORE INTO t1 VALUES (1000); |
203 |
INSERT IGNORE INTO t1 VALUES (-1000); |
|
204 |
INSERT IGNORE INTO t1 VALUES ('1000'); |
|
205 |
INSERT IGNORE INTO t1 VALUES ('-1000'); |
|
206 |
INSERT IGNORE INTO t1 VALUES (1000.0); |
|
207 |
INSERT IGNORE INTO t1 VALUES (-1000.0); |
|
1
by brian
clean slate |
208 |
UPDATE IGNORE t1 SET col1=1/NULL where col1=0; |
942.3.1
by Vladimir Kolesnikov
test generalizations |
209 |
--sorted_result
|
1
by brian
clean slate |
210 |
SELECT * FROM t1; |
211 |
DROP TABLE t1; |
|
212 |
||
213 |
# Testing INSERT with CHAR/VARCHAR |
|
214 |
||
215 |
CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6)); |
|
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
216 |
INSERT INTO t1 VALUES ('hello', 'hello'); |
217 |
INSERT INTO t1 VALUES ('he', 'he'); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
218 |
--error ER_DATA_TOO_LONG
|
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
219 |
INSERT INTO t1 VALUES ('hello ', 'hello '); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
220 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
221 |
INSERT INTO t1 (col1) VALUES ('hellobob'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
222 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
223 |
INSERT INTO t1 (col2) VALUES ('hellobob'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
224 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
225 |
INSERT INTO t1 (col2) VALUES ('hello '); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
226 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
227 |
UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he'; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
228 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
229 |
UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he'; |
230 |
INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob'); |
|
231 |
UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he'; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
232 |
--sorted_result
|
1
by brian
clean slate |
233 |
SELECT * FROM t1; |
234 |
DROP TABLE t1; |
|
235 |
||
236 |
# Testing INSERT with ENUM |
|
237 |
||
238 |
CREATE TABLE t1 (col1 enum('red','blue','green')); |
|
970.2.5
by Padraig O'Sullivan
Added extra comments to parts of test cases that were modified for this bug |
239 |
INSERT INTO t1 VALUES ('red'); |
240 |
INSERT INTO t1 VALUES ('blue'); |
|
241 |
INSERT INTO t1 VALUES ('green'); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
242 |
--error ER_INVALID_ENUM_VALUE # Bad enum
|
1
by brian
clean slate |
243 |
INSERT INTO t1 (col1) VALUES ('yellow'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
244 |
--error ER_INVALID_ENUM_VALUE # Bad enum
|
1
by brian
clean slate |
245 |
INSERT INTO t1 (col1) VALUES ('redd'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
246 |
--error ER_INVALID_ENUM_VALUE # Bad enum
|
1
by brian
clean slate |
247 |
INSERT INTO t1 VALUES (''); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
248 |
--error ER_INVALID_ENUM_VALUE # Bad enum
|
1
by brian
clean slate |
249 |
UPDATE t1 SET col1 ='yellow' WHERE col1 ='green'; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
250 |
--error ER_INVALID_ENUM_VALUE # Bad enum
|
1
by brian
clean slate |
251 |
INSERT IGNORE INTO t1 VALUES ('yellow'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
252 |
--error ER_INVALID_ENUM_VALUE # Bad enum
|
1
by brian
clean slate |
253 |
UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue'; |
254 |
SELECT * FROM t1; |
|
255 |
DROP TABLE t1; |
|
256 |
||
257 |
# Testing of insert of NULL in not NULL column |
|
258 |
||
259 |
CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL); |
|
260 |
INSERT INTO t1 VALUES (100, 'hello', '2004-08-20'); |
|
261 |
INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21'); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
262 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
263 |
INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
264 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
265 |
INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
266 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
267 |
INSERT INTO t1 VALUES (103,'',NULL); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
268 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
269 |
UPDATE t1 SET col1=NULL WHERE col1 =100; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
270 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
271 |
UPDATE t1 SET col2 =NULL WHERE col2 ='hello'; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
272 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
273 |
UPDATE t1 SET col2 =NULL where col3 IS NOT NULL; |
274 |
INSERT IGNORE INTO t1 values (NULL,NULL,NULL); |
|
275 |
SELECT * FROM t1; |
|
276 |
DROP TABLE t1; |
|
277 |
||
278 |
# Testing of default values |
|
279 |
||
280 |
CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL); |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
281 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1
by brian
clean slate |
282 |
SHOW CREATE TABLE t1; |
283 |
INSERT INTO t1 VALUES (1, 'hello'); |
|
284 |
INSERT INTO t1 (col2) VALUES ('hello2'); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
285 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
286 |
INSERT INTO t1 (col2) VALUES (NULL); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
287 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
288 |
INSERT INTO t1 (col1) VALUES (2); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
289 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
290 |
INSERT INTO t1 VALUES(default(col1),default(col2)); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
291 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
292 |
INSERT INTO t1 (col1) SELECT 1; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
293 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
294 |
INSERT INTO t1 SELECT 1,NULL; |
295 |
INSERT IGNORE INTO t1 values (NULL,NULL); |
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
296 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
297 |
INSERT IGNORE INTO t1 (col1) values (3); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
298 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
299 |
INSERT IGNORE INTO t1 () values (); |
300 |
SELECT * FROM t1; |
|
301 |
DROP TABLE t1; |
|
302 |
||
303 |
#
|
|
304 |
# Bug #9029 Traditional: Wrong SQLSTATE returned for string truncation |
|
305 |
#
|
|
306 |
||
307 |
create table t1 (charcol char(255), varcharcol varchar(255), |
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
308 |
varbinarycol varbinary(255)); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
309 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
310 |
insert into t1 (charcol) values (repeat('x',256)); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
311 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
312 |
insert into t1 (varcharcol) values (repeat('x',256)); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
313 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
314 |
insert into t1 (varbinarycol) values (repeat('x',256)); |
315 |
select * from t1; |
|
316 |
drop table t1; |
|
317 |
||
318 |
#
|
|
319 |
# Check insert with wrong CAST() (Bug #5912) |
|
320 |
#
|
|
321 |
||
322 |
create table t1 (col1 char(3), col2 integer); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
323 |
--error ER_TRUNCATED_WRONG_VALUE
|
1
by brian
clean slate |
324 |
insert into t1 (col1) values (cast(1000 as char(3))); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
325 |
--error ER_TRUNCATED_WRONG_VALUE
|
1
by brian
clean slate |
326 |
insert into t1 (col1) values (cast(1000E+0 as char(3))); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
327 |
--error ER_TRUNCATED_WRONG_VALUE
|
1
by brian
clean slate |
328 |
insert into t1 (col1) values (cast(1000.0 as char(3))); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
329 |
--error ER_TRUNCATED_WRONG_VALUE
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
330 |
insert into t1 (col2) values (cast('abc' as DECIMAL)); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
331 |
--error ER_TRUNCATED_WRONG_VALUE
|
1
by brian
clean slate |
332 |
insert into t1 (col2) values (10E+0 + 'a'); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
333 |
--error ER_WARN_DATA_TRUNCATED
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
334 |
insert into t1 (col2) values ('10a'); |
335 |
insert into t1 (col2) values (cast('10a' as DECIMAL)); |
|
336 |
insert into t1 (col2) values (cast('10' as DECIMAL)); |
|
337 |
insert into t1 (col2) values (cast('10' as DECIMAL)); |
|
1
by brian
clean slate |
338 |
insert into t1 (col2) values (10E+0 + '0 '); |
339 |
select * from t1; |
|
340 |
drop table t1; |
|
341 |
||
342 |
# Test fields with no default value that are NOT NULL (Bug #5986) |
|
343 |
CREATE TABLE t1 (i int not null); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
344 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
345 |
INSERT INTO t1 VALUES (); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
346 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
347 |
INSERT INTO t1 VALUES (DEFAULT); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
348 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
349 |
INSERT INTO t1 VALUES (DEFAULT(i)); |
350 |
ALTER TABLE t1 ADD j int; |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
351 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
352 |
INSERT INTO t1 SET j = 1; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
353 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
354 |
INSERT INTO t1 SET j = 1, i = DEFAULT; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
355 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
356 |
INSERT INTO t1 SET j = 1, i = DEFAULT(i); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
357 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
358 |
INSERT INTO t1 VALUES (DEFAULT,1); |
359 |
DROP TABLE t1; |
|
360 |
CREATE TABLE t1 (i int not null); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
361 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
362 |
INSERT INTO t1 VALUES (); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
363 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
364 |
INSERT INTO t1 VALUES (DEFAULT); |
365 |
# DEFAULT(i) is an error even with the default sql_mode |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
366 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
367 |
INSERT INTO t1 VALUES (DEFAULT(i)); |
368 |
ALTER TABLE t1 ADD j int; |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
369 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
370 |
INSERT INTO t1 SET j = 1; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
371 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
372 |
INSERT INTO t1 SET j = 1, i = DEFAULT; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
373 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
374 |
INSERT INTO t1 SET j = 1, i = DEFAULT(i); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
375 |
--error ER_NO_DEFAULT_FOR_FIELD
|
1
by brian
clean slate |
376 |
INSERT INTO t1 VALUES (DEFAULT,1); |
377 |
DROP TABLE t1; |
|
378 |
||
379 |
#
|
|
380 |
# Bugs #8295 and #8296: varchar and varbinary conversion |
|
381 |
#
|
|
382 |
||
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
383 |
--error ER_TOO_BIG_FIELDLENGTH
|
1
by brian
clean slate |
384 |
create table t1(a varchar(65537)); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
385 |
--error ER_TOO_BIG_FIELDLENGTH
|
1
by brian
clean slate |
386 |
create table t1(a varbinary(65537)); |
387 |
||
388 |
#
|
|
389 |
# Bug #9881: problem with altering table |
|
390 |
#
|
|
391 |
||
392 |
create table t1(a int, b date not null); |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
393 |
alter table t1 modify a bigint not null; |
942.3.1
by Vladimir Kolesnikov
test generalizations |
394 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1
by brian
clean slate |
395 |
show create table t1; |
396 |
drop table t1; |
|
397 |
||
398 |
#
|
|
399 |
# Bug #11964: alter table with timestamp field |
|
400 |
#
|
|
401 |
||
402 |
create table t1(a int, b timestamp); |
|
403 |
alter table t1 add primary key(a); |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
404 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1
by brian
clean slate |
405 |
show create table t1; |
406 |
drop table t1; |
|
407 |
create table t1(a int, b timestamp default 20050102030405); |
|
408 |
alter table t1 add primary key(a); |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
409 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1
by brian
clean slate |
410 |
show create table t1; |
411 |
drop table t1; |
|
412 |
||
413 |
||
414 |
#
|
|
415 |
# Bug#17626 CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode |
|
416 |
#
|
|
417 |
create table t1 (date date not null); |
|
418 |
create table t2 select date from t1; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
419 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1
by brian
clean slate |
420 |
show create table t2; |
421 |
drop table t2,t1; |
|
422 |
||
423 |
create table t1 (i int) |
|
1245.3.4
by Stewart Smith
make the equals of KEY=VALUE required for CREATE TABLE options |
424 |
comment='123456789*123456789*123456789*123456789*123456789*123456789*'; |
942.3.1
by Vladimir Kolesnikov
test generalizations |
425 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1
by brian
clean slate |
426 |
show create table t1; |
427 |
drop table t1; |
|
428 |
||
429 |
#
|
|
430 |
# Bug #26359: Strings becoming truncated and converted to numbers under STRICT mode |
|
431 |
#
|
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
432 |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
433 |
create table t1(col1 int, col2 int, |
434 |
col3 int, col4 int, |
|
435 |
col7 int, col8 int, |
|
436 |
col9 bigint, col10 bigint); |
|
784.2.1
by Stewart Smith
fix strict test for drizzle. |
437 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
438 |
insert into t1(col1) values('-'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
439 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
440 |
insert into t1(col2) values('+'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
441 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
442 |
insert into t1(col3) values('-'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
443 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
444 |
insert into t1(col4) values('+'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
445 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
446 |
insert into t1(col7) values('-'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
447 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
448 |
insert into t1(col8) values('+'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
449 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
450 |
insert into t1(col9) values('-'); |
784.2.1
by Stewart Smith
fix strict test for drizzle. |
451 |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
1
by brian
clean slate |
452 |
insert into t1(col10) values('+'); |
453 |
drop table t1; |
|
454 |
||
455 |
||
456 |
#
|
|
457 |
# Bug#27069 set with identical elements are created |
|
458 |
#
|
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
459 |
--error ER_DUPLICATED_VALUE_IN_TYPE
|
1
by brian
clean slate |
460 |
create table t1 (f1 enum('a','a')); |
461 |
||
462 |
--echo End of 5.0 tests
|