1# 2# Test of DATE_ADD 3# 4 5--disable_warnings 6drop table if exists t1; 7--enable_warnings 8 9CREATE TABLE t1 ( 10 visitor_id int(10) unsigned DEFAULT '0' NOT NULL, 11 group_id int(10) unsigned DEFAULT '0' NOT NULL, 12 hits int(10) unsigned DEFAULT '0' NOT NULL, 13 sessions int(10) unsigned DEFAULT '0' NOT NULL, 14 ts timestamp, 15 PRIMARY KEY (visitor_id,group_id) 16)/*! engine=MyISAM */; 17INSERT INTO t1 VALUES (465931136,7,2,2,20000318160952); 18INSERT INTO t1 VALUES (173865424,2,2,2,20000318233615); 19INSERT INTO t1 VALUES (173865424,8,2,2,20000318233615); 20INSERT INTO t1 VALUES (173865424,39,2,2,20000318233615); 21INSERT INTO t1 VALUES (173865424,7,2,2,20000318233615); 22INSERT INTO t1 VALUES (173865424,3,2,2,20000318233615); 23INSERT INTO t1 VALUES (173865424,6,2,2,20000318233615); 24INSERT INTO t1 VALUES (173865424,60,2,2,20000318233615); 25INSERT INTO t1 VALUES (173865424,1502,2,2,20000318233615); 26INSERT INTO t1 VALUES (48985536,2,2,2,20000319013932); 27INSERT INTO t1 VALUES (48985536,8,2,2,20000319013932); 28INSERT INTO t1 VALUES (48985536,39,2,2,20000319013932); 29INSERT INTO t1 VALUES (48985536,7,2,2,20000319013932); 30INSERT INTO t1 VALUES (465931136,3,2,2,20000318160951); 31INSERT INTO t1 VALUES (465931136,119,1,1,20000318160953); 32INSERT INTO t1 VALUES (465931136,2,1,1,20000318160950); 33INSERT INTO t1 VALUES (465931136,8,1,1,20000318160950); 34INSERT INTO t1 VALUES (465931136,39,1,1,20000318160950); 35INSERT INTO t1 VALUES (1092858576,14,1,1,20000319013445); 36INSERT INTO t1 VALUES (357917728,3,2,2,20000319145026); 37INSERT INTO t1 VALUES (357917728,7,2,2,20000319145027); 38select visitor_id,max(ts) as mts from t1 group by visitor_id 39having mts < DATE_SUB(NOW(),INTERVAL 3 MONTH); 40select visitor_id,max(ts) as mts from t1 group by visitor_id 41having DATE_ADD(mts,INTERVAL 3 MONTH) < NOW(); 42drop table t1; 43 44# 45# Bug #10627: Invalid date turned to NULL from date_sub/date_add in 46# traditional mode 47# 48set sql_mode='traditional'; 49create table t1 (d date); 50--error S22008 51insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); 52--error S22008 53insert into t1 (d) select date_add('2000-01-01',interval 8000 year); 54# No warnings/errors from the next two 55insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); 56insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); 57set sql_mode=''; 58# These will all work now, and we'll end up with some NULL entries in the 59# table and some warnings. 60insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); 61insert into t1 (d) select date_add('2000-01-01',interval 8000 year); 62insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); 63insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); 64select * from t1; 65drop table t1; 66 67--echo End of 4.1 tests 68 69# 70# Bug#21811 71# 72# Make sure we end up with an appropriate 73# date format (DATE) after addition operation 74# 75SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY; 76SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH; 77SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR; 78SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK; 79 80# 81# Bug#28450: The Item_date_add_interval in select list may fail the field 82# type assertion. 83# 84create table t1 (a int, b varchar(10)); 85insert into t1 values (1, '2001-01-01'),(2, '2002-02-02'); 86select '2007-01-01' + interval a day from t1; 87select b + interval a day from t1; 88drop table t1; 89 90--echo End of 5.0 tests 91 92# 93# MDEV-4284 Assertion `cmp_items[(uint)cmp_type]' fails in sql/item_cmpfunc.cc 94# 95 96create table t1 (a varchar(10)); 97insert t1 values ('2000-12-03'),('2008-05-03'); 98select * from t1 where case a when adddate( '2012-12-12', 7 ) then true end; 99drop table t1; 100 101--echo End of 5.5 tests 102 103--echo # 104--echo # Start of 10.1 tests 105--echo # 106 107--echo # 108--echo # MDEV-14452 Precision in INTERVAL xxx DAY_MICROSECOND parsed wrong? 109--echo # 110 111--vertical_results 112SELECT 113 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5' DAY_MICROSECOND) c1, 114 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50' DAY_MICROSECOND) c2, 115 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500' DAY_MICROSECOND) c3, 116 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000' DAY_MICROSECOND) c4, 117 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000' DAY_MICROSECOND) c5, 118 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000' DAY_MICROSECOND) c6, 119 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000' DAY_MICROSECOND) c7, 120 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000' DAY_MICROSECOND) c8, 121 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000' DAY_MICROSECOND) c9, 122 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000' DAY_MICROSECOND) c10, 123 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000' DAY_MICROSECOND) c11, 124 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000000' DAY_MICROSECOND) c12, 125 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000000' DAY_MICROSECOND) c13, 126 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000000' DAY_MICROSECOND) c14, 127 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000000000' DAY_MICROSECOND) c15, 128 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000000000' DAY_MICROSECOND) c16, 129 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000000000' DAY_MICROSECOND) c17, 130 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000000000000' DAY_MICROSECOND) c18, 131 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000000000000' DAY_MICROSECOND) c19, 132 DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000000000000' DAY_MICROSECOND) c20 133; 134--horizontal_results 135 136 137--echo # 138--echo # End of 10.1 tests 139--echo # 140 141# 142# how + interval is printed 143# 144 145create or replace view v1 as select 3 & 20010101 + interval 2 day as x; 146show create view v1; 147select 3 & 20010101 + interval 2 day, x from v1; 148 149create or replace view v1 as select (3 & 20010101) + interval 2 day as x; 150show create view v1; 151select (3 & 20010101) + interval 2 day, x from v1; 152 153create or replace view v1 as select 3 & (20010101 + interval 2 day) as x; 154show create view v1; 155select 3 & (20010101 + interval 2 day), x from v1; 156 157create or replace view v1 as select 30 + 20010101 + interval 2 day as x; 158show create view v1; 159select 30 + 20010101 + interval 2 day, x from v1; 160 161create or replace view v1 as select (30 + 20010101) + interval 2 day as x; 162show create view v1; 163select (30 + 20010101) + interval 2 day, x from v1; 164 165create or replace view v1 as select 30 + (20010101 + interval 2 day) as x; 166show create view v1; 167select 30 + (20010101 + interval 2 day), x from v1; 168 169drop view v1; 170 171--echo End of 10.2 tests 172