Minor irritant with comment parsing in a function (SQL) body

Richard Huxton writes:

CREATE OR REPLACE FUNCTION zzz_test () RETURNS text AS '
SELECT ''hello world''
-- SELECT ''goodbye world''
::text;
' LANGUAGE 'SQL';

ERROR: parser: unterminated quoted string at or near "'hello world'
-- SELECT 'goodbye world'
::text;

That's a good one. The bug is actually independent of the function
definition, but you cannot easily reproduce it in psql, because psql cuts
out -- comment before sending the command to the server. Here's how one
could do it:

cmd=$(echo -e "SELECT 'hello world'\n-- SELECT 'goodbye world'\n::text;")
psql -c "$cmd"

The problem is strings of this form:

'foo'
'bar'

This is equivalent to 'foobar'. Comments are also allowed between the
parts:

'foo'
-- abc
'bar'

Still equivalent to 'foobar'. In your case it's scanning the string
similar to

'hello world'
-- SELECT 'goodbye world
'\n::text;

Hence the complain the the string is not terminated.

The bug here is that the scanner doesn't know that a newline (or end of
input) is a required as part of a -- comment. If I change the rule

comment ("--"{non_newline}*)

in scan.l to

comment ("--"{non_newline}*){newline}

then the example works. This does not cover the case of a comment at the
end of the input, but a solution shall be forthcoming.

--
Peter Eisentraut peter_e@gmx.net

On Wednesday 08 October 2003 20:56, Peter Eisentraut wrote:

Richard Huxton writes:

CREATE OR REPLACE FUNCTION zzz_test () RETURNS text AS '
SELECT ''hello world''
-- SELECT ''goodbye world''

::text;

' LANGUAGE 'SQL';

ERROR: parser: unterminated quoted string at or near "'hello world'
-- SELECT 'goodbye world'

::text;

That's a good one.

Well, I try ;-)

The bug is actually independent of the function
definition, but you cannot easily reproduce it in psql, because psql cuts
out -- comment before sending the command to the server. Here's how one
could do it:

You intrigue me sir...

cmd=$(echo -e "SELECT 'hello world'\n-- SELECT 'goodbye world'\n::text;")
psql -c "$cmd"

The problem is strings of this form:

'foo'
'bar'

This is equivalent to 'foobar'.

Ah - now if I ever new that, I'd forgotten it.

[snip]

In your case it's scanning the string
similar to

'hello world'
-- SELECT 'goodbye world
'\n::text;

Hence the complain the the string is not terminated.

So given the "comment" defn in scan.l, it's seeing the quote as the next token
in the input stream? (Wracks brain thinking back to compiler technologies
class in a decade a long, long way from here).

The bug here is that the scanner doesn't know that a newline (or end of
input) is a required as part of a -- comment. If I change the rule

comment ("--"{non_newline}*)

in scan.l to

comment ("--"{non_newline}*){newline}

then the example works. This does not cover the case of a comment at the
end of the input, but a solution shall be forthcoming.

Ah - in perl you'd be looking for a pattern anchored with a $ - I see what you
mean.

Thanks for the explanation Peter.

--
Richard Huxton
Archonet Ltd

Peter Eisentraut <peter_e@gmx.net> writes:

The bug here is that the scanner doesn't know that a newline (or end of
input) is a required as part of a -- comment.

I think the minimum-damage place to fix this is by requiring \n after
{comment} in the horiz_whitespace rule. As is, it's possible for
xqcat to match to a second quote that is in the body of a -- comment.

regards, tom lane

Tom Lane writes:

I think the minimum-damage place to fix this is by requiring \n after
{comment} in the horiz_whitespace rule. As is, it's possible for
xqcat to match to a second quote that is in the body of a -- comment.

You mean like this?

horiz_whitespace ({horiz_space}|{comment}{newline})
whitespace_with_newline ({horiz_whitespace}*{newline}{whitespace}*)

That doesn't work. The offending comment in the example is matched to
{whitespace} after {newline} in the second rule. The {horiz_whitespace}*
is matched to empty. We could do

special_whitespace ({space}+|{comment}{newline})
horiz_whitespace ({horiz_space}|{comment})
whitespace_with_newline ({horiz_whitespace}*{newline}{special_whitespace}*)

--
Peter Eisentraut peter_e@gmx.net

Peter has fixed this in CVS. Thanks for the report.

---------------------------------------------------------------------------

Richard Huxton wrote:

Versions: 7.3, 7.4beta (not latest)
Applies to SQL functions, but not apparently to plpgsql (because of the
different parser, I presume).

This is really a "doctor it hurts when I..." thing - the fact that I've never
come across it before must mean it's pretty hard to trigger.

The first version of this function compiles fine, the second doesn't.

CREATE OR REPLACE FUNCTION zzz_test () RETURNS text AS '
SELECT ''hello world''::text;
-- SELECT ''goodbye world''
' LANGUAGE 'SQL';

CREATE OR REPLACE FUNCTION zzz_test () RETURNS text AS '
SELECT ''hello world''
-- SELECT ''goodbye world''
::text;
' LANGUAGE 'SQL';

ERROR: parser: unterminated quoted string at or near "'hello world'
-- SELECT 'goodbye world'
::text;
"

It took forever to find a simple reproducible example, but the rules seem to
be:
1. You must have quoting at the end of both adjacent lines
2. Both lines must be within a single SQL statement.
3. You must use the -- comment, not /* ... */

Originally spotted with a formulation like:
AND type1 = ''A''
AND type2 = ''B''
...
I commented one test and got an error message.

--
Richard Huxton
Archonet Ltd

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073