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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
