SELECT wat FROM sql

https://scattered-thoughts.net/writing/select-wat-from-sql/

by luu • 6 years ago

150 118 6 years ago

scattered-thoughts.net

ThreeFx• 6 years ago

Super interesting!

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

benesch5• 6 years 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.

jamii6• 6 years 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

dan-robertson3• 6 years 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

?

necovek2• 6 years 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• 6 years 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

Minor49er• 6 years 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• 6 years 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.

JohnBooty4• 6 years 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.

coverup• 6 years ago

There are parts of SQL that are crufty by that definition. For example, COMMIT; and COMMIT WORK; are two different statements that do the exact same thing according to the SQL standard.

Perhaps trivial cruft but cruft nonetheless.

mcstafford• 6 years ago

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

dan-robertson3• 6 years ago

I don’t understand what point you’re trying to make.

JohnBooty4• 6 years 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.

Areading3143• 6 years 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• 6 years ago

I'm not sure thats a point the author is trying to make. To which example are you refering?

benesch5• 6 years 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.

JohnBooty4• 6 years 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• 6 years 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.

mikl• 6 years 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• 6 years 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."

Areading3143• 6 years 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

progre6• 6 years ago

One note: While using field number for anything is perfecrly valid SQL, this will not pass a code review if I'm the one doing the review. Reason being, it's not clear without looking at the field list what is being ordered. For extra shame do something like:

    select * from foo order by 2

Order of the fields when doing "select *" may be guaranteed (I don't know) but they have been dependent on when the feild was added to the database on every system I have worked with, and sometimes patches are applied in diffrent orders on diffrent databases.

dan-robertson3• 6 years ago

Right but if you’re trying to be Postgres compatible then you have to care and if you are making a compiler off some sort which outputs sql then you have to care a lot because compiler often generates things that are not natural

jamii6• 6 years ago

Exactly. We're often on both ends of this - some BI tool will generate a SQL expression that a human would never have typed, and we have to handle it the same way that postgres would.

csours4• 6 years ago

It's not nonsense, but it is a bit of a footgun if you put it into production.

Order by 2 is the kind of thing I use all the time in investigations and POCs, but if you put it into prod, you have to account for someone else updating the select and getting things out of order.

csours4• 6 years 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• 6 years 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.

jamii6• 6 years 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. 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.

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.

csours4• 6 years 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.

sbuttgereit• 6 years ago

When I'm writing procedural code for PostgreSQL, I'm usually writing PL/pgSQL... which is very, very similar to Oracle PL/SQL (having written more than my share of PL/SQL); My recollection is the similarity is intentional.

However, there are other PL's for PostgreSQL. PL/Python, PL/Perl, and PL/Tcl are also distributed with the database and there are others available (https://www.postgresql.org/docs/12/external-pl.html... though I've only used JavaScript)

sk5t2• 6 years ago

Many of the cases seemed to be focused on arrays, which in my non-exhaustive experience you'll see more of in postgres than in other platforms. Same for lateral join, hstore, jsonb...

dragonwriter2• 6 years ago

PL/SQL isn't Oracle’s SQL, it's Oracle's SQL-based procedural language for triggers, stored procs, etc.; the postgres equivalent is PL/pgSQL.

In both Oracle and Postgres, the SQL dialect, as opposed to the procedural language, is just called “SQL”.

csours4• 6 years ago

What are the differences between the SQL dialect implementations called then? Extensions?

crorella• 6 years ago

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

craigkerstiens• 6 years 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-wi...

Pxtl6• 6 years 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.

thedufer3• 6 years 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.

adamzochowski2• 6 years ago

Sigh,

In all other programming languages the code has to be along the lines of:

    if ((jane.car.colour == james.car.colour) and
        (jane.car.colour != null            ) and 
        (           null != james.car.colour)):
       print ("James and Jane have same car color")

or attempted with a second variable to indicate if value is known

    if (( jane.car.colourKnown == True ) and 
        (james.car.colourKnown == True ) and 
        ( jane.car.colour == james.car.colour)):
       print ("James and Jane have same car colour")

I like to think that SQL nulls are the most human way. I think of a scenario: James and Jane each own a car. I don't know neither car colour. Does it mean they are of same colour? All typical programming languages say "Yes". SQL says "I can't tell you, I don't know", and that is what a human would say.

da_chicken5• 6 years ago

composing the proper check is absurdly verbose.

You're kidding, right? Instead of

  WHERE Field = 1

You have to specify

  WHERE Field = 1 OR Field IS NULL

You might even have to add parenthesis! It's so verbose it puts Java to shame!

Yes, I understand that real queries are much longer, but that's not the language's fault. That's the complexity of your data coming through.

NULL doesn't behave the way it does out of some need for mathematical purity. Understanding the model just tells you when you know you need to handle your NULLs. The system behaves the way it does because the system doesn't know what you want to do when a value doesn't exist or is unknown. Logically, there is no answer. That's what you're complaining about here. It's like asking the system to add two numbers and only giving it one of them.

What does a NULL datetime mean? The value wasn't recorded? It wasn't observed? It hasn't happened yet? That field doesn't make sense to record with the other data on the record (i.e., you denormalized your data)? That the value is actually known to be unknown? Or some combination of those? How is the system supposed to know?

When you're telling the system what to do with NULL values, you're telling the system what NULL means for that query. That's all you're doing. You're just telling the system what your data actually means in that query. That's not hard. Complaining that the system that stores structured data actually requires you to understand what your data represent and how your data are being used is... strange.

