Thanks a lot. Visibly, you are right. It's a correlated statement, OK, right.
But in the subquery : ( SELECT x.elev FROM elev_Tlse_Blagnac AS x WHERE w.id BETWEEN 31000000 and 31999999 ) the WHERE clause (= w.id BETWEEN 31000000 and 31999999) is for the SELECT x.elev FROM elev_Tlse_Blagnac which is the value 151 (one line, one value), correlation or not. So, for me, it should NOT be a WHERE clause (a condition) for the MAIN statement. According to me, there is only one condition in the main statement (SELECT w.id, w.name, w.elev FROM weather_stations ...) and it is : elev > 151 (correlation ou not correlation). In others words : for each line of table weather_stations), the only condition is : is the elev superior than the elev returned by the subquery, 151 ? Visibly, the correlated statement adds one condition (w.id BETWEEN 31000000 and 31999999) in the main statement, but it's not logical for me, because of the parentheses. >From your point of view, it is the same statement than : ----- WITH elev_Tlse_Blagnac AS ( SELECT elev FROM weather_stations WHERE id=31069001 ) SELECT w.id, w.name, w.elev FROM weather_stations AS w WHERE elev > (SELECT x.elev FROM elev_Tlse_Blagnac AS x) AND w.id BETWEEN 31000000 and 31999999; id | name | elev ----------+----------------------+------ 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 lignes) ----- For me, it's weird, not logical. Thanks again. Regards De: "Torsten Förtsch" <tfoertsch...@gmail.com> À: "PALAYRET Jacques" <jacques.palay...@meteo.fr> Cc: "PostgreSQL mailing lists" <pgsql-gene...@postgresql.org> Envoyé: Mardi 22 Mars 2022 11:16:19 Objet: Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause This is what happens: WITH elev_Tlse_Blagnac AS ( SELECT elev FROM weather_stations WHERE id=31069001 ) SELECT [ http://w.id/ | w.id ] , [ http://w.name/ | w.name ] , w.elev FROM weather_stations AS w WHERE elev > (SELECT x.elev FROM elev_Tlse_Blagnac AS x WHERE [ http://w.id/ | w.id ] BETWEEN 31000000 and 31999999); id | name | elev ----------+----------------------+------ 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 rows) Note the use of aliases, w and x. You are using a correlated subquery. On Tue, Mar 22, 2022 at 10:46 AM PALAYRET Jacques < [ mailto:jacques.palay...@meteo.fr | jacques.palay...@meteo.fr ] > wrote: 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 [ mailto:jacques.palay...@meteo.fr | jacques.palay...@meteo.fr ] Fixe : +33 561078319
