| Server IP : 104.21.38.3 / Your IP : 108.162.226.115 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/mysql-test/r/ |
Upload File : |
drop table if exists t1;
create table t1 (y year,y4 year(4));
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)
1
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 t2(yy YEAR(4), 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;
yy c2
NULL NULL
1970 1970
1999 1999
2000 2000
2001 2001
2069 2069
SELECT * FROM t4;
yyyy c4
NULL NULL
1970 1970
1999 1999
2000 2000
2001 2001
2069 2069
# Comparison of YEAR(4) with YEAR(4)
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
1999 1999 2000 2000
1970 1970 2001 2001
1999 1999 2001 2001
2000 2000 2001 2001
1970 1970 2069 2069
1999 1999 2069 2069
2000 2000 2069 2069
2001 2001 2069 2069
# Comparison with constants:
SELECT * FROM t2 WHERE yy = NULL;
yy c2
SELECT * FROM t4 WHERE yyyy = NULL;
yyyy c4
SELECT * FROM t2 WHERE yy <=> NULL;
yy c2
NULL NULL
SELECT * FROM t4 WHERE yyyy <=> NULL;
yyyy c4
NULL NULL
SELECT * FROM t2 WHERE yy < NULL;
yy c2
SELECT * FROM t2 WHERE yy > NULL;
yy c2
SELECT * FROM t2 WHERE yy = NOW();
yy c2
SELECT * FROM t4 WHERE yyyy = NOW();
yyyy c4
SELECT * FROM t2 WHERE yy = 99;
yy c2
1999 1999
SELECT * FROM t2 WHERE 99 = yy;
yy c2
1999 1999
SELECT * FROM t4 WHERE yyyy = 99;
yyyy c4
1999 1999
SELECT * FROM t2 WHERE yy = 'test';
yy c2
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'test'
SELECT * FROM t4 WHERE yyyy = 'test';
yyyy c4
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'test'
SELECT * FROM t2 WHERE yy = '1999';
yy c2
1999 1999
SELECT * FROM t4 WHERE yyyy = '1999';
yyyy c4
1999 1999
SELECT * FROM t2 WHERE yy = 1999;
yy c2
1999 1999
SELECT * FROM t4 WHERE yyyy = 1999;
yyyy c4
1999 1999
SELECT * FROM t2 WHERE yy = 1999.1;
yy c2
1999 1999
SELECT * FROM t4 WHERE yyyy = 1999.1;
yyyy c4
1999 1999
SELECT * FROM t2 WHERE yy = 1998.9;
yy c2
1999 1999
SELECT * FROM t4 WHERE yyyy = 1998.9;
yyyy c4
1999 1999
# Coverage tests for YEAR with zero/2000 constants:
SELECT * FROM t2 WHERE yy = 0;
yy c2
SELECT * FROM t2 WHERE yy = '0';
yy c2
2000 2000
SELECT * FROM t2 WHERE yy = '0000';
yy c2
SELECT * FROM t2 WHERE yy = '2000';
yy c2
2000 2000
SELECT * FROM t2 WHERE yy = 2000;
yy c2
2000 2000
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(yy) FROM t2;
COUNT(yy)
5
SELECT COUNT(yyyy) FROM t4;
COUNT(yyyy)
5
SELECT COUNT(*) FROM t2 WHERE yy = -1;
COUNT(*)
0
SELECT COUNT(*) FROM t4 WHERE yyyy > -1;
COUNT(*)
5
SELECT COUNT(*) FROM t2 WHERE yy > -1000000000000000000;
COUNT(*)
5
SELECT COUNT(*) FROM t4 WHERE yyyy > -1000000000000000000;
COUNT(*)
5
SELECT COUNT(*) FROM t2 WHERE yy < 2156;
COUNT(*)
5
SELECT COUNT(*) FROM t4 WHERE yyyy < 2156;
COUNT(*)
5
SELECT COUNT(*) FROM t2 WHERE yy < 1000000000000000000;
COUNT(*)
5
SELECT COUNT(*) FROM t4 WHERE yyyy < 1000000000000000000;
COUNT(*)
5
SELECT * FROM t2 WHERE yy < 123;
yy c2
SELECT * FROM t2 WHERE yy > 123;
yy c2
1970 1970
1999 1999
2000 2000
2001 2001
2069 2069
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 t2, 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 bad 0000 bad
0000 0 0000 bad
0000 bad 0000 0
0000 0 0000 0
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 bad 0000 bad
0000 0 0000 bad
0000 bad 0000 0
0000 0 0000 0
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(4));
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#6219: Deprecate and remove YEAR(2) type
#
CREATE TABLE t1 (c1 YEAR(4), c2 YEAR(4));
ALTER TABLE t1 MODIFY COLUMN c2 YEAR(4);
DROP TABLE t1;
#
#
# WL#6263: Remove YEAR(2) type
#
CREATE TABLE t1 (c1 YEAR(2));
ERROR HY000: Supports only YEAR or YEAR(4) column.
CREATE TABLE t1 (c1 YEAR(0));
ERROR HY000: Supports only YEAR or YEAR(4) column.
CREATE TABLE t1 (c1 YEAR(4294967295));
ERROR HY000: Supports only YEAR or YEAR(4) column.
CREATE TABLE t1 (c1 YEAR(4294967296));
ERROR HY000: Supports only YEAR or YEAR(4) column.
CREATE TABLE t1 (c1 YEAR(4));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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: Supports only YEAR or YEAR(4) column.
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;