Re: problems of comparators in Oracle

tkyte_at_us.oracle.com (Thomas J Kyte) wrote:
>Wilfred Ng <siuhungn> wrote:
>
>>Hello,
 

>>I find that it is impossible to use >, <, => or =< to compare
>>tuple projections greater than 1. For example, (Att1, Att2)>((1,2)).
>>However, in the Oracle manual(v6), it states that the comparsion
>>more than 1-arity is allowed such as (A,B)> all((1,2),(3,1)).
>>The comparsion will do according to lexciographical ordering. Anybody
>>can help?
>>
>
>Is (1,2) >= (2,1)????

According to lexciographical ordering (i.e. dictionary order) the answer is no but yes for (2,1) >= (1,2) because the 1st component of left side(i.e.,2) is greater than the 1st component of right side (i.e.,1).

More examples:

(2,1,3) > (1,2,4) yes
(9,5,7) > (9,7,5) No
(d,o,g) > (c,a,t) yes as in dictionary  

>
>What would it mean to compare these two projections? Should they be considered
>vectors and compare the magnitude (if so, perform the math and compare the
>scalar results).

It is natural to consider the comparsion according to the lexicographical ordering for vector and as useful as you look into a dictionary book to find a word. But I have no idea to formulate this comparsion in terms of scalar comparsion in Oracle. So do you have any good suggestion?

>
>In the manuals (i don't have a version 6 one sitting around), they describe
>that:
>
> a > all( d,e,f,g,h )
>
>is valid, but (a,b) > all( (d,e), (f,g) )
>
>would not be.
>

In reference Maual v6 SQL language sec 4-3 it DOES state that: (a,b) >= all((1400,300),(3000,0)) is valid. But actually Oracle DOES NOT support this!?

>
>>In fact it works fine for equality comparsion, say, (Att1, Att2) = ((1,2)).
>
>(1,2) = (1,2) would be true. (1,2) > (1,2) is not known however, the operators
>such as >, <, etc need scalars to compare with. > does not make sense with a
>multi-column projection.

Sorry but I have different opinion, > makes very good sense in multi-column projection see : A Guide to the SQL standard by C.J. Date(3rd ed) p155 about comparison conditions. It is well-defined and useful but unfortunately Oracle does not support this.

>
>
>>Cheers,
>>Wilfred.
>
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>
>
>--------------------------------------------------------
>opinions and statements are mine and do not necessarily
>reflect the opinions of Oracle Corporation.
>
Received on Fri May 03 1996 - 00:00:00 CEST