Thread: SQL function parse error ?

Radu-Adrian Popescu wrote:

>
> Take the following for example:
> create or replace function testfunc1(int) returns setof test as
> '
>     select * from test where age>$1;
> '                            ^^^^^^
> language sql;

I didn't find it in documentation, but sql functions are like bash (you 
forgot about space character):

create or replace function testfunc1(int) returns setof test as
'    select * from test where age > $1;
'
works fine.
Postgresql thinks that >$ is an operator instead of >

Tomasz Myrta

I'm perfectly aware of the fact that a space solves the issue here.

What I'm saying is that it is not natural nor common to take some whitespace
into
account when parsing, since this is not bash language, nor python, as it
shouldn't be !
This is SQL, and people who are using PostgreSql write SQL, not
whitespace-sensitive SQL, bash or whatever.

And besides, like I have already pointed out, look at php's language parser
(behavior, not source) :
the statement if(100>$a) is perfectly legal, as it should be.
Is there any operator named >$ ?

Anyone who has used anything from Mysql to Oracle will get quite annoyed on
this one.

Hope the people in charge of the parser will get to the bottom of this...
... and please forgive my caustic tone.

=====
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

----- Original Message -----
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Radu-Adrian Popescu" <radu.popescu@aldratech.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, January 09, 2003 1:56 PM
Subject: Re: [SQL] SQL function parse error ?


Radu-Adrian Popescu wrote:

>
> Take the following for example:
> create or replace function testfunc1(int) returns setof test as
> '
>     select * from test where age>$1;
> '                            ^^^^^^
> language sql;

I didn't find it in documentation, but sql functions are like bash (you
forgot about space character):

create or replace function testfunc1(int) returns setof test as
'    select * from test where age > $1;
'
works fine.
Postgresql thinks that >$ is an operator instead of >

Tomasz Myrta


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Radu-Adrian Popescu wrote:

> I'm perfectly aware of the fact that a space solves the issue here.
>
> What I'm saying is that it is not natural nor common to take some 
> whitespace
> into
> account when parsing, since this is not bash language, nor python, as it
> shouldn't be !
> This is SQL, and people who are using PostgreSql write SQL, not
> whitespace-sensitive SQL, bash or whatever.
>
> And besides, like I have already pointed out, look at php's language 
> parser
> (behavior, not source) :
> the statement if(100>$a) is perfectly legal, as it should be.
> Is there any operator named >$ ?
>
> Anyone who has used anything from Mysql to Oracle will get quite 
> annoyed on
> this one.
>
> Hope the people in charge of the parser will get to the bottom of this...
> ... and please forgive my caustic tone.
>
> =====
> Radu-Adrian Popescu
> CSA, DBA, Developer
> Aldratech Ltd.

I think you are absolutely wrong.
It is possible in Postgresql to overload operators and if you want, you 
can create operator named ">$".

There is a lot of useful things in postgresql documentation. If you read 
it, you could find this:
>CREATE OPERATOR defines a new operator, name. The user who defines an >operator becomes its owner.>The operator name
isa sequence of up to NAMEDATALEN-1 (31 by default) >characters from the following list:>+ - * / < > = ~ ! @ # % ^ & |
`? $
 

Regards,
Tomasz Myrta

"Radu-Adrian Popescu" <radu.popescu@aldratech.com> writes:
> This is SQL, and people who are using PostgreSql write SQL, not
> whitespace-sensitive SQL, bash or whatever.

Nonsense.  SQL syntax is space-sensitive.  Or have you successfully
writtenSELECTXFROMY;
lately?

There has occasionally been talk of disallowing '$' as a valid character
in operator names, which would eliminate the syntactic ambiguity in this
example.  But undoubtedly it would also break a few applications that
use '$' in user-defined operator names, so the proposal hasn't passed
to date.

> Is there any operator named >$ ?

Whether there is one in the standard distribution is quite irrelevant.
It's a valid operator name according to the current rules, and so open
to definition by anyone who wants to.

The most recent discussion I can find about this is the pgsql-hackers
thread "Dollar in identifiers" from Aug 2001, eg
http://archives.postgresql.org/pgsql-hackers/2001-08/msg00629.php
There didn't seem to be a consensus to change things, so the old
behavior stands, for the moment.
        regards, tom lane

Thomas, this line of conversation is not quite what one would expect...

I do read the postgresql docs when in run into trouble. And yes, i do know
there is such a thing as
operator overloading.

What i'm saying here, and i belive to be right, is that writing a piece of
code such as   table.columnName>$3
should not yeald an error.

Why is that ? Because the >$ does not exist, not in the default operator
list (also there is no operator defined
using $ anywhere within). And because whitespacing the code solves the
problem, which is rather thin, i must say.

Consider you would create operator >&. How should the parser interpret the
above piece of code ?
Operator or parameter ? If such a distinction is defined on strong grounds,
i have failed to see it stated anywhere
in the docs. Until then, based on experience with SQL code from alot of
dbms, c++ (operators, overloading, etc)
and php (where $name is a variable), i do belive the error reported is bogus
and unjustified.

As i was able to see from 7.3.1 docs, part of the operator documentation is
written by Tom Lane.
Perhaps some insight from the people in charge with SQL functions and/or
operators might help clear this issue.

Cheers,
=====
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

----- Original Message -----
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Radu-Adrian Popescu" <radu.popescu@aldratech.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, January 09, 2003 3:22 PM
Subject: Re: [SQL] SQL function parse error ?


Radu-Adrian Popescu wrote:

> I'm perfectly aware of the fact that a space solves the issue here.
>
> What I'm saying is that it is not natural nor common to take some
> whitespace
> into
> account when parsing, since this is not bash language, nor python, as it
> shouldn't be !
> This is SQL, and people who are using PostgreSql write SQL, not
> whitespace-sensitive SQL, bash or whatever.
>
> And besides, like I have already pointed out, look at php's language
> parser
> (behavior, not source) :
> the statement if(100>$a) is perfectly legal, as it should be.
> Is there any operator named >$ ?
>
> Anyone who has used anything from Mysql to Oracle will get quite
> annoyed on
> this one.
>
> Hope the people in charge of the parser will get to the bottom of this...
> ... and please forgive my caustic tone.
>
> =====
> Radu-Adrian Popescu
> CSA, DBA, Developer
> Aldratech Ltd.

I think you are absolutely wrong.
It is possible in Postgresql to overload operators and if you want, you
can create operator named ">$".

There is a lot of useful things in postgresql documentation. If you read
it, you could find this:
>CREATE OPERATOR defines a new operator, name. The user who defines an>operator becomes its owner.>The operator name is
asequence of up to NAMEDATALEN-1 (31 by default)>characters from the following list:>+ - * / < > = ~ ! @ # % ^ & | ` ?
$

Regards,
Tomasz Myrta


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:

>
>
> Why is that ? Because the >$ does not exist, not in the default operator
> list (also there is no operator defined
> using $ anywhere within). And because whitespacing the code solves the
> problem, which is rather thin, i must say.
>

Radu-Adrian,
i think the parser is built with yacc, (not "from scratch code") so
maybe finding if ">$" is in the specific DB's operators
would require code that whould slower the whole parsing
process (imagine what it means for performance).


==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

Hello, nice to hear from you. I sent my message just before this one
arrived, sorry... :(
About the   >Nonsense.  SQL syntax is space-sensitive.  Or have you successfully   >written   >SELECTXFROMY;
>lately?
I do hope this is a joke. If not, it's an insult.
If it's not even that, then the joke's not on me, as it is quite obvious
that the "selectxfromy" counter-example
is bogus and childish.
Look at the following C code:
intmain(){return-10;}    // no good
int main(){return-10;}    // quite valid
Or the the following SQL code :
SELECT * from test where age>23;
SELECT * from test where age           >                                       23;

Now that will work on any given sql database, both queries. And the C
program will compile with most C compilers.
The fact that >$1 gets interpreted as an operator when there is no operator
>$ is nonsense.
I belive there is an issue here, and it's quite frustrating to see it
dismissed with such childish replies.
I have been working with PostgreSql for two years now, and i like it very
much. The one reason i sent out the first email
was to report something which striked me as odd in the first place (even if
it took under 10 seconds to get it fixed), something
that will make people just starting out with postgresql (like some of my
colleagues here) turn to me and laugh their heads off,
people having years of experience with, say, MSSql.

This is supposed to be constructive, not slaping eachother.
For that reason, i appologize for whatever harsh remarks i've made, and
simply hope to get a straight answer or even better a
conversation.

Regards,

=====
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Radu-Adrian Popescu" <radu.popescu@aldratech.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, January 09, 2003 4:48 PM
Subject: Re: [SQL] SQL function parse error ?


"Radu-Adrian Popescu" <radu.popescu@aldratech.com> writes:
> This is SQL, and people who are using PostgreSql write SQL, not
> whitespace-sensitive SQL, bash or whatever.

Nonsense.  SQL syntax is space-sensitive.  Or have you successfully
written
SELECTXFROMY;
lately?

There has occasionally been talk of disallowing '$' as a valid character
in operator names, which would eliminate the syntactic ambiguity in this
example.  But undoubtedly it would also break a few applications that
use '$' in user-defined operator names, so the proposal hasn't passed
to date.

> Is there any operator named >$ ?

Whether there is one in the standard distribution is quite irrelevant.
It's a valid operator name according to the current rules, and so open
to definition by anyone who wants to.

The most recent discussion I can find about this is the pgsql-hackers
thread "Dollar in identifiers" from Aug 2001, eg
http://archives.postgresql.org/pgsql-hackers/2001-08/msg00629.php
There didn't seem to be a consensus to change things, so the old
behavior stands, for the moment.

regards, tom lane

Since you can overload and define new operators, the parser must - at some
point in time - lookup the operator definition.
It seems to me (but this is just an ideea), that the rules should go like
this :
...
check >$ is a defined operator
if true,   it is applied to left side and 1, in my example.
if not,   check that $1 is a valid expression (which it is)   ...go on...
This should do the trick. And you would be able to write leftSide>$$1 and
would get the >$ operator applied to leftSide and $1.
And leftSide>&1 would apply the >& operator to 1, and my code would then
have a [very suble] bug.
Simply put, operator precedence over local identifiers/parameters, which i
belive is a de facto standard in most languages (no flames please !)
:)

Cheers,

=====
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
----- Original Message -----
From: "Achilleus Mantzios" <achill@matrix.gatewaynet.com>
To: "Radu-Adrian Popescu" <radu.popescu@aldratech.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, January 09, 2003 9:29 PM
Subject: Re: [SQL] SQL function parse error ?


On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:

>
>
> Why is that ? Because the >$ does not exist, not in the default operator
> list (also there is no operator defined
> using $ anywhere within). And because whitespacing the code solves the
> problem, which is rather thin, i must say.
>

Radu-Adrian,
i think the parser is built with yacc, (not "from scratch code") so
maybe finding if ">$" is in the specific DB's operators
would require code that whould slower the whole parsing
process (imagine what it means for performance).


==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:

> Since you can overload and define new operators, the parser must - at some
> point in time - lookup the operator definition.
> It seems to me (but this is just an ideea), that the rules should go like
> this :
> ...
> check >$ is a defined operator
> if true,
>     it is applied to left side and 1, in my example.
> if not,
>     check that $1 is a valid expression (which it is)
>     ...go on...
> This should do the trick. And you would be able to write leftSide>$$1 and
> would get the >$ operator applied to leftSide and $1.
> And leftSide>&1 would apply the >& operator to 1, and my code would then
> have a [very suble] bug.
> Simply put, operator precedence over local identifiers/parameters, which i
> belive is a de facto standard in most languages (no flames please !)
[It's only an issue when you can define

IIRC there are issues with allowing table lookups in parse phase which the
above would require (otherwise you couldn't find out if >$ is defined).  I
believe it currently just makes an operator out of the longest sequence of
valid operator characters.  Also, I'm not sure how you'd get the above to
work with bison which is a fairly big deal.

If you can write a solution that meets all of the arguments that were made
the last time there was a discussion about it, it'd probably be accepted.

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:
>> Why is that ? Because the >$ does not exist, not in the default operator
>> list

> i think the parser is built with yacc, (not "from scratch code") so
> maybe finding if ">$" is in the specific DB's operators
> would require code that whould slower the whole parsing
> process (imagine what it means for performance).

There are a couple of good reasons why parsing strings into tokens does
not depend on looking to see which operators actually exist (as opposed
to which ones *could* exist per the defined rules for operator names):

1. It'd be impractical to detect whether the effective parsing rules are
complete or consistent, if they depend on the contents of database
tables that will vary from one installation to another.

2. The lexer and grammar stages of parsing cannot look into the database
state, because they have to be executable outside a transaction.
Otherwise we'd have problems with detecting/processing BEGIN, COMMIT,
ROLLBACK statements.

(Speed would probably be a significant issue too, though I don't have
any hard facts to back up that feeling.  We'd definitely have to abandon
the use of lex/flex tools to generate the lexing code.)

Because of these issues, the question of whether ">$" actually is
defined as an operator in a particular installation is irrelevant to
how we split character strings into tokens.  The only way we have to
adjust this behavior is by changing the rules about what an operator
name could be, for everyone.
        regards, tom lane

Please let me state again where i stand regarding this issue, apart from
tech stuff.
From the viewpoint of someone who has worked with databases for quite some
time, including postgresql (2yrs),
and is making a living out of it, -- that would be me :) -- it is a very odd
and unpleasant behaviour. That's a simple fact.
However, the feeling slips away in about 5 minutes or so, even if i'm
writing all db scripts by hand and have to pay attention
to this quite often. That's because i like pgsql, i enjoy compiling,
testing, tweaking configuration, trying to push the load
thru the roof and stuff like that. But that's me. I am not at all bothered
by this issue anymore.
What i'm saying is that i know that some of my colleagues, nice guys for
that matter, and good programmers, will come screaming
to me "what's with the b.s. error ?!?", and when i'll tell them that the sql
parser belives that's an inexisting operator, they'll start
cursing at it, just like i did.
For what it's worth, some policy should be enforced, because it shouldn't
matter how many spaces you put between the operator
and the operand, as writing SELECT * is the same as SELECT
*.
I rest my case.

Cheers,
=====
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Achilleus Mantzios" <achill@matrix.gatewaynet.com>
Cc: "Radu-Adrian Popescu" <radu.popescu@aldratech.com>;
<pgsql-sql@postgresql.org>
Sent: Thursday, January 09, 2003 5:57 PM
Subject: Re: [SQL] SQL function parse error ?


Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:
>> Why is that ? Because the >$ does not exist, not in the default operator
>> list

> i think the parser is built with yacc, (not "from scratch code") so
> maybe finding if ">$" is in the specific DB's operators
> would require code that whould slower the whole parsing
> process (imagine what it means for performance).

There are a couple of good reasons why parsing strings into tokens does
not depend on looking to see which operators actually exist (as opposed
to which ones *could* exist per the defined rules for operator names):

1. It'd be impractical to detect whether the effective parsing rules are
complete or consistent, if they depend on the contents of database
tables that will vary from one installation to another.

2. The lexer and grammar stages of parsing cannot look into the database
state, because they have to be executable outside a transaction.
Otherwise we'd have problems with detecting/processing BEGIN, COMMIT,
ROLLBACK statements.

(Speed would probably be a significant issue too, though I don't have
any hard facts to back up that feeling.  We'd definitely have to abandon
the use of lex/flex tools to generate the lexing code.)

Because of these issues, the question of whether ">$" actually is
defined as an operator in a particular installation is irrelevant to
how we split character strings into tokens.  The only way we have to
adjust this behavior is by changing the rules about what an operator
name could be, for everyone.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

On Thu, 9 Jan 2003, Tom Lane wrote:

> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:
> >> Why is that ? Because the >$ does not exist, not in the default operator
> >> list
>
> > i think the parser is built with yacc, (not "from scratch code") so
> > maybe finding if ">$" is in the specific DB's operators
> > would require code that whould slower the whole parsing
> > process (imagine what it means for performance).

> Because of these issues, the question of whether ">$" actually is
> defined as an operator in a particular installation is irrelevant to
> how we split character strings into tokens.  The only way we have to
> adjust this behavior is by changing the rules about what an operator
> name could be, for everyone.

Although the rules could be similar to those for + and - at the end of
operator strings (no $ at the end of an operator unless it contains
characters not normally in SQL92 operators).  I'm not sure that
behavior is sensible either, but if someone wanted to
do it for their own installation it's about a 2 line patch.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Although the rules could be similar to those for + and - at the end of
> operator strings (no $ at the end of an operator unless it contains
> characters not normally in SQL92 operators).  I'm not sure that
> behavior is sensible either, but if someone wanted to
> do it for their own installation it's about a 2 line patch.

It could be done that way.  But given that "$" already has one weird
special case in the operator name rules (ie, it can't be the only
character of an operator name), I feel that we'd be making things overly
complicated.

The proposal back in Aug 2001 was to remove "$" from the set of operator
name characters altogether (which would allow us to use it in
identifiers instead, improving Oracle compatibility).  I originally
objected to that idea on backwards-compatibility grounds, but I'm
leaning more and more to the view that it's the right thing to do.

I've re-opened the thread on pgsql-hackers about this, and we'll see
whether any consensus emerges this time.
        regards, tom lane

Nice to see that things are starting to move.
I was wandering however whether I've succeeded in making a point.

Regards, 
=====
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: [SQL] SQL function parse error ? 


Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Although the rules could be similar to those for + and - at the end of
> operator strings (no $ at the end of an operator unless it contains
> characters not normally in SQL92 operators).  I'm not sure that
> behavior is sensible either, but if someone wanted to
> do it for their own installation it's about a 2 line patch.

It could be done that way.  But given that "$" already has one weird
special case in the operator name rules (ie, it can't be the only
character of an operator name), I feel that we'd be making things overly
complicated.

The proposal back in Aug 2001 was to remove "$" from the set of operator
name characters altogether (which would allow us to use it in
identifiers instead, improving Oracle compatibility).  I originally
objected to that idea on backwards-compatibility grounds, but I'm
leaning more and more to the view that it's the right thing to do.

I've re-opened the thread on pgsql-hackers about this, and we'll see
whether any consensus emerges this time.

regards, tom lane

On Thu, 2003-01-09 at 11:29, Radu-Adrian Popescu wrote:
> What i'm saying is that i know that some of my colleagues, nice guys for
> that matter, and good programmers, will come screaming
> to me "what's with the b.s. error ?!?", and when i'll tell them that the sql
> parser belives that's an inexisting operator, they'll start
> cursing at it, just like i did.
>

Does oracle or mysql or whichever db you like allow the use of $ in user
defined operators?  If so, how do they know the difference?
For what it's worth, some policy should be enforced, because it shouldn't
> matter how many spaces you put between the operator
> and the operand, as writing SELECT * is the same as SELECT
> *.
> I rest my case.
> 

Thats an invalid comparison.  The problem is not that foo >         $1
doesn't work, as your example put forth. The problem is that foo>$1
doesn't work, which by comparison would be SELECT* which would also not
work.

Robert Treat

Robert, my dear fellow...

How about checking your facts before contradicting anyone ? Shame on you !
Have you actually tried to do a SELECT* from foo ? Pathetic !
Try it out, if that won't work on Oracle, MSSQL, PostgreSql I'll buy
everyone on this list a chase of Crystal.
Robert, even   select*from errors;
works on all three of them ! (Sorry about the colors, pasted from TOAD).
Here's psql for instance:

db=# SELECT * from prod ;id |     name     | price  | group_id
----+--------------+--------+---------- 4 | some product |  55.00 |        3 6 | MyPC         | 300.00 |        3
(2 rows)
db=# SELECT*from prod ;id |     name     | price  | group_id
----+--------------+--------+---------- 4 | some product |  55.00 |        3 6 | MyPC         | 300.00 |        3
(2 rows)

There you go. Now go sit in the corner ! :)
Also, as I tried to make it quite clear, the point is not whether Oracle or
mysql allow the
use of $ in operators, but whether you have to write cumbersome syntax to
get things working. And the
answer is definitely _no_.
My point here is that common sense (and the use of $1, $2,... with operator
> is going
come up a lot, opposed to the user-defined operator >$, which takes
precedence when parsing a special
case of the SQL command) should prevail over backwards compat. Loot at C++
for instance, the standard
broke a lot of C++ apps that were written poorly.

