Thread: AW: [HACKERS] Re: Subselects open issue Nr. NEW

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
> ----------
> Von:     Bruce Momjian[SMTP:maillist@candle.pha.pa.us]
> Gesendet:     Dienstag, 17. Februar 1998 17:15
> An:     Zeugswetter Andreas SARZ
> Cc:     pgsql-hackers@hub.org
> Betreff:     Re: [HACKERS] Re: Subselects open issue Nr. NEW
>
> >
> > 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
>
>

> 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