Report templates cloned from those created on XUL client causing error or producing different results

Bug #1796945 reported by tji@sitka.bclibraries.ca on 2018-10-09
32
This bug affects 7 people
Affects Status Importance Assigned to Milestone
Evergreen
High
Unassigned

Bug Description

This is related to fix in bug 1642344.

We deployed the fix and started to see issues.

Cloning process seems fine. Without updating anything the template can be saved.

When running reports from the cloned templates, there were errors complaining of either lacking field in Group By or missing From clause.

When cloning the templates, removing then putting back the complained fields solved the issue (report can run).

Some cloned templates seem working fine. But the results do not match those generated from the XUL templates.

It seems the issue is related to how the tables are joined. On the report interface, I do not see join type on templates cloned from XUL templates. But if I replace a field, I see join type. When I create a template from scratch I see the join type when choosing a field from a linked table.

tji@sitka.bclibraries.ca (tji) wrote :

Attached are two examples templates in 3 states: before being clone, cloned without editing, cloned with replaced fields from linked tables (following the same path).

tji@sitka.bclibraries.ca (tji) wrote :

another example

Anna Goben (agoben) wrote :

I can confirm we're seeing the same problems with any join that wasn't an INNER for cloned templates.

Kathy Lussier (klussier) on 2018-10-25
Changed in evergreen:
status: New → Confirmed
importance: Undecided → Medium
Remington Steed (rjs7) on 2018-12-13
tags: added: reports
Jason Boyer (jboyer) wrote :

This branch tries to address this by choosing left joins for any relationship types other than has_a (which is supposed to be inner anyway). In my testing it allowed correct XUL->Web report conversion and also helped correct an issue I was having (related to might_have links and field names != 'id').

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jboyer/lp1796945_report_joins / working/user/jboyer/lp1796945_report_joins

tags: added: pullrequest
Changed in evergreen:
milestone: none → 3.3-beta1
importance: Medium → High
Jason Boyer (jboyer) wrote :

That should have read "in my limited testing..." Anyone that's interested please test thoroughly in case I've missed some glaring issue.

Terran McCanna (tmccanna) wrote :

We have applied this code and haven't seen any issues, but we haven't identified any particular reports we had been having problems cloning yet, so not comfortable signing off. Tina, are you able to apply Jason's code to see if it fixes the problems with your reports?

tji@sitka.bclibraries.ca (tji) wrote :

We observed two types of issues with templates cloned from XUL:

1. error when running reports
2. inaccurate result

The fix helps with #2, but does not solve #1.

For #1, we usually see two types of errors: one is missing FROM clause, the other a field needs be in GROUP BY. Below is an example of the latter from today's test. I did not encounter the former today.

DBD::Pg::st execute failed: ERROR: column "f45381a54504218e39aca33492d29306.circ_lib" must appear in the GROUP BY clause or be used in an aggregate function LINE 8: HAVING "f45381a54504218e39aca33492d29306"."circ_lib" IN ($... ^ at /srv/openils/bin/clark-kent.pl line 255.

By comparing the cloned template with a new template directly created on the Webby, I noticed that the cloned template has an empty "where" section, but have the conditions in "having" section (copied an example portion below). Moving the conditions to "where" section solved the problem.

"where":[],"having":[{"alias":"Circulating Library","path .......

Note that I have at least one cloned template that has both "where" and "having" sections, but causes the GROUP BY error. It could be related to individual fields in each sections.

Testing on #2 shows positive result. I chose a template that relies on the hidden left join from Invoice table to Invoice Entry and to Invoice Items. The cloned template with Jason's fix produced the same result as the XUL template. But the one cloned without the fix produced a shorter result list.

More testing will be on the missing FROM clause error and impact of Jason's fix on templates having right join (unlikely an issue).

Tina Ji
BC Libraries Coop

tji@sitka.bclibraries.ca (tji) wrote :

An example that a right join in XUL template was changed to inner join during the cloning.

The XUL template (action.in_house_use right join asset.copy):

"from":{"path":"aihu-item","table":"action.in_house_use","label":"In House Use","alias":"e443e095dd88083d1a9da7e3630b7185","idlclass":"aihu","template_path":"aihu","join":{"item-e443e095dd88083d1a9da7e3630b7185":{"key":"id","type":"right","path":"aihu-item-acp-circ_lib","table":"asset.copy","alias":"67bf5c5e1a824cdc5d44508d00cf2ff7","join":{"id-erfcc-id-20caf18a507c84af49058a17e5f1bc79":{"key":"id","type":"inner","path":"

After the cloning, it became inner join (with the fix on, but I assume the fix is not expected to have impact on this part.)

"from":{"alias":"e443e095dd88083d1a9da7e3630b7185","path":"aihu-aihu","table":"action.in_house_use","idlclass":"aihu","label":"In House Use","join":{"item-156238d5825ba00cfa7fe9ec1979a4c9":{"type":"inner","key":"id","alias":"67bf5c5e1a824cdc5d44508d00cf2ff7","path":"aihu-item","table":"asset.copy","idlclass":"acp","join

Changed in evergreen:
milestone: 3.3-beta1 → 3.3-rc
Dan Wells (dbw2) wrote :

I think the branch posted has the right idea, but as noted, is not going to be right in some cases. Even 'has_a' doesn't necessarily mean an inner join if the data allows the link to be null. (has_a only determines "direction" of the link)

Ultimately we're going to need to pull out the defined joins from the old structure if we really want to do a true conversion, since even if we improve the default logic, the old interface allowed one to override that default and set the join manually. Pulling out the old data didn't seem obvious after looking at least one bug back, but I am not yet familiar with the structural changes.

tags: removed: pullrequest
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers