Bug #39376 Multiplying signed and unsigned integers

• Submitted: 10 Sep 2008 17:57
• Modified: 10 Sep 2008 18:06
• Reporter: aligi piccin
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.67
• OS: Windows
• CPU Architecture: Any
• Tags: multiply, signed, UNSIGNED

[10 Sep 2008 17:57] aligi piccin

Description:
A table: 

    col1 integer UNSIGNED
    col2 integer

If col2 has negative values, the expression (col1*col2) gives wrong result

Problem found in MySQL 5.0.25 and 5.1.22 too
Problem found with MyIsam engine too

How to repeat:
DROP TABLE IF EXISTS testable;
CREATE TABLE  testable (
  `col1` int(10) unsigned NOT NULL DEFAULT '0',
  `col2` int(11) DEFAULT '0',
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into testable(col1, col2) values(1,-1)

SELECT col1,col2,col1*col2 as qta FROM testable

You get : 1, -1, 18446744073709551615

[10 Sep 2008 18:06] Valeriy Kravchuk

Verified just as described:

mysql> DROP TABLE IF EXISTS testable;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CREATE TABLE  testable (
    ->   `col1` int(10) unsigned NOT NULL DEFAULT '0',
    ->   `col2` int(11) DEFAULT '0',
    ->   PRIMARY KEY (`col1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.94 sec)

mysql>
mysql> insert into testable(col1, col2) values(1,-1);
Query OK, 1 row affected (0.13 sec)

mysql> SELECT col1,col2,col1*col2 as qta FROM testable;
Field   1:  `col1`
Catalog:    `def`
Database:   `test`
Table:      `testable`
Org_table:  `testable`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED NUM PART_KEY

Field   2:  `col2`
Catalog:    `def`
Database:   `test`
Table:      `testable`
Org_table:  `testable`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NUM

Field   3:  `qta`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 20
Decimals:   0
Flags:      UNSIGNED BINARY NUM

+------+------+----------------------+
| col1 | col2 | qta                  |
+------+------+----------------------+
|    1 |   -1 | 18446744073709551615 |
+------+------+----------------------+
1 row in set (0.06 sec)

The result should NOT be unsigned. Workaround is:

SELECT col1,col2,cast(col1*col2 as signed) as qta FROM testable;

[10 Sep 2008 18:06] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

In the query `SELECT col1,col2,col1*col2 as qta FROM testable;` col2 cast as unsigned and then col1 and col2 multiply.

You should explicitly cast column 1 as signed to get meaningful results:

mysql>  SELECT col1,col2, cast(col1 as signed) * col2  as qta FROM testable;
+------+------+------+
| col1 | col2 | qta  |
+------+------+------+
|    1 |   -1 |   -1 | 
+------+------+------+
1 row in set (0.34 sec)