Bug #5799 Field values caching previous NULL state when using MySqlDataReader.Read
Submitted: 29 Sep 2004 11:19 Modified: 14 Oct 2004 14:31
Reporter: Liam Westley Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:1.0.0 beta OS:Windows (Windows XP Pro)
Assigned to: Assigned Account CPU Architecture:Any

[29 Sep 2004 11:19] Liam Westley
Description:
MySql v4.1.40-gamma-nt, MySql .NET connector v1.0.0 beta, InnoDB table format
VS.NET 2003, Win XP Pro (SP1)

If you perform a SELECT command, and then read each row from a MySqlDataReader, then any field which is found to be NULL in a row then sets that field as NULL for all subsequent rows whether they contain data or not.

Regards,

Liam Westley

P.S. The software I develop uses generic data access libraries, and handles Access MDB, SQL CE 2.0, SQL Server 2000 and MySQL.  Access to the data reader is abstracted via System.Data.IDataReader, so the exact same SQL statement and code are used for MySql and SQL Server 2000.  This makes it easy to compare expected behaviours between different data providers.

How to repeat:
Create a MySql database catalog/schema called MySqlNullBug.

In Visual Studio.NET 2003, create a C# Windows Form Application.  Add a reference to the MySql .NET connector, and add a single button, Button1.  Double click on the button and replace the event handle with the code provided below.

This creates a table with a column which we use for ordering items, and an integer column which can contain NULL values.  Four rows are inserted, and then the data is selected first in ascending order, and then by descending order.  The detection of NULL values is not repeated.  

  expected behaviour : only row 3 should return NULL value
  ASC behaviour        : rows 3 and 4 return NULL values
  DESC behaviour      : rows 3, 2 and 1 return NULL values

---- code sample ----

		private MySqlConnection _db = null;
		private void button1_Click(object sender, System.EventArgs e)
		{
			_db = new MySqlConnection("server=localhost;database=mysqlnullbug");
			_db.Open();

			MySqlCommand cmd = null;
			MySqlDataReader dr = null;
			
			try
			{ 
				cmd = new MySqlCommand("DROP TABLE MySqlNullBug", _db);
				cmd.ExecuteNonQuery();
			}
			catch { }

			cmd = new MySqlCommand("CREATE TABLE MySqlNullBug (orderingValue INT NOT NULL, anyValue INT NULL) TYPE=INNODB", _db);
			cmd.ExecuteNonQuery();

			cmd = new MySqlCommand("INSERT INTO MySqlNullBug VALUES (1, 1)", _db);
			cmd.ExecuteNonQuery();
			cmd = new MySqlCommand("INSERT INTO MySqlNullBug VALUES (2, 2)", _db);
			cmd.ExecuteNonQuery();
			cmd = new MySqlCommand("INSERT INTO MySqlNullBug VALUES (3, NULL)", _db);
			cmd.ExecuteNonQuery();
			cmd = new MySqlCommand("INSERT INTO MySqlNullBug VALUES (4, 4)", _db);
			cmd.ExecuteNonQuery();

			cmd = new MySqlCommand("SELECT * FROM MySqlNullBug ORDER BY orderingValue ASC", _db);
			dr = cmd.ExecuteReader();
			while (dr.Read())
			{
				if (dr["anyValue"] == System.DBNull.Value)
				{
					MessageBox.Show("Found null for orderingValue of : " + dr["orderingValue"].ToString());
				}
			}
			dr.Close();

			cmd = new MySqlCommand("SELECT * FROM MySqlNullBug ORDER BY orderingValue DESC", _db);
			dr = cmd.ExecuteReader();
			while (dr.Read())
			{
				if (dr["anyValue"] == System.DBNull.Value)
				{
					MessageBox.Show("Found null for orderingValue of : " + dr["orderingValue"].ToString());
				}
			}
			dr.Close();
		
		}

Suggested fix:

I traced this in the source code, 
  Field41.cs, method SetBinaryData
  Field.cs, method SetTextData

Both include the if clause,

		if (len == -1)
		{
			colValue.IsNull = true;
			buffer = null;
			return;
		}

Neither method sets colValue.IsNull = false, when (len != -1).  This results in the state of Null is cached from the previous row read, and so the first row to set .IsNull = true sets it for all subsequent rows.

I added the following after the if clause in both methods, 

		// 29 Sep 2004 - LIAM ADDED THIS LINE
		colValue.IsNull = false;

and we get the expected behaviour when reading multiple rows with the MySqlDataReader.
[29 Sep 2004 13:03] Liam Westley
Apoligies for this one it is a repeat of http://bugs.mysql.com/bug.php?id=5388
[14 Oct 2004 14:31] Reggie Burnett
No problem with the repeat.