5 ms·

Personally, I think SQLs verbose syntax is a good thing, and increases clarity/interpretability. It reduces cognitive load to have things explicitly listed out.

by tqi 5y ago

Personally, I think SQLs verbose syntax is a good thing, and increases clarity/interpretability. It reduces cognitive load to have things explicitly listed out. Not having the table and column names in the query itself makes it much harder to read and understand queries without prior knowledge of the data model.

rcoveson 5y ago
FWIW we do have `NATURAL JOIN` already, which is a lot worse than this proposal.
- benjiweber 5y ago
  NATURAL JOIN is great because it's like a relational AND. How else will you join with Table Dee and Table Dum? https://twitter.com/benjiweber/status/1476629550608101384 https://twitter.com/benjiweber/status/1476629550608101384 It's just risky if you don't design your schema with relational algebra in mind.
  - cpill 5y ago
    I'm 20 years I have never seen anyone use relational algebra. schemas have to be self documenting albeit only in the real world.
    - salawat 5y ago
      ...I do... All the time. I'm cleaning up a mess of a namespace collision as we speak. I was the only one who saw it coming. I've spent the better part of a month getting people to believe it's an issue.
    - gavinray 5y ago
      SQL is relational algebra, the core of relational algebra is Selection, Projection, Cross-products, Unions, and Set Differences. SQL is a language that implements Relational Algebra/Relational Calculus
  - rcoveson 5y ago
    That is a really interesting case. Correct me if I'm wrong, but I don't think this is a special case where you have to use `NATURAL JOIN` over `JOIN ON`. The problem is just that the Postgres SQL grammar (maybe most/all SQL grammars) require ON or USING in a non-NATURAL join, so you have to specify the (empty) equijoin predicate list yourself, i.e. the identity value: `JOIN ON TRUE`. EDIT: I had another thought about this. I think people not designing with the relational algebra in mind is the heart of the issue, specifically w.r.t. column names. We know that namespaces are a hard problem, and a consequence of that problem is that `NATURAL JOIN` as specified in the relational algebra seems risky, or overly magick-y. It makes what might be an unfortunate coincidence (name collision) into something algebraically impactful. A foreign key join gets around the problem by keeping names and namespaces out of it. It's really doing exactly what `NATURAL JOIN` is supposed to do, but only in the subset of cases where name collisions are meaningful, not coincidental.
bendbro 5y ago
I agree with your point about explicit columns being easier to read, but I still sometimes prefer implicit columns. An example: whenever you have numerous subqueries, all using the same columns, implicit columns are easier to read since there is less text on the screen. It's also less error prone to change just one line rather than numerous lines. This feeds into my view of metaprogramming-like situations. Whenever the code-time-view of a program differs significantly from some runtime-state-view of the program, I think there should be a code-time way to view and perhaps edit both the code-time-view and some kind of runtime-state-view. A programmer shouldn't have to waste time digging through numerous files to evaluate what implementation slots into some dependency injected class, or find out what structure ends up in a python method parameter, or what a preprocessor directive ultimately produces. I know IDEs can handle some of these things, but I think better tools can be produced. More concisely, instead of approaching code as the single and unchanging view of the program, perhaps it would help to approach code as something more dynamic. I have no concrete ideas as to how this would work.
mpolichette 5y ago
I'm with you on this one. I prefer it to be in your face simple to understand over the terse-ness. In the gist example, I actually prefer the SQL-92 approach where we are joining given an explicit comparison condition. Every other implementation seems to be trying to hide details, for what gain? Less typing? In order to use FOREIGN, you will need to know not just what columns a table has, but also their configuration. Which would also require that you have properly configured your tables. While this shouldn't be a hard ask, it does add additional dependency and makes use of this "tool" slightly less "portable" between systems. I have unfortunately seen cases where people will only have foreign keys un-enforced by their table config. As a dev, if you're introduced to a new DB, you wont know immediately if you can use this, and if things are configured wrong, you need to make a pretty significant change to be able to use it. I don't see a lot of harm from adding this syntax however as people are free to not use it and it relies on an existing strict convention.
- JoelJacobson 5y ago
  > I don't see a lot of harm from adding this syntax however as people are free to not use it and it relies on an existing strict convention. This is a good argument I will add to the list. Also interesting to read about un-enforced foreign keys. I haven't used MSSQL myself, the DB in which I heard it's possible, I've only been using PostgreSQL for the last 20 years, and before that MySQL. I think the problems you describe is an argument against a WITH NOCHECK feature, since it could be misused. Maybe it's necessary in some databases still, but at least in PostgreSQL, the FOR KEY SHARE lock solved all the issues with concurrent updates we had at Trustly. The FOR KEY SHARE was a huge patch [1] written mainly by Alvaro Herrera. Thanks to it, Trustly has never since had any performance problems with foreign keys, and they have AFAIK not needed to drop any foreign keys up until today due to locking/performance problems. [1] https://www.commandprompt.com/blog/fixing_foreign_key_deadlocks_part_three/ https://www.commandprompt.com/blog/fixing_foreign_key_deadlo...
