Web client report template errors when using sources with virtual fields

Confirmed on 3.0 web client

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)

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

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

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!

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.

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.

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

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...

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

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.

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.

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

Signoff branch here:

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

Thanks, Galen.

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!