Skip Menu |

This queue is for tickets about the DBD-SQLite CPAN distribution.

Report information
The Basics
Id: 84373
Status: resolved
Priority: 0/
Queue: DBD-SQLite

People
Owner: Nobody in particular
Requestors: RURBAN [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Unimportant
Broken in: (no value)
Fixed in: (no value)



Subject: Add test for RT #26775 "name)" key with DISTINCT
Attached file tests the DBD::SQLite2 bug with SELECT DISTINCT(t.name) returning "name)" as hashref key. It passes with DBD::SQLite and was fixed in the other distro with DBD-SQLite2-0.35 -- Reini Urban
Subject: rt_26775-distinct.t
#!/usr/bin/perl use strict; use warnings; use DBI; use Test::More; use_ok('DBD::SQLite'); plan tests => 22; our $DBFILE = 'rt_26775-distinct..db'; my $noprintquerymsg = '(Set ENV{PRINT_QUERY} to true value to see query)'; my $tinfo; # Remove stale database file. (We dont wanna keep the file). unlink $DBFILE if -f $DBFILE; END { unlink $DBFILE; } my $dbh = DBI->connect('DBI:SQLite:' . $DBFILE); ok( ref $dbh, "create new db: $DBFILE" ); # ###### # First we create our schema (attached in __DATA__) # my $slurp; while (my $line = <DATA>) { $slurp .= $line; } QUERY: for my $query (split m/ ; /xms, $slurp) { # remove newline + leading and trailing whitespace. chomp $query; $query =~ s/^ \s+ //xms; $query =~ s/ \s+ $//xms; next QUERY if not $query; # execute the query. my $sth = $dbh->prepare($query); $tinfo = $ENV{PRINT_QUERY} ? "prepare: $query" : "prepare: $noprintquerymsg"; ok( ref $sth, $tinfo); my $ret = $sth->execute( ); $tinfo = $ENV{PRINT_QUERY} ? "execute: $query" : "execute: $noprintquerymsg"; ok( $ret, $tinfo); $sth->finish( ); } # ###### # Then we test the bug. # # We test with both 'DISTINCT(t.name) [..]' and 'DISTINCT t.name [..]' # my $query_with_parens = trim(q{ SELECT DISTINCT(t.name), t.tagid FROM objtagmap m,tags t WHERE (m.objid = 1) AND (t.tagid = m.tagid) }); my $query_without_parens = trim(q{ SELECT DISTINCT t.name, t.tagid FROM objtagmap m,tags t WHERE (m.objid = 1) AND (t.tagid = m.tagid) }); foreach my $query (($query_with_parens, $query_without_parens)) { # just to print readable test descriptions. my $abbrev = substr $query, 0, 25; my $sth = $dbh->prepare($query); ok( ref $sth, "prepare $abbrev" ); my $ret = $sth->execute( ); ok( $ret, "execute $abbrev" ); while (my $hres = $sth->fetchrow_hashref) { # Here we should get two hash keys: 'name' and 'tagid'. ok( exists $hres->{name}, 'exists $hres->{name}' ); ok( exists $hres->{tagid}, 'exists $hres->{tagid}' ); if (! exists $hres->{name}) { $Data::Dumper::Varname = ''; eval 'require Data::Dumper;'; if (! $@) { $Data::Dumper::Varname = 'fetchrow_hashref'; print {*STDERR} "#[RT #26775] The keys we got was: ", Data::Dumper::Dumper($hres), "\n"; } } } $sth->finish; } $dbh->disconnect; sub trim { my ($string) = @_; $string =~ s/^ \s+ //xms; $string =~ s/ \s+ $//xms; $string =~ s/\s+/ /xms; return $string; } # DATA has schema for 3 tables. object, tags, and objtagmap. # We create an article object and a tag, and then we connect the article object with the # tag. __DATA__ CREATE TABLE object ( id INTEGER PRIMARY KEY NOT NULL, parent INTEGER NOT NULL DEFAULT 1, name VARCHAR(255) NOT NULL, type CHAR(16) NOT NULL default 'directory' ); CREATE TABLE objtagmap ( id INTEGER PRIMARY KEY NOT NULL, objid INTEGER NOT NULL, tagid INTEGER NOT NULL ); CREATE TABLE tags ( tagid INTEGER PRIMARY KEY NOT NULL, name char(32) NOT NULL ); INSERT INTO object (id, parent, name, type) VALUES (1, 1, 'All about the the distinct hash key problem, and how to survive deadly weapons', 'article'); INSERT INTO tags(tagid, name) VALUES (1,'bugs'); INSERT INTO objtagmap(id, objid, tagid) VALUES(1, 1, 1);
Thanks. Applied to the trunk with some modification (we can use :memory: database which we don't need to clean up, and the num-of-tests declaration should be put before use_ok). On Tue Apr 02 23:05:47 2013, RURBAN wrote: Show quoted text
> Attached file tests the DBD::SQLite2 bug with > SELECT DISTINCT(t.name) returning "name)" as hashref key. > > It passes with DBD::SQLite and was fixed in the other distro with DBD- > SQLite2-0.35
On Tue Apr 02 11:04:02 2013, ISHIGAKI wrote: Show quoted text
> Thanks. Applied to the trunk with some modification (we can use > :memory: database which we don't need to clean up, and the num-of- > tests declaration should be put before use_ok).
Yes, you are right. Thanks for catching these. Show quoted text
> On Tue Apr 02 23:05:47 2013, RURBAN wrote:
> > Attached file tests the DBD::SQLite2 bug with > > SELECT DISTINCT(t.name) returning "name)" as hashref key. > > > > It passes with DBD::SQLite and was fixed in the other distro with
> DBD-
> > SQLite2-0.35
-- Reini Urban
Closed as DBD::SQLite 1.38_02 was out. Thanks. On Wed Apr 03 00:40:00 2013, RURBAN wrote: Show quoted text
> On Tue Apr 02 11:04:02 2013, ISHIGAKI wrote:
> > Thanks. Applied to the trunk with some modification (we can use > > :memory: database which we don't need to clean up, and the num-of- > > tests declaration should be put before use_ok).
> > Yes, you are right. Thanks for catching these. >
> > On Tue Apr 02 23:05:47 2013, RURBAN wrote:
> > > Attached file tests the DBD::SQLite2 bug with > > > SELECT DISTINCT(t.name) returning "name)" as hashref key. > > > > > > It passes with DBD::SQLite and was fixed in the other distro with
> > DBD-
> > > SQLite2-0.35
>