1
drop table if exists t1;
2
create table t1 (y year,y2 year(2));
3
insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69);
12
select * from t1 order by y;
20
select * from t1 order by y2;
29
create table t1 (y year);
30
insert into t1 values (now());
32
Warning 1265 Data truncated for column 'y' at row 1
33
select if(y = now(), 1, 0) from t1;
37
create table t1(a year);
38
insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3');
40
Warning 1265 Data truncated for column 'a' at row 3
50
# Bug #49480: WHERE using YEAR columns returns unexpected results
52
CREATE TABLE t2(yy YEAR(2), c2 CHAR(4));
53
CREATE TABLE t4(yyyy YEAR(4), c4 CHAR(4));
54
INSERT INTO t2 (c2) VALUES (NULL),(1970),(1999),(2000),(2001),(2069);
55
INSERT INTO t4 (c4) SELECT c2 FROM t2;
56
UPDATE t2 SET yy = c2;
57
UPDATE t4 SET yyyy = c4;
74
# Comparison of YEAR(2) with YEAR(4)
75
SELECT * FROM t2, t4 WHERE yy = yyyy;
82
SELECT * FROM t2, t4 WHERE yy <=> yyyy;
90
SELECT * FROM t2, t4 WHERE yy < yyyy;
102
SELECT * FROM t2, t4 WHERE yy > yyyy;
114
# Comparison of YEAR(2) with YEAR(2)
115
SELECT * FROM t2 a, t2 b WHERE a.yy = b.yy;
122
SELECT * FROM t2 a, t2 b WHERE a.yy <=> b.yy;
130
SELECT * FROM t2 a, t2 b WHERE a.yy < b.yy;
142
# Comparison of YEAR(4) with YEAR(4)
143
SELECT * FROM t4 a, t4 b WHERE a.yyyy = b.yyyy;
150
SELECT * FROM t4 a, t4 b WHERE a.yyyy <=> b.yyyy;
158
SELECT * FROM t4 a, t4 b WHERE a.yyyy < b.yyyy;
170
# Comparison with constants:
171
SELECT * FROM t2 WHERE yy = NULL;
173
SELECT * FROM t4 WHERE yyyy = NULL;
175
SELECT * FROM t2 WHERE yy <=> NULL;
178
SELECT * FROM t4 WHERE yyyy <=> NULL;
181
SELECT * FROM t2 WHERE yy < NULL;
183
SELECT * FROM t2 WHERE yy > NULL;
185
SELECT * FROM t2 WHERE yy = NOW();
187
SELECT * FROM t4 WHERE yyyy = NOW();
189
SELECT * FROM t2 WHERE yy = 99;
192
SELECT * FROM t2 WHERE 99 = yy;
195
SELECT * FROM t4 WHERE yyyy = 99;
198
SELECT * FROM t2 WHERE yy = 'test';
202
Warning 1292 Truncated incorrect DOUBLE value: 'test'
203
SELECT * FROM t4 WHERE yyyy = 'test';
206
Warning 1292 Truncated incorrect DOUBLE value: 'test'
207
SELECT * FROM t2 WHERE yy = '1999';
210
SELECT * FROM t4 WHERE yyyy = '1999';
213
SELECT * FROM t2 WHERE yy = 1999;
216
SELECT * FROM t4 WHERE yyyy = 1999;
219
SELECT * FROM t2 WHERE yy = 1999.1;
222
SELECT * FROM t4 WHERE yyyy = 1999.1;
225
SELECT * FROM t2 WHERE yy = 1998.9;
228
SELECT * FROM t4 WHERE yyyy = 1998.9;
231
# Coverage tests for YEAR with zero/2000 constants:
232
SELECT * FROM t2 WHERE yy = 0;
235
SELECT * FROM t2 WHERE yy = '0';
238
SELECT * FROM t2 WHERE yy = '0000';
241
SELECT * FROM t2 WHERE yy = '2000';
244
SELECT * FROM t2 WHERE yy = 2000;
247
SELECT * FROM t4 WHERE yyyy = 0;
249
SELECT * FROM t4 WHERE yyyy = '0';
252
SELECT * FROM t4 WHERE yyyy = '0000';
254
SELECT * FROM t4 WHERE yyyy = '2000';
257
SELECT * FROM t4 WHERE yyyy = 2000;
260
# Comparison with constants those are out of YEAR range
261
# (coverage test for backward compatibility)
262
SELECT COUNT(yy) FROM t2;
265
SELECT COUNT(yyyy) FROM t4;
268
SELECT COUNT(*) FROM t2 WHERE yy = -1;
271
SELECT COUNT(*) FROM t4 WHERE yyyy > -1;
274
SELECT COUNT(*) FROM t2 WHERE yy > -1000000000000000000;
277
SELECT COUNT(*) FROM t4 WHERE yyyy > -1000000000000000000;
280
SELECT COUNT(*) FROM t2 WHERE yy < 2156;
283
SELECT COUNT(*) FROM t4 WHERE yyyy < 2156;
286
SELECT COUNT(*) FROM t2 WHERE yy < 1000000000000000000;
289
SELECT COUNT(*) FROM t4 WHERE yyyy < 1000000000000000000;
292
SELECT * FROM t2 WHERE yy < 123;
299
SELECT * FROM t2 WHERE yy > 123;
301
SELECT * FROM t4 WHERE yyyy < 123;
303
SELECT * FROM t4 WHERE yyyy > 123;