1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Test::More;
use lib "t/lib";
use SQLiteTest;
BEGIN { requires_sqlite('3.6.3') }
plan tests => 22;
use_ok('DBD::SQLite');
my $noprintquerymsg = '(Set ENV{PRINT_QUERY} to true value to see query)';
my $tinfo;
my $dbh = DBI->connect('DBI:SQLite::memory:');
ok( ref $dbh, "create new db" );
# ######
# 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);
|
