Re: parse error: LIMIT combined with UNION

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Steve Pothier
Your email address : pothiers(at)aries(dot)tucson(dot)saic(dot)com

System Configuration
---------------------
Architecture (example: Intel Pentium) : Mac PPC, G4

Operating System (example: Linux 2.0.26 ELF) : Linux payson 2.2.16

PostgreSQL version (example: PostgreSQL-6.5.1): PosegreSQL 7.1

Compiler used (example: gcc 2.8.0) :

Please enter a FULL description of your problem:
------------------------------------------------

This works as expected:

hbase=# SELECT * FROM subject WHERE subject_id LIKE '0009%' UNION (SELECT * FROM subject WHERE subject_id LIKE '0008%' );

But this fails to parse:

hbase=# SELECT * FROM subject WHERE subject_id LIKE '0009%' UNION (SELECT * FROM subject WHERE subject_id LIKE '0008%' LIMIT 5);
ERROR: parser: parse error at or near "limit"

This, despite indications to the contrary in the documentation:
file:/swl/postgresql-7.1/doc/html/sql-select.html#SQL-UNION

The following excerpt from the doc seems to be saying that a limit can
be used if included in the parenthesized select statement:

"where table_query specifies any select expression without an ORDER BY, FOR UPDATE, or
LIMIT clause. (ORDER BY and LIMIT can be attached to a sub-expression if it is enclosed in
parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION,
not to its right-hand input expression.) "

I tried several moving the parens around to no avail.

-Steve Pothier-
----------------------------------------------------------------------

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Steve Pothier <STEVEN(dot)POTHIER(at)saic(dot)com> writes:
> PostgreSQL version (example: PostgreSQL-6.5.1): PosegreSQL 7.1

> hbase=# SELECT * FROM subject WHERE subject_id LIKE '0009%' UNION (SELECT * FROM subject WHERE subject_id LIKE '0008%' LIMIT 5);
> ERROR: parser: parse error at or near "limit"

Are you sure that's actually 7.1, and not some beta release? The
command parses fine for me in current sources and in 7.1.3. AFAICT,
the last time anyone changed LIMIT/sub-SELECT parsing was 2001-01-15,
which would have been post 7.1-beta3.

regards, tom lane

Tom,

You are correct. My humblest apologies. When I did the test it was on
a machine not local to the database. The client machine was running
psql 7.1 but the server is (apparently) 7.0.3. I say "apparently"
because I'm not sure how to find the version of the server! I just
assume that the server and psql client versions match on a specific
machine.

Sorry again for my lack of diligence in the "bug" report.

-sp-

cc: pgsql-bugs(at)postgresql(dot)org
Date: Thu, 06 Sep 2001 15:24:54 -0400
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

Steve Pothier <STEVEN(dot)POTHIER(at)saic(dot)com> writes:
> PostgreSQL version (example: PostgreSQL-6.5.1): PosegreSQL 7.1

> hbase=# SELECT * FROM subject WHERE subject_id LIKE '0009%' UNION (SELECT * FROM subject WHERE subject_id LIKE '0008%' LIMIT 5);
> ERROR: parser: parse error at or near "limit"

Are you sure that's actually 7.1, and not some beta release? The
command parses fine for me in current sources and in 7.1.3. AFAICT,
the last time anyone changed LIMIT/sub-SELECT parsing was 2001-01-15,
which would have been post 7.1-beta3.

regards, tom lane

Steve Pothier <STEVEN(dot)POTHIER(at)saic(dot)com> writes:
> psql 7.1 but the server is (apparently) 7.0.3. I say "apparently"
> because I'm not sure how to find the version of the server!

select version();

regards, tom lane