Reporter: incorrect join to asset.copy from acq.lineitem_detail

[Chris Sharp]

When creating a report that joins asset.copy from acq.lineitem_detail, the resulting query uses acq.lineitem_detail.id when it should be using acq.lineitem_detail.eg_copy_id. Example query:

SELECT * FROM (SELECT "c61194bfb1b243dce5db33d04718555e"."barcode" AS "Barcode",
 "488118d5f4356a27222fe6db353b8957"."cancel_reason" AS "Cancel Reason"
  FROM acq.lineitem_detail AS "488118d5f4356a27222fe6db353b8957"
 INNER JOIN asset.copy AS "c61194bfb1b243dce5db33d04718555e" ON ("488118d5f4356a27222fe6db353b8957"."id" = "c61194bfb1b243dce5db33d04718555e"."id")
  WHERE "c61194bfb1b243dce5db33d04718555e"."deleted" = $_46500$f$_46500$
 AND "c61194bfb1b243dce5db33d04718555e"."barcode" LIKE $_46500$%ACQ%$_46500$
  GROUP BY 1, 2
  ORDER BY "c61194bfb1b243dce5db33d04718555e"."barcode" ASC, "488118d5f4356a27222fe6db353b8957"."cancel_reason" ASC
) limited_to_1048575_hits LIMIT 1048575

As you can see, the reporter has joined on the wrong field. Full debug output attached.

Evergreen 2.12/3.0
OpenSRF 3.0.0
PostgreSQL 9.5
Ubuntu 16.04 LTS

[Mike Rylander (mrylander)]

Chris, a couple questions about the template...

 * It looks like it was created with the XUL client (version 4 template structure), is that correct?
 * It looks like the core class is the ACQ Line Item Detail (that is, the direction is from acqlid to acp), is that also correct? (I just want to clarify what the description says...)
 * Was nullability activated, and used, in creating this template?

The problem stems from the join path being written as "id-acp-id-b7df08da8516c6304013fb1ac3001c3b" when it should be "eg_copy_id-acp-id-b7df08da8516c6304013fb1ac3001c3b". I don't have a cause for that yet, but nullability may be involved.

[Chris Sharp (chrissharp123)]

Mike,

- Yes, it's a XUL/version 4 template

- Yes, the core class is ACQ Line Item Detail

- I'm actually trying to verify this, but I think this was done without nullability.

Please let me know if there's more I can provide to assist.