SELECT wat FROM sql

Published 2020-04-16

Working on a postgres-compatible query compiler has taught me many things.

Things I was better off not knowing.

Let's make a database:

jamie=# create table nums(a int primary key, b text);
CREATE TABLE

jamie=# insert into nums(a,b) values (0, 'foo'), (1, 'foo'), (2, 'foo'), (3, 'bar');
INSERT 0 4

What could possibly go wrong?


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;
               ^

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)

jamie=# (select a from nums) order by b;
 a
---
 3
 0
 1
 2
(4 rows)

jamie=# ((select a from nums) union (select a from nums)) order by b;
ERROR:  column "b" does not exist
LINE 1: ...(select a from nums) union (select a from nums)) order by b;

jamie=# select a+1 from nums group by "?column?";
 ?column?
----------
        3
        4
        2
        1
(4 rows)

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;

jamie=# create table """" (x int);
CREATE TABLE
jamie=# \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | "    | table | jamie
 public | nums | table | jamie
(2 rows)

jamie=# select null = null;
 ?column?
----------

(1 row)

jamie=# select array[null] = array[null];
 ?column?
----------
 t
(1 row)

jamie=# select '{foo,bar,null}'::text[];
      text
----------------
 {foo,bar,NULL}
(1 row)

jamie=# select '{"ABC\n"}'::text[] = ARRAY['ABCn'];
 ?column?
----------
 t
(1 row)

jamie=# select array_dims(array['a', 'a'] :: text[]);
 array_dims
------------
 [1:2]
(1 row)

jamie=# select array_dims(array['a'] :: text[]);
 array_dims
------------
 [1:1]
(1 row)

jamie=# select array_dims(array[] :: text[]);
 array_dims
------------

(1 row)

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)

jamie=# select 1, generate_series(1,2), generate_series(1,3);
 ?column? | generate_series | generate_series
----------+-----------------+-----------------
        1 |               1 |               1
        1 |               2 |               2
        1 |                 |               3
(3 rows)

jamie=# select generate_series(generate_series(1,2), generate_series(3,100));
 generate_series
-----------------
               1
               2
               3
               2
               3
               4
(6 rows)

jamie=# select jsonb_array_elements(jsonb_array_elements('[[1,2],[3,4]]')), jsonb_array_elements('[7,8,9]');
 jsonb_array_elements | jsonb_array_elements
----------------------+----------------------
 1                    | 7
 2                    | 7
 3                    | 8
 4                    | 8
(4 rows)

jamie=# select ('[["foo"]]'::jsonb)->>0;
 ?column?
----------
 ["foo"]
(1 row)

jamie=# select ('["foo"]'::jsonb)->>0;
 ?column?
----------
 foo
(1 row)

jamie=# select ('"foo"'::jsonb)->>0;
 ?column?
----------
 foo
(1 row)

jamie=# select ('1'::jsonb)::int;
 int4
------
    1
(1 row)

jamie=# select ('true'::jsonb)::bool;
 bool
------
 t
(1 row)

jamie=# select ('"foo"'::jsonb)::text;
 text
-------
 "foo"
(1 row)

jamie=# select ('1'::jsonb)->>0;
 ?column?
----------
 1
(1 row)

jamie=# select ('true'::jsonb)->>0;
 ?column?
----------
 true
(1 row)

jamie=# select ('"foo"'::jsonb)->>0;
 ?column?
----------
 foo
(1 row)

Thanks to Nikhil Benesch, Justin Jaffray and several decades of "wouldn't it be nice if..." for contributions.

There will likely be a sequel.