It seems that - strangely - instead of trying to acknowledge not necessarily
incorrect but awkward behavior,
some people on this list have tried to put me down.
What's even more scary is receiving answers like "SQL queries are like bash
commands",
or "SELECTXFROMY is not valid - whitespace matters" (when in fact I was
simply pointing out that
i==3 and i == 3 should be parsed alike), or Robert's claim that SELECT* from
... is invalid SQL.

The good news is some people seem to have gotten the point and are doing
something about it - and this
makes me feel like maybe, maybe I've helped the community just a little bit.
After all, we all want to see
postgresql up there where it belongs.

That being said, I do hope that superficial replies trying to prove me wrong
will stop, as they actually don't help
anyone.

Regards,
=====
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

----- Original Message -----
From: "Robert Treat" <xzilla@users.sourceforge.net>
To: "Radu-Adrian Popescu" <radu.popescu@aldratech.com>
Cc: "Achilleus Mantzios" <achill@matrix.gatewaynet.com>; "Tom Lane"
<tgl@sss.pgh.pa.us>; <pgsql-sql@postgresql.org>
Sent: Thursday, January 09, 2003 10:44 PM
Subject: Re: [SQL] SQL function parse error ?


On Thu, 2003-01-09 at 11:29, Radu-Adrian Popescu wrote:
> What i'm saying is that i know that some of my colleagues, nice guys for
> that matter, and good programmers, will come screaming
> to me "what's with the b.s. error ?!?", and when i'll tell them that the
sql
> parser belives that's an inexisting operator, they'll start
> cursing at it, just like i did.
>

