104
104
`length(uuid())` int(10) NOT NULL DEFAULT '0'
105
105
) ENGINE=MyISAM DEFAULT CHARSET=latin1
107
create table t1 (a timestamp default '2005-05-05 01:01:01',
108
b timestamp default '2005-05-05 01:01:01');
109
insert into t1 set a = now();
113
update t1 set b = now();
114
select timediff(b, a) >= '00:00:03' from t1;
115
timediff(b, a) >= '00:00:03'
118
set global query_cache_size=1355776;
119
create table t1 (a int);
120
insert into t1 values (1),(1),(1);
121
create table t2 (a datetime default null, b datetime default null);
122
insert into t2 set a = now();
123
select a from t1 where sleep(1);
125
update t2 set b = now() where b is null;
126
insert into t2 set a = now();
127
select a from t1 where sleep(a);
129
update t2 set b = now() where b is null;
130
insert into t2 set a = now();
131
select a from t1 where sleep(1);
133
update t2 set b = now() where b is null;
134
select timediff(b, a) >= '00:00:03' from t2;
135
timediff(b, a) >= '00:00:03'
141
set global query_cache_size=default;
107
#------------------------------------------------------------------------
108
# Tests for Bug#6760 and Bug#12689
110
SET @sleep_time_per_result_row = 1;
111
SET @max_acceptable_delay = 2;
112
SET @@global.query_cache_size = 1024 * 64;
113
DROP TEMPORARY TABLE IF EXISTS t_history;
114
DROP TABLE IF EXISTS t1;
115
CREATE TEMPORARY TABLE t_history (attempt SMALLINT,
116
start_ts DATETIME, end_ts DATETIME,
117
start_cached INTEGER, end_cached INTEGER);
118
CREATE TABLE t1 (f1 BIGINT);
119
INSERT INTO t1 VALUES (1);
120
INSERT INTO t1 VALUES (1);
121
INSERT INTO t1 VALUES (1);
122
INSERT INTO t1 VALUES (1);
123
INSERT INTO t_history
124
SET attempt = 4 - 4 + 1, start_ts = NOW(),
126
SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
127
f1 SLEEP(@sleep_time_per_result_row)
132
UPDATE t_history SET end_ts = NOW()
133
WHERE attempt = 4 - 4 + 1;
134
UPDATE t_history SET end_cached = 0
135
WHERE attempt = 4 - 4 + 1;
136
INSERT INTO t_history
137
SET attempt = 4 - 3 + 1, start_ts = NOW(),
139
SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
140
f1 SLEEP(@sleep_time_per_result_row)
145
UPDATE t_history SET end_ts = NOW()
146
WHERE attempt = 4 - 3 + 1;
147
UPDATE t_history SET end_cached = 0
148
WHERE attempt = 4 - 3 + 1;
149
INSERT INTO t_history
150
SET attempt = 4 - 2 + 1, start_ts = NOW(),
152
SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
153
f1 SLEEP(@sleep_time_per_result_row)
158
UPDATE t_history SET end_ts = NOW()
159
WHERE attempt = 4 - 2 + 1;
160
UPDATE t_history SET end_cached = 0
161
WHERE attempt = 4 - 2 + 1;
162
INSERT INTO t_history
163
SET attempt = 4 - 1 + 1, start_ts = NOW(),
165
SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
166
f1 SLEEP(@sleep_time_per_result_row)
171
UPDATE t_history SET end_ts = NOW()
172
WHERE attempt = 4 - 1 + 1;
173
UPDATE t_history SET end_cached = 0
174
WHERE attempt = 4 - 1 + 1;
175
# Test 1: Does the query with SLEEP need a reasonable time?
176
SELECT COUNT(*) >= 4 - 1 INTO @aux1 FROM t_history
177
WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
178
BETWEEN 0 AND @max_acceptable_delay;
179
SELECT @aux1 AS "Expect 1";
182
# Test 2: Does the query with SLEEP need a reasonable time even in case
183
# of the non first execution?
184
SELECT COUNT(*) >= 4 - 1 - 1 INTO @aux2 FROM t_history
185
WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
186
BETWEEN 0 AND @max_acceptable_delay
188
SELECT @aux2 AS "Expect 1";
191
# Test 3: The query with SLEEP must be not cached.
192
SELECT COUNT(*) = 4 INTO @aux3 FROM t_history
193
WHERE end_cached = start_cached;
194
SELECT @aux3 AS "Expect 1";
198
DROP TEMPORARY TABLE t_history;
199
SET @@global.query_cache_size = default;
142
200
create table t1 select INET_ATON('255.255.0.1') as `a`;
143
201
show create table t1;
144
202
Table Create Table