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:
None 
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
Description:
Timestamp comparisons are not working for timestamps which have different second components - adding  '+0' to the timestamp appears to correct this.

I've had to obfuscate the table and field names in the following example:

mysql> select max(f_ts) from TAB_LC where cc = 'XX' and dp = 'XX';

<pre>
+----------------+
| max(f_ts)  |
+----------------+
| 20041101020031 |
+----------------+
1 row in set (0.45 sec)
</pre>

mysql> select f_ts from TAB_LC where cc = 'XX' and dp = 'XX' and f_ts = '20041101020031' limit 1

<pre>
Empty set (0.46 sec)
</pre>

mysql> select f_ts from TAB_LC where cc = 'XX' and dp = 'XX' and f_ts > '20041101020031' limit 1

<pre>
+----------------+
| mod_time       |
+----------------+
| 20041101020031 |
+----------------+
1 row in set (0.00 sec)
</pre>

mysql> select f_ts from TAB_LC where cc = 'XX' and dp = 'XX' and f_ts + 0 = '20041101020031' limit 1

<pre>
+----------------+
| mod_time       |
+----------------+
| 20041101020031 |
+----------------+
1 row in set (0.00 sec)
</pre>

The above example works without problem on our 4.0.21-standard servers.

How to repeat:
See description

Suggested fix:
Looking at the changelog - this appears to be related to the timestamp changes that have gone into this release.
[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.