Does oracle or mysql or whichever db you like allow the use of $ in user
defined operators?  If so, how do they know the difference?
For what it's worth, some policy should be enforced, because it shouldn't
> matter how many spaces you put between the operator
> and the operand, as writing SELECT * is the same as SELECT
> *.
> I rest my case.
>

Thats an invalid comparison.  The problem is not that foo >         $1
doesn't work, as your example put forth. The problem is that foo>$1
doesn't work, which by comparison would be SELECT* which would also not
work.

Robert Treat

On Fri, 2003-01-10 at 04:13, Radu-Adrian Popescu wrote:
> 
> Robert, my dear fellow...
> 
> How about checking your facts before contradicting anyone ? Shame on you !
> Have you actually tried to do a SELECT* from foo ? Pathetic !

At least you started out all nice and flowery...

> Try it out, if that won't work on Oracle, MSSQL, PostgreSql I'll buy
> everyone on this list a chase of Crystal.
> Robert, even
>     select*from errors;
> works on all three of them ! (Sorry about the colors, pasted from TOAD).
<snip>
> 
> There you go. Now go sit in the corner ! :)

Would an acceptable defense be that select*fromfoo doesn't work? Bah,
off to the corner I go...

> Also, as I tried to make it quite clear, the point is not whether Oracle or
> mysql allow the
> use of $ in operators, but whether you have to write cumbersome syntax to
> get things working. And the
> answer is definitely _no_.

