SELECT DISTINCT with ORDER BY

I recently wrote a query in MySQL that didn't seem to be returning the right results, and at first I couldn't figure out why. Here is a toy example, where we are tracking pages and page views (one-to-many relationship):


create table page (
    page_id integer unsigned primary key,
    name varchar(32) not null,
    created datetime not null
) engine=InnoDB;

create table page_view (
    page_view_id integer unsigned primary key,
    page_id integer unsigned not null,
    created datetime not null,
    
    foreign key (page_id) references page (page_id) on delete cascade
) engine=InnoDB;


What I want to get is the most recently viewed pages. Let's say I have the following data in my tables:


mysql> select * from page;
+---------+--------+---------------------+
| page_id | name   | created             |
+---------+--------+---------------------+
|       1 | page 1 | 2000-01-01 00:00:00 |
|       2 | page 2 | 2000-01-02 00:00:00 |
|       3 | page 3 | 2000-01-03 00:00:00 |
|       4 | page 4 | 2000-01-04 00:00:00 |
+---------+--------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from page_view;
+--------------+---------+---------------------+
| page_view_id | page_id | created             |
+--------------+---------+---------------------+
|            1 |       3 | 2000-01-01 00:00:00 |
|            2 |       1 | 2000-01-02 00:00:00 |
|            3 |       1 | 2000-01-03 00:00:00 |
|            4 |       3 | 2000-01-04 00:00:00 |
|            5 |       2 | 2000-01-05 00:00:00 |
|            6 |       4 | 2000-01-06 00:00:00 |
|            7 |       2 | 2000-01-07 00:00:00 |
+--------------+---------+---------------------+
7 rows in set (0.00 sec)


What I want to get back is page 2 (most recently viewed), then page 4, then page 3, then page 1.

So I write my query:


mysql> select distinct p.page_id, p.name, p.created from page p join page_view pv on p.page_id = pv.page_id order by pv.created desc;
+---------+--------+---------------------+
| page_id | name   | created             |
+---------+--------+---------------------+
|       4 | page 4 | 2000-01-04 00:00:00 |
|       2 | page 2 | 2000-01-02 00:00:00 |
|       1 | page 1 | 2000-01-01 00:00:00 |
|       3 | page 3 | 2000-01-03 00:00:00 |
+---------+--------+---------------------+
4 rows in set (0.00 sec)


That's not right at all! What's going on?

The problem is that I'm using distinct just on the page table, but ordering by the page_view table. Since there is a many-to-one, what is the database supposed to do when a page has multiple views? which view should it use for the order by?

What I wanted the query to do is first join, then order, then apply the distinct. That's not what MySQL does, though. It first joins, then applies the distinct, then orders the results (or something like that). You can think of it like MySQL going sequentially through the page_view table, finding rows with distinct page ids. So it would pick rows 1,2,5,6:


+--------------+---------+---------------------+
| page_view_id | page_id | created             |
+--------------+---------+---------------------+
|            1 |       3 | 2000-01-01 00:00:00 |
|            2 |       1 | 2000-01-02 00:00:00 |
|            5 |       2 | 2000-01-05 00:00:00 |
|            6 |       4 | 2000-01-06 00:00:00 |
+--------------+---------+---------------------+
4 rows in set (0.00 sec)


You can see that if you order those by created, you get the page order that the (badly written) query returned (4,2,1,3).

We can force MySQL to do things in the order we want by changing the query to:


mysql> select distinct p.page_id, p.name, p.created from (select p.page_id, p.name, p.created from page p join page_view pv on p.page_id = pv.page_id order by pv.created desc) as p;
+---------+--------+---------------------+
| page_id | name   | created             |
+---------+--------+---------------------+
|       2 | page 2 | 2000-01-02 00:00:00 |
|       4 | page 4 | 2000-01-04 00:00:00 |
|       3 | page 3 | 2000-01-03 00:00:00 |
|       1 | page 1 | 2000-01-01 00:00:00 |
+---------+--------+---------------------+
4 rows in set (0.00 sec)


But I think that's kind of a hack, and depends on MySQL doing the distinct in a certain order (I don't think order by in a subquery is standard sql, and shouldn't necessarily constraint the order of the entire query). So what's the "right" way to write this type of query?

Before I tackled that, I thought, "What would a strict database like PostgreSQL do with this type of query?" My hope was that it would throw it out altogether. And it does. Here's what I get:


postgres=# select distinct t1.id, t1.name, t1.created from table1 t1 join table2 t2 on t1.id = t2.table1_id order by t2.created desc;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list


That's much better, and the error message is very helpful, and makes sense. So here's the query I came up with that will give the correct results, and is correct SQL, in MySQL...:


mysql> select p.page_id, p.name, p.created from page p join (select page_id, max(created) as created from page_view group by page_id) v on p.page_id = v.page_id order by v.created desc;
+---------+--------+---------------------+
| page_id | name   | created             |
+---------+--------+---------------------+
|       2 | page 2 | 2000-01-02 00:00:00 |
|       4 | page 4 | 2000-01-04 00:00:00 |
|       3 | page 3 | 2000-01-03 00:00:00 |
|       1 | page 1 | 2000-01-01 00:00:00 |
+---------+--------+---------------------+
4 rows in set (0.00 sec)

