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

Hi,

Le mar. 22 mars 2022 à 10:46, PALAYRET Jacques <jacques(dot)palayret(at)meteo(dot)fr>
a écrit :

> 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.
>
>
Not a bug, just following the SQL standard as far as I remember.

>
> # 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) ?
>

Not a bug, just following the SQL standard as far as I remember.

--
Guillaume.
http://www.dalibo.com