Обсуждение: Proposition to use '==' as synonym for 'IS NOT DISTINCT FROM'

Hi.

I  have  noticed that it would be cool to use '==' in place of 'IS NOT
DISTICT FROM'

What do you think about this crazy idea?

-- 
Best regards,
Eugen Konkov

On Sat, Oct 26, 2019 at 06:41:10PM +0300, Eugen Konkov wrote:
> Hi.
> 
> I  have  noticed that it would be cool to use '==' in place of 'IS NOT
> DISTICT FROM'
> 
> What do you think about this crazy idea?

Turning "IS NOT DISTINCT FROM" into an operator sounds like a great
idea. Let the name bike-shedding begin!

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:
> On Sat, Oct 26, 2019 at 06:41:10PM +0300, Eugen Konkov wrote:
>> I  have  noticed that it would be cool to use '==' in place of 'IS NOT
>> DISTICT FROM'
>> What do you think about this crazy idea?

> Turning "IS NOT DISTINCT FROM" into an operator sounds like a great
> idea.

No it isn't.  For starters, somebody very possibly has used that
operator name in an extension.  For another, it'd be really
inconsistent to have an abbreviation for 'IS NOT DISTINCT FROM'
but not 'IS DISTINCT FROM', so you'd need another reserved operator
name for that, making the risk of breakage worse.

There's an independent set of arguments around why we'd invent a
proprietary replacement for perfectly good standard SQL.

We do have some unresolved issues around how to let dump/restore
control the interpretation of IS [NOT] DISTINCT FROM, cf

https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472bf29735c8%40gmail.com

but I don't think this idea is helping with that at all.

            regards, tom lane

I wrote:
> We do have some unresolved issues around how to let dump/restore
> control the interpretation of IS [NOT] DISTINCT FROM, cf
> https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472bf29735c8%40gmail.com
> but I don't think this idea is helping with that at all.

BTW, taking a step back and viewing this suggestion as "it'd be nice
to have *some* shorter notation than IS [NOT] DISTINCT FROM", maybe
there's a way to unify that desire with the dump/restore fix.  What
we'd really need to fix the dump/restore problem, AFAICS, is to name
the underlying equality operator --- potentially with a schema
qualification --- but then have some notation that says "handle NULLs
like IS [NOT] DISTINCT FROM does".  So instead of

    x IS NOT DISTINCT FROM y

I'm vaguely imagining

    x = {magic} y

where unlike Eugen's suggestion, "=" is the real name of the underlying
comparison operator.  For dump/restore this could be spelled verbosely
as

    x OPERATOR(someplace.=) {magic} y

The hard part is to figure out some {magic} annotation that is both
short and unambiguous.  We have to cover the IS DISTINCT variant, too.

            regards, tom lane

Hi,

On 2019-10-26 14:23:49 -0400, Tom Lane wrote:
> I wrote:
> > We do have some unresolved issues around how to let dump/restore
> > control the interpretation of IS [NOT] DISTINCT FROM, cf
> > https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472bf29735c8%40gmail.com
> > but I don't think this idea is helping with that at all.
> 
> BTW, taking a step back and viewing this suggestion as "it'd be nice
> to have *some* shorter notation than IS [NOT] DISTINCT FROM", maybe
> there's a way to unify that desire with the dump/restore fix.  What
> we'd really need to fix the dump/restore problem, AFAICS, is to name
> the underlying equality operator --- potentially with a schema
> qualification --- but then have some notation that says "handle NULLs
> like IS [NOT] DISTINCT FROM does".  So instead of
> 
>     x IS NOT DISTINCT FROM y
> 
> I'm vaguely imagining
> 
>     x = {magic} y
> 
> where unlike Eugen's suggestion, "=" is the real name of the underlying
> comparison operator.  For dump/restore this could be spelled verbosely
> as
> 
>     x OPERATOR(someplace.=) {magic} y
> 
> The hard part is to figure out some {magic} annotation that is both
> short and unambiguous.  We have to cover the IS DISTINCT variant, too.

Leaving the exact choice of how {magic} would look like, are you
thinking of somehow making it work for every operator, or just for some
subset? It's intriguing to have something generic, but I'm not quite
clear how that'd would work?  It's not clear to me how we'd
automatically infer a sensible meaning for e.g. < etc.

