Bug #6638 Binary operations produce incorrect resuls

• Submitted: 15 Nov 2004 15:56
• Modified: 16 Nov 2004 15:02
• Reporter: Szilard Dorant
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.7
• OS: Any (All)
• CPU Architecture: Any

[15 Nov 2004 15:56] Szilard Dorant

Description:
All binary operations fail when the result should be negative

Probably the results are treated us unsigned numbers before returning them to the user.

How to repeat:
mysql> select (-1 & -1);
+----------------------+
| (-1 & -1)            |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

(should be -1)

mysql> select (-1 | -1);
+----------------------+
| (-1 & -1)            |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

(should be -1)

mysql> select -1 ^ 0;
+----------------------+
| -1 ^ 0               |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

(should be -1)

mysql> select -1 << 1;
+----------------------+
| -1 << 1              |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.00 sec)

(should be -2)

mysql> select ~1;
+----------------------+
| ~1                   |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.00 sec)

(should be -2)

Older MySQL versions (e.g. 3.23.54) work fine, example:

mysql> select -1 & -1;
+---------+
| -1 & -1 |
+---------+
|      -1 |
+---------+
1 row in set (0.00 sec)

[15 Nov 2004 16:00] Szilard Dorant

The second example meant to use the OR operator:

mysql> select -1 | -1;
+----------------------+
| -1 | -1              |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

(should be -1)

[15 Nov 2004 16:33] Szilard Dorant

I have found in the documentation, that

"The result is an unsigned 64-bit integer."

Ok, so I suppose I should cast it to the signed version:

mysql> select CAST((-1 & -1) AS SIGNED);
+---------------------------+
| CAST((-1 & -1) AS SIGNED) |
+---------------------------+
|                        -1 |
+---------------------------+
1 row in set (0.00 sec)

This wors, but MySQL 3.x will not cast:

ERROR 1064: You have an error in your SQL syntax near '((-1 & -1) AS SIGNED)' at line 1

Is there any cross-version compatible solution for the problem ??

[15 Nov 2004 17:23] Szilard Dorant

Found a cross version solution for my special case:

Old expression:

(table.column & number) = (number)

New expression:

(table.column & number) = (number | 0)

This works even if "number"<0 , the "| 0" converts it to unsigned long in 4.x MySQL versions.