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

A Critique of SQL, 40 Years Later

carlineng.com

Post reply on HN
Print view View original

61–70 of 260 posts

Re: A Critique of SQL, 40 Years Later

#61

Post by mamcx »

toto444 wrote: post #14
exabrial wrote: post #10

The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs. Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise. SQL is on of my favorite tools to use and I don't see…

Every time SQL is mentioned on HN someone comes to complain about FROM coming after SELECT. I use SQL every day and not a single time have I found reason to complain about it. Can you give a bit more detail about what's wrong with it being like it is ? EDIT : thanks all for your reply. I now understand that it is an IDE related thing not something fundamental to the language.

> I now understand that it is an IDE related thing not something fundamental to the language.

No, is fundamental issue to the language!

The relational model is clear. You START with a relation and then compose with relational operators that return relations.

ie:

    rel | project
Sql do it weird. Is like in OO, where instead of define a class THEN define the properties, you define the properties THEN define the class.

And this fundamental issue with the language goes deeper. The rules are ad-hoc for each sub-operator despite the fact using relational model MUST make it simply to compose.

So, you have rules for HAVING, GROUP BY, ORDER BY, WHERE, SELECT and so on and none are like the others, are different in small but annoy ways...

Re: A Critique of SQL, 40 Years Later

#62

Post by thedataslinger »

crazygringo wrote: post #37

I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me. NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN. If I run: SELECT x.a, y.b FROM x LEFT JOIN y on x.a = y.a and I get back [5, NULL] I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table…

To piggy-back, it bothers me so much that this is valid syntax in many implementations:

UPDATE x SET a = NULL WHERE b IS NULL;

Like... wat?

Re: A Critique of SQL, 40 Years Later

#63

Post by coob »

vendiddy wrote: post #5
dominotw wrote: post #3

SQL is having somewhat of a moment in the bigdata world, thanks in part to 'modern datastack' and new age datawarehouses like snowflake,bigquery. However there are a lot of pushback from 'traditional' dataengineers who were trained on spark/scala. Its bit of hardsell to go from a highly typed language to a free for all text based logic. I think the following is needed for sql to be finally accepted as 'serious' conte…

I think these are great suggestions. It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. 2 & 3 are my biggest pain points. I can't just extract functions like I can with a regular programming language. Instead, SQL queries get increasingly complex with no great tools to manage that. For 3, products like https://materialize.com/ look interesting for b…

DBT solves 2 & 3

Re: A Critique of SQL, 40 Years Later

#64

Post by kaba0 »

SideburnsOfDoom wrote: post #38
emaginniss wrote: post #35

Earlier quoted context omitted.

Everyone else is mentioning it from an IDE perspective, but let's also think about logically from a language perspective. When you start a FROM clause and add some JOINs, a few WHERE conditions and maybe GROUP BY, you are building a virtual view of a series of tables, columns, and aggregations. You could even define this data set as an ephemeral table. What you do with that data set afterwards might vary depending on…

Agreed, it is not just an "IDE related thing", it is also a logical arrangement of thoughts thing, a readability thing.

And as another commenter noted, the underlying relational algebra is also not in agreement, so it is definitely not logical. I believe they wanted to mimic human language statements, but that goal hurt more than it helped.

Re: A Critique of SQL, 40 Years Later

#65

Post by kaba0 »

crazysim wrote: post #34
exabrial wrote: post #10

The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs. Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise. SQL is on of my favorite tools to use and I don't see…

I don't know why my memory jogged to the old ruby on rails screencasts but I suppose there's nothing stopping IDEs from jumping to the FROM section of a query and then returning you back to the SELECT in some sort of "macro"/snippet. It's a hack, I guess.

That’s exactly what Jetbrain’s Datagrip does.

Re: A Critique of SQL, 40 Years Later

#66

Post by kaba0 »

refactor_master wrote: post #45
taeric wrote: post #18

Earlier quoted context omitted.

You could improve type ahead by just pouring effort into the IDE. Especially with the amount of resources we have nowadays. Easy enough to have basic typeahead on basically all possible columns when writing the select, and then you could use the columns as a filter on the tables during auto complete. In general, this isn't done. But I don't see any technical reason it can't be done.

If you have multiple tables, how do you avoid suggesting a wrong column, before filling out the table name? You could suggest all columns up front, and then afterwards tell the user “this suggestion doesn’t exist”, which would just erode trust in the autocomplete.

Start typing “SEL”, ide suggests “SELECT _ FROM”, you press enter and the above text is entered, while the cursor is placed after from. You write that part of the query and after pressing enter it will jump back to the select part.

This is done already by Jetbrain’s datagrip for example.

Re: A Critique of SQL, 40 Years Later

#67

Post by ericHosick »

dominotw wrote: post #22
ericHosick wrote: post #16

Earlier quoted context omitted.

> where are your unit tests I do unit testing in SQL, and something I'm working on and use extensively myself ( https://www.npmjs.com/package/sql-watch ) indirectly supports unit tests. There are also SQL testing frameworks available.

Yea I do that via dbt by setting up a mock data tables in database and using a macro to use those as sources/refs when run in test mode. However what we are doing here isn't 'unit testing' its a black box integration testing. When I write equivalent code in scala, i just test the logic via unit tests, eg: logic to filter out some orders that don't qualify, I extract method in scala code and just test that logic as pa…

> I extract method in scala code and just test that logic as part of development lifecycle. There is no dependency on a database.

Unit testing seems to depend on where the unit of code is which is being tested. At the middle tier, you may mock out parts of the code so the tests aren't reliant on external sources (apis, databases, libraries, etc.).

It seems that unit testing database code would happen at the database layer: it's still a unit test as the test isn't dependent on external sources.

Re: A Critique of SQL, 40 Years Later

#68

Post by wwweston »

mamcx wrote: post #61
toto444 wrote: post #14

Earlier quoted context omitted.

Every time SQL is mentioned on HN someone comes to complain about FROM coming after SELECT. I use SQL every day and not a single time have I found reason to complain about it. Can you give a bit more detail about what's wrong with it being like it is ? EDIT : thanks all for your reply. I now understand that it is an IDE related thing not something fundamental to the language.

> I now understand that it is an IDE related thing not something fundamental to the language. No, is fundamental issue to the language! The relational model is clear. You START with a relation and then compose with relational operators that return relations. ie: rel | project Sql do it weird . Is like in OO, where instead of define a class THEN define the properties, you define the properties THEN define the class. A…

Having learned Prolog before SQL, it was weird when it clicked that both were relational languages, but SQL decided to hide that underneath a natural language facade and the inconsistencies that come with it.

Re: A Critique of SQL, 40 Years Later

#69

Post by paulclinger »

crazygringo wrote: post #37

I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me. NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN. If I run: SELECT x.a, y.b FROM x LEFT JOIN y on x.a = y.a and I get back [5, NULL] I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table…

> I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5.

You can always add SELECT y.a, which will allow you to disambiguate between the two options (it will be non-NULL in the first case and NULL in the second).

Re: A Critique of SQL, 40 Years Later

#70

Post by Koshkin »

exabrial wrote: post #10

The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs. Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise. SQL is on of my favorite tools to use and I don't see…

FWIW in LINQ, Select comes after From and Where.
Post reply on HN