Scan for tables without relying upon whereclause

Fixed bug where an UPDATE statement against a JOIN using MySQL multi-table
format would fail to include the table prefix for the target table if the
statement had no WHERE clause, as only the WHERE clause were scanned to
detect a "multi table update" at that particular point.  The target
is now also scanned if it's a JOIN to get the leftmost table as the
primary table and the additional entries as additional FROM entries.

Fixes: #5617
Change-Id: I26d74afebe06e28af28acf960258f170a1627823

2 files changed, 92 insertions, 25 deletions

diff --git a/test/profiles.txt b/test/profiles.txt
index 15f279256..4a21de427 100644
--- a/test/profiles.txt
+++ b/test/profiles.txt
@@ -153,38 +153,38 @@ test.aaa_profiling.test_compiler.CompileTest.test_update x86_64_linux_cpython_3.
 
 # TEST: test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause
 
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mariadb_mysqldb_dbapiunicode_cextensions 154
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mariadb_mysqldb_dbapiunicode_nocextensions 154
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mariadb_pymysql_dbapiunicode_cextensions 154
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mariadb_pymysql_dbapiunicode_nocextensions 154
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mssql_pyodbc_dbapiunicode_cextensions 154
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mssql_pyodbc_dbapiunicode_nocextensions 154
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mariadb_mysqldb_dbapiunicode_cextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mariadb_mysqldb_dbapiunicode_nocextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mariadb_pymysql_dbapiunicode_cextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mariadb_pymysql_dbapiunicode_nocextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mssql_pyodbc_dbapiunicode_cextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mssql_pyodbc_dbapiunicode_nocextensions 159
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mysql_mysqldb_dbapiunicode_cextensions 152
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mysql_mysqldb_dbapiunicode_nocextensions 152
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mysql_pymysql_dbapiunicode_cextensions 152
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_mysql_pymysql_dbapiunicode_nocextensions 152
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_oracle_cx_oracle_dbapiunicode_cextensions 150
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_oracle_cx_oracle_dbapiunicode_nocextensions 150
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_postgresql_psycopg2_dbapiunicode_cextensions 154
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_postgresql_psycopg2_dbapiunicode_nocextensions 154
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_sqlite_pysqlite_dbapiunicode_cextensions 154
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_sqlite_pysqlite_dbapiunicode_nocextensions 154
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mariadb_mysqldb_dbapiunicode_cextensions 160
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mariadb_mysqldb_dbapiunicode_nocextensions 158
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mariadb_pymysql_dbapiunicode_cextensions 160
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mariadb_pymysql_dbapiunicode_nocextensions 158
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mssql_pyodbc_dbapiunicode_cextensions 160
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mssql_pyodbc_dbapiunicode_nocextensions 158
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_oracle_cx_oracle_dbapiunicode_cextensions 157
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_oracle_cx_oracle_dbapiunicode_nocextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_postgresql_psycopg2_dbapiunicode_cextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_postgresql_psycopg2_dbapiunicode_nocextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_sqlite_pysqlite_dbapiunicode_cextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_2.7_sqlite_pysqlite_dbapiunicode_nocextensions 159
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mariadb_mysqldb_dbapiunicode_cextensions 165
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mariadb_mysqldb_dbapiunicode_nocextensions 165
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mariadb_pymysql_dbapiunicode_cextensions 165
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mariadb_pymysql_dbapiunicode_nocextensions 165
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mssql_pyodbc_dbapiunicode_cextensions 165
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mssql_pyodbc_dbapiunicode_nocextensions 165
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mysql_mysqldb_dbapiunicode_cextensions 158
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mysql_mysqldb_dbapiunicode_nocextensions 158
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mysql_pymysql_dbapiunicode_cextensions 158
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_mysql_pymysql_dbapiunicode_nocextensions 158
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_oracle_cx_oracle_dbapiunicode_cextensions 156
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_oracle_cx_oracle_dbapiunicode_nocextensions 156
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_postgresql_psycopg2_dbapiunicode_cextensions 160
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_postgresql_psycopg2_dbapiunicode_nocextensions 160
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_sqlite_pysqlite_dbapiunicode_cextensions 160
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_sqlite_pysqlite_dbapiunicode_nocextensions 160
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_oracle_cx_oracle_dbapiunicode_cextensions 163
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_oracle_cx_oracle_dbapiunicode_nocextensions 163
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_postgresql_psycopg2_dbapiunicode_cextensions 165
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_postgresql_psycopg2_dbapiunicode_nocextensions 165
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_sqlite_pysqlite_dbapiunicode_cextensions 165
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.8_sqlite_pysqlite_dbapiunicode_nocextensions 165
 
 # TEST: test.aaa_profiling.test_misc.CacheKeyTest.test_statement_key_is_cached
 
