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

31–40 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#31

Post by JoelJacobson »

hn_throwaway_99 wrote: post #26
JoelJacobson wrote: post #21

Earlier quoted context omitted.

Good point, but addressable: Simply decouple the relationship definition and referential integrity check, allowing a user to drop the referential integrity check if desired, but keeping the relationship definition. I cannot see why you would not want to at least always store the information a certain table/column(s) references some other table/column(s) in the data model. Enforcing referential integrity is probably g…

At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself . Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. So, what you have proposed, "…

> Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints.

Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc.

> So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of SQL tools out there as it's a huge new feature, for some minor syntactic sugar. Ain't gonna happen.

Why would it be a problem from the tools perspective if the foreign key wasn't actually enforced if the DBA insists on temporarily disabling the enforcement of the FK? If the tool would e.g. be used to insert a row, and the DB would accept it, even though it would violate the FK, what do you suggest would be the problem from the tools perspective?

This is also not a new idea. It's already implemented in MSSQL, see WITH NOCHECK.

Re: SQL language proposal: JOIN FOREIGN

#32

Post by icambron »

piaste wrote: post #28
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).

Similarly, I'd love some form of GROUP BY every column except for It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.

I wish it just had a "group by all the stuff I selected without aggregation" shortcut

Re: SQL language proposal: JOIN FOREIGN

#33

Post by oweiler »

piaste wrote: post #28
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).

Similarly, I'd love some form of GROUP BY every column except for It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.

Can't you use an alias in a group by?

Re: SQL language proposal: JOIN FOREIGN

#34

Post by seadan83 »

btilly wrote: post #17

Please, no. A common problem in data warehousing is that a design with lots of foreign keys becomes slow to load. A standard solution is to move the checks for referential integrity elsewhere, then drop the foreign key constraint. This massively improves load performance. This syntax change means that this solution can't be used because you have no idea what random queries out there might rely on the specific existen…

This, the result of a query should not change depending on a constraint.

Re: SQL language proposal: JOIN FOREIGN

#35

Post by tqi »

JoelJacobson wrote: post #8
hn_throwaway_99 wrote: post #5

This is my "Thanks, I hate it" response. Reason being if you use the example they gave: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.films_did_fkey d You need to implicitly know the table that films_did_fkey points to, because 'd' is just a table alias. I can't think of anywhere else in the SQL standard where you can introduce a table alias without explicitly referencing the table. I…

I think your concern is addressed by the idea further down in proposal; giving the foreign keys the same names as the referenced tables. This example was provided: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.distributors d

1. Just because you can doesn't mean people will. 2. That doesn't work if you need to have multiple keys pointing to the same table (ie owner_id and secondary_owner_id pointing to a users table)

Re: SQL language proposal: JOIN FOREIGN

#36

Post by mrweasel »

hn_throwaway_99 wrote: post #19
JoelJacobson wrote: post #8

Earlier quoted context omitted.

I think your concern is addressed by the idea further down in proposal; giving the foreign keys the same names as the referenced tables. This example was provided: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.distributors d

No, my concern is not that you can also specify the table name that explicitly states the name of both tables, it's that you have proposed a syntax where it is possible to leave off the explicit table name. If you got rid of the syntax that uses the underlying "magic" of needing to know which table the foreign key points to, I'd be more amenable.

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 it, sometimes a little extra typing now, will save you a lot of trouble later. The proposal claims: "The idea is to improve the SQL language", but is it really better?

Re: SQL language proposal: JOIN FOREIGN

#37

Post by Svip »

piaste wrote: post #28
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).

Similarly, I'd love some form of GROUP BY every column except for It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.

You know, you can just write GROUP BY x, y, ...

    SELECT t.i+1, count(*) FROM table t GROUP BY 1
1 in this context means the first selected item (i.e. t.i+1). I know this works in PostgreSQL.

Re: SQL language proposal: JOIN FOREIGN

#38

Post by JoelJacobson »

jimbobmcgee wrote: post #29

Is anyone actually likely to implement these based on some random gist on the internet, or are you just screaming into the void? Because, if you have that kind of clout, I've got an INSERT/SET syntax I'd like to put your way...

I'm a small PostgreSQL contributor since 2010 myself. I probably can't write the whole patch myself, but if there is enough interest, and if we can work out the details and address the problems raised in this thread and elsewhere, I'm pretty confident we can do it. If we ever get there, the next step would be a reference implementation, probably in PostgreSQL, or to discuss a proposal in the SQL committee.

Re: SQL language proposal: JOIN FOREIGN

#39

Post by [deleted] »

JoelJacobson wrote: post #31
hn_throwaway_99 wrote: post #26

Earlier quoted context omitted.

At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself . Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. So, what you have proposed, "…

> Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc. > So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of…

[deleted]

Re: SQL language proposal: JOIN FOREIGN

#40

Post by hn_throwaway_99 »

JoelJacobson wrote: post #31
hn_throwaway_99 wrote: post #26

Earlier quoted context omitted.

At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself . Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. So, what you have proposed, "…

> Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc. > So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of…

> Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc.

The point that everyone is making is there are not currently any SQL statements that depend on structural information as defined in the foreign key relationships when calculating the structure of the data. Furthermore, there are already tons of tooling and processes that depend on this fact, that your proposal would break, for a teeny bit of less typing.

Beating a dead horse at this point.

Post reply on HN