Bug #33916 Math error, with types (POW())

• Submitted: 18 Jan 2008 11:24
• Modified: 19 Jan 2008 9:42
• Reporter: Colin Charles
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.45,5.0.53bk
• OS: Linux (x86_64)
• CPU Architecture: Any

[18 Jan 2008 11:24] Colin Charles

Description:
Incorrect values being reported. Unsure if its unsigned because the column itself is unsigned. Reporting because behaviour in 5.0.22 and 5.0.45/5.0.53 bk snapshot is inconsistent. All tested on x86_64

How to repeat:
drop table if exists t1;
create table t1 (a tinyint unsigned not null default 0);
insert into t1 (a) values (2);
select pow(a-3,2) from t1;

Example output (works):
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.22    | 
+-----------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a tinyint unsigned not null default 0);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (a) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select pow(a-3,2) from t1;
+------------+
| pow(a-3,2) |
+------------+
|          1 | 
+------------+
1 row in set (0.00 sec)

Example output (fails):
(root@localhost) [test]> select version();
+-----------+
| version() |
+-----------+
| 5.0.45    | 
+-----------+
1 row in set (0.05 sec)

(root@localhost) [test]> drop table if exists t1;
Query OK, 0 rows affected (0.06 sec)

(root@localhost) [test]> create table t1 (a tinyint unsigned not null default 0);
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> insert into t1 (a) values (2);
Query OK, 1 row affected (0.01 sec)

(root@localhost) [test]> select pow(a-3,2) from t1;
+---------------------+
| pow(a-3,2)          |
+---------------------+
| 3.4028236692094e+38 | 
+---------------------+
1 row in set (0.00 sec)

[18 Jan 2008 11:51] Alexey Kopytov

The results are inconsistent due to the intentional change in behavior introduced by the patch for bug #24912.

Since "a-3" has the unsigned integer type, it evaluates to 18446744073709551615, so 3.4028236692094e+38 is the expected result of pow(a-3, 2).

[19 Jan 2008 9:42] Valeriy Kravchuk

Manual (http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html) clearly says:

"If one of the arguments is an unsigned integer, and the other argument is also an integer, the result is an unsigned integer."

So, formally, it is NOT a bug. I'd prefer, though, to have this particular incompatible change (in GA!) of 5.0.42+ vs. older version to be explicitely documented in:

http://dev.mysql.com/doc/refman/5.0/en/releasenotes-es-5-0-42.html

[21 Jan 2008 12:21] Eduard Boer

When I have a tabel A_TABLE with an UNSIGNED TINYINT,
I can do the following:

select A,1-A,-A+1 FROM A_TABLE;

result is:
"10";"18446744073709551607";"-9"

So the work around is to use "-A+1" instead of "1-A" 

What I'd like to know is how other databases would handle this. Because this behavior of mysql was a complete suprise to me.