MySQL Bugs: #6157: BUG in the alias use with ADO's Object

Bug #6157	BUG in the alias use with ADO's Object
Submitted:	19 Oct 2004 15:05	Modified:	31 Dec 2007 14:48
Reporter:	Roberto Mastrolinardo
Status:	Closed
Category:	Connector/ODBC	Severity:	S1 (Critical)
Version:	3.51.20	OS:	Microsoft Windows (Windows 95 / 98 / 2000 / XP)
Assigned to:	Jim Winstead	Target Version:	5.1.2
Triage:	D2 (Serious)

Description:
I use an alias in a select for the fields name. When I use the method Update, the program
generate the error: 

"[MySql][ODBC 3.51 Driver][MySql-5.0.0-alpha-nt] Unknown column 'col1' in 'where
clause'".

Example Visual Basic 6:

' In a form with a command button named Command1
Option Explicit
Dim filedb As New ADODB.Connection
Dim tflag As New ADODB.Recordset
Private Sub Command1_Click()
   
   If tflag.state And adstateopen Then tflag.Close
   tflag.cursorlocation = aduseclient
   tflag.Open "select campo1 as col1, campo2 as col2 from tab1", filedb_ adOpenKeyset,
adLockOptimistic
   tflag!col1 = "AAAAA"
   tflag!col2 = 21
   tflag.Update  ->  Error
   tflag.Close
   
   
End Sub

Private Sub Form_Load()
   
   filedb.Open "DSN=pippo"
   
End Sub

Private Sub Form_Unload(Cancel As Integer)
   
   filedb.Close
   
End Sub

DATABASE STRUCT:
   tab1   ->    Table
       campo1 char(50)   ->   field select with alias col1 
       campo2 int(11)   ->   field select with alias col2

Sorry for my English.

How to repeat:
' In a form with a command button named Command1
Option Explicit
Dim filedb As New ADODB.Connection
Dim tflag As New ADODB.Recordset
Private Sub Command1_Click()
   
   If tflag.state And adstateopen Then tflag.Close
   tflag.cursorlocation = aduseclient
   tflag.Open "select campo1 as col1, campo2 as col2 from tab1", filedb_ adOpenKeyset,
adLockOptimistic
   tflag!col1 = "AAAAA"
   tflag!col2 = 21
   tflag.Update  ->  Error
   tflag.Close
   
   
End Sub

Private Sub Form_Load()
   
   filedb.Open "DSN=pippo"
   
End Sub

Private Sub Form_Unload(Cancel As Integer)
   
   filedb.Close
   
End Sub

DATABASE STRUCT:
   tab1   ->    Table
       campo1 char(50)   ->   field select with alias col1 
       campo2 int(11)   ->   field select with alias col2

I have just installed new version 3.51.9 but the problem isn't resolved.

Verified with Access 2003.

Please reverify w/ 3.51.12 as there are fixes in there that most likely address this bug.

(now linked w/ libmysql 4.1.x, which should return column information correctly).

Please reverify w/ 3.51.12 as there are fixes in there that most likely address this bug.

(now linked w/ libmysql 4.1.x, which should return column information correctly).

I tested on myodbc 3.51.12 and mysqld 4.1.16. I've got the follow error:

Additional information: Current Recordset does not support updating. This may be a
limitation of the provider, or of the selected locktype.

I had the same problem with MySQL 5.0.18, MyODBC 3.51.12 and ADO 2.8.
The Select statement that generates the recordset DIDN'T use a field alias included in a
where clause BUT after executed a find command in ADO and it has found the record a
future change in the field that has been aliased causes an identical error crying for a
field not known in where clause...
I think it is internal to ADO ou in MyODBC when the UPDATE metod passes something that
distinguish the field to the provider.

Created a simpler test case for this:
SETUP
create database ado;
use ado;
create table foo (c1 INT, c2 char(20));
insert into foo values (1, 'eric');

VB CODE:
Private Sub AliasTest()
    On Error GoTo EH
    Dim strSQL As String
    Dim strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset
    Dim fld As Field

    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset

    strSQL = "SELECT c1, c2 as bar FROM foo"

    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=3;"
                    
    'strConn = "DSN=SQL Server"
 
    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseClient
    connection1.Open

    recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText
    recset1.MoveFirst
    For Each fld In recset1.Fields
        If fld.Type = adChar Then
            fld.Value = "test"
        End If
    Next
    recset1.Update

    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