And even if we just restrict it to = (and presumably <> and !=), in
which cases is this magic going to work? Would we tie it to the textual
'=', '<>' operators? btree opclass members?

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2019-10-26 14:23:49 -0400, Tom Lane wrote:
>> ... instead of
>>     x IS NOT DISTINCT FROM y
>> I'm vaguely imagining
>>     x = {magic} y
>> where unlike Eugen's suggestion, "=" is the real name of the underlying
>> comparison operator.  For dump/restore this could be spelled verbosely
>> as
>>     x OPERATOR(someplace.=) {magic} y
>> The hard part is to figure out some {magic} annotation that is both
>> short and unambiguous.  We have to cover the IS DISTINCT variant, too.

To clarify, what I have in mind here doesn't have any effect whatever
on the parse tree or the execution semantics, it's just about offering
an alternative SQL textual representation.

> Leaving the exact choice of how {magic} would look like, are you
> thinking of somehow making it work for every operator, or just for some
> subset? It's intriguing to have something generic, but I'm not quite
> clear how that'd would work?  It's not clear to me how we'd
> automatically infer a sensible meaning for e.g. < etc.

Yeah, I think it could only be made to work sanely for underlying
operators that have the semantics of equality.  The NOT DISTINCT
wrapper has the semantics

    NULL vs NULL        -> true
    NULL vs not-NULL    -> false
    not-NULL vs NULL    -> false
    not-NULL vs not-NULL    -> apply operator

and while theoretically the operator needn't be equality, those
NULL behaviors don't make much sense otherwise.  (IS DISTINCT
just inverts all the results, of course.)

I suppose that we could also imagine generalizing DistinctExpr
into something that could work with other operator semantics,
but as you say, it's a bit hard to wrap ones head around what
that would look like.

> And even if we just restrict it to = (and presumably <> and !=), in
> which cases is this magic going to work? Would we tie it to the textual
> '=', '<>' operators? btree opclass members?

See the other thread I cited --- right now, the underlying operator is
always "=" and it's looked up by name.  Whether that ought to change
seems like a separate can o' worms.

            regards, tom lane

On 26/10/2019 17:41, Eugen Konkov wrote:
> Hi.
>
> I  have  noticed that it would be cool to use '==' in place of 'IS NOT
> DISTICT FROM'
>
> What do you think about this crazy idea?


I think this is a terrible idea.  The only reason to do this would be to
index it, but indexes (btree at least) expect STRICT operators, which
this would not be.

Hi,

On October 26, 2019 4:09:29 PM PDT, Vik Fearing <vik.fearing@2ndquadrant.com> wrote:
>On 26/10/2019 17:41, Eugen Konkov wrote:
>> Hi.
>>
>> I  have  noticed that it would be cool to use '==' in place of 'IS
>NOT
>> DISTICT FROM'
>>
>> What do you think about this crazy idea?
>
>
>I think this is a terrible idea.  The only reason to do this would be
>to
>index it, but indexes (btree at least) expect STRICT operators, which
>this would not be.

It sounds like what's being suggested is just some abbreviated formulation of IS NOT DISTINCT. If implement that way,
ratherthan manually adding non strict operators, I don't think there would be an indexing issue. 

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

I wrote:
> To clarify, what I have in mind here doesn't have any effect whatever
> on the parse tree or the execution semantics, it's just about offering
> an alternative SQL textual representation.

Continuing this thread ... if we were just trying to fix the
dump/restore issue without regard for verbosity, I think I'd propose
that we implement syntaxes like

    x IS DISTINCT FROM y
    x IS DISTINCT (=) FROM y
    x IS DISTINCT (schema.=) FROM y
    x IS NOT DISTINCT FROM y
    x IS NOT DISTINCT (=) FROM y
    x IS NOT DISTINCT (schema.=) FROM y

with the understanding that the parenthesized operator name is what
to use for the underlying equality comparison, and that in the absence
of any name, the parser looks up "=" (which is what it does today).
Thus the first two alternatives are precisely equivalent, as are the
fourth and fifth.  Also, to support row-wise comparisons, we could
allow cases like

    ROW(a,b) IS NOT DISTINCT (schema1.=, schema2.=) FROM ROW(x,y)

ruleutils.c could proceed by looking up the operator(s) normally,
and skipping the verbose syntax when they would print as just "=",
so that we don't need to emit nonstandard SQL for common cases.

I haven't actually checked to ensure that Bison can handle this,
but since DISTINCT and FROM are both fully reserved words, it seems
virtually certain that this would work without syntactic ambiguity.
It also seems relatively understandable as to what it means.

