How to find LIMIT in SQL standard

[Rory Campbell-Lange]

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
<ro**@campbell-lange.net>
<www.campbell-lange.net>

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Gaetano Mendola]

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

[Peter Eisentraut]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Christopher Browne]

In an attempt to throw the authorities off his trail, ro**@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/>

[Rory Campbell-Lange]

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 (ss****@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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org