...and in PostgreSQL:

postgres=# select p.page_id, p.name, p.created from page p join (select page_id, max(created) as created from page_view group by page_id) v on p.page_id = v.page_id order by v.created desc;
page_id |  name  |       created       
---------+--------+---------------------
       2 | page 2 | 2000-01-02 00:00:00
       4 | page 4 | 2000-01-04 00:00:00
       3 | page 3 | 2000-01-03 00:00:00
       1 | page 1 | 2000-01-01 00:00:00
(4 rows)


Is there a better performing query out there to do the same thing? I'd love to know, please leave a comment! :)

PostgreSQL transactions and error handling

I'm taking our web app that runs on MySQL and seeing what it would take to get it running on PostgreSQL. I just discovered one rather glaring difference between PostgreSQL and most other DBMSs.

Here is an example to demonstrate:

I have a table: my_table, with the following row:


----------------
| id  |  val   |
----------------
| 3   | row 3  |
----------------


Let's say I want to make sure I have rows for ids 1 through 5 in the table. This is a case where I would use MySQL's 'INSERT IGNORE' statement, which doesn't exist in PostgreSQL. I have at least two options: I can create a stored procedure to do it, or I can do it in code.

Let's say I create a procedure:


CREATE OR REPLACE FUNCTION my_proc() RETURNS void AS
$$
BEGIN
  FOR i IN 1..5 LOOP
  BEGIN
    INSERT INTO my_table(id, val) VALUES (i, 'row ' || i);
  EXCEPTION WHEN unique_violation THEN
  -- do nothing
  END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;


All is well, this type of exception handling is standard in stored procedures. Here's the entire test:


postgres=# create database my_test;
CREATE DATABASE
postgres=# \c my_test;
You are now connected to database "my_test".
my_test=# create table my_table (id INTEGER PRIMARY KEY, val VARCHAR(64));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "my_table_pkey" for table "my_table"
CREATE TABLE
my_test=# CREATE OR REPLACE FUNCTION my_proc() RETURNS void AS
my_test-# $$
my_test$# BEGIN
my_test$#   FOR i IN 1..5 LOOP
my_test$#   BEGIN
my_test$#     INSERT INTO my_table(id, val) VALUES (i, 'row ' || i);
my_test$#   EXCEPTION WHEN unique_violation THEN
my_test$#   -- do nothing
my_test$#   END;
my_test$#   END LOOP;
my_test$# END;
my_test$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
my_test=# insert into my_table values (3,'row 3');
INSERT 0 1
my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
(1 row)

my_test=# select my_proc();
 my_proc 
---------
 
(1 row)

my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
  1 | row 1
  2 | row 2
  4 | row 4
  5 | row 5
(5 rows)

my_test=# 


Now here's the twist - you can't (really) do the same thing outside of a stored procedure (without using savepoints, as I'll get to later). Here's what happens:


my_test=# \set AUTOCOMMIT OFF
my_test=# delete from my_table where id <> 3;
DELETE 4
my_test=# commit;
COMMIT
my_test=# insert into my_table values (1, 'row 1');
INSERT 0 1
my_test=# insert into my_table values (2, 'row 2');
INSERT 0 1
my_test=# insert into my_table values (3, 'row 3');
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
my_test=# insert into my_table values (4, 'row 4');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
my_test=# insert into my_table values (5, 'row 5');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
my_test=# commit;
ROLLBACK
my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
(1 row)

PostgreSQL forces you to rollback a transaction that hits any error. There is no exception handling! Granted, you can get around this by using savepoints:


my_test=# insert into my_table values (1, 'row 1');
INSERT 0 1
my_test=# insert into my_table values (2, 'row 2');
INSERT 0 1
my_test=# SAVEPOINT my_hack;
SAVEPOINT
my_test=# insert into my_table values (3, 'row 3');
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
my_test=# ROLLBACK TO SAVEPOINT my_hack;
ROLLBACK
my_test=# insert into my_table values (4, 'row 4');
INSERT 0 1
my_test=# insert into my_table values (5, 'row 5');
INSERT 0 1
my_test=# commit;
COMMIT
my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
  1 | row 1
  2 | row 2
  4 | row 4
  5 | row 5
(5 rows)

my_test=# 


But I see this as more of a hack than a real solution.

I can see arguments for both sides of this issue. I really like this conversation about the issue: http://www.nabble.com/25P02,-current-transaction-is-aborted,-commands-ignored-until-end-of-transaction-block-td3710080.html . But what bugs me is that stored procedures can do exception handling, but no one else can. Especially in my case, where I'm using Java and JDBC, which throws exceptions for any errors that come back. I would rather do my own exception handling, or at least have the option of doing my own.

So here's my vote for changing PostgreSQL to behave more like MySQL in this case.