MySQL Bugs: #8292: GROUP BY / WITH ROLLUP with DataSet causes System.Data.ConstraintException

Bug #8292	GROUP BY / WITH ROLLUP with DataSet causes System.Data.ConstraintException
Submitted:	3 Feb 2005 15:57	Modified:	27 Jan 2006 15:42
Reporter:	vincent ugenti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.4	OS:	Windows (Windows 2000)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
If you're using the MySQL .NET Connector and have columns defined as non-null, WITH ROLLUP will cause an exception when you try to fill the dataset due to the null values.  Therefore there is no way to use WITH ROLLUP through the .NET connector and a DataSet unless you allow NULL values in the GROUP BY columns.

How to repeat:
Create a table with non-null constraints on at least one column.

Add some rows to the table.

Run a query using GROUP BY / WITH ROLLUP on the column with the non-null constraint through the .NET connector.

Call DataAdapter.Fill(DataSet)

System.Data.ConstraintException is thrown with the following message:
Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Suggested fix:
.NET Connector could intercept the NULL values and convert them to default values (0 / empty string / etc) before sending the result to the client.  Or the server could be modified to not return NULL values when it is a violation of table constraints but this is probably not feasible.

I think you are loading data into a typed dataset.  If so, then you are responsible for making sure the constraints in your dataset are appropriate for the data that is being inserted.

Here is a unit test that I just wrote for this and it works great.  You'll see that the null value comes back and can be checked for without trouble.

		[Test]
		public void Rollup() 
		{
			execSQL("DROP TABLE IF EXISTS test");
			execSQL("CREATE TABLE test ( id INT NOT NULL, amount INT )");
			execSQL("INSERT INTO test VALUES (1, 44)");
			execSQL("INSERT INTO test VALUES (2, 88)");

			MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test GROUP BY id WITH ROLLUP", conn);
			DataSet ds = new DataSet();
			da.Fill(ds);

			Assert.AreEqual(1, ds.Tables.Count);
			Assert.AreEqual(3, ds.Tables[0].Rows.Count);
			Assert.AreEqual(88, ds.Tables[0].Rows[2]["amount"]);
			Assert.AreEqual(DBNull.Value, ds.Tables[0].Rows[2]["id"]);
		}

You're right, by not calling DataAdapter.FillSchema to create a strongly typed dataset, you can use DataAdapter.Fill safely and then check for the DBNull.Value.