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_s tr()
{
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_s tr());


--
Rory Campbell-Lange
<rory@campbel l-lange.net>
<www.campbell-lange.net>

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

[Stephan Szabo]

Re: How to find LIMIT in SQL standard

On Mon, 22 Sep 2003, Rory Campbell-Lange wrote:
[color=blue]
> 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.[/color]

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]

Re: How to find LIMIT in SQL standard

Rory Campbell-Lange wrote:
[color=blue]
> 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.[/color]

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]

Re: How to find LIMIT in SQL standard

Rory Campbell-Lange writes:
[color=blue]
> 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.[/color]

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


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

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

[Christopher Browne]

Re: How to find LIMIT in SQL standard

In an attempt to throw the authorities off his trail, rory@campbell-lange.net (Rory Campbell-Lange) transmitted:[color=blue]
> 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.[/color]

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=cbbrow ne> 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.eviloverlor d.com/>

[Rory Campbell-Lange]

Re: How to find LIMIT in SQL standard

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@megazon e.bigpanda.com) wrote:[color=blue]
> On Mon, 22 Sep 2003, Rory Campbell-Lange wrote:
>[color=green]
> > 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.[/color]
>
> 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.[/color]


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

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

[Stephan Szabo]

Re: How to find LIMIT in SQL standard

On Thu, 25 Sep 2003, Rory Campbell-Lange wrote:
[color=blue]
> 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.[/color]

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