MySQL Bugs: #635: compare date where date= first or last

Bug #635	compare date where date= first or last
Submitted:	11 Jun 2003 4:27	Modified:	11 Jun 2003 6:12
Reporter:	Jan Andersen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23.56	OS:	Linux (Linux Redhat 9)
Assigned to:		CPU Architecture:	Any

Description:
When I want to select or update a table with date as primary key, it is impossible to select the record with the highest and lowes number.
SELECT * from performance where date=20030611; > DO NOT WORK
SELECT * from performance where date=20021014; > DO NOT WORK
SELECT * from performance where date=20030606; > DO WORK
SELECT * from performance where date=20030530; > DO WORK

20021014 | 195.13 |  100.00 | 162.09 | 100.00 | 1000000.00 |100.00 | NULL |
...
20030530 | 207.63 |  106.41 | 180.86 | 111.58 | 1159991.00 |116.00 | 0.36 |
20030606 | 213.65 |  109.49 | 186.12 | 114.83 | 1215721.00 |121.57 | 1.90 |
20030611 | 213.84 |  109.59 | 186.92 | 115.32 | 1215296.00 |121.53 |-0.12 |

How to repeat:
? (Don't understand the point.. by mail??)

Suggested fix:
? Just fix the problem...

I realised that if i write the date instead of writing null when the record is created, it works!

If I write null when the record is created then the actual date is written instead of null, but you can't select on the date afterward. (then it's only a minor bug:-)

The date field, is a timestamp(8)..

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Jan,

Your initial bug report misses critical information - Table structure and especially column `date` definition.

This is what should be in How-to-repeat
CREATE TABLE ...
INSERT ...
SELECT ....

Just in your second comment you clarify that you have date TIMESTAMP(8)

Then there is no bug at all because TIMESTAMP stores full-resolution TIMESTAMP value - up to seconds. (8) is just display value and this fact is well docummented in manual:
6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types

...

All TIMESTAMP columns have the same storage size, regardless of display size.

How to write proper WHERE clauses comparing TIMESTAMP to Date constants is also covered in manual and is off topic in Bugs Database