Re: 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