Обсуждение: How to find LIMIT in SQL standard
I have to file a bug about the implementation of some delivery report
database calls in the Kannel (open source WAP/SMS) server code.
Essentially the call (as defined below) asks for an update and adds a
LIMIT parameter on the end of the UPDATE. (eg update where x=1 limit 1).
Postgres doesn't like this and I assume it isn't SQL standards
compliant and need to refer to this in my bug report.
I've downloaded the sql document archives from
postgresql.org/postgresql/doc/sql but it isn't clear to me how to
discern what is legal in an UPDATE statement.
Help much appreciated!
Rory
static const char* sdb_get_limit_str()
{
switch (sdb_conn_type) {
case SDB_ORACLE:
return "AND ROWNUM < 2";
case SDB_OTHER:
default:
return "LIMIT 1";
}
...
sql = octstr_format("UPDATE %s SET %s=%d WHERE %s='%s' AND %s='%s' %s",
octstr_get_cstr(fields->table),
octstr_get_cstr(fields->field_status), status,
octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts), sdb_get_limit_str());
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
On Mon, 22 Sep 2003, Rory Campbell-Lange wrote:
> I have to file a bug about the implementation of some delivery report
> database calls in the Kannel (open source WAP/SMS) server code.
>
> Essentially the call (as defined below) asks for an update and adds a
> LIMIT parameter on the end of the UPDATE. (eg update where x=1 limit 1).
> Postgres doesn't like this and I assume it isn't SQL standards
> compliant and need to refer to this in my bug report.
Well, for SQL92, it looks like the correct section to start in is
13.10 (update statement: searched) which looks like:
UPDATE <table name>
SET <set clause list>
WHERE <search condition>
The last of those is the interesting one which is 8.12 (search condition)
<search condition> ::=
<boolean term>
| <search condition> OR <boolean term>
<boolean term> ::=
<boolean factor>
| <boolean term> AND <boolean factor>
<boolean factor> ::=
[ NOT ] <boolean test>
<boolean test> ::=
<boolean primary> [ IS [ NOT ] <truth value> ]
<truth value> ::=
TRUE
| FALSE
| UNKNOWN
<boolean primary> ::=
<predicate>
| <left paren> <search condition> <right paren>
Then 8.1 (predicate)
<predicate> ::=
<comparison predicate>
| <between predicate>
| <in predicate>
| <like predicate>
| <null predicate>
| <quantified comparison predicate>
| <exists predicate>
| <unique predicate>
| <match predicate>
| <overlaps predicate>
Also, since tables are effectively unordered, unless the other
where conditions are guaranteed to get a single row anyway which
row is modified is fairly indeterminate; this is only interesting
because it means that you don't necessarily get the same row
as a previous select (if any) would get. That's not always important,
but since update also has no way to order the rows that I know of,
if it were important you couldn't really get around it.
Rory Campbell-Lange wrote: > Essentially the call (as defined below) asks for an update and adds a > LIMIT parameter on the end of the UPDATE. (eg update where x=1 limit 1). > Postgres doesn't like this and I assume it isn't SQL standards > compliant and need to refer to this in my bug report. As far as I know you can not specify a limit for update in Postgres, at least not in that way. if you want to do UPDATE foo SET a='bar' where b LIMIT 1; this is possible in Postgres doing: UPDATE foo SET a = 'bar WHERE foo.oid IN ( SELECT f.oid FROM foo f WHERE b LIMIT 1 ); This fail if the table are created without OID. Regards Gaetano Mendola
Rory Campbell-Lange writes: > I've downloaded the sql document archives from > postgresql.org/postgresql/doc/sql but it isn't clear to me how to > discern what is legal in an UPDATE statement. Certainly LIMIT is not. Although LIMIT is a key word in the SQL standard, it isn't used for anything, so you cannot use it in portable applications. -- Peter Eisentraut peter_e@gmx.net
In an attempt to throw the authorities off his trail, rory@campbell-lange.net (Rory Campbell-Lange) transmitted: > Essentially the call (as defined below) asks for an update and adds > a LIMIT parameter on the end of the UPDATE. (eg update where x=1 > limit 1). Postgres doesn't like this and I assume it isn't SQL > standards compliant and need to refer to this in my bug report. No, it appears to be your understanding of the SQL standards may be a little bit deficient. LIMIT is a reserved word in SQL, but its use has not been standardized. If you use LIMIT, then your query isn't compliant with the standards, and the bug isn't in PostgreSQL, but rather is in your code. And what you are trying to do doesn't seem to make terribly much sense. It sounds as though you're happy updating any random record so long as it resembles the ones you think you might be updating. Perhaps you should use, as search criteria, elements in a unique key, so that you can be assured that the row will be unique. -- If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me http://www.ntlug.org/~cbbrowne/x.html Rules of the Evil Overlord #90. "I will not design my Main Control Room so that every workstation is facing away from the door." <http://www.eviloverlord.com/>
Thanks for the reply, Stephan. Sorry about the late reply. Your dissection of the standard (and thanks for the translation, by the way!) is a clear reason to use unique row ids. Unfortunately the project I am reporting bugs on does not do this at present. Many kind regards, Rory On 22/09/03, Stephan Szabo (sszabo@megazone.bigpanda.com) wrote: > On Mon, 22 Sep 2003, Rory Campbell-Lange wrote: > > > I have to file a bug about the implementation of some delivery report > > database calls in the Kannel (open source WAP/SMS) server code. > > > > Essentially the call (as defined below) asks for an update and adds a > > LIMIT parameter on the end of the UPDATE. (eg update where x=1 limit 1). > > Postgres doesn't like this and I assume it isn't SQL standards > > compliant and need to refer to this in my bug report. > > Well, for SQL92, it looks like the correct section to start in is > 13.10 (update statement: searched) which looks like: > > UPDATE <table name> > SET <set clause list> > WHERE <search condition> > > The last of those is the interesting one which is 8.12 (search condition) > > > <search condition> ::= > <boolean term> > | <search condition> OR <boolean term> > > <boolean term> ::= > <boolean factor> > | <boolean term> AND <boolean factor> > > <boolean factor> ::= > [ NOT ] <boolean test> > > <boolean test> ::= > <boolean primary> [ IS [ NOT ] <truth value> ] > > <truth value> ::= > TRUE > | FALSE > | UNKNOWN > > <boolean primary> ::= > <predicate> > | <left paren> <search condition> <right paren> > > > Then 8.1 (predicate) > > <predicate> ::= > <comparison predicate> > | <between predicate> > | <in predicate> > | <like predicate> > | <null predicate> > | <quantified comparison predicate> > | <exists predicate> > | <unique predicate> > | <match predicate> > | <overlaps predicate> > > Also, since tables are effectively unordered, unless the other > where conditions are guaranteed to get a single row anyway which > row is modified is fairly indeterminate; this is only interesting > because it means that you don't necessarily get the same row > as a previous select (if any) would get. That's not always important, > but since update also has no way to order the rows that I know of, > if it were important you couldn't really get around it.
On Thu, 25 Sep 2003, Rory Campbell-Lange wrote: > Thanks for the reply, Stephan. Sorry about the late reply. > > Your dissection of the standard (and thanks for the translation, by > the way!) is a clear reason to use unique row ids. Unfortunately the > project I am reporting bugs on does not do this at present. Well, if you're looking for a PostgreSQL only solution (to throw in the switch), I think someone already sent a query using oid and a subselect. It's not going to perform super well probably and will only work on tables with oids, but it should only update one row.