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 w.id, w.name, w.elev
FROM weather_stations AS w
WHERE elev > (SELECT x.elev
FROM elev_Tlse_Blagnac AS x
WHERE 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 <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
> jacques(dot)palayret(at)meteo(dot)fr
> Fixe : +33 561078319
>