Re: Hash-clobbering in DBD's fetchrow

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)

Comment on Re: Hash-clobbering in DBD's fetchrow_hashref Select or Download Code

Replies are listed 'Best First'.
Re: Re: Hash-clobbering in DBD's fetchrow_hashref by Cody Pendant (Prior) on Aug 27, 2003 at 22:12 UTC
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]	[reply] [d/l]
3Re: Hash-clobbering in DBD's fetchrow_hashref by jeffa (Bishop) on Aug 28, 2003 at 01:04 UTC
I see you used the word "assumed" ... remember, don't ever assume.¹ ;) What i was trying to tell you is that the names "a.id" and "b.id" are only good for the database query ... not the results. That is what the `AS` keyword is for. If you use the `AS` keyword, then DBI does do it your way, but i am not sure that this is DBI's responsibilty - this is really a matter of the database server in question. I really don't think that DBI even sees the table qualifications in your query ... and to make it do so is surely more trouble than it's worth. (Have you looked at the source of DBI.pm and/or it's drivers?). So, not only do you have to fully qualify the table name, you also have to ensure that the field names are not the same by aliasing them. I am curious as to why you didn't use a column alias ... the "as x" thing. Also, i don't recommend prepending the table name to it's id column (that is, a.a_id instead of a.id), but it is a solution as well. Usually, i only select the id from one of the tables that i am joining to, but when i do need more id's, i use an alias. It just seems like the logical thing to do to me ... but hey! TIMTOWTDI! Oh ... and don't worry if you don't see the light up front and think things should be this way instead of that way. I do that all the time ... here is an example! ;) 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) 1 - When i was a Recording Industry major in college, i tried to argue some points back on a test in Copyright Law. My instructor was a lawyer, and sharp (which is why he left after 2 semesters). I augmented my argument somehow with "well ... i assumed that ..." when he immediately cut me off with "AHA! You assumed! Next?"	[reply]