pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: pgsql-sql@postgreSQL.org
Cc: Stuart Rison <stuart@ludwig.ucl.ac.uk>
Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL?
Date: Mon, 16 Aug 1999 18:52:35 +0300
Message-ID: <l03130301b3dde045b9a3@[147.233.159.109]> (raw)
In-Reply-To: <v04020a00b3ddd52e6167@[128.40.242.190]>
References: <632.934812731@sss.pgh.pa.us>
	<v04020a03b3dd80db8fc9@[128.40.242.190]>

At 17:57 +0300 on 16/08/1999, Stuart Rison wrote:


> CREATE FUNCTION test(int2) RETURNS int2 AS '
> SELECT field2 FROM test
> 	WHERE field1=$1;
> ' language 'sql';
>
> ERROR:  There is no operator '=$' for types 'int2' and 'int4'
>         You will either have to retype this query using an explicit cast,
>         or you will have to define the operator using CREATE OPERATOR
>
> Same problem with SQL functions (this is with PG6.4).
>
> I don't know what the standard syntax for operators is but -if it is not
> compulsary to "bound" the operator with spaces- I guess it's a (minor) bug
> with the SQL parser.

It's a lexical analysis problem, not a parsing problem. When you see the
string 'WHERE abcdefg=$1', do you tokenize it as
<WHERE> <abcdefg> <=$> <1>
or as
<WHERE> <abcdefg> <=> <$1>

Now, imagine someone defining the boolean operator
   n =$ k
As meaning "n has k digits". ( 10 =$ 2 is true, 1 =$ 2 is false).

Either one of the two interpretations of the WHERE clause would then be a
valid one!

How does one avoid a conflict? You can disallow such operators (any
operator with a right-side $) - but that would limit the users and may
potentially hurt existing programs. You can require that spaces are always
around operators, disallowing things like field1=3 - but that would hurt
even more existing pragrams. Or, you can default to one of the two
interpretations. The one that expects =$ seems to be the more obvious
default.

Bottom line is, however, that this is a rather problematic error message.
It's not much in the way of showing the user what he did wrong. Perhaps
adding a line saying that "this may result from not having a space between
an operator and a variable" would do the trick. And the message is
completely out of touch when the $ results from an internal string
replcement, in which case I really think it is recommended to add the
spaces when replacing the string. It can't harm, and it will avoid the
problems for sure.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (9+ messages)  latest in thread

Message-ID: <l03130301b3dde045b9a3@[147.233.159.109]>
Permalink:  ../l03130301b3dde045b9a3@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130301b3dde045b9a3@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, stuart@ludwig.ucl.ac.uk
  Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL?
  In-Reply-To: <l03130301b3dde045b9a3@[147.233.159.109]>
  References: <632.934812731@sss.pgh.pa.us> <v04020a03b3dd80db8fc9@[128.40.242.190]> <l03130301b3dde045b9a3@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox