PostgreSQL JDBC and sub-select

On Sat, 9 Nov 2002, snpe wrote:

Hello,
I work with JDeveloper and PostgreSQL JDBC and I have one problem.
I get error :
sub-SELECT in FORM must have an alias
I can't change SQL command, but it is internal JDeveloper command

Is it SQL standard (must have alias) or PostgreSQL specific ?

It looks to me to be standard.

I think the appropriate portion of the grammar is:
<table reference> :=
<derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]

<derived table> := <table subquery>

<correlation name> := <identifier>

snpe <snpe@snpe.co.yu> writes:

I work with JDeveloper and PostgreSQL JDBC and I have one problem.
I get error :
sub-SELECT in FORM must have an alias

Is it SQL standard (must have alias) or PostgreSQL specific ?

The SQL standard says you must write an alias. A FROM item is a <table
reference> (SQL92 section 7.4), which is defined (in SQL92 6.3) as

<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <joined table>

where

<derived table> ::= <table subquery>

The square brackets show that an alias (<correlation name>) is optional
for a plain table name, but is required for a sub-SELECT (<derived
table>).

This is not just a random whim on the part of the SQL spec writers.
One reason why they did it that way is to ensure that some specific
<correlation name> can be associated with every column produced by a
FROM clause. I used to remember some other interesting consequences,
but it's too late on a Saturday night to recall all the details...

I can't change SQL command, but it is internal JDeveloper command

I have zero sympathy for "please change Postgres because my application
cannot be bothered to comply with the SQL specification".

regards, tom lane

snpe kirjutas L, 09.11.2002 kell 22:51:

Hello,
I work with JDeveloper and PostgreSQL JDBC and I have one problem.
I get error :
sub-SELECT in FORM must have an alias
I can't change SQL command, but it is internal JDeveloper command

You could set up query logging in the backend and see what the offending
query is. It may still be something you did (a missing or extra
something somewhere).

---------------
Hannu

You could set up query logging in the backend and see what the offending
query is. It may still be something you did (a missing or extra
something somewhere).

How ?

These settings have worked for me in a similar situation: (pulled from the
admin list archives)

<snip>
My goal was to get all of the SQL statements from a JDBC front-end to be
logged as they are executed in the postgres.log file (and not in the
syslog.) Adding the following to my postgresql.conf did the job:

syslog = 0
silent_mode = off
debug_print_query = on
debug_pretty_print = on

I'm not sure if the pretty print option does anything for the SQL, but it
didn't hurt.
</snip>

The results appear on /var/log/postgresql.log using the Debian Linux
distribution. Not sure of the location in others.

-Nick

On Sunday 10 November 2002 05:27 am, Tom Lane wrote:

snpe <snpe@snpe.co.yu> writes:

I work with JDeveloper and PostgreSQL JDBC and I have one problem.
I get error :
sub-SELECT in FORM must have an alias

Is it SQL standard (must have alias) or PostgreSQL specific ?

The SQL standard says you must write an alias. A FROM item is a <table
reference> (SQL92 section 7.4), which is defined (in SQL92 6.3) as

<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]

| <derived table> [ AS ] <correlation name>

[ <left paren> <derived column list> <right paren> ]

| <joined table>

where

<derived table> ::= <table subquery>

The square brackets show that an alias (<correlation name>) is optional
for a plain table name, but is required for a sub-SELECT (<derived
table>).

This is not just a random whim on the part of the SQL spec writers.
One reason why they did it that way is to ensure that some specific
<correlation name> can be associated with every column produced by a
FROM clause. I used to remember some other interesting consequences,
but it's too late on a Saturday night to recall all the details...

I can't change SQL command, but it is internal JDeveloper command

I have zero sympathy for "please change Postgres because my application
cannot be bothered to comply with the SQL specification".

I didn't say that.I asked if that is PostgreSQL specific.Thanks for answer.

regards
Haris Peco

On Sunday 10 November 2002 08:51 am, Hannu Krosing wrote:

snpe kirjutas L, 09.11.2002 kell 22:51:

Hello,
I work with JDeveloper and PostgreSQL JDBC and I have one problem.
I get error :
sub-SELECT in FORM must have an alias
I can't change SQL command, but it is internal JDeveloper command

You could set up query logging in the backend and see what the offending
query is. It may still be something you did (a missing or extra
something somewhere).

How ?

regards
Haris Peco

Hello,
Query :

select id i
from tab

don't work in PostgreSQL

I know that

select id as i
from tab

work, but I think that AS in 'as cluse' is optional

Please comment.

regards
Haris Peco

On Sun, 2002-11-10 at 10:41, snpe wrote:

Hello,
Query :

select id i
from tab

don't work in PostgreSQL

I know that

select id as i
from tab

work, but I think that AS in 'as cluse' is optional

Thats true. Section 7.11 has it as optional

<derived column> ::=
<value expression> [ <as clause> ]

<as clause> ::= [ AS ] <column name>

But this isn't going to be very nice to fix.

conflicts: 1378 shift/reduce, 44 reduce/reduce

--
Rod Taylor

snpe wrote:

work, but I think that AS in 'as cluse' is optional

Please comment.

See:
http://developer.postgresql.org/docs/postgres/sql-select.html

Near the bottom:

" SQL92

SELECT Clause

In the SQL92 standard, the optional keyword AS is just noise and can be
omitted without affecting the meaning. The PostgreSQL parser requires this
keyword when renaming output columns because the type extensibility features
lead to parsing ambiguities in this context. AS is optional in FROM items,
however."

Joe

Joe Conway <mail@joeconway.com> quotes:

In the SQL92 standard, the optional keyword AS is just noise and can be
omitted without affecting the meaning. The PostgreSQL parser requires this
keyword when renaming output columns because the type extensibility features
lead to parsing ambiguities in this context. AS is optional in FROM items,
however.

Actually, I think it's not so much datatype extensibility as operator
extensibility, and specifically the fact that we allow postfix
operators. If AS were optional, then

SELECT 1 + x FROM foo;

could be parsed either as "(1 + x)" (infix +, x presumably a column name)
or as "(1 +) x" (postfix +, x an AS-name).

So allowing AS to be optional would at minimum require taking out
postfix operators. There might be other features we'd have to lose,
too; I haven't tried messing with the grammar to see what would happen.

regards, tom lane

On Sunday 10 November 2002 08:51 am, Hannu Krosing wrote:

snpe kirjutas L, 09.11.2002 kell 22:51:

Hello,
I work with JDeveloper and PostgreSQL JDBC and I have one problem.
I get error :
sub-SELECT in FORM must have an alias
I can't change SQL command, but it is internal JDeveloper command

You could set up query logging in the backend and see what the offending
query is. It may still be something you did (a missing or extra
something somewhere).

query is like this :
select * from (select * from tab) where 1=2

This is work with Oracle,DB2,SQL Server
Postgresql request alias in sub-select in from clause

regards
Haris Peco

On Sunday 10 November 2002 08:51 am, Hannu Krosing wrote:

snpe kirjutas L, 09.11.2002 kell 22:51:

Hello,
I work with JDeveloper and PostgreSQL JDBC and I have one problem.
I get error :
sub-SELECT in FORM must have an alias
I can't change SQL command, but it is internal JDeveloper command

You could set up query logging in the backend and see what the offending
query is. It may still be something you did (a missing or extra
something somewhere).

query is like this :
select * from (select * from tab) where 1=2

This is work with Oracle,DB2,SQL Server
Postgresql request alias in sub-select in from clause

The SQL standard requires that you alias it. Postgres follows the standard
on this:

select * from (select * from tab) as sub where 1=2;

Chris