Thread

1. Re: Subselects open issue Nr. NEW

   Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-17T03:31:16Z

   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
   
   

2. Re: [HACKERS] Re: Subselects open issue Nr. NEW

   Michael Hirohama <kamesan@ricochet.net> — 1998-02-17T04:14:40Z

   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>
   
   
   
   

3. Re: [HACKERS] Re: Subselects open issue Nr. NEW

   Michael Meskes <meskes@topsystem.de> — 1998-02-17T14:50:00Z

   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