~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/select.result

Committer: Brian Aker
Date: 2009-02-12 22:59:43 UTC
mfrom: (873.1.8 temporal-new)
Revision ID: brian@tangent.org-20090212225943-g2fh9aaw82sg9hko

Merge Jay.

files modified:
drizzled/field/date.cc

drizzled/field/datetime.cc

drizzled/item.cc

drizzled/item/cmpfunc.cc

drizzled/item/cmpfunc.h

tests/r/loaddata.result

tests/r/select.result

tests/r/type_date.result

tests/r/type_ranges.result

tests/t/loaddata.test

tests/t/select.test

tests/t/type_date.test

tests/t/type_ranges.test

Show diffs side-by-side

added added

removed removed

tests/r/select.result

1969

CREATE TABLE t2 ( hmid int default NULL, volid int default NULL, sampletid int default NULL, sampletime datetime default NULL, samplevalue bigint default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

1970

INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);

1971

SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;

1972

gvid the_success the_fail the_size the_time

1973

Warnings:

1974

Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1

1975

Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1

1972

ERROR HY000: Received an invalid datetime value 'wrong-date-value'.

1976

1973

SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;

1977

1974

gvid the_success the_fail the_size the_time

1978

1975

DROP TABLE t1,t2;

2813

2810

insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),

2814

2811

(4,'2005-10-01'),(5,'2005-12-30');

2815

2812

select * from t1 where f2 >= 0 order by f2;

2816

f1 f2

2817

1 2005-01-01

2818

2 2005-09-01

2819

3 2005-09-30

2820

4 2005-10-01

2821

5 2005-12-30

2813

ERROR HY000: Received an invalid datetime value '0'.

2822

2814

select * from t1 where f2 >= '0000-00-00' order by f2;

2823

f1 f2

2824

1 2005-01-01

2825

2 2005-09-01

2826

3 2005-09-30

2827

4 2005-10-01

2828

5 2005-12-30

2815

ERROR HY000: Received an invalid datetime value '0000-00-00'.

2829

2816

select * from t1 where f2 >= '2005-09-31' order by f2;

2830

f1 f2

2831

4 2005-10-01

2832

5 2005-12-30

2833

Warnings:

2834

Warning 1292 Incorrect date value: '2005-09-31' for column 'f2' at row 1

2817

ERROR HY000: Received an invalid datetime value '2005-09-31'.

2835

2818

select * from t1 where f2 >= '2005-09-3a' order by f2;

2836

f1 f2

2837

3 2005-09-30

2838

4 2005-10-01

2839

5 2005-12-30

2840

Warnings:

2841

Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1

2819

ERROR HY000: Received an invalid datetime value '2005-09-3a'.

2842

2820

select * from t1 where f2 <= '2005-09-31' order by f2;

2843

f1 f2

2844

1 2005-01-01

2845

2 2005-09-01

2846

3 2005-09-30

2847

Warnings:

2848

Warning 1292 Incorrect date value: '2005-09-31' for column 'f2' at row 1

2821

ERROR HY000: Received an invalid datetime value '2005-09-31'.

2849

2822

select * from t1 where f2 <= '2005-09-3a' order by f2;

2850

f1 f2

2851

1 2005-01-01

2852

2 2005-09-01

2853

Warnings:

2854

Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1

2823

ERROR HY000: Received an invalid datetime value '2005-09-3a'.

2855

2824

drop table t1;

2856

2825

CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a));

2857

2826

CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a));

3611

3580

3612

3581

3613

3582

DROP TABLE t1, t2, t3;

3614

select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'

3615

and '2007/10/20 00:00:00 GMT';

3616

str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'

3617

and '2007/10/20 00:00:00 GMT'

3618

3619

Warnings:

3620

Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT'

3621

Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT'

3622

select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';

3623

str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'

3624

3625

Warnings:

3626

Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6'

3627

select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';

3628

str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'

3629

3630

Warnings:

3631

Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6'

3632

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';

3633

str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'

3634

3635

Warnings:

3636

Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6'

3637

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';

3638

str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'

3639

3640

Warnings:

3641

Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6'

3642

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';

3643

str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'

3644

3645

Warnings:

3646

Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6'

3647

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';

3648

str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'

3649

3650

Warnings:

3651

Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6'

3652

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';

3653

str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'

3654

3655

Warnings:

3656

Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6'

3657

select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3658

str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'

3659

3660

Warnings:

3661

Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'

3662

select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3663

str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'

3664

3665

Warnings:

3666

Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'

3667

select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';

3668

str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'

3669

3670

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';

3671

str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'

3672

3673

select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';

3674

str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'

3675

3676

select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';

3677

str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'

3678

3679

Warnings:

3680

Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34'

3681

select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';

3682

str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'

3683

3684

Warnings:

3685

Warning 1292 Truncated incorrect datetime value: '2007-02-30 12:34'

3686

select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';

3687

str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'

3688

3689

select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'

3690

and '2007/10/20 00:00:00';

3691

str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'

3692

and '2007/10/20 00:00:00'

3693

3694

select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';

3695

str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'

3696

3697

Warnings:

3698

Warning 1292 Truncated incorrect datetime value: ''

3699

select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';

3700

str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'

3701

3702

select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';

3703

str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'

3704

3705

select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';

3706

str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'

3707

NULL

3708

select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';

3709

str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''

3710

3711

Warnings:

3712

Warning 1292 Truncated incorrect datetime value: ''

3713

select str_to_date('1','%Y-%m-%d') = '1';

3714

str_to_date('1','%Y-%m-%d') = '1'

3715

3716

Warnings:

3717

Warning 1292 Truncated incorrect date value: '1'

3718

select str_to_date('1','%Y-%m-%d') = '1';

3719

str_to_date('1','%Y-%m-%d') = '1'

3720

3721

Warnings:

3722

Warning 1292 Truncated incorrect date value: '1'

3723

select str_to_date('','%Y-%m-%d') = '';

3724

str_to_date('','%Y-%m-%d') = ''

3725

3726

Warnings:

3727

Warning 1292 Truncated incorrect date value: ''

3728

select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;

3729

str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL

3730

3731

select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';

3732

str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'

3733

3734

select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;

3735

str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL

3736

3737

3583

3738

3584

3739

3585

# Bug#30736: Row Size Too Large Error Creating a Table and

Older »