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

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

--error 1686 # No more bad dates!

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

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

169

167

170

168

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

171

169

SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" 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;

170

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

179

171

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

180

172

181

173

195

187

create table t1 (id int);

196

188

create table t2 (id int, date date);

197

189

insert into t1 values (1);

198

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

190

insert into t2 values (1, NULL);

199

191

insert into t1 values (2);

200

192

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

201

193

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

207

199

208

200

CREATE TABLE t1 (updated text) ENGINE=MyISAM;

209

201

INSERT INTO t1 VALUES ('');

202

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

210

203

SELECT month(updated) from t1;

204

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

211

205

SELECT year(updated) from t1;

212

206

drop table t1;

213

207

218

212

219

213

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

220

214

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

215

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

221

216

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

217

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

222

218

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

219

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

223

220

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

224

221

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

225

222

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

223

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

226

224

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

227

225

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

228

226

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

297

295

298

296

299

297

# Check positive shift. (it happens only on

300

# platfroms with unsigned time_t, such as QNX)

298

# platfroms with time_t, such as QNX)

301

299

302

300

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

303

301

446

444

# Bug #18501: monthname and NULLs

447

445

448

446

447

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

449

448

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

450

449

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

451

450

452

451

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

463

452

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

464

453

# can lead to a wrong result.

465

454

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

514

503

SELECT MAKETIME(-4294967296, 0, 0);

515

504

SELECT MAKETIME(0, 4294967296, 0);

516

505

SELECT MAKETIME(0, 0, 4294967296);

517

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

518

506

519

507

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

520

508

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

522

510

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

523

511

# and out-of-range value occur

524

512

CREATE TABLE t1(f1 TIME);

513

--error 1292

525

514

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

526

515

SELECT * FROM t1;

527

516

DROP TABLE t1;

528

517

529

518

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

535

536

519

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

537

520

# mysql-connector-j driver.

538

521

539

522

540

SET NAMES latin1;

541

SET character_set_results = NULL;

542

523

SHOW VARIABLES LIKE 'character_set_results';

543

524

544

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

525

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

545

526

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

546

527

547

528

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

548

529

549

530

DROP TABLE testBug8868;

550

531

551

SET NAMES DEFAULT;

552

553

532

554

533

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

555

534

# filesort

603

582

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

604

583

605

584

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

661

585

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

662

586

663

587

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

703

627

704

628

DROP TABLE t1,t2;

705

629

706

707

# Restore timezone to default

708

set time_zone= @@global.time_zone;

709

710

630

711

631

# Bug #22229: bug in DATE_ADD()

712

632

746

666

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

747

667

748

668

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

773

669

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

774

670

# instead of DATETIME

775

671

793

689

# TIMESTAMPADD / TIMESTAMPDIFF, is a server error.

794

690

795

691

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

796

--disable_ps_protocol

797

692

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

798

693

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

799

--enable_ps_protocol

800

694

801

695

--error ER_PARSE_ERROR

802

696

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

Older »