Thread: Subselects open issue Nr. NEW

Hm, new one. Oracle 6:

SQL> select * from a where (x,z) in (select y, l from b);

         X          Z
---------- ----------
         1          2

SQL> select * from a where (x,z) = ANY (select y, l from b);

         X          Z
---------- ----------
         1          2

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 ?

Vadim
P.S. Maybe I should ask in DB newsgroups ?..

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

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>

Gosh, please leave it in it is superb, great, fantastic ...
If somebody defines a different behavior as standard in the future,
we will need to tell him that he has a gordian knot in his brains :-)

Andreas

> 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 ?"

Sorry, I take back my gordian knot, I found it in my own brains ;-(
For a <=, <, >=, > a lexical ordering would be more intuitive,
since that is how a compound index would sort.

so (a, b) <= (c, d) would resolve to:
(a <= c) or ((a = c) and (b <= c))

What happens to !=~ ? Should also be _OR_ ed.
I guess that leaves us at a point of no go. Take it out ? *tear drops
falling*

Andreas

> Vectors cannot be strictly ordered.  "Partial orderings" are possible.

I think it should say: an order has to be defined (lexical, vector lenght,
area size ..... )

>
> 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?

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

>
> Gosh, please leave it in it is superb, great, fantastic ...
> If somebody defines a different behavior as standard in the future,
> we will need to tell him that he has a gordian knot in his brains :-)
>
> Andreas
>
> > 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 ?"
>
>
>

I think my recent posting answers this.  You have to comare from
left-to-right until you find an answer.

--
Bruce Momjian
maillist@candle.pha.pa.us