Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause - Mailing list pgsql-general

From David G. Johnston
Subject Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause
Date
Msg-id CAKFQuwZZHe4sjbUewdBnHcx+X2bYcLWQ_ws8GL1fM3+RnL+0AA@mail.gmail.com
Whole thread Raw
In response to Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause  (PALAYRET Jacques <jacques.palayret@meteo.fr>)
Responses Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause
List pgsql-general
On Tuesday, March 22, 2022, PALAYRET Jacques <jacques.palayret@meteo.fr> wrote:

According to me, there is only one condition in the main statement (SELECT w.id, w.name, w.elev FROM weather_stations ...)
and it is : elev > 151 (correlation ou not correlation).
In others words : for each line of table weather_stations), the only condition is : is the elev superior than the elev returned by the subquery, 151 ?

Visibly, the correlated statement adds one condition (w.id BETWEEN 31000000 and 31999999) in the main statement, but it's not logical for me, because of the parentheses.

I agree your example query is written poorly and thus is confusing.  But it is not possible for the system to distinguish a poorly written query from a goodly written one that uses the same functionality.  As the functionality is useful, and the parentheses simply don’t isolate the subquery in the manner you ascribe to them, you’ll just need to adapt to reality.  Its doesn’t have to seem logical to you, but this is how it is defined to work and thus the observed behavior is not a bug.

David J.

pgsql-general by date:

Previous
From: PALAYRET Jacques
Date:
Subject: Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause
Next
From: PALAYRET Jacques
Date:
Subject: Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause