Bug http://rt.cpan.org/Public/Bug/Display.html?id=16928 was placed for
SQL::Statement, but in fact falls back to all of DBI
Where SQL code allows
sql> select foo, foo from table_foo;
Exaple in postgres:
test=# select usr, usr from usr where c_usr = 203;
usr | usr
--------+--------
prosup | prosup
(1 row)
DBI cannot handle that in the fetchrow_hashref () method, as fields map
to keys, and duplicate keys are not allowed in hashes. period
--8<--- test.pl
use strict;
use warnings;
use Data::Peek;
use DBI;
my $dbh = DBI->connect ($ENV{DBI_DSN}, $ENV{DBI_USER}, $ENV{DBI_PASS}, {
RaiseError => 1,
PrintError => 1,
});
my $sth = $dbh->prepare ("select usr, usr from usr where c_usr = 203");
$sth->execute;
DDumper $sth->{NAME_lc};
DDumper [ $sth->fetchrow ];
$sth->execute;
DDumper $sth->fetchrow_arrayref;
$sth->execute;
DDumper $sth->fetchrow_hashref;
$sth->bind_columns (\my ($x, $y));
$sth->execute; $sth->fetch;
DDumper [ $x, $y ];
-->8---
$ perl test.pl
[ 'usr',
'usr'
]
[ 'prosup',
'prosup'
]
[ 'prosup',
'prosup'
]
{ usr => 'prosup'
}
[ 'prosup',
'prosup'
]
$
I think is is very worthy documenting this somewhere
Changing the select statement to
my $sth = $dbh->prepare ("select usr x, usr y from usr where c_usr = 203");
will (of course) fix it for all DBD's that support field aliasing
$ perl test.pl
[ 'x',
'y'
]
[ 'prosup',
'prosup'
]
[ 'prosup',
'prosup'
]
{ x => 'prosup',
y => 'prosup'
}
[ 'prosup',
'prosup'
]
But it can get worse ...
$ describe foo
Table Field N Type
---------------------------- -------------------- - ---------------------------
foo c_foo 5:int2 (2)
foo foo 12:text (20)
foo count 4:int4 (4)
--8<---
my $stc = $dbh->prepare ("select count(*) from foo");
$stc->execute;
DDumper $stc->{NAME_lc};
DDumper $stc->fetchrow_hashref;
-->8---
=>
[ 'count'
]
{ count => 0
}
Huh? Where is my "(*)"?
--8<---
my $stc = $dbh->prepare ("select count, count(*) from foo group by count");
$stc->execute;
DDumper $stc->{NAME_lc};
DDumper $stc->fetchrow_hashref;
-->8---
=>
[ 'count',
'count'
]
undef
Wonderful! and no error message
my $stc = $dbh->prepare ("select count, count(*) c from foo group by count");
[ 'count',
'c'
]
undef
my $stc = $dbh->prepare ("select count c, count(*) from foo group by c");
[ 'c',
'count'
]
undef
--
H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/
using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/
http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
