>Description:
When attempting to select a result set by subtracting the value
of an unsigned INT column against UNIX_TIMESTAMP() the result set
is invalid.
>How-To-Repeat:
Test case:
mysql> create table t (ts int unsigned not null); insert into t values
(1008884715), (1008886691), (1008887691);
Query OK, 0 rows affected (0.05 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select (ts - unix_timestamp()) from t;
+-------------------------+
| (ts - unix_timestamp()) |
+-------------------------+
| 18446744073709551463 |
| 1823 |
| 2823 |
+-------------------------+
3 rows in set (0.01 sec)
mysql> alter table t modify ts int;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select (ts - unix_timestamp()) from t;
+-------------------------+
| (ts - unix_timestamp()) |
+-------------------------+
| -174 |
| 1802 |
| 2802 |
+-------------------------+
3 rows in set (0.01 sec)
mysql> alter table t modify ts int unsigned;
Query OK, 3 rows affected (0.24 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select (ts - unix_timestamp()) from t;
+-------------------------+
| (ts - unix_timestamp()) |
+-------------------------+
| 18446744073709551421 |
| 1781 |
| 2781 |
+-------------------------+
3 rows in set (0.00 sec)
mysql> set @ts=0; select @ts:=ts, (@ts - unix_timestamp()+0) from t;
Query OK, 0 rows affected (0.00 sec)
+------------+----------------------------+
| @ts:=ts | (@ts - unix_timestamp()+0) |
+------------+----------------------------+
| 1008884715 | -290 |
| 1008886691 | 1686 |
| 1008887691 | 2686 |
+------------+----------------------------+
3 rows in set (0.01 sec)
>Fix:
As shown above this can be correct by using a variable which I believe recasts
the
result set as a signed int.
This problem first appeared in 4.0.0-alpha and has been verified by multiple
people.
>Submitter-Id:
>Originator: Colin Faber
>Organization:
fpsn.net, Inc.
>MySQL support: none
>Synopsis: subtraction on an unsigned INT column against UNIX_TIMESTAMP() returns
>an invalid result set when the resulting value is below zero.
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.0-alpha (Source distribution)
>Environment:
System:
FreeBSD 4.3-STABLE - SMP
GCC:
Using builtin specs.
gcc version 2.95.3 [FreeBSD] 20010315 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS=''
LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1202644 Jul 15 23:17 /usr/lib/libc.a
lrwxr-xr-x 1 root wheel 9 Jul 15 23:17 /usr/lib/libc.so -> libc.so.4
lrwxr-xr-x 1 root wheel 9 May 18 2001 /usr/lib/libc.so.3 ->
libc.so.4
-r--r--r-- 1 root wheel 570968 Jul 15 23:17 /usr/lib/libc.so.4
Configure command:
./configure --prefix=/usr/local/mysql
---------------------------------------------------------------------
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
