| Subject: | Strings looking like numbers corrupted |
The attached script creates a file numbers.sqlite that showcases the
bug. The output here (32 bit Linux on i686, Perl 5.8.8) is:
$ perl sqlitebug.pl
2100000517|wanted: 0000002100000517
-2094966779|wanted: 0000002200000517
Inf|wanted: 0000001e00000517
Happened with DBD::SQLite 1.21.
The bug is a real show-stopper.
In 1.19_09 you started guessing the type of values, when the Perl
function looks_like_number returns true. But obviously sqlite3 and Perl
5.8.8 have different notions of what a number is.
I think the correct fix is to completely revert that change, because
essentially the data gets corrupted by it. You can only do this
guessing if you can somehow guarantee that strings looking like numbers
are numerically equal after the cast. For that, you either have to read
them back, or somehow check which numerical value the database engine
/would/ write.
The least thing you should do is roll your own looks_like_number()
function, and that function must be a lot stricter than the Perl
internal. The reason for that is, that Perl already called the function
on the value, and it has already done the appropriate conversion. Consider:
$sth = $dbh->prepare("INSERT INTO foo (bar) VALUES (?)");
$sth->execute(00002.000);
# Your code sees a plain 2.
$sth->execute("00002.000");
# Your code sees 00002.000, but why do you convert it into
# a double? This is obviously not what the user wants.
my $string = "00002.0000";
my $number = 00002.0000;
$sth->execute($string);
# Again, why convert this?
$sth->execute($number);
I think this guessing is dangerous and gives you more headaches than it
cures. But the least needed is a check like $number == 1 * $number;
| Subject: | sqlitebug.pl |
#! /usr/local/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect('dbi:SQLite:dbname=numbers.sqlite', '', '',
{
AutoCommit => 1,
RaiseError => 1,
PrintError => 1,
});
my @strings = qw (
0000002100000517
0000002200000517
0000001e00000517
);
$dbh->do(<<EOF);
CREATE TABLE IF NOT EXISTS numbers(node_id TEXT, wanted TEXT)
EOF
$dbh->do("DELETE FROM numbers");
my $sth = $dbh->prepare("INSERT INTO numbers (node_id, wanted) VALUES (?, ?)");
foreach my $string (@strings) {
$sth->execute($string, "wanted: $string");
}
$sth->finish;
$sth = $dbh->prepare("SELECT node_id, wanted FROM numbers");
$sth->execute;
while (my @row = $sth->fetchrow_array) {
my ($node_id, $wanted) = @row;
print "$node_id|$wanted\n";
}
$dbh->disconnect;
print "Happened with DBD::SQLite $DBD::SQLite::VERSION.\n";
my $string = "0000002200000517";
my $number = 1 * $string;
print "$number\n";