hathawsh2• 6 years ago

The fact that null != null (and NaN != NaN) is, to me, a helpful reminder that the mathematical model behind SQL is not as simple and elegant as newcomers would like it to be. It reminds me of the classic "proof" that 1 = 2, based on simple algebra:

https://www.math.toronto.edu/mathnet/falseProofs/first1eq2.h...

If even basic algebra has a trap like that, then all of us need to be prepared for similar traps.

sjwright9• 6 years ago

Interesting. The SQL concept of null always made perfect sense to me.

Two people with the same middle name would have equality with respect to the string value of middleName. The same applies with two people known to have no middle name—where you store a zero length string.

Two people who didn’t supply a middle name should not have equality with respect to middleName. Hence null. This acknowledged lack of data cannot be equal to a different lack of data.

progval• 6 years ago

The same applies with two people known to have no middle name—where you store a zero length string.

I think that's the thing. People use NULL to mean empty value, instead of unknown value.

Not necessarily for strings because there's the empty string, but for numeric fields, including foreign keys

rmrfchik• 6 years ago

Except, Oracle treats zero length string as null.

lopsidedBrain• 6 years ago

People generally expect equality to produce either true or false. Not a tri-value pair.

That interpretation makes sense to me when I read the docs, but is way too easy to miss when I'm actually doing comparisons.

himinlomax• 6 years ago

Yes, it's perfectly sensible; the only problem is with the name used. If it was called `UNKNOWN` or something instead, nobody would be complaining.

endymi0n• 6 years ago

...except that NULL is still conflating the two concepts of "not known to exist" and "known to not exist" (I know some people who would argue for empty strings to be bad style).

Especially for (composite) unique indexes on NULLable foreign keys, where NULL signifies the non-existence of a different table entry (if you don't want to resort to a special "not existing" entry in that table), this behavior can be surprising and definitely got me a few times.

Pxtl6• 6 years ago

  SELECT 'foo' WHERE bar = baz FROM quux
  UNION ALL
  SELECT 'bar' WHERE bar <> baz FROM quux

I would understand returning an error in that case if bar or baz were unknown. The truthiness of bar = baz is unknown, so that's an error.

But that's not what SQL Servers do. They're all pedantic about how NULL means unknown right up until the rubber hits the road of the WHERE clause, when suddenly NULL means false.

Which means that they're not saying that foo = bar is unknown, they're instead making a definitive assertion that both foo = bar is false and foo <> bar is false. And that's disastrously wrong.

frankmcsherry2• 6 years ago

Let's check out SQL's "acknowledged lack of data" in a case statement:

    create table test(a bool);
    insert into test(a) values (NULL);
    select case a when a then true else false end from test;

The answer should be NULL, right?

necovek2• 6 years ago

Since we are in the context of Postgresql, there is 'IS (NOT) DISTINCT FROM' for those who believe NULL's behaviour is reversed in (in)equality comparisons.

EmielMols• 6 years ago

It's fairly nice but indeed rather implicit. I would kill for a postgres setting where feeding (an evaluated) NULL to the where clause logs a warning. This would force developers to coalesce (at least on top level) and think about the null case when applicable.

scandox• 6 years ago

Yeah the big kick in the pants I got this year was using nullable columns in composite keys in MySQL and finding out that when their value is null, the result key is considered unique, because NULL is literally unknowable. So:

  col A, Col B
  null, 1
  null, 1

Is not constrained, but:

  col A, Col B
  1, 1
  1, 1 <--- NOT ALLOWED

I guess this is one of those things that is completely obvious to those that know it.

dahdum• 6 years 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.

X-Istence• 6 years 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-appendi...

kyllo• 6 years ago

This is the reason why ORMs and SQL generators typically quote all column names in statements.

em500• 6 years 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

progre6• 6 years 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• 6 years 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• 6 years ago

But that's the thing, it's not

    (select a from nums) order by b;

but

    (select a from nums order by b);

progre6• 6 years ago

I agree, I should have written that it helps to think in sets rather than tables. SQL implementors do a lot of fantastic shit to get our queries to run in a reasonable time, and SQL is not one of those langages that are like "understand this one core principle, and you get it" like lisp, prolog, TCL, Smalltalk, Forth etc. Have a look at the query plan if your DBMS can output it, it's not always easy to guess what it will spit out. In the case of

    (select a from nums) order by b;

my best guess is that the parser helpfully strips out the parens as because the "order by" needs to be a part of a table expression to make sense. This expression is a syntax error in SQLite by the way.

mFixman• 6 years ago

The worst part about SQL is statement order.

IIRC most SQL engines do FROM → WHERE → GROUP BY → HAVING → ORDER BY → SELECT → LIMIT → UNION, which is why you can SELECT after ORDER BY in the previous example but not after union.

If I were to start SQL from scratch I would allow statements in an arbitrary order and execute them in whichever order I coded them.

tpetry• 6 years ago

But then i will not be SQL. SQL is built like this because all these operations are projections on a set. You can not really change their order and have meaningful results.

Some oeprators can be swapped, but this part of the optimization process and nothing you need to be aware of.

gigatexal• 6 years ago

that hellscape doesn't exist and I am glad it doesn't. The order should just be considered a spec and accepted and worked around -- I think SQL has proven its staying power over the years.

SELECT wat FROM sql

Related Stories