CODEWITHSUNDEEP
pythondjangopostgresqlmigration
Dave
Dave

Reputation: 11879

SELECT DISTINCT, ORDER BY created by Django works in MySQL and SQLite, not PostgreSQL

I just switched from mysql to postgres (yay!) but now on my postgres server (only) I'm getting an error that:

SELECT DISTINCT, ORDER BY expressions must appear in select list

It's the same query:

SELECT DISTINCT * FROM table ORDER BY '?'

BTW this is a query auto-generated by django. Alternatively, any way to get django to actually render correct sql?

Addendum

It turns out that for whatever reason, with my Postgres 8.4 production server, Django was quashing the error, while with my Postgres 9.0 dev server it was not. I have no idea if it was related to the Postgres versions or not, but it WAS in fact erroring in both Postgreses and NOT in MySQL.

Upvotes: 0

Views: 1217

Answers (2)

Dave
Dave

Reputation: 11879

As @Catcall said, technically this statement is illegal and nonsensical in SQL.

However, MySQL and SQLite allow you to do a SELECT DISTINCT id FROM test ORDER BY whatever, while PostgreSQL does not. It's as simple as that.

MySQL and SQLite may produce non-deterministic results using this method, I'm not sure.

However if you are ordering by '?', it should be fine.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

I'd expect all SQL platforms that comply with SQL standards to raise a similar error.

Think about it this way.

CREATE TABLE test (
    id INTEGER NOT NULL,
    c CHAR(1) NOT NULL,
    PRIMARY KEY (id, c)
);

INSERT INTO test VALUES 
    (1, 'a'),
    (1, 'z'),
    (2, 'b');

SELECT * FROM test ORDER BY c;

id  c
--  -
1   a
2   b
1   z

Now think about what this next statement means.

SELECT DISTINCT id FROM test ORDER BY c;

Which value of 'c' should the dbms use to position 'id' 1 in the sort order? If it chooses 'a', the order is '1,2'. If it chooses 'z', the order is '2,1'.

Upvotes: 2

Related Questions