drop table if exists t1;
create table t1 (y year,y4 year(4));
Warnings:
Warning	1287	'YEAR(4)' is deprecated and will be removed in a future release. Please use YEAR instead
insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69);
select * from t1;
y	y4
0000	0000
1999	1999
2000	2000
2001	2001
1970	1970
2069	2069
select * from t1 order by y;
y	y4
0000	0000
1970	1970
1999	1999
2000	2000
2001	2001
2069	2069
select * from t1 order by y4;
y	y4
0000	0000
1970	1970
1999	1999
2000	2000
2001	2001
2069	2069
drop table t1;
create table t1 (y year);
insert into t1 values (now());
select if(y = now(), 1, 0) from t1;
if(y = now(), 1, 0)
0
drop table t1;
create table t1(a year);
insert ignore into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3');
Warnings:
Warning	1265	Data truncated for column 'a' at row 3
select * from t1;
a
2001
2001
2001
2001
drop table t1;
End of 5.0 tests
#
# Bug #49480: WHERE using YEAR columns returns unexpected results
#
CREATE TABLE t4(yyyy YEAR, c4 CHAR(4));
INSERT INTO t4 (c4) VALUES (NULL),(1970),(1999),(2000),(2001),(2069);
UPDATE t4 SET yyyy = c4;
SELECT * FROM t4;
yyyy	c4
NULL	NULL
1970	1970
1999	1999
2000	2000
2001	2001
2069	2069
# Comparison of YEAR with YEAR
SELECT * FROM t4 a, t4 b WHERE a.yyyy = b.yyyy;
yyyy	c4	yyyy	c4
1970	1970	1970	1970
1999	1999	1999	1999
2000	2000	2000	2000
2001	2001	2001	2001
2069	2069	2069	2069
SELECT * FROM t4 a, t4 b WHERE a.yyyy <=> b.yyyy;
yyyy	c4	yyyy	c4
NULL	NULL	NULL	NULL
1970	1970	1970	1970
1999	1999	1999	1999
2000	2000	2000	2000
2001	2001	2001	2001
2069	2069	2069	2069
SELECT * FROM t4 a, t4 b WHERE a.yyyy < b.yyyy;
yyyy	c4	yyyy	c4
1970	1970	1999	1999
1970	1970	2000	2000
1970	1970	2001	2001
1970	1970	2069	2069
1999	1999	2000	2000
1999	1999	2001	2001
1999	1999	2069	2069
2000	2000	2001	2001
2000	2000	2069	2069
2001	2001	2069	2069
# Comparison with constants:
SELECT * FROM t4 WHERE yyyy = NULL;
yyyy	c4
SELECT * FROM t4 WHERE yyyy <=> NULL;
yyyy	c4
NULL	NULL
SELECT * FROM t4 WHERE yyyy = NOW();
yyyy	c4
SELECT * FROM t4 WHERE yyyy = 99;
yyyy	c4
1999	1999
SELECT * FROM t4 WHERE yyyy = 'test';
yyyy	c4
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'test'
SELECT * FROM t4 WHERE yyyy = '1999';
yyyy	c4
1999	1999
SELECT * FROM t4 WHERE yyyy = 1999;
yyyy	c4
1999	1999
SELECT * FROM t4 WHERE yyyy = 1999.1;
yyyy	c4
1999	1999
SELECT * FROM t4 WHERE yyyy = 1998.9;
yyyy	c4
1999	1999
# Coverage tests for YEAR with zero/2000 constants:
SELECT * FROM t4 WHERE yyyy = 0;
yyyy	c4
SELECT * FROM t4 WHERE yyyy = '0';
yyyy	c4
2000	2000
SELECT * FROM t4 WHERE yyyy = '0000';
yyyy	c4
SELECT * FROM t4 WHERE yyyy = '2000';
yyyy	c4
2000	2000
SELECT * FROM t4 WHERE yyyy = 2000;
yyyy	c4
2000	2000
# Comparison with constants those are out of YEAR range
# (coverage test for backward compatibility)
SELECT COUNT(yyyy) FROM t4;
COUNT(yyyy)
5
SELECT COUNT(*) FROM t4 WHERE yyyy > -1;
COUNT(*)
5
SELECT COUNT(*) FROM t4 WHERE yyyy > -1000000000000000000;
COUNT(*)
5
SELECT COUNT(*) FROM t4 WHERE yyyy < 2156;
COUNT(*)
5
SELECT COUNT(*) FROM t4 WHERE yyyy < 1000000000000000000;
COUNT(*)
5
SELECT * FROM t4 WHERE yyyy < 123;
yyyy	c4
SELECT * FROM t4 WHERE yyyy > 123;
yyyy	c4
1970	1970
1999	1999
2000	2000
2001	2001
2069	2069
DROP TABLE t4;
#
# Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type
#
CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4));
INSERT IGNORE INTO t1 (s) VALUES ('bad');
Warnings:
Warning	1364	Field 'y' doesn't have a default value
INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001);
SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y;
y	s	y	s
0000	0	0000	0
0000	0	0000	bad
0000	bad	0000	0
0000	bad	0000	bad
2000	2000	2000	2000
2001	2001	2001	2001
SELECT * FROM t1 WHERE t1.y = 0;
y	s
0000	bad
0000	0
SELECT * FROM t1 WHERE t1.y = 2000;
y	s
2000	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;
ta_y	s	tb_y	s
0000	0	0000	0
0000	0	0000	bad
0000	bad	0000	0
0000	bad	0000	bad
2000	2000	2000	2000
2001	2001	2001	2001
DROP TABLE t1;
#
# Bug #59211: Select Returns Different Value for min(year) Function
#
CREATE TABLE t1(c1 YEAR);
INSERT INTO t1 VALUES (1901),(2155),(0000);
SELECT * FROM t1;
c1
1901
2155
0000
SELECT COUNT(*) AS total_rows, MIN(c1) AS min_value, MAX(c1) FROM t1;
total_rows	min_value	MAX(c1)
3	0	2155
DROP TABLE t1;
#
# WL#6263: Remove YEAR(2) type
#
CREATE TABLE t1 (c1 YEAR(2));
ERROR HY000: Invalid display width. Use YEAR instead.
CREATE TABLE t1 (c1 YEAR(0));
ERROR HY000: Invalid display width. Use YEAR instead.
CREATE TABLE t1 (c1 YEAR(4294967295));
ERROR HY000: Invalid display width. Use YEAR instead.
CREATE TABLE t1 (c1 YEAR(4294967296));
ERROR HY000: Invalid display width. Use YEAR instead.
CREATE TABLE t1 (c1 YEAR(4));
Warnings:
Warning	1287	'YEAR(4)' is deprecated and will be removed in a future release. Please use YEAR instead
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t1 VALUES (55);
UPDATE t1 SET c1=2016;
CHECK TABLE t1 FOR UPGRADE;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	note	The storage engine for the table doesn't support repair
DELETE FROM t1;
ALTER TABLE t1 MODIFY COLUMN c1 YEAR(2);
ERROR HY000: Invalid display width. Use YEAR instead.
DROP TABLE t1;
#
End of 5.1 tests
#
# Start of 5.6 tests
#
SET timestamp=UNIX_TIMESTAMP('2011-12-31 15:44:00');
CREATE TABLE t1 (a YEAR);
INSERT INTO t1 VALUES (CURRENT_TIME);
INSERT INTO t1 VALUES (TIME'15:44:00');
INSERT INTO t1 VALUES (TIME'25:00:00');
SELECT * FROM t1;
a
2011
2011
2012
DROP TABLE t1;
SET timestamp=DEFAULT;
#
# Bug#28172538 YEAR RANGE CHECK FAILS WITH MAX YEAR FLOAT CONSTANT
#
CREATE TABLE t(y YEAR);
INSERT INTO t VALUES (2155), (2155.0);
SELECT * FROM t;
y
2155
2155
Used to fail
INSERT INTO t VALUES (2155.0E00);
INSERT INTO t VALUES (2.1550E+03);
SELECT * FROM t;
y
2155
2155
2155
2155
DROP TABLE t;
#
# Bug#28562930 AVG(YEAR(DATETIME_FIELD)) MAKES AN ERROR RESULT BEACUSE OF OVERFLOW
#
CREATE TABLE t1(id INT,
name VARCHAR(10),
class CHAR(10),
date1 DATETIME);
INSERT INTO t1 VALUES (1,'zhangsan','class1','2014-01-14 15:32:17'),
(2,'lisi','class1','2013-12-14 10:21:27'),
(3,'wangwu','class2','2003-05-21 08:25:14'),
(4,'zhaoliu','class2','2001-07-19 09:35:18');
SELECT COUNT(DISTINCT name), AVG(YEAR(date1)) AS aa, class
FROM t1 GROUP BY class ORDER BY aa;
COUNT(DISTINCT name)	aa	class
2	2002.0000	class2
2	2013.5000	class1
DROP TABLE t1;
#
# Bug#29668676 DATA TRUNCATION ERROR OCCURRED ON A WRITE OF
#              COLUMN 0DATA WAS 0 BYTES LONG AND
#
CREATE TABLE t(QC_DATE_KEY bigint, QC_DATE date, QC_TIMESTAMP datetime);
INSERT INTO t VALUES
(1, '1999-02-28', '1999-11-28 14:45:00'),
(2, '1999-12-31', '2000-01-01 00:00:00');
SELECT ((year(QC_DATE) - year(QC_TIMESTAMP))) FROM t
WHERE QC_DATE_KEY = 2;
((year(QC_DATE) - year(QC_TIMESTAMP)))
-1
DROP TABLE t;
#
# WL#13537: Deprecate display width for the YEAR datatype
#
CREATE TABLE t1(a YEAR(4), b YEAR);
Warnings:
Warning	1287	'YEAR(4)' is deprecated and will be removed in a future release. Please use YEAR instead
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` year DEFAULT NULL,
  `b` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1';
COLUMN_TYPE
year
year
DESCRIBE t1;
Field	Type	Null	Key	Default	Extra
a	year	YES		NULL	
b	year	YES		NULL	
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v1';
COLUMN_TYPE
year
year
DESCRIBE v1;
Field	Type	Null	Key	Default	Extra
a	year	YES		NULL	
b	year	YES		NULL	
DROP VIEW v1;
DROP TABLE t1;
CREATE FUNCTION f1(a YEAR(4)) RETURNS YEAR(4) RETURN 1974;
Warnings:
Warning	1287	'YEAR(4)' is deprecated and will be removed in a future release. Please use YEAR instead
Warning	1287	'YEAR(4)' is deprecated and will be removed in a future release. Please use YEAR instead
CREATE FUNCTION f2(a YEAR) RETURNS YEAR RETURN 1974;
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(a YEAR(4)) RETURNS year
RETURN 1974	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
SHOW CREATE FUNCTION f2;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f2	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f2`(a YEAR) RETURNS year
RETURN 1974	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'f1';
DTD_IDENTIFIER
year
SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'f2';
DTD_IDENTIFIER
year
SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'f1';
DTD_IDENTIFIER
year
year
SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'f2';
DTD_IDENTIFIER
year
year
DROP FUNCTION f1;
DROP FUNCTION f2;
CREATE PROCEDURE p1(a YEAR(4), b YEAR) BEGIN END;
Warnings:
Warning	1287	'YEAR(4)' is deprecated and will be removed in a future release. Please use YEAR instead
SHOW CREATE PROCEDURE p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(a YEAR(4), b YEAR)
BEGIN END	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'p1';
DTD_IDENTIFIER
year
year
DROP PROCEDURE p1;
CREATE TABLE t1(a YEAR UNSIGNED);
Warnings:
Warning	1681	UNSIGNED for the YEAR data type is deprecated and support for it will be removed in a future release.
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
# Bug#31994744: Year should not be part of property functions
#               is_numeric_type() and is_integer_type()
CREATE TABLE t1
(y YEAR,
d DATE,
i INTEGER,
v VARCHAR(10));
INSERT INTO t1 VALUES(1901, DATE'1901-01-01', 1, 'one');
SELECT LEAST(d, y) FROM t1;
LEAST(d, y)
1901
Warnings:
Warning	1292	Incorrect date value: '1901' for column 'd' at row 1
SELECT LEAST(y, d) FROM t1;
LEAST(y, d)
1901
Warnings:
Warning	1292	Incorrect date value: '1901' for column 'd' at row 1
SELECT LEAST(i, y, v) FROM t1;
LEAST(i, y, v)
1
DROP TABLE t1;
CREATE TABLE t1
(pk INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
year_field YEAR
);
INSERT INTO t1 VALUES(NULL, 1901), (NULL, 1902), (NULL, 1903), (NULL, 1904);
SELECT pk FROM t1 WHERE year_field IN (1901, 1903);
pk
1
3
SELECT pk FROM t1 WHERE year_field IN ('1901', '1903');
pk
1
3
SELECT pk FROM t1 WHERE year_field IN (1901, '1903');
pk
1
3
DROP TABLE t1;
# Bug#32395335: Assertion `!is_temporal()' failed|sql/item.cc
CREATE TABLE t1 (
d DATE DEFAULT NULL,
y1 YEAR DEFAULT NULL,
y2 YEAR DEFAULT NULL,
dbl DOUBLE DEFAULT NULL
);
INSERT INTO t1 VALUES('1978-07-14',1957,2085,87315.633);
SELECT TIMESTAMPDIFF(QUARTER, LEAST(y1, y2), dbl)
FROM t1;
TIMESTAMPDIFF(QUARTER, LEAST(y1, y2), dbl)
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1957'
SELECT HOUR(GREATEST(y1, y2))
FROM t1;
HOUR(GREATEST(y1, y2))
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '2085'
DROP TABLE t1;