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

[PALAYRET Jacques]

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