Re: proposal: Preference SQL

Yes, the preference clause can be rewritten using standard SQL. The syntax to duplicate the example result set is listed below. The syntax is not very flexible or easy to read.

select id
from computer
where (main_memory = (select max(main_memory)
from computer)
and cpu_speed = (select max(cpu_speed)
from computer
where cpu_speed < (select max(cpu_speed) from computer)))
or (cpu_speed = (select max(cpu_speed)
from computer)
and main_memory = (select max(main_memory)
from computer
where main_memory < (select max(main_memory) from computer)))
;
~
Kevin Walker

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Jan Urbanski
Sent: Saturday, May 31, 2008 7:34 AM
To: Stephen R. van den Berg
Cc: Postgres - Hackers
Subject: Re: [HACKERS] proposal: Preference SQL

Stephen R. van den Berg wrote:
> Jan Urbański wrote:
>> An example of a preference query would be (quoting the linked PDF):
>
>> SELECT * FROM programmers PREFERRING exp IN ('java', 'C++'); or
>> SELECT * FROM computers PREFERRING HIGHEST(main_memory) AND
>> HIGHEST(cpu_speed);
>
> Forgive my ignorance, but it appears that this can already be achieved
> by using a properly weighted ORDER BY clause, as in:
>
> SELECT * FROM computers
> ORDER BY HIGHEST(main_memory) DESC, HIGHEST(cpu_speed) DESC;

No, these are quite different. Consider a table with three columns: id, main_memory, cpu_speed containing four tuples:
id main_memory cpu_speed
---------------------------------------------------
comp1 100 80
comp2 80 100
comp3 100 70
comp4 60 60

Now the result of a SELECT id FROM computers PREFERRING
HIGHEST(main_memory) AND HIGHEST(cpu_speed) would be:
id
---------
comp1
comp2

This is because comp1 and comp2 are incomparable under the partial order defined by the preferences. comp1 has the largest main memory and comp2 the fastest CPU, but the preference states you like main memory just as much as CPU speed, so you get both tuples in the result. On the other hand, comp3 is not in the result set, because comp1 is greater than it under the preference partial order. The main_memory preference is satisfied by comp3 just as well as it is by comp1, but the cpu_speed preference is worse. The same goes for comp4.

And all this is significantly different from an ORDER BY, because first it doesn't throw away any rows and second it gives you a linear order, where every tuple can be compared with another. The clause you proposed (though it's not legal in PG, because there is no HIGHEST function,
right?) would, as I understand it, prefer main memory more than CPU speed.

There are still some issues about the exact meaning of a PREFERRING clause, but it is very different from a simple ORDER BY (and it has more options than just PREFERRING and AND).
Anyway, from what I've read most or all preference clauses can be rewritten to standard clauses, but sometimes it's difficult, and many times it's costly.

Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

_________________________________________________________________
Make every e-mail and IM count. Join the i'm Initiative from Microsoft.
http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_ MakeCount