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

[19 Oct 2004 13:05] Roberto Mastrolinardo
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
[19 Oct 2004 13:21] Roberto Mastrolinardo
I have just installed new version 3.51.9 but the problem isn't resolved.
[19 Oct 2004 15:19] Miguel Solorzano
Verified with Access 2003.
[6 Oct 2005 21:43] Mark Matthews
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).
[6 Oct 2005 21:43] Mark Matthews
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).
[9 Oct 2005 11:08] Vasily Kishkin
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.
[23 Feb 2006 14:12] Marcio Monego
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.
[13 Jun 2007 6:19] Erica Moss
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
[13 Jun 2007 6:19] Erica Moss
ODBC trace against mysql

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

[13 Jun 2007 6:20] Erica Moss
ODBC trace against sql server

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

[22 Aug 2007 23:11] Kent Boortz
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
[10 Sep 2007 10:28] Tonci Grgin
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
[11 Sep 2007 17:28] Jim Winstead
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.
[17 Dec 2007 22:19] Jim Winstead
Use original field name instead of alias in SQLSetPos w/ SQL_ADD

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

[17 Dec 2007 22:21] Jim Winstead
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.
[17 Dec 2007 22:26] Jim Winstead
Updated patch, which fixes SQLSetPos() w/ SQL_UPDATE, too

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

[18 Dec 2007 19:31] Jim Winstead
Fixed in 5.1.2.
[31 Dec 2007 13:48] MC Brown
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.
[9 Feb 2008 18:22] Diego Milazzo
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 ?
[10 Feb 2008 8:35] Lawrenty Novitsky
Diego, if nothing goes wrong, it will be released early this week.