~drizzle-trunk/drizzle/development

select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(date_add(curdate(), interval 1 day))-to_days(curdate()),weekday("1997-11-29");

select period_add("9602",-12),period_diff(199505,"9404") ;

select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());

select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;

select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"),

sec_to_time(time_to_sec("0:30:47")/6.21);

select sec_to_time(time_to_sec('-838:59:59'));

select now()-curdate()*1000000-curtime();

select strcmp(current_timestamp(),concat(current_date()," ",current_time()));

select strcmp(localtime(),concat(current_date()," ",current_time()));

select strcmp(localtimestamp(),concat(current_date()," ",current_time()));

select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w");

select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));

select dayofmonth("1997-01-02"),dayofmonth(19970323);

select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");

--error ER_INVALID_DATETIME_VALUE # No more bad dates!

select month("2001-02-00"),year("2001-01-01");

select DAYOFYEAR("1997-03-03"), QUARTER(980303);

select month("2001-02-00"),year("2001-00-00");

select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303);

select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);

# Test of week and yearweek

select week(19980101),week(19970101),week(19980101,1),week(19970101,1);

select week(19981231),week(19971231),week(19981231,1),week(19971231,1);

select week(19950101),week(19950101,1);

select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1);

select yearweek('1987-01-01',1),yearweek('1987-01-01');

select week("2000-01-01",0) as '2000', week("2001-01-01",0) as '2001', week("2002-01-01",0) as '2002',week("2003-01-01",0) as '2003', week("2004-01-01",0) as '2004', week("2005-01-01",0) as '2005', week("2006-01-01",0) as '2006';

select week("2000-01-06",0) as '2000', week("2001-01-06",0) as '2001', week("2002-01-06",0) as '2002',week("2003-01-06",0) as '2003', week("2004-01-06",0) as '2004', week("2005-01-06",0) as '2005', week("2006-01-06",0) as '2006';

select week("2000-01-01",1) as '2000', week("2001-01-01",1) as '2001', week("2002-01-01",1) as '2002',week("2003-01-01",1) as '2003', week("2004-01-01",1) as '2004', week("2005-01-01",1) as '2005', week("2006-01-01",1) as '2006';

select week("2000-01-06",1) as '2000', week("2001-01-06",1) as '2001', week("2002-01-06",1) as '2002',week("2003-01-06",1) as '2003', week("2004-01-06",1) as '2004', week("2005-01-06",1) as '2005', week("2006-01-06",1) as '2006';

select yearweek("2000-01-01",0) as '2000', yearweek("2001-01-01",0) as '2001', yearweek("2002-01-01",0) as '2002',yearweek("2003-01-01",0) as '2003', yearweek("2004-01-01",0) as '2004', yearweek("2005-01-01",0) as '2005', yearweek("2006-01-01",0) as '2006';

select yearweek("2000-01-06",0) as '2000', yearweek("2001-01-06",0) as '2001', yearweek("2002-01-06",0) as '2002',yearweek("2003-01-06",0) as '2003', yearweek("2004-01-06",0) as '2004', yearweek("2005-01-06",0) as '2005', yearweek("2006-01-06",0) as '2006';

select yearweek("2000-01-01",1) as '2000', yearweek("2001-01-01",1) as '2001', yearweek("2002-01-01",1) as '2002',yearweek("2003-01-01",1) as '2003', yearweek("2004-01-01",1) as '2004', yearweek("2005-01-01",1) as '2005', yearweek("2006-01-01",1) as '2006';

select yearweek("2000-01-06",1) as '2000', yearweek("2001-01-06",1) as '2001', yearweek("2002-01-06",1) as '2002',yearweek("2003-01-06",1) as '2003', yearweek("2004-01-06",1) as '2004', yearweek("2005-01-06",1) as '2005', yearweek("2006-01-06",1) as '2006';

select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3);

select week(20001231,2),week(20001231,3);

select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2', week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5', week(19981231,6) as '6', week(19981231,7) as '7';

select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2', week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5', week(20000101,6) as '6', week(20000101,7) as '7';

select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2', week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5', week(20000106,6) as '6', week(20000106,7) as '7';

