Description:
Subtraction of fields from two joined subqueries does not produce desired result. See example below. When Negative results are expected they are returned UNSIGNED. Same results using various clients.
How to repeat:
--
-- Table structure for table `Example`
--
CREATE TABLE `Example` (
`id1` int(10) unsigned NOT NULL,
`id2` int(10) unsigned NOT NULL,
`value` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `Example`
--
INSERT INTO `Example` VALUES (1, 1, 75);
INSERT INTO `Example` VALUES (1, 2, 100);
------------------------------------------------
mysql> SELECT * FROM Example;
+-----+-----+-------+
| id1 | id2 | value |
+-----+-----+-------+
| 1 | 1 | 75 |
| 1 | 2 | 100 |
+-----+-----+-------+
2 rows in set (0.00 sec)
mysql> SELECT
-> s1.value,
-> s2.value,
-> (s1.value - s2.value) as difference
-> FROM
-> (
-> select id1, value
-> FROM Example
-> WHERE id2=1
-> ) s1
-> LEFT JOIN
-> (
-> select id1, value
-> FROM Example
-> WHERE id2=2
-> ) s2
-> USING (id1)
-> ;
+-------+-------+----------------------+
| value | value | difference |
+-------+-------+----------------------+
| 75 | 100 | 18446744073709551591 |
+-------+-------+----------------------+
1 row in set (0.00 sec)
Suggested fix:
Workaround:
mysql> SELECT
-> s1.value,
-> s2.value,
-> CAST(s1.value - s2.value AS SIGNED)
-> FROM
-> (
-> select id1, value
-> FROM Example
-> WHERE id2=1
-> ) s1
-> LEFT JOIN
-> (
-> select id1, value
-> FROM Example
-> WHERE id2=2
-> ) s2
-> USING (id1)
-> ;
+-------+-------+-------------------------------------+
| value | value | CAST(s1.value - s2.value AS SIGNED) |
+-------+-------+-------------------------------------+
| 75 | 100 | -25 |
+-------+-------+-------------------------------------+
1 row in set (0.00 sec)
Description: Subtraction of fields from two joined subqueries does not produce desired result. See example below. When Negative results are expected they are returned UNSIGNED. Same results using various clients. How to repeat: -- -- Table structure for table `Example` -- CREATE TABLE `Example` ( `id1` int(10) unsigned NOT NULL, `id2` int(10) unsigned NOT NULL, `value` int(10) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `Example` -- INSERT INTO `Example` VALUES (1, 1, 75); INSERT INTO `Example` VALUES (1, 2, 100); ------------------------------------------------ mysql> SELECT * FROM Example; +-----+-----+-------+ | id1 | id2 | value | +-----+-----+-------+ | 1 | 1 | 75 | | 1 | 2 | 100 | +-----+-----+-------+ 2 rows in set (0.00 sec) mysql> SELECT -> s1.value, -> s2.value, -> (s1.value - s2.value) as difference -> FROM -> ( -> select id1, value -> FROM Example -> WHERE id2=1 -> ) s1 -> LEFT JOIN -> ( -> select id1, value -> FROM Example -> WHERE id2=2 -> ) s2 -> USING (id1) -> ; +-------+-------+----------------------+ | value | value | difference | +-------+-------+----------------------+ | 75 | 100 | 18446744073709551591 | +-------+-------+----------------------+ 1 row in set (0.00 sec) Suggested fix: Workaround: mysql> SELECT -> s1.value, -> s2.value, -> CAST(s1.value - s2.value AS SIGNED) -> FROM -> ( -> select id1, value -> FROM Example -> WHERE id2=1 -> ) s1 -> LEFT JOIN -> ( -> select id1, value -> FROM Example -> WHERE id2=2 -> ) s2 -> USING (id1) -> ; +-------+-------+-------------------------------------+ | value | value | CAST(s1.value - s2.value AS SIGNED) | +-------+-------+-------------------------------------+ | 75 | 100 | -25 | +-------+-------+-------------------------------------+ 1 row in set (0.00 sec)