MySQL Bugs: #31930: Stored procedures with "ambiguous column name" error cause lock-ups

Bug #31930	Stored procedures with "ambiguous column name" error cause lock-ups
Submitted:	30 Oct 2007 0:14	Modified:	13 Nov 2007 12:30
Reporter:	Vasili Sviridov
Status:	Closed
Category:	Connector/Net	Severity:	S1 (Critical)
Version:	5.1.3	OS:	Any
Assigned to:		Target Version:

Description:
When there is an column name ambiguity in the stored procedure, that error is not properly
caught in the Connector/Net and application starts to hang indefinitely.

How to repeat:
create a stored procedure with a select statement. something along these lines

SELECT ambColumn FROM tableOne to JOIN tableTwo tt ON tt.ambColumn = to.ambColumn;

when ran in Query Browser it would report the ambiguity of the column in the select
statement.

in C#

try
{
MySqlCommand cmd = new MySqlCommand("prcAmbiguousName", connection);
cmd.CommandType = CommandType.StoredProcedure;
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds); // <- hangs here
}
catch(Exception ex)
{
//never gets here
}

I cannot repeat this with the following test case.  Are you sure your proc is not hanging
for other reasons.

[Test]
public void AmbiguousColumns()
{
  execSQL("CREATE TABLE t1 (id INT)");
  execSQL("CREATE TABLE t2 (id1 INT, id INT)");
  execSQL("CREATE PROCEDURE spTest() BEGIN SELECT id FROM t1 JOIN t2 on t1.id=t2.id;
END");

  MySqlCommand cmd = new MySqlCommand("spTest", conn);
  cmd.CommandType = CommandType.StoredProcedure;
  MySqlDataAdapter da = new MySqlDataAdapter(cmd);
  DataTable dt = new DataTable();
  try
  {
     da.Fill(dt);
     Assert.Fail("The above should have thrown an exception");
  }
  catch (Exception ex)
  {
  }
}

Notice that you're trying it with DataTable, whereas my code contains a DataSet.
It might make the difference. I'll try to test it on my side again to produce a minimal
problematic code.

I've tested it on my side, and I can confirm that when used with DataTable, errors in the
stored procedure are indeed captured and appear on .Net side. But when used with DataSet
they follow the behavior originally described. So please re-open this bug and investigate
further.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37129

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37132

Fixed in 5.0.9 and 5.1.4

A note has been added to the 5.1.4 and 5.0.9 changelogs:

A syntax error in a set of batch statements could leave the data adapter in a state that
appears hung.