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@postgresql.org [mailtogsql-hackers-owner@postgresql.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@postgresql.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...source=EML_WL_ MakeCount

Kevin Walker wrote:
> 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)))

Well, that's not 100% correct, but the idea is something like this. In
particular, if you'd have only one entry in the table, then this query
would not return any rows, which would be wrong. Also, if you had a
computer that has larger main memory and a faster CPU than any other
copmuter, it should be returned as the result, but the above query would
fail to do that.

The point is not rewriting that particular preference query into a
standard query. The point is whether it's worth having an automated
mechanism for executing arbitrary preference queries with complex
preferences (again: the syntax is richer, I didn't want to go into any
detail about it before getting some feedback).

Let me give you a more sophisticated example.
You have a webpage that sells used cars. You have your typical search
form with car make, colour, engine power and so on. Normally, you would
make the search form input fields correspond to SQL WHERE clauses. So,
if I want a white Honda with a 180 hp engine and about 40k kilometers of
mileage I enter these parameters and hit the submit button. Now imagine
I don't get any results for my search. That could mean that you have no
Honda cars in stock, but it can also mean that you have my perfect Honda
at a bargain price, it's only that it's black. Or maybe you have a
Honda, but it has a 160 hp engine? Or is it just that the one perfect
Honda you have has a mileage of just over 41k km, and that's why I
didnt' get it in my result set? People seldom want a perfect match when
they are searching for something. They want the best match, they can
get. So, if I wanted to get a Honda with a decent engine and my
favourite color is white, I'd say:

SELECT * FROM cars WHERE make = 'Honda' PREFERRING (power = '180' AND
mileage AROUND 40000) CASCADE color = 'white';

Remember, that an AND in a preference clause constructs a partial order.
The query says: I equally prefer having a 180 hp engine and having a car
that has a mileage of 40k km. Tha CASCADE clause intrudoces a less
important preference. It means that the color is not as important to me
as power and mileage, but if I had a choice I'd take the white one.

I'd strongly recommend skimming through the paper I mentioned in my
first email, it explains stuff much better than I do.

Preference SQL in Postgres could, as I see it, become one of the
distinct features that no other widespread database system has and that
could potentially be massively useful for online shops, social networks,
etc. - you name it.
After hearing Bruce's keynote at PGCon and how Postgres now should be
aiming at more that just catching up with the big guys I just thought:
well, that's one neat feature that none of them has, that's useful,
that's kind of sexy *and* I could get my degree out of it.

Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

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

Jan,

;-) ... agree with your points. Note I wasn't trying to duplicate the full functionality of the preferences clause. ...just pointing out that the example presented could be duplicated with standard SQL with the result being both ugly and confusing.

My intent was show that the syntax you proposed was easier to read and more flexible.

Kevin



> Date: Sat, 31 May 2008 16:53:54 +0200
> From: j.urbanski@students.mimuw.edu.pl
> To: skywalkereast@hotmail.com
> CC: srb@cuci.nl; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] proposal: Preference SQL
>
> Kevin Walker wrote:
> > 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)))
>
> Well, that's not 100% correct, but the idea is something like this. In
> particular, if you'd have only one entry in the table, then this query
> would not return any rows, which would be wrong. Also, if you had a
> computer that has larger main memory and a faster CPU than any other
> copmuter, it should be returned as the result, but the above query would
> fail to do that.
>
> The point is not rewriting that particular preference query into a
> standard query. The point is whether it's worth having an automated
> mechanism for executing arbitrary preference queries with complex
> preferences (again: the syntax is richer, I didn't want to go into any
> detail about it before getting some feedback).
>
> Let me give you a more sophisticated example.
> You have a webpage that sells used cars. You have your typical search
> form with car make, colour, engine power and so on. Normally, you would
> make the search form input fields correspond to SQL WHERE clauses. So,
> if I want a white Honda with a 180 hp engine and about 40k kilometers of
> mileage I enter these parameters and hit the submit button. Now imagine
> I don't get any results for my search. That could mean that you have no
> Honda cars in stock, but it can also mean that you have my perfect Honda
> at a bargain price, it's only that it's black. Or maybe you have a
> Honda, but it has a 160 hp engine? Or is it just that the one perfect
> Honda you have has a mileage of just over 41k km, and that's why I
> didnt' get it in my result set? People seldom want a perfect match when
> they are searching for something. They want the best match, they can
> get. So, if I wanted to get a Honda with a decent engine and my
> favourite color is white, I'd say:
>
> SELECT * FROM cars WHERE make = 'Honda' PREFERRING (power = '180' AND
> mileage AROUND 40000) CASCADE color = 'white';
>
> Remember, that an AND in a preference clause constructs a partial order.
> The query says: I equally prefer having a 180 hp engine and having a car
> that has a mileage of 40k km. Tha CASCADE clause intrudoces a less
> important preference. It means that the color is not as important to me
> as power and mileage, but if I had a choice I'd take the white one.
>
> I'd strongly recommend skimming through the paper I mentioned in my
> first email, it explains stuff much better than I do.
>
> Preference SQL in Postgres could, as I see it, become one of the
> distinct features that no other widespread database system has and that
> could potentially be massively useful for online shops, social networks,
> etc. - you name it.
> After hearing Bruce's keynote at PGCon and how Postgres now should be
> aiming at more that just catching up with the big guys I just thought:
> well, that's one neat feature that none of them has, that's useful,
> that's kind of sexy *and* I could get my degree out of it.
>
> Cheers,
> Jan
>
> --
> Jan Urbanski
> GPG key ID: E583D7D2
>
> ouden estin

__________________________________________________ _______________
E-mail for the greater good. Join the i'm Initiative from Microsoft.
http://im.live.com/Messenger/IM/Join...source=EML_WL_ GreaterGood