Contrib.social

Inconsistencies in database server and search keys processing

Open on Drupal.org →
Created on 11 February 2025, 3 months ago

Problem/Motivation

There is inconsistency between the way the data is stored in the database server and the way search keys work. For example, if you have a full-text field for a product with an SKU of "FOO-012345", with stemming and tokenization index preprocessors activated, it will be saved in the database as follows:

+--------------------------------+--------------+------------+---+
| entity:commerce_product/1:en   | sku_fulltext | 12345      | 0 |
| entity:commerce_product/1:en   | sku_fulltext | foo        | 0 |
| entity:commerce_product/1:en   | sku_fulltext | foo 12345  | 0 |
+--------------------------------+--------------+------------+---+

But when you try to search for the keys "FOO-012345", it is parsed like this:

Index: search\n
Keys: 'FOO-012345'\n
Parsed keys: 'foo 012345'\n
Searched fields: [ALL]\n

The query doesn't know about any special processing done by the database server. This is done in the Drupal\search_api_db\Plugin\search_api\backend\Database::cleanNumericString() method. Basically, this method alters the data when adding new data to the database, breaking consistency with the original data. As a result, the query will not be able to find the product.

Steps to reproduce

  • Create a product with an SKU such as "FOO-012345".
  • Enable Stemmer and Tokenizer index preprocessors.
  • Attempt to search for the product using the key "FOO-012345".
  • Observe that the product is not found in the search results.

Proposed Resolution

I’m not sure how to resolve this issue because the database server and key parsers appear to be completely disconnected. Removing the trimming could lead to issues, especially considering its intended purpose as mentioned in #2451745: Why trimming leading zeros in text and token types? → .

Maybe possible solution is to introduce a hook or event for key parsing and implement it for the database server. This would modify the parsed keys from Parsed keys: 'foo 012345'\n to Parsed keys: 'foo 12345'\n, aligning them with the data in the database and ensuring accurate search results.

🐛 Bug report
Status

Active

Version

1.37

Component

Database backend

Created by

🇷🇺Russia niklan Russia, Perm

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Merge Requests

Comments & Activities

  • Issue created by @niklan
  • Comment 3 months ago →
    🇷🇺Russia niklan Russia, Perm

    I've prepared a patch for the tokenizer which basically "emulates" the behavior of the database server. This might be helpful for someone, but it's a dirty solution and should only work in most cases. It hasn't been tested with other search server types, so it will probably require reindexing indexes. However, since it's only related to database servers and is likely used when there's no dedicated search engine, it shouldn't cause any problems unless multiple servers are used together.

  • Comment 3 months ago →
    🇦🇹Austria drunken monkey Vienna, Austria

    drunken monkey → made their first commit to this issue’s fork.

  • Merge request !212Resolve #3505734: Fix problems with leading zeros. → (Merged) created by drunken monkey
  • Comment 3 months ago →
    🇦🇹Austria drunken monkey Vienna, Austria

    Thanks for reporting this issue!
    You are right, this is currently not handled correctly. Luckily, there is an easy way to fix this right in the database backend, namely at the point where we convert the search keywords to an SQL query.

    Please try out the MR to see if this fixes the problem for you. (I even spotted an unrelated second bug, fixed that, too.)

  • Comment 3 months ago →
    🇷🇺Russia niklan Russia, Perm

    Thank you. I've tested it and it fixed the issue.

    Now, I can even see that parsed keys do not contain any leading zeroes and it makes sense in relation to the data within the index itself.

    Index: search\n
Keys: 'FOO-012345'\n
Parsed keys: 'foo 12345'\n
Searched fields: [ALL]\n
  • Pipeline finished with Skipped
    3 months ago
    #431827
  • Comment 3 months ago →
    System Message
  • Comment 3 months ago →
    🇦🇹Austria drunken monkey Vienna, Austria

    Good to hear, thanks a lot for testing!
    Merged.

  • Comment 2 months ago →
    System Message

    Automatically closed - issue fixed for 2 weeks with no activity.

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024