Thanks for the information.
I agree with what you say.
There is just one comment I'd like to make.
You are right that the TIMESTAMP has a specific range. I am comparing
it to a date outside that range. This could cause problems.
But I strongly believe that the SQL user, who in many cases is not a
MySQL guru, should be abstracted from the mechanisms used within MySQL.
The SQL:
... WHERE [some_date_time_field] > '0001-01-01 00:00:00'
Is legal ANSI-SQL. Therefore the user should not be told off because
they haven't read that manual page. (Which is good reading, thanks for
the URI.)
But I am indebted to MySQL for kindly resolving this to work with the
DATETIME and return valid data.
Possibly two mechanisms would be a 'perfect' solution. The Warnings for
the SQL user to show them where their syntax is bad. Then a DBA log to
show where the internal mechanisms of MySQL are being violated....
But I love MySQL so people don't jump on me for suggesting changes!
You other point from my email:
>> ... WHERE '2006-06-06' > '2006-06-06 12:00:00'
>>
>> This will give a valid warning, this is bad SQL, time for a re-code!
I cannot replicate this. I did have a query where a time & date field
was compared to a date field which produced a warning, version 5.1.6.
(I know about warnings, they crash my programs!) So I'll have to leave
this point. I'll try and find it again if anybody is interested...
Thanks for the info,
Regards,
Ben
Michael Stassen wrote:
Ben Clewett wrote:
<snip>
> C# has two DateTime constants:
>
> DateTime.MinValue = '0001-01-01 00:00:00.000'
> DateTime.MaxValue = '9999-12-31 23:59:59.999'
>
<snip>
>
> MySQL really doesn't like these values, it shows warnings:
>
> +---------+------+-------------------------------------------------+
> | Level | Code | Message |
> +---------+------+-------------------------------------------------+
> | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' |
> +---------+------+-------------------------------------------------+
>
> The real problem with these warning is:
>
> 1. This date is legal, if a little unlikely.
> 2. Any warning crash MySql.Data.dll!!!
<snip>
Ben Clewett wrote:
> Hi Barry,
>
> This will happen when comparing against a TIMESTAMP field.
>
> CREATE TABLE a ( t TIMESTAMP );
>
> SELECT * FROM a WHERE t > '0001-01-01 00:00:00';
<snip>
Ben Clewett wrote:
Hi Barry,
> Well removing 'explicit' warnings for every user having problems with
> 3rd party modules would have mysql without any warnings nowadays ;)
>
> i think that your mono should get more stable.
I completely take this on board. This is a bug outside MySQL.
Warnings are very useful. When we test code and get a warning, this
almost always results in us re-coding. Eg:
... WHERE '2006-06-06' > '2006-06-06 12:00:00'
This will give a valid warning, this is bad SQL, time for a re-code!
This will not give a warning, as it is perfectly valid sql. (It will
simply return no rows, as the WHERE clause condition cannot be
satisfied.) Why do you think otherwise?
BUT my warning is a special case:
- It's not bad SQL.
Yes, it is bad sql. Your WHERE clause is
WHERE t > '0001-01-01 00:00:00'
As t is a TIMESTAMP column, mysql must convert the DATETIME constant to
a TIMESTAMP, but '0001-01-01 00:00:00' is not a valid TIMESTAMP. Hence
the warning.
- Because of the C# DateTime object, this happens often.
Therefore a candidate for dropping. Which will also avoid bugs in
badly written MySQL clients :)
But, I've probably said enough on the point :)
Ben
According to the manual
<http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html>,
the officially supported range for DATETIME columns is '1000-01-01
00:00:00' to '9999-12-31 23:59:59', though '0001-01-01 00:00:00' seems
to work in practice.
From the same manual page, the officially supported range for TIMESTAMP
columns is '1970-01-01 00:00:00' to partway through the year 2037. In
practice, TIMESTAMP columns take timezone into account. Hence, as my
offset from GMT is -5, the earliest TIMESTAMP I can get away with is
'1969-12-31 19:00:01'. That is,
SELECT * FROM a WHERE t > '1969-12-31 19:00:00';
produes a warning, but
SELECT * FROM a WHERE t > '1969-12-31 19:00:01';
does not.
I believe your problem begins with your assumption that DATETIME and
TIMESTAMP are interchangeable. They are not. You would probably be
fine using C#'s
DateTime.MinValue = '0001-01-01 00:00:00.000'
for actual DATETIME columns, but it just isn't valid for TIMESTAMP
columns. That said, mysql manages to do the right thing when faced with
this query. As '0001-01-01 00:00:00.000' is earlier than the first
valid TIMESTAMP, it is converted to a TIMESTAMP of 0, one second before
the earliest valid TIMESTAMP, and the query proceeds, giving the results
you expected, I believe. It also issues a warning to let you know what
it did.
This is where the second problem comes into play. Your system crashes
on warnings! As you've already admitted, this is "a bug in the .NET
MySQL library". The solution seems plain. The library must be fixed to
handle warnings. Mysql should not be changed back to the old days of
silently changing your query without issuing warnings.
Michael
--
Ben Clewett
+44(0)1923 460000
Project Manager
Road Tech Computer Systems Ltd
http://www.roadrunner.uk.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
