I wrote a column on that :-) (Well it was months that I wanted to say this in a node!).

This has nothing to do with MySQL. It's a DBI issue. If you fetch a record with fetchrow_hashref and you have more columns with the same name, you will get only one of them.

See DBI recipes and look, towards the end, for a section titled "A trap to avoid with fetchrow_hashref".

The solution to your problem is either using an alias in your SQL (select a.id as AID, b.id as BID) or using a different method (fetchrow_array, fetchrow_hashref).

 _  _ _  _  
(_|| | |(_|><
 _|   

select table_a.id as a_id,
       table_b.id as b_id,
       ..
[download]

blokhead

select table_a.id as "id1", table_b.id as "id2", ...
[download]

select "id1" = table_a.id, "id2" = table_b.id, ...
[download]

I think my point is in direct contradiction of 'this is expected behaviour' though -- my post was pretty much about the fact that I didn't expect it!

What you're saying is, this is not a feature request I should make, or some lesser-known option in DBD::MySQL like for instance --

    use_table_prefixes => 1
[download]

-- it's a feature. I guess I can live with that.

But I still think it's kind of intuitive that, having already distinguished between the fields in order to get the select to work, I shouldn't have to do it twice to get the hashref to work. In what circumstances would my expected behaviour have negative consequences?

($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
 =~y~b-v~a-z~s;                             print
[download]

Cody

I suppose what you expected and what others expect is a little different. Just because you used the two id's in a join in a SQL select doesn't mean that either should appear in the output!

The behaviour of DBD is quite well documented, their are plenty of tutorials around, even within the Monastery, and some excellent books that deal with all of the concepts.

What you are saying is that in your view the behaviour of DBD in this instance is counter-intuitive whilst to those of us who live with DBI/DBD every day it is quite the expected behaviour and their are several ways of dealing with the issue once you are aware of it.

jdtoronto

It's really quite simple:

my %hash = (
   id => 'foo',
   id => 'bar',
   id => 'baz',
);
[download]

Now, what is the value of $hash{id}? It is baz, because that was the last key/val pair in the list. You know that hashes behave this way, why should DBI do something different?

Here is a little test you might be interested in:

my $sth = $dbh->prepare('select 1,1,1');

$sth->execute;
print Dumper $sth->fetchrow_hashref;

$sth->execute;
print Dumper $sth->fetchrow_arrayref;

$sth = $dbh->prepare('select 1 as a,1 as b,1 as c');
$sth->execute;
print Dumper $sth->fetchrow_hashref;
[download]

Even when you are dealing with just SQL, this is expected behavior. It is not at all uncommon to use column aliases, and if you really did "distinguish ... the fields in order to get the select to work" then you "shouldn't have to do it twice to get the hashref to work". Remember, just qualifying the column by appending the table name to it DOES NOT change the column name:

mysql> select baz.foo, baz.bar from baz;
+---------------+------+
| foo           | bar  |
+---------------+------+
| <tag> & stuff |    0 |
| one           |    1 |
| two           |    2 |
| three         |    3 |
| zero          |    0 |
| null          | NULL |
+---------------+------+
6 rows in set (0.00 sec)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

I wouldn't want people to go away from this question thinking I'm still sulking about it and saying that DBI is broken in some way, I'm really not. I found my own workaround and you guys have very helpfully shown me that other way to go about it, the "as x" thing.

Maybe it's because I'm relatively new to this stuff that I even asked, but I want to repeat my point as simply as possible.

I selected "a.id" and "b.id" from some tables. I assumed that because I'd selected them using those names, that DBI would put them into the hash with those names.

I wasn't enraged or even mildly surprised that it didn't. I just wondered why not.

Are there any negative consequences that I can't see, that would occur if DBI did it "my" way?

---




($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
 =~y~b-v~a-z~s;                             print
[download]