But the point I was trying to make is that maybe the reason the answer
is no is that they don't support $ in operators. You'll note that a
clause like  mytime<now() works. Perhaps it's a quirk on my part that I
tend to want to know *why* something works the way it does before I go
about changing it. 

> My point here is that common sense (and the use of $1, $2,... with operator
> > is going
> come up a lot, opposed to the user-defined operator >$, which takes
> precedence when parsing a special
> case of the SQL command) should prevail over backwards compat. Loot at C++
> for instance, the standard
> broke a lot of C++ apps that were written poorly.
> 

Would you suggest the parser should assume >$1 equals > $1? That seems
likely to break a lot of cases where >$ was being used. OTOH, if your
saying that support of >$1  is more important than support of >$ 1
that's a different argument. That's what you are saying (afaict) and
that's also the path that the developers have taken in trying to resolve
the issue. 

> It seems that - strangely - instead of trying to acknowledge not necessarily
> incorrect but awkward behavior,
> some people on this list have tried to put me down.

I think people were trying to explain to you the reasons for the current
behavior, at least that's what I was trying to attempt to do. 

> What's even more scary is receiving answers like "SQL queries are like bash
> commands",
> or "SELECTXFROMY is not valid - whitespace matters" (when in fact I was
> simply pointing out that
> i==3 and i == 3 should be parsed alike), or Robert's claim that SELECT* from
> ... is invalid SQL.
> 

yeah, my bad on that one. but your argument was still a non starter
because we aren't debating support for >              $1 (like in your
example), but support for >$1.  Actually you should be thanking in me,
since while my supposition was wrong, my example helps bolster your case
somewhat. :-)  

> The good news is some people seem to have gotten the point and are doing
> something about it - and this
> makes me feel like maybe, maybe I've helped the community just a little bit.
> After all, we all want to see
> postgresql up there where it belongs.
> 
> That being said, I do hope that superficial replies trying to prove me wrong
> will stop, as they actually don't help
> anyone.
> 

Maybe I need to re-read some of the other posts, but I think your taking
this too personally. My email was simply trying to help frame the issue
properly, because I saw you making an invalid argument in your own
defense. Furthermore you need to realize that when someone makes a claim
that a certain feature needs to work in a different fashion, or needs to
be added at the expense of another feature, that it is only natural and
a good thing that the proposal be given a little scrutiny to make sure
it stands up. At this point yours does so in my book, though I still
would like to see answered is whether oracle or others support >$ as an
operator, or if the sql spec has anything to say on the matter. 

Robert Treat

Robert Treat <xzilla@users.sourceforge.net> writes:
> ... I still
> would like to see answered is whether oracle or others support >$ as an
> operator, or if the sql spec has anything to say on the matter. 

The SQL spec does not have the concept of user-definable operators at
all, so it provides no useful guidance about the set of characters that
ought to be allowed in operator names (except, obviously, that we must
include every character actually used in a SQL-standard operator).

Oracle reportedly treats $ as an identifier character, so I don't think
they could consider it an operator character.
        regards, tom lane