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

151–160 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#151

Post by Latty »

dolmen wrote: post #141
exabrial wrote: post #110

Earlier quoted context omitted.

I think this is an operator problem. You're using the wrong tool for the job. TablePlus, SequelAce, the official MySQL client all support cntrl-space autocompletion. I wish we used Postgres, but I imagine the landscape is the same. The big box databases like Oracle, DB2 undoubtedly having this tooling as well. That being said, here is our fk naming convention: `fk-asset_types->asset_categories` which pretty states wh…

SQL is not only written in an SQL client. SQL is also written (and read from) embedded/mixed in an other programming language were tooling is not always available. Having to know the names of foreign keys (in addition to the column names of the 2 tables) is adding more cognitive load. I don't think that is an improvement.

At least when it comes to both JetBrains and VSCode, they can handle one language embedded in another. I'm kind of surprised there are environments that don't handle that these days.

Re: SQL language proposal: JOIN FOREIGN

#152

Post by tpetry »

orthoxerox wrote: post #94
xyzzy_plugh wrote: post #82

Earlier quoted context omitted.

Isn't this just SELECT DISTINCT ...?

No. What icambron and I would like is something that can get rid of this group by: select division_name, branch_name, dealer_id, dealer_name, quarter, month, sum(total_paid) from divsions, branches, dealers, transactions where ..... --buncha joins group by division_name, branch_name, dealer_id, dealer_name, quarter, month If I didn't want to group the result by division_name, branch_name, dealer_id, dealer_name, quar…

In PostgreSQL you can GROUP BY on the primary key so you can omit specifying all other columns

Re: SQL language proposal: JOIN FOREIGN

#153

Post by slotrans »

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 is heap/clustered, and literally every other implementation detail of the RDBMS.

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.

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, is critical to the value prop of the relational model and a big reason why it's been so hyper-successful.

So no, let's not do this.

Re: SQL language proposal: JOIN FOREIGN

#154

Post by mtone »

JoelJacobson wrote: post #99
tqi wrote: post #44

Earlier quoted context omitted.

The person you are responding to was more direct/harsh than was necessary, but I think it would be good to step back for a moment and reflect on the feedback from this community. You said "If someone can convince me this is a bad idea, that would help me forget about all of this, so I would greatly appreciate your thoughts, no matter how negative or positive." I think there are enough valid objections here to at leas…

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 Dynamics/Dataverse) which doesn't provide direct SQL access and is not supported by common ORMs. Some of the data APIs require relationship schema information which invariably put a needle in attempts to generalize functionality, or just consume.

In this context, creating a simple in-memory test database or serializing records between modules, cannot possibly function without also knowing schema information if queries are going to make use of it. So now you need to carry schema information for everything, and load it at run-time from generated code or a live database, in back-end and front-end, just to interact with data where simple SQL would have worked just fine.

Conclusion: I dislike the proposal -- even if not breaking backward compatibility, it brings database-configuration details to SQL. SQL is imperfect and is already hurt by database-implementation details (like date functions) but it remains a beautiful expression of relational algebra and set theory - a query given the same data should return the same output, regardless of context. SQL is lingua franca for a reason.

The proposal feels like a fairly specific developer-centric extension and isn't where SQL should be headed, in my opinion.

Re: SQL language proposal: JOIN FOREIGN

#155

Post by gigatexal »

tapas73 wrote: post #148
clintonb wrote: post #47

Earlier quoted context omitted.

How is this more explicit than specifying join columns today?

It enforces foreign keys during select. One can say that it makes you explicitly state, which foreign key do you use. "on a.foo = b.bar" can be a incomplete and/or incorrect join condition.

Exactly this.

Re: SQL language proposal: JOIN FOREIGN

#156

Post by srcreigh »

We should reject this simply because it does not provide any clear benefits, but meanwhile asks for a lot. People to learn the FK names in their DB, people to learn new SQL syntax (and tooling to support it).

It's actually difficult to list all the problems with this proposal. It's just unnecessary and the effort required to predict all the problems it will cause isn't worth doing.

Re: SQL language proposal: JOIN FOREIGN

#157

Post by hnthrowaway0315 »

dolmen wrote: post #144
hnthrowaway0315 wrote: post #6

What I really need is something like: SELECT -col1, -col14 FROM table LIMIT 50; Where the minus sign means I don't want these two columns. I still don't see a way to do it easily (for Vertica and in Datagrip).

> SELECT -col1, -col14 FROM table LIMIT 50; This is already valid SQL. Example: SELECT -col1, -col4 FROM (SELECT 1 AS col1, 2 AS col4) AS tbl; Do you think seriously that a new meaning could ever be attached to that syntax?

I just say something similar, doesn't have to be the exact form.

Re: SQL language proposal: JOIN FOREIGN

#158

Post by hn_throwaway_99 »

tapas73 wrote: post #139
mrweasel wrote: post #36

Earlier quoted context omitted.

Yeah, my concern is people in the real world. If you can "magically" join on a foreign key and leave the table name out, or still call the thing anything you want, some smart ass will abuse the live crap out of it and I'll somehow be left to deal with it. I like things to be explicit. Tell me what you're joining and how you want to join it. What is the use case for this? Other than saving some typing, and let's face…

First, very valid concern regarding table name. > Other than saving some typing, I would like to point out the corectness aspect of the proposal. Today foreign keys are enforced during inserts, updates, deletes. (you just can't violate fk. this is so good) But you can violate it in selects. (e.g. mistype column name, forget to include a column) This proposal (or its adjustment) would allow to use fk also during selec…

In my entire software development career, I've never had that problem, nor seen it.

Sure, I've mistyped a column name or left one out. But the failure is then nearly always so catastrophic (the query failed to compile), or the data response then so different from what I'd expect (pages of results instead on 1) that it's not something I've seen that needs fixing.

Re: SQL language proposal: JOIN FOREIGN

#159

Post by JoelJacobson »

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_target ] conflict_action
where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name
[1] https://www.postgresql.org/docs/current/sql-insert.html

Re: SQL language proposal: JOIN FOREIGN

#160

Post by JoelJacobson »

DevKoala wrote: post #135
JoelJacobson wrote: post #85

Earlier quoted context omitted.

Nice example! The join you describe would remain as a JOIN ON. This is per design. Quote from the proposal: "The idea is to improve the SQL language, specifically the join syntax, for the special but common case when joining on foreign key columns." ... "If the common simple joins (when joining on foreign key columns) would be written in a different syntax, the remaining joins would visually stand out and we could fo…

But how could you accurately tell if some queries join on the foreign key, but were written by someone without knowledge of the new specification?

Not sure I understand, can please elaborate? Perhaps with an example?
Post reply on HN