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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ([email protected]) 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
