Description:
On Linux Debian 10 / MySQL 8.0.21, GROUP BY WITH ROLLUP converts the aggregated field from non INT into INT.
In the test, I will aggregate a table on a DECIMAL field. GROUP BY will keep the field type as DECIMAL, GROUP BY WITH ROLLUP will convert it into an INT data type.
The test is carried out on MySQL 8.0.21 installed on 2 OS, Windows Server 2012 and Debian 10. Results on Windows are correct, and wrong on Linux Debian 10.
How to repeat:
CREATE TABLE IF NOT EXISTS `test` (
`ID` INT NOT NULL,
`price` DECIMAL(6,3) UNSIGNED NOT NULL,
`quantity` SMALLINT UNSIGNED NOT NULL,
`amount` DECIMAL(7,3) UNSIGNED NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `test` VALUES (1,1.50,3,4.50),(2,1.50,5,7.5),(3,1.73,3,5.19),(4,1.73,7,12.11),(5,1.47,6,8.82),(6,1.47,9,13.23);
2 queries for tests:
SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price`;
SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price` WITH ROLLUP;
Results on Windows (correct)
----------------------------
mysql> SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price`;
+-------+---------------+-------------+
| price | totalquantity | totalamount |
+-------+---------------+-------------+
| 1.500 | 8 | 12.000 |
| 1.730 | 10 | 17.300 |
| 1.470 | 15 | 22.050 |
+-------+---------------+-------------+
3 rows in set (0.00 sec)
mysql> SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price` WITH ROLLUP;
+-------+---------------+-------------+
| price | totalquantity | totalamount |
+-------+---------------+-------------+
| 1.470 | 15 | 22.050 | <- 'price' keeps its original data type
| 1.500 | 8 | 12.000 | <- 'price' keeps its original data type
| 1.730 | 10 | 17.300 | <- 'price' keeps its original data type
| NULL | 33 | 51.350 |
+-------+---------------+-------------+
4 rows in set (0.00 sec)
Results on Linux Debian 10 (wrong on WITH ROLLUP)
--------------------------
mysql> SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price`;
+-------+---------------+-------------+
| price | totalquantity | totalamount |
+-------+---------------+-------------+
| 1.500 | 8 | 12.000 |
| 1.730 | 10 | 17.300 |
| 1.470 | 15 | 22.050 |
+-------+---------------+-------------+
3 rows in set (0.00 sec)
mysql> SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price` WITH ROLLUP;
+-------+---------------+-------------+
| price | totalquantity | totalamount |
+-------+---------------+-------------+
| 1 | 15 | 22.050 | <- 'price' has now an INT field type
| 2 | 8 | 12.000 | <- 'price' has now an INT field type
| 2 | 10 | 17.300 | <- 'price' has now an INT field type
| NULL | 33 | 51.350 |
+-------+---------------+-------------+
4 rows in set (0.00 sec)
The aggregated field 'price' is now an INT field instead of a DECIMAL(6,3) field.
I've got the same issue with UNSIGNED INT as aggregated field, it is converted as well into INT.
Suggested fix:
Same behavior as on Windows, keep the aggregated field with its original definition.
I don't have workaround.
Description: On Linux Debian 10 / MySQL 8.0.21, GROUP BY WITH ROLLUP converts the aggregated field from non INT into INT. In the test, I will aggregate a table on a DECIMAL field. GROUP BY will keep the field type as DECIMAL, GROUP BY WITH ROLLUP will convert it into an INT data type. The test is carried out on MySQL 8.0.21 installed on 2 OS, Windows Server 2012 and Debian 10. Results on Windows are correct, and wrong on Linux Debian 10. How to repeat: CREATE TABLE IF NOT EXISTS `test` ( `ID` INT NOT NULL, `price` DECIMAL(6,3) UNSIGNED NOT NULL, `quantity` SMALLINT UNSIGNED NOT NULL, `amount` DECIMAL(7,3) UNSIGNED NOT NULL, PRIMARY KEY (`ID`)); INSERT INTO `test` VALUES (1,1.50,3,4.50),(2,1.50,5,7.5),(3,1.73,3,5.19),(4,1.73,7,12.11),(5,1.47,6,8.82),(6,1.47,9,13.23); 2 queries for tests: SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price`; SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price` WITH ROLLUP; Results on Windows (correct) ---------------------------- mysql> SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price`; +-------+---------------+-------------+ | price | totalquantity | totalamount | +-------+---------------+-------------+ | 1.500 | 8 | 12.000 | | 1.730 | 10 | 17.300 | | 1.470 | 15 | 22.050 | +-------+---------------+-------------+ 3 rows in set (0.00 sec) mysql> SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price` WITH ROLLUP; +-------+---------------+-------------+ | price | totalquantity | totalamount | +-------+---------------+-------------+ | 1.470 | 15 | 22.050 | <- 'price' keeps its original data type | 1.500 | 8 | 12.000 | <- 'price' keeps its original data type | 1.730 | 10 | 17.300 | <- 'price' keeps its original data type | NULL | 33 | 51.350 | +-------+---------------+-------------+ 4 rows in set (0.00 sec) Results on Linux Debian 10 (wrong on WITH ROLLUP) -------------------------- mysql> SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price`; +-------+---------------+-------------+ | price | totalquantity | totalamount | +-------+---------------+-------------+ | 1.500 | 8 | 12.000 | | 1.730 | 10 | 17.300 | | 1.470 | 15 | 22.050 | +-------+---------------+-------------+ 3 rows in set (0.00 sec) mysql> SELECT `price`,SUM(quantity) AS `totalquantity`,SUM(amount) AS `totalamount` FROM `tests`.`test` GROUP BY `price` WITH ROLLUP; +-------+---------------+-------------+ | price | totalquantity | totalamount | +-------+---------------+-------------+ | 1 | 15 | 22.050 | <- 'price' has now an INT field type | 2 | 8 | 12.000 | <- 'price' has now an INT field type | 2 | 10 | 17.300 | <- 'price' has now an INT field type | NULL | 33 | 51.350 | +-------+---------------+-------------+ 4 rows in set (0.00 sec) The aggregated field 'price' is now an INT field instead of a DECIMAL(6,3) field. I've got the same issue with UNSIGNED INT as aggregated field, it is converted as well into INT. Suggested fix: Same behavior as on Windows, keep the aggregated field with its original definition. I don't have workaround.