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

ne 28. 3. 2021 v 14:39 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> On Sun, Mar 28, 2021, at 13:51, Pavel Stehule wrote:
>
> 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).
>
> https://www.gooddata.com/
>
>
> Very good points.
>
> As a counter-argument, I could argue that you don't need to use this
> feature.
> But that would be a poor argument, since you might have to work with code
> written by other developers.
>
> I'm also fearful of newbies misusing features, not understanding what they
> are doing, producing inefficient code.
> However, this is a general problem, complex queries are hard to reason
> about,
> and I'm not sure making some INNER JOINs implicit would worsen the
> situation,
> you could also make the counter-argument that the remaining explicit JOINs
> become more visible,
> and will stand-out, exposing what is really complex in the query.
>

It is not the problem only for newbies - yesterday a very experienced user
(I know him personally) reported an issue related to misunderstanding some
behaviour and just some typo, I like some mandatory redundancy in syntax,
because it allows to detect some typo errors. SQL is not consistent in this
- the query is relatively safe, but if you use subqueries, then are not
safe because you can use an outer identifier without qualification, and
what is worse, the identifiers are prioritized - there is not amobigonuous
column check. So SQL has enough traps already, and I am afraid to introduce
some new implicit features.

Theoretically you can introduce own procedural language

CREATE OR REPLACE FUNCTION foo(a int)
RETURNS TABLE (x int, y int) AS $$
SELECT t1.x, t2.y WHERE t3.a = a;
$$ LANGUAGE mylanguage.

It is well wrapped, and well isolated.

> Also, this proposed syntax would surely appeal to the NoSQL-crowd,
> and should reduce their cravings for MongoDB.
>
> So ask yourself the following question: Ten years from now, would you
> rather be forced to
> work with code using MongoDB or a more concise SQL?
>

I am a man who likes SQL - for me, it is a human readable language with a
good level of verbosity and abstraction - all time, when I learned SQL. But
I see that SQL is not a fully "safe" language. It allows bad joins, or
doesn't detect all human errors. It can be a good reason for a new layer
over SQL - some more abstract language. And it can work - I have really
good experience with GoodData query language. This is a transpiler from
domain language to SQL.

I think so every tool, every layer should have a similar level of
abstraction to be well usable.

> Lastly, let me reiterate I think you made a very good point,
> your argument is the heaviest weigh on the negative side of my own scale.
>

:)

> /Joel
>