Comment 1 for bug 1161616

Revision history for this message
Jason Boyer (jboyer) wrote : Re: Reporter: no link between hold_request & hold_request_cancel_cause

We've run into this recently because a user was trying to get a count of cancellation reasons by date. Looking at the Debugging Output SQL somehow the Left Outer Join is trying to match ahr.id = ahrcc.id where it should be ahr.cancel_cause = ahrcc.id

SELECT * FROM (SELECT "ahrcc"."id" AS "Cause ID",
 "ahrcc"."label" AS "Cause Label",
 "ahr"."cancel_cause" AS "Cancelation cause",
 COUNT(DISTINCT "ahr"."id") AS "Hold ID"
  FROM action.hold_request AS "ahr"
 LEFT OUTER JOIN action.hold_request_cancel_cause AS "ahrcc" ON ("ahr"."id" = "ahrcc"."id")
  WHERE date("ahr"."cancel_time") BETWEEN $_27678$2016-03-20$_27678$ AND $_27678$2016-03-26$_27678$
  GROUP BY 1, 2, 3
  ORDER BY "ahrcc"."id" ASC, "ahrcc"."label" ASC, "ahr"."cancel_cause" ASC, COUNT(DISTINCT "ahr"."id") ASC
) limited_to_1048575_hits LIMIT 1048575

See the end of line 6.

I can't see any reason in fm_IDL.xml that this would be the case, however.