~drizzle-trunk/drizzle/development

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');

129

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

130

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

131

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

132

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

# The following is an interval, not a time!

--error ER_INVALID_DATETIME_VALUE # Bad datetime

133

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

100

134

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

101

135

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

103

137

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

104

138

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

105

139

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

106

--error ER_INVALID_DATETIME_VALUE # Bad datetime

107

140

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

108

141

109

142

122

155

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

123

156

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

124

157

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

158

159

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

160

161

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

162

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

125

163

126

164

127

165

# Test big intervals (Bug #3498)

148

186

create table t1 (id int);

149

187

create table t2 (id int, date date);

150

188

insert into t1 values (1);

151

insert into t2 values (1, NULL);

189

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

152

190

insert into t1 values (2);

153

191

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

154

192

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

158

196

159

197

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

160

198

161

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

199

CREATE TABLE t1 (updated text) ENGINE=MyISAM;

162

200

INSERT INTO t1 VALUES ('');

163

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

164

201

SELECT month(updated) from t1;

165

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

166

202

SELECT year(updated) from t1;

167

203

drop table t1;

168

204

172

208

173

209

174

210

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

175

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

176

--error ER_INVALID_DATETIME_VALUE # Bad dates...

177

211

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

178

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

179

212

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

180

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

181

213

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

182

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

183

214

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

184

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

185

215

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

186

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

216

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

187

217

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

188

--error ER_INVALID_DATETIME_VALUE # Bad datetime

189

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

190

--error ER_INVALID_DATETIME_VALUE # Ugh. bad datetime

218

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

219

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

191

220

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;

192

221

drop table t1;

193

222

214

243

215

244

select @a:=FROM_UNIXTIME(1);

216

245

select unix_timestamp(@a);

217

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

246

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

218

247

219

248

220

249

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

243

272

# check for invalid dates

244

273

245

274

# bad year

246

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

247

275

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

248

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

249

276

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

250

277

# bad month

251

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

252

278

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

253

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

254

279

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

255

280

# bad day

256

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

257

281

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

258

--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp

259

282

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

260

283

261

284

263

286

# conversion).

264

287

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

265

288

289

290

# Check positive shift. (it happens only on

291

# platfroms with time_t, such as QNX)

292

293

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

294

266

295

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

267

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

296

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

268

297

269

298

270

299

271

300

# Test types from + INTERVAL

272

301

273

302

274

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

275

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

303

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

304

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

276

305

SELECT * from t1;

277

306

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

278

307

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

353

382

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

354

383

355

384

# end of bug

385

386

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

356

387

drop table t1;

357

388

358

389

# test for last_day

359

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

360

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

361

--error ER_INVALID_DATETIME_VALUE # Bad date

362

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

363

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

364

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

365

select last_day(NULL);

366

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

390

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

391

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

392

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

393

last_day('2001-02-12');

367

394

368

395

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

369

396

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

381

408

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

382

409

# TZ variable set to GMT-3

383

410

384

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

385

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

411

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

412

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

413

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

414

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

386

415

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

416

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

387

417

388

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);

418

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);

389

419

390

420

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

391

421

CREATE TABLE t1 (d DATETIME);

399

429

# Bug #10568

400

430

401

431

402

--error ER_INVALID_DATETIME_VALUE # Bad date

403

432

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

404

--error ER_INVALID_DATETIME_VALUE # Bad date

405

433

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

406

--error ER_INVALID_DATETIME_VALUE # Bad date

407

434

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

408

435

409

436

437

# Bug #18501: monthname and NULLs

438

439

440

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

441

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

442

443

410

444

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

411

445

# can lead to a wrong result.

412

446

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

413

create table t1(f1 date, f3 datetime);

414

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

415

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

447

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

448

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

449

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

416

450

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

417

451

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

418

452

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

453

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

454

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

419

455

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);

420

456

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);

421

457

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

425

461

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

426

462

drop table t1;

427

463

464

465

# Bug #16546

466

467

468

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

469

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

470

show create table t1;

471

drop table t1;

472

473

474

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

475

476

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

477

SELECT SEC_TO_TIME(3300000);

478

SELECT SEC_TO_TIME(3300000)+0;

479

SELECT SEC_TO_TIME(3600 * 4294967296);

480

481

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

482

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

483

484

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

485

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

486

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

487

488

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

489

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

490

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

491

492

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

493

SELECT MAKETIME(916,0,0);

494

SELECT MAKETIME(4294967296, 0, 0);

495

SELECT MAKETIME(-4294967296, 0, 0);

496

SELECT MAKETIME(0, 4294967296, 0);

497

SELECT MAKETIME(0, 0, 4294967296);

498

428

499

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

429

--error ER_INVALID_DATETIME_VALUE # Bad datetime

430

500

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

431

501

502

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

503

# and out-of-range value occur

504

CREATE TABLE t1(f1 TIME);

505

--error 1292

506

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

507

SELECT * FROM t1;

508

DROP TABLE t1;

509

432

510

433

511

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

434

512

# mysql-connector-j driver.

444

522

DROP TABLE testBug8868;

445

523

446

524

525

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

526

# filesort

527

528

CREATE TABLE t1 (

529

a TIMESTAMP

530

);

531

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

532

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

533

DROP TABLE t1;

534

535

# Bug #19844 time_format in Union truncates values

536

537

538

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

539

union

540

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

541

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

542

union

543

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

544

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

545

union

546

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

547

548

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

549

union

550

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

551

552

447

553

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

448

554

449

555

450

--error ER_INVALID_DATETIME_VALUE # Bad date

451

556

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

452

557

558

559

# Bug 23616: datetime functions with double argumets

560

561

562

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

563

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

564

453

565

--echo End of 4.1 tests

454

566

455

567

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

498

610

INSERT INTO t2 VALUES

499

611

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

500

612

501

--sorted_result

502

613

SELECT * FROM t1, t2

503

614

WHERE t1.day BETWEEN

504

615

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

505

--sorted_result

506

616

SELECT * FROM t1, t2

507

617

WHERE CAST(t1.day AS DATE) BETWEEN

508

618

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

510

620

DROP TABLE t1,t2;

511

621

512

622

623

# Bug #22229: bug in DATE_ADD()

624

625

626

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

627

628

513

629

# Bug #21103: DATE column not compared as DATE

514

630

515

631

523

639

drop table t1;

524

640

525

641

642

# Bug #25643: SEC_TO_TIME function problem

643

644

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

645

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

646

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

647

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

648

FROM t1;

649

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

650

FROM t1 ORDER BY a DESC;

651

DROP TABLE t1;

652

653

654

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

655

656

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

657

658

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

659

660

526

661

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

527

662

# instead of DATETIME

528

663

Older »