MySQL Bugs: #18936: Incorrect summarize with "WITH ROLLUP" option

Bug #18936	Incorrect summarize with "WITH ROLLUP" option
Submitted:	10 Apr 2006 9:07	Modified:	18 Sep 2007 15:55
Reporter:	Danilo Paliani	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.10-BK, 5.1.7-beta	OS:	Linux (Linux, win32)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
Using "with rollup" clause in a select statement when are involved summarization and comparison function on positive and negative float values lead to non accurate results.

How to repeat:
CREATE DATABASE car;

USE car;

CREATE TABLE vendite (
  ID int(10) unsigned NOT NULL auto_increment,
  ID_Clienti mediumint(8) unsigned default '0',
  ID_DettaglioAcquisti int(10) unsigned default '0',
  ID_CodiciMerciAppoggio varchar(5) default NULL,
  ID_AliquoteIva tinyint(3) unsigned default '0',
  ID_FattureVendita mediumint(8) unsigned default '0',
  ID_UnitaMisura tinyint(3) unsigned default NULL,
  Numero_Colli smallint(5) unsigned default '0',
  Peso_Lordo float(8,1) default '0.0',
  Tara_Unitaria float(5,2) default '0.00',
  Prezzo float(6,3) default '0.000',
  Data_Immissione date default NULL,
  Ora_Immissione time default NULL,
  Record_Sospeso tinyint(3) unsigned default '0',
  Lotto_Vendita varchar(50) default NULL,
  Record_Prenotato tinyint(3) unsigned default '0',
  PRIMARY KEY  (ID),
  KEY ID_Clienti (ID_Clienti),
  KEY ID_DettagllioAcquisti (ID_DettaglioAcquisti),
  KEY ID_CodiciMerciAppoggio (ID_CodiciMerciAppoggio),
  KEY ID_AliquoteIva (ID_AliquoteIva),
  KEY ID_FattureVendita (ID_FattureVendita),
  KEY ID_UnitaMisura (ID_UnitaMisura)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE aliquoteiva (
  ID tinyint(3) unsigned NOT NULL auto_increment,
  Percentuale tinyint(3) unsigned default '0',
  PRIMARY KEY  (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE fatturevendita (
  ID mediumint(8) unsigned NOT NULL auto_increment,
  Numero_Fattura mediumint(8) unsigned NOT NULL default '0',
  ID_Clienti mediumint(8) unsigned default '0',
  Documento_Annullato tinyint(1) unsigned default '0',
  Data_Emissione date NOT NULL default '0000-00-00',
  Ora_Emissione time default NULL,
  Credito tinyint(3) unsigned default '0',
  Esportata tinyint(3) unsigned default '0',
  ID_SaldoFatture mediumint(8) unsigned default '0',
  ID_FattureVendita_NotaCredito mediumint(8) unsigned default '0',
  ID_Venditori smallint(5) unsigned default '1',
  PRIMARY KEY  (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO aliquoteiva (ID, Percentuale) VALUES (NULL, 4);

INSERT INTO vendite (ID, ID_Clienti, ID_DettaglioAcquisti, ID_CodiciMerciAppoggio, ID_AliquoteIva, ID_FattureVendita, ID_UnitaMisura, Numero_Colli, Peso_Lordo, Tara_Unitaria, Prezzo, Data_Immissione, Ora_Immissione, Record_Sospeso, Lotto_Vendita, Record_Prenotato) VALUES (NULL, 99, 0, 'AA', 1, 1, 1, 100, 1234, 0.55, 1.368, '2006-04-10', NULL, 0, NULL, 0);
INSERT INTO vendite (ID, ID_Clienti, ID_DettaglioAcquisti, ID_CodiciMerciAppoggio, ID_AliquoteIva, ID_FattureVendita, ID_UnitaMisura, Numero_Colli, Peso_Lordo, Tara_Unitaria, Prezzo, Data_Immissione, Ora_Immissione, Record_Sospeso, Lotto_Vendita, Record_Prenotato) VALUES (NULL, 99, 0, 'AB', 1, 1, 1, 100, 4321, 0.35, 1.746, '2006-04-10', NULL, 0, NULL, 0);
INSERT INTO vendite (ID, ID_Clienti, ID_DettaglioAcquisti, ID_CodiciMerciAppoggio, ID_AliquoteIva, ID_FattureVendita, ID_UnitaMisura, Numero_Colli, Peso_Lordo, Tara_Unitaria, Prezzo, Data_Immissione, Ora_Immissione, Record_Sospeso, Lotto_Vendita, Record_Prenotato) VALUES (NULL, 99, 0, 'AC', 1, 1, 1, 100, 3241, 0.43, 1.345, '2006-04-10', NULL, 0, NULL, 0);
INSERT INTO vendite (ID, ID_Clienti, ID_DettaglioAcquisti, ID_CodiciMerciAppoggio, ID_AliquoteIva, ID_FattureVendita, ID_UnitaMisura, Numero_Colli, Peso_Lordo, Tara_Unitaria, Prezzo, Data_Immissione, Ora_Immissione, Record_Sospeso, Lotto_Vendita, Record_Prenotato) VALUES (NULL, 99, 0, 'AA', 1, 2, 2, 0, 1234, 0, -0.345, '2006-04-10', NULL, 0, NULL, 0);

INSERT INTO fatturevendita (ID, Numero_Fattura, ID_Clienti, Documento_Annullato, Data_Emissione, Ora_Emissione, Credito, Esportata, ID_SaldoFatture, ID_FattureVendita_NotaCredito, ID_Venditori) VALUES (NULL, 1000, 99, 0, '2006-04-10', NULL, 0, 0, 0, 2, 1);
INSERT INTO fatturevendita (ID, Numero_Fattura, ID_Clienti, Documento_Annullato, Data_Emissione, Ora_Emissione, Credito, Esportata, ID_SaldoFatture, ID_FattureVendita_NotaCredito, ID_Venditori) VALUES (NULL, 1001, 99, 0, '2006-04-10', NULL, 0, 0, 0, 2, 1);

==========================================

SELECT aliquoteiva.Percentuale,
  IF(vendite.Prezzo > 0,
	    SUM(TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))+0.0051,2)),
			SUM(TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))-0.0051,2))
		)	AS Tot_Imponibile,
  IF(vendite.Prezzo > 0,
	SUM(TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))+0.0051,2))*(aliquoteiva.Percentuale/100))+0.0051,2)),
	SUM(TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))-0.0051,2))*(aliquoteiva.Percentuale/100))-0.0051,2))
		)	AS Tot_IVA,
  IF(vendite.Prezzo > 0,
	SUM(TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))+0.0051,2))*(1+(aliquoteiva.Percentuale/100)))+0.0051,2)), 
	SUM(TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))-0.0051,2))*(1+(aliquoteiva.Percentuale/100)))-0.0051,2))
		)	AS Tot_Importo
	FROM vendite
	LEFT JOIN aliquoteiva ON aliquoteiva.ID = vendite.ID_AliquoteIva
	LEFT JOIN fatturevendita ON fatturevendita.ID = vendite.ID_FattureVendita
	WHERE fatturevendita.Data_Emissione >= '20060410'
	AND fatturevendita.Data_Emissione <= '20060410'
	GROUP BY vendite.ID WITH ROLLUP ;

Suggested fix:
Using this select instead of the previous work for me:

SELECT aliquoteiva.Percentuale,
	SUM(TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))+(SIGN(vendite.Prezzo)*0.0051),2)) AS Tot_Imponibile,
	SUM(TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))+(SIGN(vendite.Prezzo)*0.0051),2))*(aliquoteiva.Percentuale/100))+(SIGN(vendite.Prezzo)*0.0051),2)) AS Tot_IVA,
	SUM(TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))+(SIGN(vendite.Prezzo)*0.0051),2))*(1+(aliquoteiva.Percentuale/100)))+(SIGN(vendite.Prezzo)*0.0051),2)) AS Tot_Importo
	FROM vendite
	LEFT JOIN aliquoteiva ON aliquoteiva.ID = vendite.ID_AliquoteIva
	LEFT JOIN fatturevendita ON fatturevendita.ID = vendite.ID_FattureVendita
	WHERE fatturevendita.Data_Emissione >= '20060410'
	AND fatturevendita.Data_Emissione <= '20060410'
	GROUP BY vendite.ID WITH ROLLUP ;

Reproduncing bug commands

Attachment: sql51_bugtest.txt (text/plain), 6.28 KiB.

Thank you for a problem report. I've got the following results for the first query of your test case:

+-------------+----------------+---------+-------------+
| Percentuale | Tot_Imponibile | Tot_IVA | Tot_Importo |
+-------------+----------------+---------+-------------+
| 4           | 1612.87        | 64.51   | 1677.38     |
| 4           | 7483.36        | 299.33  | 7782.69     |
| 4           | 4301.31        | 172.05  | 4473.36     |
| 4           | -425.73        | -17.03  | -442.76     |
| 4           | 12971.78       | 518.83  | 13490.61    |
+-------------+----------------+---------+-------------+
5 rows in set (0.07 sec)

