Обсуждение: BUG #2224: unlogical syntax error

The following bug has been logged online:

Bug reference:      2224
Logged by:          Hervé Hénoch
Email address:      h.henoch@isc84.org
PostgreSQL version: 8.1.2
Operating system:   Linux FC4
Description:        unlogical syntax error
Details:

I do this request :

select id_caisse as caisse from adm_pat where nip = '20020523' ;

Under PgAdmin this request work fine but via JDBC I have the following
messages server side :

CETLOG:  statement: PREPARE <unnamed> AS SELECT id_caisse AS caisse FROM
adm_pat WHERE nip = '20020523'

CETLOG:  statement: EXECUTE <unnamed>  [PREPARE:  SELECT id_caisse AS caisse
FROM adm_pat WHERE nip = '20020523']

CETLOG:  statement: PREPARE <unnamed> AS SELECT adm_pat."nip", id_caisse
caisse FROM adm_pat  WHERE nip = '20020523'

CETERROR:  syntax error at or near "caisse" at character 33

CETLOG:  statement: PREPARE <unnamed> AS SELECT id_caisse AS caisse FROM
adm_pat WHERE nip = '20020523'

CETLOG:  statement: EXECUTE <unnamed>  [PREPARE:  SELECT id_caisse AS caisse
FROM adm_pat WHERE nip = '20020523']


The table adm_pat does not have another column named caisse. So what is the
problem (if problem).

Thanks a lot.

"Hervé Hénoch" <h.henoch@isc84.org> writes:
> CETLOG:  statement: PREPARE <unnamed> AS SELECT adm_pat."nip", id_caisse
> caisse FROM adm_pat  WHERE nip = '20020523'

> CETERROR:  syntax error at or near "caisse" at character 33

"AS" before a column alias is not optional in Postgres.

            regards, tom lane

On Mon, Jan 30, 2006 at 10:47:10AM +0000, Herv Hnoch wrote:
> CETLOG:  statement: PREPARE <unnamed> AS SELECT adm_pat."nip", id_caisse
> caisse FROM adm_pat  WHERE nip = '20020523'
>
> CETERROR:  syntax error at or near "caisse" at character 33

Are you writing this query or is JDBC generating it?  The AS keyword
is required for column aliases; its absence is causing the syntax
error.

test=> CREATE TABLE foo (abc text);
test=> SELECT abc AS xyz FROM foo;
 xyz
-----
(0 rows)

test=> SELECT abc xyz FROM foo;
ERROR:  syntax error at or near "xyz" at character 12
LINE 1: SELECT abc xyz FROM foo;
                   ^

--
Michael Fuhr

[Please copy the mailing list on replies.]

On Tue, Jan 31, 2006 at 08:53:44AM +0100, Hnoch Herv wrote:
> Thank for you response .... I have never written this query : I think it
> is JDBC that have generated it ... My query (written by me) is :
>
>    select id_caisse as caisse from FROM adm_pat WHERE nip = '20020523'
>
> I know I have to use "as" statement ... I'd never disturb you for a
> simple syntax error ...

The above query does have a syntax error ("from FROM"), although
the query in your original report looked correct.

> Jdbc version :  jdbc3 8.1.404

I downloaded postgresql-8.1-404.jdbc3.jar onto a Solaris 9 box
running PostgreSQL 8.1.2 and wrote a test program to run your
original query:

select id_caisse as caisse from adm_pat where nip = '20020523'

The query ran successfully and logged the following:

LOG:  statement: PREPARE <unnamed> AS select id_caisse as caisse from adm_pat where nip = '20020523'
LOG:  statement: <BIND>
LOG:  statement: EXECUTE <unnamed>  [PREPARE:  select id_caisse as caisse from adm_pat where nip = '20020523']

> The very stange think is, if I write :
>
>    select nip, id_caisse as caisse from FROM adm_pat WHERE nip = '20020523'
>
> There is no syntax error !!!!!

There should be a syntax error due to "from FROM"; please post the
actual queries you're running.  It might also be helpful if you
could post a simple but complete program so we can see everything
you're doing.  You might also try asking in the pgsql-jdbc list to
see if anybody there has heard of this problem.

--
Michael Fuhr