Michael Widenius wrote:
>
> Hi!
>
> >>>>> "Colin" == Colin Faber <[EMAIL PROTECTED]> 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.
Agreed; But isn't this a case where an expected result of zero is more
important that the unexpected `true'
result of `18446744073709551463'?
I don't know how other people feel about this but I personally think a
NULL value, zero value
or error returned would be a better solution.
>
> Regards,
> Monty
>
> ---------------------------------------------------------------------
> 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
--
Colin Faber
(303) 859-1491
fpsn.net, Inc.
---------------------------------------------------------------------
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
