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

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.  Feels a bit like JOIN NATURAL, which many
people discourage because of this problem.

-- 
Álvaro Herrera                            39°49'30"S 73°17'W
Si no sabes adonde vas, es muy probable que acabes en otra parte.

On 3/27/21 9:27 PM, Joel Jacobson wrote:
> Hi,
> 
> The database Neo4j has a language called "Cypher" where one of the key selling points is they "don’t need join
tables".
> 
> Here is an example from https://neo4j.com/developer/cypher/guide-sql-to-cypher/
> 
> SQL:
> 
> SELECT DISTINCT c.company_name
> FROM customers AS c
> JOIN orders AS o ON c.customer_id = o.customer_id
> JOIN order_details AS od ON o.order_id = od.order_id
> JOIN products AS p ON od.product_id = p.product_id
> WHERE p.product_name = 'Chocolade';
> 
> Neo4j's Cypher:
> 
> MATCH (p:product {product_name:"Chocolade"})<-[:PRODUCT]-(:order)<-[:PURCHASED]-(c:customer)
> RETURN distinct c.company_name;
> 
> 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.
-- 
Vik Fearing

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.

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. 

Feels a bit like JOIN NATURAL, which many

people discourage because of this problem.

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/

On 3/27/21 5:11 PM, 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.  Feels a bit like JOIN NATURAL, which many
> people discourage because of this problem.
>


Maybe. I don't recall ever having seen a column with more than one FK.
Is that a common thing? In itself it seems like a bad idea.

Not saying I think this suggestion is a good idea, though. We've seen
many frameworks that hide joins, and the results are ... less than
universally good. If your application programmers don't like using
joins, then either a) you should have the DBA create some views for them
that contain the joins, or b) you have the wrong application programmers -:)


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Andrew Dunstan <andrew@dunslane.net> writes:
> On 3/27/21 5:11 PM, Alvaro Herrera wrote:
>> 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.  Feels a bit like JOIN NATURAL, which many
>> people discourage because of this problem.

> Maybe. I don't recall ever having seen a column with more than one FK.
> Is that a common thing? In itself it seems like a bad idea.

Yeah, that aspect seems like a complete show-stopper.  We have a way
to enforce that you can't *drop* a constraint that some stored view
depends on for semantic validity.  We don't have a way to say that
you can't *add* a constraint-with-certain-properties.  And I don't
think it'd be very practical to do (consider race conditions, if
nothing more).

However, that stumbling block is just dependent on the assumption
that the foreign key constraint being used is implicit.  If the
syntax names it explicitly then you just have a normal constraint
dependency and all's well.

You might be able to have a shorthand notation in which the constraint
isn't named and the system will accept it as long as there's just one
candidate (but then, when dumping a stored view, the constraint name
would always be shown explicitly).  However I'm not sure that the
"shorthand" would be any shorter.  I'm imagining a syntax in which
you give the constraint name instead of the column name.  Thought
experiment: how could the original syntax proposal make any use of
a multi-column foreign key?

> Not saying I think this suggestion is a good idea, though. We've seen
> many frameworks that hide joins, and the results are ... less than
> universally good.

Yeah, I'm pretty much not sold on this idea either.  I think it would
lead to the same problems we see with ORMs, namely that people write
queries that are impossible to execute efficiently and then blame
the database for their poor choice of schema.

            regards, tom lane

Andrew Dunstan <andrew@dunslane.net> writes:

> Maybe. I don't recall ever having seen a column with more than one FK.

> Is that a common thing? In itself it seems like a bad idea.

Yeah, that aspect seems like a complete show-stopper.  We have a way

to enforce that you can't *drop* a constraint that some stored view

depends on for semantic validity.  We don't have a way to say that

you can't *add* a constraint-with-certain-properties.  And I don't

think it'd be very practical to do (consider race conditions, if

nothing more).

On 3/28/21 10:04 AM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> On 3/27/21 5:11 PM, Alvaro Herrera wrote:
>>> 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.  Feels a bit like JOIN NATURAL, which many
>>> people discourage because of this problem.
>> Maybe. I don't recall ever having seen a column with more than one FK.
>> Is that a common thing? In itself it seems like a bad idea.
> Yeah, that aspect seems like a complete show-stopper.  We have a way
> to enforce that you can't *drop* a constraint that some stored view
> depends on for semantic validity.  We don't have a way to say that
> you can't *add* a constraint-with-certain-properties.  And I don't
> think it'd be very practical to do (consider race conditions, if
> nothing more).
>
> However, that stumbling block is just dependent on the assumption
> that the foreign key constraint being used is implicit.  If the
> syntax names it explicitly then you just have a normal constraint
> dependency and all's well.
>
> You might be able to have a shorthand notation in which the constraint
> isn't named and the system will accept it as long as there's just one
> candidate (but then, when dumping a stored view, the constraint name
> would always be shown explicitly).  However I'm not sure that the
> "shorthand" would be any shorter.  I'm imagining a syntax in which
> you give the constraint name instead of the column name.  Thought
> experiment: how could the original syntax proposal make any use of
> a multi-column foreign key?


I guess we could have a special operator, which allows the LHS to be
either a column (in which case it must have only one single-valued FK
constraint) or a constraint name in which case it would match the
corresponding columns on both sides.


It gets kinda tricky though, as there are FKs going both ways:


    customers <- orders <- order_details -> products


and in fact this could make composing the query LESS clear. The natural
place to start this query (show me the name of every customer who
ordered chocolate) is with orders ISTM, but the example given starts
with order_details which seems somewhat unnatural.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On 3/28/21 1:26 PM, Joel Jacobson wrote:
> 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.

In that case, I oppose this suggestion.

> 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.

SQL is not an implicit language, though.

I wouldn't mind something like

  FROM a JOIN b WITH a_b_fk

or something.  That would be really nice when the keys are multicolumn.
But I don't want an implicit join like you're suggesting.
-- 
Vik Fearing

I'm imagining a syntax in which

you give the constraint name instead of the column name.  Thought

experiment: how could the original syntax proposal make any use of

a multi-column foreign key?

> Not saying I think this suggestion is a good idea, though. We've seen

> many frameworks that hide joins, and the results are ... less than

> universally good.

Yeah, I'm pretty much not sold on this idea either.  I think it would

lead to the same problems we see with ORMs, namely that people write

queries that are impossible to execute efficiently and then blame

the database for their poor choice of schema.

On 3/29/21 11:59 AM, Joel Jacobson wrote:
> On Sun, Mar 28, 2021, at 16:04, Tom Lane wrote:
>> I'm imagining a syntax in which
>> you give the constraint name instead of the column name.  Thought
>> experiment: how could the original syntax proposal make any use of
>> a multi-column foreign key?
> 
> Thanks for coming up with this genius idea.
> 
> At first I didn't see the beauty of it; I wrongly thought the constraint name needed to be
> unique per schema, but I realize we could just use the foreign table's name
> as the constraint name, which will allow a nice syntax:
> 
> SELECT DISTINCT order_details.orders.customers.company_name
> FROM order_details
> WHERE order_details.products.product_name = 'Chocolade';
> 
> Given this data model:
> 
> CREATE TABLE customers (
> customer_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
> company_name text,
> PRIMARY KEY (customer_id)
> );
> 
> CREATE TABLE orders (
> order_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
> customer_id bigint NOT NULL,
> PRIMARY KEY (order_id),
> CONSTRAINT customers FOREIGN KEY (customer_id) REFERENCES customers
> );
> 
> CREATE TABLE products (
> product_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
> product_name text NOT NULL,
> PRIMARY KEY (product_id)
> );
> 
> CREATE TABLE order_details (
> order_id bigint NOT NULL,
> product_id bigint NOT NULL,
> PRIMARY KEY (order_id, product_id),
> CONSTRAINT orders FOREIGN KEY (order_id) REFERENCES orders,
> CONSTRAINT products FOREIGN KEY (product_id) REFERENCES products
> );


If you write your schema like this, then it becomes standards compliant:

CREATE TYPE customers AS (
    company_name text
);
CREATE TABLE customers OF customers (
    REF IS customer_id SYSTEM GENERATED
);

CREATE TYPE orders AS (
    customer REF(customers) NOT NULL
);
CREATE TABLE orders OF orders (
    REF IS order_id SYSTEM GENERATED
);

CREATE TYPE products AS (
    product_name text
);
CREATE TABLE products OF products (
    REF IS product_id SYSTEM GENERATED
);

CREATE TABLE order_details (
    "order" REF(orders),
    product REF(products),
    quantity integer,
    PRIMARY KEY ("order", product)
);


And the query would be:

SELECT DISTINCT order_details."order"->customer->company_name
FROM order_details
WHERE order_details.product->product_name = 'Chocolade';


Postgres already supports most of that, but not all of it.
-- 
Vik Fearing

If you write your schema like this, then it becomes standards compliant:

...

CREATE TABLE order_details (

    "order" REF(orders),

    product REF(products),

    quantity integer,

    PRIMARY KEY ("order", product)

);

And the query would be:

SELECT DISTINCT order_details."order"->customer->company_name

FROM order_details

WHERE order_details.product->product_name = 'Chocolade';

Postgres already supports most of that, but not all of it.

CREATE TABLE order_details (

    "order" REF(orders),

    product REF(products),

    quantity integer,

    PRIMARY KEY ("order", product)

);

And the query would be:

SELECT DISTINCT order_details."order"->customer->company_name

FROM order_details

WHERE order_details.product->product_name = 'Chocolade';

Postgres already supports most of that, but not all of it.

On 3/29/21 7:55 PM, Joel Jacobson wrote:
> On Mon, Mar 29, 2021, at 16:17, Vik Fearing wrote:
>> CREATE TABLE order_details (
>>     "order" REF(orders),
>>     product REF(products),
>>     quantity integer,
>>     PRIMARY KEY ("order", product)
>> );
>>
>>
>> And the query would be:
>>
>> SELECT DISTINCT order_details."order"->customer->company_name
>> FROM order_details
>> WHERE order_details.product->product_name = 'Chocolade';
>>
>>
>> Postgres already supports most of that, but not all of it.
> 
> Do you know if REF is meant to be a replacement for foreign keys?
> 
> Are they a different thing meant to co-exist with foreign keys,
> or are they actually foreign keys "under the hood"
> or something else entirely?

They're supposed to be OOP where each row in the typed table is an
instance of the object.  Types can also have methods associated with
them, and the instance tables can have subtables similar to our table
inheritance.  The dereference operator is replaced by a subquery.

There is a whole slew of things in this area of the standard that
apparently never caught on.
-- 
Vik Fearing

On 3/29/21 7:55 PM, Joel Jacobson wrote:

> Do you know if REF is meant to be a replacement for foreign keys?

> 

> Are they a different thing meant to co-exist with foreign keys,

> or are they actually foreign keys "under the hood"

> or something else entirely?

They're supposed to be OOP where each row in the typed table is an

instance of the object.  Types can also have methods associated with

them, and the instance tables can have subtables similar to our table

inheritance.  The dereference operator is replaced by a subquery.

There is a whole slew of things in this area of the standard that

apparently never caught on.

On Tue, Mar 30, 2021 at 08:03:09AM +0200, Pavel Stehule wrote:
> 
> On second hand, it can be very nice to have some special strict mode in
> Postgres - maybe slower, not compatible, that disallow some dangerous or
> unsafe queries. But it is possible to solve in extensions, but nobody did
> it. Something like plpgsql_check for SQL - who will write sql_check?

The #1 cause of problems is probably unqualified outer references, and
unfortunately I don't think it's really possible to detect that in an
extension, as the required information is only available in the raw parsetree.

On Tue, Mar 30, 2021 at 09:02:39AM +0200, Pavel Stehule wrote:
> út 30. 3. 2021 v 8:52 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:
> 
> > On Tue, Mar 30, 2021 at 08:03:09AM +0200, Pavel Stehule wrote:
> > >
> > > On second hand, it can be very nice to have some special strict mode in
> > > Postgres - maybe slower, not compatible, that disallow some dangerous or
> > > unsafe queries. But it is possible to solve in extensions, but nobody did
> > > it. Something like plpgsql_check for SQL - who will write sql_check?
> >
> > The #1 cause of problems is probably unqualified outer references, and
> > unfortunately I don't think it's really possible to detect that in an
> > extension, as the required information is only available in the raw
> > parsetree.
> >
> 
> the raw parsetree is available  I think. I didn't check it. But it can be
> easy to attach or attach a copy to Query structure. Maybe there is no
> necessary hook. But it can be a good reason for implementing a post parsing
> hook.

It's not available in any existing hook.  And even if it was you would have to
import most of transformTopLevelStmt() and all underlying functions to be able
to detect this case I think.  This should be best done in core postgres.

> It should be easy to check if all joins are related to foreign key
> constraints.

Yes, and also if the referenced columns are covered by indexes for instance.
My concern is mostly that you won't be able to cover the unqualified outer
references, which can lead to wrong query results rather than just slow
queries.

Maybe there were no technical problems.  Just this technology was coming at a bad time.  The people who needed (wanted) OOP access to data got the Hibernate, and there was no necessity to do this work on SQL level. In this time, there was possibility to use GUI for databases, and in this time there were a lot of graphic query designers.

I don't like the idea of foreign key constraint names - it doesn't look comfortable to me.  I don't say it is a bad idea, but it is not SQL, and I am not sure if it needs more or less work than explicitly to write PK=FK.

On second hand, it can be very nice to have some special strict mode in Postgres - maybe slower, not compatible, that disallow some dangerous or unsafe queries. But it is possible to solve in extensions, but nobody did it. Something like plpgsql_check for SQL - who will write sql_check?

I think this is a good example of where this improves the situation the most,

when you have multiple joins of the same table, forcing you to come up with multiple aliases

for the same table, keeping them all in memory while writing and reading such queries.

...

I remember multiple self joins only when developers used an EAV model. This is an antipattern, and today we have better tools, and we don't need it.  It is scary, because it is completely against the relational model.

For our example data model, this would produce:

ALTER TABLE public.orders RENAME CONSTRAINT orders_customer_id_fkey TO customers;

ALTER TABLE public.order_details RENAME CONSTRAINT order_details_order_id_fkey TO orders;

ALTER TABLE public.order_details RENAME CONSTRAINT order_details_product_id_fkey TO products;

you fix one issue, but you lost interesting informations

On Tue, Mar 30, 2021, at 10:24, Pavel Stehule wrote:

For our example data model, this would produce:

ALTER TABLE public.orders RENAME CONSTRAINT orders_customer_id_fkey TO customers;

ALTER TABLE public.order_details RENAME CONSTRAINT order_details_order_id_fkey TO orders;

ALTER TABLE public.order_details RENAME CONSTRAINT order_details_product_id_fkey TO products;

you fix one issue, but you lost interesting informations

No, it's not lost. It's still there:

# \d order_details

Foreign-key constraints:

    "orders" FOREIGN KEY (order_id) REFERENCES orders(order_id)

    "products" FOREIGN KEY (product_id) REFERENCES products(product_id)

You can still easily find out what tables/columns are referencing/referenced,

by using \d or look in the information_schema.

The primarily reason why this information is duplicated in the default name,

is AFAIK due to avoid hypothetical name conflicts,

which is only a real problem for users who would need to export the schema

to some other SQL database, or use apps that depend on the names to be

unique within the namespace, and not just within the table.

The comment in pg_constraint.c explains this:

/* Select a nonconflicting name for a new constraint.

*

* The objective here is to choose a name that is unique within the

* specified namespace.  Postgres does not require this, but the SQL

* spec does, and some apps depend on it.  Therefore we avoid choosing

* default names that so conflict.

/Joel

On Sat, 27 Mar 2021 at 16:28, Joel Jacobson <joel@compiler.org> 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 regularly do this type of thing via views. It's a bit confusing as writes go to one set of tables while selects often go through the view with all the details readily available.

I think I'd want these shortcuts to be well defined and obvious to someone exploring via psql. I can also see uses where a foreign key might not be available (left join rather than join).

I wonder if GENERATED ... VIRTUAL might be a way of defining this type of added record.

ALTER TABLE order ADD customer record GENERATED JOIN customer USING (customer_id) VIRTUAL;

ALTER TABLE order_detail ADD order record GENERATED JOIN order USING (order_id) VIRTUAL;

SELECT order.customer.company_name FROM order_detail;

Of course, if they don't reference the GENERATED column then the join isn't added to the query.

SELECT DISTINCT order_details."order"->customer->company_name

FROM order_details

WHERE order_details.product->product_name = 'Chocolade';

On Tue, 30 Mar 2021 at 14:30, Joel Jacobson <joel@compiler.org> wrote:

If the expression ends with a column_name,

you get the value for the column.

If the expression ends with a constraint_name,

you get the referenced table as a record.

Can’t you just leave off the “ends with a column_name” part? If you want one of its columns, just put .column_name:

table -> constraint -> ... -> constraint . column_name

Then you know that -> expects a constraint_name and only that to its right.

Also, should the join be a left join, which would therefore return a NULL when there is no matching record? Or could we have a variation such as ->? to give a left join (NULL when no matching record) with -> using an inner join (record is not included in result when no matching record).

For the record I would find something like this quite useful. I constantly find myself joining in code lookup tables and the like, and while from a mathematical view it’s just another join, explicitly listing the table in the FROM clause of a large query does not assist with readability to say the least.

On Tue, 30 Mar 2021 at 15:33, Joel Jacobson <joel@compiler.org> wrote:

Also, should the join be a left join, which would therefore return a NULL when there is no matching record? Or could we have a variation such as ->? to give a left join (NULL when no matching record) with -> using an inner join (record is not included in result when no matching record).

Interesting idea, but I think we can keep it simple, and still support the case you mention:

If we only have -> and you want to exclude records where the column is NULL (i.e. INNER JOIN),

I think we should just use the WHERE clause and filter on such condition.

Just to be clear, it will always be a left join? Agreed that getting the inner join behaviour can be done in the WHERE clause. I think this is a case where simple is good. As long as the left join case is supported I'm happy.

 

Thanks for the encouraging words. I have exactly the same experience myself and share your view.

I look forward to continued discussion on this matter.

I had another idea: maybe the default name of a foreign key constraint to a primary key should simply be the name of the target table? That is, if I say:

FOREIGN KEY (...) REFERENCES t

... then unless the table name t is already in use as a constraint name, it will be used as the constraint name. It would be nice not to have to keep repeating, like this:

CONSTRAINT t FOREIGN KEY (...) REFERENCES t

On Wed, Mar 31, 2021 at 12:50:19AM +0200, Joel Jacobson wrote:
> On Tue, Mar 30, 2021, at 22:01, Isaac Morland wrote:
> > On Tue, 30 Mar 2021 at 15:33, Joel Jacobson <joel@compiler.org> wrote:
> >>> Also, should the join be a left join, which would therefore return a NULL when there is no matching record? Or
couldwe have a variation such as ->? to give a left join (NULL when no matching record) with -> using an inner join
(recordis not included in result when no matching record).
 
> >> 
> >> Interesting idea, but I think we can keep it simple, and still support the case you mention:
> >> 
> >> If we only have -> and you want to exclude records where the column is NULL (i.e. INNER JOIN),
> >> I think we should just use the WHERE clause and filter on such condition.
> >> 
> > 
> > Just to be clear, it will always be a left join? Agreed that getting the inner join behaviour can be done in the
WHEREclause. I think this is a case where simple is good. As long as the left join case is supported I'm happy.
 
> 
> Hmm, I guess, since technically, if all foreign key column(s) are declared as NOT NULL, we would know for sure such
valuesexist, so a LEFT JOIN and INNER JOIN would always produce the same result.
 
> I'm not sure if the query planner could produce different plans though, and if an INNER JOIN could be more efficient.
Ifit matters, then I think we should generate an INNER JOIN for the "all column(s) NOT NULL" case.
 

I'm not sure who is supposed to be the target for this proposal.

As far as I understand this won't change the fact that users will still have to
understand the "relational" part of RDBMS, understand what is a JOIN
cardinality and everything that comes with it.  So you think that people who
are too lazy to learn the proper JOIN syntax will still bother to learn about
relational algebra and understand what they're doing, and I'm very doubtful
about that.

You also think that writing a proper JOIN is complex, but somehow writing a
proper WHERE clause to subtly change the query behavior is not a problem, or
that if users want to use aggregate or anything more complex then they'll
happily open the documentation and learn how to do that.  In my experience what
will happen is that instead users will keep using that limited subset of SQL
features and build creative and incredibly inefficient systems to avoid using
anything else and will then complain that postgres is too slow.

As an example just yesterday some user complained that it's not possible to
write a trigger on a table that could intercept inserting a textual value on an
integer field and replace it with the referenced value.  And he rejected our
suggested solution to replace the "INSERT INTO sometable VALUES..." with
"INSERT INTO sometable SELECT ...".  And no this proposal would not have
changed anything because changing the python script doing the import to add
some minimal SQL knowledge was apparently too problematic.  Instead he will
insert the data in a temporary table and dispatch everything on a per-row
basis, using triggers.  So here again the problem wasn't the syntax but having
to deal with a relational rather than an imperative approach.

Even if I'm totally wrong about that, I still think your proposal will lead to
problematic or ambiguous situation unless you come up with a syntax that can
fully handle the JOIN grammar.  For instance, what should happen if the query
also contains an explicit JOIN for the same relation?  I can see many reason
why this would happen with this proposal given the set of features it can
handle.  For instance:

- you want multiple JOIN.  Like one OUTER JOIN and one INNER JOIN for the same
  relation
- you want to push predicates on an OUTER JOIN

On Wed, Mar 31, 2021 at 12:50:19AM +0200, Joel Jacobson wrote:

> On Tue, Mar 30, 2021, at 22:01, Isaac Morland wrote:

> > On Tue, 30 Mar 2021 at 15:33, Joel Jacobson <joel@compiler.org> wrote:

> >>> Also, should the join be a left join, which would therefore return a NULL when there is no matching record? Or could we have a variation such as ->? to give a left join (NULL when no matching record) with -> using an inner join (record is not included in result when no matching record).

> >> 

> >> Interesting idea, but I think we can keep it simple, and still support the case you mention:

> >> 

> >> If we only have -> and you want to exclude records where the column is NULL (i.e. INNER JOIN),

> >> I think we should just use the WHERE clause and filter on such condition.

> >> 

> > 

> > Just to be clear, it will always be a left join? Agreed that getting the inner join behaviour can be done in the WHERE clause. I think this is a case where simple is good. As long as the left join case is supported I'm happy.

> 

> Hmm, I guess, since technically, if all foreign key column(s) are declared as NOT NULL, we would know for sure such values exist, so a LEFT JOIN and INNER JOIN would always produce the same result.

> I'm not sure if the query planner could produce different plans though, and if an INNER JOIN could be more efficient. If it matters, then I think we should generate an INNER JOIN for the "all column(s) NOT NULL" case.

I'm not sure who is supposed to be the target for this proposal.

As far as I understand this won't change the fact that users will still have to

understand the "relational" part of RDBMS, understand what is a JOIN

cardinality and everything that comes with it.  So you think that people who

are too lazy to learn the proper JOIN syntax will still bother to learn about

relational algebra and understand what they're doing, and I'm very doubtful

about that.

You also think that writing a proper JOIN is complex, but somehow writing a

proper WHERE clause to subtly change the query behavior is not a problem, or

that if users want to use aggregate or anything more complex then they'll

happily open the documentation and learn how to do that.  In my experience what

will happen is that instead users will keep using that limited subset of SQL

features and build creative and incredibly inefficient systems to avoid using

anything else and will then complain that postgres is too slow.

As an example just yesterday some user complained that it's not possible to

write a trigger on a table that could intercept inserting a textual value on an

integer field and replace it with the referenced value.  And he rejected our

suggested solution to replace the "INSERT INTO sometable VALUES..." with

"INSERT INTO sometable SELECT ...".  And no this proposal would not have

changed anything because changing the python script doing the import to add

some minimal SQL knowledge was apparently too problematic.  Instead he will

insert the data in a temporary table and dispatch everything on a per-row

basis, using triggers.  So here again the problem wasn't the syntax but having

to deal with a relational rather than an imperative approach.

On Wed, Mar 31, 2021 at 5:19 PM Joel Jacobson <joel@compiler.org> wrote:
>
> If using the -> notation, you would only need to manually
> inspect the tables involved in the remaining JOINs;
> since you could be confident all uses of -> cannot affect cardinality.

Talking about that, do you have some answers to the points raised in
my previous mail, which is how it's supposed to behave when a table is
both join using your "->" syntax and a plain JOIN, how to join the
same table multiple time using this new syntax, and how to add
predicates to the join clause using  this new syntax.

> I think this would be a win also for an expert SQL consultant working
> with a new complex data model never seen before.

By experience if the queries are written with ANSI JOIN it's not
really a problem.  And if it's a new complex data model that was never
seen, I would need to inspect the data model first anyway to
understand what the query is (or should be) doing.

On Wed, Mar 31, 2021 at 5:19 PM Joel Jacobson <joel@compiler.org> wrote:

>

> If using the -> notation, you would only need to manually

> inspect the tables involved in the remaining JOINs;

> since you could be confident all uses of -> cannot affect cardinality.

Talking about that, do you have some answers to the points raised in

my previous mail, which is how it's supposed to behave when a table is

both join using your "->" syntax and a plain JOIN, how to join the

same table multiple time using this new syntax, and how to add

predicates to the join clause using  this new syntax.

SELECT DISTINCT customers.company_name

FROM order_details->products

JOIN order_details->orders->customers

WHERE products.product_name = 'Chocolade';

Maybe I have a different proposal in mind than anybody else, but I don't think there is a problem with multiple joins to the same table. If the joins are via the same constraint, then a single join is enough, and if they are via different constraints, the constraints have unique names.

I think if TA is a table with a foreign key constraint CB to another table TB, then the hypothetical expression:

TA -> CB

really just means:

(select TB from TB where (TB.[primary key columns) = (TA.[source columns of constraint CB]))

You can then add .fieldname to get the required fieldname. The issue is that writing it this way is hopelessly verbose, but the short form is fine. The query planner also needs to be guaranteed to collapse multiple references through the same constraint to a single actual join (and then take all the multiple fields requested).

If TA is a table with a foreign key constraint CB to TB, which has a foreign key constraint CC to TC, then this expression:

TA -> CB -> CC

just means, by the same definition (except I won't expand it fully, only one level):

(select TC from TC where (TC.[primary key columns) = ((TA -> CB).[source columns of constraint CC]))

Which reminds me, I often find myself wanting to write something like a.(f1, f2, f3) = b.(f1, f2, f3) rather than (a.f1, a.f2, a.f3) = (b.f1, b.f2, b.f3). But that's another story

On 3/31/21 6:54 PM, Pavel Stehule wrote:
>>
>>
>>
>> If using the -> notation, you would only need to manually
>> inspect the tables involved in the remaining JOINs;
>> since you could be confident all uses of -> cannot affect cardinality.
>>
>> I think this would be a win also for an expert SQL consultant working
>> with a new complex data model never seen before.
>>
>>
> I did not feel comfortable when I read about this proprietary extension of
> SQL.  I can accept and it can be nice to support ANSI/SQL object's
> referentions, but implementing own syntax for JOIN looks too strange. I
> don't see too strong benefit in inventing new syntax and increasing the
> complexity and possible disorientation of users about correct syntax. Some
> users didn't adopt a difference between old joins and modern joins, and you
> are inventing a third syntax.

I'm with you on this: let's do it the Standard way, or not do it at all.
-- 
Vik Fearing