300281 - sql_limit is no longer needed with MySQL 4.0.6

Status: RESOLVED WONTFIX

(Bugzilla :: Database, enhancement)

Description

[Max Kanat-Alexander]

In MySQL 4.0.6, they've added a syntax for LIMIT that is compatible with
PostgreSQL's LIMIT statement, which I suspect is ANSI.

I'll have to look at how Oracle and a few other DBs do this also, but I think
it's possible we could eliminate the sql_limit function from Bugzilla::DB and
Bugzilla's SQL.

Comment 1

[Lance Larsh]

(In reply to comment #0)
> I'll have to look at how Oracle and a few other DBs do this also, but I think
> it's possible we could eliminate the sql_limit function from Bugzilla::DB and
> Bugzilla's SQL.

Oracle uses "WHERE ROWNUM < x" rather than "LIMIT x", so we still need the 
sql_limit abstraction.  However, note that because of the way Oracle's ROWNUM 
works, it is not possible to emulate MySQL/PostgreSQL "LIMIT x OFFSET y" 
efficiently in Oracle SQL.  I think this is true for other DB platforms as well.

It probably would be best to remove the OFFSET functionality from the sql_limit 
spec, in the interest of database portability.  As of today, the only caller 
that uses the OFFSET functionality is the query that picks a random quip in 
buglist.cgi, so that query would need to be rewritten.

Comment 2

[Max Kanat-Alexander]

(In reply to comment #1)
> It probably would be best to remove the OFFSET functionality from the sql_limit 
> spec, in the interest of database portability.  As of today, the only caller 
> that uses the OFFSET functionality is the query that picks a random quip in 
> buglist.cgi, so that query would need to be rewritten.

  OK, that's probably the best thing to do. Feel free to file a bug for that, if
you'd like.

  Sigh... and I was so hoping to be able to remove this function. Ah well.