Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1amzcZ-0003DS-7r for pgsql-sql@arkaria.postgresql.org; Mon, 04 Apr 2016 08:12:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1amzcY-00069V-9o for pgsql-sql@arkaria.postgresql.org; Mon, 04 Apr 2016 08:12:26 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1amzcX-00069J-G4 for pgsql-sql@postgresql.org; Mon, 04 Apr 2016 08:12:25 +0000 Received: from mail-ig0-x229.google.com ([2607:f8b0:4001:c05::229]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1amzcQ-0006d0-Iy for pgsql-sql@postgresql.org; Mon, 04 Apr 2016 08:12:24 +0000 Received: by mail-ig0-x229.google.com with SMTP id f1so61363520igr.1 for ; Mon, 04 Apr 2016 01:12:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc; bh=0KzKzxqhyO9tPTp4I3OAQqQjrTbciEJWN7HS9mnEDGM=; b=R8bfvem0R5BIqwzgIPVszIjWF4LASUU9v6iYhGIeAh183ukxIPWh32NO2KOHfrIcou 89RhRK1zosFBgbgM1WEBWMscRAsIu/gtrn7WkgW5+QBvLWyLyEAHgTeWHeQd16SPXuDU irIKymYI6S0/jr3CkElmAICObtt3MRbFthhgK2HQPJBSBiFf3PtvshtVy3KTJ16qhJAZ cTh3FgOnZ8v/tc7VWLvkkLXqCAXLO9OvgqNmO/A+LwrjjLm6JY9wBC3uPRmUvqVJfshm xRzrUYw2PT2nUa+OFGuZY7O9ylLcDcgf0SUQYZss0qVqxiUOLHH3aq1y4e+N7W34hWm4 4mgQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc; bh=0KzKzxqhyO9tPTp4I3OAQqQjrTbciEJWN7HS9mnEDGM=; b=aC0kaTPLNrQg/G7v1xzmnZi5WmhluKTtllOo+sfw/izhTxlqtCtk30Itp2u+uEVDGn gVLqAwDqhCDDcFegvr1LYZINUJpA4SqJwwFoR4TWms83Mja7Z0dMd2pp9VGBYPyNJFVg BCSDoqe7DMn2AKfYNnFCAosAU7WY7F/XpOO9bEuAwDnUs4UnIfn64aU5JMOCJdRIe0xC uyjYbAhWftqmYpk6s2YJgz8kFRhnbJLfZo8WAw2mboVHJMBYTthTUu3sEnQpn7lEDP4A o8Leo2rEcvWeM7p6ZNXzQpfheBmsc+o5t+XZubsST766xVspYCY3JZtWdIgAL9yuPypO Azew== X-Gm-Message-State: AD7BkJISSGuRMS+Wvpe8Jm7lcVWGK/i1pAPnW837dA2PwXt+fnWmODY+IILCuoEH0BYQtSSLQ6FqqzcaGZ6h4A== MIME-Version: 1.0 X-Received: by 10.50.138.233 with SMTP id qt9mr9776305igb.13.1459757537746; Mon, 04 Apr 2016 01:12:17 -0700 (PDT) Received: by 10.36.3.3 with HTTP; Mon, 4 Apr 2016 01:12:17 -0700 (PDT) In-Reply-To: References: Date: Mon, 4 Apr 2016 11:12:17 +0300 Message-ID: Subject: Re: SQL query syntax question From: Alexander Levsha To: "David G. Johnston" Cc: "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=089e013c6a987ef718052fa4496f X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --089e013c6a987ef718052fa4496f Content-Type: text/plain; charset=UTF-8 Ah i see, i does make sense when you talk about FROM having access to relations that have been "created" in some way. My current goal is to extract all (or as many as possible) external dependencies from an arbitrary query. I've created a parser grammar using ANTLR4 and now i need to analyse the generated syntax trees and that involves this kind of scope and alias tracking. Thanks. Alexander Levsha On Mon, Apr 4, 2016 at 10:54 AM, David G. Johnston < david.g.johnston@gmail.com> wrote: > On Monday, April 4, 2016, Alexander Levsha > wrote: > >> Hello all. >> I'm currently developing an SQL parser/analyzer for internal PostgreSQL >> tool we use at my workplace. >> Naturally, i use psql in conjunction with PostgreSQL documentation to >> develop and verify my work. >> >> Recently i've noticed the following oddity in query syntax/execution: >> >> select (select c1 from tt) from t1 tt; >> --ERROR: relation "tt" does not exist >> --LINE 1: select (select c1 from tt) from t1 tt; >> >> >> select (select tt.c1 ) from t1 tt; >> --c1 >> ------ >> --(0 rows) >> >> >> Why doesn't the first one work when the second one works fine? In other >> words, why do subquery's traget list and range table list use different >> namespaces to resolve table references/aliases? Is this an unintended >> behaviour, implementation detail or is there an actual reason for this? >> >> Cannot speak to restrictions imposed by the SQL standard but the > exhibited behavior seems logical given the nature of subqueries. > > The relation named in the FROM clause must exist in the database schema or > have been previously "created" using a CTE. Within a subquery all from > clause entries behave the same but you can reference a column by name (with > usually optional table and schema prefix) if it exists in the containing > scope. Thus there is no need to complicate things by requiring (or > allowing) the outer relation names to be targeted by a FROM clause in a > subquery. > > Correlated subqueries require target list resolution to behave in that > manner. It doesn't seem useful to complicate range table resolution > lacking a similar need. > > What is the motivation for the inquiry? > > David J. > --089e013c6a987ef718052fa4496f Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Ah i see, i does make sense whe= n you talk about FROM having access to relations that have been "creat= ed" in some way.

My current goal is to extract all (or as many as possible)= external dependencies from an arbitrary query.
I've created a parser grammar using ANTLR4 and now i need to analy= se the generated syntax trees and that involves this kind of scope and alia= s tracking.

Thanks.
Alexander Levsha

On Mon, Apr 4, 2016 at 1= 0:54 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, A= pril 4, 2016, Alexander Levsha <levsha.alexander@gmail.com> wrote:
= Hello all.
I'm currently developi= ng an SQL parser/analyzer for internal PostgreSQL tool we use at my workpla= ce.
Naturally, i use psql in conjuncti= on with PostgreSQL documentation to develop and verify my work.

Recentl= y i've noticed the following oddity in query syntax/execution:

selec= t (select c1 from tt) from t1 tt; --ERROR: relation "tt" does not exist --LINE 1: select (select c1 from tt) from t1 tt;

select (select tt.c1 ) from t=
1 tt;
--c1=20
------
--(0 rows)

Why doesn't the first one =
work when the second one works fine? In other words, why do subquery's =
traget list and range table list use different namespaces to resolve table =
references/aliases?
Is this an unintended behaviour, implementation detail or is there an actua=
l reason for this?

<=
/pre>
Cannot speak to restrictions impo= sed by the SQL standard but the exhibited behavior seems logical given the = nature of subqueries.

The relation named in the FR= OM clause must exist in the database schema or have been previously "c= reated" using a CTE.=C2=A0 Within a subquery all from clause entries b= ehave the same but you can reference a=C2=A0column=C2=A0by name (with usual= ly=C2=A0optional table and schema prefix) if it exists in the containing sc= ope.=C2=A0 Thus there is no need to complicate things by requiring (or allo= wing) the outer relation names to be targeted by a FROM clause in a subquer= y.

Correlated subqueries require target list resol= ution to behave in that manner.=C2=A0 It doesn't seem useful to complic= ate range table resolution lacking a similar need.

What= is the motivation for the inquiry?

David J.

--089e013c6a987ef718052fa4496f--