select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2', week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5', week(20001231,6) as '6', week(20001231,7) as '7';

select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2', week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5', week(20010101,6) as '6', week(20010101,7) as '7';

select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2), yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6), yearweek(20001231,7);

set default_week_format = 6;

select week(20001231), week(20001231,6);

set default_week_format = 0;

set default_week_format = 2;

select week(20001231),week(20001231,2),week(20001231,0);

set default_week_format = 0;

select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');

select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');

132

select extract(YEAR_MONTH FROM "1999-01-02");

133

select extract(DAY FROM "1999-01-02");

134

select extract(DAY_HOUR FROM "1999-01-02 10:11:12");

# The following is an interval, not a time!

--error ER_INVALID_DATETIME_VALUE # Bad datetime

135

select extract(DAY_MINUTE FROM "02 10:11:12");

# The following is an interval, not a time!

--error ER_INVALID_DATETIME_VALUE # Bad datetime

136

select extract(DAY_SECOND FROM "225 10:11:12");

100

137

select extract(HOUR FROM "1999-01-02 10:11:12");

101

138

select extract(HOUR_MINUTE FROM "10:11:12");

103

140

select extract(MINUTE FROM "10:11:12");

104

141

select extract(MINUTE_SECOND FROM "10:11:12");

105

142

select extract(SECOND FROM "1999-01-02 10:11:12");

106

--error ER_INVALID_DATETIME_VALUE # Bad datetime

107

143

select extract(MONTH FROM "2001-02-00");

108

144

109

145

122

158

SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter;

123

159

SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter;

124

160

SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter;

161

162

# MySQL Bugs: #12356: DATE_SUB or DATE_ADD incorrectly returns null

163

164

SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);

165

SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);

125

166

126

167

127

168

# Test big intervals (Bug #3498)

128

169

129

170

SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND;

130

171

SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND;

131

SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;

172

SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND;

173

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE;

174

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR;

175

176

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND;

177

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE;

178

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR;

132

179

SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;

133

180

134

181

148

195

create table t1 (id int);

149

196

create table t2 (id int, date date);

150

197

insert into t1 values (1);

151

insert into t2 values (1, NULL);

198

insert into t2 values (1, "0000-00-00");

152

199

insert into t1 values (2);

153

200

insert into t2 values (2, "2000-01-01");

154

201

select monthname(date) from t1 inner join t2 on t1.id = t2.id;

158

205

159

206

# Test bug with month() and year() on text fields with wrong information

160

207

161

CREATE TEMPORARY TABLE t1 (updated text) ENGINE=MyISAM;

208

CREATE TABLE t1 (updated text) ENGINE=MyISAM;

162

209

INSERT INTO t1 VALUES ('');

163

--error ER_INVALID_DATETIME_VALUE # "" is not a date!

164

210

SELECT month(updated) from t1;

165

--error ER_INVALID_DATETIME_VALUE # "" is not a date for pete's sake.

166

211

SELECT year(updated) from t1;

167

212

drop table t1;

168

213

172

217

173

218

174

219

create table t1 (d date, dt datetime, t timestamp, c char(10));

175

insert into t1 values (null, null, null, null);

176

177

--error ER_INVALID_DATE_VALUE # Bad dates...

178

220

insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");

179

180

--error ER_INVALID_DATETIME_VALUE # 0000-00-00 is a bad date

181

221

select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1;

182

183

--error ER_INVALID_DATETIME_VALUE # 0000-00-00 is a bad date

184

222

select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1;

185

186

--error ER_INVALID_DATETIME_VALUE # 0000-00-00 is not a damn date.

187

223

select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;

188

189

--error ER_INVALID_DATETIME_VALUE # 0000-00-00 not a date

190

224

select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;

191

192

--error ER_INVALID_DATETIME_VALUE # Argh. 0000-00-00 is not a date.

225

select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1;

193

226

select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;

194

195

--error ER_INVALID_DATETIME_VALUE # Bad datetime

196

select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(c) from t1;

197

198

--error ER_INVALID_DATETIME_VALUE # Ugh. bad datetime

227

select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1;

228

select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1;

