Debian Codegraph @

mysql-test/suite/engines/funcs/r/ta_set_drop_default.result

raw
1 DROP TABLE IF EXISTS t1;
2 SET sql_mode='NO_ENGINE_SUBSTITUTION';
3 CREATE TABLE t1(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
4 SHOW TABLES;
5 Tables_in_test
6 t1
7 SHOW CREATE TABLE t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `c1` tinyint NOT NULL,
11 `c2` tinyint DEFAULT NULL,
12 PRIMARY KEY (`c1`)
13 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
14 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
15 INSERT INTO t1(c1) VALUES(1);
16 SELECT * FROM t1 ORDER BY c1;
17 c1 c2
18 1 10
19 SHOW CREATE TABLE t1;
20 Table Create Table
21 t1 CREATE TABLE `t1` (
22 `c1` tinyint NOT NULL,
23 `c2` tinyint DEFAULT '10',
24 PRIMARY KEY (`c1`)
25 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
26 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
27 INSERT INTO t1(c1) VALUES(2);
28 Warnings:
29 Warning 1364 Field 'c2' doesn't have a default value
30 SELECT * FROM t1 ORDER BY c1;
31 c1 c2
32 1 10
33 2 NULL
34 SHOW CREATE TABLE t1;
35 Table Create Table
36 t1 CREATE TABLE `t1` (
37 `c1` tinyint NOT NULL,
38 `c2` tinyint,
39 PRIMARY KEY (`c1`)
40 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
41 DROP TABLE t1;
42 SHOW TABLES;
43 Tables_in_test
44 CREATE TABLE t1(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
45 SHOW TABLES;
46 Tables_in_test
47 t1
48 SHOW CREATE TABLE t1;
49 Table Create Table
50 t1 CREATE TABLE `t1` (
51 `c1` smallint NOT NULL,
52 `c2` smallint DEFAULT NULL,
53 PRIMARY KEY (`c1`)
54 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
55 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
56 INSERT INTO t1(c1) VALUES(1);
57 SELECT * FROM t1 ORDER BY c1;
58 c1 c2
59 1 10
60 SHOW CREATE TABLE t1;
61 Table Create Table
62 t1 CREATE TABLE `t1` (
63 `c1` smallint NOT NULL,
64 `c2` smallint DEFAULT '10',
65 PRIMARY KEY (`c1`)
66 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
67 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
68 INSERT INTO t1(c1) VALUES(2);
69 Warnings:
70 Warning 1364 Field 'c2' doesn't have a default value
71 SELECT * FROM t1 ORDER BY c1;
72 c1 c2
73 1 10
74 2 NULL
75 SHOW CREATE TABLE t1;
76 Table Create Table
77 t1 CREATE TABLE `t1` (
78 `c1` smallint NOT NULL,
79 `c2` smallint,
80 PRIMARY KEY (`c1`)
81 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
82 DROP TABLE t1;
83 SHOW TABLES;
84 Tables_in_test
85 CREATE TABLE t1(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
86 SHOW TABLES;
87 Tables_in_test
88 t1
89 SHOW CREATE TABLE t1;
90 Table Create Table
91 t1 CREATE TABLE `t1` (
92 `c1` mediumint NOT NULL,
93 `c2` mediumint DEFAULT NULL,
94 PRIMARY KEY (`c1`)
95 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
96 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
97 INSERT INTO t1(c1) VALUES(1);
98 SELECT * FROM t1 ORDER BY c1;
99 c1 c2
100 1 10
101 SHOW CREATE TABLE t1;
102 Table Create Table
103 t1 CREATE TABLE `t1` (
104 `c1` mediumint NOT NULL,
105 `c2` mediumint DEFAULT '10',
106 PRIMARY KEY (`c1`)
107 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
108 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
109 INSERT INTO t1(c1) VALUES(2);
110 Warnings:
111 Warning 1364 Field 'c2' doesn't have a default value
112 SELECT * FROM t1 ORDER BY c1;
113 c1 c2
114 1 10
115 2 NULL
116 SHOW CREATE TABLE t1;
117 Table Create Table
118 t1 CREATE TABLE `t1` (
119 `c1` mediumint NOT NULL,
120 `c2` mediumint,
121 PRIMARY KEY (`c1`)
122 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
123 DROP TABLE t1;
124 SHOW TABLES;
125 Tables_in_test
126 CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY, c2 INT);
127 SHOW TABLES;
128 Tables_in_test
129 t1
130 SHOW CREATE TABLE t1;
131 Table Create Table
132 t1 CREATE TABLE `t1` (
133 `c1` int NOT NULL,
134 `c2` int DEFAULT NULL,
135 PRIMARY KEY (`c1`)
136 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
137 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
138 INSERT INTO t1(c1) VALUES(1);
139 SELECT * FROM t1 ORDER BY c1;
140 c1 c2
141 1 10
142 SHOW CREATE TABLE t1;
143 Table Create Table
144 t1 CREATE TABLE `t1` (
145 `c1` int NOT NULL,
146 `c2` int DEFAULT '10',
147 PRIMARY KEY (`c1`)
148 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
149 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
150 INSERT INTO t1(c1) VALUES(2);
151 Warnings:
152 Warning 1364 Field 'c2' doesn't have a default value
153 SELECT * FROM t1 ORDER BY c1;
154 c1 c2
155 1 10
156 2 NULL
157 SHOW CREATE TABLE t1;
158 Table Create Table
159 t1 CREATE TABLE `t1` (
160 `c1` int NOT NULL,
161 `c2` int,
162 PRIMARY KEY (`c1`)
163 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
164 DROP TABLE t1;
165 SHOW TABLES;
166 Tables_in_test
167 CREATE TABLE t1(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
168 SHOW TABLES;
169 Tables_in_test
170 t1
171 SHOW CREATE TABLE t1;
172 Table Create Table
173 t1 CREATE TABLE `t1` (
174 `c1` int NOT NULL,
175 `c2` int DEFAULT NULL,
176 PRIMARY KEY (`c1`)
177 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
178 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
179 INSERT INTO t1(c1) VALUES(1);
180 SELECT * FROM t1 ORDER BY c1;
181 c1 c2
182 1 10
183 SHOW CREATE TABLE t1;
184 Table Create Table
185 t1 CREATE TABLE `t1` (
186 `c1` int NOT NULL,
187 `c2` int DEFAULT '10',
188 PRIMARY KEY (`c1`)
189 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
190 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
191 INSERT INTO t1(c1) VALUES(2);
192 Warnings:
193 Warning 1364 Field 'c2' doesn't have a default value
194 SELECT * FROM t1 ORDER BY c1;
195 c1 c2
196 1 10
197 2 NULL
198 SHOW CREATE TABLE t1;
199 Table Create Table
200 t1 CREATE TABLE `t1` (
201 `c1` int NOT NULL,
202 `c2` int,
203 PRIMARY KEY (`c1`)
204 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
205 DROP TABLE t1;
206 SHOW TABLES;
207 Tables_in_test
208 CREATE TABLE t1(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
209 SHOW TABLES;
210 Tables_in_test
211 t1
212 SHOW CREATE TABLE t1;
213 Table Create Table
214 t1 CREATE TABLE `t1` (
215 `c1` bigint NOT NULL,
216 `c2` bigint DEFAULT NULL,
217 PRIMARY KEY (`c1`)
218 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
219 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
220 INSERT INTO t1(c1) VALUES(1);
221 SELECT * FROM t1 ORDER BY c1;
222 c1 c2
223 1 10
224 SHOW CREATE TABLE t1;
225 Table Create Table
226 t1 CREATE TABLE `t1` (
227 `c1` bigint NOT NULL,
228 `c2` bigint DEFAULT '10',
229 PRIMARY KEY (`c1`)
230 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
231 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
232 INSERT INTO t1(c1) VALUES(2);
233 Warnings:
234 Warning 1364 Field 'c2' doesn't have a default value
235 SELECT * FROM t1 ORDER BY c1;
236 c1 c2
237 1 10
238 2 NULL
239 SHOW CREATE TABLE t1;
240 Table Create Table
241 t1 CREATE TABLE `t1` (
242 `c1` bigint NOT NULL,
243 `c2` bigint,
244 PRIMARY KEY (`c1`)
245 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
246 DROP TABLE t1;
247 SHOW TABLES;
248 Tables_in_test
249 CREATE TABLE t1(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
250 SHOW TABLES;
251 Tables_in_test
252 t1
253 SHOW CREATE TABLE t1;
254 Table Create Table
255 t1 CREATE TABLE `t1` (
256 `c1` double NOT NULL,
257 `c2` double DEFAULT NULL,
258 PRIMARY KEY (`c1`)
259 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
260 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
261 INSERT INTO t1(c1) VALUES(1);
262 SELECT * FROM t1 ORDER BY c1;
263 c1 c2
264 1 10
265 SHOW CREATE TABLE t1;
266 Table Create Table
267 t1 CREATE TABLE `t1` (
268 `c1` double NOT NULL,
269 `c2` double DEFAULT '10',
270 PRIMARY KEY (`c1`)
271 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
272 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
273 INSERT INTO t1(c1) VALUES(2);
274 Warnings:
275 Warning 1364 Field 'c2' doesn't have a default value
276 SELECT * FROM t1 ORDER BY c1;
277 c1 c2
278 1 10
279 2 NULL
280 SHOW CREATE TABLE t1;
281 Table Create Table
282 t1 CREATE TABLE `t1` (
283 `c1` double NOT NULL,
284 `c2` double,
285 PRIMARY KEY (`c1`)
286 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
287 DROP TABLE t1;
288 SHOW TABLES;
289 Tables_in_test
290 CREATE TABLE t1(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
291 SHOW TABLES;
292 Tables_in_test
293 t1
294 SHOW CREATE TABLE t1;
295 Table Create Table
296 t1 CREATE TABLE `t1` (
297 `c1` double NOT NULL,
298 `c2` double DEFAULT NULL,
299 PRIMARY KEY (`c1`)
300 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
301 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
302 INSERT INTO t1(c1) VALUES(1);
303 SELECT * FROM t1 ORDER BY c1;
304 c1 c2
305 1 10
306 SHOW CREATE TABLE t1;
307 Table Create Table
308 t1 CREATE TABLE `t1` (
309 `c1` double NOT NULL,
310 `c2` double DEFAULT '10',
311 PRIMARY KEY (`c1`)
312 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
313 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
314 INSERT INTO t1(c1) VALUES(2);
315 Warnings:
316 Warning 1364 Field 'c2' doesn't have a default value
317 SELECT * FROM t1 ORDER BY c1;
318 c1 c2
319 1 10
320 2 NULL
321 SHOW CREATE TABLE t1;
322 Table Create Table
323 t1 CREATE TABLE `t1` (
324 `c1` double NOT NULL,
325 `c2` double,
326 PRIMARY KEY (`c1`)
327 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
328 DROP TABLE t1;
329 SHOW TABLES;
330 Tables_in_test
331 CREATE TABLE t1(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
332 SHOW TABLES;
333 Tables_in_test
334 t1
335 SHOW CREATE TABLE t1;
336 Table Create Table
337 t1 CREATE TABLE `t1` (
338 `c1` float NOT NULL,
339 `c2` float DEFAULT NULL,
340 PRIMARY KEY (`c1`)
341 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
342 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
343 INSERT INTO t1(c1) VALUES(1);
344 SELECT * FROM t1 ORDER BY c1;
345 c1 c2
346 1 10
347 SHOW CREATE TABLE t1;
348 Table Create Table
349 t1 CREATE TABLE `t1` (
350 `c1` float NOT NULL,
351 `c2` float DEFAULT '10',
352 PRIMARY KEY (`c1`)
353 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
354 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
355 INSERT INTO t1(c1) VALUES(2);
356 Warnings:
357 Warning 1364 Field 'c2' doesn't have a default value
358 SELECT * FROM t1 ORDER BY c1;
359 c1 c2
360 1 10
361 2 NULL
362 SHOW CREATE TABLE t1;
363 Table Create Table
364 t1 CREATE TABLE `t1` (
365 `c1` float NOT NULL,
366 `c2` float,
367 PRIMARY KEY (`c1`)
368 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
369 DROP TABLE t1;
370 SHOW TABLES;
371 Tables_in_test
372 CREATE TABLE t1(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
373 SHOW TABLES;
374 Tables_in_test
375 t1
376 SHOW CREATE TABLE t1;
377 Table Create Table
378 t1 CREATE TABLE `t1` (
379 `c1` decimal(10,0) NOT NULL,
380 `c2` decimal(10,0) DEFAULT NULL,
381 PRIMARY KEY (`c1`)
382 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
383 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
384 INSERT INTO t1(c1) VALUES(1);
385 SELECT * FROM t1 ORDER BY c1;
386 c1 c2
387 1 10
388 SHOW CREATE TABLE t1;
389 Table Create Table
390 t1 CREATE TABLE `t1` (
391 `c1` decimal(10,0) NOT NULL,
392 `c2` decimal(10,0) DEFAULT '10',
393 PRIMARY KEY (`c1`)
394 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
395 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
396 INSERT INTO t1(c1) VALUES(2);
397 Warnings:
398 Warning 1364 Field 'c2' doesn't have a default value
399 SELECT * FROM t1 ORDER BY c1;
400 c1 c2
401 1 10
402 2 NULL
403 SHOW CREATE TABLE t1;
404 Table Create Table
405 t1 CREATE TABLE `t1` (
406 `c1` decimal(10,0) NOT NULL,
407 `c2` decimal(10,0),
408 PRIMARY KEY (`c1`)
409 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
410 DROP TABLE t1;
411 SHOW TABLES;
412 Tables_in_test
413 CREATE TABLE t1(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
414 SHOW TABLES;
415 Tables_in_test
416 t1
417 SHOW CREATE TABLE t1;
418 Table Create Table
419 t1 CREATE TABLE `t1` (
420 `c1` decimal(10,0) NOT NULL,
421 `c2` decimal(10,0) DEFAULT NULL,
422 PRIMARY KEY (`c1`)
423 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
424 ALTER TABLE t1 ALTER c2 SET DEFAULT 10;
425 INSERT INTO t1(c1) VALUES(1);
426 SELECT * FROM t1 ORDER BY c1;
427 c1 c2
428 1 10
429 SHOW CREATE TABLE t1;
430 Table Create Table
431 t1 CREATE TABLE `t1` (
432 `c1` decimal(10,0) NOT NULL,
433 `c2` decimal(10,0) DEFAULT '10',
434 PRIMARY KEY (`c1`)
435 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
436 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
437 INSERT INTO t1(c1) VALUES(2);
438 Warnings:
439 Warning 1364 Field 'c2' doesn't have a default value
440 SELECT * FROM t1 ORDER BY c1;
441 c1 c2
442 1 10
443 2 NULL
444 SHOW CREATE TABLE t1;
445 Table Create Table
446 t1 CREATE TABLE `t1` (
447 `c1` decimal(10,0) NOT NULL,
448 `c2` decimal(10,0),
449 PRIMARY KEY (`c1`)
450 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
451 DROP TABLE t1;
452 SHOW TABLES;
453 Tables_in_test
454 CREATE TABLE t1(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
455 SHOW TABLES;
456 Tables_in_test
457 t1
458 SHOW CREATE TABLE t1;
459 Table Create Table
460 t1 CREATE TABLE `t1` (
461 `c1` tinyint NOT NULL,
462 `c2` tinyint DEFAULT NULL,
463 PRIMARY KEY (`c1`)
464 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
465 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
466 INSERT INTO t1(c1) VALUES(1);
467 SELECT * FROM t1 ORDER BY c1;
468 c1 c2
469 1 10
470 SHOW CREATE TABLE t1;
471 Table Create Table
472 t1 CREATE TABLE `t1` (
473 `c1` tinyint NOT NULL,
474 `c2` tinyint DEFAULT '10',
475 PRIMARY KEY (`c1`)
476 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
477 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
478 INSERT INTO t1(c1) VALUES(2);
479 Warnings:
480 Warning 1364 Field 'c2' doesn't have a default value
481 SELECT * FROM t1 ORDER BY c1;
482 c1 c2
483 1 10
484 2 NULL
485 SHOW CREATE TABLE t1;
486 Table Create Table
487 t1 CREATE TABLE `t1` (
488 `c1` tinyint NOT NULL,
489 `c2` tinyint,
490 PRIMARY KEY (`c1`)
491 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
492 DROP TABLE t1;
493 SHOW TABLES;
494 Tables_in_test
495 CREATE TABLE t1(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
496 SHOW TABLES;
497 Tables_in_test
498 t1
499 SHOW CREATE TABLE t1;
500 Table Create Table
501 t1 CREATE TABLE `t1` (
502 `c1` smallint NOT NULL,
503 `c2` smallint DEFAULT NULL,
504 PRIMARY KEY (`c1`)
505 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
506 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
507 INSERT INTO t1(c1) VALUES(1);
508 SELECT * FROM t1 ORDER BY c1;
509 c1 c2
510 1 10
511 SHOW CREATE TABLE t1;
512 Table Create Table
513 t1 CREATE TABLE `t1` (
514 `c1` smallint NOT NULL,
515 `c2` smallint DEFAULT '10',
516 PRIMARY KEY (`c1`)
517 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
518 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
519 INSERT INTO t1(c1) VALUES(2);
520 Warnings:
521 Warning 1364 Field 'c2' doesn't have a default value
522 SELECT * FROM t1 ORDER BY c1;
523 c1 c2
524 1 10
525 2 NULL
526 SHOW CREATE TABLE t1;
527 Table Create Table
528 t1 CREATE TABLE `t1` (
529 `c1` smallint NOT NULL,
530 `c2` smallint,
531 PRIMARY KEY (`c1`)
532 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
533 DROP TABLE t1;
534 SHOW TABLES;
535 Tables_in_test
536 CREATE TABLE t1(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
537 SHOW TABLES;
538 Tables_in_test
539 t1
540 SHOW CREATE TABLE t1;
541 Table Create Table
542 t1 CREATE TABLE `t1` (
543 `c1` mediumint NOT NULL,
544 `c2` mediumint DEFAULT NULL,
545 PRIMARY KEY (`c1`)
546 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
547 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
548 INSERT INTO t1(c1) VALUES(1);
549 SELECT * FROM t1 ORDER BY c1;
550 c1 c2
551 1 10
552 SHOW CREATE TABLE t1;
553 Table Create Table
554 t1 CREATE TABLE `t1` (
555 `c1` mediumint NOT NULL,
556 `c2` mediumint DEFAULT '10',
557 PRIMARY KEY (`c1`)
558 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
559 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
560 INSERT INTO t1(c1) VALUES(2);
561 Warnings:
562 Warning 1364 Field 'c2' doesn't have a default value
563 SELECT * FROM t1 ORDER BY c1;
564 c1 c2
565 1 10
566 2 NULL
567 SHOW CREATE TABLE t1;
568 Table Create Table
569 t1 CREATE TABLE `t1` (
570 `c1` mediumint NOT NULL,
571 `c2` mediumint,
572 PRIMARY KEY (`c1`)
573 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
574 DROP TABLE t1;
575 SHOW TABLES;
576 Tables_in_test
577 CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY, c2 INT);
578 SHOW TABLES;
579 Tables_in_test
580 t1
581 SHOW CREATE TABLE t1;
582 Table Create Table
583 t1 CREATE TABLE `t1` (
584 `c1` int NOT NULL,
585 `c2` int DEFAULT NULL,
586 PRIMARY KEY (`c1`)
587 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
588 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
589 INSERT INTO t1(c1) VALUES(1);
590 SELECT * FROM t1 ORDER BY c1;
591 c1 c2
592 1 10
593 SHOW CREATE TABLE t1;
594 Table Create Table
595 t1 CREATE TABLE `t1` (
596 `c1` int NOT NULL,
597 `c2` int DEFAULT '10',
598 PRIMARY KEY (`c1`)
599 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
600 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
601 INSERT INTO t1(c1) VALUES(2);
602 Warnings:
603 Warning 1364 Field 'c2' doesn't have a default value
604 SELECT * FROM t1 ORDER BY c1;
605 c1 c2
606 1 10
607 2 NULL
608 SHOW CREATE TABLE t1;
609 Table Create Table
610 t1 CREATE TABLE `t1` (
611 `c1` int NOT NULL,
612 `c2` int,
613 PRIMARY KEY (`c1`)
614 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
615 DROP TABLE t1;
616 SHOW TABLES;
617 Tables_in_test
618 CREATE TABLE t1(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
619 SHOW TABLES;
620 Tables_in_test
621 t1
622 SHOW CREATE TABLE t1;
623 Table Create Table
624 t1 CREATE TABLE `t1` (
625 `c1` int NOT NULL,
626 `c2` int DEFAULT NULL,
627 PRIMARY KEY (`c1`)
628 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
629 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
630 INSERT INTO t1(c1) VALUES(1);
631 SELECT * FROM t1 ORDER BY c1;
632 c1 c2
633 1 10
634 SHOW CREATE TABLE t1;
635 Table Create Table
636 t1 CREATE TABLE `t1` (
637 `c1` int NOT NULL,
638 `c2` int DEFAULT '10',
639 PRIMARY KEY (`c1`)
640 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
641 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
642 INSERT INTO t1(c1) VALUES(2);
643 Warnings:
644 Warning 1364 Field 'c2' doesn't have a default value
645 SELECT * FROM t1 ORDER BY c1;
646 c1 c2
647 1 10
648 2 NULL
649 SHOW CREATE TABLE t1;
650 Table Create Table
651 t1 CREATE TABLE `t1` (
652 `c1` int NOT NULL,
653 `c2` int,
654 PRIMARY KEY (`c1`)
655 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
656 DROP TABLE t1;
657 SHOW TABLES;
658 Tables_in_test
659 CREATE TABLE t1(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
660 SHOW TABLES;
661 Tables_in_test
662 t1
663 SHOW CREATE TABLE t1;
664 Table Create Table
665 t1 CREATE TABLE `t1` (
666 `c1` bigint NOT NULL,
667 `c2` bigint DEFAULT NULL,
668 PRIMARY KEY (`c1`)
669 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
670 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
671 INSERT INTO t1(c1) VALUES(1);
672 SELECT * FROM t1 ORDER BY c1;
673 c1 c2
674 1 10
675 SHOW CREATE TABLE t1;
676 Table Create Table
677 t1 CREATE TABLE `t1` (
678 `c1` bigint NOT NULL,
679 `c2` bigint DEFAULT '10',
680 PRIMARY KEY (`c1`)
681 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
682 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
683 INSERT INTO t1(c1) VALUES(2);
684 Warnings:
685 Warning 1364 Field 'c2' doesn't have a default value
686 SELECT * FROM t1 ORDER BY c1;
687 c1 c2
688 1 10
689 2 NULL
690 SHOW CREATE TABLE t1;
691 Table Create Table
692 t1 CREATE TABLE `t1` (
693 `c1` bigint NOT NULL,
694 `c2` bigint,
695 PRIMARY KEY (`c1`)
696 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
697 DROP TABLE t1;
698 SHOW TABLES;
699 Tables_in_test
700 CREATE TABLE t1(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
701 SHOW TABLES;
702 Tables_in_test
703 t1
704 SHOW CREATE TABLE t1;
705 Table Create Table
706 t1 CREATE TABLE `t1` (
707 `c1` double NOT NULL,
708 `c2` double DEFAULT NULL,
709 PRIMARY KEY (`c1`)
710 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
711 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
712 INSERT INTO t1(c1) VALUES(1);
713 SELECT * FROM t1 ORDER BY c1;
714 c1 c2
715 1 10
716 SHOW CREATE TABLE t1;
717 Table Create Table
718 t1 CREATE TABLE `t1` (
719 `c1` double NOT NULL,
720 `c2` double DEFAULT '10',
721 PRIMARY KEY (`c1`)
722 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
723 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
724 INSERT INTO t1(c1) VALUES(2);
725 Warnings:
726 Warning 1364 Field 'c2' doesn't have a default value
727 SELECT * FROM t1 ORDER BY c1;
728 c1 c2
729 1 10
730 2 NULL
731 SHOW CREATE TABLE t1;
732 Table Create Table
733 t1 CREATE TABLE `t1` (
734 `c1` double NOT NULL,
735 `c2` double,
736 PRIMARY KEY (`c1`)
737 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
738 DROP TABLE t1;
739 SHOW TABLES;
740 Tables_in_test
741 CREATE TABLE t1(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
742 SHOW TABLES;
743 Tables_in_test
744 t1
745 SHOW CREATE TABLE t1;
746 Table Create Table
747 t1 CREATE TABLE `t1` (
748 `c1` double NOT NULL,
749 `c2` double DEFAULT NULL,
750 PRIMARY KEY (`c1`)
751 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
752 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
753 INSERT INTO t1(c1) VALUES(1);
754 SELECT * FROM t1 ORDER BY c1;
755 c1 c2
756 1 10
757 SHOW CREATE TABLE t1;
758 Table Create Table
759 t1 CREATE TABLE `t1` (
760 `c1` double NOT NULL,
761 `c2` double DEFAULT '10',
762 PRIMARY KEY (`c1`)
763 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
764 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
765 INSERT INTO t1(c1) VALUES(2);
766 Warnings:
767 Warning 1364 Field 'c2' doesn't have a default value
768 SELECT * FROM t1 ORDER BY c1;
769 c1 c2
770 1 10
771 2 NULL
772 SHOW CREATE TABLE t1;
773 Table Create Table
774 t1 CREATE TABLE `t1` (
775 `c1` double NOT NULL,
776 `c2` double,
777 PRIMARY KEY (`c1`)
778 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
779 DROP TABLE t1;
780 SHOW TABLES;
781 Tables_in_test
782 CREATE TABLE t1(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
783 SHOW TABLES;
784 Tables_in_test
785 t1
786 SHOW CREATE TABLE t1;
787 Table Create Table
788 t1 CREATE TABLE `t1` (
789 `c1` float NOT NULL,
790 `c2` float DEFAULT NULL,
791 PRIMARY KEY (`c1`)
792 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
793 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
794 INSERT INTO t1(c1) VALUES(1);
795 SELECT * FROM t1 ORDER BY c1;
796 c1 c2
797 1 10
798 SHOW CREATE TABLE t1;
799 Table Create Table
800 t1 CREATE TABLE `t1` (
801 `c1` float NOT NULL,
802 `c2` float DEFAULT '10',
803 PRIMARY KEY (`c1`)
804 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
805 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
806 INSERT INTO t1(c1) VALUES(2);
807 Warnings:
808 Warning 1364 Field 'c2' doesn't have a default value
809 SELECT * FROM t1 ORDER BY c1;
810 c1 c2
811 1 10
812 2 NULL
813 SHOW CREATE TABLE t1;
814 Table Create Table
815 t1 CREATE TABLE `t1` (
816 `c1` float NOT NULL,
817 `c2` float,
818 PRIMARY KEY (`c1`)
819 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
820 DROP TABLE t1;
821 SHOW TABLES;
822 Tables_in_test
823 CREATE TABLE t1(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
824 SHOW TABLES;
825 Tables_in_test
826 t1
827 SHOW CREATE TABLE t1;
828 Table Create Table
829 t1 CREATE TABLE `t1` (
830 `c1` decimal(10,0) NOT NULL,
831 `c2` decimal(10,0) DEFAULT NULL,
832 PRIMARY KEY (`c1`)
833 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
834 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
835 INSERT INTO t1(c1) VALUES(1);
836 SELECT * FROM t1 ORDER BY c1;
837 c1 c2
838 1 10
839 SHOW CREATE TABLE t1;
840 Table Create Table
841 t1 CREATE TABLE `t1` (
842 `c1` decimal(10,0) NOT NULL,
843 `c2` decimal(10,0) DEFAULT '10',
844 PRIMARY KEY (`c1`)
845 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
846 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
847 INSERT INTO t1(c1) VALUES(2);
848 Warnings:
849 Warning 1364 Field 'c2' doesn't have a default value
850 SELECT * FROM t1 ORDER BY c1;
851 c1 c2
852 1 10
853 2 NULL
854 SHOW CREATE TABLE t1;
855 Table Create Table
856 t1 CREATE TABLE `t1` (
857 `c1` decimal(10,0) NOT NULL,
858 `c2` decimal(10,0),
859 PRIMARY KEY (`c1`)
860 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
861 DROP TABLE t1;
862 SHOW TABLES;
863 Tables_in_test
864 CREATE TABLE t1(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
865 SHOW TABLES;
866 Tables_in_test
867 t1
868 SHOW CREATE TABLE t1;
869 Table Create Table
870 t1 CREATE TABLE `t1` (
871 `c1` decimal(10,0) NOT NULL,
872 `c2` decimal(10,0) DEFAULT NULL,
873 PRIMARY KEY (`c1`)
874 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
875 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT 10;
876 INSERT INTO t1(c1) VALUES(1);
877 SELECT * FROM t1 ORDER BY c1;
878 c1 c2
879 1 10
880 SHOW CREATE TABLE t1;
881 Table Create Table
882 t1 CREATE TABLE `t1` (
883 `c1` decimal(10,0) NOT NULL,
884 `c2` decimal(10,0) DEFAULT '10',
885 PRIMARY KEY (`c1`)
886 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
887 ALTER TABLE t1 ALTER COLUMN c2 DROP DEFAULT;
888 INSERT INTO t1(c1) VALUES(2);
889 Warnings:
890 Warning 1364 Field 'c2' doesn't have a default value
891 SELECT * FROM t1 ORDER BY c1;
892 c1 c2
893 1 10
894 2 NULL
895 SHOW CREATE TABLE t1;
896 Table Create Table
897 t1 CREATE TABLE `t1` (
898 `c1` decimal(10,0) NOT NULL,
899 `c2` decimal(10,0),
900 PRIMARY KEY (`c1`)
901 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
902 DROP TABLE t1;
903 SHOW TABLES;
904 Tables_in_test
905 SET sql_mode=default;