But of course, this is the exact opposite of addressing Eugen's
concern about verbosity :-(.  Can we pack the same functionality
into fewer characters?

            regards, tom lane

>         x IS NOT DISTINCT FROM y

> I'm vaguely imagining

>         x = {magic} y

> where unlike Eugen's suggestion, "=" is the real name of the underlying
> comparison operator.  For dump/restore this could be spelled verbosely
> as

>         x OPERATOR(someplace.=) {magic} y

> The hard part is to figure out some {magic} annotation that is both
> short and unambiguous.  We have to cover the IS DISTINCT variant, too.

I  am  from  Perl  world.  There  are  == and != operators.
Here short snippet of code:

my $x = undef;
my $y = 'some value';
my $z = undef;
$x == $y; # FALSE
$x == $z; # TRUE
$x != $y ; # TRUE
$x != $z;  # FALSE


>         x OPERATOR(someplace.=) {magic} y
If we should follow this form, then IS DISTINCT should be written as:
x =! y
This  looks unusual, because JavaScript also follow != form. so I hope
it  will be easy to detect/implement != form, which I used to read as:
negate the result of comparison



Can   we   supply   additional   parameters  to  OPERATOR  via  double
parentheses( double parentheses is another crazy idea)?
x =(( 'NULL' )) y

or

x OPERATOR(someplace.=, magic ) y
which   will  be  internally  converted(  I  suppose  )  to  OPERATOR(
someplace.=, x, y, magic )

-- 
Best regards,
Eugen Konkov

On Mon, Oct 28, 2019 at 7:54 AM Isaac Morland <isaac.morland@gmail.com> wrote:
> Postgres already allows != as a synonym for <>. I think having =! mean something subtly but significantly different
isa terrible idea. At a minimum we would have to remove the synonym, which would be a backwards compatibility break.
 

I certainly agree with that. I do think, though, that IS DISTINCT FROM
is a terribly verbose thing to have to write all the time.  It's not
that bad when you write a query that contains one instance of it, but
I've both seen and written queries where you need to use it a bunch of
times, and that can get really annoying. So I don't think adding an
operator that means the same thing is a bad idea.  I don't think ==
and !== would be crazy, for instance; Tom's statement that someone
might already be using == in an extension doesn't persuade me, because
(1) even if it's true it's likely to inconvenience only a very small
percentage of users and (2) the same argument can be applied to any
operator name and is more likely to apply to operator names that don't
look like line noise, and I refuse to accept the idea that we should
commit either to never adding new operators ever again, or the
competing idea that any we do add should look like line noise.

AFAICS, Tom's got the right idea about how to fix the pg_dump
schema-qualification issue, and the idea of creating an operator
notation is a separate and possibly harder problem. Whatever we need
to add to the IS [NOT] DISTINCT FROM syntax for pg_dump can just be
hard-coded, but I guess if we want new operators we'd have to run
around and update all of our built-in data types and extensions, after
the (not so easy) preliminary step of reaching agreement on how it
should all work.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 10/28/19 8:37 AM, Robert Haas wrote:
> On Mon, Oct 28, 2019 at 7:54 AM Isaac Morland <isaac.morland@gmail.com> wrote:
>> Postgres already allows != as a synonym for <>. I think having =! mean something subtly but significantly different
isa terrible idea. At a minimum we would have to remove the synonym, which would be a backwards compatibility break.
 
> I certainly agree with that. I do think, though, that IS DISTINCT FROM
> is a terribly verbose thing to have to write all the time.  It's not
> that bad when you write a query that contains one instance of it, but
> I've both seen and written queries where you need to use it a bunch of
> times, and that can get really annoying. 



How about instead of new operators we just provide a nice shorthand way
of saying these? e.g. ARE and AINT :-)


cheers


andrew

-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, 28 Oct 2019 at 13:31, Andrew Dunstan
<andrew.dunstan@2ndquadrant.com> wrote:
> How about instead of new operators we just provide a nice shorthand way
> of saying these? e.g. ARE and AINT :-)

Seems to me like this is something that those users who want it can
implement for themselves with little to no effort without forcing the
change on everyone else.

CREATE OR REPLACE FUNCTION fnnotdistinctfrom(anyelement, anyelement)
RETURNS boolean LANGUAGE SQL AS $_$
  SELECT CASE WHEN $1 IS NOT DISTINCT FROM $2 THEN true ELSE false END;
