1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
#
# WL#6219: Deprecate and remove YEAR(2) type
# Storage engine-specific test
#
#
# Variables which have to be set before calling this script:
#
# $engine_type -- Storage engine to be tested
#
let $year_default_storage_engine=`select @@global.default_storage_engine`;
eval SET global default_storage_engine=$engine_type;
eval SET session default_storage_engine=$engine_type;
--echo # Test for warnings on column creation
CREATE TABLE t1 (y YEAR NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
--error ER_PARSE_ERROR
CREATE TABLE t1 (y YEAR(-4) NOT NULL);
--error ER_INVALID_YEAR_COLUMN_LENGTH
CREATE TABLE t1 (y YEAR(0) NOT NULL);
--error ER_INVALID_YEAR_COLUMN_LENGTH
CREATE TABLE t1 (y YEAR(1) NOT NULL);
--error ER_INVALID_YEAR_COLUMN_LENGTH
CREATE TABLE t1 (y YEAR(2) NOT NULL);
--error ER_INVALID_YEAR_COLUMN_LENGTH
CREATE TABLE t1 (y YEAR(5) NOT NULL);
--error ER_INVALID_YEAR_COLUMN_LENGTH
CREATE TABLE t1 (y YEAR(10240) NOT NULL);
--error ER_PARSE_ERROR
CREATE TABLE t1 (y YEAR(-1) NOT NULL);
--error ER_PARSE_ERROR
CREATE TABLE t1 (y YEAR(-4294967296) NOT NULL);
--error ER_PARSE_ERROR
CREATE TABLE t1 (y YEAR(-4294967295) NOT NULL);
--error ER_PARSE_ERROR
CREATE TABLE t1 (y YEAR(NULL) NOT NULL);
--error ER_PARSE_ERROR
CREATE TABLE t1 (y YEAR('') NOT NULL);
--error ER_PARSE_ERROR
CREATE TABLE t1 (y YEAR('a') NOT NULL);
--error ER_PARSE_ERROR
CREATE TABLE t1 (y YEAR('-a') NOT NULL);
--error ER_INVALID_YEAR_COLUMN_LENGTH
CREATE TABLE t1 (y YEAR(4294967296) NOT NULL);
--error ER_INVALID_YEAR_COLUMN_LENGTH
CREATE TABLE t1 (y YEAR(4294967295) NOT NULL);
--echo # Test for default column width value
CREATE TABLE t1 (y YEAR NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
--echo #
--echo # Check various ALTER TABLE operations on YEAR(2) type
--echo #
CREATE TABLE t1 (i INT NOT NULL);
--echo # Try to create new YEAR(2) column with ALTER TABLE
--error ER_INVALID_YEAR_COLUMN_LENGTH
ALTER TABLE t1 ADD COLUMN y YEAR(2) NOT NULL;
SHOW CREATE TABLE t1;
--echo # Try to convert YEAR(4) column to YEAR(2) with ALTER TABLE
--error ER_INVALID_YEAR_COLUMN_LENGTH
ALTER TABLE t1 MODIFY COLUMN y YEAR(2) NOT NULL;
SHOW CREATE TABLE t1;
--error ER_INVALID_YEAR_COLUMN_LENGTH
ALTER TABLE t1 MODIFY COLUMN i YEAR(2) NOT NULL;
SHOW CREATE TABLE t1;
ALTER TABLE t1 MODIFY COLUMN i INT NOT NULL;
DROP TABLE t1;
if ($engine_type != "MERGE") {
--echo #
--echo # Regression tests
--echo #
CREATE TABLE t1 (y YEAR NOT NULL DEFAULT 0,
i INT NOT NULL DEFAULT 0,
c VARCHAR(20) NOT NULL DEFAULT '');
--disable_query_log
--echo # insert [1901..2155]
let $i = 255;
while ($i) {
let $x = 1901 + 255 - $i;
eval INSERT INTO t1 (i, c) VALUES($x, $x);
dec $i;
}
--echo # insert [0..99]
let $i = 100;
while ($i) {
let $x = 100 - $i;
eval INSERT INTO t1 (i, c) VALUES($x, $x);
dec $i;
}
--enable_query_log
UPDATE t1 SET i = c;
--echo # should return no warnings
UPDATE t1 SET y = c;
--echo # should return no warnings
UPDATE t1 SET y = i;
--echo # should return no warnings
INSERT INTO t1 (y) VALUES (0), ('00'), ('0000');
TRUNCATE t1;
--echo # test out-of-range values, should return warnings
INSERT INTO t1 (i) VALUES (-1), (1800), (2156), (2147483647);
--echo # should return 4 warnings
UPDATE IGNORE t1 SET y = i;
--sorted_result
SELECT y, i FROM t1;
TRUNCATE t1;
--echo # should return 4 warnings
INSERT INTO t1 (c) VALUES (''), (' '), ('2012qwer'), ('qwer');
UPDATE IGNORE t1 SET y = c;
--sorted_result
SELECT y, CONCAT('"', c, '"') FROM t1;
DROP TABLE t1;
} # if ($engine_type != "MERGE")
eval SET @@global.default_storage_engine = $year_default_storage_engine;
eval SET @@session.default_storage_engine = $year_default_storage_engine;
--echo #
|
