Cannot Reproduce
Details
Details
Assignee
Kurt Nordstrom
Kurt NordstromReporter
Lisa McColl
Lisa McCollPriority
P2Sprint
None
Development Team
Thor
RCA Group
TBD
Affected Institution
Lehigh
TestRail: Cases
Open TestRail: Cases
TestRail: Runs
Open TestRail: Runs
Created September 28, 2022 at 12:24 PM
Updated June 17, 2024 at 12:13 PM
Resolved February 7, 2023 at 6:59 AM
Overview: Some tables created for the LDP local folder behave as expected in the LDP app, others generate an error that appears like this:
Steps to Reproduce:
Create a table in a 'local' folder in the LDP using this query:
DROP TABLE IF EXISTS local.instance_notes;-- Create a local table for notes in instance records that includes the type id and name. Here note can be either public or for staff. CREATE TABLE local.instance_notes AS SELECT instances.id AS instance_id, instances.hrid AS instance_hrid, json_extract_path_text(notes.data, 'instanceNoteTypeId') AS note_type_id, instance_note_types.name AS note_type_name, json_extract_path_text(notes.data, 'note') AS note, json_extract_path_text(notes.data, 'staffOnly')::boolean AS staff_only FROM inventory_instances AS instances CROSS JOIN json_array_elements(json_extract_path(data, 'notes')) AS notes (data) LEFT JOIN inventory_instance_note_types AS instance_note_types ON json_extract_path_text(notes.data, 'instanceNoteTypeId') = instance_note_types.id;CREATE INDEX ON local.instance_notes (instance_id);CREATE INDEX ON local.instance_notes (instance_hrid);CREATE INDEX ON local.instance_notes (note_type_id);CREATE INDEX ON local.instance_notes (note_type_name);-- This field is not indexed by default; the values may be too large. -- CREATE INDEX ON local.instance_notes (note);CREATE INDEX ON local.instance_notes (staff_only);Log into the FOLIO environment that the LDP in step one is related to
Go to the LDP app, and select "local" from the drop down menu. Select instance_notes from the drop down menu under table
Click Submit - you will see the expected results showing the instance_notes table.
Go back to the LDP and drop the instance_notes table. Refresh the local folder.
Still in the LDP, create the same table but with one change in the name:
CREATE TABLE local.instance_xnotes AS SELECT instances.id AS instance_id, instances.hrid AS instance_hrid, json_extract_path_text(notes.data, 'instanceNoteTypeId') AS note_type_id, instance_note_types.name AS note_type_name, json_extract_path_text(notes.data, 'note') AS note, json_extract_path_text(notes.data, 'staffOnly')::boolean AS staff_only FROM inventory_instances AS instances CROSS JOIN json_array_elements(json_extract_path(data, 'notes')) AS notes (data) LEFT JOIN inventory_instance_note_types AS instance_note_types ON json_extract_path_text(notes.data, 'instanceNoteTypeId') = instance_note_types.id;CREATE INDEX ON local.instance_notes (instance_id);CREATE INDEX ON local.instance_notes (instance_hrid);CREATE INDEX ON local.instance_notes (note_type_id);CREATE INDEX ON local.instance_notes (note_type_name);-- This field is not indexed by default; the values may be too large. -- CREATE INDEX ON local.instance_notes (note);CREATE INDEX ON local.instance_notes (staff_only);Go back to the FOLIO LDP app and repeat step 3, this time selecting the table named "instance_xnotes"
Expected Results: When clicking Submit the same table should appear as was seen prior to the table name change
Actual Results:
Additional Information:
This error seems to get triggered easily - this name change was one way I could reliably reproduce it. If I create a table with a simple query I get the same error message for example the table created with this query results in the same error:
CREATE TABLE local.lehigh_authors AS SELECT ii.hrid AS instance_hrid, ll2.location_name, ih.call_number, ii.title, ip.date_of_publication, ii.id AS instance_uuid FROM inventory_instances ii LEFT JOIN folio_reporting.instance_notes in2 ON in2.instance_id = ii.id LEFT JOIN folio_reporting.instance_publication ip ON ip.instance_id = ii.id LEFT JOIN inventory_holdings ih ON ih.instance_id = ii.id LEFT JOIN folio_reporting.locations_libraries ll2 ON ll2.location_id = ih.permanent_location_id WHERE in2.note like 'Lehigh Faculty Author%';