Michael Hirohama wrote:
> 
> [...]
> >SQL> select * from a where (x,z) >= ANY (select y, l from b);
> >select * from a where (x,z) >= ANY (select y, l from b)
> >                            *
> >ERROR at line 1:
> >ORA-00920: invalid relational operator
> >
> >: only '=' and '<>' are allowed if there are more than one
> >expression on the left side of clause with subselect...
> >
> >Is this in standard or Oracle' preference ?
> 
> This is a mathematical law.
> 
> Vectors cannot be strictly ordered.  "Partial orderings" are possible.
> 
> Let A be (1, 2)
> Let B be (4, 7)
> Let C be (3, 5)
> Let D be (5, 10)
> 
> A is smallest; D is largest; how do B and C relate?

I understand this. And this is how it works currently:

select * from tab where (A,B) >= ANY (select X, Y from tab2);

means: select tuples where A >= X _and_ B >= Y for some tuple from tab2.
                                  ^^^^^
         'AND' is used for all Op-s except for '<>' when 'OR' is used.

Question is "should we drop this feature (?) or leave it as is ?"

Comments ?

Vadim

Tue, 17 Feb 1998 10:31:16 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: Subselects open issue Nr. NEW
At 10:31 +0700 2/17/98, Vadim B. Mikheev wrote:
[...]
>
>I understand this. And this is how it works currently:
>
>select * from tab where (A,B) >= ANY (select X, Y from tab2);
>
>means: select tuples where A >= X _and_ B >= Y for some tuple from tab2.
>                                  ^^^^^
>         'AND' is used for all Op-s except for '<>' when 'OR' is used.
>
>Question is "should we drop this feature (?) or leave it as is ?"
>
>Comments ?
>
>Vadim

I recommend dropping this feature and only supporing =ANY and <>ANY.
Supporing the relational operators cannot be optimized in the general case.

If I needed to perform the <=ANY query efficiently, I might use a
functional index on 'A' and 'B' using an R-tree method.

Regards,

--
Michael Hirohama <kamesan@ricochet.net>



Mon, 16 Feb 1998 20:14:40 -0800
Michael Hirohama <kamesan@ricochet.net>
Re: [HACKERS] Re: Subselects open issue Nr. NEW
Vadim B. Mikheev writes:
> I understand this. And this is how it works currently:
> 
> select * from tab where (A,B) >= ANY (select X, Y from tab2);
> 
> means: select tuples where A >= X _and_ B >= Y for some tuple from tab2.
>                                   ^^^^^
>          'AND' is used for all Op-s except for '<>' when 'OR' is used.
> 
> Question is "should we drop this feature (?) or leave it as is ?"

If we have it, please keep it. Yes, it's not really standard but with some
docu that doesn't hurt. It's still better than an error message IMO. Also
this does make sense as a shortcut version of asking both to be greater or
equal. Did you ever try to do something like that in Oracle? I would have
loved this feature.

Michael

-- 
Dr. Michael Meskes, Project-Manager    | topsystem Systemhaus GmbH
meskes@topsystem.de                    | Europark A2, Adenauerstr. 20
meskes@debian.org                      | 52146 Wuerselen
Go SF49ers! Go Rhein Fire!             | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux!                  | Fax: (+49) 2405/4670-10

Tue, 17 Feb 1998 15:50:00 +0100 (CET)
Michael Meskes <meskes@topsystem.de>
Re: [HACKERS] Re: Subselects open issue Nr. NEW