EH:
    Debug.Print Err.Number & ":" & Err.Description
End Sub

Results in error:
-2147217900:[MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt-log]Unknown column 'bar'
in 'where clause'

Tested against 3.5.15

Also ran this same test case against SQL Server and it ran fine.  ODBC traces for both
runs are attached:
SQL-mysql.LOG
SQL-sqlserver.LOG

ODBC trace against mysql

Attachment: SQL-mysql.LOG (application/octet-stream, text), 75.19 KiB.

ODBC trace against sql server

Attachment: SQL-sqlserver.LOG (application/octet-stream, text), 81.94 KiB.

Looking at the logs and source "driver/results.c", ADO creates
an Update SQL statement. To get the column names, SQLColAttribute()
is run, and it will with the MySQL ODBC driver list an alias that
seems not to be valid any longer, with SQLServer the original
table name is used

  "UPDATE `ado`.`foo` SET `bar`=? WHERE `c1`=? AND `bar`=?\ 0"

  "UPDATE "ado".."foo" SET "c2"=? WHERE "c1"=? AND "c2"=?\ 0"

The alias in the MySQL driver comes from that we use the 
struct MYSQL_FIELD field "name" instead of "org_name".

Now, why "name" lists an alias used in the previous select
and think it is an permanent alias I don't know, should it?
And in case it can be valid or not, I think the naming
of the MYSQL_FIELD fields could be more clear "possibly_alias"
and "real_name" maybe.

The small patch below likely fixes the problem in this case
(with some ifdef 5.0 and up maybe), but not sure it is the
right one, and if there are other places that should use
the "org_*" version, for tables and column names.

--- driver/results.c    (revision 674)
+++ driver/results.c    (working copy)
@@ -632,7 +632,7 @@
         case SQL_DESC_NAME:
         case SQL_COLUMN_NAME:
             return copy_str_data(SQL_HANDLE_STMT, stmt, CharacterAttributePtr,
-                                 BufferLength, StringLengthPtr, field->name);
+                                 BufferLength, StringLengthPtr, field->org_name);
 
         case SQL_DESC_BASE_TABLE_NAME:
 #if MYSQL_VERSION_ID >= 40100

This bug is still present in latest GA.

070910 12:26:03	      3 Connect     root@localhost on test
		      3 Query       SET SQL_AUTO_IS_NULL = 0
		      3 Query       select database()
		      3 Query       SELECT @@tx_isolation
		      3 Query       drop table if exists bug6157
		      3 Query       create table bug6157(x SERIAL, VCField VARCHAR(30) DEFAULT NULL,
DtField DATE)
		      3 Query       INSERT INTO bug6157 VALUES (NULL, 'TEST', '2007-09-10')
		      3 Query       SELECT x, VCField AS Fld2, DtField AS Fld3 FROM bug6157
		      3 Query       UPDATE `test`.`bug6157` SET `Fld2`='testing' WHERE `x`=1 AND
`Fld2`='TEST'
070910 12:26:08	      3 Quit

Looking at the two traces, ADO (or maybe the OLE-DB driver for ODBC) is querying for
SQL_COLUMN_NAME from our driver, but SQL_BASE_COLUMN_NAME from SQL Server's driver. We
also support SQL_BASE_COLUMN_NAME (which is the correct attribute to be using), and it's
not clear why ADO is not trying to use that.

As with Bug #2966, we're going to need to connect with someone at Microsoft to find out
what ADO is trying to do here.

Use original field name instead of alias in SQLSetPos w/ SQL_ADD

Attachment: bug6157.patch (text/plain), 2.27 KiB.

Using 3.51, ADO generates this query itself, and behaves as described in my previous
comment.

With Connector/ODBC 5.1, it uses SQLSetPos(), which exposes a bug in the driver itself.

With this patch, ADO works with the 5.1 driver. There does not appear to be a way to fix
this for the 3.51 driver.

Updated patch, which fixes SQLSetPos() w/ SQL_UPDATE, too

Attachment: bug6157.patch (text/plain), 2.73 KiB.

Fixed in 5.1.2.

A note has been added to the 5.1.2 changelog: 

Adding or updating a row using SQLSetPos() on a result set with aliased columns would
fail.

I have the same problem.
I test it with 3.51 and 5.1.1

Which is the release date for the ODBC 5.1.2 ?

Diego, if nothing goes wrong, it will be released early this week.