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
Category:Connector/Net Severity:S2 (Serious)
Version:1.0.4 OS:Microsoft Windows (Windows 2000)
Assigned to: Reggie Burnett Target Version:

[3 Feb 2005 15:57] vincent ugenti 
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.
[26 Feb 2005 4:49] Reggie Burnett 
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"]);
		}
[27 Jan 2006 15:42] vincent ugenti 
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.