Duplicate fields in select_hashref

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/