[Dbix-class] @bind confusion

[Wolfgang Kinkeldei]

Hi Peter,

Am 03.08.2010 um 11:59 schrieb Peter Rabbitson:

> Wolfgang Kinkeldei wrote:
>> Hello,
>> when using subqueries in the 'from' part of a query, sometimes the list of @bind parameters is not maintained correct. As a simple testcase please find a primitive subselect enclosed that just does a count with a correlated subquery.
>> <snip>
>> # a simple counting subquery
>> my $subquery =     $schema->resultset('PersonRole')
>>        ->search( { role_id => 'admin' } ) # 1st bind param
>>        ->count_rs
>>        ->as_query;
>> my @people =     $schema->resultset('Person')
>>        ->search(
>>            {
>>                'me.person_id' => 42, # 2nd bind param
>>            },
>>            {
>>                select => [ $subquery ],
>>                # whatever you join here, @bind gets confused
>>                join => 'person_regions'
>>            })
>>        ->all;
>> <snap>
>> if the 'join' attribute in the main query is left off, the SQL is fired as expected:
>> SELECT (SELECT COUNT( * ) FROM person_role me WHERE ( role_id = $1 )) FROM person me WHERE ( me.person_id = $2 )
>> DETAIL:  parameters: $1 = 'admin', $2 = '42'
> 
> Ok then, do you understand that the above SQL does in fact? person_role and person
> are *NOT CORRELATED* in any way. You might as well say:

the original query was correlated, the example I tracked down to find the easiest case that triggers the error is not correlated any more. In the meantime, however, I think that correlation is not the point. It looks like a combination of joins and the column-names a subquery contains and the bind-parameters are bound to.

> SELECT 42 FROM person me WHERE (me.person_id = ?)
> 
> While dbic is incorrectly dropping the bind param (and I will look into this), your
> query makes absolutely no sense as a whole.

right. THIS subquery is nonsense. But I have found real cases of queries that fail. However, they will need much more explanation and a schema definition which I wanted to suppress in order to keep the mail short. And my thought was that a simple person-role relation is easily comprehensible for everyone even without knowing the schema definition.

> Remember - any subquery statement is
> *ABSOLUTELY OBLIVIOUS* to anything outside of its outer closing ()s. I would like
> to provide you with a correct search() call, but I literally can not understand what
> you are trying to express with the above.

... just trying to trigger the error :-)

[...]


Best,

Wolfgang Kinkeldei

-- 

' /\_/\ ' .print[split??,"".(($/=q|Cms)+-03467:;<=|)=~tr!C-z -B! -z!)x
'( o.o )' .$/]->[hex]foreach split qr<>,qq+1ecd039ad65b025b8063475b+||
' > ^ < ' .q<!-- Wolfgang Kinkeldei - mailto:wolfgang at kinkeldei.de -->