$_$;
CREATE OR REPLACE FUNCTION fndistinctfrom(anyelement, anyelement)
RETURNS boolean LANGUAGE SQL AS $_$
  SELECT CASE WHEN $1 IS DISTINCT FROM $2 THEN true ELSE false END;
$_$;
CREATE OPERATOR == (
  PROCEDURE = fnnotdistinctfrom,
  LEFTARG=anyelement,
  RIGHTARG=anyelement,
  NEGATOR = =!
);
CREATE OPERATOR =! (
  PROCEDURE = fndistinctfrom,
  LEFTARG = anyelement,
  RIGHTARG = anyelement,
  NEGATOR = ==
);

I'm at a loss to understand why anyone would want to implement what is
basically a personal preference for syntactic sugar at the system
level. There's not even the advantage of other-system-compatibility.

Geoff

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> On 10/28/19 8:37 AM, Robert Haas wrote:
>> I certainly agree with that. I do think, though, that IS DISTINCT FROM
>> is a terribly verbose thing to have to write all the time.  It's not
>> that bad when you write a query that contains one instance of it, but
>> I've both seen and written queries where you need to use it a bunch of
>> times, and that can get really annoying. 

> How about instead of new operators we just provide a nice shorthand way
> of saying these? e.g. ARE and AINT :-)

The thing about providing a shorthand that looks like an operator is
that then people will try to use it as an operator, and we'll be having
to explain why constructs like "ORDER BY ==" or "x == ANY (SELECT ...)"
don't work.  Or else make them work, but I think you'll find that
that moves this task well outside the easy-finger-exercise category.

I kind of like AINT ;-) ... although adding two new short,
fully-reserved words is likely to cause push-back from people
whose schemas get broken by that.

A more practical answer might be to allow these to be abbreviated
along the lines of

    x DIST y
    x NOT DIST y

if we're willing to make DIST a fully reserved word.
It's possible that we could make

    x IS DIST y
    x IS NOT DIST y

work without fully reserving DIST, but I've not tried it.

Of course neither of those ideas is as short as "==", but
I think we should put some weight on not breaking things.
I do not believe Robert's position that nobody will complain
if we break extensions' use of "==" just to save some typing.

            regards, tom lane

On Mon, Oct 28, 2019 at 10:07 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I kind of like AINT ;-) ... although adding two new short,
> fully-reserved words is likely to cause push-back from people
> whose schemas get broken by that.
>
> A more practical answer might be to allow these to be abbreviated
> along the lines of
>
>         x DIST y
>         x NOT DIST y
>
> if we're willing to make DIST a fully reserved word.
> It's possible that we could make
>
>         x IS DIST y
>         x IS NOT DIST y
>
> work without fully reserving DIST, but I've not tried it.

I don't like either of these proposals much. I think DIST is not very
clear: I think a variety of things other than DISTINCT might come to
mind (distribution?) and we have no precedent for chopping off the
tail end of an English word just to save keystrokes. And I think
adding fully-reserved keywords would do far more damage than we can
justify on account of this annoyance.

> Of course neither of those ideas is as short as "==", but
> I think we should put some weight on not breaking things.
> I do not believe Robert's position that nobody will complain
> if we break extensions' use of "==" just to save some typing.

I mean, do we have to break the extensions? If we just added ==
operators that behaved like IS NOT DISTINCT FROM to each datatype, why
would anything get broken? I mean, if someone out there has a
==(int4,int4) operator, that would get broken, but what's the evidence
that any such thing exists, or that its semantics are any different
from what we're talking about?

If we added == as a magic parser shortcut for IS NOT DISTINCT FROM,
that would be more likely to break things, because it would affect
every conceivable data type. I don't think that's a great idea, but
I'd also be curious to see what evidence you have that there are
enough extensions out there of sufficient popularity that this would
be a big problem. For instance, if PostGIS uses this operator name,
that'd be good evidence that it's a real problem, but if the only
examples we can find are things that are relatively obscure, then, at
least to me, that would be different.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Oct 28, 2019 at 10:07 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Of course neither of those ideas is as short as "==", but
>> I think we should put some weight on not breaking things.
>> I do not believe Robert's position that nobody will complain
>> if we break extensions' use of "==" just to save some typing.

> I mean, do we have to break the extensions? If we just added ==
> operators that behaved like IS NOT DISTINCT FROM to each datatype, why
> would anything get broken?

