[Dbix-class] possible optimization bug in 0.08250

[Alexander Hartmaier]

One of my result classes has a rel ordered by a related column:

__PACKAGE__->has_many(
    "entries",
    "NAC::Model::DBIC::Table::Patch_Interface",
    'fk_patch_request',
    { order_by =3D> 'row_index' },
);

The following code used to sort by entries.row_index but doesn't any more:

my $request =3D $rs->find( $id, { prefetch =3D> [
            'rel_accept',       'rel_req_status',
            'rel_req_customer', 'view_version',
            'entries',
        ],
    });

This is the resulting SQL. It does an order by but on the primary key of th=
e result class:

SELECT me.id_patch_request, me.req_id, me.req_desc, me.req_dn, me.req_name,=
 me.req_email, me.dst_kst_ktr, me.req_datetime, me.accept, me.accept_dateti=
me, me.accept_agent, me.req_status, me.req_status_datetime, me.req_agent, m=
e.agent_comment, me.req_customer, me.req_comment, rel_accept.id_bool_vals, =
rel_accept.bool_val_name, rel_req_status.id_fw_status, rel_req_status.statu=
s_name, rel_req_customer.id_customer, rel_req_customer.customername, rel_re=
q_customer.create_datetime, rel_req_customer.create_user, rel_req_customer.=
update_datetime, rel_req_customer.update_user, view_version.id_patch_reques=
t, view_version.version, entries.fk_patch_request, entries.row_index, entri=
es.hostid, entries.fk_location, entries.rack, entries.fk_patch_conn_type, e=
ntries.fk_ip_net, entries.want_ipv4, entries.ipaddr, entries.want_ipv6, ent=
ries.ipv6, entries.fqdn, entries.cable_id
  FROM patch_request me
  LEFT JOIN bool_vals rel_accept
    ON rel_accept.id_bool_vals =3D me.accept
  JOIN fw_status rel_req_status
    ON rel_req_status.id_fw_status =3D me.req_status
  JOIN customer rel_req_customer
    ON rel_req_customer.id_customer =3D me.req_customer
  JOIN view_patch_request_version view_version
    ON view_version.id_patch_request =3D me.id_patch_request
  LEFT JOIN patch_interface entries
    ON entries.fk_patch_request =3D me.id_patch_request
WHERE me.id_patch_request =3D '9569'
ORDER BY me.id_patch_request

I worked around it by adding the order_by to the calling find:

my $request =3D $rs->find( $id, { prefetch =3D> [
            'rel_accept',       'rel_req_status',
            'rel_req_customer', 'view_version',
            'entries',
        ],
        order_by =3D> 'entries.row_index',
    });

which produces this SQL as expected:

SELECT me.id_patch_request, me.req_id, me.req_desc, me.req_dn, me.req_name,=
 me.req_email, me.dst_kst_ktr, me.req_datetime, me.accept, me.accept_dateti=
me, me.accept_agent, me.req_status, me.req_status_datetime, me.req_agent, m=
e.agent_comment, me.req_customer, me.req_comment, rel_accept.id_bool_vals, =
rel_accept.bool_val_name, rel_req_status.id_fw_status, rel_req_status.statu=
s_name, rel_req_customer.id_customer, rel_req_customer.customername, rel_re=
q_customer.create_
datetime, rel_req_customer.create_user, rel_req_customer.update_datetime, r=
el_req_customer.update_user, view_version.id_patch_request, view_version.ve=
rsion, entries.fk_patch_request, entries.row_index, entries.hostid, entries=
.fk_location, entries.rack, entries.fk_patch_conn_type, entries.fk_ip_net, =
entries.want_ipv4, entries.ipaddr, entries.want_ipv6, entries.ipv6, entries=
.fqdn, entries.cable_id
  FROM patch_request me
  LEFT JOIN bool_vals rel_accept
    ON rel_accept.id_bool_vals =3D me.accept
  JOIN fw_status rel_req_status
    ON rel_req_status.id_fw_status =3D me.req_status
  JOIN customer rel_req_customer
    ON rel_req_customer.id_customer =3D me.req_customer
  JOIN view_patch_request_version view_version
    ON view_version.id_patch_request =3D me.id_patch_request
  LEFT JOIN patch_interface entries
    ON entries.fk_patch_request =3D me.id_patch_request
WHERE me.id_patch_request =3D '9569'
ORDER BY entries.row_index

--
Cheers, Alex


*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*
Notice: This e-mail contains information that is confidential and may be pr=
ivileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130602/a6a=
6ca59/attachment.htm