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

[PALAYRET Jacques <jacques.palayret@xxxxxxxx>]

Constrained and forced, I am obliged to accept the behavior which is a means of using useful functionalities (standard SQL a priori). It's instructive for me.

I don't know if it's the same behavior (more or less strict SQL standard) in other DBMS.
For example, in Oracle it is not possible to have a SELECT statement without a FROM clause (using DUAL table), so maybe " ambiguous " columns are not handled in the same way.

Anyway, thank you very much for the explanation.
Regards

---

De: "David G. Johnston" <david.g.johnston@xxxxxxxxx>
À: "PALAYRET Jacques" <jacques.palayret@xxxxxxxx>
Cc: "PostgreSQL mailing lists" <pgsql-general@xxxxxxxxxxxxxx>
Envoyé: Mardi 22 Mars 2022 14:04:32
Objet: Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause

On Tuesday, March 22, 2022, PALAYRET Jacques <jacques.palayret@xxxxxxxx> 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.