Description:
In MYSQL 5.7.29, I have an issue with a result of my query.
I have 2 tables lignes (6 267 130 rows) and ventes (2 068 872 rows), corresponding to my sales (ventes) and the details of them (lignes).
When I do my query, the results are incorrect in 5.7.29.
I changed my query to create a temporary table and when I did the "sum" and the "group by" on this temporary table I didn't have any problems.
When I did the same test on MYSQL 8.0.19 and I didn't have any problems.
How to repeat:
Download the files (ventes.txt and lignes.txt) in 2 tables you have created before.
Here are the queries to create the tables :
CREATE TABLE "lignes" ( "vente" int DEFAULT NULL, "CodeMag" char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '', "famille" char(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '', "stockID" int DEFAULT NULL, "quantite" decimal(10,3) DEFAULT '0.000', "barcode" char(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '', KEY "ParStockID" ("stockID"), KEY "ParVente" ("vente"), KEY "ParCodeMag" ("CodeMag"), KEY "Parfamille" ("famille")) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin ;
CREATE TABLE "ventes" ( "vente" int NOT NULL DEFAULT '0', "nature" char(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT 'VENTE', KEY "ParNature" ("nature"), KEY "ParVente" ("vente")) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
And launch the query :
create temporary table query1
select lignes.StockID, sum(lignes.Quantite) as SumQte, lignes.barcode
from lignes join ventes on (lignes.vente = ventes.vente)
where (lignes.famille not like '~%')
And ((ventes.nature in ('VENTE','AVOIR','REGUL', 'RESA', 'MOUVEMENT'))
or (ventes.nature like '-> %'))
And (lignes.CodeMag = 'MERCI111')
group by StockID ;
Select *
from query1
Where barcode = 'H19127003'
Here is the comparaison of the results and the result I expected is under the column Result OK :
StockID Result OK Result not OK
1574088 4 4
2244892 2 2
2637984 3 2
Description: In MYSQL 5.7.29, I have an issue with a result of my query. I have 2 tables lignes (6 267 130 rows) and ventes (2 068 872 rows), corresponding to my sales (ventes) and the details of them (lignes). When I do my query, the results are incorrect in 5.7.29. I changed my query to create a temporary table and when I did the "sum" and the "group by" on this temporary table I didn't have any problems. When I did the same test on MYSQL 8.0.19 and I didn't have any problems. How to repeat: Download the files (ventes.txt and lignes.txt) in 2 tables you have created before. Here are the queries to create the tables : CREATE TABLE "lignes" ( "vente" int DEFAULT NULL, "CodeMag" char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '', "famille" char(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '', "stockID" int DEFAULT NULL, "quantite" decimal(10,3) DEFAULT '0.000', "barcode" char(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '', KEY "ParStockID" ("stockID"), KEY "ParVente" ("vente"), KEY "ParCodeMag" ("CodeMag"), KEY "Parfamille" ("famille")) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin ; CREATE TABLE "ventes" ( "vente" int NOT NULL DEFAULT '0', "nature" char(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT 'VENTE', KEY "ParNature" ("nature"), KEY "ParVente" ("vente")) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin; And launch the query : create temporary table query1 select lignes.StockID, sum(lignes.Quantite) as SumQte, lignes.barcode from lignes join ventes on (lignes.vente = ventes.vente) where (lignes.famille not like '~%') And ((ventes.nature in ('VENTE','AVOIR','REGUL', 'RESA', 'MOUVEMENT')) or (ventes.nature like '-> %')) And (lignes.CodeMag = 'MERCI111') group by StockID ; Select * from query1 Where barcode = 'H19127003' Here is the comparaison of the results and the result I expected is under the column Result OK : StockID Result OK Result not OK 1574088 4 4 2244892 2 2 2637984 3 2