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:Microsoft Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[7 Apr 2005 14:23] Stefano Bartaletti
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
[12 Apr 2005 14:42] Stefano Bartaletti
We are now trying a test environment using the same program and PostgreSQL, everything works fine
[13 Apr 2005 22:52] Jorge del Conde
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.
[14 Apr 2005 8:41] Stefano Bartaletti
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
[14 Apr 2005 14:10] Jorge del Conde
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 !!
[14 Apr 2005 15:39] Stefano Bartaletti
Magic and MySQL logfiles compressed with WinZip 9

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

[14 Apr 2005 15:40] Stefano Bartaletti
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;
[14 Apr 2005 15:43] Stefano Bartaletti
Group by() before the record-by-record fetch

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

[14 Apr 2005 15:43] Stefano Bartaletti
Same Group by() after

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

[14 May 2005 23:00] Bugs System
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".
[18 May 2005 10:12] Stefano Bartaletti
Suspended? weird, I had submitted info some time ago

Oh well...
[18 May 2005 12:35] Miguel Solorzano
Re-opened.
[19 May 2005 6:37] Vasily Kishkin
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.
[19 Jun 2005 23:00] Bugs System
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".
[30 May 2013 12:40] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.