Uploaded image for project: 'mod-inventory-storage'
  1. mod-inventory-storage
  2. MODINVSTOR-247

filtering /item-storage/items by 'query=barcode==123' does not perform

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      Core: Platform - Sprint 56
    • Story Points:
      3
    • Development Team:
      Core: Platform

      Description

      Filtering /item-storage/items by 'query=barcode==123' does not perform well and generates the following SQL:

      ((jsonb_typeof(items_mt_view.jsonb->'barcode')='number' AND 
          items_mt_view.jsonb->'barcode'=to_jsonb(695582749))
      OR
      (jsonb_typeof(items_mt_view.jsonb->'barcode')<>'number' AND
          items_mt_view.jsonb->'barcode'='"695582749"' AND
          lower(f_unaccent(items_mt_view.jsonb->>'barcode'))='695582749'))
      

      Eric Valuk reported this query from a run on 2019-01-17, note that it doesn't use the view:

      SELECT COUNT(*) FROM fs08000010_mod_inventory_storage.item
      WHERE
      ((jsonb_typeof(item.jsonb->'barcode')='number' AND
          item.jsonb->'barcode'=to_jsonb(6603634596))
      OR
      (jsonb_typeof(item.jsonb->'barcode')<>'number' AND
          item.jsonb->'barcode'='"6603634596"' AND
          lower(f_unaccent(item.jsonb->>'barcode'))='6603634596'))
      

      The barcode is a string and we should be using the fulltext index to search it.

      We assume this can be fixed by marking the index as "fulltext" in the schema.json. (This may be sufficient, otherwise try removing custom view code.)

      Adam Dickmeiss Julian Ladisch Marc Johnson Hongwei Ji Eric Valuk

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                kurt Kurt Nordstrom
                Reporter:
                jakub Jakub Skoczen
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases