MySQL Bugs: #20308: ExecuteReader returns unknown sql type when sum() is used in select

Bug #20308	ExecuteReader returns unknown sql type when sum() is used in select
Submitted:	7 Jun 2006 5:36	Modified:	14 Jun 2006 12:04
Reporter:	hari babu	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51	OS:	Windows (XP / Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
When more then one sum() is used in Select statement, ExecuteReader throws error "Unknown SQL Type - 0". Randomly sometimes it returns Int64 even in such case it fails for subsequent sum() function.

Example :
Dim mycon As System.Data.Odbc.OdbcConnection
Dim mycmd As System.Data.Odbc.OdbcCommand
Dim mydr As System.Data.Odbc.OdbcDataReader
mycon = New System.Data.Odbc.OdbcConnection(System.Configuration.ConfigurationSettings.AppSettings("Active_DB").ToString)
mycon.Open()
mycmd = New System.Data.Odbc.OdbcCommand("Select sum(NewCalls) as a, sum(RetryCalls) as b from rp_dly_calls ", mycon)
mydr = mycmd.ExecuteReader()
If mydr.HasRows Then
mydr.Read()
Messagebox.show(System.Convert.ToString(mydr.getInt32(mydr.getOrdinal("a"))))
Messagebox.show(System.Convert.ToString(mydr.getInt32(mydr.getOrdinal("c"))))
End If

Even if the first MessageBox shows a proper value, an error occurs before the second MessageBox                 

How to repeat:
Table Name : rp_dly_calls
Engine : MyISAM
Fields :-
NewCalls type integer (4) not null default value : 0000
RetryCalls type integer (4) not null default value : 0000

The table had one record with 0 in all the fields.

The code is as follows :

Dim mycon As System.Data.Odbc.OdbcConnection
Dim mycmd As System.Data.Odbc.OdbcCommand
Dim mydr As System.Data.Odbc.OdbcDataReader
mycon = New System.Data.Odbc.OdbcConnection(System.Configuration.ConfigurationSettings.AppSettings("Active_DB").ToString)
mycon.Open()
mycmd = New System.Data.Odbc.OdbcCommand("Select sum(NewCalls) as a, sum(RetryCalls) as b from rp_dly_calls ", mycon)
mydr = mycmd.ExecuteReader()
If mydr.HasRows Then
mydr.Read()
Messagebox.show(System.Convert.ToString(mydr.getInt32(mydr.getOrdinal("a"))))
Messagebox.show(System.Convert.ToString(mydr.getInt32(mydr.getOrdinal("c"))))
End If

Even if the first MessageBox shows a proper value, an error occurs for the second MessageBox

Hi Hari, thanks for your problem report. Can you please post exact connect string?

Hari, since you provided no actual data for this test, I used my own:
CREATE TABLE `tblTransactions` (
  `iTransactionId` int(11) NOT NULL auto_increment,
  `iUserId` int(11) default NULL,
  `iAmount` int(11) NOT NULL,
  `vFirstName` varchar(255) default NULL,
  `vLastName` varchar(255) default NULL,
  `cCountryCode` char(2) default NULL,
  `vAddress` varchar(255) default NULL,
  `vCity` varchar(255) default NULL,
  `vZipCode` varchar(255) default NULL,
  `vEmail` varchar(255) default NULL,
  `vReferenceNr` varchar(255) default NULL,
  `vSecondaryReferenceNr` varchar(255) default NULL,
  `iIPAddressUser` int(11) unsigned NOT NULL,
  `dTime` datetime NOT NULL,
  `iNumberOfAttempts` int(11) NOT NULL default '1',
  `eTransactionState` enum('PENDING','REDIRECTED','ADDED','FINISHED','FAILED') NOT NULL default 'PENDING',
  `vTransactionResultCode` varchar(255) default NULL,
  `dRecurringNextDate` date default NULL,
  `eRecurringInterval` enum('NONE','WEEK','TWO_WEEKS','MONTH','QUARTER','YEAR','ENDED') default 'NONE',
  `iCreditCardType` int(11) default NULL,
  `iRecieptMailSent` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`iTransactionId`),
  KEY `id_date_ind` (`iTransactionId`,`dTime`),
  KEY `userid_date_ind` (`iUserId`,`dTime`),
  KEY `userid_ind` (`iUserId`),
  KEY `ref_nr` (`vReferenceNr`)
) ENGINE=MyISAM AUTO_INCREMENT=1002 DEFAULT CHARSET=latin1;

Test case works as expected, showing both sums:
        Dim conn As System.Data.Odbc.OdbcConnection
        Dim mydr As System.Data.Odbc.OdbcDataReader
        Dim mycmd As System.Data.Odbc.OdbcCommand
        conn = New System.Data.Odbc.OdbcConnection()
        With conn
            .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                               "SERVER=munja;" & _
                               "PORT=3307;" & _
                               "DATABASE=test;" & _
                               "USER=root;" & _
                               "PASSWORD=;" & _
                               "OPTION= 1 + 2 + 3 + 8 + 32 + 2048 + 16384"
            .Open()
            If .State = 1 Then
                mycmd = New System.Data.Odbc.OdbcCommand("SELECT SUM(t.iUserID) AS a, SUM(t.iAmount) AS c FROM tblTransactions AS t", conn)
                mydr = mycmd.ExecuteReader()
                If mydr.HasRows Then
                    mydr.Read()
MsgBox(System.Convert.ToString(mydr.GetInt32(mydr.GetOrdinal("a"))))
MsgBox(System.Convert.ToString(mydr.GetInt32(mydr.GetOrdinal("c"))))
                End If
                mydr.Close()
            End If
            .Close()
        End With

MySQL server 5.0.23-debug on Suse 10
WinXP Pro SP2
NET framework 2.0.50727
ADO 2.0

I fixed the version number at this bug report. This should be a MyODBC version 3.51 bug report.