199

229

select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1;

200

230

drop table t1;

201

231

222

252

223

253

select @a:=FROM_UNIXTIME(1);

224

254

select unix_timestamp(@a);

225

select unix_timestamp('1970-01-01 00:00:01');

255

select unix_timestamp('1969-12-01 19:00:01');

226

256

227

257

228

258

# Tests for bug #6439 "unix_timestamp() function returns wrong datetime

250

280

251

281

# check for invalid dates

252

282

253

# Legal, Drizzle has 64bit timestamp

254

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

283

# bad year

255

284

select unix_timestamp('2039-01-20 01:00:00');

256

257

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

258

285

select unix_timestamp('1968-01-20 01:00:00');

259

260

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

286

# bad month

261

287

select unix_timestamp('2038-02-10 01:00:00');

262

263

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

264

288

select unix_timestamp('1969-11-20 01:00:00');

265

266

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

289

# bad day

267

290

select unix_timestamp('2038-01-20 01:00:00');

268

269

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

270

291

select unix_timestamp('1969-12-30 01:00:00');

271

292

272

293

274

295

# conversion).

275

296

select unix_timestamp('2038-01-17 12:00:00');

276

297

298

299

# Check positive shift. (it happens only on

300

# platfroms with unsigned time_t, such as QNX)

301

302

select unix_timestamp('1970-01-01 03:00:01');

303

277

304

# check bad date, close to the boundary (we cut them off in the very end)

278

select unix_timestamp('2038-01-19 03:14:07');

305

select unix_timestamp('2038-01-19 07:14:07');

279

306

280

307

281

308

282

309

# Test types from + INTERVAL

283

310

284

311

285

CREATE TABLE t1 (datetime_arg datetime, timestamp_arg timestamp, date_arg date);

286

INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02");

312

CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time);

313

INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08");

287

314

SELECT * from t1;

288

315

select date_add("1997-12-31",INTERVAL 1 SECOND);

289

316

select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH);

290

317

291

select date_add(datetime_arg, INTERVAL 1 SECOND) from t1;

292

select date_add(datetime_arg, INTERVAL 1 YEAR) from t1;

318

select date_add(datetime, INTERVAL 1 SECOND) from t1;

319

select date_add(datetime, INTERVAL 1 YEAR) from t1;

293

320

294

select date_add(date_arg, INTERVAL 1 SECOND) from t1;

295

select date_add(date_arg, INTERVAL 1 MINUTE) from t1;

296

select date_add(date_arg, INTERVAL 1 HOUR) from t1;

297

select date_add(date_arg, INTERVAL 1 DAY) from t1;

298

select date_add(date_arg, INTERVAL 1 MONTH) from t1;

299

select date_add(date_arg, INTERVAL 1 YEAR) from t1;

300

select date_add(date_arg, INTERVAL "1:1" MINUTE_SECOND) from t1;

301

select date_add(date_arg, INTERVAL "1:1" HOUR_MINUTE) from t1;

302

select date_add(date_arg, INTERVAL "1:1" DAY_HOUR) from t1;

303

select date_add(date_arg, INTERVAL "1 1" YEAR_MONTH) from t1;

304

select date_add(date_arg, INTERVAL "1:1:1" HOUR_SECOND) from t1;

305

select date_add(date_arg, INTERVAL "1 1:1" DAY_MINUTE) from t1;

306

select date_add(date_arg, INTERVAL "1 1:1:1" DAY_SECOND) from t1;

307

select date_add(date_arg, INTERVAL "1" WEEK) from t1;

308

select date_add(date_arg, INTERVAL "1" QUARTER) from t1;

309

select timestampadd(MINUTE, 1, date_arg) from t1;

310

select timestampadd(WEEK, 1, date_arg) from t1;

311

select timestampadd(SQL_TSI_SECOND, 1, date_arg) from t1;

321

select date_add(date,INTERVAL 1 SECOND) from t1;

322

select date_add(date,INTERVAL 1 MINUTE) from t1;

323

select date_add(date,INTERVAL 1 HOUR) from t1;

324

select date_add(date,INTERVAL 1 DAY) from t1;

325

select date_add(date,INTERVAL 1 MONTH) from t1;

326

select date_add(date,INTERVAL 1 YEAR) from t1;

327

select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1;

328

select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1;

329

select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1;

330

select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1;

331

select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1;

332

select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1;

333

select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;

334

select date_add(date,INTERVAL "1" WEEK) from t1;

335

select date_add(date,INTERVAL "1" QUARTER) from t1;

336

select timestampadd(MINUTE, 1, date) from t1;

337

select timestampadd(WEEK, 1, date) from t1;

338

select timestampadd(SQL_TSI_SECOND, 1, date) from t1;

312

339

# mysqltest.c discards an expected 'deprecated' warning on prepare stage

313

340

--disable_ps_protocol

314

select timestampadd(SQL_TSI_FRAC_SECOND, 1, date_arg) from t1;

341

select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1;

315

342

--enable_ps_protocol

316

343

317

344

select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;

364

391

SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29');

365

392

366

393

# end of bug

394

395

select date_add(time,INTERVAL 1 SECOND) from t1;

367

396

drop table t1;

368

397

369

398

# test for last_day

370

select last_day('2000-02-05');

371

select last_day('2002-12-31');

372

--error ER_INVALID_DATETIME_VALUE # Bad date

373

select last_day('2003-03-32');

374

select last_day('2003-04-01');

375

select last_day('2001-01-01 01:01:01');

376

select last_day(NULL);

377

select last_day('2001-02-12');

399

select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2,

400

last_day('2003-03-32') as f3, last_day('2003-04-01') as f4,

401

last_day('2001-01-01 01:01:01') as f5, last_day(NULL),

402

last_day('2001-02-12');

378

403

379

404

create table t1 select last_day('2000-02-05') as a,

380

405

from_days(to_days("960101")) as b;

392

417

# Test SAPDB UTC_% functions. This part is TZ dependant (It is supposed that

393

418

# TZ variable set to GMT-3

394

419

395

select strcmp(date_sub(localtimestamp(), interval 0 hour), utc_timestamp())=0;

396

select strcmp(date_format(date_sub(localtimestamp(), interval 0 hour),"%Y-%m-%d"), utc_date())=0;

420

select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0;

421

select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0;

422

select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0;

423

select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0;

397

424

select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;

425

select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;

398

426

399

explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);

427

explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);

400

428

401

429

SET @TMP='2007-08-01 12:22:49';

402

430

CREATE TABLE t1 (d DATETIME);

410

438

# Bug #10568

411

439

412

440

413

--error ER_INVALID_DATETIME_VALUE # Bad date

414

441

select last_day('2005-00-00');

415

--error ER_INVALID_DATETIME_VALUE # Bad date

416

442

select last_day('2005-00-01');

417

--error ER_INVALID_DATETIME_VALUE # Bad date

418

443

select last_day('2005-01-00');

419

444

420

445

446

# Bug #18501: monthname and NULLs

447

448

449

select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')),

450

monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));

451

452

453

# Bug #16327: problem with timestamp < 1970

454

455

456

set time_zone='-6:00';

457

create table t1(a timestamp);

458

insert into t1 values (19691231190001);

459

select * from t1;

460

drop table t1;

461

462

421

463

# Bug#16377 result of DATE/TIME functions were compared as strings which

422

464

# can lead to a wrong result.

423

465

# Now wrong dates should be compared only with CAST()

424

create table t1(f1 date, f3 datetime);

425

insert into t1 values ("2006-01-01", "2006-01-01 12:01:01");

426

insert into t1 values ("2006-01-02", "2006-01-02 12:01:02");

466

create table t1(f1 date, f2 time, f3 datetime);

467

insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");

468

insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");

427

469

select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);

428

470

select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);

429

471

select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);

472

select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);

473

select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);

430

474

select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);

431

475

select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);

432

476

select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;

436

480

select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);

437

481

drop table t1;

438

482

483

484

# Bug #16546

485

486

487

create table t1 select now() - now(), curtime() - curtime(),

488

sec_to_time(1) + 0, from_unixtime(1) + 0;

489

show create table t1;

490

drop table t1;

491

492

493

# Bug #11655: Wrong time is returning from nested selects - maximum time exists

494

495

# check if SEC_TO_TIME() handles out-of-range values correctly

496

SELECT SEC_TO_TIME(3300000);

497

SELECT SEC_TO_TIME(3300000)+0;

498

SELECT SEC_TO_TIME(3600 * 4294967296);

499

500

# check if TIME_TO_SEC() handles out-of-range values correctly

501

SELECT TIME_TO_SEC('916:40:00');

502

503

# check if ADDTIME() handles out-of-range values correctly

504

SELECT ADDTIME('500:00:00', '416:40:00');

505

SELECT ADDTIME('916:40:00', '416:40:00');

506

507

# check if SUBTIME() handles out-of-range values correctly

508

SELECT SUBTIME('916:40:00', '416:40:00');

509

SELECT SUBTIME('-916:40:00', '416:40:00');

510

511

# check if MAKETIME() handles out-of-range values correctly

512

SELECT MAKETIME(916,0,0);

513

SELECT MAKETIME(4294967296, 0, 0);

514

SELECT MAKETIME(-4294967296, 0, 0);

515

SELECT MAKETIME(0, 4294967296, 0);

516

SELECT MAKETIME(0, 0, 4294967296);

517

SELECT MAKETIME(CAST(-1 AS UNSIGNED), 0, 0);

518

439

519

# check if EXTRACT() handles out-of-range values correctly

440

--error ER_INVALID_DATETIME_VALUE # Bad datetime

441

520

SELECT EXTRACT(HOUR FROM '100000:02:03');

442

521

522

# check if we get proper warnings if both input string truncation

523

# and out-of-range value occur

524

CREATE TABLE t1(f1 TIME);

525

INSERT INTO t1 VALUES('916:00:00 a');

526

SELECT * FROM t1;

527

DROP TABLE t1;

528

529

530

# Bug #20927: sec_to_time treats big unsigned as signed

531

532

# check if SEC_TO_TIME() handles BIGINT UNSIGNED values correctly

533

SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED));

534

443

535

444

536

# 21913: DATE_FORMAT() Crashes mysql server if I use it through

445

537

# mysql-connector-j driver.

446

538

447

539

540

SET NAMES latin1;

541

SET character_set_results = NULL;

448

542

SHOW VARIABLES LIKE 'character_set_results';

449

543

450

CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32));

544

CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY);

451

545

INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd');

452

546

453

547

SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868;

454

548

455

549

DROP TABLE testBug8868;

456

550

551

SET NAMES DEFAULT;

552

553

554

# Bug #31160: MAKETIME() crashes server when returning NULL in ORDER BY using

555

# filesort

556

557

CREATE TABLE t1 (

558

a TIMESTAMP

559

);

560

INSERT INTO t1 VALUES (now()), (now());

561

SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a);

562

DROP TABLE t1;

563

564

# Bug #19844 time_format in Union truncates values

565

566

567

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H)

568

union

569

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H);

570

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H)

571

union

572

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H);

573

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H)

574

union

575

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H);

576

577

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H)

578

union

579

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H);

580

457

581

458

582

# Bug #23653: crash if last_day('0000-00-00')

459

583

460

584

461

--error ER_INVALID_DATETIME_VALUE # Bad date

462

585

select last_day('0000-00-00');

463

586

587

588

# Bug 23616: datetime functions with double argumets

589

590

591

select isnull(week(now() + 0)), isnull(week(now() + 0.2)),

592

week(20061108), week(20061108.01), week(20061108085411.000002);

593

464

594

--echo End of 4.1 tests

465

595

466

596

explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,

473

603

select time_format('100:00:00', '%H %k %h %I %l');

474

604

475

605

606

# Bug #12562: Make SYSDATE behave like it does in Oracle: always the current

607

# time, regardless of magic to make NOW() always the same for the

608

# entirety of a statement.

609

SET GLOBAL log_bin_trust_function_creators = 1;

610

611

create table t1 (a timestamp default '2005-05-05 01:01:01',

612

b timestamp default '2005-05-05 01:01:01');

613

delimiter //;

