MySQL Bugs: #31493: Type Mismatch in ASP when using output from SUM in SQL statement

Bug #31493	Type Mismatch in ASP when using output from SUM in SQL statement
Submitted:	9 Oct 2007 21:47	Modified:	10 Oct 2007 5:59
Reporter:	Marc Dixon	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (2000 Server SP4, XP, Vista)
Assigned to:		CPU Architecture:	Any
Tags:	avg, sum, type mismatch

Description:
Database Server OS : Linux Fedora Core 6
Database Server MySQL : v5.0.45
Web Server OS : Windows 2000 Server
ODBC Connector : v3.51.21.00

ODBC Connector has the following options ticked in the Advanced Tab :
* Return Matching Rows
* Change BIGINT Columns to Int

We have just upgraded our database servers from v4.1.18 to v5.0.45.  It should be noted that I have also noticed this behaviour on an older v5.0.18 database.

Everything seems to be going fine except for any code that processes the output of SUM in an SQL statement in our ASP web pages or any VBscript code.

Example SQL that fails :
  SELECT SUM(Counter) AS Total FROM Test.TestError

When we try to process the output of the "Total" field from the recordset we get a VBscript "Type mismatch - 800A000D" error.  I believe this is because the data type that is being returned is not numeric.

How to repeat:
Lets say we have created the following table :

CREATE TABLE  `Test`.`TestError` (
  `Counter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Counter`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

With the following data :
|---------|
| Counter |
|---------|
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
-----------

I then have this sample vbscript code written to demonstrate the fault :

Sub Test()
Dim Conn
Dim RS
Dim strSQL
Const ODBCName = "DBWrite"   ' ODBC DSN Name
   Set Conn = CreateObject("ADODB.Connection")
   Set RS = CreateObject("ADODB.Recordset")
   Conn.Open ODBCName
   strSQL = "SELECT SUM(Counter) AS Total FROM Test.TestError"
   Set RS = Conn.Execute(strSQL)
	
   ' The line below works fine and returns "15"
   MsgBox "Total = " & RS.Fields.Item("Total").value
	
   ' The line below does not work when I try to add 1 to the result.
   ' Gives error "Type mismatch - 800A000D"
   MsgBox "Total + 1 = " & RS.Fields.Item("Total").value + 1
   
   If Not RS Is Nothing Then
      RS.Close
      Set RS = Nothing
   End If
   If Not Conn Is Nothing Then
      Conn.Close
      Set Conn = Nothing
   End If
End Sub

Suggested fix:
I have found that by casting the field in the query I seem to obtain a usable result.  However, I do not believe that this should be necessary as it has not been necessary before and I have hundreds of SQL statements that I would have to update.

Example:

Original SQL :
SELECT SUM(Counter) AS Total FROM Test.TestError

SQL that works :
SELECT CAST(SUM(Counter) AS SIGNED) AS Total FROM Test.TestError

Changing category for Connector/ODBC since within the mysql client
not happen the behavior reported.

c:\dev\5.0>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.52-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE  `Test`.`TestError` (
    ->   `Counter` int(11) NOT NULL default '0',
    ->   PRIMARY KEY  (`Counter`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into TestError values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT SUM(Counter) AS Total FROM Test.TestError;
+-------+
| Total |
+-------+
|    15 |
+-------+
1 row in set (0.00 sec)

The issue only seems to happen when I try to do something with the results of the query within ASP or a vbscript using the ODBC driver.

As stated previously :

' The line below works fine and returns "15"
   MsgBox "Total = " & RS.Fields.Item("Total").value
	
   ' The line below does not work when I try to add 1 to the result.
   ' Gives error "Type mismatch - 800A000D"
   MsgBox "Total + 1 = " & RS.Fields.Item("Total").value + 1

I believe that the result of the SUM is not being passed back in the recordset via the ODBC driver as a numerical type.

Something else that I have just noted.

If I run the following VBscript command :
    VarType(RS.Fields.Item("Total").value

I receive the following results for the different versions of MySQL :
   v4.1.18 = 5   Which I believe is a Double
   v5.0.45 = 14  ???  I'm not sure what this is

Does this help to shed any light on the solution?  Is 14 a new data type that is incompatible with VBScript?  If so, is there a work around that you know of?

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Explanation: Hi Marc and thanks for your report. This is a duplicate of Bug#18157 (snip from there):
--<cut>--
"Microsoft VBScript runtime error '800a000d'
Type mismatch: ...
	
Cause: You are trying to convert a non-numeric variable into numeric.
Solution: Make sure the variable is numeric.
Use If IsNumeric( ) function to validate first.

We can test this easily, just put '&' instead of '+' in your formula and the error goes away. Now what we can do is rewrite formula to get numeric value

I have just upgraded from 4.1 to 5.1, and this error is still not fixed in the latest ODBC driver (3.51.27)

I have a SQL statement

  SELECT Sum(Booking_Antal) AS CurBookings FROM Booking

Which I then assign a variable in ASP with (DataIn is my recordset)

  CurBookings = DataIn("CurBookings")

Before the upgrade, this worked as it should, but now, when I try to do math on the assigned variable I get a "Microsoft VBScript runtime  error '800a000d'" error.

If I force a conversion, with

  CurBookings = CLng(DataIn("CurBookings"))

It works as it should.

Also, if I try and do a VarType on the variable, I get the variable to be of type 14, which according to the following webpage (which I trust very much), doesn't even exist

http://www.w3schools.com/vbScript/func_vartype.asp

This bug is stated as a duplicate of a bug that has been fixed, but, seeing that this bug is still very much alive in the latest ODBC, using the latest MySQL, i'd say that this is most definately not fixed.