Lists: | pgsql-generalpgsql-hackerspgsql-jdbc |
---|
From: | snpe <snpe(at)snpe(dot)co(dot)yu> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org>, pgsql-general(at)postgresql(dot)org, <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | PostgreSQL JDBC and sub-select |
Date: | 2002-11-09 17:51:41 |
Message-ID: | 200211091751.41603.snpe@snpe.co.yu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
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 ?
Regards
Haris Peco
From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | snpe <snpe(at)snpe(dot)co(dot)yu> |
Cc: | <pgsql-hackers(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org>, <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL JDBC and sub-select |
Date: | 2002-11-09 19:07:17 |
Message-ID: | 20021109110438.J29283-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
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>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | snpe <snpe(at)snpe(dot)co(dot)yu> |
Cc: | pgsql-hackers(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: [HACKERS] PostgreSQL JDBC and sub-select |
Date: | 2002-11-10 05:27:53 |
Message-ID: | 6947.1036906073@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
snpe <snpe(at)snpe(dot)co(dot)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
From: | Hannu Krosing <hannu(at)tm(dot)ee> |
---|---|
To: | snpe <snpe(at)snpe(dot)co(dot)yu> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, pgsql-general(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: [HACKERS] PostgreSQL JDBC and sub-select |
Date: | 2002-11-10 08:51:08 |
Message-ID: | 1036918269.1918.6.camel@rh72.home.ee |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
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
From: | "Nick Fankhauser" <nickf(at)ontko(dot)com> |
---|---|
To: | "snpe" <snpe(at)snpe(dot)co(dot)yu>, <pgsql-hackers(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org>, <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: [JDBC] [HACKERS] PostgreSQL JDBC and sub-select |
Date: | 2002-11-10 14:37:44 |
Message-ID: | NEBBLAAHGLEEPCGOBHDGCEFNGCAA.nickf@ontko.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
> > 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
From: | snpe <snpe(at)snpe(dot)co(dot)yu> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: [HACKERS] PostgreSQL JDBC and sub-select |
Date: | 2002-11-10 14:51:56 |
Message-ID: | 200211101451.56836.snpe@snpe.co.yu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
On Sunday 10 November 2002 05:27 am, Tom Lane wrote:
> snpe <snpe(at)snpe(dot)co(dot)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
From: | snpe <snpe(at)snpe(dot)co(dot)yu> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, pgsql-general(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: [HACKERS] PostgreSQL JDBC and sub-select |
Date: | 2002-11-10 14:52:58 |
Message-ID: | 200211101452.58950.snpe@snpe.co.yu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
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
From: | snpe <snpe(at)snpe(dot)co(dot)yu> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | SQL syntax (column alias) |
Date: | 2002-11-10 15:41:54 |
Message-ID: | 200211101541.55030.snpe@snpe.co.yu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
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
From: | Rod Taylor <rbt(at)rbt(dot)ca> |
---|---|
To: | snpe <snpe(at)snpe(dot)co(dot)yu> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: SQL syntax (column alias) |
Date: | 2002-11-10 16:15:59 |
Message-ID: | 1036944960.47533.7.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
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
From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | snpe <snpe(at)snpe(dot)co(dot)yu> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: SQL syntax (column alias) |
Date: | 2002-11-10 16:38:37 |
Message-ID: | 3DCE8B8D.4080509@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Joe Conway <mail(at)joeconway(dot)com> |
Cc: | snpe <snpe(at)snpe(dot)co(dot)yu>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: SQL syntax (column alias) |
Date: | 2002-11-10 17:44:45 |
Message-ID: | 20681.1036950285@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
Joe Conway <mail(at)joeconway(dot)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
From: | snpe <snpe(at)snpe(dot)co(dot)yu> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, pgsql-general(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: [JDBC] [HACKERS] PostgreSQL JDBC and sub-select |
Date: | 2002-11-14 13:31:11 |
Message-ID: | 200211141331.11894.snpe@snpe.co.yu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
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
From: | "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | "snpe" <snpe(at)snpe(dot)co(dot)yu>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [JDBC] PostgreSQL JDBC and sub-select |
Date: | 2002-11-15 03:13:58 |
Message-ID: | GNELIHDDFBOCMGBFGEFOEEJICEAA.chriskl@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-hackers pgsql-jdbc |
> 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