Web client report template errors when using sources with virtual fields

Bug #1721807 reported by Remington Steed on 2017-10-06
48
This bug affects 8 people
Affects Status Importance Assigned to Milestone
Evergreen
High
Unassigned
2.12
High
Unassigned
3.0
High
Unassigned

Bug Description

Evergreen 3.0
Web client (but not XUL client)

Certain types of report templates created with the new web client template editor generate errors when running a report. However, an identical template created with the XUL interface does not cause errors. The problem seems to be related to how virtual fields are used to generate the SQL.

For example, consider this report template:

Source: Bibliographic Record

Fields:
  - Bibliographic Record: Record ID
  - Bibliographic Record -> Simple Record Extracts: Author (normalized)
  - Bibliographic Record -> Simple Record Extracts: Title (normalized)

Create this same template using both the new web client template editor and the XUL template editor. Running a report from the XUL template succeeds. However, running a report from the web client template results in an error displayed in the "outputs" table row:

DBD::Pg::st execute failed: ERROR: column d94d9ed7d30666ea41bbd3190401885c.simple_record does not exist LINE 5: ..._record AS "7e7a63baa086b0371331e503b7479689" ON ("d94d9ed7d... ^ at /openils/bin/clark-kent.pl line 255.

Andrea Neiman (aneiman) wrote :

Confirmed on 3.0 web client

Changed in evergreen:
status: New → Confirmed
tags: added: webstaffclient
Andrea Neiman (aneiman) wrote :

Forgot to add, in my testing this seemed to be failing only with reports that used joins across multiple sources -- if I ran a report where all the fields/filters are from Simple Record Extracts, it ran as expected & delivered the expected output.

Here's an example of one I ran with no issues:

Source Path Column Label Data Type Field Transform
Simple Record Extracts Author (normalized) text Raw Data
Simple Record Extracts Title Proper (normalized) text Raw Data
Simple Record Extracts Record ID id Raw Data

Filter: Author contains matching substring (ignore case)

Andrea Neiman (aneiman) wrote :

Ugh, sorry for that formatting...

Source Path | Column Label | Data Type | Field Transform
Simple Record Extracts | Author (normalized) | text | Raw Data
Simple Record Extracts | Title Proper (normalized) | text | Raw Data
Simple Record Extracts | Record ID | id | Raw Data

Robert J Jackson (rjackson) wrote :

Attempting to convert a Legacy Staff Client Report to Web Client and received similar error on the following source / field: Item->Total Circulations->Total Circulation Count

Mike Rylander (mrylander) wrote :

Remington,

This will be a long-shot, but do you happen to have the report output from the original error? That should have, behind a "debug" link, the raw SQL as constructed.

Thanks!

Remington Steed (rjs7) wrote :

Mike,

When I click "View report output" for the broken report, I get an error page:

"Not Found

The requested URL /reporter/271/405/11426/report-data.html was not found on this server."

The raw SQL is probably in our logs, but it may be faster for you to create the example report in the original bug (which still breaks when created with the 3.0.2 web client, I just confirmed). Sorry I'm not more help right now.

Chris Sharp (chrissharp123) wrote :

Mike,

I just hit this bug (different virtual field) and here's the output from the failure email:

Your report, named [chrl lang -- Circulation Count by Language (by Item-Owning Library)], scheduled to run at 2018-01-29 08:12:20.625762-05,
has failed with the following error message:

 DBD::Pg::st execute failed: ERROR: column d3305dbc9fd90da113198381c9f3fe8c.fixed_fields does not exist
LINE 8: ...criptor AS "0aefe22da96118fcece72b2cdd68bfff" ON ("d3305dbc9...
                                                             ^ at /openils/bin/clark-kent.pl line 255.

External documentation for the template is available at the following URL:

The SQL command attempted was:

 SELECT * FROM (SELECT "0af99bc785606cc26f77cf2eaccf4ba0"."shortname" AS "Owning Library",
        "f2f7c7866103ae9fa4aa903b0daf9102"."value" AS "Language",
        COUNT("f45381a54504218e39aca33492d29306"."id") AS "Circ ID"
  FROM action.circulation AS "f45381a54504218e39aca33492d29306"
        INNER JOIN asset.copy AS "c04d21c9fe3de7845ae87a892351d59c" ON ("f45381a54504218e39aca33492d29306"."target_copy" = "c04d21c9fe3de7845ae87a892351d59c"."id")
        INNER JOIN asset.call_number AS "8e2b33229707683d00a4879c97d184c2" ON ("c04d21c9fe3de7845ae87a892351d59c"."call_number" = "8e2b33229707683d00a4879c97d184c2"."id")
        INNER JOIN biblio.record_entry AS "d3305dbc9fd90da113198381c9f3fe8c" ON ("8e2b33229707683d00a4879c97d184c2"."record" = "d3305dbc9fd90da113198381c9f3fe8c"."id")
        INNER JOIN metabib.rec_descriptor AS "0aefe22da96118fcece72b2cdd68bfff" ON ("d3305dbc9fd90da113198381c9f3fe8c"."fixed_fields" = "0aefe22da96118fcece72b2cdd68bfff"."record")
        INNER JOIN config.language_map AS "f2f7c7866103ae9fa4aa903b0daf9102" ON ("0aefe22da96118fcece72b2cdd68bfff"."item_lang" = "f2f7c7866103ae9fa4aa903b0daf9102"."code")
        INNER JOIN actor.org_unit AS "0af99bc785606cc26f77cf2eaccf4ba0" ON ("8e2b33229707683d00a4879c97d184c2"."owning_lib" = "0af99bc785606cc26f77cf2eaccf4ba0"."id")
  WHERE "0af99bc785606cc26f77cf2eaccf4ba0"."id" IN ($_156418$217$_156418$)
        AND date("f45381a54504218e39aca33492d29306"."xact_start") BETWEEN $_156418$2017-07-29$_156418$ AND $_156418$2018-01-29$_156418$
  GROUP BY 1, 2
  ORDER BY "0af99bc785606cc26f77cf2eaccf4ba0"."shortname" ASC, "f2f7c7866103ae9fa4aa903b0daf9102"."value" ASC, COUNT("f45381a54504218e39aca33492d29306"."id") ASC
) limited_to_1048575_hits LIMIT 1048575

If you are unsure of the meaning of this message, please contact your
ILS adminstrator or support staff and give them both the error message
and the SQL command.

Scott Thomas (scott-thomas-9) wrote :

We have been seeing this one a lot, but we have been unable to determine a pattern. Some reports run fine.

Chris Sharp (chrissharp123) wrote :
Download full text (6.0 KiB)

Marking this with webstaffblocker and high status, since this prevents our libraries from moving fully to the web client. I have two more examples from today alone of templates that will not work in the web client because it's not virtual-fields-aware:

Example 1:

Your report, named [chrl patrons -- Patron Addresses for Mapping], scheduled to run at 2018-02-28 16:34:26.955515-05,
has failed with the following error message:

 DBD::Pg::st execute failed: ERROR: column fe21ca9ecd516f33ec379ecc11aa7051.addresses does not exist
LINE 13: ...address AS "d7be6249d9d2944ea6cf72e7993d6fa2" ON ("fe21ca9ec...
                                                              ^ at /openils/bin/clark-kent.pl line 255.

External documentation for the template is available at the following URL:

The SQL command attempted was:

 SELECT * FROM (SELECT "c59fb2c005a910a9e0ffc9739d9ec4ca"."shortname" AS "Home Library",
        "fbf10dea4b72d516ca3673f5d55f2781"."barcode" AS "Barcode",
        "fe21ca9ecd516f33ec379ecc11aa7051"."expire_date" AS "Privilege Expiration Date",
        "d7be6249d9d2944ea6cf72e7993d6fa2"."street1" AS "Street (1)",
        "d7be6249d9d2944ea6cf72e7993d6fa2"."street2" AS "Street (2)",
        "d7be6249d9d2944ea6cf72e7993d6fa2"."city" AS "City",
        "d7be6249d9d2944ea6cf72e7993d6fa2"."state" AS "State",
        "d7be6249d9d2944ea6cf72e7993d6fa2"."county" AS "County",
        "d7be6249d9d2944ea6cf72e7993d6fa2"."post_code" AS "Postal Code"
  FROM actor.usr AS "fe21ca9ecd516f33ec379ecc11aa7051"
        INNER JOIN actor.card AS "fbf10dea4b72d516ca3673f5d55f2781" ON ("fe21ca9ecd516f33ec379ecc11aa7051"."card" = "fbf10dea4b72d516ca3673f5d55f2781"."id")
        INNER JOIN actor.org_unit AS "c59fb2c005a910a9e0ffc9739d9ec4ca" ON ("fe21ca9ecd516f33ec379ecc11aa7051"."home_ou" = "c59fb2c005a910a9e0ffc9739d9ec4ca"."id")
        INNER JOIN actor.usr_address AS "d7be6249d9d2944ea6cf72e7993d6fa2" ON ("fe21ca9ecd516f33ec379ecc11aa7051"."addresses" = "d7be6249d9d2944ea6cf72e7993d6fa2"."usr")
  WHERE "c59fb2c005a910a9e0ffc9739d9ec4ca"."id" IN ($_49377$216$_49377$,$_49377$219$_49377$,$_49377$220$_49377$,$_49377$217$_49377$,$_49377$218$_49377$)
        AND "fe21ca9ecd516f33ec379ecc11aa7051"."deleted" = $_49377$false$_49377$
        AND "fe21ca9ecd516f33ec379ecc11aa7051"."active" = $_49377$true$_49377$
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
  ORDER BY "c59fb2c005a910a9e0ffc9739d9ec4ca"."shortname" ASC, "fbf10dea4b72d516ca3673f5d55f2781"."barcode" ASC, "fe21ca9ecd516f33ec379ecc11aa7051"."expire_date" ASC, "d7be6249d9d2944ea6cf72e7993d6fa2"."street1" ASC, "d7be6249d9d2944ea6cf72e7993d6fa2"."street2" ASC, "d7be6249d9d2944ea6cf72e7993d6fa2"."city" ASC, "d7be6249d9d2944ea6cf72e7993d6fa2"."state" ASC, "d7be6249d9d2944ea6cf72e7993d6fa2"."county" ASC, "d7be6249d9d2944ea6cf72e7993d6fa2"."post_code" ASC
) limited_to_1048575_hits LIMIT 1048575

If you are unsure of the meaning of this message, please contact your
ILS adminstrator or support staff and give them both the error message
and the SQL command.

Example 2:

Your report, named [fhbc -- Item List by Shelving Location], scheduled to run at 2018-02-28 13:42:20.254371-05,
has failed with the following error mess...

Read more...

tags: added: webstaffblocker
Changed in evergreen:
importance: Medium → High
Chris Sharp (chrissharp123) wrote :

Adding the template and report JSON data from "Example 1" in Comment #9.

Chris Sharp (chrissharp123) wrote :
Galen Charlton (gmc) on 2018-03-06
Changed in evergreen:
assignee: nobody → Galen Charlton (gmc)
Galen Charlton (gmc) wrote :

A work-in-progress patch is available in the user/gmcharlt/lp1721807_webstaff_reporter_joins branch. This patch has been lightly tested and seems to fix the problem, so feedback and additional testing would be helpful; the final branch will include a unit test or two.

Chris Sharp (chrissharp123) wrote :

I can confirm that this branch fixes the three example reports I listed above. If there's any more testing or feedback you'd like, I'm happy to do it. Or I'm happy to go ahead and sign off - either way.

Galen Charlton (gmc) wrote :

Thanks for testing, Chris.

I've force-pushed to user/gmcharlt/lp1721807_webstaff_reporter_joins a two-patch series. The first patch is an automated unit test, while the second patch is the same as what Chris tested with a minor change to a comment. Now requesting testing and signoff:

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

tags: added: pullrequest
Changed in evergreen:
assignee: Galen Charlton (gmc) → nobody
milestone: none → 3.0.5
Kathy Lussier (klussier) on 2018-03-19
Changed in evergreen:
assignee: nobody → Kathy Lussier (klussier)
Kathy Lussier (klussier) wrote :

It works for me too. I've merged Galen's code with Chris' signoff to master, release 3.0 and release 2.12. Thank you Remington, Andrea, Scott, Galen and Chris!

Changed in evergreen:
assignee: Kathy Lussier (klussier) → nobody
status: Confirmed → Fix Committed
milestone: 3.0.5 → 3.1-rc
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers