| Subject: | Using SQLite::VirtualTable::PerlData, can't find completely numeric values in a virtual table column defined as text |
| Date: | Tue, 27 Mar 2018 15:16:42 +0000 |
| To: | bug-DBD-SQLite [...] rt.cpan.org |
| From: | Tom Wittbrodt <tomwitt2 [...] gmail.com> |
Hi.
I'm trying to use the SQLite::VirtualTable::PerlData module but I've run
into a snag where values which are entirely numeric values, in a defined
text column, are not being found.
Text values are seen correctly but text values comprised solely of numbers
are not being seen.
These are the details of the system on which I'm testing.
Module of concern: SQLite::VirtualTable::PerlData
$> cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04.5 LTS"
$> uname -a
Linux hostname_redacted 3.13.0-132-generic #181-Ubuntu SMP Wed Sep 13
13:25:03 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
perl version 5.18.2
$>perlmodver DBD::SQLite DBI Test::More
DBD::SQLite : 1.56
DBI : 1.636
Test::More : 1.302062
A sample script which illustrates the problem follows. The mixed data
(searching for numbers and text) and number only (searching for only number
values) fail.
#!/usr/bin/env perl
#-----------------------------------------------------------------------------
#
# test_sqlite_virtual_perldata.pl
#
# Quick test program to show problems where the SQLite::Virtual::PerlData
# issues when selecting numbers from a column defined as text
#
#-----------------------------------------------------------------------------
use DBI;
use DBD::SQLite;
use Test::More tests => 4;
my $dbh = DBI->connect("dbi:SQLite:dbname=mixed_data.db", '', '',
{RaiseError => 1, AutoCommit => 1});
# register the module and declare the virtual table
$dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
# create a table, reference_values, with 2 columns
# ref_value - a text column which will have strings and numeric data (as
text)
# our_id - a numeric column with integers
$dbh->do('DROP TABLE IF EXISTS reference_values');
$dbh->do('CREATE TABLE reference_values(ref_value text, our_id int)');
my @data_to_insert = (
[ 'aaaa', 1 ],
[ 'bbbb', 2 ],
[ 'cccc', 3 ],
[ 'xxxx', 4 ],
[ 'yyyy', 5 ],
[ '0003', 6 ],
[ '1000', 7 ],
[ '2222', 8 ],
[ '3000', 9 ],
[ '4000', 10 ],
[ '5abc', 11 ],
[ 'a6cd', 12 ],
[ 'ab7d', 13 ],
[ 'abc8', 14 ],
[ '9aaa', 15 ],
);
my $sth = $dbh->prepare('INSERT INTO reference_values VALUES (?, ?)');
foreach my $data_aref (@data_to_insert) {
$sth->execute(@$data_aref)
or die "Couldn't insert data row:" . $dbh->errstr;
}
# these are data sets that will be used by the virtual perldata function
# we'll add these as a virtual table then do an inner join on our
reference_value
# table to find matching values
my $text_column_search_sets = {
strings_only => [ qw( aaaa abcd bbbb bcde cccc yyyy ) ],
mixed => [ qw( aaaa 0003 z8z8 6666 cccc 1000 zzzz 7777 ) ],
initial_digit => [ qw( 1aaa 2bbb 5abc 6abc 9aaa 3aaa 1aaa 2aaa ) ],
numbers_only => [ qw( 0001 0003 9999 1000 5555 3000 6666 4000 ) ] ,
};
my $expected_answers = {
strings_only =>
[ [ 'aaaa', 1 ], [ 'bbbb', 2 ], [ 'cccc', 3 ], [ 'yyyy', 5 ] ],
mixed => [ [ 'aaaa', 1 ], [ 'cccc', 3 ], [ '0003', 6 ], [ '1000', 7 ] ],
initial_digit => [ [ '5abc', 11 ], [ '9aaa', 15 ] ],
numbers_only =>
[ [ '0003', 6 ], [ '1000', 7 ], [ '3000', 9 ], [ '4000', 10 ] ]
};
our $search_value_set;
my $temp_table_number = 0;
my @test_order = qw(strings_only mixed initial_digit numbers_only );
foreach my $test_desc (@test_order) {
$temp_table_number++;
my $temp_table_name = 'temp.lookup_values_' . $temp_table_number;
$search_value_set = $text_column_search_sets->{$test_desc};
my $virt_table_sql =<< "EOT";
CREATE VIRTUAL TABLE $temp_table_name
USING perl(lookup_value text, colref="main::search_value_set")
EOT
$dbh->do($virt_table_sql);
my $lookup_sql =<< "EOT";
select ref_value, our_id from reference_values
inner join $temp_table_name
on $temp_table_name.lookup_value = reference_values.ref_value
EOT
my $got_aref = $dbh->selectall_arrayref($lookup_sql);
my $expected_aref = $expected_answers->{$test_desc};
is_deeply($got_aref, $expected_aref, $test_desc);
}