1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
drop table if exists t1;
CREATE TABLE t1 (
visitor_id int(10) unsigned DEFAULT '0' NOT NULL,
group_id int(10) unsigned DEFAULT '0' NOT NULL,
hits int(10) unsigned DEFAULT '0' NOT NULL,
sessions int(10) unsigned DEFAULT '0' NOT NULL,
ts timestamp,
PRIMARY KEY (visitor_id,group_id)
)/*! engine=MyISAM */;
INSERT INTO t1 VALUES (465931136,7,2,2,20000318160952);
INSERT INTO t1 VALUES (173865424,2,2,2,20000318233615);
INSERT INTO t1 VALUES (173865424,8,2,2,20000318233615);
INSERT INTO t1 VALUES (173865424,39,2,2,20000318233615);
INSERT INTO t1 VALUES (173865424,7,2,2,20000318233615);
INSERT INTO t1 VALUES (173865424,3,2,2,20000318233615);
INSERT INTO t1 VALUES (173865424,6,2,2,20000318233615);
INSERT INTO t1 VALUES (173865424,60,2,2,20000318233615);
INSERT INTO t1 VALUES (173865424,1502,2,2,20000318233615);
INSERT INTO t1 VALUES (48985536,2,2,2,20000319013932);
INSERT INTO t1 VALUES (48985536,8,2,2,20000319013932);
INSERT INTO t1 VALUES (48985536,39,2,2,20000319013932);
INSERT INTO t1 VALUES (48985536,7,2,2,20000319013932);
INSERT INTO t1 VALUES (465931136,3,2,2,20000318160951);
INSERT INTO t1 VALUES (465931136,119,1,1,20000318160953);
INSERT INTO t1 VALUES (465931136,2,1,1,20000318160950);
INSERT INTO t1 VALUES (465931136,8,1,1,20000318160950);
INSERT INTO t1 VALUES (465931136,39,1,1,20000318160950);
INSERT INTO t1 VALUES (1092858576,14,1,1,20000319013445);
INSERT INTO t1 VALUES (357917728,3,2,2,20000319145026);
INSERT INTO t1 VALUES (357917728,7,2,2,20000319145027);
select visitor_id,max(ts) as mts from t1 group by visitor_id
having mts < DATE_SUB(NOW(),INTERVAL 3 MONTH);
visitor_id mts
48985536 2000-03-19 01:39:32
173865424 2000-03-18 23:36:15
357917728 2000-03-19 14:50:27
465931136 2000-03-18 16:09:53
1092858576 2000-03-19 01:34:45
select visitor_id,max(ts) as mts from t1 group by visitor_id
having DATE_ADD(mts,INTERVAL 3 MONTH) < NOW();
visitor_id mts
48985536 2000-03-19 01:39:32
173865424 2000-03-18 23:36:15
357917728 2000-03-19 14:50:27
465931136 2000-03-18 16:09:53
1092858576 2000-03-19 01:34:45
drop table t1;
set sql_mode='traditional';
create table t1 (d date);
insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR);
ERROR 22008: Datetime function: datetime field overflow
insert into t1 (d) select date_add('2000-01-01',interval 8000 year);
ERROR 22008: Datetime function: datetime field overflow
insert into t1 values (date_add(NULL, INTERVAL 1 DAY));
insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY));
set sql_mode='';
insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR);
Warnings:
Warning 1441 Datetime function: datetime field overflow
insert into t1 (d) select date_add('2000-01-01',interval 8000 year);
Warnings:
Warning 1441 Datetime function: datetime field overflow
insert into t1 values (date_add(NULL, INTERVAL 1 DAY));
insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY));
select * from t1;
d
NULL
NULL
NULL
NULL
NULL
NULL
drop table t1;
End of 4.1 tests
SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY;
CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY
2006-09-27
SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH;
CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH
2006-10-26
SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR;
CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR
2007-09-26
SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK;
CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK
2006-10-03
create table t1 (a int, b varchar(10));
insert into t1 values (1, '2001-01-01'),(2, '2002-02-02');
select '2007-01-01' + interval a day from t1;
'2007-01-01' + interval a day
2007-01-02
2007-01-03
select b + interval a day from t1;
b + interval a day
2001-01-02
2002-02-04
drop table t1;
End of 5.0 tests
create table t1 (a varchar(10));
insert t1 values ('2000-12-03'),('2008-05-03');
select * from t1 where case a when adddate( '2012-12-12', 7 ) then true end;
a
drop table t1;
End of 5.5 tests
#
# Start of 10.1 tests
#
#
# MDEV-14452 Precision in INTERVAL xxx DAY_MICROSECOND parsed wrong?
#
SELECT
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5' DAY_MICROSECOND) c1,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50' DAY_MICROSECOND) c2,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500' DAY_MICROSECOND) c3,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000' DAY_MICROSECOND) c4,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000' DAY_MICROSECOND) c5,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000' DAY_MICROSECOND) c6,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000' DAY_MICROSECOND) c7,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000' DAY_MICROSECOND) c8,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000' DAY_MICROSECOND) c9,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000' DAY_MICROSECOND) c10,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000' DAY_MICROSECOND) c11,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000000' DAY_MICROSECOND) c12,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000000' DAY_MICROSECOND) c13,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000000' DAY_MICROSECOND) c14,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000000000' DAY_MICROSECOND) c15,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000000000' DAY_MICROSECOND) c16,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000000000' DAY_MICROSECOND) c17,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000000000000' DAY_MICROSECOND) c18,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000000000000' DAY_MICROSECOND) c19,
DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000000000000' DAY_MICROSECOND) c20
;
c1 1000-01-01 00:00:01.500000
c2 1000-01-01 00:00:01.500000
c3 1000-01-01 00:00:01.500000
c4 1000-01-01 00:00:01.500000
c5 1000-01-01 00:00:01.500000
c6 1000-01-01 00:00:01.500000
c7 1000-01-01 00:00:01.500000
c8 1000-01-01 00:00:01.500000
c9 1000-01-01 00:00:01.500000
c10 1000-01-01 00:00:01.500000
c11 1000-01-01 00:00:01.500000
c12 1000-01-01 00:00:01.500000
c13 1000-01-01 00:00:01.500000
c14 1000-01-01 00:00:01.500000
c15 1000-01-01 00:00:01.500000
c16 1000-01-01 00:00:01.500000
c17 1000-01-01 00:00:01.500000
c18 1000-01-01 00:00:01.500000
c19 1000-01-01 00:00:01.500000
c20 NULL
#
# End of 10.1 tests
#
create or replace view v1 as select 3 & 20010101 + interval 2 day as x;
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 3 & 20010101 + interval 2 day AS `x` latin1 latin1_swedish_ci
select 3 & 20010101 + interval 2 day, x from v1;
3 & 20010101 + interval 2 day x
3 3
create or replace view v1 as select (3 & 20010101) + interval 2 day as x;
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (3 & 20010101) + interval 2 day AS `x` latin1 latin1_swedish_ci
select (3 & 20010101) + interval 2 day, x from v1;
(3 & 20010101) + interval 2 day x
NULL NULL
Warnings:
Warning 1292 Incorrect datetime value: '1'
Warning 1292 Incorrect datetime value: '1'
create or replace view v1 as select 3 & (20010101 + interval 2 day) as x;
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 3 & 20010101 + interval 2 day AS `x` latin1 latin1_swedish_ci
select 3 & (20010101 + interval 2 day), x from v1;
3 & (20010101 + interval 2 day) x
3 3
create or replace view v1 as select 30 + 20010101 + interval 2 day as x;
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 30 + 20010101 + interval 2 day AS `x` latin1 latin1_swedish_ci
select 30 + 20010101 + interval 2 day, x from v1;
30 + 20010101 + interval 2 day x
2001-02-02 2001-02-02
create or replace view v1 as select (30 + 20010101) + interval 2 day as x;
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 30 + 20010101 + interval 2 day AS `x` latin1 latin1_swedish_ci
select (30 + 20010101) + interval 2 day, x from v1;
(30 + 20010101) + interval 2 day x
2001-02-02 2001-02-02
create or replace view v1 as select 30 + (20010101 + interval 2 day) as x;
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 30 + (20010101 + interval 2 day) AS `x` latin1 latin1_swedish_ci
select 30 + (20010101 + interval 2 day), x from v1;
30 + (20010101 + interval 2 day) x
20010133 20010133
drop view v1;
End of 10.2 tests
|
