On Thursday, 01.02.2007 at 06:18 +0100, Danesh Daroui wrote:
> About allowing corrupted data, it is not the responsibility of database
> engine to verify if the data is valid or not since the database engine
> only "Manages" data and not validate.
It depends what you mean by 'validate'. The database ought, at least,
to return the same value that was INSERTed. Or, if it is not able to do
so, it should raise an error on the INSERT.
Look! This is MySQL 5:
>> mysql> create table foo(a date);
>> Query OK, 0 rows affected (0.05 sec)
>>
>> mysql> describe foo;
>> +-------+------+------+-----+---------+-------+
>> | Field | Type | Null | Key | Default | Extra |
>> +-------+------+------+-----+---------+-------+
>> | a | date | YES | | NULL | |
>> +-------+------+------+-----+---------+-------+
>> 1 row in set (0.00 sec)
>>
>> mysql> insert into foo values('2007-02-31');
>> Query OK, 1 row affected, 1 warning (0.00 sec)
>>
>> mysql> select * from foo;
>> +------------+
>> | a |
>> +------------+
>> | 0000-00-00 |
>> +------------+
>> 1 row in set (0.00 sec)
It generates a warning but still inserts the record. This strikes me as
dangerous, especially as the default behaviour of 'BEGIN' and 'COMMIT'
wrapped around the above *still* inserts the record. I seem to remember
that slightly older MySQL versions returned '2007-03-03' instead in the
above circumstances, which was ludicrous; at least that has been fixed.
The same test under PostgreSQL 8.1:
>> pg=# create table foo(a date);
>> CREATE TABLE
>> pg=# \d foo
>> Table "public.foo"
>> Column | Type | Modifiers
>> --------+------+-----------
>> a | date |
>>
>> pg=# insert into foo values('2007-02-31');
>> ERROR: date/time field value out of range: "2007-02-31"
>> pg=# select * from foo;
>> a
>> ---
>> (0 rows)
Seems like very sensible behaviour to me.
Dave.
--
Please don't CC me on list messages!
...
Dave Ewart - davee@sungate.co.uk - jabber: davee@jabber.org
All email from me is now digitally signed, key from http://www.sungate.co.uk/
Fingerprint: AEC5 9360 0A35 7F66 66E9 82E4 9E10 6769 CD28 DA92
Attachment:
signature.asc
Description: Digital signature