Dear Maintainer,

I'm working on a small perl script that pulls random records from an sqlite3
database.  Typically, as with the sqlite interface or cpan installations of
this module, I can do this with a simple "ORDER BY RANDOM() LIMIT 1" clause.
For some reason, when I use the .deb package version of the library, the result
of that clause is always the first matching database record rather than a
random one.

To be honest, I have no immediate idea; at least we don't have any
patches that do something like this.

But may I suggest that you try a newer version than 1.40-2 first?
Testing currently has 1.42-2, and unstable 1.44-1.

Also:

libsqlite3-0 is also quite ancient; in DBD::SQLite's Changes file is
e.g. a note that sqlite 3.8.4.1 fixes ORDER BY bugs.

(testing has 3.8.6-1 and unstable 3.8.7-1)


Cheers,
gregor

sqlite3
of
clause.
result
I've tried to reproduce this problem on libdbd-sqlite3-perl 1.37-1, 1.40-2
and 1.44-1 without success.
Below is what was done to try and reproduce the problem:
arnestig@debian:~$ sqlite3 database.db "CREATE TABLE test (id INTEGER, data
VARCHAR)"
arnestig@debian:~$ sqlite3 database.db "INSERT INTO test VALUES( 1, 'item1'
)"
arnestig@debian:~$ sqlite3 database.db "INSERT INTO test VALUES( 2, 'item2'
)"
arnestig@debian:~$ sqlite3 database.db "INSERT INTO test VALUES( 3, 'item3'
)"
arnestig@debian:~$ perl -e 'use DBD::SQLite; my $dbh =
DBI->connect("DBI:SQLite:dbname=database.db"); my $stmt = "SELECT * FROM
test ORDER BY RANDOM() LIMIT 1"; my $sth = $dbh->prepare( $stmt ); my $rv =
$sth->execute() or die $DBI::errstr; while(my @row =
$sth->fetchrow_array()) { print "Result: $row[0],$row[1]\n"; }'
Result: 2,item2
arnestig@debian:~$ perl -e 'use DBD::SQLite; my $dbh =
DBI->connect("DBI:SQLite:dbname=database.db"); my $stmt = "SELECT * FROM
test ORDER BY RANDOM() LIMIT 1"; my $sth = $dbh->prepare( $stmt ); my $rv =
$sth->execute() or die $DBI::errstr; while(my @row =
$sth->fetchrow_array()) { print "Result: $row[0],$row[1]\n"; }'
Result: 3,item3
arnestig@debian:~$ perl -e 'use DBD::SQLite; my $dbh =
DBI->connect("DBI:SQLite:dbname=database.db"); my $stmt = "SELECT * FROM
test ORDER BY RANDOM() LIMIT 1"; my $sth = $dbh->prepare( $stmt ); my $rv =
$sth->execute() or die $DBI::errstr; while(my @row =
$sth->fetchrow_array()) { print "Result: $row[0],$row[1]\n"; }'
Result: 1,item1
arnestig@debian:~$ perl -e 'use DBD::SQLite; my $dbh =
DBI->connect("DBI:SQLite:dbname=database.db"); my $stmt = "SELECT * FROM
test ORDER BY RANDOM() LIMIT 1"; my $sth = $dbh->prepare( $stmt ); my $rv =
$sth->execute() or die $DBI::errstr; while(my @row =
$sth->fetchrow_array()) { print "Result: $row[0],$row[1]\n"; }'
Result: 2,item2

Please let us know if you have more information on how to reproduce the
problem.

 - Tobias