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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
|
#
# Test year
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (y year,y2 year(2));
insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69);
select * from t1;
select * from t1 order by y;
select * from t1 order by y2;
drop table t1;
#
# Bug 2335
#
create table t1 (y year);
insert into t1 values (now());
select if(y = now(), 1, 0) from t1;
drop table t1;
#
# Bug #27176: Assigning a string to an year column has unexpected results
#
create table t1(a year);
insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3');
select * from t1;
drop table t1;
--echo End of 5.0 tests
--echo #
--echo # Bug #49480: WHERE using YEAR columns returns unexpected results
--echo #
CREATE TABLE t2(yy YEAR(2), c2 CHAR(4));
CREATE TABLE t4(yyyy YEAR(4), c4 CHAR(4));
INSERT INTO t2 (c2) VALUES (NULL),(1970),(1999),(2000),(2001),(2069);
INSERT INTO t4 (c4) SELECT c2 FROM t2;
UPDATE t2 SET yy = c2;
UPDATE t4 SET yyyy = c4;
SELECT * FROM t2;
SELECT * FROM t4;
--echo # Comparison of YEAR(2) with YEAR(4)
SELECT * FROM t2, t4 WHERE yy = yyyy;
SELECT * FROM t2, t4 WHERE yy <=> yyyy;
SELECT * FROM t2, t4 WHERE yy < yyyy;
SELECT * FROM t2, t4 WHERE yy > yyyy;
--echo # Comparison of YEAR(2) with YEAR(2)
SELECT * FROM t2 a, t2 b WHERE a.yy = b.yy;
SELECT * FROM t2 a, t2 b WHERE a.yy <=> b.yy;
SELECT * FROM t2 a, t2 b WHERE a.yy < b.yy;
--echo # Comparison of YEAR(4) with YEAR(4)
SELECT * FROM t4 a, t4 b WHERE a.yyyy = b.yyyy;
SELECT * FROM t4 a, t4 b WHERE a.yyyy <=> b.yyyy;
SELECT * FROM t4 a, t4 b WHERE a.yyyy < b.yyyy;
--echo # Comparison with constants:
SELECT * FROM t2 WHERE yy = NULL;
SELECT * FROM t4 WHERE yyyy = NULL;
SELECT * FROM t2 WHERE yy <=> NULL;
SELECT * FROM t4 WHERE yyyy <=> NULL;
SELECT * FROM t2 WHERE yy < NULL;
SELECT * FROM t2 WHERE yy > NULL;
SELECT * FROM t2 WHERE yy = NOW();
SELECT * FROM t4 WHERE yyyy = NOW();
SELECT * FROM t2 WHERE yy = 99;
SELECT * FROM t2 WHERE 99 = yy;
SELECT * FROM t4 WHERE yyyy = 99;
SELECT * FROM t2 WHERE yy = 'test';
SELECT * FROM t4 WHERE yyyy = 'test';
SELECT * FROM t2 WHERE yy = '1999';
SELECT * FROM t4 WHERE yyyy = '1999';
SELECT * FROM t2 WHERE yy = 1999;
SELECT * FROM t4 WHERE yyyy = 1999;
SELECT * FROM t2 WHERE yy = 1999.1;
SELECT * FROM t4 WHERE yyyy = 1999.1;
SELECT * FROM t2 WHERE yy = 1998.9;
SELECT * FROM t4 WHERE yyyy = 1998.9;
--echo # Coverage tests for YEAR with zero/2000 constants:
SELECT * FROM t2 WHERE yy = 0;
SELECT * FROM t2 WHERE yy = '0';
SELECT * FROM t2 WHERE yy = '0000';
SELECT * FROM t2 WHERE yy = '2000';
SELECT * FROM t2 WHERE yy = 2000;
SELECT * FROM t4 WHERE yyyy = 0;
SELECT * FROM t4 WHERE yyyy = '0';
SELECT * FROM t4 WHERE yyyy = '0000';
SELECT * FROM t4 WHERE yyyy = '2000';
SELECT * FROM t4 WHERE yyyy = 2000;
--echo # Comparison with constants those are out of YEAR range
--echo # (coverage test for backward compatibility)
SELECT COUNT(yy) FROM t2;
SELECT COUNT(yyyy) FROM t4;
SELECT COUNT(*) FROM t2 WHERE yy = -1;
SELECT COUNT(*) FROM t4 WHERE yyyy > -1;
SELECT COUNT(*) FROM t2 WHERE yy > -1000000000000000000;
SELECT COUNT(*) FROM t4 WHERE yyyy > -1000000000000000000;
SELECT COUNT(*) FROM t2 WHERE yy < 2156;
SELECT COUNT(*) FROM t4 WHERE yyyy < 2156;
SELECT COUNT(*) FROM t2 WHERE yy < 1000000000000000000;
SELECT COUNT(*) FROM t4 WHERE yyyy < 1000000000000000000;
SELECT * FROM t2 WHERE yy < 123;
SELECT * FROM t2 WHERE yy > 123;
SELECT * FROM t4 WHERE yyyy < 123;
SELECT * FROM t4 WHERE yyyy > 123;
DROP TABLE t2, t4;
--echo #
--echo # Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type
--echo #
CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4));
INSERT INTO t1 (s) VALUES ('bad');
INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001);
SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y;
SELECT * FROM t1 WHERE t1.y = 0;
SELECT * FROM t1 WHERE t1.y = 2000;
SELECT ta.y AS ta_y, ta.s, tb.y AS tb_y, tb.s FROM t1 ta, t1 tb HAVING ta_y = tb_y;
DROP TABLE t1;
--echo #
--echo # Bug #59211: Select Returns Different Value for min(year) Function
--echo #
CREATE TABLE t1(c1 YEAR(4));
INSERT INTO t1 VALUES (1901),(2155),(0000);
SELECT * FROM t1;
SELECT COUNT(*) AS total_rows, MIN(c1) AS min_value, MAX(c1) FROM t1;
SELECT COUNT(*) AS total_rows, MIN(c1+0) AS min_value, MAX(c1+0) FROM t1;
DROP TABLE t1;
--echo #
--echo # WL#6219: Deprecate and remove YEAR(2) type
--echo #
CREATE TABLE t1 (c1 YEAR(2), c2 YEAR(4));
ALTER TABLE t1 MODIFY COLUMN c2 YEAR(2);
DROP TABLE t1;
--echo #
--echo End of 5.1 tests
#
# fun with convert_const_to_int
# in some cases 00 is equal to 2000, in others it is not.
#
create function y2k() returns int deterministic return 2000;
create table t1 (a year(2), b int);
insert t1 values (0,2000);
select a from t1 where a=2000; # constant.
select a from t1 where a=1000+1000; # still a constant.
# select a from t1 where a=(select 2000); # even this is a constant
select a from t1 where a=(select 2000 from dual where 1); # constant, but "expensive"
select a from t1 where a=y2k(); # constant, but "expensive"
select a from t1 where a=b; # not a constant
drop table t1;
drop function y2k;
|
