Bug #11947 MySQLCommandBuilder mishandling CONCAT() aliased column
Submitted: 14 Jul 2005 16:56 Modified: 3 Oct 2005 20:43
Reporter: NOT_FOUND NOT_FOUND Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:1.04 OS:Windows (Windows XP)
Assigned to: Reggie Burnett CPU Architecture:Any

[14 Jul 2005 16:56] NOT_FOUND NOT_FOUND
Description:
When attempting to select columns from a database that includes a column that does not exist within the table (created through functions) the CommandBuilder complains of a 'multi-table' error.

Specifically I have tested this with the CONCAT(...) function which produces problems. 

How to repeat:
Given the following SELECT statement:

SELECT id,surname,forename,title,CONCAT(forename, ' ', surname) "fullName" FROM customers

an error is produced when calling the DataAdapter.Update(dataSet) function. The following SELECT statement will work though (only change is the omission of the CONCAT aliased column:

SELECT id,surname,forename,title FROM customers

Sample Code Listing:

MySqlConnection conn = new MySqlConnection("Database=regency;Data Source=localhost;User Id=...;Password=...");
        MySqlDataAdapter adapter = new MySqlDataAdapter();

private void CreateCommands()
{
    MySqlCommandBuilder cb = new MySqlCommandBuilder(adapter);
    adapter.TableMappings.Add("Table", "customers");

    ////// CHANGE THE COMMAND HERE AS DESCRIBED ABOVE /////
    String cmd = "SELECT id,surname,forename,title FROM customers"
    adapter.SelectCommand = new MySqlCommand(cmd, conn);
}

private void Update()
{
    BindingContext[customerDataSet, "customers"].EndCurrentEdit();

    if (customerDataSet.HasChanges())
    {
        conn.Open();

        ////// ERROR THROWN HERE //////
        adapter.Update(customerDataSet);
        conn.Close();
    }
}

Suggested fix:
When dealing with columns which have been created 'client-side' (i.e. as above, ones that do not exist within the database) they should be omitted from the generated commands. 

This behaviour should be the typical behaviour desired for most scenarios that require this setup (or desire custom client-side columns)
[2 Oct 2005 11:43] Vasily Kishkin
Could you please attach full test of your test case ?
[3 Oct 2005 20:43] Reggie Burnett
Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

This is fixed in 1.0.6