tapas73 5y ago
I would argue that conceptually it's not too different from needing to know what tables/columns exist in datamodel. I get that it is not common now to care about FKs when writing selects. But it could be. Tooling can be improved to help here. (show fks, autocomplete) Btw. Everybody seems to concentrate on conciseness, but keep in mind that this helps also with query correctness.

by tqi 5y ago

rcoveson 5y ago

FWIW we do have `NATURAL JOIN` already, which is a lot worse than this proposal.

benjiweber 5y ago
NATURAL JOIN is great because it's like a relational AND. How else will you join with Table Dee and Table Dum? https://twitter.com/benjiweber/status/1476629550608101384 https://twitter.com/benjiweber/status/1476629550608101384 It's just risky if you don't design your schema with relational algebra in mind.
- cpill 5y ago
  I'm 20 years I have never seen anyone use relational algebra. schemas have to be self documenting albeit only in the real world.
  - salawat 5y ago
    ...I do... All the time. I'm cleaning up a mess of a namespace collision as we speak. I was the only one who saw it coming. I've spent the better part of a month getting people to believe it's an issue.
  - gavinray 5y ago
    SQL is relational algebra, the core of relational algebra is Selection, Projection, Cross-products, Unions, and Set Differences. SQL is a language that implements Relational Algebra/Relational Calculus
- rcoveson 5y ago
  That is a really interesting case. Correct me if I'm wrong, but I don't think this is a special case where you have to use `NATURAL JOIN` over `JOIN ON`. The problem is just that the Postgres SQL grammar (maybe most/all SQL grammars) require ON or USING in a non-NATURAL join, so you have to specify the (empty) equijoin predicate list yourself, i.e. the identity value: `JOIN ON TRUE`. EDIT: I had another thought about this. I think people not designing with the relational algebra in mind is the heart of the issue, specifically w.r.t. column names. We know that namespaces are a hard problem, and a consequence of that problem is that `NATURAL JOIN` as specified in the relational algebra seems risky, or overly magick-y. It makes what might be an unfortunate coincidence (name collision) into something algebraically impactful. A foreign key join gets around the problem by keeping names and namespaces out of it. It's really doing exactly what `NATURAL JOIN` is supposed to do, but only in the subset of cases where name collisions are meaningful, not coincidental.

bendbro 5y ago

I agree with your point about explicit columns being easier to read, but I still sometimes prefer implicit columns. An example: whenever you have numerous subqueries, all using the same columns, implicit columns are easier to read since there is less text on the screen. It's also less error prone to change just one line rather than numerous lines. This feeds into my view of metaprogramming-like situations. Whenever the code-time-view of a program differs significantly from some runtime-state-view of the program, I think there should be a code-time way to view and perhaps edit both the code-time-view and some kind of runtime-state-view. A programmer shouldn't have to waste time digging through numerous files to evaluate what implementation slots into some dependency injected class, or find out what structure ends up in a python method parameter, or what a preprocessor directive ultimately produces. I know IDEs can handle some of these things, but I think better tools can be produced. More concisely, instead of approaching code as the single and unchanging view of the program, perhaps it would help to approach code as something more dynamic. I have no concrete ideas as to how this would work.

mpolichette 5y ago

I'm with you on this one. I prefer it to be in your face simple to understand over the terse-ness. In the gist example, I actually prefer the SQL-92 approach where we are joining given an explicit comparison condition. Every other implementation seems to be trying to hide details, for what gain? Less typing? In order to use FOREIGN, you will need to know not just what columns a table has, but also their configuration. Which would also require that you have properly configured your tables. While this shouldn't be a hard ask, it does add additional dependency and makes use of this "tool" slightly less "portable" between systems. I have unfortunately seen cases where people will only have foreign keys un-enforced by their table config. As a dev, if you're introduced to a new DB, you wont know immediately if you can use this, and if things are configured wrong, you need to make a pretty significant change to be able to use it. I don't see a lot of harm from adding this syntax however as people are free to not use it and it relies on an existing strict convention.

JoelJacobson 5y ago
> I don't see a lot of harm from adding this syntax however as people are free to not use it and it relies on an existing strict convention. This is a good argument I will add to the list. Also interesting to read about un-enforced foreign keys. I haven't used MSSQL myself, the DB in which I heard it's possible, I've only been using PostgreSQL for the last 20 years, and before that MySQL. I think the problems you describe is an argument against a WITH NOCHECK feature, since it could be misused. Maybe it's necessary in some databases still, but at least in PostgreSQL, the FOR KEY SHARE lock solved all the issues with concurrent updates we had at Trustly. The FOR KEY SHARE was a huge patch [1] written mainly by Alvaro Herrera. Thanks to it, Trustly has never since had any performance problems with foreign keys, and they have AFAIK not needed to drop any foreign keys up until today due to locking/performance problems. [1] https://www.commandprompt.com/blog/fixing_foreign_key_deadlocks_part_three/ https://www.commandprompt.com/blog/fixing_foreign_key_deadlo...

tapas73 5y ago

I would argue that conceptually it's not too different from needing to know what tables/columns exist in datamodel. I get that it is not common now to care about FKs when writing selects. But it could be. Tooling can be improved to help here. (show fks, autocomplete) Btw. Everybody seems to concentrate on conciseness, but keep in mind that this helps also with query correctness.