Subselects open issue Nr. NEW

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

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 ..... )

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

I allready took my statement back, about an hour after I said this.
But yes, I agree that the left-right approach would be intuitive (same as
compound index).
In mathematics it is called lexical order, I only thought this would be hard
to implement.
Also there are a lot of operators (all negated Op's that) will want an _OR_
e.g. !=~, not only <>

So all not very easy, I'll try to think some more about it.
Andreas

But yes, I agree that the left-right approach would be intuitive (same as
compound index).
In mathematics it is called lexical order, I only thought this would be hard
to implement.
Also there are a lot of operators (all negated Op's that) will want an _OR_
e.g. !=~, not only <>

We had started discussing this back when we implemented the general SQL92
syntax for multiple left-hand arguments in expressions.

One possibility was to make the generalization that any operator with "!" or
"<>" (others too?) in the operator string would be handled as a negation, and
all others otherwise. Then, many operators would "magically" work correctly or
intuitively, while a few, perhaps, would not behave correctly. However, imho
this is preferable to not allowing _any_ of these, and perhaps we would learn
over the next few months a way to tighten it up...

- Tom

Thomas G. Lockhart wrote:

One possibility was to make the generalization that any operator with "!" or
"<>" (others too?) in the operator string would be handled as a negation, and
all others otherwise. Then, many operators would "magically" work correctly or
intuitively, while a few, perhaps, would not behave correctly. However, imho
this is preferable to not allowing _any_ of these, and perhaps we would learn
over the next few months a way to tighten it up...

- Tom

Since we can't tell by the 'name' of the operator what it does (it
works often, but not always) without imposing regulations on the
naming of operators...
...add a column to pg_operator to tell what kind of operator it is:

Group Examples
------- --------
EQ =
NE <>
LT <
GT >
LE <=
GE >=
OTHER <-> <?> @-@ ## *~ !== *=~ etc...

(or give the operators other attributes which can be used to find out
if we should use OR or AND when rewriting '(x,y) OP (z,q)' queries).

... and then just check what group of operator we are dealing with.
If it is "OTHER", then produce an ERROR or NOT IMPLEMENTED message.

This could be useful in other places too. I think I have seen something
about this some weeks or months ago, when somebody had problems finding
out how to select an appropriate operator, and just had to *assume*
that "=" was the equality operator in all cases (which might not always
be true, and will create hard-to-find bugs sooner or later, since
most people will never think of the possibility that someone will use
something else than '=' for equality...)

/* m */

Thomas G. Lockhart <lockhart@alumni.caltech.edu> wrote:

But yes, I agree that the left-right approach would be intuitive (same as
compound index).
In mathematics it is called lexical order, I only thought this would be hard
to implement.
Also there are a lot of operators (all negated Op's that) will want an _OR_
e.g. !=~, not only <>

We had started discussing this back when we implemented the general SQL92
syntax for multiple left-hand arguments in expressions.

One possibility was to make the generalization that any operator with "!" or
"<>" (others too?) in the operator string would be handled as a negation, and
all others otherwise. Then, many operators would "magically" work correctly or
intuitively, while a few, perhaps, would not behave correctly. However, imho
this is preferable to not allowing _any_ of these, and perhaps we would learn
over the next few months a way to tighten it up...

- Tom

Hi,

I think we can handle all operators in one general way,
something like this:

for (i = 0; i < n-1; i++) /* (n-1) iterations here. */
{
if (a[i] is NULL || b[i] is NULL)
return UNKNOWN; /* three-value boolean logic. */
else if (a[i] == b[i])
continue;
else
return (a[i] op b[i]);
}
return (a[n-1] op b[n-1]); /* the last n-th iteration. */

Works well for all standard ==, !=, <, <=, >, >=.

The problem appears if someone would define an operator like
"approximately equals". An operator of this sort should
replace the operator == in the above pseudo-code.
Also someone could have operator "approximately less than"
which uses "approximately equals" as operator == in the above
along with ordinary operators < and ==.

So the problem seems can be solved if we assume that both
operator == and operator op in the above are parameters.

The proposed syntax for tuple comparison predicate could be
(a1, a2,...) [USING EQUALS OPERATOR [op2]] op (subselect)
with op2 defaults to:
op, if "USING EQUALS OPERATOR" is specified,
"==" otherwise.

Any comments ?

To have much more cool object-oriented spirit we could allow
operator overloading for tuples. Let's leave it until later...

--
Yurik