Is that the proposal?  I certainly assumed that Eugen had in mind a
parser-level hack, because adding dozens of new operators and their
underlying functions would be a Lot Of Tedious Work.  But I agree
that if we did it like that, it (probably) wouldn't break anything.

I'd be somewhat inclined to adopt "===" and "!===" as the standard
names, trading off one more keystroke to get to a point where we
almost certainly aren't conflicting with anybody's existing usage.

One objection to proceeding like that is that there'd be no
visible connection between a datatype's "=" and "===" operators,
removing any hope of someday optimizing, for example, "x IS NOT
DISTINCT FROM 42" into an indexscan on x.  We're certainly not
very bright about these constructs today, but at least there
exists the possibility of doing better in future.  I suppose
we could think about extending btree opclasses to allow for
an === entry, but that'd be another pile of work ...

            regards, tom lane

On Mon, Oct 28, 2019 at 11:20 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I mean, do we have to break the extensions? If we just added ==
> > operators that behaved like IS NOT DISTINCT FROM to each datatype, why
> > would anything get broken?
>
> Is that the proposal?  I certainly assumed that Eugen had in mind a
> parser-level hack, because adding dozens of new operators and their
> underlying functions would be a Lot Of Tedious Work.  But I agree
> that if we did it like that, it (probably) wouldn't break anything.

I'm not sure we've yet converged on a single proposal yet.  This seems
to be at the spitballing stage.

> I'd be somewhat inclined to adopt "===" and "!===" as the standard
> names, trading off one more keystroke to get to a point where we
> almost certainly aren't conflicting with anybody's existing usage.

Maybe.  It's an open question in my mind which of those is more likely
to be taken already.  Javascript uses === and !== for a certain kind
of equality comparison, so I'd guess that the chance of someone having
used === is better-than-average for that reason.  Also, if we decide
that the opposite of === is !=== rather than !==, someone may hate us.

> One objection to proceeding like that is that there'd be no
> visible connection between a datatype's "=" and "===" operators,
> removing any hope of someday optimizing, for example, "x IS NOT
> DISTINCT FROM 42" into an indexscan on x.  We're certainly not
> very bright about these constructs today, but at least there
> exists the possibility of doing better in future.  I suppose
> we could think about extending btree opclasses to allow for
> an === entry, but that'd be another pile of work ...

Yeah.  If we went this route, I think we'd probably have to do that
extension of the btree operator class machinery first. Virtually
nobody is gonna want a new spelling of IS NOT DISTINCT FROM that is
shorter but performs terribly.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

On 2019-10-28 10:41:31 -0400, Robert Haas wrote:
> I mean, do we have to break the extensions? If we just added ==
> operators that behaved like IS NOT DISTINCT FROM to each datatype, why
> would anything get broken? I mean, if someone out there has a
> ==(int4,int4) operator, that would get broken, but what's the evidence
> that any such thing exists, or that its semantics are any different
> from what we're talking about?
> 
> If we added == as a magic parser shortcut for IS NOT DISTINCT FROM,
> that would be more likely to break things, because it would affect
> every conceivable data type. I don't think that's a great idea, but

Without some magic, the amount of repetitive changes, the likelihood of
inconsistencies, and the reduced information about semantic meaning to
the planner (it'd not be a btree op anymore!), all seem to argue against
adding such an operator.

Greetings,

Andres Freund

Diggory Blake <diggsey@googlemail.com> writes:
> Would it be possible to just use `IS`, `IS NOT` instead of `IS [NOT]
> DISTINCT FROM`? It's always surprised me that you can write `IS NULL`, `IS
> TRUE`, etc. but they're all special-cased. I could see it introducing a
> parsing ambiguity, but it doesn't seem impossible to resolve?

Cute idea, but I'm afraid it breaks down when you come to
"x IS DOCUMENT".  We'd have to make DOCUMENT fully reserved
(or at least more reserved --- maybe type_func_name_keyword
would be enough?) or it'd be unclear whether that meant a
not-distinct comparison to a column named "document".
And I'd bet a lot that there are people out there with
columns named "document", so even type_func_name_keyword
reserved-ness would be enough to break their applications.

In the bigger picture, even if we were okay with that, I'm
afraid that we'd constantly be in danger of the SQL committee
adding some new "x IS KEYWORD(s)" test, causing new problems.

            regards, tom lane