13 ms·

SELECT wat FROM sql

150 points by luu 7y ago

ThreeFx 7y ago
Super interesting! Although it sure would be nice to have explanations for amateurs such as me.
- benesch 7y ago
  I highly recommend puzzling through some of these on your own first (it's a great way to build intuition for SQL's craziness), but here's a few explanations if you're stuck. jamie=# select a+1 from nums group by a+1; ?column? ---------- 2 4 1 3 (4 rows) jamie=# select a+1 from nums group by 1+a; ERROR: column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select a+1 from nums group by 1+a; In general, a query like `SELECT a FROM t GROUP BY a` is valid, while a query `SELECT b FROM t GROUP BY a` is not valid. Because you didn't group by column b, there are multiple values of b within each group, and it's not clear how to combine them. What you usually meant is something like SELECT a, sum(b) FROM t GROUP BY a where you can see the sum of all b's for each distinct value of a. The error message explains it really nicely: if there is a GROUP BY clause, every column in a SELECT list must either be used in the GROUP BY clause or appear in an aggregate function. This is kind of annoying, though; what if you want to group by a value derived from a column, like `a + 1`? (More realistically, maybe you want to trim whitespace or something.) I think the SQL standard would have you write SELECT a1 FROM (SELECT a + 1 AS a1 FROM t) _ GROUP BY a1 but that's annoying. PostgreSQL (and maybe other SQL implementations; not sure!) has some smarts about detecting when an expression is used verbatim in a SQL query, so you can just write SELECT a+1 FROM t GROUP BY a+1 and it will notice that the "a+1" in the SELECT list is literally the same as the "a+1" in the GROUP BY clause. But it's not that smart, because it doesn'that realize that "a+1" and "1+a" are semantically equivalent, and so this is an error: SELECT a+1 FROM t GROUP BY 1+a ---- jamie=# select b from nums group by a; b --- (0 rows) jamie=# select a from nums group by b; ERROR: column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select a from nums group by b; An extension of the previous example, where PostgreSQL can infer that when grouping by a all b's are unique. It can't infer the converse. The reason is that a is the primary key and so guaranteed to be unique, and therefore `GROUP BY a` is meaningless and can be elided. (This one really confused me for a while. I only learned this recently.) ---- jamie=# select a, b from nums order by 2; a | b ---+----- 3 | bar 0 | foo 1 | foo 2 | foo (4 rows) jamie=# select a, b from nums order by 1+1; a | b ---+----- 0 | foo 1 | foo 2 | foo 3 | bar (4 rows) `ORDER BY <n>` means "order by the Nth column", but `ORDER BY <expr>` means "order by the value of <expr>`. So you get the goofy behavior that `ORDER BY 2` in the above query means order by column b, but `ORDER BY 1 + 1` means order by the literal value "2", i.e., don't order by anything useful at all. (It just so happens that you get primary key order in the example above, but that's not guaranteed by any means.) ---- Most of the others have a similar feel. Let me know if you're particularly puzzled by any others.
  - jamii 7y ago
    > PostgreSQL (and maybe other SQL implementations; not sure!) has some smarts about detecting when an expression is used verbatim in a SQL query I think this one is in the SQL 2016 spec. There's definitely a section about how to put the AST into a normal form to compare expressions for syntactic equality. EDIT found it - https://news.ycombinator.com/item?id=22991487 https://news.ycombinator.com/item?id=22991487
  - dan-robertson 7y ago
    I’m not sure the sub query would be necessary if the weird ast-based naming didn’t exist. Couldn’t you just write: SELECT a+1 AS a1 FROM t GROUP BY a1 ?
    - benesch 7y ago
      You can do that in PostgreSQL, but not according to the spec, apparently, and the PostgreSQL developers regret the feature. [0] (As someone who's spent a lot of time trying to simulate PostgreSQL's name resolution rules... it's reassuring to know that the rules are painful inside of PostgreSQL too.) [0]: https://www.postgresql.org/message-id/7608.1259177709%40sss.pgh.pa.us https://www.postgresql.org/message-id/7608.1259177709%40sss....
      - jamii 7y ago
        I never figured out why AS isn't allowed in the GROUP BY clause. It would be much simpler to implement `SELECT a1 FROM nums GROUP BY a+1 AS a1`.
        deleted 7y ago
        [deleted]
  - necovek 7y ago
    I was confused by "select b from nums group by a;" indeed. I am a little bit surprised by "select array[null] = array[null];" too. The rest did seem like 'undefined behaviour' (or well-defined behaviour involving NULLs, literal-matching in the query planner [used in matching partial indexes too]...).
samcheng 7y ago
SQL is indeed pretty crufty. After all, it dates back to the 70's, and is generally regarded as an 'entry level' language for analysts. It's nowhere near as crazy as Javascript (and, arguably, Ruby): https://www.destroyallsoftware.com/talks/wat https://www.destroyallsoftware.com/talks/wat
- Minor49er 7y ago
  Comparing SQL to JavaScript or Ruby is like comparing apples to oranges. They have completely different purposes and applications. Rather, it would make more sense to compare SQL to MySQL (or any other derivative). It's like comparing BASIC to QBASIC. I would argue that SQL itself isn't very crufty. Rather, it's fairly simple and well thought out since it's based on many years of thought and consideration from the creators of the relational database model. E.F. Codd, for one, wrote at length about how these models can work for years before SQL appeared, refining the concept for maybe a decade afterwards. His goal was to make a system that made it easy for developers to store and request information with as little redundancy as needed. Vendors and developers of database systems have implemented various functions and features that expand on or alter SQL, but those are reflections more on those particular systems rather than on SQL. For example, MySQL's GROUP BY does not verify if the extra columns in the selectlist are dependent on the group by columns, which is against SQL's definition. It wouldn't make sense to count that as an issue with SQL itself.
- yashap 7y ago
  Huh? SQL isn’t meant to be a general purpose programming language like JS/Ruby, it’s a DSL. Many have tried to create “better” DSLs for interfacing with DBs, and they’ve basically all failed, because SQL is just too god damn useful and well designed. Also, it’s every bit as useful for application developers as it is for analysts, the statement that SQL “generally regarded as an ‘entry level’ language for analysts” couldn’t be more wrong.
- JohnBooty 7y ago
  I don't think SQL is particularly crufty, but I'm willing to admit that's highly debatable -- depends on your definition of "crufty," really. However, the examples in the linked article are not examples of cruft. I'm using this example of "cruft": Cruft is a jargon word for anything that is left over, redundant and getting in the way. It is used particularly for defective, superseded, useless, superfluous, or dysfunctional elements in computer software. Nearly all of the examples in the linked article are based on core SQL sorting and grouping concepts (with a sprinkle of trivalue NULL logic) that have been around since Day One. I also don't think they really illustrate flaws/gotcha/etc in SQL itself. Nor were they meant to, I don't think... these highly pathological examples are examples of the sorts of pathologically ambiguous things a compiler needs to handle, but are not representative of anything I've seen in 20+ years of real world SQL experience.
deleted 7y ago
[deleted]
mcstafford 7y ago
Choosing an ineffective tool, or not knowing being familiar with its effective use says more about the crafter than the tool.
- dan-robertson 7y ago
  I don’t understand what point you’re trying to make.
- JohnBooty 7y ago
  The examples in the article are given as things the author learned while "working on a postgres-compatible query compiler." When writing any tool, particularly something as complex as a compiler, there will of course be many of these weird edge cases. They are not presented as flaws in SQL or anything else.
Areading314 7y ago
The author of this seems to be surprised that the DB doesn't just sort things (which could be billions of rows) automatically. SQL deals with "sets", which are unordered, and the user needs to specify the order by if they need sorted output. I think this is an excellent design.
- GeertJohan 7y ago
  I'm not sure thats a point the author is trying to make. To which example are you refering?
- benesch 7y ago
  You're not looking at the examples closely enough, if that's your takeaway. I assure you they are much weirder than forgetting to specify an ORDER BY clause and expecting sorted output.
- JohnBooty 7y ago
  SQL deals with "sets", which are unordered, and the user needs to specify the order by if they need sorted output. I think this is an excellent design. Well-said, and I completely agree. However, I'm not sure that the author is complaining about SQL, so much as he is kvetching about some of the weird-looking things one needs to deal with while "working on a postgres-compatible query compiler."
- ben509 7y ago
  > SQL deals with "sets" SQL deals with tables, and all operations are working on tables. However... these tables are bags of rows, not sets, as evidenced by: 1. "distinct" transforms bags into sets 2. "union all" preserves bags 3. the ability to create a table with no primary key, allowing entirely duplicate rows in a table 4. the ability to select columns not including a primary key, allowing duplicate rows in the resulting table > ... which are unordered While (base) tables themselves guarantee no order: 1. every "select" is itself returning a table, which can absolutely have an order. 2. a "view" is a (derived) table, and it can guarantee an order 3. aggregate functions rely on ordering > I think this is an excellent design. Your approach would be an excellent design.
qroshan 7y ago
I'll be downvoted on HN, but those are pretty stupid examples. 1) Group By Nth column is a shorthand syntactic sugar. No SQL user would use it to turn that into an expression. 2) Similarly 'a+1' and '1+a' is another stupid way to express anything. General theme of the post is the author is trying to use SQL as some turing complete programming language instead of it's true intention "A shorthand to express relational algebra in an elegant enough fashion given constraints for mathematical notations" Once you get this and understand the powerful transformations you could do with exbytes of data with trillions of rows, using SQL (and relational algebra), you'd stop worrying about silly things like this.
mikl 7y ago
Most of these are “play stupid games, win stupid prices”, not things you’d encounter in normal usage. Queries like `select a, b from nums order by 2` are nonsense. And sure, you can create tables with weird names if you want, but that’s not really a problem.
- anamexis 7y ago
  Some of those queries are nonsense, but `select a, b from nums order by 2` is not one of them. `order by 2` here means "order by the second field in the select statement."
  - Areading314 7y ago
    No, it is using the constant expression 2 as a sort key, resulting in a random order which should be expected Edit -- this is wrong see below
    - benesch 7y ago
      That is not correct. The parent has it right. The output is in fact sorted by the second field; it is not random. > Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values. > The ordinal number refers to the ordinal (left-to-right) position of the output column. This feature makes it possible to define an ordering on the basis of a column that does not have a unique name. This is never absolutely necessary because it is always possible to assign a name to an output column using the AS clause. (emphasis mine) https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY https://www.postgresql.org/docs/current/sql-select.html#SQL-...
      - Areading314 7y ago
        Interesting -- didn't know it did this
        7y ago
csours 7y ago
So I looked at these, I didn't notice any examples where the problem was with ANSI SQL? Please let me know if there were. It seems like the problems were with PostgreSQL flavored queries. Speaking of which - I know Microsoft has Transact-SQL and Oracle has PL/SQL, is PostgreSQL just PostgreSQL?
- mst 7y ago
  PL/SQL and T-SQL are the names of the stored procedure languages - postgres' on of those is PL/PGSQL. All three have variations from ANSI SQL in their non-sproc SQL too, oracle's far worse than the other two, but pg and SQL Server document where they deviate pretty damn well. There definitely were ANSI examples in there, "ORDER BY 2" is in ANSI (and IIRC plenty of pre-ANSI dialects as well) for a start, I'm too tired (and/or lazy) to go back and look for others, sorry.
- jamii 7y ago
  I think a lot of these are in the ANSI 2016 spec. I haven't looked at the older specs, maybe they were simpler. Eg here is the `a+1` vs `1+a` thing explained for ORDER BY - https://i.imgur.com/bklpjkP.png https://i.imgur.com/bklpjkP.png. Here are the conditions under which ORDER BY can reach inside SELECT to pick columns from the FROM clause - https://i.imgur.com/aoeqDGn.png https://i.imgur.com/aoeqDGn.png. Chapter 2 of the spec is 1732 pages of that kind of language, so it's hard to be sure that any particular behaviour is not in there somewhere. But I'm fairly sure that all the examples with set-valued functions are postgres-isms.
  - csours 7y ago
    Wow, I never would have guessed that ORDER BY stuff was part of the standard. Seems like one of the special corners of SQL. From that brief read of the ANSI Spec it seems like one would need a great deal of time and some advanced mathematics to understand it.
    - jamii 7y ago
      There isn't really any math involved. It's just a lot of edge cases written out in lots of detail. I think a lot of what makes SQL hard to implement comes down to consistently prioritizing local convenience over global complexity, combined with not being able to break backwards compatibility to simplify the design.
crorella 7y ago
All of them seem valid errors. I’m don’t get the idea of the post lol.
craigkerstiens 7y ago
A great one to pile on here is null. Null is quite the unique thing within SQL, this post from some years ago highlights a lot of the unintuitive behavior around it within Postgres - http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-with-nulls/ http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-wi...
- Pxtl 7y ago
  Yes this. I always see people raving about the mathematical beauty of three-value Boolean algebra, but any programming language where testing if foo = foo can return something mechanically equivalent to false is just plain broken. Yes, I get that it's pure and mathematically beautiful, but over here in the real world I've squished an endless supply of bugs caused by this absurdity, and in languages where there is no null-aware equality test, the composing the proper check is absurdly verbose.
  - thedufer 7y ago
    > any programming language where testing if foo = foo can return something mechanically equivalent to false is just plain broken Isn't this pretty much any language? You're describing how IEEE float NaNs work. I'm not aware of many languages that don't have IEEE-compliant floats.
    - lmm 7y ago
      IEEE floats with non-signalling NaNs make some very aggressive tradeoffs (in the name of performance) that were maybe a reasonable default in 1985 but are certainly not so today. General-purpose programming languages should not be offering them unless very explicitly opted in to.
      - thedufer 7y ago
        Can you actually name a mainstream language that doesn't have NaNs with that behavior? Python, Ruby, Go, Rust, Java, JavaScript, etc all do. Or maybe what you're saying is really just that they shouldn't have them, and every modern language has gotten this wrong?
dahdum 7y ago
I think I’ve been drinking the SQL kool aid for too long, only a couple at the end surprised me. The rest seem perfectly logical. Was a lot of fun to read through them though.
- deleted 7y ago
  [deleted]
X-Istence 7y ago
jamie=# create table users ("user" text, "password" text); CREATE TABLE jamie=# insert into users values ('bob', 'whatever'); INSERT 0 1 jamie=# select user, password from users; user | password -------+---------- jamie | whatever (1 row) Is because the name /user/ is a pseudo-function keyword, and is an alias for /current_user/ test=# select "user", "password" from users; user | password ------+---------- bob | whatever (1 row) Newer versions of PostgreSQL make this more clear: test=# select user, password from users; current_user | password --------------+---------- postgres | whatever (1 row) Here is a full list of keywords: https://www.postgresql.org/docs/current/sql-keywords-appendix.html https://www.postgresql.org/docs/current/sql-keywords-appendi...
- kyllo 7y ago
  This is the reason why ORMs and SQL generators typically quote all column names in statements.
- em500 7y ago
  And here's what the SQLite manual says about keywords: The SQL standard specifies a large number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object. https://www.sqlite.org/lang_keywords.html https://www.sqlite.org/lang_keywords.html
progre 7y ago
This one jamie=# ((select a from nums) union (select a from nums)) order by b; ERROR: column "b" does not exist This seems to point to a missunderstanding of how sql works. It's not a table query language, but a set query language. Tables are just a way to store sets while thay are not in use. (select a from nums) This part creates a new set from the "a" column of nums. The column name is carried over as convinience but the nums table is not visable from the union.
- Gaelan 7y ago
  Note the example above: (select a from nums) order by b; a --- 3 0 1 2 (4 rows) If things worked as you described, that would also fail. So Postgres is, in some cases, cheating with the "new set" thing. The weirdness is that it does it inconsistently.
  - glogla 7y ago
    But that's the thing, it's not (select a from nums) order by b; but (select a from nums order by b);
    - rwbaskette 7y ago
      What I’ve noticed in my years with various databases is that many SQL parsers error on ambiguity and not necessarily strict compliance. In the case above the parser is likely dropping the parentheses where in the union there’s ambiguity because of the use of two sets and it can’t tell between which set’s b you’d like to order by. I wonder what it would do if you aliased the subquery and ordered by alias_one.b
      - progre 7y ago
        I think it's even simpler. Starting a subquey here doesn't make sense since there is no main query. So the parens gets dropped promoting this query to main. Same with the union

SELECT wat FROM sql

150 points by luu 7y ago

ThreeFx 7y ago

Super interesting! Although it sure would be nice to have explanations for amateurs such as me.

benesch 7y ago
I highly recommend puzzling through some of these on your own first (it's a great way to build intuition for SQL's craziness), but here's a few explanations if you're stuck. jamie=# select a+1 from nums group by a+1; ?column? ---------- 2 4 1 3 (4 rows) jamie=# select a+1 from nums group by 1+a; ERROR: column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select a+1 from nums group by 1+a; In general, a query like `SELECT a FROM t GROUP BY a` is valid, while a query `SELECT b FROM t GROUP BY a` is not valid. Because you didn't group by column b, there are multiple values of b within each group, and it's not clear how to combine them. What you usually meant is something like SELECT a, sum(b) FROM t GROUP BY a where you can see the sum of all b's for each distinct value of a. The error message explains it really nicely: if there is a GROUP BY clause, every column in a SELECT list must either be used in the GROUP BY clause or appear in an aggregate function. This is kind of annoying, though; what if you want to group by a value derived from a column, like `a + 1`? (More realistically, maybe you want to trim whitespace or something.) I think the SQL standard would have you write SELECT a1 FROM (SELECT a + 1 AS a1 FROM t) _ GROUP BY a1 but that's annoying. PostgreSQL (and maybe other SQL implementations; not sure!) has some smarts about detecting when an expression is used verbatim in a SQL query, so you can just write SELECT a+1 FROM t GROUP BY a+1 and it will notice that the "a+1" in the SELECT list is literally the same as the "a+1" in the GROUP BY clause. But it's not that smart, because it doesn'that realize that "a+1" and "1+a" are semantically equivalent, and so this is an error: SELECT a+1 FROM t GROUP BY 1+a ---- jamie=# select b from nums group by a; b --- (0 rows) jamie=# select a from nums group by b; ERROR: column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select a from nums group by b; An extension of the previous example, where PostgreSQL can infer that when grouping by a all b's are unique. It can't infer the converse. The reason is that a is the primary key and so guaranteed to be unique, and therefore `GROUP BY a` is meaningless and can be elided. (This one really confused me for a while. I only learned this recently.) ---- jamie=# select a, b from nums order by 2; a | b ---+----- 3 | bar 0 | foo 1 | foo 2 | foo (4 rows) jamie=# select a, b from nums order by 1+1; a | b ---+----- 0 | foo 1 | foo 2 | foo 3 | bar (4 rows) `ORDER BY <n>` means "order by the Nth column", but `ORDER BY <expr>` means "order by the value of <expr>`. So you get the goofy behavior that `ORDER BY 2` in the above query means order by column b, but `ORDER BY 1 + 1` means order by the literal value "2", i.e., don't order by anything useful at all. (It just so happens that you get primary key order in the example above, but that's not guaranteed by any means.) ---- Most of the others have a similar feel. Let me know if you're particularly puzzled by any others.
- jamii 7y ago
  > PostgreSQL (and maybe other SQL implementations; not sure!) has some smarts about detecting when an expression is used verbatim in a SQL query I think this one is in the SQL 2016 spec. There's definitely a section about how to put the AST into a normal form to compare expressions for syntactic equality. EDIT found it - https://news.ycombinator.com/item?id=22991487 https://news.ycombinator.com/item?id=22991487
- dan-robertson 7y ago
  I’m not sure the sub query would be necessary if the weird ast-based naming didn’t exist. Couldn’t you just write: SELECT a+1 AS a1 FROM t GROUP BY a1 ?
  - benesch 7y ago
    You can do that in PostgreSQL, but not according to the spec, apparently, and the PostgreSQL developers regret the feature. [0] (As someone who's spent a lot of time trying to simulate PostgreSQL's name resolution rules... it's reassuring to know that the rules are painful inside of PostgreSQL too.) [0]: https://www.postgresql.org/message-id/7608.1259177709%40sss.pgh.pa.us https://www.postgresql.org/message-id/7608.1259177709%40sss....
    - jamii 7y ago
      I never figured out why AS isn't allowed in the GROUP BY clause. It would be much simpler to implement `SELECT a1 FROM nums GROUP BY a+1 AS a1`.
      - deleted 7y ago
        [deleted]
- necovek 7y ago
  I was confused by "select b from nums group by a;" indeed. I am a little bit surprised by "select array[null] = array[null];" too. The rest did seem like 'undefined behaviour' (or well-defined behaviour involving NULLs, literal-matching in the query planner [used in matching partial indexes too]...).

samcheng 7y ago

SQL is indeed pretty crufty. After all, it dates back to the 70's, and is generally regarded as an 'entry level' language for analysts. It's nowhere near as crazy as Javascript (and, arguably, Ruby): https://www.destroyallsoftware.com/talks/wat https://www.destroyallsoftware.com/talks/wat

Minor49er 7y ago
Comparing SQL to JavaScript or Ruby is like comparing apples to oranges. They have completely different purposes and applications. Rather, it would make more sense to compare SQL to MySQL (or any other derivative). It's like comparing BASIC to QBASIC. I would argue that SQL itself isn't very crufty. Rather, it's fairly simple and well thought out since it's based on many years of thought and consideration from the creators of the relational database model. E.F. Codd, for one, wrote at length about how these models can work for years before SQL appeared, refining the concept for maybe a decade afterwards. His goal was to make a system that made it easy for developers to store and request information with as little redundancy as needed. Vendors and developers of database systems have implemented various functions and features that expand on or alter SQL, but those are reflections more on those particular systems rather than on SQL. For example, MySQL's GROUP BY does not verify if the extra columns in the selectlist are dependent on the group by columns, which is against SQL's definition. It wouldn't make sense to count that as an issue with SQL itself.
yashap 7y ago
Huh? SQL isn’t meant to be a general purpose programming language like JS/Ruby, it’s a DSL. Many have tried to create “better” DSLs for interfacing with DBs, and they’ve basically all failed, because SQL is just too god damn useful and well designed. Also, it’s every bit as useful for application developers as it is for analysts, the statement that SQL “generally regarded as an ‘entry level’ language for analysts” couldn’t be more wrong.
JohnBooty 7y ago
I don't think SQL is particularly crufty, but I'm willing to admit that's highly debatable -- depends on your definition of "crufty," really. However, the examples in the linked article are not examples of cruft. I'm using this example of "cruft": Cruft is a jargon word for anything that is left over, redundant and getting in the way. It is used particularly for defective, superseded, useless, superfluous, or dysfunctional elements in computer software. Nearly all of the examples in the linked article are based on core SQL sorting and grouping concepts (with a sprinkle of trivalue NULL logic) that have been around since Day One. I also don't think they really illustrate flaws/gotcha/etc in SQL itself. Nor were they meant to, I don't think... these highly pathological examples are examples of the sorts of pathologically ambiguous things a compiler needs to handle, but are not representative of anything I've seen in 20+ years of real world SQL experience.

deleted 7y ago

[deleted]

mcstafford 7y ago

Choosing an ineffective tool, or not knowing being familiar with its effective use says more about the crafter than the tool.

dan-robertson 7y ago
I don’t understand what point you’re trying to make.
JohnBooty 7y ago
The examples in the article are given as things the author learned while "working on a postgres-compatible query compiler." When writing any tool, particularly something as complex as a compiler, there will of course be many of these weird edge cases. They are not presented as flaws in SQL or anything else.

Areading314 7y ago

The author of this seems to be surprised that the DB doesn't just sort things (which could be billions of rows) automatically. SQL deals with "sets", which are unordered, and the user needs to specify the order by if they need sorted output. I think this is an excellent design.

GeertJohan 7y ago
I'm not sure thats a point the author is trying to make. To which example are you refering?
benesch 7y ago
You're not looking at the examples closely enough, if that's your takeaway. I assure you they are much weirder than forgetting to specify an ORDER BY clause and expecting sorted output.
JohnBooty 7y ago
SQL deals with "sets", which are unordered, and the user needs to specify the order by if they need sorted output. I think this is an excellent design. Well-said, and I completely agree. However, I'm not sure that the author is complaining about SQL, so much as he is kvetching about some of the weird-looking things one needs to deal with while "working on a postgres-compatible query compiler."
ben509 7y ago
> SQL deals with "sets" SQL deals with tables, and all operations are working on tables. However... these tables are bags of rows, not sets, as evidenced by: 1. "distinct" transforms bags into sets 2. "union all" preserves bags 3. the ability to create a table with no primary key, allowing entirely duplicate rows in a table 4. the ability to select columns not including a primary key, allowing duplicate rows in the resulting table > ... which are unordered While (base) tables themselves guarantee no order: 1. every "select" is itself returning a table, which can absolutely have an order. 2. a "view" is a (derived) table, and it can guarantee an order 3. aggregate functions rely on ordering > I think this is an excellent design. Your approach would be an excellent design.

qroshan 7y ago

I'll be downvoted on HN, but those are pretty stupid examples. 1) Group By Nth column is a shorthand syntactic sugar. No SQL user would use it to turn that into an expression. 2) Similarly 'a+1' and '1+a' is another stupid way to express anything. General theme of the post is the author is trying to use SQL as some turing complete programming language instead of it's true intention "A shorthand to express relational algebra in an elegant enough fashion given constraints for mathematical notations" Once you get this and understand the powerful transformations you could do with exbytes of data with trillions of rows, using SQL (and relational algebra), you'd stop worrying about silly things like this.

mikl 7y ago

Most of these are “play stupid games, win stupid prices”, not things you’d encounter in normal usage. Queries like `select a, b from nums order by 2` are nonsense. And sure, you can create tables with weird names if you want, but that’s not really a problem.

anamexis 7y ago
Some of those queries are nonsense, but `select a, b from nums order by 2` is not one of them. `order by 2` here means "order by the second field in the select statement."
- Areading314 7y ago
  No, it is using the constant expression 2 as a sort key, resulting in a random order which should be expected Edit -- this is wrong see below
  - benesch 7y ago
    That is not correct. The parent has it right. The output is in fact sorted by the second field; it is not random. > Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values. > The ordinal number refers to the ordinal (left-to-right) position of the output column. This feature makes it possible to define an ordering on the basis of a column that does not have a unique name. This is never absolutely necessary because it is always possible to assign a name to an output column using the AS clause. (emphasis mine) https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY https://www.postgresql.org/docs/current/sql-select.html#SQL-...
    - Areading314 7y ago
      Interesting -- didn't know it did this
      - 7y ago

csours 7y ago

So I looked at these, I didn't notice any examples where the problem was with ANSI SQL? Please let me know if there were. It seems like the problems were with PostgreSQL flavored queries. Speaking of which - I know Microsoft has Transact-SQL and Oracle has PL/SQL, is PostgreSQL just PostgreSQL?

mst 7y ago
PL/SQL and T-SQL are the names of the stored procedure languages - postgres' on of those is PL/PGSQL. All three have variations from ANSI SQL in their non-sproc SQL too, oracle's far worse than the other two, but pg and SQL Server document where they deviate pretty damn well. There definitely were ANSI examples in there, "ORDER BY 2" is in ANSI (and IIRC plenty of pre-ANSI dialects as well) for a start, I'm too tired (and/or lazy) to go back and look for others, sorry.
jamii 7y ago
I think a lot of these are in the ANSI 2016 spec. I haven't looked at the older specs, maybe they were simpler. Eg here is the `a+1` vs `1+a` thing explained for ORDER BY - https://i.imgur.com/bklpjkP.png https://i.imgur.com/bklpjkP.png. Here are the conditions under which ORDER BY can reach inside SELECT to pick columns from the FROM clause - https://i.imgur.com/aoeqDGn.png https://i.imgur.com/aoeqDGn.png. Chapter 2 of the spec is 1732 pages of that kind of language, so it's hard to be sure that any particular behaviour is not in there somewhere. But I'm fairly sure that all the examples with set-valued functions are postgres-isms.
- csours 7y ago
  Wow, I never would have guessed that ORDER BY stuff was part of the standard. Seems like one of the special corners of SQL. From that brief read of the ANSI Spec it seems like one would need a great deal of time and some advanced mathematics to understand it.
  - jamii 7y ago
    There isn't really any math involved. It's just a lot of edge cases written out in lots of detail. I think a lot of what makes SQL hard to implement comes down to consistently prioritizing local convenience over global complexity, combined with not being able to break backwards compatibility to simplify the design.

crorella 7y ago

All of them seem valid errors. I’m don’t get the idea of the post lol.

craigkerstiens 7y ago

A great one to pile on here is null. Null is quite the unique thing within SQL, this post from some years ago highlights a lot of the unintuitive behavior around it within Postgres - http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-with-nulls/ http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-wi...

Pxtl 7y ago
Yes this. I always see people raving about the mathematical beauty of three-value Boolean algebra, but any programming language where testing if foo = foo can return something mechanically equivalent to false is just plain broken. Yes, I get that it's pure and mathematically beautiful, but over here in the real world I've squished an endless supply of bugs caused by this absurdity, and in languages where there is no null-aware equality test, the composing the proper check is absurdly verbose.
- thedufer 7y ago
  > any programming language where testing if foo = foo can return something mechanically equivalent to false is just plain broken Isn't this pretty much any language? You're describing how IEEE float NaNs work. I'm not aware of many languages that don't have IEEE-compliant floats.
  - lmm 7y ago
    IEEE floats with non-signalling NaNs make some very aggressive tradeoffs (in the name of performance) that were maybe a reasonable default in 1985 but are certainly not so today. General-purpose programming languages should not be offering them unless very explicitly opted in to.
    - thedufer 7y ago
      Can you actually name a mainstream language that doesn't have NaNs with that behavior? Python, Ruby, Go, Rust, Java, JavaScript, etc all do. Or maybe what you're saying is really just that they shouldn't have them, and every modern language has gotten this wrong?

dahdum 7y ago

I think I’ve been drinking the SQL kool aid for too long, only a couple at the end surprised me. The rest seem perfectly logical. Was a lot of fun to read through them though.

deleted 7y ago
[deleted]

X-Istence 7y ago

jamie=# create table users ("user" text, "password" text); CREATE TABLE jamie=# insert into users values ('bob', 'whatever'); INSERT 0 1 jamie=# select user, password from users; user | password -------+---------- jamie | whatever (1 row) Is because the name /user/ is a pseudo-function keyword, and is an alias for /current_user/ test=# select "user", "password" from users; user | password ------+---------- bob | whatever (1 row) Newer versions of PostgreSQL make this more clear: test=# select user, password from users; current_user | password --------------+---------- postgres | whatever (1 row) Here is a full list of keywords: https://www.postgresql.org/docs/current/sql-keywords-appendix.html https://www.postgresql.org/docs/current/sql-keywords-appendi...

kyllo 7y ago
This is the reason why ORMs and SQL generators typically quote all column names in statements.
em500 7y ago
And here's what the SQLite manual says about keywords: The SQL standard specifies a large number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object. https://www.sqlite.org/lang_keywords.html https://www.sqlite.org/lang_keywords.html

progre 7y ago

This one jamie=# ((select a from nums) union (select a from nums)) order by b; ERROR: column "b" does not exist This seems to point to a missunderstanding of how sql works. It's not a table query language, but a set query language. Tables are just a way to store sets while thay are not in use. (select a from nums) This part creates a new set from the "a" column of nums. The column name is carried over as convinience but the nums table is not visable from the union.

Gaelan 7y ago
Note the example above: (select a from nums) order by b; a --- 3 0 1 2 (4 rows) If things worked as you described, that would also fail. So Postgres is, in some cases, cheating with the "new set" thing. The weirdness is that it does it inconsistently.
- glogla 7y ago
  But that's the thing, it's not (select a from nums) order by b; but (select a from nums order by b);
  - rwbaskette 7y ago
    What I’ve noticed in my years with various databases is that many SQL parsers error on ambiguity and not necessarily strict compliance. In the case above the parser is likely dropping the parentheses where in the union there’s ambiguity because of the use of two sets and it can’t tell between which set’s b you’d like to order by. I wonder what it would do if you aliased the subquery and ordered by alias_one.b
    - progre 7y ago
      I think it's even simpler. Starting a subquey here doesn't make sense since there is no main query. So the parens gets dropped promoting this query to main. Same with the union