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