[Dbix-class] @bind confusion

[Wolfgang Kinkeldei]

Hi Peter,

If you need the full schema files, I could provide a tarball for you. However, this is nothing that I would like to see in the mailing list as a whole. It would not help most of the people reading this thread...


Am 03.08.2010 um 13:20 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>
> 
> 
> All I can say at this point is that the problem lies somewhere in the way
> count_rs processes arguments. However I need to see a *real* world search()
> query to wrap my head around which use-case did I miss when I designed the
> feature. Please provide me with a complete meaningful search()/DBIC_TRACE
> pair, no matter how complex as long as it is complete.

This is the search taken from my project. The points marked like (1), (2), (3) contain places where changes make the query work or fail.

A working example omitting bind parameters in the subqueries is this (the COUNTing here is done in two different ways but this has no influence of error or success):

my $nr_uploads =
    $schema->resultset('Subelement')
      ->search(
          {
              'element.concept_id' => { '=', \'me.concept_id' },
              
              # (1)
              # vanilla SQL works
              'step.flags' => { '~' => \q{E'\\\\mallow_uploads\\\\M'} },
              
              # (1)
              # with bind values here, things get bad
              #'step.flags' => { '~' => '\\mallow_uploads\\M' },
          },
          {
              alias => 'subelement',
              join => ['step', 'element'],
          })
      ->count_rs
      ->as_query;

my $nr_uploads_done =
    $schema->resultset('File')
      ->search(
          {
              'element.concept_id' => { '=', \'me.concept_id' },
              
              # (2)
              # vanilla SQL works
              'file.origin' => { '=' => \q{'satellite'} },
              'file.path' => { '~' => \q{'/A001'} },
              
              # (2)
              ### with bind values here, things get bad.
              ### reason: collecting the binds as 'where' for subselect _AND_ as 'from' for main select
              # 'file.origin' => { '=' => 'satellite' },
              # 'file.path' => { '~' => '/A001' },
              ### FIXME: looks we are not interested where the files came from, he?
          },
          {
              alias => 'file',
              join => {subelement => ['step', 'element']},
              select => 'count(*)',
              as => 'nr_uploads_done',
          })
      ->as_query;


my $concept_rs =
    $schema->resultset('Concept')
      ->search(
          {
              -not_bool => 'step.is_final',
              'responsibilities.responsibility_kind_id' => 'agency',
              -bool => 'satellites.active',
              'satellites.satellite_id' => 4,
          },
          {
              # (3)
              # when throwing away the joins, things will also work.
              join => [
                'step',
                {responsibilities => {person => {agency => 'satellites'}}},
              ],
              'select' => [
                qw(me.concept_id me.name me.job_no),
                \q{step.name},
                \q{folder_name(me.name || '_' || me.job_no, me.concept_id)},
                $nr_uploads,
                $nr_uploads_done,
              ],
              'as' => [qw(campaign_id name job_no step_name folder_name nr_uploads nr_uploads_done)],
          });
          
my @concepts = $concept_rs->all;


SELECT me.concept_id, me.name, me.job_no, step.name, folder_name(me.name || '_' || me.job_no, me.concept_id), (SELECT COUNT( * ) FROM subelement subelement JOIN step step ON step.step_id = subelement.step_id JOIN element element ON element.element_id = subelement.element_id WHERE ( ( element.concept_id = me.concept_id AND step.flags ~ E'\\mallow_uploads\\M' ) )), (SELECT count(*) FROM file file LEFT JOIN subelement subelement ON subelement.subelement_id = file.subelement_id LEFT JOIN element element ON element.element_id = subelement.element_id WHERE ( ( element.concept_id = me.concept_id AND file.origin = 'satellite' AND file.path ~ '/A001' ) )) FROM concept me JOIN step step ON step.step_id = me.step_id LEFT JOIN responsibility responsibilities ON responsibilities.concept_id = me.concept_id LEFT JOIN person person ON person.person_id = responsibilities.person_id LEFT JOIN agency agency ON agency.agency_id = person.agency_id LEFT JOIN satellite satellites ON satellites.agency_id = agency.agency_id WHERE ( ( satellites.active AND (NOT step.is_final) AND responsibilities.responsibility_kind_id = ? AND satellites.satellite_id = ? ) ): 'agency', '4'


when changing the condition marked (1) to use bind parameters:

my $nr_uploads =
    $schema->resultset('Subelement')
      ->search(
          {
              'element.concept_id' => { '=', \'me.concept_id' },
              
              # (1)
              # vanilla SQL works
              # 'step.flags' => { '~' => \q{E'\\\\mallow_uploads\\\\M'} },
              
              # (1)
              # with bind values here, things get bad
              'step.flags' => { '~' => '\\mallow_uploads\\M' },
          },
          {
              alias => 'subelement',
              join => ['step', 'element'],
          })
      ->count_rs
      ->as_query;

-- remaining part stays the same, the trace displays:

SELECT me.concept_id, me.name, me.job_no, step.name, folder_name(me.name || '_' || me.job_no, me.concept_id), (SELECT COUNT( * ) FROM subelement subelement JOIN step step ON step.step_id = subelement.step_id JOIN element element ON element.element_id = subelement.element_id WHERE ( ( element.concept_id = me.concept_id AND step.flags ~ ? ) )), (SELECT count(*) FROM file file LEFT JOIN subelement subelement ON subelement.subelement_id = file.subelement_id LEFT JOIN element element ON element.element_id = subelement.element_id WHERE ( ( element.concept_id = me.concept_id AND file.origin = 'satellite' AND file.path ~ '/A001' ) )) FROM concept me JOIN step step ON step.step_id = me.step_id LEFT JOIN responsibility responsibilities ON responsibilities.concept_id = me.concept_id LEFT JOIN person person ON person.person_id = responsibilities.person_id LEFT JOIN agency agency ON agency.agency_id = person.agency_id LEFT JOIN satellite satellites ON satellites.agency_id = agency.agency_id WHERE ( ( satellites.active AND (NOT step.is_final) AND responsibilities.responsibility_kind_id = ? AND satellites.satellite_id = ? ) ): '\mallow_uploads\M', '\mallow_uploads\M', 'agency', '4'
Cannot bind unknown placeholder 4 (4) at /Library/Perl/5.10.0/DBIx/Class/Storage/DBI.pm line 1475.


The query part looks absolutely perfect, only the subquery-bind-parameter is used too often :-(



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