MySQL cross-database foreign key picker lists the wrong table's columns

[datlechin]

On MySQL and MariaDB, a foreign key that references a table in another database makes the foreign key picker list the wrong table's columns, or fail outright.

What happens

Given orders.customer_id REFERENCES crm.customers(id) on a MySQL connection, open the picker on that cell while browsing the orders database. The column list it offers, which is what the label-column menu is built from, comes from a table named customers in the browsing database, not from crm.customers. If no such table exists there the lookup fails; if one does, the picker offers its columns instead.

Why

ForeignKeyLookupService.referencedColumns asks the driver for the referenced table's columns and passes the referenced schema alongside (ForeignKeyLookupService.swift:36):

try await driver.fetchColumns(table: table, schema: schema)

The MySQL implementation ignores the schema: argument (MySQLPluginDriver.swift:512):

func fetchColumns(table: String, schema: String?) async throws -> [PluginColumnInfo] {
    guard !flavor.isDatabend else { return try await databendColumns(table: table) }
    let result = try await execute(query: "SHOW FULL COLUMNS FROM \(quoteIdentifier(table))")

SHOW FULL COLUMNS FROM \customers`resolves against the session's current database. The driver reached here throughwithMetadataDriver(scope:)with the scope'sdatabasestill set to the tab's database, so the session is onorders` and the statement names a table there.

The row search on the next call is not affected: it qualifies the name properly through SchemaQualifiedName.render, so the picker can end up listing one table's columns while searching another's.

Scope

MySQL, MariaDB and TiDB, and only for a cross-database reference. Same-database foreign keys are unaffected, because the unqualified name then resolves to the right table by accident. Databend takes a different path and is not affected.

Independent of #2746 and of the label-column key problem it exposed; found while tracing that one.