1# 2# Test year 3# 4 5create table t1 (y year,y2 year(2)); 6insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69); 7select * from t1; 8select * from t1 order by y; 9select * from t1 order by y2; 10drop table t1; 11 12--echo # 13--echo # Bug 2335 14--echo # 15 16create table t1 (y year); 17insert ignore into t1 values (now()); 18select if(y = now(), 1, 0) from t1; 19drop table t1; 20 21--echo # 22--echo # Bug #27176: Assigning a string to an year column has unexpected results 23--echo # 24 25create table t1(a year); 26insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3'); 27select * from t1; 28drop table t1; 29 30--echo # 31--echo # End of 5.0 tests 32--echo # 33 34--echo # 35--echo # Bug #49480: WHERE using YEAR columns returns unexpected results 36--echo # 37 38CREATE TABLE t2(yy YEAR(2), c2 CHAR(4)); 39CREATE TABLE t4(yyyy YEAR(4), c4 CHAR(4)); 40 41INSERT INTO t2 (c2) VALUES (NULL),(1970),(1999),(2000),(2001),(2069); 42INSERT INTO t4 (c4) SELECT c2 FROM t2; 43UPDATE t2 SET yy = c2; 44UPDATE t4 SET yyyy = c4; 45 46SELECT * FROM t2; 47SELECT * FROM t4; 48 49--echo # Comparison of YEAR(2) with YEAR(4) 50 51SELECT * FROM t2, t4 WHERE yy = yyyy; 52SELECT * FROM t2, t4 WHERE yy <=> yyyy; 53SELECT * FROM t2, t4 WHERE yy < yyyy; 54SELECT * FROM t2, t4 WHERE yy > yyyy; 55 56--echo # Comparison of YEAR(2) with YEAR(2) 57 58SELECT * FROM t2 a, t2 b WHERE a.yy = b.yy; 59SELECT * FROM t2 a, t2 b WHERE a.yy <=> b.yy; 60SELECT * FROM t2 a, t2 b WHERE a.yy < b.yy; 61 62--echo # Comparison of YEAR(4) with YEAR(4) 63 64SELECT * FROM t4 a, t4 b WHERE a.yyyy = b.yyyy; 65SELECT * FROM t4 a, t4 b WHERE a.yyyy <=> b.yyyy; 66SELECT * FROM t4 a, t4 b WHERE a.yyyy < b.yyyy; 67 68--echo # Comparison with constants: 69 70SELECT * FROM t2 WHERE yy = NULL; 71SELECT * FROM t4 WHERE yyyy = NULL; 72SELECT * FROM t2 WHERE yy <=> NULL; 73SELECT * FROM t4 WHERE yyyy <=> NULL; 74SELECT * FROM t2 WHERE yy < NULL; 75SELECT * FROM t2 WHERE yy > NULL; 76 77SELECT * FROM t2 WHERE yy = NOW(); 78SELECT * FROM t4 WHERE yyyy = NOW(); 79 80SELECT * FROM t2 WHERE yy = 99; 81SELECT * FROM t2 WHERE 99 = yy; 82SELECT * FROM t4 WHERE yyyy = 99; 83 84SELECT * FROM t2 WHERE yy = 'test'; 85SELECT * FROM t4 WHERE yyyy = 'test'; 86 87SELECT * FROM t2 WHERE yy = '1999'; 88SELECT * FROM t4 WHERE yyyy = '1999'; 89 90SELECT * FROM t2 WHERE yy = 1999; 91SELECT * FROM t4 WHERE yyyy = 1999; 92 93SELECT * FROM t2 WHERE yy = 1999.1; 94SELECT * FROM t4 WHERE yyyy = 1999.1; 95 96SELECT * FROM t2 WHERE yy = 1998.9; 97SELECT * FROM t4 WHERE yyyy = 1998.9; 98 99--echo # Coverage tests for YEAR with zero/2000 constants: 100 101SELECT * FROM t2 WHERE yy = 0; 102SELECT * FROM t2 WHERE yy = '0'; 103SELECT * FROM t2 WHERE yy = '0000'; 104SELECT * FROM t2 WHERE yy = '2000'; 105SELECT * FROM t2 WHERE yy = 2000; 106 107SELECT * FROM t4 WHERE yyyy = 0; 108SELECT * FROM t4 WHERE yyyy = '0'; 109SELECT * FROM t4 WHERE yyyy = '0000'; 110SELECT * FROM t4 WHERE yyyy = '2000'; 111SELECT * FROM t4 WHERE yyyy = 2000; 112 113--echo # Comparison with constants those are out of YEAR range 114--echo # (coverage test for backward compatibility) 115 116SELECT COUNT(yy) FROM t2; 117SELECT COUNT(yyyy) FROM t4; 118 119SELECT COUNT(*) FROM t2 WHERE yy = -1; 120SELECT COUNT(*) FROM t4 WHERE yyyy > -1; 121SELECT COUNT(*) FROM t2 WHERE yy > -1000000000000000000; 122SELECT COUNT(*) FROM t4 WHERE yyyy > -1000000000000000000; 123 124SELECT COUNT(*) FROM t2 WHERE yy < 2156; 125SELECT COUNT(*) FROM t4 WHERE yyyy < 2156; 126SELECT COUNT(*) FROM t2 WHERE yy < 1000000000000000000; 127SELECT COUNT(*) FROM t4 WHERE yyyy < 1000000000000000000; 128 129SELECT * FROM t2 WHERE yy < 123; 130SELECT * FROM t2 WHERE yy > 123; 131SELECT * FROM t4 WHERE yyyy < 123; 132SELECT * FROM t4 WHERE yyyy > 123; 133 134DROP TABLE t2, t4; 135 136--echo # 137--echo # Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type 138--echo # 139 140CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4)); 141INSERT IGNORE INTO t1 (s) VALUES ('bad'); 142INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001); 143 144SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y; 145SELECT * FROM t1 WHERE t1.y = 0; 146SELECT * FROM t1 WHERE t1.y = 2000; 147 148SELECT ta.y AS ta_y, ta.s, tb.y AS tb_y, tb.s FROM t1 ta, t1 tb HAVING ta_y = tb_y; 149 150DROP TABLE t1; 151 152--echo # 153--echo # Bug #59211: Select Returns Different Value for min(year) Function 154--echo # 155 156CREATE TABLE t1(c1 YEAR(4)); 157INSERT INTO t1 VALUES (1901),(2155),(0000); 158SELECT * FROM t1; 159SELECT COUNT(*) AS total_rows, MIN(c1) AS min_value, MAX(c1) FROM t1; 160SELECT COUNT(*) AS total_rows, MIN(c1+0) AS min_value, MAX(c1+0) FROM t1; 161DROP TABLE t1; 162 163--echo # 164--echo # WL#6219: Deprecate and remove YEAR(2) type 165--echo # 166 167CREATE TABLE t1 (c1 YEAR(2), c2 YEAR(4)); 168ALTER TABLE t1 MODIFY COLUMN c2 YEAR(2); 169DROP TABLE t1; 170 171--echo # 172--echo # End of 5.1 tests 173--echo # 174 175# 176# fun with convert_const_to_int 177# in some cases 00 is equal to 2000, in others it is not. 178# 179create function y2k() returns int deterministic return 2000; 180create table t1 (a year(2), b int); 181insert t1 values (0,2000); 182select a from t1 where a=2000; # constant. 183select a from t1 where a=1000+1000; # still a constant. 184# select a from t1 where a=(select 2000); # even this is a constant 185select a from t1 where a=(select 2000 from dual where 1); # constant, but "expensive" 186select a from t1 where a=y2k(); # constant, but "expensive" 187select a from t1 where a=b; # not a constant 188drop table t1; 189drop function y2k; 190 191--echo # 192--echo # MDEV-17257 Server crashes in Item::field_type_for_temporal_comparison or in get_datetime_value on SELECT with YEAR field and IN 193--echo # 194 195CREATE TABLE t1 (y YEAR); 196SELECT * FROM t1 WHERE y IN ( CAST( '1993-03-26 10:14:20' AS DATE ), NULL ); 197DROP TABLE t1; 198 199--echo # 200--echo # End of 10.0 tests 201--echo # 202 203--echo # 204--echo # MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010 205--echo # 206CREATE TABLE t1 (a YEAR); 207INSERT INTO t1 VALUES (2010),(2020); 208SELECT * FROM t1 WHERE a=2010 AND a>=2010; 209EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010; 210SELECT * FROM t1 WHERE a=2010 AND a>=10; 211EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=10; 212SELECT * FROM t1 WHERE a=10 AND a>=2010; 213EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND a>=2010; 214SELECT * FROM t1 WHERE a=10 AND a>=10; 215EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND a>=10; 216DROP TABLE t1; 217 218 219--echo # 220--echo # MDEV-16958 Assertion `field_length < 5' failed in Field_year::val_str or data corruption upon SELECT with UNION and aggregate functions 221--echo # 222 223CREATE TABLE t1 (f YEAR); 224INSERT IGNORE INTO t1 VALUES (1971),(1972); 225SELECT MAX( NULLIF( f, '1900' ) ) AS f FROM t1 UNION SELECT MAX( NULLIF( f, '1900' ) ) AS f FROM t1; 226DROP TABLE t1; 227 228 229--echo # 230--echo # End of 10.1 tests 231--echo # 232 233--echo # 234--echo # MDEV-9392 Copying from DECIMAL to YEAR is not consistent about warnings 235--echo # 236CREATE TABLE t1 (a YEAR); 237INSERT IGNORE INTO t1 VALUES (-0.1); 238DROP TABLE t1; 239 240CREATE TABLE t1 (a YEAR); 241CREATE TABLE t2 (a DECIMAL(10,1)); 242INSERT INTO t2 VALUES (-0.1); 243INSERT IGNORE INTO t1 SELECT * FROM t2; 244DROP TABLE t1,t2; 245 246CREATE TABLE t1 (a DECIMAL(10,1)); 247INSERT INTO t1 VALUES (-0.1); 248SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 249ALTER TABLE t1 MODIFY a YEAR; 250DROP TABLE t1; 251 252CREATE TABLE t1 (a YEAR); 253INSERT IGNORE INTO t1 VALUES (-0.1e0); 254DROP TABLE t1; 255 256CREATE TABLE t1 (a YEAR); 257CREATE TABLE t2 (a DOUBLE); 258INSERT INTO t2 VALUES (-0.1); 259INSERT IGNORE INTO t1 SELECT * FROM t2; 260DROP TABLE t1,t2; 261 262CREATE TABLE t1 (a DOUBLE); 263INSERT INTO t1 VALUES (-0.1); 264SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 265ALTER TABLE t1 MODIFY a YEAR; 266DROP TABLE t1; 267 268--echo # 269--echo # Various widths of the YEAR 270--echo # 271create or replace table t1 (a YEAR(0)); SHOW CREATE TABLE t1; 272create or replace table t1 (a YEAR(1)); SHOW CREATE TABLE t1; 273create or replace table t1 (a YEAR(2)); SHOW CREATE TABLE t1; 274create or replace table t1 (a YEAR(3)); SHOW CREATE TABLE t1; 275create or replace table t1 (a YEAR(4)); SHOW CREATE TABLE t1; 276create or replace table t1 (a YEAR(5)); SHOW CREATE TABLE t1; 277create or replace table t1 (a YEAR(100)); SHOW CREATE TABLE t1; 278drop table t1; 279 280--echo # 281--echo # End of 10.2 tests 282--echo # 283