Update although I will certainly entertain arguments about whether they should be supported. MySQL didn't used to support them, now it sort of does but it's causing problems. Thoughts?

AFACT, SQL::Statement can bind the placeholders prior to "generating an execution plan for the query." As each DBD differs slightly anyways (DBD::mysql and DBD::Pg anyone?!), you can advertise this as a "feature" with little developer cost.

I've always thought that the execution plan argument against LIMIT placeholders was a bunch of hooey. The execution plan isn't be any worse because you're taking the 100th-119th records vs. full set. You still have to go retrieve the whole record set anyways. The only things I can see are

• If your LIMIT starts at 0, you might lose an optimization if your tables are split across disks.
• If your LIMIT starts at a higher number than your cardinality in one of the indices, you execute the query even though you can know you'll be returning an empty set.

Those are all lost optimizations. They're not degradations of the base query's execution plan. Oracle's developers are just lazy.

---

• In general, if you think something isn't in Perl, try it out, because it usually is. :-)
• "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"

Just tried PostgreSQL


$dbh2->selectall_arrayref("SELECT * FROM sessions limit ? offset ?", {
+}, 2,3);
[download]

works like a charm

OK. Facts accepted. So what would be the best way to implement paging of records in chunks of, say, 20 per page?

The immediate solution is to dynamically generate the SQL. This could be as simple as:

my $sql = <<__END_SQL__;
YOUR STATEMENT HERE
__END_SQL__

$sql .= "LIMIT $start, $chunk_size\n";

my $sth = $dbh->prepare( $sql );
# etc.
[download]

---

• In general, if you think something isn't in Perl, try it out, because it usually is. :-)
• "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"

Variable interpolation?

sub LIMIT_CLAUSE {
    my($self,$limit_clause) = @_;
#    $limit_clause = trim($limit_clause);
    $limit_clause =~ s/^\s+//;
    $limit_clause =~ s/\s+$//;

    return 1 if !$limit_clause;
    my($offset,$limit,$junk) = split /,/, $limit_clause;
    return $self->do_err('Bad limit clause!')
         if (defined $limit and $limit =~ /[^\d]/)
         or ( defined $offset and $offset =~ /[^\d]/ )
         or defined $junk;
    if (defined $offset and !defined $limit) {
        $limit = $offset;
        undef $offset;
    }
    $self->{"struct"}->{"limit_clause"} = {
        limit  => $limit,
        offset => $offset,
     };
     return 1;
}
[download]

The important part is the middle. SQL::Parser demands that all parameters to a LIMIT clause be numeric. This is inline with most (but not all) DBD::'s. The major exception is DBD::mysql, which allows parameters in LIMIT clauses (with certain restrictions).

If you feel that this is a useful feature, provide a few test cases for the author. Better is the patch as well, but the test cases are a minimum.

Most databases do not support placeholders in the limit clause (I believe MySQL, as always, is at least one exception to the rule). It is the same with the ORDER BY and GROUP BY clauses. Placeholders are for column values.

My guess is that DBD::CSV is sticking with the norm here.

With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it.

I believe the LIMIT clause is applied _after_ the data are retrieved, hence, the database has no way of creating a valid statement and its execution plan if you use placeholders for it.

Actually, the LIMIT clause is applied before the data are retrieved, as it can influence the execution plan. Something with a LIMIT above the number of records in the table shouldn't use indexes, whereas something with a LIMIT of 1 should. See 7.2.12. How MySQL Optimizes LIMIT.

My normal rule of thumb is that placeholders work on values (field values, or values derived from them in the case of some where clauses), not parameters (limit clause, 'DESC' in the case of ordering), names (field names, table names, schema names, etc). Unfortunately, the mysql documentation doesn't seem to cover what it's allowed to replace. (There's a section in the documentation 13.7. SQL Syntax for Prepared Statements, and the closest I can find is the explaination of the various data types for placeholders in 24.2.5. C API Prepared Statement Data types)

DBD::mysql allows placeholders in the LIMIT clause. It's not clear whether MySQL itself is as accomodating.

The difference arises because DBD::mysql handles placeholders client-side. There is a push to have the DBD hand off placeholders to the server (now that MySQL supports server-side prepares), but the current CPAN version doesn't have that functionality yet. q.v. New twist for DBD::mysql for more on the topic.

---

• In general, if you think something isn't in Perl, try it out, because it usually is. :-)
• "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"

In SQL::Statement there are two kinds of LIMIT clauses -those that can be applied during a search and those that can only be applied afterwards. Strictly speaking, a LIMIT clause without an ORDER BY clause makes no sense since it depends on the physical ordering of rows (which nothing ever should). But given the way S::S operates, I allow it to do LIMIT without ORDER BY since that's the only way to actually limit what is being searched as distinct from limiting a result set after a search.

Yep, the placeholders can only be used in certain places in the statement and as the argument to a LIMIT clause is not one of those places.

/J\