~drizzle-trunk/drizzle/development

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/t/select.test

1792

INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);

1793

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;

1794

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

1795

# Disable PS becasue we get more warnings from PS than from normal execution

1796

--disable_ps_protocol

1795

--error 1686 # bad datetime

1797

1796

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;

1798

--enable_ps_protocol

1799

1797

# Testing the same select with NULL's instead of invalid datetime values

1800

1798

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;

1801

1799

DROP TABLE t1,t2;

2587

2585

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

2588

2586

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

2589

2587

# should return all records

2588

--error 1686 # Bad date

2590

2589

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

2590

--error 1686 # Bad date

2591

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

2592

# should return 4,5

2593

--error 1686 # Bad date

2593

2594

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

2595

--error 1686 # Bad date

2594

2596

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

2595

# should return 1,2,3

2597

--error 1686 # Bad date

2596

2598

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

2599

--error 1686 # Bad date

2597

2600

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

2598

2601

drop table t1;

2599

2602

3296

3299

# Bug#31800: Date comparison fails with timezone and slashes for greater

3297

3300

# than comparison

3298

3301

3299

3302

3303

# @TODO Commenting all str_to_date out for now...should move to a plugin.

3304

3300

3305

# On DATETIME-like literals with trailing garbage, BETWEEN fudged in a

3301

3306

# DATETIME comparator, while greater/less-than used bin-string comparisons.

3302

3307

# Should correctly be compared as DATE or DATETIME, but throw a warning:

3303

3308

3304

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

3305

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

3306

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

3307

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

3309

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

3310

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

3311

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

3312

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

3308

3313

3309

3314

# We have all we need -- and trailing garbage:

3310

3315

# (leaving out a leading zero in first example to prove it's a

3311

3316

# value-comparison, not a string-comparison!)

3312

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

3313

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

3314

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

3315

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

3317

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

3318

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

3319

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

3320

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

3316

3321

# no time at all:

3317

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

3322

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

3318

3323

# partial time:

3319

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

3324

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

3320

3325

# fail, different second part:

3321

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

3326

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

3322

3327

# correct syntax, no trailing nonsense -- this one must throw no warning:

3323

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

3328

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

3324

3329

# no warning, but failure (different hour parts):

3325

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

3330

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

3326

3331

# succeed:

3327

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

3332

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

3328

3333

# succeed, but warn for "trailing garbage" (":34"):

3329

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

3334

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

3330

3335

# invalid date (Feb 30) succeeds

3331

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

3336

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

3332

3337

# 0-day for both, just works in default SQL mode.

3333

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

3338

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

3334

3339

# 0-day, succeed

3335

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

3336

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

3337

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

3338

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

3339

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

3340

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

3341

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

3340

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

3341

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

3342

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

3343

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

3344

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

3345

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

3346

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

3342

3347

3343

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

3344

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

3345

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

3348

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

3349

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

3350

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

3346

3351

3347

3352

# these three should work!

3348

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

3349

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

3350

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

3353

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

3354

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

3355

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

3351

3356

###########################################################################

3352

3357

3353

3358

--echo

Older »