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:[email protected] -->
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
