might_have reporter link for actor.org unit uses wrong id

[Rogan Hamby]

A little background:

I doing a custom field mapper entry for a non-Evergreen table that uses a might_have link to the actor.org_unit table. It looked a bit like this:

id SERIAL
owning_lib INTEGER <--- could be null but might have actor.org_unit.id value in it
other stuff (not relevant)

Since the field did not always have an org_unit I used a might_have link type for the owning_lib. Reports consistently showed the id of the table being used rather than owning_lib. The only stock Evergreen might_have link to actor.org_unit.id is for the actor.toolbar but a report built for it shows the same issue.

 FROM actor.toolbar AS "d603ee12c4bc9b209374c6dfe833636f"
 LEFT OUTER JOIN actor.org_unit AS "653faef474990d9fcbc992665e0de2a2" ON ("d603ee12c4bc9b209374c6dfe833636f"."id" = "653faef474990d9fcbc992665e0de2a2"."id")
 LEFT OUTER JOIN actor.usr AS "180efdf191a74dae8d65588766b38d86" ON ("d603ee12c4bc9b209374c6dfe833636f"."id" = "180efdf191a74dae8d65588766b38d86"."id")
 LEFT OUTER JOIN actor.workstation AS "9ef0e80f2ffdd84f5cb750612bfbb091" ON ("d603ee12c4bc9b209374c6dfe833636f"."id" = "9ef0e80f2ffdd84f5cb750612bfbb091"."id")

cleaned up:

FROM actor.toolbar AS atoolb
LEFT OUTER JOIN actor.org_unit AS aou ON (atoolb."id" = aou."id")
LEFT OUTER JOIN actor.usr AS au ON (atoolb."id" = au."id")
LEFT OUTER JOIN actor.workstation AS workst ON (atoolb."id" = workst."id")

And the run report confirms that the results do not look like the database contents. To replicate all you have to do is a report giving the contents of the Custom Toolbar source and link to give the org unit name.

My testing of an Evergreen system was on 3.3

[Galen Charlton (gmc)]

I've tracked this down to a regression in the patch I wrote for bug 1721807 - see commit 88bdd77b97a. Upshot is that the special logic to use the primary key of the left side of the join should be done only if the field is virtual; otherwise, p.uplink.name should be used instead.

I suspect this has been less of an issue for stock reporting sources than one might expect because most of the has_many and might_have linking fields are virtual. (And nullability provides an escape valve when you're sure you want a left or right join.)