Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause

On Tuesday, March 22, 2022, PALAYRET Jacques <jacques(dot)palayret(at)meteo(dot)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.