Bug#766501: libdbd-sqlite3-perl: 'order by random limit 1' not random

[Tobias Eliasson]

On Thu, 23 Oct 2014 11:38:37 -0400 Pablo Virgo <medusa.v2 at gmail.com> wrote:
> 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.
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 at debian:~$ sqlite3 database.db "CREATE TABLE test (id INTEGER, data
VARCHAR)"
arnestig at debian:~$ sqlite3 database.db "INSERT INTO test VALUES( 1, 'item1'
)"
arnestig at debian:~$ sqlite3 database.db "INSERT INTO test VALUES( 2, 'item2'
)"
arnestig at debian:~$ sqlite3 database.db "INSERT INTO test VALUES( 3, 'item3'
)"
arnestig at 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 at 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 at 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 at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.alioth.debian.org/pipermail/pkg-perl-maintainers/attachments/20141103/6d08cf8f/attachment-0001.html>