Thread: How to find LIMIT in SQL standard

How to find LIMIT in SQL standard

From
Rory Campbell-Lange
Date:
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>

Re: How to find LIMIT in SQL standard

From
Stephan Szabo
Date:
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.

Re: How to find LIMIT in SQL standard

From
Gaetano Mendola
Date:
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


Re: How to find LIMIT in SQL standard

From
Peter Eisentraut
Date:
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


Re: How to find LIMIT in SQL standard

From
Christopher Browne
Date:
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/>

Re: How to find LIMIT in SQL standard

From
Rory Campbell-Lange
Date:
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.


Re: How to find LIMIT in SQL standard

From
Stephan Szabo
Date:
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.