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

Thanks a lot.
Visibly, you are right.
It's a correlated statement, OK, right.

But in the subquery :
(
SELECT x.elev
FROM elev_Tlse_Blagnac AS x
WHERE w.id BETWEEN 31000000 and 31999999
)
the WHERE clause (= w.id BETWEEN 31000000 and 31999999) is for the SELECT x.elev FROM elev_Tlse_Blagnac
which is the value 151 (one line, one value), correlation or not.
So, for me, it should NOT be a WHERE clause (a condition) for the MAIN statement.

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.

From your point of view, it is the same statement than :
-----
WITH elev_Tlse_Blagnac AS (
SELECT elev FROM weather_stations WHERE id=31069001
)
SELECT w.id, w.name, w.elev
FROM weather_stations AS w
WHERE elev > (SELECT x.elev
FROM elev_Tlse_Blagnac AS x)
AND w.id BETWEEN 31000000 and 31999999;
id | name | elev
----------+----------------------+------
31006001 | ALBIAC AGGLOMERATION | 289
31010001 | LUCHON-ANTIGNAC | 599
(2 lignes)
-----

For me, it's weird, not logical.

Thanks again.
Regards

De: "Torsten Förtsch" <tfoertsch123(at)gmail(dot)com>
À: "PALAYRET Jacques" <jacques(dot)palayret(at)meteo(dot)fr>
Cc: "PostgreSQL mailing lists" <pgsql-general(at)postgresql(dot)org>
Envoyé: Mardi 22 Mars 2022 11:16:19
Objet: Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause

This is what happens:
WITH elev_Tlse_Blagnac AS (
SELECT elev FROM weather_stations WHERE id=31069001
)
SELECT [ http://w.id/ | w.id ] , [ http://w.name/ | w.name ] , w.elev
FROM weather_stations AS w
WHERE elev > (SELECT x.elev
FROM elev_Tlse_Blagnac AS x
WHERE [ http://w.id/ | w.id ] BETWEEN 31000000 and 31999999);
id | name | elev
----------+----------------------+------
31006001 | ALBIAC AGGLOMERATION | 289
31010001 | LUCHON-ANTIGNAC | 599
(2 rows)

Note the use of aliases, w and x. You are using a correlated subquery.

On Tue, Mar 22, 2022 at 10:46 AM PALAYRET Jacques < [ mailto:jacques(dot)palayret(at)meteo(dot)fr | jacques(dot)palayret(at)meteo(dot)fr ] > wrote:

Hello,

# Let's consider a table defined as follows :
CREATE TABLE weather_stations(
id integer,
name varchar(30),
elev integer
) ;

# After loading, the content :
id | name | elev
----------+----------------------+------
31069001 | TOULOUSE-BLAGNAC | 151
31006001 | ALBIAC AGGLOMERATION | 289
31010001 | LUCHON-ANTIGNAC | 599
50003001 | AGON-COUTAINVILLE | 2
50195001 | GATHEMO | 330
(5 lignes)

### With CTE :
# I'm suprised by the following result, the behavior of PostgreSQL ; is that a bug ? :
= Statement 1 : =
WITH elev_Tlse_Blagnac AS (
SELECT elev FROM weather_stations WHERE id=31069001
)
SELECT id, name, elev FROM weather_stations
WHERE elev > (
SELECT elev FROM elev_Tlse_Blagnac WHERE id BETWEEN 31000000 and 31999999
) ;
id | name | elev
----------+----------------------+------
31006001 | ALBIAC AGGLOMERATION | 289
31010001 | LUCHON-ANTIGNAC | 599
(2 lignes)
# According to me, the previous result is an error, because the parentheses are not taken into account.
The column id is not part of elev_Tlse_Blagnac.

# The same result as following, which is of course OK :
= Statement 2 : =
WITH elev_Tlse_Blagnac AS (
SELECT elev FROM weather_stations WHERE id=31069001
)
SELECT id, name, elev FROM weather_stations
WHERE elev > (
SELECT elev FROM elev_Tlse_Blagnac
)
AND id BETWEEN 31000000 and 31999999
;
id | name | elev
----------+----------------------+------
31006001 | ALBIAC AGGLOMERATION | 289
31010001 | LUCHON-ANTIGNAC | 599
(2 lignes)

### Same weird behavior with subquery in FROM clause :
# NOT OK (according to me), because the parentheses are not taken into account :
= Statement 3 : =
SELECT id, name, elev FROM weather_stations
WHERE elev > (
SELECT elev FROM (SELECT elev FROM weather_stations WHERE id=31069001) elev_Tlse_Blagnac WHERE id BETWEEN 31000000 and 31999999
) ;
id | name | elev
----------+----------------------+------
31006001 | ALBIAC AGGLOMERATION | 289
31010001 | LUCHON-ANTIGNAC | 599
(2 lignes)

# OK, the parentheses are taken into account because there is no confusion with the column id (elev_Tlse_Blagnac has a column named id) :
= Statement 4 : =
SELECT id, name, elev FROM weather_stations WHERE elev > (
SELECT elev FROM (SELECT * FROM weather_stations WHERE id=31069001) elev_Tlse_Blagnac WHERE id BETWEEN 31000000 and 31999999
) ;
id | name | elev
----------+----------------------+------
31006001 | ALBIAC AGGLOMERATION | 289
31010001 | LUCHON-ANTIGNAC | 599
50195001 | GATHEMO | 330
(3 lignes)

# OK (of course) :
= Statement 5 : =
SELECT id, name, elev FROM weather_stations WHERE elev > (
SELECT elev FROM (SELECT * FROM weather_stations WHERE id=31069001) elev_Tlse_Blagnac
)
AND id BETWEEN 31000000 and 31999999
;
id | name | elev
----------+----------------------+------
31006001 | ALBIAC AGGLOMERATION | 289
31010001 | LUCHON-ANTIGNAC | 599
(2 lignes)

Is that a PostgreSQL bug or not, statement 1 or statement 3 (yes according to me) ?

Regards
----- Météo-France -----
PALAYRET JACQUES
DCSC/GDC
[ mailto:jacques(dot)palayret(at)meteo(dot)fr | jacques(dot)palayret(at)meteo(dot)fr ]
Fixe : +33 561078319