Error when attempting to join relations across different databases

[MasanoriOnishi]

Describe the bug

We can't make the code for joining relations across different databases.

To Reproduce

Provide detailed steps to reproduce, an executable script would be best.

1. setup the following code.

ROM.container(
  default: [:sql, 'mysql2://localhost/defalut'], # gateway 1 and has users table
  legacy: [:sql, 'mysql2://localhost/legacy']  # gateway 2 and has other_users table
) 

default_relation = rom.relations[:users]
other_relation = container.relations[:other_users]
  
p default_relation.join(other_relation, container.gateways[:default][:users][:id] => container.gateways[:legacy][:other_users][:default_id])

2. Run the script

SELECT * FROM `users` INNER JOIN `other_users` on (`users`.`id` = `other_users`.`default_id`)

Expected behavior

We want to see the output

SELECT * FROM `default`.`users` INNER JOIN `legacy`.`other_users` on (`default`.`users`.`id` = `legacy`.`other_users`.`default_id`)

My environment

• Affects my production application: YES
• Ruby version: ...2.7.7
• OS: Mac

[flash-gordon]

Different gateways use different connections by design, and joining across different connections is not possible. However, there's no check in the code ensuring you don't do that. The results you see are quite random. I'm not sure if rom-sql can generate identifiers that include the database name, it probably can't. We have a similar problem in PG where explicit schemas in resulting SQL are not supported. Once that support is added we can think of a convenient API for MySQL as well

[MasanoriOnishi]

@flash-gordon Thank you for your kind comment. If the https://github.com/rom-rb/rom-sql/pull/405 is merged, the problem will be solved...??

That's because I think the pr will make it possible. Sequel.qualify("legacy","other_users")

p default_relation.join(Sequel.qualify("legacy", "other_users"), container.gateways[:default][:users][:id] => container.gateways[:legacy][:other_users][:default_id])

[flash-gordon]

Hard to tell, I wouldn't hope for it TBH. Problem is we don't have a proper abstraction for schemas (PG, Oracle)/databases (MySQL) in rom-sql itself, making all our attempts to bypass that leaky. That said, having a method (or even a patch) in one's codebase is not a big deal, IMO. Another approach: DB synonyms may work in MySQL, take a look https://blog.mclaughlinsoftware.com/2013/11/24/mysql-synonym/