Weird result when grouping and self-joining a table

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?

It's because your price column is unsigned.

CASTing the values works ok:

SELECT
 ii.invoice_item_id,
 pii.invoice_item_id,
 ii.previous_invoice_item_id,
 cast(ii.price as signed) - cast(pii.price as signed ),
 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

StackExchange Database Administrators Q#151113, answer score: 4