Re: proposal: Preference SQL

<div style="text-align: left;">Yes, the preference clause can be rewritten using standard SQL.  The syntax to duplicate
theexample result set is listed below.  The syntax is not very flexible or easy to read.   <br /><br />select id <br
/>fromcomputer<br />where (main_memory = (select max(main_memory) <br />                      from computer)<br
/>      and cpu_speed = (select max(cpu_speed) <br />                        from computer<br />                       
wherecpu_speed < (select max(cpu_speed) from computer)))<br />   or (cpu_speed = (select max(cpu_speed) <br /> 
;                      from computer)<br />       and   main_memory = (select max(main_memory) <br
/>                       from computer<br />                         where main_memory < (select max(main_memory)
fromcomputer)))<br />;<br />~ <br />Kevin Walker<br /><br />-----Original Message-----<br />From:
pgsql-hackers-owner@postgresql.org[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Jan Urbanski<br />Sent:
Saturday,May 31, 2008 7:34 AM<br />To: Stephen R. van den Berg<br />Cc: Postgres - Hackers<br />Subject: Re: [HACKERS]
proposal:Preference SQL<br /><br />Stephen R. van den Berg wrote:<br />> Jan Ur bański wrote:<br />>> An
exampleof a preference query would be (quoting the linked PDF):<br />> <br />>> SELECT * FROM programmers
PREFERRINGexp IN ('java', 'C++'); or <br />>> SELECT * FROM computers PREFERRING HIGHEST(main_memory) AND <br
/>>>HIGHEST(cpu_speed);<br />> <br />> Forgive my ignorance, but it appears that this can already be
achieved<br />> by using a properly weighted ORDER BY clause, as in:<br />> <br />> SELECT * FROM computers<br
/>>ORDER BY HIGHEST(main_memory) DESC, HIGHEST(cpu_speed) DESC;<br /><br />No, these are quite different. Consider a
tablewith three columns: id, main_memory, cpu_speed containing four tuples:<br />   id          main_memory       
cpu_speed<br/>---------------------------------------------------<br />comp1             100                      
80<br/>comp2             80                      100<br />comp3             100                     70<br
/>comp4            60                        60<br /><br />Now the result of a SELECT id FROM computers PREFERRING<br
/>HIGHEST(main_memory)AND HIGHEST(cpu_speed) would be:<br />    id<br />---------<br />comp1<br />comp2<br /><br />This
isbecause comp1 and comp2 are incomparable under the partial orde r defined by the preferences. comp1 has the largest
mainmemory and comp2 the fastest CPU, but the preference states you like main memory just as much as CPU speed, so you
getboth tuples in the result. On the other hand, comp3 is not in the result set, because comp1 is greater than it under
thepreference partial order. The main_memory preference is satisfied by comp3 just as well as it is by comp1, but the
cpu_speedpreference is worse. The same goes for comp4.<br /><br />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
comparedwith another. The clause you proposed (though it's not legal in PG, because there is no HIGHEST function,<br
/>right?)would, as I understand it, prefer main memory more than CPU speed.<br /><br />There are still some issues
aboutthe exact meaning of a PREFERRING clause, but it is very different from a simple ORDER BY (and it has more optio
nsthan just PREFERRING and AND).<br />Anyway, from what I've read most or all preference clauses can be rewritten to
standardclauses, but sometimes it's difficult, and many times it's costly.<br /><br />Cheers,<br />Jan<br /><br />--<br
/>JanUrbanski<br />GPG key ID: E583D7D2<br /><br />ouden estin<br /><br /><br />--<br />Sent via pgsql-hackers mailing
list(pgsql-hackers@postgresql.org) To make changes to your subscription:<br
/>http://www.postgresql.org/mailpref/pgsql-hackers<br/><br /></div><br /><hr />Make every e-mail and IM count. <a
href="http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_MakeCount" target="_new">Join the i'm Initiative
fromMicrosoft.</a>