614

drop function if exists t_slow_sysdate;

615

create function t_slow_sysdate() returns timestamp

616

begin

617

do sleep(2);

618

return sysdate();

619

end;

620

621

622

insert into t1 set a = sysdate(), b = t_slow_sysdate();//

623

624

create trigger t_before before insert on t1

625

for each row begin

626

set new.b = t_slow_sysdate();

627

end

628

629

630

delimiter ;//

631

632

insert into t1 set a = sysdate();

633

634

select a != b from t1;

635

636

drop trigger t_before;

637

drop function t_slow_sysdate;

638

drop table t1;

639

640

SET GLOBAL log_bin_trust_function_creators = 0;

641

642

create table t1 (a datetime, i int, b datetime);

643

insert into t1 select sysdate(), sleep(1), sysdate() from dual;

644

select a != b from t1;

645

drop table t1;

646

647

delimiter //;

648

create procedure t_sysdate()

649

begin

650

select sysdate() into @a;

651

do sleep(2);

652

select sysdate() into @b;

653

select @a != @b;

654

end;

655

656

delimiter ;//

657

call t_sysdate();

658

drop procedure t_sysdate;

659

660

476

661

# Bug #13534: timestampdiff() returned incorrect results across leap years

477

662

478

663

select timestampdiff(month,'2004-09-11','2004-09-11');

509

694

INSERT INTO t2 VALUES

510

695

(1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15');

511

696

512

--sorted_result

513

697

SELECT * FROM t1, t2

514

698

WHERE t1.day BETWEEN

515

699

'2005.09.01' - INTERVAL 6 MONTH AND t2.day;

516

--sorted_result

517

700

SELECT * FROM t1, t2

518

701

WHERE CAST(t1.day AS DATE) BETWEEN

519

702

'2005.09.01' - INTERVAL 6 MONTH AND t2.day;

520

703

521

704

DROP TABLE t1,t2;

522

705

706

707

# Restore timezone to default

708

set time_zone= @@global.time_zone;

709

710

711

# Bug #22229: bug in DATE_ADD()

712

713

714

select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;

715

523

716

524

717

# Bug #21103: DATE column not compared as DATE

525

718

534

727

drop table t1;

535

728

536

729

730

# Bug #25643: SEC_TO_TIME function problem

731

732

CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY (a));

733

INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL),

734

(2, '11:00:00', '11:15:00', '1972-02-06');

735

SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)

736

FROM t1;

737

SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)

738

FROM t1 ORDER BY a DESC;

739

DROP TABLE t1;

740

741

742

# Bug #20293: group by cuts off value from time_format

743

744

# Check if using GROUP BY with TIME_FORMAT() produces correct results

745

746

SELECT TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s") FROM (SELECT 3020399 AS a UNION SELECT 3020398 ) x GROUP BY 1;

747

748

749

# Bug#28875 Conversion between ASCII and LATIN1 charsets does not function

750

751

set names latin1;

752

create table t1 (a varchar(15) character set ascii not null);

753

insert into t1 values ('070514-000000');

754

# Conversion of date_format() result to ASCII

755

# is safe with the default locale en_US

756

--replace_column 1 #

757

select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;

758

# Error for swe7: it is not ASCII compatible

759

set names swe7;

760

--error 1267

761

select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;

762

set names latin1;

763

# Conversion of date_format() result to ASCII

764

# is not safe with the non-default locale fr_FR

765

# because month and day names can have accented characters

766

set lc_time_names=fr_FR;

767

--error 1267

768

select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;

769

set lc_time_names=en_US;

770

drop table t1;

771

772

537

773

# Bug#32180: DATE_ADD treats datetime numeric argument as DATE

538

774

# instead of DATETIME

539

775

557

793

# TIMESTAMPADD / TIMESTAMPDIFF, is a server error.

558

794

559

795

# mysqltest.c discards an expected 'deprecated' warning on prepare stage

796

--disable_ps_protocol

560

797

SELECT TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18');

561

798

SELECT TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18');

799

--enable_ps_protocol

562

800

563

801

--error ER_PARSE_ERROR

564

802

SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND);

Older »