Bug #8574 MySQLCommandBuilder unable to support sub-queries
Submitted: 17 Feb 2005 16:28 Modified: 24 Feb 2005 16:14
Reporter: Scott Mankowitz Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:1.0.4 OS:Windows (Win XP)
Assigned to: Reggie Burnett CPU Architecture:Any

[17 Feb 2005 16:28] Scott Mankowitz
Description:
When calling MySQL command builder against a SelectCommand that contains a sub-query, the commandbuilder fails, claiming that it can not support multi-table queries, even though all of the returned fields are from a single table.

How to repeat:
SQL = 
"SELECT * FROM t1 WHERE FormID IN (SELECT id FROM t2 WHERE age < 10)"

        ida = New MySqlDataAdapter(sql, conn)
        Dim cb As New MySqlCommandBuilder(CType(ida, MySqlDataAdapter), True)
        cb.QuotePrefix = "`"
        cb.QuoteSuffix = "`"

Suggested fix:
I am not sure, but I think it has something to do with the persistence of _TableName, because once it fails once, it keeps on failing for even simple queries.
[17 Feb 2005 17:36] Scott Mankowitz
I think I misattributed the error - 

It seems that it gives me the multitable issue when I use an inherent DB command, such as now()

if the SQL is  "SELECT *, now() as ServerTime from tblMaster", then it chokes because it doesn't know that now() is a function and not a field.
[24 Feb 2005 16:14] Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

This is tricky to fix since the command builder really needs to be able to reinsert the function into the generated final select to properly update the dataset with changes.  It is not doing this right now.  I have changed it to just leave the function out.  You can get what you need by doing the following:

DataSet changes = myDS.GetChanges();
da.Update(changes);
myDS.Merge(changes);
myDS.AcceptChanges();

This will preserve the function based columns and merge them with the result of the update.