Take the following for example:

create or replace fun= ction=20 testfunc1(int) returns setof test as
'
select * fr= om=20 test where age>$1;
'

language sql;

Th= is yealds the=20 following error, which frankly is kind of too=20 much :

ERROR: Unable t= o identify=20 an operator '>$' for types 'integer' and 'integer'

I strongly belive the parser is a bit keen= on=20 interpreting the >$ sequence as an operator. Take for instance php, wher= e all=20 variables start with $; " 100>$foo "<= /DIV>

is quite valid !

The error goes away of course when rewriti= ng it as=20 " age > $1".

I'm afraid that taking whitespace into=20 consideration when parsing a language is not the way to do it, except when = it's=20 python, of course :)

Hope this, helps,

Regards,
=3D=3D=3D=3D=3D
Radu-Adria= n Popescu
CSA,=20 DBA, Developer
Aldratech Ltd.

------=_NextPart_000_008C_01C2B7E0.FAB0E710-- From jasiek@klaster.net Thu Jan 9 11:52:32 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B6DDA475E22 for ; Thu, 9 Jan 2003 06:52:32 -0500 (EST) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by postgresql.org (Postfix) with ESMTP id 1D3F9475CCE for ; Thu, 9 Jan 2003 06:52:32 -0500 (EST) Received: from klaster.net (pe125.krakow.cvx.ppp.tpnet.pl [213.76.40.125]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 9FF162B21C; Thu, 9 Jan 2003 12:50:28 +0100 (CET) Message-ID: <3E1D6362.80701@klaster.net> Date: Thu, 09 Jan 2003 12:56:18 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US; rv:1.2b) Gecko/20021016 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Radu-Adrian Popescu Cc: pgsql-sql@postgresql.org Subject: Re: SQL function parse error ? References: <008f01c2b7d0$3733fdf0$0600a8c0@rpopescu> In-Reply-To: <008f01c2b7d0$3733fdf0$0600a8c0@rpopescu> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/121 X-Sequence-Number: 10986 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 From radu.popescu@aldratech.com Thu Jan 9 12:41:27 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B996D4758C9 for ; Thu, 9 Jan 2003 07:41:27 -0500 (EST) Received: from aldratech.com (unknown [213.154.158.58]) by postgresql.org (Postfix) with ESMTP id 79245474E61 for ; Thu, 9 Jan 2003 07:41:26 -0500 (EST) Received: from rpopescu ([192.168.0.6]) by aldratech.com for with eXtremail; Thu, 9 Jan 2003 15:42:16 +2GMT Message-ID: <00a501c2b7dc$a5c626b0$0600a8c0@rpopescu> From: "Radu-Adrian Popescu" To: References: <008f01c2b7d0$3733fdf0$0600a8c0@rpopescu> <3E1D6362.80701@klaster.net> Subject: Re: SQL function parse error ? Date: Thu, 9 Jan 2003 14:43:02 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/122 X-Sequence-Number: 10987 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" To: "Radu-Adrian Popescu" Cc: 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 From jasiek@klaster.net Thu Jan 9 13:19:13 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 813754758C9 for ; Thu, 9 Jan 2003 08:19:13 -0500 (EST) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by postgresql.org (Postfix) with ESMTP id D7A7C474E61 for ; Thu, 9 Jan 2003 08:19:12 -0500 (EST) Received: from klaster.net (pe233.krakow.cvx.ppp.tpnet.pl [213.76.40.233]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 11BBC2B21C; Thu, 9 Jan 2003 14:17:13 +0100 (CET) Message-ID: <3E1D77B3.4000503@klaster.net> Date: Thu, 09 Jan 2003 14:22:59 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US; rv:1.2b) Gecko/20021016 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Radu-Adrian Popescu Cc: pgsql-sql@postgresql.org Subject: Re: SQL function parse error ? References: <008f01c2b7d0$3733fdf0$0600a8c0@rpopescu> <3E1D6362.80701@klaster.net> <00a501c2b7dc$a5c626b0$0600a8c0@rpopescu> In-Reply-To: <008f01c2b7d0$3733fdf0$0600a8c0@rpopescu> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/123 X-Sequence-Number: 10988 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 a sequence of up to NAMEDATALEN-1 (31 by default) >characters from the following list: >+ - * / < > = ~ ! @ # % ^ & | ` ? $ Regards, Tomasz Myrta From tgl@sss.pgh.pa.us Thu Jan 9 14:48:14 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 12A31475D64 for ; Thu, 9 Jan 2003 09:48:14 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 4FE8F475D20 for ; Thu, 9 Jan 2003 09:48:13 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h09EmE0U029441; Thu, 9 Jan 2003 09:48:14 -0500 (EST) To: "Radu-Adrian Popescu" Cc: pgsql-sql@postgresql.org Subject: Re: SQL function parse error ? In-reply-to: <00a501c2b7dc$a5c626b0$0600a8c0@rpopescu> References: <008f01c2b7d0$3733fdf0$0600a8c0@rpopescu> <3E1D6362.80701@klaster.net> <00a501c2b7dc$a5c626b0$0600a8c0@rpopescu> Comments: In-reply-to "Radu-Adrian Popescu" message dated "Thu, 09 Jan 2003 14:43:02 +0200" Date: Thu, 09 Jan 2003 09:48:14 -0500 Message-ID: <29440.1042123694@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/125 X-Sequence-Number: 10990 "Radu-Adrian Popescu" 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 From radu.popescu@aldratech.com Thu Jan 9 15:04:13 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 892A4476139 for ; Thu, 9 Jan 2003 10:04:13 -0500 (EST) Received: from aldratech.com (unknown [213.154.158.58]) by postgresql.org (Postfix) with ESMTP id 4DED7475D64 for ; Thu, 9 Jan 2003 10:04:07 -0500 (EST) Received: from rpopescu ([192.168.0.6]) by aldratech.com for with eXtremail; Thu, 9 Jan 2003 18:04:59 +2GMT Message-ID: <001101c2b7f0$950c98e0$0600a8c0@rpopescu> From: "Radu-Adrian Popescu" To: References: <008f01c2b7d0$3733fdf0$0600a8c0@rpopescu> <3E1D6362.80701@klaster.net> <00a501c2b7dc$a5c626b0$0600a8c0@rpopescu> <3E1D77B3.4000503@klaster.net> Subject: Re: SQL function parse error ? Date: Thu, 9 Jan 2003 17:05:43 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/126 X-Sequence-Number: 10991 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" To: "Radu-Adrian Popescu" Cc: 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 a sequence 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 From radu.popescu@aldratech.com Thu Jan 9 15:22:25 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 67F7A47630A for ; Thu, 9 Jan 2003 10:22:25 -0500 (EST) Received: from aldratech.com (unknown [213.154.158.58]) by postgresql.org (Postfix) with ESMTP id 889A047661D for ; Thu, 9 Jan 2003 10:21:01 -0500 (EST) Received: from rpopescu ([192.168.0.6]) by aldratech.com for with eXtremail; Thu, 9 Jan 2003 18:21:54 +2GMT Message-ID: <001b01c2b7f2$f1cdcc00$0600a8c0@rpopescu> From: "Radu-Adrian Popescu" To: "Tom Lane" Cc: References: <008f01c2b7d0$3733fdf0$0600a8c0@rpopescu> <3E1D6362.80701@klaster.net> <00a501c2b7dc$a5c626b0$0600a8c0@rpopescu> <29440.1042123694@sss.pgh.pa.us> Subject: Re: SQL function parse error ? Date: Thu, 9 Jan 2003 17:22:38 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/128 X-Sequence-Number: 10993 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" To: "Radu-Adrian Popescu" Cc: Sent: Thursday, January 09, 2003 4:48 PM Subject: Re: [SQL] SQL function parse error ? "Radu-Adrian Popescu" 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 From radu.popescu@aldratech.com Thu Jan 9 15:37:40 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B71F1476785 for ; Thu, 9 Jan 2003 10:37:40 -0500 (EST) Received: from aldratech.com (unknown [213.154.158.58]) by postgresql.org (Postfix) with ESMTP id 868E5476599 for ; Thu, 9 Jan 2003 10:37:12 -0500 (EST) Received: from rpopescu ([192.168.0.6]) by aldratech.com for with eXtremail; Thu, 9 Jan 2003 18:38:06 +2GMT Message-ID: <003301c2b7f5$34b6c920$0600a8c0@rpopescu> From: "Radu-Adrian Popescu" To: "Achilleus Mantzios" Cc: References: Subject: Re: SQL function parse error ? Date: Thu, 9 Jan 2003 17:38:49 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/129 X-Sequence-Number: 10994 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" To: "Radu-Adrian Popescu" Cc: 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 From sszabo@megazone23.bigpanda.com Thu Jan 9 15:47:25 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4A1D7475D22 for ; Thu, 9 Jan 2003 10:47:25 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id AD2FF475D12 for ; Thu, 9 Jan 2003 10:47:24 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 2F3FBD60B; Thu, 9 Jan 2003 07:47:29 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 24FBA5C04; Thu, 9 Jan 2003 07:47:29 -0800 (PST) Date: Thu, 9 Jan 2003 07:47:29 -0800 (PST) From: Stephan Szabo To: Radu-Adrian Popescu Cc: Achilleus Mantzios , Subject: Re: SQL function parse error ? In-Reply-To: <003301c2b7f5$34b6c920$0600a8c0@rpopescu> Message-ID: <20030109073958.B91463-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/130 X-Sequence-Number: 10995 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. From tgl@sss.pgh.pa.us Thu Jan 9 15:57:18 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E7C62476917 for ; Thu, 9 Jan 2003 10:57:18 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 54A374763E5 for ; Thu, 9 Jan 2003 10:57:08 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h09Fv60U000778; Thu, 9 Jan 2003 10:57:07 -0500 (EST) To: Achilleus Mantzios Cc: Radu-Adrian Popescu , pgsql-sql@postgresql.org Subject: Re: SQL function parse error ? In-reply-to: References: Comments: In-reply-to Achilleus Mantzios message dated "Thu, 09 Jan 2003 17:29:09 -0200" Date: Thu, 09 Jan 2003 10:57:06 -0500 Message-ID: <777.1042127826@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/131 X-Sequence-Number: 10996 Achilleus Mantzios 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 From radu.popescu@aldratech.com Thu Jan 9 16:27:43 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3CDF1476477 for ; Thu, 9 Jan 2003 11:27:43 -0500 (EST) Received: from aldratech.com (unknown [213.154.158.58]) by postgresql.org (Postfix) with ESMTP id AB80B475EC7 for ; Thu, 9 Jan 2003 11:27:40 -0500 (EST) Received: from rpopescu ([192.168.0.6]) by aldratech.com for with eXtremail; Thu, 9 Jan 2003 19:28:35 +2GMT Message-ID: <006301c2b7fc$41e58e40$0600a8c0@rpopescu> From: "Radu-Adrian Popescu" To: "Achilleus Mantzios" , "Tom Lane" Cc: References: <777.1042127826@sss.pgh.pa.us> Subject: Re: SQL function parse error ? Date: Thu, 9 Jan 2003 18:29:18 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/132 X-Sequence-Number: 10997 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" To: "Achilleus Mantzios" Cc: "Radu-Adrian Popescu" ; Sent: Thursday, January 09, 2003 5:57 PM Subject: Re: [SQL] SQL function parse error ? Achilleus Mantzios 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) From sszabo@megazone23.bigpanda.com Thu Jan 9 17:00:27 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 27AEC475FEB for ; Thu, 9 Jan 2003 12:00:27 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 74A9B475FC6 for ; Thu, 9 Jan 2003 12:00:26 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 741CCD60B; Thu, 9 Jan 2003 09:00:31 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 69D675C03; Thu, 9 Jan 2003 09:00:31 -0800 (PST) Date: Thu, 9 Jan 2003 09:00:31 -0800 (PST) From: Stephan Szabo To: Tom Lane Cc: Achilleus Mantzios , Radu-Adrian Popescu , Subject: Re: SQL function parse error ? In-Reply-To: <777.1042127826@sss.pgh.pa.us> Message-ID: <20030109085635.I92199-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/133 X-Sequence-Number: 10998 On Thu, 9 Jan 2003, Tom Lane wrote: > Achilleus Mantzios 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. From tgl@sss.pgh.pa.us Thu Jan 9 17:17:16 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 81F1347621D for ; Thu, 9 Jan 2003 12:17:16 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 86080475D12 for ; Thu, 9 Jan 2003 12:17:15 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h09HHF0U005571; Thu, 9 Jan 2003 12:17:15 -0500 (EST) To: Stephan Szabo Cc: Achilleus Mantzios , Radu-Adrian Popescu , pgsql-sql@postgresql.org Subject: Re: SQL function parse error ? In-reply-to: <20030109085635.I92199-100000@megazone23.bigpanda.com> References: <20030109085635.I92199-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Thu, 09 Jan 2003 09:00:31 -0800" Date: Thu, 09 Jan 2003 12:17:15 -0500 Message-ID: <5570.1042132635@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/134 X-Sequence-Number: 10999 Stephan Szabo 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 From radu.popescu@aldratech.com Thu Jan 9 17:35:42 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AE5E8476EFC for ; Thu, 9 Jan 2003 12:35:42 -0500 (EST) Received: from aldratech.com (unknown [213.154.158.58]) by postgresql.org (Postfix) with ESMTP id C309B476D32 for ; Thu, 9 Jan 2003 12:35:27 -0500 (EST) Received: from rpopescu ([192.168.0.6]) by aldratech.com for with eXtremail; Thu, 9 Jan 2003 20:36:20 +2GMT Message-ID: <001501c2b805$b8d59eb0$0600a8c0@rpopescu> From: "Radu-Adrian Popescu" To: "Stephan Szabo" , "Tom Lane" Cc: "Achilleus Mantzios" , References: <20030109085635.I92199-100000@megazone23.bigpanda.com> <5570.1042132635@sss.pgh.pa.us> Subject: Re: SQL function parse error ? Date: Thu, 9 Jan 2003 19:37:03 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/135 X-Sequence-Number: 11000 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" Subject: Re: [SQL] SQL function parse error ? Stephan Szabo 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 From achill@matrix.gatewaynet.com Thu Jan 9 15:20:47 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 73ED24768EE for ; Thu, 9 Jan 2003 10:20:47 -0500 (EST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 3EE0C47683E for ; Thu, 9 Jan 2003 10:20:07 -0500 (EST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h09JT9nv022229; Thu, 9 Jan 2003 17:29:09 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h09JT9fE022225; Thu, 9 Jan 2003 17:29:09 -0200 Date: Thu, 9 Jan 2003 17:29:09 -0200 (GMT+2) From: Achilleus Mantzios To: Radu-Adrian Popescu Cc: pgsql-sql@postgresql.org Subject: Re: SQL function parse error ? In-Reply-To: <001101c2b7f0$950c98e0$0600a8c0@rpopescu> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/127 X-Sequence-Number: 10992 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 From xzilla@users.sourceforge.net Thu Jan 9 20:44:12 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9B0C9475DA7 for ; Thu, 9 Jan 2003 15:44:12 -0500 (EST) Received: from localhost.localdomain (unknown [65.217.53.66]) by postgresql.org (Postfix) with ESMTP id 098EC475CB4 for ; Thu, 9 Jan 2003 15:44:12 -0500 (EST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h09L1P6P009084; Thu, 9 Jan 2003 16:01:26 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h09Ki3j03843; Thu, 9 Jan 2003 15:44:05 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id XX5J5BAT; Thu, 9 Jan 2003 15:44:03 -0500 Subject: Re: SQL function parse error ? From: Robert Treat To: Radu-Adrian Popescu Cc: Achilleus Mantzios , Tom Lane , pgsql-sql@postgresql.org In-Reply-To: <006301c2b7fc$41e58e40$0600a8c0@rpopescu> References: <777.1042127826@sss.pgh.pa.us> <006301c2b7fc$41e58e40$0600a8c0@rpopescu> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 09 Jan 2003 15:44:03 -0500 Message-Id: <1042145043.2007.31.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/138 X-Sequence-Number: 11003 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 From radu.popescu@aldratech.com Fri Jan 10 09:11:31 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7DFB0475E5E for ; Fri, 10 Jan 2003 04:11:31 -0500 (EST) Received: from aldratech.com (unknown [213.154.158.58]) by postgresql.org (Postfix) with ESMTP id 1D598475E24 for ; Fri, 10 Jan 2003 04:11:30 -0500 (EST) Received: from rpopescu ([192.168.0.6]) by aldratech.com for with eXtremail; Fri, 10 Jan 2003 12:12:28 +2GMT Message-ID: <000b01c2b888$79e18100$0600a8c0@rpopescu> From: "Radu-Adrian Popescu" To: "Robert Treat" Cc: "Achilleus Mantzios" , "Tom Lane" , References: <777.1042127826@sss.pgh.pa.us> <006301c2b7fc$41e58e40$0600a8c0@rpopescu> <1042145043.2007.31.camel@camel> Subject: Re: SQL function parse error ? Date: Fri, 10 Jan 2003 11:13:01 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/148 X-Sequence-Number: 11013 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" To: "Radu-Adrian Popescu" Cc: "Achilleus Mantzios" ; "Tom Lane" ; 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 From xzilla@users.sourceforge.net Fri Jan 10 14:27:03 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7B8FF475A1E for ; Fri, 10 Jan 2003 09:27:03 -0500 (EST) Received: from localhost.localdomain (unknown [65.217.53.66]) by postgresql.org (Postfix) with ESMTP id A664A475925 for ; Fri, 10 Jan 2003 09:27:02 -0500 (EST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h0AEiT6P010849; Fri, 10 Jan 2003 09:44:30 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h0AEQwj11674; Fri, 10 Jan 2003 09:26:59 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id XX5J5DBF; Fri, 10 Jan 2003 09:26:56 -0500 Subject: Re: SQL function parse error ? From: Robert Treat To: Radu-Adrian Popescu Cc: Achilleus Mantzios , Tom Lane , pgsql-sql@postgresql.org In-Reply-To: <000b01c2b888$79e18100$0600a8c0@rpopescu> References: <777.1042127 826@sss.pgh.pa.us> <006301c2b7fc$41e58e40$0600a8c0@rpopescu> <1042145043.2007.31.camel@camel> <000b01c2b888$79e18100$0600a8c0@rpopescu> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 10 Jan 2003 09:26:58 -0500 Message-Id: <1042208818.2008.83.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/151 X-Sequence-Number: 11016 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). > > 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 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 From tgl@sss.pgh.pa.us Fri Jan 10 14:48:02 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B85AF475AFF for ; Fri, 10 Jan 2003 09:48:02 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 0CD64475925 for ; Fri, 10 Jan 2003 09:48:02 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0AEm10U000844; Fri, 10 Jan 2003 09:48:01 -0500 (EST) To: Robert Treat Cc: Radu-Adrian Popescu , Achilleus Mantzios , pgsql-sql@postgresql.org Subject: Re: SQL function parse error ? In-reply-to: <1042208818.2008.83.camel@camel> References: <777.1042127 826@sss.pgh.pa.us> <006301c2b7fc$41e58e40$0600a8c0@rpopescu> <1042145043.2007.31.camel@camel> <000b01c2b888$79e18100$0600a8c0@rpopescu> <1042208818.2008.83.camel@camel> Comments: In-reply-to Robert Treat message dated "10 Jan 2003 09:26:58 -0500" Date: Fri, 10 Jan 2003 09:48:01 -0500 Message-ID: <843.1042210081@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/152 X-Sequence-Number: 11017 Robert Treat 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