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

129

122

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

130

123

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

131

124

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

132

133

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

134

135

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

136

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

137

125

138

126

139

127

# Test big intervals (Bug #3498)

283

271

# Test types from + INTERVAL

284

272

285

273

286

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

287

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

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

288

276

SELECT * from t1;

289

277

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

290

278

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

365

353

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

366

354

367

355

# end of bug

368

369

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

370

356

drop table t1;

371

357

372

358

# test for last_day

396

382

# TZ variable set to GMT-3

397

383

398

384

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

399

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

400

385

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

401

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

402

386

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

403

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

404

387

405

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

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

406

389

407

390

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

408

391

CREATE TABLE t1 (d DATETIME);

424

407

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

425

408

426

409

427

# Bug #18501: monthname and NULLs

428

429

430

--error 1686 # Once again no bad dates allowed!

431

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

432

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

433

434

435

410

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

436

411

# can lead to a wrong result.

437

412

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

438

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

439

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

440

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

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

441

416

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

442

417

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

443

418

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

444

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

445

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

446

419

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

447

420

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

448

421

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

452

425

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

453

426

drop table t1;

454

427

455

456

# Bug #16546

457

458

459

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

460

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

461

show create table t1;

462

drop table t1;

463

464

465

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

466

467

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

468

SELECT SEC_TO_TIME(3300000);

469

SELECT SEC_TO_TIME(3300000)+0;

470

SELECT SEC_TO_TIME(3600 * 4294967296);

471

472

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

473

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

474

475

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

476

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

477

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

478

479

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

480

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

481

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

482

483

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

484

SELECT MAKETIME(916,0,0);

485

SELECT MAKETIME(4294967296, 0, 0);

486

SELECT MAKETIME(-4294967296, 0, 0);

487

SELECT MAKETIME(0, 4294967296, 0);

488

SELECT MAKETIME(0, 0, 4294967296);

489

490

428

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

491

429

--error 1686 # Bad datetime

492

430

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

493

431

494

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

495

# and out-of-range value occur

496

CREATE TABLE t1(f1 TIME);

497

--error 1292

498

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

499

SELECT * FROM t1;

500

DROP TABLE t1;

501

502

432

503

433

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

504

434

# mysql-connector-j driver.

514

444

DROP TABLE testBug8868;

515

445

516

446

517

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

518

# filesort

519

520

CREATE TABLE t1 (

521

a TIMESTAMP

522

);

523

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

524

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

525

DROP TABLE t1;

526

527

# Bug #19844 time_format in Union truncates values

528

529

530

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

531

union

532

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

533

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

534

union

535

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

536

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

537

union

538

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

539

540

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

541

union

542

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

543

544

545

447

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

546

448

547

449

606

508

DROP TABLE t1,t2;

607

509

608

510

609

# Bug #22229: bug in DATE_ADD()

610

611

612

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

613

614

615

511

# Bug #21103: DATE column not compared as DATE

616

512

617

513

625

521

drop table t1;

626

522

627

523

628

# Bug #25643: SEC_TO_TIME function problem

629

630

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

631

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

632

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

633

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

634

FROM t1;

635

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

636

FROM t1 ORDER BY a DESC;

637

DROP TABLE t1;

638

639

640

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

641

642

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

643

644

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

645

646

647

524

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

648

525

# instead of DATETIME

649

526

Older »