Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause - Mailing list pgsql-general

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.palayret@meteo.fr
Fixe : +33 561078319