1set default_storage_engine='tokudb'; 2drop table if exists t1; 3create table t1 (y year,y2 year(2)); 4Warnings: 5Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 6insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69); 7select * from t1; 8y y2 90000 00 101999 99 112000 00 122001 01 131970 70 142069 69 15select * from t1 order by y; 16y y2 170000 00 181970 70 191999 99 202000 00 212001 01 222069 69 23select * from t1 order by y2; 24y y2 251970 70 261999 99 270000 00 282000 00 292001 01 302069 69 31drop table t1; 32create table t1 (y year); 33insert into t1 values (now()); 34Warnings: 35Warning 1265 Data truncated for column 'y' at row 1 36select if(y = now(), 1, 0) from t1; 37if(y = now(), 1, 0) 381 39drop table t1; 40create table t1(a year); 41insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3'); 42Warnings: 43Warning 1265 Data truncated for column 'a' at row 3 44select * from t1; 45a 462001 472001 482001 492001 50drop table t1; 51End of 5.0 tests 52# 53# Bug #49480: WHERE using YEAR columns returns unexpected results 54# 55CREATE TABLE t2(yy YEAR(2), c2 CHAR(4)); 56Warnings: 57Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 58CREATE TABLE t4(yyyy YEAR(4), c4 CHAR(4)); 59INSERT INTO t2 (c2) VALUES (NULL),(1970),(1999),(2000),(2001),(2069); 60INSERT INTO t4 (c4) SELECT c2 FROM t2; 61UPDATE t2 SET yy = c2; 62UPDATE t4 SET yyyy = c4; 63SELECT * FROM t2; 64yy c2 65NULL NULL 6670 1970 6799 1999 6800 2000 6901 2001 7069 2069 71SELECT * FROM t4; 72yyyy c4 73NULL NULL 741970 1970 751999 1999 762000 2000 772001 2001 782069 2069 79# Comparison of YEAR(2) with YEAR(4) 80SELECT * FROM t2, t4 WHERE yy = yyyy; 81yy c2 yyyy c4 8270 1970 1970 1970 8399 1999 1999 1999 8400 2000 2000 2000 8501 2001 2001 2001 8669 2069 2069 2069 87SELECT * FROM t2, t4 WHERE yy <=> yyyy; 88yy c2 yyyy c4 89NULL NULL NULL NULL 9070 1970 1970 1970 9199 1999 1999 1999 9200 2000 2000 2000 9301 2001 2001 2001 9469 2069 2069 2069 95SELECT * FROM t2, t4 WHERE yy < yyyy; 96yy c2 yyyy c4 9770 1970 1999 1999 9870 1970 2000 2000 9999 1999 2000 2000 10070 1970 2001 2001 10199 1999 2001 2001 10200 2000 2001 2001 10370 1970 2069 2069 10499 1999 2069 2069 10500 2000 2069 2069 10601 2001 2069 2069 107SELECT * FROM t2, t4 WHERE yy > yyyy; 108yy c2 yyyy c4 10999 1999 1970 1970 11000 2000 1970 1970 11101 2001 1970 1970 11269 2069 1970 1970 11300 2000 1999 1999 11401 2001 1999 1999 11569 2069 1999 1999 11601 2001 2000 2000 11769 2069 2000 2000 11869 2069 2001 2001 119# Comparison of YEAR(2) with YEAR(2) 120SELECT * FROM t2 a, t2 b WHERE a.yy = b.yy; 121yy c2 yy c2 12270 1970 70 1970 12399 1999 99 1999 12400 2000 00 2000 12501 2001 01 2001 12669 2069 69 2069 127SELECT * FROM t2 a, t2 b WHERE a.yy <=> b.yy; 128yy c2 yy c2 129NULL NULL NULL NULL 13070 1970 70 1970 13199 1999 99 1999 13200 2000 00 2000 13301 2001 01 2001 13469 2069 69 2069 135SELECT * FROM t2 a, t2 b WHERE a.yy < b.yy; 136yy c2 yy c2 13770 1970 99 1999 13870 1970 00 2000 13999 1999 00 2000 14070 1970 01 2001 14199 1999 01 2001 14200 2000 01 2001 14370 1970 69 2069 14499 1999 69 2069 14500 2000 69 2069 14601 2001 69 2069 147# Comparison of YEAR(4) with YEAR(4) 148SELECT * FROM t4 a, t4 b WHERE a.yyyy = b.yyyy; 149yyyy c4 yyyy c4 1501970 1970 1970 1970 1511999 1999 1999 1999 1522000 2000 2000 2000 1532001 2001 2001 2001 1542069 2069 2069 2069 155SELECT * FROM t4 a, t4 b WHERE a.yyyy <=> b.yyyy; 156yyyy c4 yyyy c4 157NULL NULL NULL NULL 1581970 1970 1970 1970 1591999 1999 1999 1999 1602000 2000 2000 2000 1612001 2001 2001 2001 1622069 2069 2069 2069 163SELECT * FROM t4 a, t4 b WHERE a.yyyy < b.yyyy; 164yyyy c4 yyyy c4 1651970 1970 1999 1999 1661970 1970 2000 2000 1671999 1999 2000 2000 1681970 1970 2001 2001 1691999 1999 2001 2001 1702000 2000 2001 2001 1711970 1970 2069 2069 1721999 1999 2069 2069 1732000 2000 2069 2069 1742001 2001 2069 2069 175# Comparison with constants: 176SELECT * FROM t2 WHERE yy = NULL; 177yy c2 178SELECT * FROM t4 WHERE yyyy = NULL; 179yyyy c4 180SELECT * FROM t2 WHERE yy <=> NULL; 181yy c2 182NULL NULL 183SELECT * FROM t4 WHERE yyyy <=> NULL; 184yyyy c4 185NULL NULL 186SELECT * FROM t2 WHERE yy < NULL; 187yy c2 188SELECT * FROM t2 WHERE yy > NULL; 189yy c2 190SELECT * FROM t2 WHERE yy = NOW(); 191yy c2 192SELECT * FROM t4 WHERE yyyy = NOW(); 193yyyy c4 194SELECT * FROM t2 WHERE yy = 99; 195yy c2 19699 1999 197SELECT * FROM t2 WHERE 99 = yy; 198yy c2 19999 1999 200SELECT * FROM t4 WHERE yyyy = 99; 201yyyy c4 2021999 1999 203SELECT * FROM t2 WHERE yy = 'test'; 204yy c2 20500 2000 206Warnings: 207Warning 1292 Truncated incorrect DOUBLE value: 'test' 208SELECT * FROM t4 WHERE yyyy = 'test'; 209yyyy c4 210Warnings: 211Warning 1292 Truncated incorrect DOUBLE value: 'test' 212SELECT * FROM t2 WHERE yy = '1999'; 213yy c2 21499 1999 215SELECT * FROM t4 WHERE yyyy = '1999'; 216yyyy c4 2171999 1999 218SELECT * FROM t2 WHERE yy = 1999; 219yy c2 22099 1999 221SELECT * FROM t4 WHERE yyyy = 1999; 222yyyy c4 2231999 1999 224SELECT * FROM t2 WHERE yy = 1999.1; 225yy c2 22699 1999 227SELECT * FROM t4 WHERE yyyy = 1999.1; 228yyyy c4 2291999 1999 230SELECT * FROM t2 WHERE yy = 1998.9; 231yy c2 23299 1999 233SELECT * FROM t4 WHERE yyyy = 1998.9; 234yyyy c4 2351999 1999 236# Coverage tests for YEAR with zero/2000 constants: 237SELECT * FROM t2 WHERE yy = 0; 238yy c2 23900 2000 240SELECT * FROM t2 WHERE yy = '0'; 241yy c2 24200 2000 243SELECT * FROM t2 WHERE yy = '0000'; 244yy c2 24500 2000 246SELECT * FROM t2 WHERE yy = '2000'; 247yy c2 24800 2000 249SELECT * FROM t2 WHERE yy = 2000; 250yy c2 25100 2000 252SELECT * FROM t4 WHERE yyyy = 0; 253yyyy c4 254SELECT * FROM t4 WHERE yyyy = '0'; 255yyyy c4 2562000 2000 257SELECT * FROM t4 WHERE yyyy = '0000'; 258yyyy c4 259SELECT * FROM t4 WHERE yyyy = '2000'; 260yyyy c4 2612000 2000 262SELECT * FROM t4 WHERE yyyy = 2000; 263yyyy c4 2642000 2000 265# Comparison with constants those are out of YEAR range 266# (coverage test for backward compatibility) 267SELECT COUNT(yy) FROM t2; 268COUNT(yy) 2695 270SELECT COUNT(yyyy) FROM t4; 271COUNT(yyyy) 2725 273SELECT COUNT(*) FROM t2 WHERE yy = -1; 274COUNT(*) 2750 276SELECT COUNT(*) FROM t4 WHERE yyyy > -1; 277COUNT(*) 2785 279SELECT COUNT(*) FROM t2 WHERE yy > -1000000000000000000; 280COUNT(*) 2815 282SELECT COUNT(*) FROM t4 WHERE yyyy > -1000000000000000000; 283COUNT(*) 2845 285SELECT COUNT(*) FROM t2 WHERE yy < 2156; 286COUNT(*) 2875 288SELECT COUNT(*) FROM t4 WHERE yyyy < 2156; 289COUNT(*) 2905 291SELECT COUNT(*) FROM t2 WHERE yy < 1000000000000000000; 292COUNT(*) 2935 294SELECT COUNT(*) FROM t4 WHERE yyyy < 1000000000000000000; 295COUNT(*) 2965 297SELECT * FROM t2 WHERE yy < 123; 298yy c2 29970 1970 30099 1999 30100 2000 30201 2001 30369 2069 304SELECT * FROM t2 WHERE yy > 123; 305yy c2 306SELECT * FROM t4 WHERE yyyy < 123; 307yyyy c4 308SELECT * FROM t4 WHERE yyyy > 123; 309yyyy c4 3101970 1970 3111999 1999 3122000 2000 3132001 2001 3142069 2069 315DROP TABLE t2, t4; 316# 317# Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type 318# 319CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4)); 320INSERT INTO t1 (s) VALUES ('bad'); 321Warnings: 322Warning 1364 Field 'y' doesn't have a default value 323INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001); 324SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y; 325y s y s 3260000 bad 0000 bad 3270000 0 0000 bad 3280000 bad 0000 0 3290000 0 0000 0 3302000 2000 2000 2000 3312001 2001 2001 2001 332SELECT * FROM t1 WHERE t1.y = 0; 333y s 3340000 bad 3350000 0 336SELECT * FROM t1 WHERE t1.y = 2000; 337y s 3382000 2000 339SELECT ta.y AS ta_y, ta.s, tb.y AS tb_y, tb.s FROM t1 ta, t1 tb HAVING ta_y = tb_y; 340ta_y s tb_y s 3410000 bad 0000 bad 3420000 0 0000 bad 3430000 bad 0000 0 3440000 0 0000 0 3452000 2000 2000 2000 3462001 2001 2001 2001 347DROP TABLE t1; 348# 349# Bug #59211: Select Returns Different Value for min(year) Function 350# 351CREATE TABLE t1(c1 YEAR(4)); 352INSERT INTO t1 VALUES (1901),(2155),(0000); 353SELECT * FROM t1; 354c1 3551901 3562155 3570000 358SELECT COUNT(*) AS total_rows, MIN(c1) AS min_value, MAX(c1) FROM t1; 359total_rows min_value MAX(c1) 3603 0 2155 361DROP TABLE t1; 362# 363# WL#6219: Deprecate and remove YEAR(2) type 364# 365CREATE TABLE t1 (c1 YEAR(2), c2 YEAR(4)); 366Warnings: 367Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 368ALTER TABLE t1 MODIFY COLUMN c2 YEAR(2); 369Warnings: 370Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 371Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 372DROP TABLE t1; 373# 374End of 5.1 tests 375# 376# Start of 5.6 tests 377# 378SET timestamp=UNIX_TIMESTAMP('2011-12-31 15:44:00'); 379CREATE TABLE t1 (a YEAR); 380INSERT INTO t1 VALUES (CURRENT_TIME); 381Warnings: 382Warning 1265 Data truncated for column 'a' at row 1 383INSERT INTO t1 VALUES (TIME'15:44:00'); 384Warnings: 385Warning 1265 Data truncated for column 'a' at row 1 386INSERT INTO t1 VALUES (TIME'25:00:00'); 387Warnings: 388Warning 1265 Data truncated for column 'a' at row 1 389SELECT * FROM t1; 390a 3910000 3920000 3930000 394DROP TABLE t1; 395SET timestamp=DEFAULT; 396