[Bug 43241] New: REST API: q= parameter bypasses datetime conversion for values nested in DBIC operators
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Bug ID: 43241 Summary: REST API: q= parameter bypasses datetime conversion for values nested in DBIC operators Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: REST API Assignee: koha-bugs@lists.koha-community.org Reporter: tomascohen@gmail.com QA Contact: testopia@bugs.koha-community.org CC: tomascohen@gmail.com Target Milestone: --- When datetime filter values are passed inside DBIC operators (-and, -or) in the q= parameter, _recursive_fixup is never called on them. This means RFC3339 timestamps (e.g. 2026-08-05T00:00:00Z) reach MySQL unconverted. On tables with many rows (e.g. action_logs), MySQL cannot use the timestamp index when comparing against ISO 8601 format with the T separator, causing full table scans and very slow responses. The root cause is architectural: 1. _parse_dbic_query (in Koha::REST::Plugin::Query) maps field names but does not convert values 2. attributes_from_api (which calls _recursive_fixup for the conversion) is called on the merged result, but only iterates top-level keys 3. When the top-level key is -and or -or, the nested column values are passed through without fixup This affects any kohaTable-based page that combines default_filters with a global search term, since the DataTables layer wraps the combined query in -and. The fix: - Move attributes_from_api to process named query params before merging with q params - Teach _parse_dbic_query to call _recursive_fixup when it encounters a key that maps to a known column This ensures RFC3339 values are converted to MySQL-native format regardless of query nesting depth. Related: Bug 43208 (reported as a viewlog performance issue, same root cause) -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|normal |major Summary|REST API: q= parameter |`q=` parameter bypasses |bypasses datetime |datetime conversion for |conversion for values |values nested in DBIC |nested in DBIC operators |operators Assignee|koha-bugs@lists.koha-commun |tomascohen@gmail.com |ity.org | -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |nick@bywatersolutions.com --- Comment #1 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- *** Bug 43208 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrew@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@gmail.com, | |martin.renvoize@openfifth.c | |o.uk, | |pedro.amorim@openfifth.co.u | |k -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff Patch complexity|--- |Small patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #2 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 203279 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203279&action=edit Bug 43241: Add tests for datetime/boolean fixup inside DBIC operators This patch adds regression tests proving that RFC3339 datetime values and boolean values nested inside DBIC operators (-and, -or, -between) in q= queries are not converted to their DB-native format. Unit tests (Query.t): - Datetime: >=, <=, range, -or, -between, nested -and/-or, me. prefix - Boolean: true/false inside -and, -or, nested -and within -or - Non-datetime values pass through unchanged Integration tests (Objects.t): - Full API stack tests using action_logs with controlled data - Same operator/nesting coverage as unit tests Test plan: 1. Apply patch 2. Run: $ prove t/Koha/REST/Plugin/Query.t $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => FAIL: Tests fail! Values inside -and are not converted 3. Sign off :-D Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #3 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 203280 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203280&action=edit Bug 43241: Fix datetime/boolean conversion in q= queries with DBIC operators _parse_dbic_query maps field names but did not convert values. When datetime or boolean filters are nested inside DBIC operators (-and, -or) in the q= parameter, attributes_from_api (which normally handles the conversion via _recursive_fixup) cannot reach them because it only iterates top-level keys. This patch fixes the issue in two places: 1. _parse_dbic_query (Query.pm): When a key maps to a known column (on the primary table or a related object), call _recursive_fixup to convert RFC3339 timestamps to MySQL format and booleans to 0/1. Related object instances are cached on the result_set to avoid repeated prefetch_whitelist lookups during recursive parsing. 2. Objects.pm: Move attributes_from_api to process named query params before merging with q= params. This avoids double-processing since q= values are already fixed up by _parse_dbic_query. Test plan: 1. Apply the regression tests from the previous commit 2. Run: $ prove t/Koha/REST/Plugin/Query.t $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => FAIL: Tests fail! 3. Apply this patch 4. Repeat step 2 => SUCCESS: Tests pass! 5. Sign off :-D Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #4 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Just found an edge case. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #203279|0 |1 is obsolete| | Attachment #203280|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #5 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 203286 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203286&action=edit Bug 43241: Add tests for datetime/boolean fixup inside DBIC operators This patch adds regression tests proving that RFC3339 datetime values and boolean values nested inside DBIC operators (-and, -or, -between) in q= queries are not converted to their DB-native format. Unit tests (Query.t): - Datetime: >=, <=, range, -or, -between, nested -and/-or, me. prefix - Boolean: true/false inside -and, -or, nested -and within -or - Non-datetime values pass through unchanged Integration tests (Objects.t): - Full API stack tests using action_logs with controlled data - Same operator/nesting coverage as unit tests Test plan: 1. Apply patch 2. Run: $ prove t/Koha/REST/Plugin/Query.t $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => FAIL: Tests fail! Values inside -and are not converted 3. Sign off :-D Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #6 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 203287 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203287&action=edit Bug 43241: Fix datetime/boolean conversion in q= queries with DBIC operators _parse_dbic_query maps field names but did not convert values. When datetime or boolean filters are nested inside DBIC operators (-and, -or) in the q= parameter, attributes_from_api (which normally handles the conversion via _recursive_fixup) cannot reach them because it only iterates top-level keys. This patch fixes the issue in two places: 1. _parse_dbic_query (Query.pm): When a key maps to a known column (on the primary table or a related object), call _recursive_fixup to convert RFC3339 timestamps to MySQL format and booleans to 0/1. Related object instances are cached on the result_set to avoid repeated prefetch_whitelist lookups during recursive parsing. 2. Objects.pm: Move attributes_from_api to process named query params before merging with q= params. This avoids double-processing since q= values are already fixed up by _parse_dbic_query. Test plan: 1. Apply the regression tests from the previous commit 2. Run: $ prove t/Koha/REST/Plugin/Query.t $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => FAIL: Tests fail! 3. Apply this patch 4. Repeat step 2 => SUCCESS: Tests pass! 5. Sign off :-D Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #7 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 203288 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203288&action=edit Bug 43241: (follow-up) Use form => for q= params in tests The + character in RFC3339 timezone offsets (e.g. +00:00) is decoded as a space when interpolated directly into a URL query string. Use Test::Mojo form => { q => ... } which properly URL-encodes the parameter value. Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #203286|0 |1 is obsolete| | Attachment #203287|0 |1 is obsolete| | Attachment #203288|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #8 from David Nind <david@davidnind.com> --- Created attachment 203314 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203314&action=edit Bug 43241: Add tests for datetime/boolean fixup inside DBIC operators This patch adds regression tests proving that RFC3339 datetime values and boolean values nested inside DBIC operators (-and, -or, -between) in q= queries are not converted to their DB-native format. Unit tests (Query.t): - Datetime: >=, <=, range, -or, -between, nested -and/-or, me. prefix - Boolean: true/false inside -and, -or, nested -and within -or - Non-datetime values pass through unchanged Integration tests (Objects.t): - Full API stack tests using action_logs with controlled data - Same operator/nesting coverage as unit tests Test plan: 1. Apply patch 2. Run: $ prove t/Koha/REST/Plugin/Query.t $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => FAIL: Tests fail! Values inside -and are not converted 3. Sign off :-D Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #9 from David Nind <david@davidnind.com> --- Created attachment 203315 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203315&action=edit Bug 43241: Fix datetime/boolean conversion in q= queries with DBIC operators _parse_dbic_query maps field names but did not convert values. When datetime or boolean filters are nested inside DBIC operators (-and, -or) in the q= parameter, attributes_from_api (which normally handles the conversion via _recursive_fixup) cannot reach them because it only iterates top-level keys. This patch fixes the issue in two places: 1. _parse_dbic_query (Query.pm): When a key maps to a known column (on the primary table or a related object), call _recursive_fixup to convert RFC3339 timestamps to MySQL format and booleans to 0/1. Related object instances are cached on the result_set to avoid repeated prefetch_whitelist lookups during recursive parsing. 2. Objects.pm: Move attributes_from_api to process named query params before merging with q= params. This avoids double-processing since q= values are already fixed up by _parse_dbic_query. Test plan: 1. Apply the regression tests from the previous commit 2. Run: $ prove t/Koha/REST/Plugin/Query.t $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => FAIL: Tests fail! 3. Apply this patch 4. Repeat step 2 => SUCCESS: Tests pass! 5. Sign off :-D Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #10 from David Nind <david@davidnind.com> --- Created attachment 203316 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203316&action=edit Bug 43241: (follow-up) Use form => for q= params in tests The + character in RFC3339 timezone offsets (e.g. +00:00) is decoded as a space when interpolated directly into a URL query string. Use Test::Mojo form => { q => ... } which properly URL-encodes the parameter value. Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 mteal@greenvillelibrary.org changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mteal@greenvillelibrary.org -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Lisette Scheer <lisette@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |jonathan.druart@gmail.com |y.org | CC| |lisette@bywatersolutions.co | |m -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=37831 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #203314|0 |1 is obsolete| | Attachment #203315|0 |1 is obsolete| | Attachment #203316|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #11 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 204089 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204089&action=edit Bug 43241: Add tests for datetime/boolean fixup inside DBIC operators This patch adds regression tests proving that RFC3339 datetime values and boolean values nested inside DBIC operators (-and, -or, -between) in q= queries are not converted to their DB-native format. Unit tests (Query.t): - Datetime: >=, <=, range, -or, -between, nested -and/-or, me. prefix - Boolean: true/false inside -and, -or, nested -and within -or - Non-datetime values pass through unchanged Integration tests (Objects.t): - Full API stack tests using action_logs with controlled data - Same operator/nesting coverage as unit tests Test plan: 1. Apply patch 2. Run: $ prove t/Koha/REST/Plugin/Query.t $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => FAIL: Tests fail! Values inside -and are not converted 3. Sign off :-D Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #12 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 204090 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204090&action=edit Bug 43241: Fix datetime/boolean conversion in q= queries with DBIC operators _parse_dbic_query maps field names but did not convert values. When datetime or boolean filters are nested inside DBIC operators (-and, -or) in the q= parameter, attributes_from_api (which normally handles the conversion via _recursive_fixup) cannot reach them because it only iterates top-level keys. This patch fixes the issue in two places: 1. _parse_dbic_query (Query.pm): When a key maps to a known column (on the primary table or a related object), call _recursive_fixup to convert RFC3339 timestamps to MySQL format and booleans to 0/1. Related object instances are cached on the result_set to avoid repeated prefetch_whitelist lookups during recursive parsing. 2. Objects.pm: Move attributes_from_api to process named query params before merging with q= params. This avoids double-processing since q= values are already fixed up by _parse_dbic_query. Test plan: 1. Apply the regression tests from the previous commit 2. Run: $ prove t/Koha/REST/Plugin/Query.t $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => FAIL: Tests fail! 3. Apply this patch 4. Repeat step 2 => SUCCESS: Tests pass! 5. Sign off :-D Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #13 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 204091 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204091&action=edit Bug 43241: (follow-up) Use form => for q= params in tests The + character in RFC3339 timezone offsets (e.g. +00:00) is decoded as a space when interpolated directly into a URL query string. Use Test::Mojo form => { q => ... } which properly URL-encodes the parameter value. Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #14 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 204092 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204092&action=edit Bug 43241: Add tests for timezone support in RFC3339 q= queries Co-Authored-By: Mistral Vibe <vibe@mistral.ai> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #15 from Kristi Krueger <kkrueger@cuyahogalibrary.org> --- Just making a reminder note for myself (and others interested) that a use case here is performance speed when searching tables, especially in the log viewer. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #16 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- (In reply to Kristi Krueger from comment #15)
Just making a reminder note for myself (and others interested) that a use case here is performance speed when searching tables, especially in the log viewer.
The impact is on any date/datetime column lookup. Not being able to use the index makes it a LIKE query on the whole table! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |26.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43241 --- Comment #17 from Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> --- Thanks everyone! Pushed to main for 26.11! -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org