and for the second:

+-------------+----------------+---------+-------------+
| Percentuale | Tot_Imponibile | Tot_IVA | Tot_Importo |
+-------------+----------------+---------+-------------+
| 4           | 1612.87        | 64.51   | 1677.38     |
| 4           | 7483.36        | 299.33  | 7782.69     |
| 4           | 4301.31        | 172.05  | 4473.36     |
| 4           | -425.73        | -17.03  | -442.76     |
| 4           | 12971.81       | 518.86  | 13490.67    |
+-------------+----------------+---------+-------------+
5 rows in set (0.01 sec)

on 5.1.10-BK (ChangeSet@1.2303.1.1, 2006-04-09 19:43:36-07:00) on Linux. So, they seems the same for me. Please, specify what did you expect to get and what is the indication of bug?

Hi and tx for reply.
The totals in the last row of the first query is wrong.
The totals in the last row of the second query is right.

# streamlined test-case:

DROP DATABASE IF EXISTS car;
CREATE DATABASE car;
USE car;

CREATE TABLE vendite ( ID int(10) unsigned NOT NULL auto_increment, Prezzo float(6,3) default '0.000', PRIMARY KEY(ID)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO vendite VALUES (NULL, 1.368), (NULL, 1.746), (NULL, 1.345), (NULL, -0.345);

SELECT vendite.ID,
  SUM(vendite.Prezzo) AS orig,
  SUM(TRUNCATE(vendite.Prezzo,4)) AS trunc4,
  IF(vendite.Prezzo > 0,
            SUM(vendite.Prezzo + 0.0051),
            SUM(vendite.Prezzo - 0.0051)) AS with_if,
  SUM(vendite.Prezzo + SIGN(vendite.Prezzo) * 0.0051) AS with_sign,
  SUM(IF(vendite.Prezzo > 0, vendite.Prezzo + 0.0051,
                             vendite.Prezzo - 0.0051)) AS fixed_if
	FROM vendite
	GROUP BY vendite.ID WITH ROLLUP ;

# with_if   = orig + 4 * 0.0051  (4 == n)
# with_sign = orig + 2 * 0.0051  (2 == +1, +1, -1, +1)
# fixed_if  = orig + 2 * 0.0051, IF() belongs within SUM(), not without!

# ID     orig     trunc4   with_if   with_sign   fixed_if
# 1      1.368    1.3680   1.3731    1.3731      1.3731
# 2      1.746    1.7460   1.7511    1.7511      1.7511
# 3      1.345    1.3450   1.3501    1.3501      1.3501
# 4     -0.345   -0.3449  -0.3501   -0.3501     -0.3501
# NULL   4.114    4.1141   4.0936    4.1242      4.1242

DROP DATABASE car;

# this in turn implies the following query for the original case:

SELECT aliquoteiva.Percentuale,
  SUM(IF(vendite.Prezzo > 0,
         TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))+0.0051,2),
         TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))-0.0051,2))
     ) AS Tot_Imponibile,

  SUM(IF(vendite.Prezzo > 0,
         TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))+0.0051,2))*(aliquoteiva.Percentuale/100))+0.0051,2),
         TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))-0.0051,2))*(aliquoteiva.Percentuale/100))-0.0051,2))
     ) AS Tot_IVA,

  SUM(IF(vendite.Prezzo > 0,
         TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))+0.0051,2))*(1+(aliquoteiva.Percentuale/100)))+0.0051,2), 
	 TRUNCATE(((TRUNCATE((vendite.Prezzo*(vendite.Peso_Lordo-(vendite.Numero_Colli*vendite.Tara_Unitaria)))-0.0051,2))*(1+(aliquoteiva.Percentuale/100)))-0.0051,2))
     ) AS Tot_Importo
	FROM vendite
	LEFT JOIN aliquoteiva ON aliquoteiva.ID = vendite.ID_AliquoteIva
	LEFT JOIN fatturevendita ON fatturevendita.ID = vendite.ID_FattureVendita
	WHERE fatturevendita.Data_Emissione >= '20060410'
	AND fatturevendita.Data_Emissione <= '20060410'
	GROUP BY vendite.ID WITH ROLLUP ;