Bug #6361 | Timestamp comparisons are failing at the second level | ||
---|---|---|---|
Submitted: | 1 Nov 2004 11:18 | Modified: | 1 Nov 2004 19:20 |
Reporter: | Richard Evans | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
Version: | 4.0.22-standard | OS: | Linux (RH Linux (2.4.22-1.2115.nptl)) |
Assigned to: | CPU Architecture: | Any |
[1 Nov 2004 11:18]
Richard Evans
[1 Nov 2004 14:51]
MySQL Verification Team
Hi, Thank you for the report, but I can't repeat it with my test data. Could you create a repeatable test case?
[1 Nov 2004 15:45]
Richard Evans
Ok, I've just done this: create table richard_test ( aa timestamp (14), bb varchar(10) not null unique, cc varchar(10), primary key(bb) ); insert into richard_test (aa, bb, cc) values (null, 'a', 'b'); insert into richard_test (aa, bb, cc) values (null, 'b', 'c'); Then the queries: mysql> select * from richard_test; +----------------+----+------+ | aa | bb | cc | +----------------+----+------+ | 20041101153410 | a | b | | 20041101153435 | b | c | +----------------+----+------+ 2 rows in set (0.00 sec) mysql> select max(aa) from richard_test; +----------------+ | max(aa) | +----------------+ | 20041101153435 | +----------------+ 1 row in set (0.00 sec) mysql> select * from richard_test where aa='20041101153435'; Empty set (0.00 sec) mysql> select * from richard_test where aa='20041101153410'; Empty set (0.00 sec) mysql> select * from richard_test where aa + 0='20041101153410'; +----------------+----+------+ | aa | bb | cc | +----------------+----+------+ | 20041101153410 | a | b | +----------------+----+------+ 1 row in set (0.00 sec) mysql> select * from richard_test where aa + 0='20041101153435'; +----------------+----+------+ | aa | bb | cc | +----------------+----+------+ | 20041101153435 | b | c | +----------------+----+------+ 1 row in set (0.00 sec) Repeating this on 4.0.21-standard, I get the expected result in all cases.
[1 Nov 2004 16:12]
MySQL Verification Team
Sorry, What do you get with the following query: select * from richard_test where aa=20041101153435;
[1 Nov 2004 16:30]
Richard Evans
Ok, here goes: mysql> select * from richard_test where aa = 20041101153435; Empty set (0.00 sec) mysql> select * from richard_test where aa + 0 = 20041101153435; +----------------+----+------+ | aa | bb | cc | +----------------+----+------+ | 20041101153435 | b | c | +----------------+----+------+ 1 row in set (0.00 sec)
[1 Nov 2004 19:20]
MySQL Verification Team
Worked like a charm for me with same version: mysql> create table t1(aa timestamp(14), bb varchar(10), cc varchar(10), primary key(bb)); Query OK, 0 rows affected (0.04 sec) mysql> insert into t1 values (null,"a","b"); Query OK, 1 row affected (0.02 sec) mysql> insert into t1 values (null,"c","c"); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----------------+----+------+ | aa | bb | cc | +----------------+----+------+ | 20041101212501 | a | b | | 20041101212505 | c | c | +----------------+----+------+ 2 rows in set (0.00 sec) mysql> select * from t1 where aa=20041101212501; +----------------+----+------+ | aa | bb | cc | +----------------+----+------+ | 20041101212501 | a | b | +----------------+----+------+ 1 row in set (0.08 sec) mysql> select * from t1 where aa=20041101212505; +----------------+----+------+ | aa | bb | cc | +----------------+----+------+ | 20041101212505 | c | c | +----------------+----+------+ 1 row in set (0.00 sec) mysql> select * from t1 where aa="20041101212501"; +----------------+----+------+ | aa | bb | cc | +----------------+----+------+ | 20041101212501 | a | b | +----------------+----+------+ 1 row in set (0.00 sec) mysql> select * from t1 where aa="20041101212505"; +----------------+----+------+ | aa | bb | cc | +----------------+----+------+ | 20041101212505 | c | c | +----------------+----+------+ 1 row in set (0.00 sec)
[2 Nov 2004 9:22]
Richard Evans
I've just set the same test up on our backup server and it works fine there, so it looks like we have a server problem. Sorry for the false alarm and thanks for looking into this so quickly.