MySQL Bugs: #97191: Prepared Statements when using ODBC / ClassicASP always return 0 for INT values

Bug #97191	Prepared Statements when using ODBC / ClassicASP always return 0 for INT values
Submitted:	10 Oct 2019 22:10	Modified:	16 Dec 2019 22:59
Reporter:	Mark Taylor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.17	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
Related to: https://forums.mysql.com/read.php?37,678406,678507#msg-678507

When running a SELECT prepared statement using Classic ASP/VBScript and ODBC (I tested both version 8.0.17 & 5.3.13, 32-bit/64-bit) any column that is an INT datatype always returns a value of 0.

The issue only seems to appear (at least in my testing) when a column with the LONGTEXT datatype is part of the query. It also appears to only manifest when there is a WHERE condition on the select.

How to repeat:
CREATE TABLE `testDB`.`test` (
  `idtest` int(11) NOT NULL AUTO_INCREMENT,
  `txtStr` varchar(45) DEFAULT NULL,
  `intVal` int(11) DEFAULT NULL,
  `longText` longtext,
  PRIMARY KEY (`idtest`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `testDB`.`test` (`txtStr`, `intVal`) VALUES ('Test1', '10');
INSERT INTO `testDB`.`test` (`txtStr`, `intVal`) VALUES ('Test2', '20');
INSERT INTO `testDB`.`test` (`txtStr`, `intVal`) VALUES ('Test3', '30');
INSERT INTO `testDB`.`test` (`txtStr`, `intVal`) VALUES ('Test4', '40');

Running on Server 2019 / IIS 10 / ClassicASP (VBScript):
<%
SET connTemp = Server.CreateObject("ADODB.Connection")
connTemp.Open "Provider=MSDASQL;Driver={MySQL ODBC 8.0 UNICODE Driver};Server=localhost;User=*****;Password=*****;Database=testDB;Option=3;Pooling=True;"

'QUERY 1
'====================================================
	WITH Server.CreateObject("ADODB.Command")
		.ActiveConnection = connTemp : .Prepared = TRUE : .CommandType = ad_CmdText : .CommandTimeout = 30					
		.CommandText = "SELECT * FROM `testDB`.`test`;"
		SET rsTemp = .Execute
			DO WHILE NOT rsTemp.EOF 								
				Response.write "Txt Str: " & rsTemp("txtStr") & "<br>"
				Response.write "Int Val: " & rsTemp("intVal") & "<br><br>"
				rsTemp.MoveNext
			LOOP
		SET rsTemp = NOTHING
	END WITH
' QUERY 1 RESULT (in html)
'	Txt Str: Test1
'	Int Val: 10
'	
'	Txt Str: Test2
'	Int Val: 20
'	
'	Txt Str: Test3
'	Int Val: 30
'	
'	Txt Str: Test4
'	Int Val: 40

' QUERY 2
'====================================================

	WITH Server.CreateObject("ADODB.Command")
		.ActiveConnection = connTemp : .Prepared = TRUE : .CommandType = ad_CmdText : .CommandTimeout = 30					
		.CommandText = "SELECT * FROM `testDB`.`test` WHERE idTest=? LIMIT 1;"
		.Parameters.Append( .CreateParameter("@idTest", ad_Integer, ad_ParamInput, , "2" ) )

		SET rsTemp = .Execute
			DO WHILE NOT rsTemp.EOF 								
				Response.write "Txt Str: " & rsTemp("txtStr") & "<br>"
				Response.write "Int Val: " & rsTemp("intVal") & "<br><br>"
				rsTemp.MoveNext
			LOOP
		SET rsTemp = NOTHING
	END WITH

' QUERY 2 RESULT (in html)
'=============================
'	Txt Str: Test2
'	Int Val: 0	

' QUERY 2 EXPECTED RESULT (in html)
'=============================
'	Txt Str: Test2
'	Int Val: 20	

' QUERY 3 (SELECT COLUMNS NOT INCLUDING LONGTEXT DATATYPE COLUMN)
'====================================================
	WITH Server.CreateObject("ADODB.Command")
		.ActiveConnection = connTemp : .Prepared = TRUE : .CommandType = ad_CmdText : .CommandTimeout = 30					
		.CommandText = "SELECT txtStr, intVal FROM `aefbeta_v3`.`test` WHERE idTest=? LIMIT 1;"
		.Parameters.Append( .CreateParameter("@idTest", ad_Integer, ad_ParamInput, , "2" ) )

		SET rsTemp = .Execute
			DO WHILE NOT rsTemp.EOF 								
				Response.write "Query 3:<br>"
				Response.write "Txt Str: " & rsTemp("txtStr") & "<br>"
				Response.write "Int Val: " & rsTemp("intVal") & "<br><br>"
				rsTemp.MoveNext
			LOOP
		SET rsTemp = NOTHING
	END WITH

' QUERY 3 RESULT (in html)
'=============================
'	Txt Str: Test2
'	Int Val: 20

%>

ALTER TABLE `testDB`.`test` DROP COLUMN `longText`;

<%

'QUERY 4
'====================================================

	WITH Server.CreateObject("ADODB.Command")
		.ActiveConnection = connTemp : .Prepared = TRUE : .CommandType = ad_CmdText : .CommandTimeout = 30					
		.CommandText = "SELECT * FROM `testDB`.`test`;"
		SET rsTemp = .Execute
			DO WHILE NOT rsTemp.EOF 								
				Response.write "Txt Str: " & rsTemp("txtStr") & "<br>"
				Response.write "Int Val: " & rsTemp("intVal") & "<br><br>"
				rsTemp.MoveNext
			LOOP
		SET rsTemp = NOTHING
	END WITH
	
	
' QUERY 4 RESULT (in html)
'=============================
'	Txt Str: Test1
'	Int Val: 10
'	
'	Txt Str: Test2
'	Int Val: 20
'	
'	Txt Str: Test3
'	Int Val: 30
'	
'	Txt Str: Test4
'	Int Val: 40

' QUERY 5
'====================================================

	WITH Server.CreateObject("ADODB.Command")
		.ActiveConnection = connTemp : .Prepared = TRUE : .CommandType = ad_CmdText : .CommandTimeout = 30					
		.CommandText = "SELECT * FROM `testDB`.`test` WHERE idTest=? LIMIT 1;"
		.Parameters.Append( .CreateParameter("@idTest", ad_Integer, ad_ParamInput, , "2" ) )

		SET rsTemp = .Execute
			DO WHILE NOT rsTemp.EOF 								
				Response.write "Txt Str: " & rsTemp("txtStr") & "<br>"
				Response.write "Int Val: " & rsTemp("intVal") & "<br><br>"
				rsTemp.MoveNext
			LOOP
		SET rsTemp = NOTHING
	END WITH

' QUERY 5 RESULT (in html)
'=============================
'	Txt Str: Test2
'	Int Val: 20	

%>

I can confirm that this happens with both ODBC drivers 8.0.17 and 5.3.13, 32-bit/64-bit

Suggested fix:
Figure out why the presence of a column with the DataType LONGTEXT in a select statement when using prepared statements in ClassicASP/VBScript causes INT fields to return 0 instead of their stored value in database

Just for kicks, i tested everything again, but substituting LONGTEXT for both MEDIUMTEXT and TEXT with the same results. Any Prepared Statement with a query for a column using LONGTEXT, MEDIUMTEXT, and TEXT causes INT values to return as 0.

Forgot to include TINYTEXT in my update... its affected too.

BLOB DataType appears to also cause the problem

JSON dataType is affected as well

Changing the Prepared Statement as follows does produce the value stored in the database (Casting the INT column into a CHAR or DECIMAL):

	WITH Server.CreateObject("ADODB.Command")
		.ActiveConnection = connTemp : .Prepared = TRUE : .CommandType = ad_CmdText : .CommandTimeout = 30					
		.CommandText = "SELECT txtStr, CAST(intVal AS CHAR) AS intVal, textStr FROM `aefbeta_v3`.`test` WHERE idTest=? LIMIT 1;"
		.Parameters.Append( .CreateParameter("@idTest", ad_Integer, ad_ParamInput, , "2" ) )

		SET rsTemp = .Execute
			DO WHILE NOT rsTemp.EOF 								
				Response.write "Query 3:<br>"
				Response.write "Txt Str: " & rsTemp("txtStr") & "<br>"
				Response.write "Int Val: " & rsTemp("intVal") & "<br><br>"
				rsTemp.MoveNext
			LOOP
		SET rsTemp = NOTHING
	END WITH

	WITH Server.CreateObject("ADODB.Command")
		.ActiveConnection = connTemp : .Prepared = TRUE : .CommandType = ad_CmdText : .CommandTimeout = 30					
		.CommandText = "SELECT txtStr, CAST(intVal AS DECIMAL) AS intVal , textStr FROM `aefbeta_v3`.`test` WHERE idTest=? LIMIT 1;"
		.Parameters.Append( .CreateParameter("@idTest", ad_Integer, ad_ParamInput, , "2" ) )

		SET rsTemp = .Execute
			DO WHILE NOT rsTemp.EOF 								
				Response.write "Query 3:<br>"
				Response.write "Txt Str: " & rsTemp("txtStr") & "<br>"
				Response.write "Int Val: " & rsTemp("intVal") & "<br><br>"
				rsTemp.MoveNext
			LOOP
		SET rsTemp = NOTHING
	END WITH

Hi Mark,

Thank you for a detailed bug report with all relevant details and the test case.
I was able to repeat the problem. Our team is working on fixing it.

This is also an issue when using System.Data.Odbc in .NET, as described in this Stack Overflow question:

https://stackoverflow.com/q/58459844/2144390

My bug as well: https://bugs.mysql.com/bug.php?id=92774

Prepared statements do not work... can not upgrade to anything after 5.3.10

Posted by developer:
 
Fixed as of the upcoming MySQL Router 8.0.19 release, and here's the proposed changelog entry to review:

With prepared statements, the presence of a LONGTEXT column in a SELECT
statement caused INT fields to return 0 instead of their stored value.

Thank you for the bug report.

For the Changelog entry, it did not affect JUST the LONGTEXT, but ALL text fields except for the VARCHAR (i never tested the CHAR field) so the fields that caused the issue were:  LONGTEXT, MEDIUMTEXT, TEXT, TINYTEXT, BLOB, and JSON (based on my testing)

Posted by developer:
 
Thanks Mark, much appreciated. 

Setting bug status to "Need Doc Info" so the dev team can confirm exactly which data types are affected. Also to confirm whether or not 5.3.x was affected (and fixed) by this bug.

Bug #96201 is marked as duplicate of this one.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.19 release, and here's the proposed changelog entry by the documentation team:

With prepared SELECT statements the fixed-length numeric types such as INT
were set to 0 instead of their stored value.

Thank you for the bug report.

The Changelog entry does not reference that the issue only appeared if one of the various TEXT fields was ALSO part of the select statement. If the INT field was by itself or did not include any of the TEXT (not including CHAR/VARCHAR) that the INT field returned the correct value.

Bug #98060 marked as duplicate of this one