~drizzle-trunk/drizzle/development

Viewing changes to tests/r/select.result

Committer: Jay Pipes
Date: 2009-02-12 18:16:06 UTC
mto: This revision was merged to the branch mainline in revision 883.
Revision ID: jpipes@serialcoder-20090212181606-vi7rd85rufufqxvc

Fixes Arg_comparator::can_compare_as_dates to never, ever allow bad
input data in comparisons. No more implicit conversions from bad
datetimes are allowed. We throw an error on bad input always.

In addition, fixes the Field_date class's store() methods for integer
types and corrects test cases that were allowing bad input.

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 »