I'm on MySQL 5.5.49, the results of the following query seem wrong to me.
Test data :
CREATE TABLE `test` (
`invoice_item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) unsigned NOT NULL,
`previous_invoice_item_id` int(10) unsigned DEFAULT NULL,
`price` decimal(12,4) unsigned NOT NULL,
PRIMARY KEY (`invoice_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test (invoice_id, previous_invoice_item_id, price)
VALUES (1, NULL, 1), (1, 1, 0);
Query :
SELECT
ii.invoice_item_id,
pii.invoice_item_id,
ii.previous_invoice_item_id,
ii.price - pii.price,
ii.price,
pii.price
FROM test ii
JOIN test pii ON pii.invoice_item_id = ii.previous_invoice_item_id
GROUP BY ii.invoice_item_id;
The problem is that ii.price - pii.price
returns 0
when it should return -1
. If I remove the GROUP BY
clause, the result is correct. If the difference is positive, the result is also correct.
I'm at a loss to explain the result, can someone enlighten me?
Best Answer
It's because your
price
column isunsigned
.CASTing the values works ok: