Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL language proposal: JOIN FOREIGN

gist.github.com

Post reply on HN
Print view View original

161–170 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#161

Post by bradleyjg »

JoelJacobson wrote: post #159
slotrans wrote: post #153

This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables . Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table…

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1] INSERT INTO ... ON CONFLICT [ conflict_ta…

PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.

Re: SQL language proposal: JOIN FOREIGN

#162

Post by nrabulinski »

antender wrote: post #120

I, personally, feel like this is a rather pointless addition to an already somewhat bloated language. It it isn't saving that much typing (we have NATURAL JOIN's already, nobody is using them), is kind of inconsistent with other language principles (we should be able to join anything to anything if we need to, see SQL-89) and limits JOIN flexibility (you don't have to join with "=" operator, JOIN's supports different…

Nobody is using NATURAL because usually it’s broken and doesn’t do what you want it to and for any more complex joins or joins on other than = you would still use JOIN ON, what’s the issue? JOIN was introduced when we had multiple tables in the FROM clause and conditions in WHERE for clarity and convenience, why are people so opposed to this change then?

Re: SQL language proposal: JOIN FOREIGN

#163

Post by [deleted] »

slotrans wrote: post #153

This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables . Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table…

[deleted]

Re: SQL language proposal: JOIN FOREIGN

#164

Post by JoelJacobson »

bradleyjg wrote: post #161
JoelJacobson wrote: post #159

Earlier quoted context omitted.

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1] INSERT INTO ... ON CONFLICT [ conflict_ta…

PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.

The INSERT ... ON CONFLICT UPDATE patch has been documented here: https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemente...

There are some clear benefits possible only thanks to specifying constraint names. Quoted from the link:

* The SQL-standard MERGE doesn't provide for choosing an index, so use of a unique index would need to be conditioned on equality quals against indexed columns in order to provide the behavior being discussed for UPSERT.

* The ON expression will need to be evaluated to see whether it properly compares to a unique index on the target table. Initially this will need to be done to determine whether the MERGE is allowed at all; later it will determine which sort of plan is allowed. It would be easier to match an index name, provided the index name is known and stable.

Re: SQL language proposal: JOIN FOREIGN

#165

Post by JoelJacobson »

mtone wrote: post #154
JoelJacobson wrote: post #99

Earlier quoted context omitted.

When I wrote that comment, the idea had even more flaws than currently, at that time I suggested using "WITH" and a new "->" operator. Thanks to new ideas coming from other users in the PostgreSQL and Hacker News community, those problems have been solved, and we now have less remaining problems with the proposal. I'm really grateful for all the help. Like I said in another reply, I will put together a "Drawbacks / R…

I also agree that relying on database schema is a show stopper. First counterpoint: As long as data type information isn't completely messed up, I can dump excel spreadsheets in a database, or dump database CSVs in a data lake, and start querying them right away using SQL with complex joins using auto-completion from the dataset alone. Second counterpoint (harder to communicate): I work with a SaaS database (MS Dynam…

> First counterpoint: As long as data type information isn't completely messed up, I can dump excel spreadsheets in a database, or dump database CSVs in a data lake, and start querying them right away using SQL with complex joins using auto-completion from the dataset alone.

In your example, all you have is data and no foreign keys, that's the show stopper? That means you have all the relationships in your head and that's how you can write complex joins right away? Sure, if that's the case, then you can't use foreign keys since you don't have any. Don't see how this would be a counterpoint though. There is nothing forcing you to use JOIN FOREIGN, you could just do what you describe. But I'm sure you are aware many databases have foreign keys for all relationships to enforce referential integrity. I should have mentioned in the proposal, the scope is limited to such databases.

I enjoyed your example though. I want to share a similar example. It happened to me at least a few times, I've had to deal with data, shipped as multiple CSV files, but without any schema at all. What I tend to do then is to quickly write a very loose data model with mostly text columns, to accept any values. Once the CSVs are in SQL, I can then clean up the data step by step, by inspecting the tables and converting the text columns to proper data types. Next, when suspecting some column(s) in some table seem to be referencing some other column(s) in some other table, based on the content of the columns in both tables, I then try to add a FOREIGN KEY with a suitable name between such column(s). If successful, we know there is referential integrity between the columns, and we know also have a name to describe such relationship. Win-win! Otherwise if the foreign key could not be created, I investigate what rows that only appear in the referencing table that are not present in the referenced table, using a NOT EXISTS (...) query. If the extra rows can safely be deleted, such as if e.g. forgetting to handle empty string values as NULL values, I can then try to create the foreign key again.

Re: SQL language proposal: JOIN FOREIGN

#166

Post by [deleted] »

JoelJacobson wrote: post #159
slotrans wrote: post #153

This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables . Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table…

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1] INSERT INTO ... ON CONFLICT [ conflict_ta…

[deleted]

Re: SQL language proposal: JOIN FOREIGN

#167

Post by [deleted] »

JoelJacobson wrote: post #164
bradleyjg wrote: post #161

Earlier quoted context omitted.

PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.

The INSERT ... ON CONFLICT UPDATE patch has been documented here: https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemente... There are some clear benefits possible only thanks to specifying constraint names. Quoted from the link: * The SQL-standard MERGE doesn't provide for choosing an index, so use of a unique index would need to be conditioned on equality quals against indexed columns in order to provide the b…

[deleted]

Re: SQL language proposal: JOIN FOREIGN

#168

Post by dstola »

Not a fan since proposal would do an implicit join on foreign keys rather than explicit join on the columns. If you know the schemas its great, but would add an extra step to check FKs on the tables otherwise

In general explicit > implicit, IMO

Re: SQL language proposal: JOIN FOREIGN

#169

Post by cryptonector »

slotrans wrote: post #153

This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables . Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table…

> and produce correct results

This doesn't interfere with that.

> relying only on the structure and content of tables

Constraints are part of the table schema, not index schema.

> his proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed.

Not allowed says who? The standard? I doubt it, and anyways, it can be changed.

"That's not allowed" is not a good argument. A better argument is that this is the first time a constraint can change the meaning of a query -- that is a good argument, but it would be better if the constraint could change existing queries, which it does not do. Because this would only affect queries that refer to the constraint, this seems quite allowable to me.

> More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, ...

This is not about optimization, ergo this argument is out.

I'm not sure I want this particular extension, but I don't buy your arguments against it.

Re: SQL language proposal: JOIN FOREIGN

#170

Post by cryptonector »

JoelJacobson wrote: post #159
slotrans wrote: post #153

This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables . Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table…

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1] INSERT INTO ... ON CONFLICT [ conflict_ta…

That's DML, not DQL. Still, I disagree with GP's arguments (see separate comment).
Post reply on HN