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

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

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]

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]

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?"