From: | PALAYRET Jacques <jacques(dot)palayret(at)meteo(dot)fr> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause |
Date: | 2022-03-22 09:45:55 |
Message-ID: | 1370619684.47054020.1647942355596.JavaMail.zimbra@meteo.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)palayret(at)meteo(dot)fr
Fixe : +33 561078319
From | Date | Subject | |
---|---|---|---|
Next Message | Torsten Förtsch | 2022-03-22 10:16:19 | Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause |
Previous Message | McDermott, Becky | 2022-03-21 23:16:44 | RE: [EXTERNAL] Re: Can you install/run postgresql on a FIPS enabled host? |