Bug #10292 Duplicate entry error in SELECT

• Submitted: 1 May 2005 16:10
• Modified: 3 Jun 2005 12:19
• Reporter: [ name withheld ] (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.0.4 max nt
• OS: Windows (windows 2000 sp4)
• Assigned to: Evgeny Potemkin
• CPU Architecture: Any

[1 May 2005 16:10] [ name withheld ]

Description:
I'm running this selection query against the database reported in the "how to repeat" section and I get a duplicate key error:

select c.cap, sum(d_o.prezzounitario * d_o.quantit) as importo, p.nomeprodotto
from
clienti c inner join ordini o on c.idcliente = o.idcliente
inner join dettagli_ordini d_o on o.idordine = d_o.idordine
inner join prodotti p on d_o.idprodotto = p.idprodotto
group by c.cap, p.nomeprodotto
order by c.cap, p.nomeprodotto

ERROR 1062 (23000): Duplicate entry '05033-Chai' for key 1

Why would it mind a duplicate entry?

How to repeat:
Create the following database and tables:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `southwind`;
USE `southwind`;
CREATE TABLE `clienti` (
  `IDCliente` varchar(5) NOT NULL default '',
  `NomeSocietà ` varchar(40) default NULL,
  `Contatto` varchar(30) default NULL,
  `Posizione` varchar(30) default NULL,
  `Indirizzo` varchar(60) default NULL,
  `Città ` varchar(15) default NULL,
  `Zona` varchar(15) default NULL,
  `CAP` varchar(10) default NULL,
  `Paese` varchar(15) default NULL,
  `Telefono` varchar(24) default NULL,
  `Fax` varchar(24) default NULL,
  PRIMARY KEY  (`IDCliente`),
  KEY `CLIENTI_CAP` (`CAP`),
  KEY `CLIENTI_PAESE` (`Paese`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dettagli_ordini` (
  `IDOrdine` int(10) NOT NULL default '0',
  `IDProdotto` int(10) NOT NULL default '0',
  `PrezzoUnitario` decimal(19,4) default NULL,
  `Quantit` smallint(5) default NULL,
  `Sconto` double(7,0) default NULL,
  PRIMARY KEY  (`IDOrdine`,`IDProdotto`),
  KEY `FK_dettagli_ordini_ref_prodotti` (`IDProdotto`),
  CONSTRAINT `FK_dettagli_ordini_ref_ordini` FOREIGN KEY (`IDOrdine`) REFERENCES `ordini` (`IDOrdine`),
  CONSTRAINT `FK_dettagli_ordini_ref_prodotti` FOREIGN KEY (`IDProdotto`) REFERENCES `prodotti` (`IDProdotto`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ordini` (
  `IDOrdine` int(10) NOT NULL default '0',
  `IDCliente` varchar(5) NOT NULL default '',
  `IDImpiegato` int(10) NOT NULL default '0',
  `DataOrdine` date default NULL,
  `DataRichiesta` date default NULL,
  `DataSpedizione` date default NULL,
  `Corriere` int(10) default NULL,
  `Trasporto` decimal(19,4) default NULL,
  `Destinatario` varchar(40) default NULL,
  `IndirizzoDestinatario` varchar(60) default NULL,
  `CittDestinatario` varchar(15) default NULL,
  `ZonaDestinatario` varchar(15) default NULL,
  `CAPDestinatario` varchar(10) default NULL,
  `PaeseDestinatario` varchar(15) default NULL,
  PRIMARY KEY  (`IDOrdine`),
  KEY `FK_ordini_ref_clienti` (`IDCliente`),
  KEY `FK_ordini_ref_impiegati` (`IDImpiegato`),
  KEY `FK_ordini_ref_corrieri` (`Corriere`),
  CONSTRAINT `FK_ordini_ref_clienti` FOREIGN KEY (`IDCliente`) REFERENCES `clienti` (`IDCliente`),
  CONSTRAINT `FK_ordini_ref_corrieri` FOREIGN KEY (`Corriere`) REFERENCES `corrieri` (`IDCorriere`),
  CONSTRAINT `FK_ordini_ref_impiegati` FOREIGN KEY (`IDImpiegato`) REFERENCES `impiegati` (`IDImpiegato`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `prodotti` (
  `IDProdotto` int(10) NOT NULL default '0',
  `NomeProdotto` varchar(40) default NULL,
  `IDFornitore` int(10) NOT NULL default '0',
  `IDCategoria` int(10) NOT NULL default '0',
  `QuantitPerUnit` varchar(30) default NULL,
  `PrezzoUnitario` decimal(19,4) default NULL,
  `Scorte` smallint(5) default NULL,
  `QuantitOrdinata` smallint(5) default NULL,
  `LivelloDiRiordino` smallint(5) default NULL,
  `Sospeso` char(1) NOT NULL default '',
  PRIMARY KEY  (`IDProdotto`,`IDFornitore`,`IDCategoria`),
  KEY `FK_prodotti_ref_categorie` (`IDCategoria`),
  KEY `FK_prodotti_ref_fornitori` (`IDFornitore`),
  CONSTRAINT `FK_prodotti_ref_categorie` FOREIGN KEY (`IDCategoria`) REFERENCES `categorie` (`IDCategoria`),
  CONSTRAINT `FK_prodotti_ref_fornitori` FOREIGN KEY (`IDFornitore`) REFERENCES `fornitori` (`IDFornitore`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The database is running in:
sql-mode=ORACLE

Populate it with data (not included ....) and run the query reported above.

[2 May 2005 10:38] MySQL Verification Team

Could you upload tables to our ftp and let us know the file name?
ftp://ftp.mysql.com/pub/mysql/upload/

[2 May 2005 12:43] [ name withheld ]

Sorry for the previous note, I added the file in the "Files" section of this bugreport, it's name is "backup per bugreport.zip"

[3 May 2005 8:58] [ name withheld ]

Did you get the file?

[5 May 2005 8:43] [ name withheld ]

Any news???

[10 May 2005 15:36] MySQL Verification Team

Verified on Linux:

mysql> select c.cap, sum(d_o.prezzounitario * d_o.quantit) as importo, p.nomeprodotto
    -> from
    -> clienti c inner join ordini o on c.idcliente = o.idcliente
    -> inner join dettagli_ordini d_o on o.idordine = d_o.idordine
    -> inner join prodotti p on d_o.idprodotto = p.idprodotto
    -> group by c.cap, p.nomeprodotto
    -> order by c.cap, p.nomeprodotto;
ERROR 1062 (23000): Duplicate entry '05033-Chai' for key 1

[3 Jun 2005 10:01] [ name withheld ]

It seems corrected in mysql 5.0.6 max nt.
I think it can be closed.
Regards

[3 Jun 2005 12:19] MySQL Verification Team

Thank you for the feedback.