Hi, Le mar. 22 mars 2022 à 10:46, PALAYRET Jacques <jacques.palay...@meteo.fr> a écrit :
> 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. > > Not a bug, just following the SQL standard as far as I remember. > > # 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) ? > Not a bug, just following the SQL standard as far as I remember. -- Guillaume. http://www.dalibo.com
