Skip to content

MySQL dialect on UPDATE with JOIN doesn't give prefixes in the SET statement if WHERE clasue is missing #5617

New issue
New issue
Closed
Closed
MySQL dialect on UPDATE with JOIN doesn't give prefixes in the SET statement if WHERE clasue is missing#5617
Labels
bugSomething isn't workingmysql
Milestone
 
1.3.x
@rockallite

Description

@rockallite
rockallite
opened on Sep 29, 2020 · edited by rockallite

Describe the bug
An update() can be passed an LEFT OUTER JOIN statement, but the compiled statement with MySQL dialect lacks table (or alias) prefixes in the SET statement. If a column that is set exists in multiple tables, an error of ERROR: Column 'col_name' in field list is ambiguous will be raised when the generated SQL statement runs on the actual MySQL server.

Expected behavior
Table (or alias) prefixes in the SET statement should be added.

To Reproduce

from sqlalchemy import update, func, table, column, types
from sqlalchemy.dialects import mysql

mysql_dialect = mysql.dialect()
c1 = column("id", types.Integer)
c2 = column("event_date", types.Date)
c3 = column("sales", types.Integer)
c4 = column("returns", types.Integer)
t1 = table("t1", c1, c2, c3, c4)
t2 = table("t2", c1, c2, c3)
t3 = table("t3", c1, c2, c4)
j = t1.outerjoin(
    t2, t1.c.event_date == t2.c.event_date
).outerjoin(
    t3, t1.c.event_date == t3.c.event_date
)
v = {
    t1.c.sales: func.coalesce(t2.c.sales, 0),
    t1.c.returns: func.coalesce(t3.c.returns, 0),
}
s = update(j).values(v)
print(s.compile(dialect=mysql_dialect))

Output
(Line breaks are manually added for clarity)

UPDATE t1
 LEFT OUTER JOIN t2 ON t3.event_date = t3.event_date
 LEFT OUTER JOIN t3 ON t3.event_date = t3.event_date
 SET sales=coalesce(t2.sales, %s), returns=coalesce(t3.returns, %s)

Expected output:

UPDATE t1
 LEFT OUTER JOIN t2 ON t3.event_date = t3.event_date
 LEFT OUTER JOIN t3 ON t3.event_date = t3.event_date
 SET t1.sales=coalesce(t2.sales, %s), t1.returns=coalesce(t3.returns, %s)

Versions.

  • OS: macOS Catalina (10.15.7)
  • Python: 3.8.2
  • SQLAlchemy: 1.3.19
  • Database: MySQL 8.0.21
  • DBAPI: mysqlclient 2.0.1

Have a nice day!

Activity

rockallite
added
requires triageNew issue that requires categorization
on Sep 29, 2020
zzzeek
added
bugSomething isn't working
mysql
and removed
requires triageNew issue that requires categorization
on Sep 29, 2020
zzzeek
added this to the 1.3.x milestone on Sep 29, 2020
zzzeek

zzzeek commented on Sep 29, 2020

@zzzeek
zzzeek
on Sep 29, 2020
Member

hi there -

there is a bug here, but also your test script can't work because you are assigning the same column objects to multiple tables.

workaround for now is to provide a WHERE clause, I'm not sure why that is but it should be fixed momentarily, however you need to also use columns unique to each table, otherwise "t3.c.name" is the same object as "t1.c.name".

from sqlalchemy import update, func, table, column, types
from sqlalchemy.dialects import mysql

mysql_dialect = mysql.dialect()
c1 = column("id", types.Integer)
c2 = column("event_date", types.Date)
c3 = column("sales", types.Integer)
c4 = column("returns", types.Integer)
t1 = table("t1", c1, c2, c3, c4)

c1 = column("id", types.Integer)
c2 = column("event_date", types.Date)
c3 = column("sales", types.Integer)
c4 = column("returns", types.Integer)
t2 = table("t2", c1, c2, c3)

c1 = column("id", types.Integer)
c2 = column("event_date", types.Date)
c3 = column("sales", types.Integer)
c4 = column("returns", types.Integer)
t3 = table("t3", c1, c2, c4)

j = t1.outerjoin(
    t2, t1.c.event_date == t2.c.event_date
).outerjoin(
    t3, t1.c.event_date == t3.c.event_date
)
v = {
    t1.c.sales: func.coalesce(t2.c.sales, 0),
    t1.c.returns: func.coalesce(t3.c.returns, 0),
}

s = update(j).values(v).where(t1.c.id > 0)
print(s.compile(dialect=mysql_dialect))
zzzeek
changed the title [-]MySQL dialect on UPDATE with JOIN doesn't give prefixes in the SET statement[/-] [+]MySQL dialect on UPDATE with JOIN doesn't give prefixes in the SET statement if WHERE clasue is missing[/+] on Sep 29, 2020
sqla-tester

sqla-tester commented on Sep 29, 2020

@sqla-tester
sqla-tester
on Sep 29, 2020
Collaborator

Mike Bayer has proposed a fix for this issue in the master branch:

Scan for tables without relying upon whereclause https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2274

sqla-tester

sqla-tester commented on Sep 29, 2020

@sqla-tester
sqla-tester
on Sep 29, 2020
Collaborator

Mike Bayer has proposed a fix for this issue in the rel_1_3 branch:

Scan for tables without relying upon whereclause https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2275

rockallite

rockallite commented on Sep 30, 2020

@rockallite
rockallite
on Sep 30, 2020
Author

@zzzeek Thanks for reminding me! I never realized that column instance must be unique, at least not in the documentation.

sqlalchemy-bot
closed this as completedin f483573on Sep 30, 2020
sqlalchemy-bot
added a commit that references this issue on Sep 30, 2020

Scan for tables without relying upon whereclause

f4cc32f
zzzeek

zzzeek commented on Sep 30, 2020

@zzzeek
zzzeek
on Sep 30, 2020
Member

@zzzeek Thanks for reminding me! I never realized that column instance must be unique, at least not in the documentation.

schema level columns guard against this but lower cases don't. new bug coming

zzzeek
mentioned this on Sep 30, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingmysql

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @zzzeek@rockallite@sqla-tester

        Issue actions
          MySQL dialect on UPDATE with JOIN doesn't give prefixes in the SET statement if WHERE clasue is missing · Issue #5617 · sqlalchemy/sqlalchemy