i would like to write about a things that are not really errors by themselves – they are simply situations when database acts in undesired way. a way that's perfectly logical, but where the logic is not always clearly seen “at first sight".

so, don't expect any postgresql-bashing. if you want some, indicate so in comments, i'll find some things to bash postgresql for.

but in here i'd rather bash us – people – for making mistakes. the ones that are not really easy to see as mistakes for database.

first gotcha is the one that bite me some time ago, but was engreved in my brain so hard, that i recently even thought i saw the same problem in situation where it simply wasn't.

let's see this simple sql:

SELECT * FROM x WHERE i ! IN (3, 5);

do you see the problem?

if yes – congratulations. you're the lucky one :). i see it clearly now, but i wasn't seeing it as clearly some time ago.

what is the problem, some of you might ask?

technically – it is possible that there is no problem – it all depends on what you wanted to do.

let's see how it works:

# CREATE TABLE x (i int4);
CREATE TABLE
# INSERT INTO x (i) SELECT * FROM generate_series(1,10);
INSERT 0 10
# SELECT * FROM x WHERE i ! IN (3, 5);

how many rows will that query return?

if you have a strong programming background you might be tempted to say that 8 rows, with values: 1, 2, 4, 6, 7, 8, 9, 10.

if this was your answer – you was wrong. this query doesn't return any rows. why? because “!" is not negation operator.

it is factorial! let's see:

# SELECT i, i! FROM x;
 i  | ?COLUMN?
----+----------
  1 |        1
  2 |        2
  3 |        6
  4 |       24
  5 |      120
  6 |      720
  7 |     5040
  8 |    40320
  9 |   362880
 10 |  3628800
(10 ROWS)

so my query was trying to: select * from x where (i!) in (3,5). there are no such values that would have factorial of 3 or 5.

now – you might say it's obvious, and nobody should write “!" in sql – we should use “not". and you're right. i know. but once in a while you just write this kind of thing, and then you spend some time wondering – what's wrong with pg, that is breaks on such an easy query.

it might get even worse. let's imagine you're doing something like this on a table which holds large numbers. or simply with larger number of rows. oops 🙂

second gotcha for today is even simpler.

i was asked to check for some things and i noticed query (about 5 lines long) with a lot of joins. and this condition:

... where ... and date_field > 2007-01-01 and some_other_field = 1 and ...

again – do you see the problem? i'm sure most of you do. if not, let's see something simple:

# EXPLAIN analyze SELECT 1 WHERE now()::DATE > 2007-01-01;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 RESULT  (cost=0.01..0.02 ROWS=1 width=0) (actual TIME=0.146..0.148 ROWS=1 loops=1)
   One-TIME FILTER: (((now())::DATE)::text > '2005'::text)
 Total runtime: 0.216 ms
(3 ROWS)

take a look at “filter" line. “> ‘2005' ? where did this 2005 come from?

it's simple: 2007-01-01 is not a date. it's aritmetical formula, that can be written in a more readable way as: 2007 – 1 – 1. which is 2005.

to be perfectly fair – this issue has been fixed in 8.3. if i would issue it in 8.3 i would get:

# EXPLAIN analyze SELECT 1 WHERE now()::DATE > 2007-01-01;
ERROR:  operator does NOT exist: DATE > INTEGER
LINE 1: EXPLAIN analyze SELECT 1 WHERE now()::DATE > 2007-01-01;
                                                   ^
HINT:  No operator matches the given name AND argument TYPE(s). You might need TO ADD explicit TYPE casts.

does it help? sure it does. it will most probably break some existing application. break in a positive way – they have a code that doesn't really do what they think it does, but in 8.3 it will raise exception.

last gotcha for today is a nasty one. i actually wrote about it some time ago (in polish, as a brain teaser), but lately found exactly the same situation in real-life code.

let's see it:

# CREATE TABLE FIRST ( first_id int8, text1 text );
CREATE TABLE
# CREATE TABLE SECOND ( second_id int8, text2 text );
CREATE TABLE
# INSERT INTO FIRST (first_id, text1) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
INSERT 0 4
# INSERT INTO SECOND (second_id, text2) VALUES (1, 'e'), (2, 'a'), (3, 'z');
INSERT 0 3
# SELECT * FROM FIRST;
 first_id | text1
----------+-------
        1 | a
        2 | b
        3 | c
        4 | d
(4 ROWS)
# SELECT * FROM SECOND;
 second_id | text2
-----------+-------
         1 | e
         2 | a
         3 | z
(3 ROWS)

everything clear. now think about this query:

SELECT * FROM FIRST WHERE text1 IN ( SELECT text1 FROM SECOND );

at first you might say – simple, returns 1 row, the one with first_id 1, because it has the only shared letter (‘a').

then you might notice that there is a typo – select text1 from second. there is no ‘text1' field in second table! so, it will return error.

in fact it will happily return all rows from first table:

# SELECT * FROM FIRST WHERE text1 IN ( SELECT text1 FROM SECOND );
 first_id | text1
----------+-------
        1 | a
        2 | b
        3 | c
        4 | d
(4 ROWS)

why? because it become so called ‘correlated subquery' – i'm not good at teaching, so if you don't understand it search google, or ask your sql guru – sorry, but i'm really lousy at explaining things.

technically – postgresql did what it was told to. was it the desired outcome? not really likely. we can't remove correlated subqueries from postgresql because they are highly useful.

how can we protect ourselves from this kind of typos? 2 things:

• don't name columns in such a way!
• always use table aliasing. then you will have to make 2 typos to get in this situation. aliased query: select f.* from first f where f.text1 in (select s.text1 from second s); will break because now we tell postgresql exactly from which table it should get the field. and in second table there is no text1 field.

so, as i said earlier – nothing really spectacular. nothing to say “postgresql is bad, because it breaks when …". just some “gotchas" 🙂

with some “luck" i will write about next gotchas some other day – life is full of human errors.