Cross-source joins fail if tables have same name

[stevenhorner]

Describe the bug
Cross-source joins fail between sources if the table on either side of the join have the same table name. Tested with SQL Server and SQLite sources.

To Reproduce

Originally noticed this with SQL Server Cross-source joins but managed to reproduce with the tutorial data, which is given below.

Using the sakila actor data in the tutorial. test is a copy of sakila data, the following would fail stating actor already exists
sq '@sakila.actor | join(@test.actor, .actor_id) | .first_name'

If you add an alias to the table you wont get the error but it would return all rows and all columns from @sakila left side of the join. I think this is a separate bug regarding alias not working with cross-source joins.

sq '@sakila.actor:s | join(@test.actor:t, .actor_id) | .s.first_name'

If you did a join to a different table in @test it will work as expected

sq '@sakila.actor | join(@test.film_actor, .actor_id) | .first_name'

If you copied the actor table in @test to a new table called actor2 it would work as expected

sq '@sakila.actor | join(@test.actor2, .actor_id) | .first_name'

Limited testing of JSON files as the source also display the same behaviour which obviously don't contain multiple tables but the table is reference as .data.

Expected behaviour

If using the following command I would expect it to join the 2 actor tables together on .actor_id and in this case would return only the .actor from @sakila because it's the left side of the join which is what it states in the docs.

sq '@sakila.actor | join(@test.actor, .actor_id) | .first_name'

If using the following command I would expect it to show me 2 columns of first_name the first from @sakila.actor and the second column would show the same names but from @test.actor

sq '@sakila.actor:s | join(@test.actor:t, .actor_id) | .s.first_name, .t.first_name'

sq version

Paste the output of sq version --yaml into the code block below:

version: v0.48.3
commit: 3ae4104
timestamp: 2024-03-12T02:55:04Z
latest_version: v0.48.3
host:
  platform: linux
  arch: amd64
  kernel: Linux
  kernel_version: 5.4.0-196-generic
  variant: Ubuntu
  variant_version: 20.04.6 LTS (Focal Fossa)

I have also tested on a MacBook with the latest version with the same results.

Source details

I have the same results with any source I have tried SQL Server, SQLite and JSON sources, so not specific to one source type.

# $ sq inspect --overview --yaml @your_source
# REPLACE THIS WITH YOUR OUTPUT
handle: "@your_source"
location: postgres://sakila:[email protected]/sakila
name: sakila
name_fq: sakila.public
schema: public
driver: postgres
db_driver: postgres
db_product: PostgreSQL 12.13 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit
db_version: "12.13"
user: sakila
size: 17359727

Logs

Not provided logs, instead used the tutorial data to enable it to be reproduced.

Screenshots

N/A

Additional context

N/A

[neilotoole]

@stevenhorner Thanks for putting in the work of providing so much detail on the issue, it's much appreciated.

At first glance, that does indeed look like a bug. I'll take a deeper look when there's time, which there's not a lot of these days. If you happen to have a handy PR lying around, it would be most welcome 😄

[YeharaDananjaya]

maybe this solution will solve your problem click