5 messages in com.mysql.lists.bugsUNIX_TIMESTAMP() & INT unsigned problems| From | Sent On | Attachments |
|---|---|---|
| Colin Faber | 20 Dec 2001 14:04 | |
| Michael Widenius | 21 Dec 2001 05:13 | |
| Sinisa Milivojevic | 21 Dec 2001 05:28 | |
| Colin Faber | 21 Dec 2001 12:31 | |
| Michael Widenius | 22 Dec 2001 02:01 |
| Subject: | UNIX_TIMESTAMP() & INT unsigned problems |
|---|---|
| From: | Michael Widenius (mon...@mysql.com) |
| Date: | 12/21/2001 05:13:01 AM |
| List: | com.mysql.lists.bugs |
Hi!
"Colin" == Colin Faber <cfa...@fpsn.net> writes:
Description:
Colin> When attempting to select a result set by subtracting the value Colin> of an unsigned INT column against UNIX_TIMESTAMP() the result set Colin> is invalid.
How-To-Repeat:
Colin> Test case:
mysql> create table t (ts int unsigned not null); insert into t values
(1008884715), (1008886691), (1008887691);
Colin> Query OK, 0 rows affected (0.05 sec)
Colin> Query OK, 3 rows affected (0.00 sec) Colin> Records: 3 Duplicates: 0 Warnings: 0
mysql> select (ts - unix_timestamp()) from t; Colin> +-------------------------+ Colin> | (ts - unix_timestamp()) | Colin> +-------------------------+ Colin> | 18446744073709551463 | Colin> | 1823 | Colin> | 2823 | Colin> +-------------------------+ Colin> 3 rows in set (0.01 sec)
The above is correct; You are subtracting two unsigned values, and the result is unsigned. Compare how C works!
<cut>
Colin> As shown above this can be correct by using a variable which I believe
recasts the
Colin> result set as a signed int.
Colin> This problem first appeared in 4.0.0-alpha and has been verified by
multiple people.
Yes; The difference is that MySQL 4.0 can now correctly handle unsigned values; Something that was wrong in 3.23.
If we would try to fix this, then we would not anymore have full support of 64 bit values in MySQL.
The proper way to fix this is to add a cast operator for unsigned to signed and vice versa. We shall look into doing this.
Regards, Monty