MySQL Bugs: #9720: Problems with sum() and group by with Magic Development

Bug #9720	Problems with sum() and group by with Magic Development
Submitted:	7 Apr 2005 14:23	Modified:	30 May 2013 12:40
Reporter:	Stefano Bartaletti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.11	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
Hi,
we are facing a problem with Magic Development (www.magic-sw.com).

We created a program that is :
- querying an SQL table with sum() and group by statements
- checking the same SQL table with repeated single-record fetches
- querying again with the same sum() and group by as before
the second query with sum() has much less records than before, but the statement is exactly the same.

Moreover, in order to get again a good result we have to make a different operation on the same table (such as a 'select * fro table' statement), otherwise the 'group by' result is still wrong

We have tested it with an older mysql odbc version (3.51.06) and it works fine in this case, but it gives us different problems with transactions.

Some notes
- installed on a Slackware 9.1 server
- mysql server side version is 4.1.10a compiled from source, but the same problem was present with the previous server version shipped in binary format with Slackware (4.0.15a)
- access is done via a generic Magic ODBC internal gateway
- we can provide logs both on server and client side

How to repeat:
We can provide a complete application to replicate this error, difficulties may come as Magic must be a licensed product but we can find a time-limited version of Magic to let you replicate the tests in your lab

We are now trying a test environment using the same program and PostgreSQL, everything works fine

Hi,

Can you please provide us with a list of queries and the create statements of the tables that you used so that we can verify this bug ?

Thanks.

No problem, please send me an email address where I can send everything, or should I cut&paste here? I't a LOT of data

Hi!

Can you please add all the files to a zip/gzip file and upload it to this bug report under the "File" tab ?

Thanks a lot !!

Magic and MySQL logfiles compressed with WinZip 9

Attachment: log2.zip (application/x-zip-compressed, text), 148.10 KiB.

Here is the CREATE statement (note that the table was built using Magic, we did not write the CREATE statement):

CREATE TABLE `tbconmov` (
  `Deposito` char(1) NOT NULL default '',
  `Reparto` char(1) NOT NULL default '',
  `Classe` char(3) NOT NULL default '',
  `Tipo` char(3) NOT NULL default '',
  `Item` char(8) NOT NULL default '',
  `Movimento` char(1) NOT NULL default '',
  `Causale` char(3) NOT NULL default '',
  `Macchina` char(5) NOT NULL default '',
  `Passo` tinyint(4) NOT NULL default '0',
  `Bidone` tinyint(4) NOT NULL default '0',
  `Data` date default NULL,
  `Ora` int(11) default NULL,
  `Quantita` binary(15) NOT NULL default '',
  `PrezzoEUR` binary(12) NOT NULL default '',
  `PrezzoVAL` binary(12) NOT NULL default '',
  `Pesatura` char(1) NOT NULL default '',
  `Operatore` char(10) NOT NULL default '',
  `OrdineCotta` char(8) NOT NULL default '',
  `CentroDiCosto` char(4) NOT NULL default '',
  `DDTIngresso` char(20) NOT NULL default '',
  `DataDDT` date default NULL,
  `Verificato` tinyint(1) NOT NULL default '0',
  `Check` tinyint(1) NOT NULL default '0',
  `Filler` char(46) NOT NULL default '',
  UNIQUE KEY `per.Codice.Data.Deposito` (`Classe`,`Tipo`,`Item`,`Data`,`Ora`,`Deposito`,`Reparto`,`Movimento`,`CentroDiCosto`,`Macchina`,`Passo`),
  KEY `per.Codice.Tipo.Deposito` (`Deposito`,`Reparto`,`Classe`,`Tipo`,`Item`,`Movimento`,`Data`,`Ora`,`Macchina`,`Passo`),
  KEY `per.Tipo` (`Deposito`,`Classe`,`Tipo`,`Item`,`Movimento`,`Data`,`Ora`),
  KEY `per.Verifica` (`Deposito`,`Classe`,`Tipo`,`Item`,`Verificato`,`Data`),
  KEY `per.Data` (`Deposito`,`Data`,`Movimento`),
  KEY `per.Cotta` (`Deposito`,`OrdineCotta`,`Macchina`,`Passo`,`Bidone`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Group by() before the record-by-record fetch

Attachment: sql1.png (image/png, text), 12.60 KiB.

Same Group by() after

Attachment: sql2.png (image/png, text), 8.29 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Suspended? weird, I had submitted info some time ago

Oh well...

Re-opened.

I tried to repeat bug with my data (on my test case). All works fine. Could you store part of your data by SELECT ... INTO OUTFILE 'file_name' . I would like to test with your data.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.