Cross-database joins unsupported

[bryantgeorge]

Description of the bug

Cross-database joins don't seem to be supported without defining prefixes in the $databases config within settings.php.

Steps To Reproduce

$query = db_select("db1.table1", "table1");
$query->addExpression("COUNT(*)", "count");
$query->join("db2.table2", "table2", "table2.uid = table1.uid");
$query->execute();

Actual behavior

Results in an error like SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db1.db2.table2' doesn't exist.

Expected behavior

I expect the query to execute successfully despite the fact that the tables are in different databases.

Additional information

• Backdrop CMS version: 1.32.1
• Web server and its version: Apache 2.4.58
• PHP version: 8.3.11
• Database server and its version: MariaDB 10.11.14

Current $databases config in settings.php:

$databases['default']['default'] = [
      'driver' => 'mysql',
      'database' => 'db1',
      'username' => 'REDACTED',
      'password' => 'REDACTED',
      'host' => 'REDACTED',
      'port' => 'REDACTED',
      'prefix' => [
        'default' => '',
      ],
      'collation' => 'utf8mb4_unicode_ci',
      'charset' => 'utf8mb4',
];

$databases['default']['db2'] = $databases['default']['default'];
$databases['default']['db2']['database'] = 'db2';
$databases['default']['db2']['prefix'] = [
  'default' => '',
];

Notes

I know that it's possible to add 'table2' => 'db2.', to the prefix array, but it would be useful if this wasn't necessary for every table.

I recognize that this may be intentionally unsupported, but I wanted to report it in case it was an oversight. If it is intentional, it would be helpful to know why.

See also #4745 which discusses the aforementioned prefix configuration as well as cross-database queries in general (referring to them as a "hack" in general, suggesting that this may be intentionally unsupported).

Thanks!