After the latest Drupal 7 update, while we were verifying that everything still works on our website, I came across a problem searching for a test forum entry / reply comment by typing in "ha ha" into the search field. The error I got from the API was the following:

SQLSTATE[42S02]: Base table or view not found: 1146 Table '<database>.t' doesn't exist

I get this error every time I search for a string with multiple two-letter-words divided by a whitespace or a special character (' ', '%', ','). It does not seem to matter how many two character words I use, so it happens for "ha ha" but also for "ha pu do", "ha pu do mi", etc.

As soon as either of those space separated words contains more than two letters it works fine again ("haa ha", "ha haa", "haa pu do", "ha puu do", ...), as is the case for a single two-letter word (just "ha"). It also does not seem to matter whether the query uses a node or user index, or if the operator is = or LIKE, I get the same error no matter what. So it can be as simple as:

Index: <name>_user_index
Keys: NULL
Filters:
    name = 'ha pu do mi'
Options: array (
    'conjunction' => 'AND',
    'parse mode' => 'terms',
    'filter class' => 'SearchApiQueryFilter',
    'search id' => 'SearchApiQuery',
  )

or as complex as:

Index: <name>_node_index
Keys: NULL
Filters:
    type = 'forums'
  AND
    [
      title LIKE 'ha pu do mi'
      OR
        body:value LIKE 'ha pu do mi'
      OR
        author:name LIKE 'ha pu do mi'
      OR
        taxonomy_forums:name LIKE 'ha pu do mi'
      OR
        taxonomy_forums:description LIKE 'ha pu do mi'
      OR
        taxonomy_forums:parents_all:name LIKE 'ha pu do mi'
      OR
        taxonomy_forums:parents_all:description LIKE 'ha pu do mi'
      OR
        taxonomy_forums:parents_all:parents_all:name LIKE 'ha pu do mi'
      OR
        taxonomy_forums:parents_all:parents_all:description LIKE 'ha pu do mi'
    ]
Options: array (
    'conjunction' => 'AND',
    'parse mode' => 'terms',
    'filter class' => 'SearchApiQueryFilter',
    'search id' => 'SearchApiQuery',
  )

It always seems to lose the table name for any subqueries it attempts to do:

SELECT COUNT(*) AS expression
FROM 
(SELECT DISTINCT t.item_id AS item_id, :score AS score, 1 AS expression
FROM 
{search_api_db_<name>_user_index} t
WHERE ( (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) )) subquery
SELECT COUNT(*) AS expression
FROM 
(SELECT DISTINCT t.item_id AS item_id, :score AS score, 1 AS expression
FROM 
{search_api_db_<name>_node_index} t
WHERE ( (t.type = :db_condition_placeholder_0) AND( (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) OR (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) OR (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) OR (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) OR (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) OR (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) OR (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) OR (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) OR (t.item_id IN  (SELECT t.item_id AS item_id
FROM 
{} t)) ))) subquery

though with all of the sub-queries receiving the alias `t` I get the feeling it messes up in that regard already, not just in dropping the table names (but as I never attempted something like this myself, I cannot be sure).

I also tried to add the "%" wildcard automatically in between words and at the beginning and end of the search string when the operator is LIKE, in case that might help in any way, however that did not result in any change of the output (this actually only worked for any fields that had :parents_all in it, for all other fields the % character was changed into a single space).

I already re-index'd everything, ran the cron (to check for further updates), Drupal 7 and Search API are current and unchanged, though this issue is already present on the live version of our website, which is still using Search API v 7.x-1.13, so it seems the issue has been around for a while already.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

drunken monkey’s picture

Project: Search API » Search API Database Search
Version: 7.x-1.15 » 7.x-1.x-dev
Component: Framework » Code
Status: Active » Needs review
FileSize
1.46 KB
943 bytes

This patch (the one without the "tests_only" suffix) should fix it, please test!

Status: Needs review » Needs work

The last submitted patch, 1: 2511860-1--filter_on_multiple_short_words--tests_only.patch, failed testing.

The last submitted patch, 1: 2511860-1--filter_on_multiple_short_words.patch, failed testing.

drunken monkey’s picture

Re-posting.

Status: Needs review » Needs work

The last submitted patch, 4: 2511860-1--filter_on_multiple_short_words--tests_only.patch, failed testing.

drunken monkey’s picture

Project: Search API Database Search » Search API
Version: 7.x-1.x-dev » 8.x-1.x-dev
Component: Code » Database backend
Status: Needs work » Patch (to be ported)
Issue tags: +Novice

Looks good. Would have been great if someone could have verified this works, but since the test bot is happy, let's just hope for the best.
Committed.

Moving to Search API 8.x for porting (since I guess the same bug is in the D8 version).

The last submitted patch, 7: 2511860-7--filter_on_multiple_short_words--tests_only.patch, failed testing.

The last submitted patch, 7: 2511860-7--filter_on_multiple_short_words--tests_only.patch, failed testing.

Status: Needs review » Needs work

The last submitted patch, 7: 2511860-7--filter_on_multiple_short_words.patch, failed testing.

The last submitted patch, 7: 2511860-7--filter_on_multiple_short_words.patch, failed testing.

The last submitted patch, 12: 2511860-12--filter_on_multiple_short_words--tests_only.patch, failed testing.

The last submitted patch, 12: 2511860-12--filter_on_multiple_short_words--tests_only.patch, failed testing.

Status: Needs review » Needs work

The last submitted patch, 15: 2511860-15--filter_on_multiple_short_words.patch, failed testing.

The last submitted patch, 15: 2511860-15--filter_on_multiple_short_words.patch, failed testing.

  • drunken monkey committed a7b4ecb on 8.x-1.x
    Issue #2511860 by drunken monkey: Fixed errors when search for multiple...
drunken monkey’s picture

Status: Needs work » Fixed

Seems to work fine now, so: committed.

Status: Fixed » Closed (fixed)

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