1. 49
  1.  

  2. 11

    I would love to see an explanation on these wats, I have no idea why they happen.

    1. 9

      Luckily there is a spec! Many of these are explained in chapter 2, which is only 1732 pages long. Should be pretty straightforward.

      1. 1

        select a, b from nums order by 2;

        sorts by values in the second column

        select a, b from nums order by 1+1;

        sorts by a constant, just like order by 3*1 would

        ((select a from nums) union (select a from nums)) order by b;

        the set resulting from this operation (union) does not include a column named b

        select b from nums group by a;

        b is functionally dependant on the primary key a

        select user, password from users;

        see select user; alone. I suggest you always qualify your columns

        I’ll add my own “wat”:

        select sum(nums.a) from nums group by ();

      2. 3

        This makes me wish we had a better 4GL for RDBMSes than SQL. The abstractions it enables are so powerful (and how it enables rich query planners to do what you mean, not as you say), but the language is clumsy and syntax a bit archaic (way too PL/I and “plain English” which is anything but, we could make query planning more predictable/less stochastic); we’ve learned a lot in these decades, and it’d be nice to get a do-over of some mistakes. My biggest sadness with NoSQL is that it threw out the schemas-and-foreign keys world, when that was never the problem with RDBMSes.

        Prior art: QUEL on Ingres.

        1. 2

          I’m (very slowly) working on it - https://scattered-thoughts.net/writing/imp-intro/

        2. 3
          jamie=# select null = null;
           ?column?
          ----------
           f
          (1 row)
          

          What version of Postgres behaves like this?

          fudo=# select version();
                                                            version                                                  
          -----------------------------------------------------------------------------------------------------------
           PostgreSQL 9.2.10 on x86_64-unknown-freebsd10.4, compiled by gcc (FreeBSD Ports Collection) 4.8.5, 64-bit
          (1 row)
          
          fudo=# select null = null;
           ?column? 
          ----------
           
          (1 row)
          
          fudo=# 
          
          fudo=# select version();
          ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
          │                                                                       version                                                                       │
          ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
          │ PostgreSQL 11.5 on amd64-portbld-freebsd12.0, compiled by FreeBSD clang version 6.0.1 (tags/RELEASE_601/final 335540) (based on LLVM 6.0.1), 64-bit │
          └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
          (1 row)
          
          Time: 0.581 ms
          fudo=# select null = null;
          ┌──────────┐
          │ ?column? │
          ├──────────┤
          │          │
          └──────────┘
          (1 row)
          
          Time: 0.275 ms
          
          1. 1

            Oh oops, I must have screwed up while pasting stuff. Thanks.

          2. 2

            woah that

            select b from nums group by a
            select a from nums group by b
            

            behavior is pretty weird.

            I chuckled at the

            select a+1 from nums group by a+1
            select a+1 from nums group by 1+a
            

            I implemented ONLY_FULL_GROUP_BY in a commercial dbms couple years ago and… yah I think we still have similar behavior. Our check recurses through subexpressions in project list operators and stops if it finds the current expression in the group by list. Notably, it doesn’t recurse over subexpressions in the group by list. Something something there must be a better way something something relational algebra.

            The only_full_group_by stuff also has to manage the having clauses and window functions. (Those portions were added later and I did not write that code).

            Yah SQL is cool. Thanks for sharing this list of garbo queries, I enjoyed the read :)