Re: Idea: Avoid JOINs by using path expressions to follow FKs

On Sat, Mar 27, 2021, at 22:11, Alvaro Herrera wrote:
> On 2021-Mar-27, Joel Jacobson wrote:
>
> > If there would be multiple foreign keys on a column we try to follow,
> > the query planner would throw an error forcing the user to use explicit joins instead.
>
> This seems pretty dangerous -- you just have to create one more FK, and
> suddenly a query that worked perfectly fine, now starts throwing errors
> because it's now ambiguous.

Creating one more FK referencing some other column,
would break queries in the same way USING breaks,
if a column is added which causes ambiguity.

In my experience, it's extremely rare to have multiple different FKs on the same set of columns.
Maybe I'm missing something here, can we think of a realistic use-case?

If such a FK if created, it would break in the same way as USING breaks
if a column is added which causes ambiguity, except this is much less likely to happen than the equivalent use case.

I think this problem is hypothetical compared to the actual problem with USING,
since adding a column with the same name as some existing column actually happens sometimes.

> Feels a bit like JOIN NATURAL, which many
> people discourage because of this problem.

The problem with NATURAL is due to matching based on column names.
My proposal doesn't match on column names at all.
It merely follows the foreign key for a column.
With NATURAL you can also suddenly get a different join,
whereas my proposal at worst will generate an error due to multiple FKs on the same column,
there can never be any ambiguity.

/Joel