Only to make sure we don't have any OS-specific I tried your test on my
Win32 installation. Same result:
---< snip
mysql> create table test (seconds bigint not null primary key);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values (998556700), (1000013720), (1000011111);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------------+
| seconds |
+------------+
| 998556700 |
| 1000011111 |
| 1000013720 |
+------------+
3 rows in set (0.00 sec)
mysql> select min(seconds) from test;
+--------------+
| min(seconds) |
+--------------+
| 998556700 |
+--------------+
1 row in set (0.03 sec)
mysql> select max(seconds) from test;
+--------------+
| max(seconds) |
+--------------+
| 1000013720 |
+--------------+
1 row in set (0.00 sec)
mysql> status
--------------
mysql Ver 11.15 Distrib 3.23.41, for Win95/Win98 (i32)
---< snap
Then I thought, maybe a problem with indexes, so I recreated the table
without a primary key. Still same result. However, in the first case (with
primary key), the select might not have used the index, so I tested for
that. Indeed: explain came up with the comment
---< snip
+------------------------------+
| Comment |
+------------------------------+
| Select tables optimized away |
+------------------------------+
---< snap
A quick search of the documentation did not yield any useful information.
Therefore, I inserted an additional 200000 rows into the table (resulted in
1.7MB data and 3.5MB index size), but still the same comment. So it appears
that the min and max value is stored somewhere for the table or the index.
To find out a little more, I recreated the table once again without an index
and repeated the procedure. The result was:
---< snip
mysql> select min(seconds) from test;
+--------------+
| min(seconds) |
+--------------+
| 0 |
+--------------+
1 row in set (0.07 sec)
mysql> explain select min(seconds) from test;
+-------+------+---------------+------+---------+------+--------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+--------+-------+
| test | ALL | NULL | NULL | NULL | NULL | 200004 | |
+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)
---< snap
Now I got the expected full table scan. Same thing for 3 rows. But still the
result is correct. The problem could still be with the port of MySQL Panos
is using or with the table definition. "Script" seems to make it unlikely
that it would be in the definition of the variable used for storing the
results retrieved.
I've wandered a bit off the original topic here, but since I think the
results may still be interesting for some of you I'm posting this anyway.
Cheers,
Christian
-----Ursprüngliche Nachricht-----
Von: Jason Brooke [mailto:[EMAIL PROTECTED]]
Gesendet: Sonntag, 9. September 2001 09:24
An: Panos Kalos; [EMAIL PROTECTED]
Betreff: Re: Billionium Bug
> To Whom it may concern,
>
> I've noticed a bug with the Billionium. Now that we have hit 1 Billion
> seconds since the beginning of the Epoch mysql will NOT work properly with
a
> MIN clause involving seconds before the Billionium.
>
> To clarify, I have a script that keeps track of the seconds of which the
> data was entered. I then use another script that pulls that data using a
> 'MIN(seconds)' statement to pull the oldest entry. This no longer works
as
> it will only pull data after 1 billion seconds and disregarding anything
> prior.
>
> Im sure you can easily recreate this event by entering false info into a
DB.
>
> Regards,
> Panos
I'm not seeing this behaviour with 3.23.41
mysql> show columns from test;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| seconds | int(11) | | MUL | 1 | |
+---------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select min(seconds) from test;
+--------------+
| min(seconds) |
+--------------+
| 998556700 |
+--------------+
1 row in set (0.18 sec)
mysql> select max(seconds) from test;
+--------------+
| max(seconds) |
+--------------+
| 1000013720 |
+--------------+
1 row in set (0.00 sec)
mysql> status;
--------------
mysql Ver 11.15 Distrib 3.23.41, for pc-linux-gnu (i686)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
