[Dbix-class] delete_related issue when related resultset does a prefetch

[Greg Hill]

Me again.  Now that the other prefetch stuff is sorted, I'm seeing =

problems with delete_related where the related resultset has a default =

prefetch defined. It's producing invalid SQL (the db in question here is =

mysql, we haven't yet ported this code to postgres):

You can't specify target table 'subaccnt_status' for update in FROM =

clause [for Statement "UPDATE subaccnt_status SET effective_date =3D ? =

WHERE ( id IN ( SELECT me.id FROM subaccnt_status me  JOIN =

c_subaccnt_status status_code ON status_code.id =3D me.status_code WHERE ( =

( accnt =3D ? AND subaccnt =3D ? ) ) ORDER BY effective_date ) )" with =

ParamValues: 0=3D'2012-07-24T23:59:30', 1=3D'369083', 2=3D'1'

The appropriate query should just be:

DELETE FROM subaccnt_status WHERE (accnt =3D ? AND subaccnt =3D ?);

This is the innocuous code in question:

$sub->delete_related('subaccnt_status');

The relationship is defined as such:

__PACKAGE__->has_many(
     subaccnt_status =3D> 'LW::Schema::Billing::subaccnt_status',
     { 'foreign.accnt' =3D> 'self.paccnt', 'foreign.subaccnt' =3D> 'self.id=
'},
     { cascade_delete =3D> 0 },
);

This is the default prefetch stuff defined in 'subaccnt_status':

__PACKAGE__->resultset_attributes({prefetch =3D> ['status_code'], order_by =

=3D> 'effective_date'});

So, I think this is a bug, but I thought I'd ask about it to see if =

there's an easy solution or workaround.  It is working correctly on the =

ancient version we're currently using.

It seems to boil down to the DBIx::Class::Resultset:: _rs_update_delete =

method should be ignoring stuff like prefetch and order_by when being =

called by 'delete'.  But maybe there's a scenario where that behavior is =

desirable?  Something like this:

my $attrs =3D { %{$self->_resolved_attrs} };
|||+
+if ($op eq 'delete') {
+ delete @$attrs{qw(order_by prefetch)}; # are there others that we =

should ignore?
+}
|
But since the internals are new to me, that might not be a valid =

assumption in all cases.  Anyone want to weigh in on whether that's a =

bad idea or not?  I'm willing to work on a patch and tests, but I want =

to make sure I'm heading in the right direction first.

Thanks.

Greg

P.S. Apologies if this isn't the right venue to voice these questions.  =

Feel free to point me elsewhere if I should be going elsewhere.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120725/095=
7cee7/attachment.htm