pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedParentheses in FROM clause and evaluation order.
6+ messages / 4 participants
[nested] [flat]
* Parentheses in FROM clause and evaluation order.
@ 2005-08-11 00:42 dario.mx@gmail.com
0 siblings, 1 reply; 6+ messages in thread
From: dario.mx@gmail.com @ 2005-08-11 00:42 UTC (permalink / raw)
To: pgsql-sql
Hi,
I thought that the parenthesis in the table expression
(FROM clause), could be used to indicate the desired
evaluation order. But, I tried with a couple of samples
and the explain command returned me the same result; no matter
what parentheses association I used. I am using only INNER JOINs.
In fact, I thought that the whole table expression was gonna be
evaluated before the WHERE filter. Does the stantard says something
about this evaluation order when the parentheses are present?
Does PostgreSQL implements this behavior?
Thanks in advance.
salu2
dario estepario ...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Parentheses in FROM clause and evaluation order.
@ 2005-08-15 14:02 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: dario.mx@gmail.com
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2005-08-15 14:02 UTC (permalink / raw)
To: dario.mx@gmail.com; +Cc: pgsql-sql
On Wed, 10 Aug 2005 dario.mx@gmail.com wrote:
> I thought that the parenthesis in the table expression
> (FROM clause), could be used to indicate the desired
> evaluation order. But, I tried with a couple of samples
> and the explain command returned me the same result; no matter
> what parentheses association I used. I am using only INNER JOINs.
>
> In fact, I thought that the whole table expression was gonna be
> evaluated before the WHERE filter. Does the stantard says something
> about this evaluation order when the parentheses are present?
> Does PostgreSQL implements this behavior?
AFAIK we only try to provide final results that are equivalent to
following the steps in order, so it'll reorder joins or push clauses
around as long as it thinks the semantics of the query won't change. For
example, actually doing unconstrainted joins before where clauses is a
very bad plan if you've got a FROM table1, table2, table3 style query. If
you're seeing a place where the reorder affects the query results as
opposed to the query plan, that's probably a bug, can you give more
information?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Parentheses in FROM clause and evaluation order.
@ 2005-08-15 16:34 Dario Bahena Tapia <dario.mx@gmail.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dario Bahena Tapia @ 2005-08-15 16:34 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
Hi,
The final result seems to be the same, I just was curious about the
standard behavior. Does the SQl says something about this execution
order?
Thanks for your response.
salu2
dario estepario ...
2005/8/15, Stephan Szabo <sszabo@megazone.bigpanda.com>:
> On Wed, 10 Aug 2005 dario.mx@gmail.com wrote:
>
> > I thought that the parenthesis in the table expression
> > (FROM clause), could be used to indicate the desired
> > evaluation order. But, I tried with a couple of samples
> > and the explain command returned me the same result; no matter
> > what parentheses association I used. I am using only INNER JOINs.
> >
> > In fact, I thought that the whole table expression was gonna be
> > evaluated before the WHERE filter. Does the stantard says something
> > about this evaluation order when the parentheses are present?
> > Does PostgreSQL implements this behavior?
>
> AFAIK we only try to provide final results that are equivalent to
> following the steps in order, so it'll reorder joins or push clauses
> around as long as it thinks the semantics of the query won't change. For
> example, actually doing unconstrainted joins before where clauses is a
> very bad plan if you've got a FROM table1, table2, table3 style query. If
> you're seeing a place where the reorder affects the query results as
> opposed to the query plan, that's probably a bug, can you give more
> information?
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Parentheses in FROM clause and evaluation order.
@ 2005-08-16 01:48 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Dario Bahena Tapia <dario.mx@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2005-08-16 01:48 UTC (permalink / raw)
To: Dario Bahena Tapia <dario.mx@gmail.com>; +Cc: pgsql-sql
On Mon, 15 Aug 2005, Dario Bahena Tapia wrote:
> The final result seems to be the same, I just was curious about the
> standard behavior. Does the SQl says something about this execution
> order?
I believe SQL defines the order to pay attention to parens, so A join (B
join C) style clauses result in a "table" being derived from B join C and
another from A joined with that table.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Parentheses in FROM clause and evaluation order.
@ 2005-08-16 02:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-08-16 02:02 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Dario Bahena Tapia <dario.mx@gmail.com>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Mon, 15 Aug 2005, Dario Bahena Tapia wrote:
>> The final result seems to be the same, I just was curious about the
>> standard behavior. Does the SQl says something about this execution
>> order?
> I believe SQL defines the order to pay attention to parens, so A join (B
> join C) style clauses result in a "table" being derived from B join C and
> another from A joined with that table.
SQL only constrains the results, though. It does not forbid the
implementation from doing the work in whatever way seems best to it,
so long as the results are the same (and "same" does not consider
row ordering).
For example, SQL92 3.3.4.4 says
A conforming implementation is not required to perform the exact
sequence of actions defined in the General Rules, but shall achieve
the same effect on SQL-data and schemas as that sequence.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Parentheses in FROM clause and evaluation order.
@ 2005-08-16 02:20 Dario Bahena Tapia <dario.mx@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Dario Bahena Tapia @ 2005-08-16 02:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
Ok, thanks for the responses guys.
Then, in the case where the final result is the same, could we think
the parentheses in the FROM clause, as a tool to clarify the query to
the user? Since in the end, this order could be changed by the
implementation for performance reasons.
salu2
dario estepario ...
2005/8/15, Tom Lane <tgl@sss.pgh.pa.us>:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > On Mon, 15 Aug 2005, Dario Bahena Tapia wrote:
> >> The final result seems to be the same, I just was curious about the
> >> standard behavior. Does the SQl says something about this execution
> >> order?
>
> > I believe SQL defines the order to pay attention to parens, so A join (B
> > join C) style clauses result in a "table" being derived from B join C and
> > another from A joined with that table.
>
> SQL only constrains the results, though. It does not forbid the
> implementation from doing the work in whatever way seems best to it,
> so long as the results are the same (and "same" does not consider
> row ordering).
>
> For example, SQL92 3.3.4.4 says
>
> A conforming implementation is not required to perform the exact
> sequence of actions defined in the General Rules, but shall achieve
> the same effect on SQL-data and schemas as that sequence.
>
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-08-16 02:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-11 00:42 Parentheses in FROM clause and evaluation order. dario.mx@gmail.com
2005-08-15 14:02 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-08-15 16:34 ` Dario Bahena Tapia <dario.mx@gmail.com>
2005-08-16 01:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-08-16 02:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-16 02:20 ` Dario Bahena Tapia <dario.mx@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