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

[7 Jun 2006 5:36] hari babu
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
[13 Jun 2006 12:06] Tonci Grgin
Hi Hari, thanks for your problem report. Can you please post exact connect string?
[14 Jun 2006 12:04] Tonci Grgin
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
[17 Oct 2007 13:43] Susanne Ebrecht
I fixed the version number at this bug report. This should be a MyODBC version 3.51 bug report.