1 # Testing of "strict" mode
3 -- source include/have_innodb.inc
5 set @org_mode=@@sql_mode;
6 set @@sql_mode='ansi,traditional';
10 DROP TABLE IF EXISTS t1, t2;
13 # Test INSERT with DATE
15 CREATE TABLE t1 (col1 date);
16 INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');
17 INSERT INTO t1 VALUES('0000-10-31');
19 # All test cases expected to fail should return
20 # SQLSTATE 22007 <invalid date value>
22 INSERT INTO t1 VALUES('2004-0-31');
24 INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31');
26 INSERT INTO t1 VALUES('2004-10-0');
28 INSERT INTO t1 VALUES('2004-09-31');
30 INSERT INTO t1 VALUES('2004-10-32');
32 INSERT INTO t1 VALUES('2003-02-29');
34 INSERT INTO t1 VALUES('2004-13-15');
36 INSERT INTO t1 VALUES('0000-00-00');
37 # Standard says we should return SQLSTATE 22018
39 INSERT INTO t1 VALUES ('59');
41 # Test the different related modes
42 set @@sql_mode='STRICT_ALL_TABLES';
43 INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31');
44 set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
46 INSERT INTO t1 VALUES('2004-0-30');
48 INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05');
49 INSERT INTO t1 VALUES('0000-00-00');
50 INSERT IGNORE INTO t1 VALUES('2004-0-29');
51 set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
53 INSERT INTO t1 VALUES('0000-00-00');
54 INSERT IGNORE INTO t1 VALUES('0000-00-00');
55 INSERT INTO t1 VALUES ('2004-0-30');
57 INSERT INTO t1 VALUES ('2004-2-30');
58 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
59 INSERT INTO t1 VALUES ('2004-2-30');
60 set @@sql_mode='ansi,traditional';
61 INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00');
66 # Test difference in behaviour with InnoDB and MyISAM tables
68 set @@sql_mode='strict_trans_tables';
69 CREATE TABLE t1 (col1 date) engine=myisam;
71 INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
72 INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
73 INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
75 INSERT INTO t1 VALUES ('2003-02-29');
76 INSERT ignore INTO t1 VALUES('2003-02-30');
77 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
78 INSERT ignore INTO t1 VALUES('2003-02-31');
82 set @@sql_mode='strict_trans_tables';
83 CREATE TABLE t1 (col1 date) engine=innodb;
85 INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');
87 INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');
88 INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');
90 INSERT INTO t1 VALUES ('2003-02-29');
91 INSERT ignore INTO t1 VALUES('2003-02-30');
92 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
93 INSERT ignore INTO t1 VALUES('2003-02-31');
96 set @@sql_mode='ansi,traditional';
98 # Test INSERT with DATETIME
100 CREATE TABLE t1 (col1 datetime);
101 INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
102 INSERT INTO t1 VALUES('0000-10-31 15:30:00');
104 # All test cases expected to fail should return
105 # SQLSTATE 22007 <invalid datetime value>
107 INSERT INTO t1 VALUES('2004-0-31 15:30:00');
109 INSERT INTO t1 VALUES('2004-10-0 15:30:00');
111 INSERT INTO t1 VALUES('2004-09-31 15:30:00');
113 INSERT INTO t1 VALUES('2004-10-32 15:30:00');
115 INSERT INTO t1 VALUES('2003-02-29 15:30:00');
117 INSERT INTO t1 VALUES('2004-13-15 15:30:00');
119 INSERT INTO t1 VALUES('0000-00-00 15:30:00');
120 # Standard says we should return SQLSTATE 22018
122 INSERT INTO t1 VALUES ('59');
126 # Test INSERT with TIMESTAMP
128 CREATE TABLE t1 (col1 timestamp);
129 INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');
131 # All test cases expected to fail should return
132 # SQLSTATE 22007 <invalid datetime value>
133 # Standard says we should return ok, but we can't as this is out of range
135 INSERT INTO t1 VALUES('0000-10-31 15:30:00');
137 INSERT INTO t1 VALUES('2004-0-31 15:30:00');
139 INSERT INTO t1 VALUES('2004-10-0 15:30:00');
141 INSERT INTO t1 VALUES('2004-09-31 15:30:00');
143 INSERT INTO t1 VALUES('2004-10-32 15:30:00');
145 INSERT INTO t1 VALUES('2003-02-29 15:30:00');
147 INSERT INTO t1 VALUES('2004-13-15 15:30:00');
149 INSERT INTO t1 VALUES('2004-02-29 25:30:00');
151 INSERT INTO t1 VALUES('2004-02-29 15:65:00');
153 INSERT INTO t1 VALUES('2004-02-29 15:31:61');
155 INSERT INTO t1 VALUES('0000-00-00 15:30:00');
157 INSERT INTO t1 VALUES('0000-00-00 00:00:00');
158 INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');
159 # Standard says we should return SQLSTATE 22018
161 INSERT INTO t1 VALUES ('59');
163 set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
165 INSERT INTO t1 VALUES('2004-0-31 15:30:00');
167 INSERT INTO t1 VALUES('2004-10-0 15:30:00');
169 INSERT INTO t1 VALUES('2004-10-32 15:30:00');
171 INSERT INTO t1 VALUES('2004-02-30 15:30:04');
172 INSERT INTO t1 VALUES('0000-00-00 00:00:00');
173 set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
174 INSERT INTO t1 VALUES('0000-00-00 00:00:00');
175 set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
177 INSERT INTO t1 VALUES('0000-00-00 00:00:00');
178 set @@sql_mode='ansi,traditional';
183 #### Test INSERT with STR_TO_DATE into DATE/DATETIME/TIMESTAMP
185 CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
187 INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y'));
188 INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
189 INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
191 ## Test INSERT with STR_TO_DATE into DATE
192 # All test cases expected to fail should return
193 # SQLSTATE 22007 <invalid date value>
195 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
198 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
200 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
202 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
204 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
206 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
208 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
210 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
212 ## Test INSERT with STR_TO_DATE into DATETIME
213 # All test cases expected to fail should return
214 # SQLSTATE 22007 <invalid datetime value>
216 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
219 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
221 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
223 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
225 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
227 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
229 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
231 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
233 ## Test INSERT with STR_TO_DATE into TIMESTAMP
234 # All test cases expected to fail should return
235 # SQLSTATE 22007 <invalid datetime value>
238 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
240 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
242 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
244 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
246 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
248 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
250 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
252 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
257 #### Test INSERT with CAST AS DATE/DATETIME into DATE/DATETIME/TIMESTAMP
259 CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
261 INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));
262 INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
263 INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
266 ## Test INSERT with CAST AS DATE into DATE
267 # All test cases expected to fail should return
268 # SQLSTATE 22007 <invalid date value>
270 INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE));
273 INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE));
275 INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE));
277 # deactivated because of Bug#8294
278 # Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
280 # INSERT INTO t1 (col1) VALUES(CAST('2004-9-31' AS DATE));
282 # INSERT INTO t1 (col1) VALUES(CAST('2004-10-32' AS DATE));
284 # INSERT INTO t1 (col1) VALUES(CAST('2003-02-29' AS DATE));
286 # INSERT INTO t1 (col1) VALUES(CAST('2004-13-15' AS DATE));
288 # deactivated because of Bug#6145
289 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
291 INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE));
293 ## Test INSERT with CAST AS DATETIME into DATETIME
294 # All test cases expected to fail should return
295 # SQLSTATE 22007 <invalid datetime value>
297 INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
300 INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
302 INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
304 # deactivated because of Bug#8294
305 # Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
307 #INSERT INTO t1 (col2) VALUES(CAST('2004-9-31 15:30' AS DATETIME));
309 #INSERT INTO t1 (col2) VALUES(CAST('2004-10-32 15:30' AS DATETIME));
311 #INSERT INTO t1 (col2) VALUES(CAST('2003-02-29 15:30' AS DATETIME));
313 #INSERT INTO t1 (col2) VALUES(CAST('2004-13-15 15:30' AS DATETIME));
315 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
317 INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME));
319 ## Test INSERT with CAST AS DATETIME into TIMESTAMP
320 # All test cases expected to fail should return
321 # SQLSTATE 22007 <invalid datetime value>
323 INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
325 # We accept this to be a failure
328 INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
330 INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME));
331 # should return SQLSTATE 22007 <invalid datetime value>
333 # deactivated because of Bug#8294
334 # Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
336 #INSERT INTO t1 (col3) VALUES(CAST('2004-9-31 15:30' AS DATETIME));
338 #INSERT INTO t1 (col3) VALUES(CAST('2004-10-32 15:30' AS DATETIME));
340 #INSERT INTO t1 (col3) VALUES(CAST('2003-02-29 15:30' AS DATETIME));
342 #INSERT INTO t1 (col3) VALUES(CAST('2004-13-15 15:30' AS DATETIME));
344 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
346 INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME));
351 #### Test INSERT with CONVERT to DATE/DATETIME into DATE/DATETIME/TIMESTAMP
353 CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
355 INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE));
356 INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
357 INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
360 ## Test INSERT with CONVERT to DATE into DATE
361 # All test cases expected to fail should return
362 # SQLSTATE 22007 <invalid date value>
364 INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE));
367 INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE));
369 INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE));
371 # deactivated because of Bug#8294
372 # Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
374 #INSERT INTO t1 (col1) VALUES(CONVERT('2004-9-31' , DATE));
376 #INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-32' , DATE));
378 #INSERT INTO t1 (col1) VALUES(CONVERT('2003-02-29' , DATE));
380 #INSERT INTO t1 (col1) VALUES(CONVERT('2004-13-15',DATE));
382 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
384 INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE));
386 ## Test INSERT with CONVERT to DATETIME into DATETIME
387 # All test cases expected to fail should return
388 # SQLSTATE 22007 <invalid datetime value>
390 INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
393 INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
395 INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
397 # deactivated because of Bug#8294
398 # Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
400 #INSERT INTO t1 (col2) VALUES(CONVERT('2004-9-31 15:30',DATETIME));
402 #INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-32 15:30',DATETIME));
404 #INSERT INTO t1 (col2) VALUES(CONVERT('2003-02-29 15:30',DATETIME));
406 #INSERT INTO t1 (col2) VALUES(CONVERT('2004-13-15 15:30',DATETIME));
408 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
410 INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME));
412 ## Test INSERT with CONVERT to DATETIME into DATETIME
413 # All test cases expected to fail should return
414 # SQLSTATE 22007 <invalid datetime value>
416 INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
418 # We accept this to be a failure
421 INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
423 INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME));
425 # deactivated because of Bug#8294
426 # Bug#8294 Traditional: Misleading error message for invalid CAST to DATE
428 #INSERT INTO t1 (col3) VALUES(CONVERT('2004-9-31 15:30',DATETIME));
430 #INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-32 15:30',DATETIME));
432 #INSERT INTO t1 (col3) VALUES(CONVERT('2003-02-29 15:30',DATETIME));
434 #INSERT INTO t1 (col3) VALUES(CONVERT('2004-13-15 15:30',DATETIME));
436 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values
438 INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME));
443 # Test INSERT with TINYINT
445 CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED);
446 INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0);
447 # Test that we restored the mode checking properly after an ok query
448 SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
450 INSERT INTO t1 (col1) VALUES(-129);
452 INSERT INTO t1 (col1) VALUES(128);
454 INSERT INTO t1 (col2) VALUES(-1);
456 INSERT INTO t1 (col2) VALUES(256);
458 INSERT INTO t1 (col1) VALUES('-129');
460 INSERT INTO t1 (col1) VALUES('128');
462 INSERT INTO t1 (col2) VALUES('-1');
464 INSERT INTO t1 (col2) VALUES('256');
466 INSERT INTO t1 (col1) VALUES(128.0);
468 INSERT INTO t1 (col2) VALUES(-1.0);
470 INSERT INTO t1 (col2) VALUES(256.0);
471 SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1;
473 UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
475 UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0;
477 UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0;
478 set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
479 INSERT INTO t1 values (1/0,1/0);
480 set @@sql_mode='ansi,traditional';
481 SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;
482 # Should return SQLSTATE 22018 invalid character value for cast
484 INSERT INTO t1 (col1) VALUES ('');
486 INSERT INTO t1 (col1) VALUES ('a59b');
488 INSERT INTO t1 (col1) VALUES ('1a');
489 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
490 INSERT IGNORE INTO t1 values (1/0,1/0);
491 set @@sql_mode='ansi';
492 INSERT INTO t1 values (1/0,1/0);
493 set @@sql_mode='ansi,traditional';
494 INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256');
495 INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0);
496 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
501 # Test INSERT with SMALLINT
503 CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED);
504 INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0);
507 INSERT INTO t1 (col1) VALUES(-32769);
509 INSERT INTO t1 (col1) VALUES(32768);
511 INSERT INTO t1 (col2) VALUES(-1);
513 INSERT INTO t1 (col2) VALUES(65536);
515 INSERT INTO t1 (col1) VALUES('-32769');
517 INSERT INTO t1 (col1) VALUES('32768');
519 INSERT INTO t1 (col2) VALUES('-1');
521 INSERT INTO t1 (col2) VALUES('65536');
523 INSERT INTO t1 (col1) VALUES(-32769.0);
525 INSERT INTO t1 (col1) VALUES(32768.0);
527 INSERT INTO t1 (col2) VALUES(-1.0);
529 INSERT INTO t1 (col2) VALUES(65536.0);
531 UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
533 UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;
535 UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0;
537 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
539 INSERT INTO t1 (col1) VALUES ('');
541 INSERT INTO t1 (col1) VALUES ('a59b');
543 INSERT INTO t1 (col1) VALUES ('1a');
544 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
545 INSERT IGNORE INTO t1 values (1/0,1/0);
546 INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536);
547 INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536');
548 INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0);
549 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
554 # Test INSERT with MEDIUMINT
556 CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED);
557 INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0);
559 INSERT INTO t1 (col1) VALUES(-8388609);
561 INSERT INTO t1 (col1) VALUES(8388608);
563 INSERT INTO t1 (col2) VALUES(-1);
565 INSERT INTO t1 (col2) VALUES(16777216);
567 INSERT INTO t1 (col1) VALUES('-8388609');
569 INSERT INTO t1 (col1) VALUES('8388608');
571 INSERT INTO t1 (col2) VALUES('-1');
573 INSERT INTO t1 (col2) VALUES('16777216');
575 INSERT INTO t1 (col1) VALUES(-8388609.0);
577 INSERT INTO t1 (col1) VALUES(8388608.0);
579 INSERT INTO t1 (col2) VALUES(-1.0);
581 INSERT INTO t1 (col2) VALUES(16777216.0);
584 UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
586 UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;
588 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
590 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
592 INSERT INTO t1 (col1) VALUES ('');
594 INSERT INTO t1 (col1) VALUES ('a59b');
596 INSERT INTO t1 (col1) VALUES ('1a');
597 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
598 INSERT IGNORE INTO t1 values (1/0,1/0);
599 INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216);
600 INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216');
601 INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0);
602 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
607 # Test INSERT with INT
609 CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED);
610 INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0);
612 INSERT INTO t1 (col1) VALUES(-2147483649);
614 INSERT INTO t1 (col1) VALUES(2147643648);
616 INSERT INTO t1 (col2) VALUES(-1);
618 INSERT INTO t1 (col2) VALUES(4294967296);
620 INSERT INTO t1 (col1) VALUES('-2147483649');
622 INSERT INTO t1 (col1) VALUES('2147643648');
624 INSERT INTO t1 (col2) VALUES('-1');
626 INSERT INTO t1 (col2) VALUES('4294967296');
628 INSERT INTO t1 (col1) VALUES(-2147483649.0);
630 INSERT INTO t1 (col1) VALUES(2147643648.0);
632 INSERT INTO t1 (col2) VALUES(-1.0);
634 INSERT INTO t1 (col2) VALUES(4294967296.0);
637 UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;
639 UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0;
641 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
643 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
645 INSERT INTO t1 (col1) VALUES ('');
647 INSERT INTO t1 (col1) VALUES ('a59b');
649 INSERT INTO t1 (col1) VALUES ('1a');
650 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
651 INSERT IGNORE INTO t1 values (1/0,1/0);
652 INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296);
653 INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296');
654 INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0);
655 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
659 # Test INSERT with BIGINT
660 # Note that this doesn't behave 100 % to standard as we rotate
661 # integers when it's too big/small (just like C)
663 CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED);
664 INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615);
665 INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615');
666 INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0);
669 INSERT INTO t1 (col1) VALUES(-9223372036854775809);
671 INSERT INTO t1 (col1) VALUES(9223372036854775808);
673 INSERT INTO t1 (col2) VALUES(-1);
676 INSERT INTO t1 (col2) VALUES(18446744073709551616);
678 INSERT INTO t1 (col1) VALUES('-9223372036854775809');
680 INSERT INTO t1 (col1) VALUES('9223372036854775808');
682 INSERT INTO t1 (col2) VALUES('-1');
684 INSERT INTO t1 (col2) VALUES('18446744073709551616');
686 # Note that the following two double numbers are slighty bigger than max/min
687 # bigint becasue of rounding errors when converting it to bigint
689 INSERT INTO t1 (col1) VALUES(-9223372036854785809.0);
691 INSERT INTO t1 (col1) VALUES(9223372036854785808.0);
693 INSERT INTO t1 (col2) VALUES(-1.0);
695 INSERT INTO t1 (col2) VALUES(18446744073709551616.0);
697 # The following doesn't give an error as it's done in integer context
698 # UPDATE t1 SET col1=col1 - 5000 WHERE col1 < 0;
699 # UPDATE t1 SET col2 =col2 + 5000 WHERE col2 > 0;
702 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
704 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
706 INSERT INTO t1 (col1) VALUES ('');
708 INSERT INTO t1 (col1) VALUES ('a59b');
710 INSERT INTO t1 (col1) VALUES ('1a');
711 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
712 INSERT IGNORE INTO t1 values (1/0,1/0);
713 INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616);
714 INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616');
715 INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0);
716 UPDATE IGNORE t1 SET col2=1/NULL where col1=0;
720 # Test INSERT with NUMERIC
722 CREATE TABLE t1 (col1 NUMERIC(4,2));
723 INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01);
724 # Note that the +/-10.5555 is inserted as +/-10.55, not +/-10.56 !
725 INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01');
727 # The 2 following inserts should generate a warning, but doesn't yet
728 # because NUMERIC works like DECIMAL
730 INSERT INTO t1 VALUES (101.55);
732 INSERT INTO t1 VALUES (101);
734 INSERT INTO t1 VALUES (-101.55);
736 INSERT INTO t1 VALUES (1010.55);
738 INSERT INTO t1 VALUES (1010);
739 # The 2 following inserts should generate a warning, but doesn't yet
740 # because NUMERIC works like DECIMAL
742 INSERT INTO t1 VALUES ('101.55');
744 INSERT INTO t1 VALUES ('101');
746 INSERT INTO t1 VALUES ('-101.55');
748 INSERT INTO t1 VALUES ('-1010.55');
750 INSERT INTO t1 VALUES ('-100E+1');
752 INSERT INTO t1 VALUES ('-100E');
754 UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11;
756 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;
758 UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;
761 INSERT INTO t1 (col1) VALUES ('');
764 INSERT INTO t1 (col1) VALUES ('a59b');
766 INSERT INTO t1 (col1) VALUES ('1a');
767 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
768 INSERT IGNORE INTO t1 values (1/0);
769 INSERT IGNORE INTO t1 VALUES(1000),(-1000);
770 INSERT IGNORE INTO t1 VALUES('1000'),('-1000');
771 INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0);
772 UPDATE IGNORE t1 SET col1=1/NULL where col1=0;
776 # Test INSERT with FLOAT
778 CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED);
779 INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38);
780 INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38');
781 # We don't give warnings for underflow
782 INSERT INTO t1 (col1) VALUES (3E-46);
784 INSERT INTO t1 (col1) VALUES (+3.4E+39);
786 INSERT INTO t1 (col2) VALUES (-1.1E-3);
788 INSERT INTO t1 (col1) VALUES ('+3.4E+39');
790 INSERT INTO t1 (col2) VALUES ('-1.1E-3');
792 UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;
794 UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
796 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
798 INSERT INTO t1 (col1) VALUES ('');
800 INSERT INTO t1 (col1) VALUES ('a59b');
802 INSERT INTO t1 (col1) VALUES ('1a');
803 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
804 INSERT IGNORE INTO t1 (col1) VALUES (1/0);
805 INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39);
806 INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39');
810 # Test INSERT with DOUBLE
812 CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED);
813 INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308);
814 INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308');
815 # We don't give warnings for underflow
816 INSERT INTO t1 (col1) VALUES (-2.2E-330);
818 INSERT INTO t1 (col1) VALUES (+1.7E+309);
820 INSERT INTO t1 (col2) VALUES (-1.1E-3);
822 INSERT INTO t1 (col1) VALUES ('+1.8E+309');
824 INSERT INTO t1 (col2) VALUES ('-1.2E-3');
825 UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;
827 UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;
829 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;
831 INSERT INTO t1 (col1) VALUES ('');
833 INSERT INTO t1 (col1) VALUES ('a59b');
835 INSERT INTO t1 (col1) VALUES ('1a');
836 INSERT IGNORE INTO t1 (col1) VALUES ('2a');
837 INSERT IGNORE INTO t1 (col1) values (1/0);
839 INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309);
840 INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309');
842 --replace_result -0 0 1.7976931348623e+308 1.79769313486232e+308
846 # Testing INSERT with CHAR/VARCHAR
848 CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));
849 INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello ');
851 INSERT INTO t1 (col1) VALUES ('hellobob');
853 INSERT INTO t1 (col2) VALUES ('hellobob');
854 INSERT INTO t1 (col2) VALUES ('hello ');
856 UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he';
858 UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he';
859 INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob');
860 UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he';
864 # Testing INSERT with ENUM
866 CREATE TABLE t1 (col1 enum('red','blue','green'));
867 INSERT INTO t1 VALUES ('red'),('blue'),('green');
869 INSERT INTO t1 (col1) VALUES ('yellow');
871 INSERT INTO t1 (col1) VALUES ('redd');
873 INSERT INTO t1 VALUES ('');
875 UPDATE t1 SET col1 ='yellow' WHERE col1 ='green';
876 INSERT IGNORE INTO t1 VALUES ('yellow');
877 UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue';
881 # Testing of insert of NULL in not NULL column
883 CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL);
884 INSERT INTO t1 VALUES (100, 'hello', '2004-08-20');
885 INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21');
887 INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01');
889 INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01');
891 INSERT INTO t1 VALUES (103,'',NULL);
893 UPDATE t1 SET col1=NULL WHERE col1 =100;
895 UPDATE t1 SET col2 =NULL WHERE col2 ='hello';
897 UPDATE t1 SET col2 =NULL where col3 IS NOT NULL;
898 INSERT IGNORE INTO t1 values (NULL,NULL,NULL);
902 # Testing of default values
904 CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);
905 SHOW CREATE TABLE t1;
906 INSERT INTO t1 VALUES (1, 'hello');
907 INSERT INTO t1 (col2) VALUES ('hello2');
909 INSERT INTO t1 (col2) VALUES (NULL);
911 INSERT INTO t1 (col1) VALUES (2);
913 INSERT INTO t1 VALUES(default(col1),default(col2));
915 INSERT INTO t1 (col1) SELECT 1;
917 INSERT INTO t1 SELECT 1,NULL;
918 INSERT IGNORE INTO t1 values (NULL,NULL);
919 INSERT IGNORE INTO t1 (col1) values (3);
920 INSERT IGNORE INTO t1 () values ();
925 # Bug #9029 Traditional: Wrong SQLSTATE returned for string truncation
928 set sql_mode='traditional';
929 create table t1 (charcol char(255), varcharcol varchar(255),
930 binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext,
931 tinyblobcol tinyblob);
933 insert into t1 (charcol) values (repeat('x',256));
935 insert into t1 (varcharcol) values (repeat('x',256));
937 insert into t1 (binarycol) values (repeat('x',256));
939 insert into t1 (varbinarycol) values (repeat('x',256));
941 insert into t1 (tinytextcol) values (repeat('x',256));
943 insert into t1 (tinyblobcol) values (repeat('x',256));
948 # Bug #5902: STR_TO_DATE() didn't give errors in traditional mode
951 set sql_mode='traditional';
952 create table t1 (col1 datetime);
954 insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
956 insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
958 insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
960 insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
962 insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
963 insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
964 insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
965 insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
967 # Some correct values, just to test the functions
968 insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i'));
969 insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r'));
970 insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T'));
974 # Check that select don't abort even in strict mode (for now)
975 set sql_mode='traditional';
977 select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL;
982 # Check insert with wrong CAST() (Bug #5912)
985 create table t1 (col1 char(3), col2 integer);
987 insert into t1 (col1) values (cast(1000 as char(3)));
989 insert into t1 (col1) values (cast(1000E+0 as char(3)));
991 insert into t1 (col1) values (cast(1000.0 as char(3)));
993 insert into t1 (col2) values (cast('abc' as signed integer));
995 insert into t1 (col2) values (10E+0 + 'a');
997 insert into t1 (col2) values (cast('10a' as unsigned integer));
998 insert into t1 (col2) values (cast('10' as unsigned integer));
999 insert into t1 (col2) values (cast('10' as signed integer));
1000 insert into t1 (col2) values (10E+0 + '0 ');
1005 # Zero dates using numbers was not checked properly (Bug #5933 & #6145)
1008 create table t1 (col1 date, col2 datetime, col3 timestamp);
1010 insert into t1 values (0,0,0);
1012 insert into t1 values (0.0,0.0,0.0);
1014 insert into t1 (col1) values (convert('0000-00-00',date));
1016 insert into t1 (col1) values (cast('0000-00-00' as date));
1018 set sql_mode='no_zero_date';
1019 insert into t1 values (0,0,0);
1020 insert into t1 values (0.0,0.0,0.0);
1022 set sql_mode='traditional';
1023 create table t1 (col1 date);
1024 insert ignore into t1 values ('0000-00-00');
1026 insert into t1 select * from t1;
1027 insert ignore into t1 values ('0000-00-00');
1028 insert ignore into t1 (col1) values (cast('0000-00-00' as date));
1030 insert into t1 select * from t1;
1032 alter table t1 modify col1 datetime;
1033 alter ignore table t1 modify col1 datetime;
1035 insert into t1 select * from t1;
1040 # Test of inserting an invalid value via a stored procedure (Bug #5907)
1042 create table t1 (col1 tinyint);
1043 drop procedure if exists t1;
1045 create procedure t1 () begin declare exit handler for sqlexception
1046 select'a'; insert into t1 values (200); end;|
1056 set sql_mode=@org_mode;
1058 # Test fields with no default value that are NOT NULL (Bug #5986)
1059 SET @@sql_mode = 'traditional';
1060 CREATE TABLE t1 (i int not null);
1062 INSERT INTO t1 VALUES ();
1064 INSERT INTO t1 VALUES (DEFAULT);
1066 INSERT INTO t1 VALUES (DEFAULT(i));
1067 ALTER TABLE t1 ADD j int;
1069 INSERT INTO t1 SET j = 1;
1071 INSERT INTO t1 SET j = 1, i = DEFAULT;
1073 INSERT INTO t1 SET j = 1, i = DEFAULT(i);
1075 INSERT INTO t1 VALUES (DEFAULT,1);
1077 SET @@sql_mode = '';
1078 CREATE TABLE t1 (i int not null);
1079 INSERT INTO t1 VALUES ();
1080 INSERT INTO t1 VALUES (DEFAULT);
1081 # DEFAULT(i) is an error even with the default sql_mode
1083 INSERT INTO t1 VALUES (DEFAULT(i));
1084 ALTER TABLE t1 ADD j int;
1085 INSERT INTO t1 SET j = 1;
1086 INSERT INTO t1 SET j = 1, i = DEFAULT;
1088 INSERT INTO t1 SET j = 1, i = DEFAULT(i);
1089 INSERT INTO t1 VALUES (DEFAULT,1);
1093 # Bugs #8295 and #8296: varchar and varbinary conversion
1096 set @@sql_mode='traditional';
1098 create table t1(a varchar(65537));
1100 create table t1(a varbinary(65537));
1103 # Bug #9881: problem with altering table
1106 set @@sql_mode='traditional';
1107 create table t1(a int, b date not null);
1108 alter table t1 modify a bigint unsigned not null;
1109 show create table t1;
1113 # Bug #5906: handle invalid date due to conversion
1115 set @@sql_mode='traditional';
1116 create table t1 (d date);
1118 insert into t1 values ('2000-10-00');
1120 insert into t1 values (1000);
1121 insert into t1 values ('2000-10-01');
1123 update t1 set d = 1100;
1128 # Bug #11964: alter table with timestamp field
1131 set @@sql_mode='traditional';
1132 create table t1(a int, b timestamp);
1133 alter table t1 add primary key(a);
1134 show create table t1;
1136 create table t1(a int, b timestamp default 20050102030405);
1137 alter table t1 add primary key(a);
1138 show create table t1;
1145 set @@sql_mode='traditional';
1146 create table t1(a bit(2));
1148 insert into t1 values(b'101');
1153 # Bug#17626 CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode
1155 set sql_mode='traditional';
1156 create table t1 (date date not null);
1157 create table t2 select date from t1;
1158 show create table t2;
1160 set @@sql_mode= @org_mode;
1163 # Bug #13934 Silent truncation of table comments
1165 set @@sql_mode='traditional';
1166 --error ER_TOO_LONG_TABLE_COMMENT
1167 create table t1 (i int)
1168 comment '123456789*123456789*123456789*123456789*123456789*
1169 123456789*123456789*123456789*123456789*123456789*';
1170 --error ER_TOO_LONG_FIELD_COMMENT
1173 '123456789*123456789*123456789*123456789*
1174 123456789*123456789*123456789*123456789*
1175 123456789*123456789*123456789*123456789*
1176 123456789*123456789*123456789*123456789*
1177 123456789*123456789*123456789*123456789*
1178 123456789*123456789*123456789*123456789*
1179 123456789*123456789*123456789*123456789*');
1180 set @@sql_mode= @org_mode;
1183 '123456789*123456789*123456789*123456789*
1184 123456789*123456789*123456789*123456789*
1185 123456789*123456789*123456789*123456789*
1186 123456789*123456789*123456789*123456789*
1187 123456789*123456789*123456789*123456789*
1188 123456789*123456789*123456789*123456789*
1189 123456789*123456789*123456789*123456789*');
1191 select column_name, column_comment from information_schema.columns where
1192 table_schema = 'test' and table_name = 't1';
1196 create table t1 (i int)
1197 comment '123456789*123456789*123456789*123456789*123456789*123456789*';
1198 show create table t1;
1202 # Bug #39591: Crash if table comment is longer than 62 characters
1205 #60 chars, 120 (+1) bytes (UTF-8 with 2-byte chars)
1206 CREATE TABLE t3 (f1 INT) COMMENT 'כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן';
1207 SHOW CREATE TABLE t3;
1211 # Bug #26359: Strings becoming truncated and converted to numbers under STRICT mode
1213 set sql_mode= 'traditional';
1214 create table t1(col1 tinyint, col2 tinyint unsigned,
1215 col3 smallint, col4 smallint unsigned,
1216 col5 mediumint, col6 mediumint unsigned,
1217 col7 int, col8 int unsigned,
1218 col9 bigint, col10 bigint unsigned);
1220 insert into t1(col1) values('-');
1222 insert into t1(col2) values('+');
1224 insert into t1(col3) values('-');
1226 insert into t1(col4) values('+');
1228 insert into t1(col5) values('-');
1230 insert into t1(col6) values('+');
1232 insert into t1(col7) values('-');
1234 insert into t1(col8) values('+');
1236 insert into t1(col9) values('-');
1238 insert into t1(col10) values('+');
1242 # Bug #27176: Assigning a string to an year column has unexpected results
1244 set sql_mode='traditional';
1245 create table t1(a year);
1247 insert into t1 values ('-');
1249 insert into t1 values ('+');
1251 insert into t1 values ('');
1253 insert into t1 values ('2000a');
1255 insert into t1 values ('2E3x');
1259 # Bug#27069 set with identical elements are created
1261 set sql_mode='traditional';
1263 create table t1 (f1 set('a','a'));
1265 create table t1 (f1 enum('a','a'));
1268 # Bug #22824: strict, datetime, NULL, wrong warning
1270 set @@sql_mode='NO_ZERO_DATE';
1271 create table t1(a datetime not null);
1272 select count(*) from t1 where a is null;
1275 --echo End of 5.0 tests