From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Joel Jacobson <joel(at)compiler(dot)org> |
Cc: | Vik Fearing <vik(at)postgresfriends(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Idea: Avoid JOINs by using path expressions to follow FKs |
Date: | 2021-03-28 11:51:19 |
Message-ID: | CAFj8pRAQFff2fsyDgGiizJD2C9-kF5-HVFAa6u791_=o3=zCFw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
ne 28. 3. 2021 v 13:27 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:
> On Sun, Mar 28, 2021, at 12:25, Vik Fearing wrote:
>
> On 3/27/21 9:27 PM, Joel Jacobson wrote:
> > Imagine if we could simply write the SQL query like this:
> >
> > SELECT DISTINCT od.order_id.customer_id.company_name
> > FROM order_details AS od
> > WHERE od.product_id.product_name = 'Chocolade';
> >
> > I took the inspiration for this syntax from SQL/JSON path expressions.
>
> This is a terrible idea, but let me explain why.
>
> First of all, neo4j claims they don't have joins because they actually
> don't have joins. The nodes point directly to the other nodes. Your
> proposal is syntactic sugar over a real join. The equivalent to neo4j
> would be somehow storing the foreign ctid in the column or something.
>
> Secondly, and more importantly IMO, graph queries are coming to SQL.
> They are mostly based on Cypher but not entirely because they amalgamate
> concepts from other graph query languages like Oracle's PGQ. The
> "common" syntax is called GQL (https://www.gqlstandards.org/) and it's
> in the process of becoming Part 16 of the SQL standard. The timeline on
> that website says August 2022 (it started in September 2019).
>
> If that timeline holds and ambitious people work on it (I already know
> one who is; not me), I would expect this to be in PostgreSQL 16 or 17.
> The earliest your proposal could get in is 15, so it's not that much of
> a wait.
>
>
> I think you misunderstood my idea entirely.
>
> It has absolutely nothing to do with graph query languages,
> except the one similarity I mentioned, not having joins.
>
> What I propose is a way to do implicit joins by following foreign keys,
> thus improving the SQL query language, making it more concise for
> the simple case when what you want to do is an INNER JOIN on a
> single column on which there is a single FOREIGN KEY.
>
There were some similar tools already. Personally I like the current
state, where tables should be explicitly defined, and join should be
explicitly defined. The joining of tables is not cheap - and I like the
visibility of this. On the other hand, this is very frustrable for a lot of
people, and I can understand. I don't want to see this feature inside
Postgres, because it can reduce the possibility to detect badly written
query. But it can be a great feature of some outer tool. I worked for
GoodData and this tool knows the model, and it generates necessary joins
implicitly, and people like it (this tool uses Postgres too).
Regards
Pavel
> /Joel
>
From | Date | Subject | |
---|---|---|---|
Next Message | Joel Jacobson | 2021-03-28 12:15:42 | Re: Idea: Avoid JOINs by using path expressions to follow FKs |
Previous Message | Joel Jacobson | 2021-03-28 11:48:35 | Re: pl/pgsql feature request: shorthand for argument and local variable references |