pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL query syntax question
4+ messages / 3 participants
[nested] [flat]
* SQL query syntax question
@ 2016-04-04 07:19 Alexander Levsha <outcastatsabre@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Alexander Levsha @ 2016-04-04 07:19 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks for your help. Alexander Levsha
^ permalink raw reply [nested|flat] 4+ messages in thread
* SQL query syntax question
@ 2016-04-04 07:23 Alexander Levsha <levsha.alexander@gmail.com>
2016-04-04 07:54 ` Re: SQL query syntax question David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Alexander Levsha @ 2016-04-04 07:23 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks for your help. Alexander Levsha
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL query syntax question
2016-04-04 07:23 SQL query syntax question Alexander Levsha <levsha.alexander@gmail.com>
@ 2016-04-04 07:54 ` David G. Johnston <david.g.johnston@gmail.com>
2016-04-04 08:12 ` Re: SQL query syntax question Alexander Levsha <levsha.alexander@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2016-04-04 07:54 UTC (permalink / raw)
To: Alexander Levsha <levsha.alexander@gmail.com>; +Cc: pgsql-sql
On Monday, April 4, 2016, Alexander Levsha <levsha.alexander@gmail.com>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL query syntax question
2016-04-04 07:23 SQL query syntax question Alexander Levsha <levsha.alexander@gmail.com>
2016-04-04 07:54 ` Re: SQL query syntax question David G. Johnston <david.g.johnston@gmail.com>
@ 2016-04-04 08:12 ` Alexander Levsha <levsha.alexander@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Alexander Levsha @ 2016-04-04 08:12 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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 <levsha.alexander@gmail.com>
> 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.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-04-04 08:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-04-04 07:19 SQL query syntax question Alexander Levsha <outcastatsabre@gmail.com>
2016-04-04 07:23 SQL query syntax question Alexander Levsha <levsha.alexander@gmail.com>
2016-04-04 07:54 ` David G. Johnston <david.g.johnston@gmail.com>
2016-04-04 08:12 ` Alexander Levsha <levsha.alexander@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox