Thread: Strange behavior of "=" as assignment operator

* Moshe Jacobson (moshe@neadwerx.com) wrote:
> It seems that the comparison operator "=" is functioning as the assignment
> operator ":=" in this plpgsql trigger script I wrote. I was under the
> impression that "=" is only for comparison and not assignment. If this is
> true, please explain the transcript below. If it's not true, where is this
> documented? I'm using PostgreSQL 9.1.4.

Both are supported.  It's not really documented as using '=' is
considered 'legacy' but it's also extensively used and removing it would
break quite a bit of code for people.

My general feeling is that we should either document it *as* legacy
or add it to the "list-of-things-to-break-in-10.0". :)

    Thanks,

        Stephen

2013/5/28 Stephen Frost <sfrost@snowman.net>:
> * Moshe Jacobson (moshe@neadwerx.com) wrote:
>> It seems that the comparison operator "=" is functioning as the assignment
>> operator ":=" in this plpgsql trigger script I wrote. I was under the
>> impression that "=" is only for comparison and not assignment. If this is
>> true, please explain the transcript below. If it's not true, where is this
>> documented? I'm using PostgreSQL 9.1.4.
>
> Both are supported.  It's not really documented as using '=' is
> considered 'legacy' but it's also extensively used and removing it would
> break quite a bit of code for people.
>
> My general feeling is that we should either document it *as* legacy
> or add it to the "list-of-things-to-break-in-10.0". :)

should be cleaned and removed

same undocumented behave has GET DIAGNOSTICS STATEMENT, ":=" is
allowed there :-(

note from source code

assign_operator>: '='<->/* not documented because it might be removed someday */
<------><------><------><------>| COLON_EQUALS
<------><------><------><------>;

Regards

Pavel



>
>         Thanks,
>
>                 Stephen

* Moshe Jacobson (moshe@neadwerx.com) wrote:
> Any PG committers who can change this in 9.3?

It will certainly not be changed for 9.3.

As suggested, perhaps in 10.0, but I tend to doubt it.  It will
certainly be mentioned in the release notes when it happens.

    Thanks,

        Stephen

On 05/28/2013 01:06 PM, Stephen Frost wrote:
> * Moshe Jacobson (moshe@neadwerx.com) wrote:
>> It seems that the comparison operator "=" is functioning as the assignment
>> operator ":=" in this plpgsql trigger script I wrote. I was under the
>> impression that "=" is only for comparison and not assignment. If this is
>> true, please explain the transcript below. If it's not true, where is this
>> documented? I'm using PostgreSQL 9.1.4.
> Both are supported.  It's not really documented as using '=' is
> considered 'legacy' but it's also extensively used and removing it would
> break quite a bit of code for people.
>
>

You mean legacy code as provided in the documentation examples?
http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html (see
examples 39-5 and 39-6).

Fixing the example code is probably a low-hanging-fruit place to start.
I don't have time right now or I'd submit a patch.

Cheers,
Steve

Hello

2013/5/28 Moshe Jacobson <moshe@neadwerx.com>:
> On Tue, May 28, 2013 at 4:06 PM, Stephen Frost <sfrost@snowman.net> wrote:
>>
>> Both are supported.  It's not really documented as using '=' is
>> considered 'legacy' but it's also extensively used and removing it would
>> break quite a bit of code for people.
>
>
> This is crazy! By leaving it in, they are allowing my obsolescent code to
> compile and run without warning!
> This means that one day down the road, MY code is going to break because the
> developers assumed that this obsolescent behavior was OK to turn on by
> default.
> IMO they should have disabled the default acceptance of "=" and given us an
> option to enable it if our code broke.
> Not leaving it in by default.
>

There are lot of "undocumented features", that you can use in almost
all software (MS SQL, Oracle) on own risk.

> Any PG committers who can change this in 9.3?

I hope, so we will be able to identify and raise warning in 9.4.

Regards

Pavel

>
>
> --
> Moshe Jacobson
> Nead Werx, Inc. | Manager of Systems Engineering
> 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> moshe@neadwerx.com | www.neadwerx.com
>
> "Quality is not an act, it is a habit." -- Aristotle

2013/5/28 Steve Crawford <scrawford@pinpointresearch.com>:
> On 05/28/2013 01:06 PM, Stephen Frost wrote:
>>
>> * Moshe Jacobson (moshe@neadwerx.com) wrote:
>>>
>>> It seems that the comparison operator "=" is functioning as the
>>> assignment
>>> operator ":=" in this plpgsql trigger script I wrote. I was under the
>>> impression that "=" is only for comparison and not assignment. If this is
>>> true, please explain the transcript below. If it's not true, where is
>>> this
>>> documented? I'm using PostgreSQL 9.1.4.
>>
>> Both are supported.  It's not really documented as using '=' is
>> considered 'legacy' but it's also extensively used and removing it would
>> break quite a bit of code for people.
>>
>>
>
> You mean legacy code as provided in the documentation examples?
> http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html (see examples
> 39-5 and 39-6).
>
> Fixing the example code is probably a low-hanging-fruit place to start. I
> don't have time right now or I'd submit a patch.

this is bug and should be fixed. I''ll send a patch.

Pavel

>
> Cheers,
> Steve
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Stephen Frost <sfrost@snowman.net> writes:
> * Moshe Jacobson (moshe@neadwerx.com) wrote:
>> Any PG committers who can change this in 9.3?

> It will certainly not be changed for 9.3.

IMO, if we do anything about this at all, it should be to document the
"=" option not remove it.  If we change it, the squawks from people who
were (perhaps unintentionally) depending on the current behavior will
outnumber the plaudits from people who think that such a change is a
good idea by several orders of magnitude.

            regards, tom lane

2013/6/1 Tom Lane <tgl@sss.pgh.pa.us>:
> Stephen Frost <sfrost@snowman.net> writes:
>> * Moshe Jacobson (moshe@neadwerx.com) wrote:
>>> Any PG committers who can change this in 9.3?
>
>> It will certainly not be changed for 9.3.
>
> IMO, if we do anything about this at all, it should be to document the
> "=" option not remove it.  If we change it, the squawks from people who
> were (perhaps unintentionally) depending on the current behavior will
> outnumber the plaudits from people who think that such a change is a
> good idea by several orders of magnitude.

Moving from undocumented feature to documented feature is solution.
But I don't like it.

This is not only one use case. Then we should to document possibility
GET DIAGNOSTICS var := ident and some others. And this can be strong
problem for people who start on Postgres and would to use Oracle.

I don't propose remove this undocumented feature in next few cycles.
But if we will have integrated plpgsql_check_function, then we can
raise a warning 3 cycles, and then we can drop it. For almost all
cases we can provide simple migration tool.

similar issue is ELSIF and ELSEIF (although it is documented).

Regards

Pavel

>
>                         regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Tom Lane-2 wrote
> Stephen Frost <

> sfrost@

> > writes:
>> * Moshe Jacobson (

> moshe@

> ) wrote:
>>> Any PG committers who can change this in 9.3?
>
>> It will certainly not be changed for 9.3.
>
> IMO, if we do anything about this at all, it should be to document the
> "=" option not remove it.  If we change it, the squawks from people who
> were (perhaps unintentionally) depending on the current behavior will
> outnumber the plaudits from people who think that such a change is a
> good idea by several orders of magnitude.
>
>             regards, tom lane

*Nutshell*

In short; I generally do not like undocumented behavior - especially for
something that an inexperienced user is going to encounter without even
thinking about it.  "=" for assignment is so common that you need to decide
to either allow it or not; many people will use it just to see if it works
and then, if it does not, will RTFM and learn that they should instead be
using ":=".  GET DIAGNOSTICS is simply a special-form of the basic
assignment and thus should have the same rules and documentation.

The fact that we talking about documenting this as opposed to making it work
reinforces this choice.  Time will only make the problem worse.  Either
there is a strong enough argument NOW to do away with this dual-usage of "="
and we define the plan of action starting with warnings in 9.4 OR we
document it for 9.3 (and, really, back to all supported versions).  Crap or
get off of the toilet.

*Exposition*

I have not gone and looked but I know there is some form of the "check
function" routine floating around here and that routine could have a "strict
assignment" option for those who want that safety check.

That said, in the OP's example the code intended to use assignment and
correctly did so.  I am having trouble finding any example, let alone a
compelling one, where allowing "=" to have both assignment and equality
meanings would lead to silent bugs.  This may be a lack of imagination on my
part.  The fact that assignment cannot occur in an expression while equality
must is the reason for the difficulty.  It is when assignment can occur in
an expression (and thus, like equality, has a return value) that ambiguity
(and thus bugs) arises.

Pavel brings up the point of cross-language compatibility and learning and
in general I would agree but, and again because of the exclusive syntax
zones for assignment and equality in the "PL/PGSQL" language said
compatibility can be something achieved at a higher level - plus I would
think having more options would work in favor.  If the issue is people
moving their PostgreSQL code to Oracle then I am not sure whether I'd change
the language to make that work more easily - I'd much rather keep everyone
who has been using the "=" for assignment happy and let higher level
compatibility tools handle the conversion.

I agree with documenting the "GET DIAGNOSTICS var := item;" syntax and in
fact from a purely OCD standpoint wonder why non-diagnostic assignment is
documented for ":=" while diagnostic assignment is documented for "=".  The
"GET DIAGNOSTICS" part is a modifier for the statement but doesn't magically
turn the construct into an expression.

David J.

P.S.  Undocumented behavior should be considered "not yet documented"
behavior.  It isn't worth documenting everything but if an issue or
confusion arises then at least document "we allow - for the following
reasons - this behavior but recommend you do not rely on it - for the
following reasons".  The visibility of said documentation should be directly
proportional to the level of experience of the user that will encounter said
behavior.







--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Strange-behavior-of-as-assignment-operator-tp5757205p5757631.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

2013/6/1 David Johnston <polobo@yahoo.com>:
> Tom Lane-2 wrote
>> Stephen Frost <
>
>> sfrost@
>
>> > writes:
>>> * Moshe Jacobson (
>
>> moshe@
>
>> ) wrote:
>>>> Any PG committers who can change this in 9.3?
>>
>>> It will certainly not be changed for 9.3.
>>
>> IMO, if we do anything about this at all, it should be to document the
>> "=" option not remove it.  If we change it, the squawks from people who
>> were (perhaps unintentionally) depending on the current behavior will
>> outnumber the plaudits from people who think that such a change is a
>> good idea by several orders of magnitude.
>>
>>                       regards, tom lane
>
> *Nutshell*
>
> In short; I generally do not like undocumented behavior - especially for
> something that an inexperienced user is going to encounter without even
> thinking about it.  "=" for assignment is so common that you need to decide
> to either allow it or not; many people will use it just to see if it works
> and then, if it does not, will RTFM and learn that they should instead be
> using ":=".  GET DIAGNOSTICS is simply a special-form of the basic
> assignment and thus should have the same rules and documentation.
>

yes, I agree - this should be documented and there should be warning
about a possible problems in future.

> The fact that we talking about documenting this as opposed to making it work
> reinforces this choice.  Time will only make the problem worse.  Either
> there is a strong enough argument NOW to do away with this dual-usage of "="
> and we define the plan of action starting with warnings in 9.4 OR we
> document it for 9.3 (and, really, back to all supported versions).  Crap or
> get off of the toilet.
>
> *Exposition*
>
> I have not gone and looked but I know there is some form of the "check
> function" routine floating around here and that routine could have a "strict
> assignment" option for those who want that safety check.
>
> That said, in the OP's example the code intended to use assignment and
> correctly did so.  I am having trouble finding any example, let alone a
> compelling one, where allowing "=" to have both assignment and equality
> meanings would lead to silent bugs.  This may be a lack of imagination on my
> part.  The fact that assignment cannot occur in an expression while equality
> must is the reason for the difficulty.  It is when assignment can occur in
> an expression (and thus, like equality, has a return value) that ambiguity
> (and thus bugs) arises.
>
> Pavel brings up the point of cross-language compatibility and learning and
> in general I would agree but, and again because of the exclusive syntax
> zones for assignment and equality in the "PL/PGSQL" language said
> compatibility can be something achieved at a higher level - plus I would
> think having more options would work in favor.  If the issue is people
> moving their PostgreSQL code to Oracle then I am not sure whether I'd change
> the language to make that work more easily - I'd much rather keep everyone
> who has been using the "=" for assignment happy and let higher level
> compatibility tools handle the conversion.
>
> I agree with documenting the "GET DIAGNOSTICS var := item;" syntax and in
> fact from a purely OCD standpoint wonder why non-diagnostic assignment is
> documented for ":=" while diagnostic assignment is documented for "=".  The
> "GET DIAGNOSTICS" part is a modifier for the statement but doesn't magically
> turn the construct into an expression.

just note to GET DIAGNOSTICS statement

It is ANSI/SQL statement and there is exact definition GET DIAGNOSTICS
var = item, ...

So support anything else is creating own SQL extension

>
> David J.
>
> P.S.  Undocumented behavior should be considered "not yet documented"
> behavior.  It isn't worth documenting everything but if an issue or
> confusion arises then at least document "we allow - for the following
> reasons - this behavior but recommend you do not rely on it - for the
> following reasons".  The visibility of said documentation should be directly
> proportional to the level of experience of the user that will encounter said
> behavior.
>
>
>
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/Strange-behavior-of-as-assignment-operator-tp5757205p5757631.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

2013/6/1 Chris Travers <chris.travers@gmail.com>:
> Agreed about undocumented behavior (actually there is a *lot* of
> undocumented behavior in PostgreSQL as I have slowly found out-- if you want
> to see a lot of it, go look at the pg_dump source code).
>
> However = as assignment is particularly odd to me for two reasons.  First it
> is not ambiguous but it leads to difficult to read constructs, like this:
>
> CREATE OR REPLACE FUNCTION cmp(in_left text, in_right text) returns bool
> language plpgsql as
> $$
> DECLARE out_var bool;
> BEGIN
>    out_var = in_left = in_right;
>    return out_var;
> END;
> $$;
>

good example


>
> Secondly it is way too easy for a beginner to accidently use = as an
> assignment operator.  This can lead to odd, difficult to understand, bugs.
> If it is going to be legacy behavior at least we should consider raising a
> warning.
>
> Best Wishes,
> Chris Travers
>
>
> On Fri, May 31, 2013 at 11:36 PM, David Johnston <polobo@yahoo.com> wrote:
>>
>> Tom Lane-2 wrote
>> > Stephen Frost <
>>
>> > sfrost@
>>
>> > > writes:
>> >> * Moshe Jacobson (
>>
>> > moshe@
>>
>> > ) wrote:
>> >>> Any PG committers who can change this in 9.3?
>> >
>> >> It will certainly not be changed for 9.3.
>> >
>> > IMO, if we do anything about this at all, it should be to document the
>> > "=" option not remove it.  If we change it, the squawks from people who
>> > were (perhaps unintentionally) depending on the current behavior will
>> > outnumber the plaudits from people who think that such a change is a
>> > good idea by several orders of magnitude.
>> >
>> >                       regards, tom lane
>>
>> *Nutshell*
>>
>> In short; I generally do not like undocumented behavior - especially for
>> something that an inexperienced user is going to encounter without even
>> thinking about it.  "=" for assignment is so common that you need to
>> decide
>> to either allow it or not; many people will use it just to see if it works
>> and then, if it does not, will RTFM and learn that they should instead be
>> using ":=".  GET DIAGNOSTICS is simply a special-form of the basic
>> assignment and thus should have the same rules and documentation.
>>
>> The fact that we talking about documenting this as opposed to making it
>> work
>> reinforces this choice.  Time will only make the problem worse.  Either
>> there is a strong enough argument NOW to do away with this dual-usage of
>> "="
>> and we define the plan of action starting with warnings in 9.4 OR we
>> document it for 9.3 (and, really, back to all supported versions).  Crap
>> or
>> get off of the toilet.
>>
>> *Exposition*
>>
>> I have not gone and looked but I know there is some form of the "check
>> function" routine floating around here and that routine could have a
>> "strict
>> assignment" option for those who want that safety check.
>>
>> That said, in the OP's example the code intended to use assignment and
>> correctly did so.  I am having trouble finding any example, let alone a
>> compelling one, where allowing "=" to have both assignment and equality
>> meanings would lead to silent bugs.  This may be a lack of imagination on
>> my
>> part.  The fact that assignment cannot occur in an expression while
>> equality
>> must is the reason for the difficulty.  It is when assignment can occur in
>> an expression (and thus, like equality, has a return value) that ambiguity
>> (and thus bugs) arises.
>>
>> Pavel brings up the point of cross-language compatibility and learning and
>> in general I would agree but, and again because of the exclusive syntax
>> zones for assignment and equality in the "PL/PGSQL" language said
>> compatibility can be something achieved at a higher level - plus I would
>> think having more options would work in favor.  If the issue is people
>> moving their PostgreSQL code to Oracle then I am not sure whether I'd
>> change
>> the language to make that work more easily - I'd much rather keep everyone
>> who has been using the "=" for assignment happy and let higher level
>> compatibility tools handle the conversion.
>>
>> I agree with documenting the "GET DIAGNOSTICS var := item;" syntax and in
>> fact from a purely OCD standpoint wonder why non-diagnostic assignment is
>> documented for ":=" while diagnostic assignment is documented for "=".
>> The
>> "GET DIAGNOSTICS" part is a modifier for the statement but doesn't
>> magically
>> turn the construct into an expression.
>>
>> David J.
>>
>> P.S.  Undocumented behavior should be considered "not yet documented"
>> behavior.  It isn't worth documenting everything but if an issue or
>> confusion arises then at least document "we allow - for the following
>> reasons - this behavior but recommend you do not rely on it - for the
>> following reasons".  The visibility of said documentation should be
>> directly
>> proportional to the level of experience of the user that will encounter
>> said
>> behavior.
>>
>>
>>
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/Strange-behavior-of-as-assignment-operator-tp5757205p5757631.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>

Chris Travers-5 wrote
> However = as assignment is particularly odd to me for two reasons.  First
> it is not ambiguous but it leads to difficult to read constructs, like
> this:
>
>    out_var = in_left = in_right;

Agreed but the genie is already out of the bottle and I am OK with something
at the "lint/check_function" level trying to enforce good coding habits as
opposed to changing the language to prohibit what has now been allowed for
many years.

Also, "out_var := in_left = in_right;" is not THAT much more readable than
the former and in either case seeing the variable "out_var" begin the
statement is likely a much bigger indicator that "out_var is being assigned
to" than is the presence of the "=".  Since we do not allow multiple
assignment everything to the right of the variable has to be an expression
(i.e. we are NOT assigning the value of in_right to in_left and out_var).


> Secondly it is way too easy for a beginner to accidentally use = as an
> assignment operator.  This can lead to odd, difficult to understand, bugs.
>  If it is going to be legacy behavior at least we should consider raising
> a
> warning.

Should read:

"... accidentally use = as assignment operator - when they intended to use
it as an equality operator."

While I admit this is possible, using a statement when you meant to use an
expression, it seems to me it would be an unusual occurrence mainly because
if I need an equality expression the first thing I am going to type will be
the "IF/DO/CASE" context syntax in which the expression is evaluated.  I
type "var = ..." if I am thinking assignment and I type "IF var = ..." if I
am thinking equality.

If they are using it for assignment intentionally then this is not a
problem.

If they want to perform an assignment within an expression such would not
work because the assignment would look like an equality comparison but doing
this is likely not something an SQL programmer would attempt generally and
is IMO not something where prevention is worth the cost of breaking forward
compatibility.

A comment was made that "GET DIAGNOSTICS var = item;" is standard defined.
Is the use of ":=" for assignment also standard defined?  If so its not that
inconsistent standards surprise me but...anyway.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Strange-behavior-of-as-assignment-operator-tp5757205p5757645.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

>
> A comment was made that "GET DIAGNOSTICS var = item;" is standard defined.
> Is the use of ":=" for assignment also standard defined?  If so its not that
> inconsistent standards surprise me but...anyway.

":=" coming from different world (ALGOL like languages) and is never
used in SQL. Oracle did merge these two worlds together, but we don't
need bigger chaos than is now.

>
> David J.
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/Strange-behavior-of-as-assignment-operator-tp5757205p5757645.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Chris Travers-5 wrote
> My  preference would be that at some point we start adding warnings when =
> is used as an assignment.  Such warnings could be turned off.  Then at
> some
> later point we can decide whether to change the behavior.  A decision to
> changing the language would be different if such behavior had given
> warnings for several years prior.

We can play the "let's pretend we have already been warning people for the
past 5 years about this: would we, today, turn off the behavior without a
recourse to turn it back on?" game.  We need to make a decision with the
information we have right now - not at "some later point".  The decision is
independent of whether we have been warning people or not - the warning is
simply part of the "how to go about affecting the decision that has already
been made".

A bogus warning is nearly as bad as simply disallowing the syntax in the
first place and I do not like turning one on unless there is the decision to
disallow the syntax in the future.

My preference would be to document the currently allowable usage of both
":=" and "=" (including within GET DIAGNOSTICS).  Address in the
documentation compatibility concerns.  And add configurations to the "check
function" implementation that allow the programmer to decide which usages
are allowable and which are not.  The default would be to allow either
symbol in either situation - i.e., the current behavior.

The status-quo, from the lack of discussion surrounding this recently,
doesn't seem to be that bad.  My sample and exposure to other's code,
though, is quite minimal and not everything makes it to the lists (or is
read if it does) so maybe there is some underlying significant risk that I
am simply missing.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Strange-behavior-of-as-assignment-operator-tp5757205p5757670.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

David Johnston <polobo@yahoo.com> writes:
> A bogus warning is nearly as bad as simply disallowing the syntax in the
> first place and I do not like turning one on unless there is the decision to
> disallow the syntax in the future.

TBH I do not see this happening.  GET DIAGNOSTICS is just the tip of the
iceberg.  The SQL standard uses "=" for assignment in other contexts,
most notably UPDATE, but also the SQL/PSM standard uses it in
<assignment statement> which is the exact same thing as in pl/pgsql.
So while purists might wish we only accepted :=, doing so would be
inconsistent with SQL.

I think we should just document it and move on.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> The SQL standard uses "=" for assignment in other contexts,
> most notably UPDATE, but also the SQL/PSM standard uses it in
> <assignment statement> which is the exact same thing as in
> pl/pgsql.  So while purists might wish we only accepted :=, doing
> so would be inconsistent with SQL.
>
> I think we should just document it and move on.

+1

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company