mysql.connector.errors.ProgrammingError: 1052: Column 'date' in field list is ambiguous

[tormen]

CLIENT:
MySQL Connector/Python version 0.3.2
Linux oneoneoh 2.6.18-5-amd64 #1 SMP Mon Dec 3 04:55:28 UTC 2007 x86_64 GNU/Linux
mysql version: 5.0.32-Debian_7etch3-log

SERVER:
MySQL version:
5.2.3-MariaDB-mariadb87-log
[but the tables in use are not of type mariadb]
Linux oneohone 2.6.18-6-amd64 #1 SMP Fri Dec 12 05:49:32 UTC 2008 x86_64 GNU/Linux

PROBLEM:
This query:
UPDATE myTable, tempTable SET fooCount=fooCount+1, lastDate=GREATEST(`date`, lastDate) WHERE myTable.id=tempTable.ID AND type = 'foo'

Causes:
mysql.connector.errors.ProgrammingError: 1052: Column 'date' in field list is ambiguous

But we tested on the CLIENT in mysql yesterday: "SELECT date from myTable" and it worked without warning/error?!
--> mysql/connector should do the same?!
... or I missed something :)

[Geert JM Vanderkelen (geertjmvdk)]

The error is valid and tells you wat the problem is: you have in both tables the field named 'date' (which is a bad name for a column btw).
Here some more code that shows the exact same error:

def bug797754():
    cnx = mysql.connector.connect(user='root',database='test')

    cur = cnx.cursor()
    cur.execute("DROP TABLE IF EXISTS t1")
    cur.execute("CREATE TABLE t1 (c1 DATE, c2 DATE)")
    cur.execute("UPDATE t1 AS a1, t1 as a2 SET c1 = GREATEST(c1,c2) "
        " WHERE a1.c1 = a2.c2")
    cur.close()
    cnx.close()

Anyway, not a bug, just that the query needs fixing.