Re: [BUGS] No error when FROM is missing in subquery
On Mon, Dec 18, 2006 at 11:48 PM, in message
[EMAIL PROTECTED],
Tom Lane [EMAIL PROTECTED] wrote:
The reason there's no error thrown is that the reference to mov_id
in
the sub- SELECT is a perfectly legal outer reference to the mov_id
column
available from the upper SELECT. If the column truly did not exist
anywhere in the tables used in the query, it would have thrown an
error.
It's easy to see how it resolves the column references; but the syntax
is still not compliant with the SQL standard. The FROM clause is
required. We could call it a PostgreSQL extension, but I'm curious if
anyone actually finds it useful. So far it just seems to provide an
opportunity for error.
-Kevin
*scalar_subquery
: subquery
;
*subquery
: LEFT_PAREN query_expression RIGHT_PAREN
;
*query_expression
: non_join_query_expression
| joined_table
;
*non_join_query_expression
: non_join_query_term
| query_expression TK_union [ TK_all ]
[ corresponding_spec ] query_term
| query_expression TK_except [ TK_all ]
[ corresponding_spec ] query_term
;
*non_join_query_term
: non_join_query_primary
| query_term TK_intersect [ TK_all ]
[ corresponding_spec ] query_primary
;
*non_join_query_primary
: simple_table
| LEFT_PAREN non_join_query_expression RIGHT_PAREN
;
*simple_table
: query_specification
| table_value_constructor
| explicit_table
;
*query_specification
: TK_select [ set_quantifier ] select_list table_expression
;
*select_list
: ASTERISK
| select_sublist [ { COMMA select_sublist }... ]
;
*select_sublist
: derived_column
| qualifier PERIOD ASTERISK
;
*derived_column
: value_expression [ as_clause ]
;
*as_clause
: [ TK_as ] column_name
;
*table_expression
: from_clause
[ where_clause ]
[ group_by_clause ]
[ having_clause ]
;
*from_clause
: TK_from table_reference
[ { COMMA table_reference }... ]
;
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [BUGS] No error when FROM is missing in subquery
Kevin Grittner [EMAIL PROTECTED] writes: It's easy to see how it resolves the column references; but the syntax is still not compliant with the SQL standard. The FROM clause is required. We could call it a PostgreSQL extension, but I'm curious if anyone actually finds it useful. You've never done SELECT some_expression ? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] No error when FROM is missing in subquery
On Tue, Dec 19, 2006 at 9:23 AM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] writes: It's easy to see how it resolves the column references; but the syntax is still not compliant with the SQL standard. The FROM clause is required. We could call it a PostgreSQL extension, but I'm curious if anyone actually finds it useful. You've never done SELECT some_expression ? Not, as far as I can recall, in the ANSI SQL spec. Did I miss something? I'm having trouble seeing how it is a useful construct in the context of a scalar subquery. A non-standard extension should be useful in some way. -Kevin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] No error when FROM is missing in subquery
Kevin Grittner [EMAIL PROTECTED] writes: I'm having trouble seeing how it is a useful construct in the context of a scalar subquery. A non-standard extension should be useful in some way. There is 0 chance that we'd disallow it at the top level after allowing it all these years. And probably not even just top-level; consider select 1 union all select 2 union all select 3; which has been the recommended workaround up to 8.2 for our lack of multi-row VALUES lists. We will certainly break a lot of code if we disallow that. So now you have to make a case why we should make a non-orthogonal distinction between certain subqueries and other subqueries. As for potential usefulness, consider a set-returning function invoked in the targetlist: it makes perfect sense to do WHERE foo IN (SELECT mysrf(...)) and maybe even add an ORDER BY/LIMIT to that. Yeah, no doubt this is easy to change to putting the SRF in FROM, but you can bet there are Postgres applications out there today using it; we have never officially deprecated this way of using an SRF. I also recall having advised people in years past to use dummy (SELECT expr) sub-selects to work around planning issues. I don't recall at the moment whether any of those issues are still live, but again you can bet the construct is still in some applications' SQL. So basically we get to introduce some arbitrary non-orthogonality, plus break an unknown number of applications, in order to block off a type of user error that AFAIR has never been complained of before in all the years I've been working with Postgres. Sorry, no sale ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] No error when FROM is missing in subquery
On Tue, Dec 19, 2006 at 9:58 AM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] writes: I'm having trouble seeing how it is a useful construct in the context of a scalar subquery. A non- standard extension should be useful in some way. There is 0 chance that we'd disallow it at the top level after allowing it all these years. I wouldn't want to eliminate it there -- it is clearly a useful extension to the standard at the top level. And probably not even just top- level; consider select 1 union all select 2 union all select 3; which has been the recommended workaround up to 8.2 for our lack of multi- row VALUES lists. We will certainly break a lot of code if we disallow that. Point taken. So now you have to make a case why we should make a non- orthogonal distinction between certain subqueries and other subqueries. Well, I don't think of the terms for set operations as subqueries, and there are other differences already in what is allowed for a query term and a subquery. Arguably there is more risk of error of the type recently reported where you are in a scalar subquery context. As for potential usefulness, consider a set- returning function invoked in the targetlist: it makes perfect sense to do WHERE foo IN (SELECT mysrf(...)) and maybe even add an ORDER BY/LIMIT to that. That is sufficient to answer my concerns. I tend to operate from the context of the standard, because we have our own ANSI based parser which generates portable Java query classes. ORDER BY and LIMIT are not allowed in the subqueries in the standard but are obviously useful extensions. The missing FROM then adds value to the other extensions. Case closed. Thanks. By the way, when I read my previous message it struck me that it could be taken with a tone I didn't intend. That was the result of whipping it out quickly without taking sufficient time to review it. Sorry; no offense was intended. I'll try to avoid doing that again. -Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] No error when FROM is missing in subquery
Following query is considered as correct, no missing from error has been reported (so, entire table will be updated and on update triggers will be fired for every row): update item set obj_id = obj_id where obj_id in (select obj_id where item_point is null order by obj_modified limit 10) Is it a bug? If no, maybe to produce warning in such cases? -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] No error when FROM is missing in subquery
ok, sorry, I've realized that it's yet another example of outer reference, Tom will say read any SQL book again :-) http://archives.postgresql.org/pgsql-bugs/2006-12/msg00115.php On 12/19/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: Following query is considered as correct, no missing from error has been reported (so, entire table will be updated and on update triggers will be fired for every row): update item set obj_id = obj_id where obj_id in (select obj_id where item_point is null order by obj_modified limit 10) Is it a bug? If no, maybe to produce warning in such cases? -- Best regards, Nikolay -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] No error when FROM is missing in subquery
On 12/19/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: Following query is considered as correct, no missing from error has been reported (so, entire table will be updated and on update triggers will be fired for every row): update item set obj_id = obj_id where obj_id in (select obj_id where item_point is null order by obj_modified limit 10) Is it a bug? If no, maybe to produce warning in such cases? On 12/18/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: ok, sorry, I've realized that it's yet another example of outer reference, Tom will say read any SQL book again :-) http://archives.postgresql.org/pgsql-bugs/2006-12/msg00115.php not really... AFAIK, the FROM clause is mandatory per SQL... older releases of postgres fill the missing from clause if it was easy to determine, in recent releases it's mandatory unless you specify the opposite in postgresql.conf with the add_missing_from parameter -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] No error when FROM is missing in subquery
Is it a bug? If no, maybe to produce warning in such cases? oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... no clue which ones :-/ - thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] No error when FROM is missing in subquery
On 12/18/06, Thomas H. [EMAIL PROTECTED] wrote: Is it a bug? If no, maybe to produce warning in such cases? oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... and the UPDATE was? also the limit applies only to the subselect, it has nothing to do with the upper query so the upper query can return more than number of rows specified in the subselect... no clue which ones :-/ LIMIT is often meaningfull only in conjuction with ORDER BY -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] No error when FROM is missing in subquery
oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... and the UPDATE was? that was done by the application with the returned recordset. also the limit applies only to the subselect, it has nothing to do with the upper query so the upper query can return more than number of rows specified in the subselect... IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... LIMIT is often meaningfull only in conjuction with ORDER BY yep but not here. all i wanted to do is to get names from 2 movies and run an *observed* edit on them. what did pgsql actually do with that subquery? did it return all records for which mov_name match '%, %'? - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] No error when FROM is missing in subquery
On 12/18/06, Thomas H. [EMAIL PROTECTED] wrote: oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... and the UPDATE was? that was done by the application with the returned recordset. also the limit applies only to the subselect, it has nothing to do with the upper query so the upper query can return more than number of rows specified in the subselect... IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... have you tested the query in psql? what results do you get? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] No error when FROM is missing in subquery
SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... have you tested the query in psql? what results do you get? the data is damaged so the result isn't the same... regenearting it now from a backup. from first tests i would say it returned records with names that match the WHERE in the subselect. i guess what happened is: it took each record in movies.names, then run the subquery for that record which resulted in WHERE mov_id IN (mov_id) = true for records with a ', ' in the name and WHERE mov_id IN () = false for all others. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] No error when FROM is missing in subquery
Thomas H. [EMAIL PROTECTED] writes: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 It didn't ignore anything. Each execution of the sub-select returned 1 row, containing the current mov_id from the outer query. So basically this would've selected everything passing the LIKE condition. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] No error when FROM is missing in subquery
Also check that the mov_id column exists in the table/view that you are running the SELECT DISTINCT against. Pgsql does not throw an error (at least prior to 8.2) if the column referenced by the select statement for the IN clause does not exist. It will run only SELECT * FROM movies.names in this case. Mike On Tue, 2006-12-19 at 06:01 +0100, Thomas H. wrote: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... have you tested the query in psql? what results do you get? the data is damaged so the result isn't the same... regenearting it now from a backup. from first tests i would say it returned records with names that match the WHERE in the subselect. i guess what happened is: it took each record in movies.names, then run the subquery for that record which resulted in WHERE mov_id IN (mov_id) = true for records with a ', ' in the name and WHERE mov_id IN () = false for all others. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] No error when FROM is missing in subquery
mike [EMAIL PROTECTED] writes: Pgsql does not throw an error (at least prior to 8.2) if the column referenced by the select statement for the IN clause does not exist. My, there's a lot of misinformation in this thread. The reason there's no error thrown is that the reference to mov_id in the sub-SELECT is a perfectly legal outer reference to the mov_id column available from the upper SELECT. If the column truly did not exist anywhere in the tables used in the query, it would have thrown an error. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] No error when FROM is missing in subquery
You are right as usual My apologies yet again. I have wrongly assumed that the lower statement would run first since it is enclosed in parenthesis. On Tue, 2006-12-19 at 00:48 -0500, Tom Lane wrote: mike [EMAIL PROTECTED] writes: Pgsql does not throw an error (at least prior to 8.2) if the column referenced by the select statement for the IN clause does not exist. My, there's a lot of misinformation in this thread. The reason there's no error thrown is that the reference to mov_id in the sub-SELECT is a perfectly legal outer reference to the mov_id column available from the upper SELECT. If the column truly did not exist anywhere in the tables used in the query, it would have thrown an error. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