diff --git a/test/sql/test_update.py b/test/sql/test_update.py
index 201e6c64f..ec96af207 100644
--- a/test/sql/test_update.py
+++ b/test/sql/test_update.py
@@ -1016,7 +1016,7 @@ class UpdateFromCompileTest(
             dialect="mysql",
         )
 
-    def test_update_from_join_mysql(self):
+    def test_update_from_join_mysql_whereclause(self):
         users, addresses = self.tables.users, self.tables.addresses
 
         j = users.join(addresses)
@@ -1034,6 +1034,73 @@ class UpdateFromCompileTest(
             dialect=mysql.dialect(),
         )
 
+    def test_update_from_join_mysql_no_whereclause_one(self):
+        users, addresses = self.tables.users, self.tables.addresses
+
+        j = users.join(addresses)
+        self.assert_compile(
+            update(j).values(name="newname"),
+            ""
+            "UPDATE users "
+            "INNER JOIN addresses ON users.id = addresses.user_id "
+            "SET users.name=%s",
+            checkparams={"name": "newname"},
+            dialect=mysql.dialect(),
+        )
+
+    def test_update_from_join_mysql_no_whereclause_two(self):
+        users, addresses = self.tables.users, self.tables.addresses
+
+        j = users.join(addresses)
+        self.assert_compile(
+            update(j).values({users.c.name: addresses.c.email_address}),
+            ""
+            "UPDATE users "
+            "INNER JOIN addresses ON users.id = addresses.user_id "
+            "SET users.name=addresses.email_address",
+            checkparams={},
+            dialect=mysql.dialect(),
+        )
+
+    def test_update_from_join_mysql_no_whereclause_three(self):
+        users, addresses, dingalings = (
+            self.tables.users,
+            self.tables.addresses,
+            self.tables.dingalings,
+        )
+
+        j = users.join(addresses).join(dingalings)
+        self.assert_compile(
+            update(j).values({users.c.name: dingalings.c.id}),
+            ""
+            "UPDATE users "
+            "INNER JOIN addresses ON users.id = addresses.user_id "
+            "INNER JOIN dingalings ON addresses.id = dingalings.address_id "
+            "SET users.name=dingalings.id",
+            checkparams={},
+            dialect=mysql.dialect(),
+        )
+
+    def test_update_from_join_mysql_no_whereclause_four(self):
+        users, addresses, dingalings = (
+            self.tables.users,
+            self.tables.addresses,
+            self.tables.dingalings,
+        )
+
+        j = users.join(addresses).join(dingalings)
+
+        self.assert_compile(
+            update(j).values(name="foo"),
+            ""
+            "UPDATE users "
+            "INNER JOIN addresses ON users.id = addresses.user_id "
+            "INNER JOIN dingalings ON addresses.id = dingalings.address_id "
+            "SET users.name=%s",
+            checkparams={"name": "foo"},
+            dialect=mysql.dialect(),
+        )
+
     def test_render_table(self):
         users, addresses = self.tables.users, self.tables.addresses