Arvados

• Yes, users want to be able to find files called "example_abc123-FOO.bar" by searching "example_abc123"
• to_tsquery('example_abc123:*') matches that example. plainto_tsquery('example_abc123:*') does not match.
• Both to_tsquery('example_abc123-FOO.bar:*') and plainto_tsquery('example_abc123-FOO.bar') succeed.
• Neither form matches with just 'abc123' or 'abc123:*'.
• select ... where column @@ to_tsquery('and:*') returns no rows, and emits lots of logs (one per row?), "query contains only stop words or doesn't contain lexemes". Likewise to_tsquery('').

• type "foo:bar" → workbench sends "foo:bar:*" &arr; postgresql syntax error
• type "'foo:bar'" → workbench sends "'foo:bar':*" → interpreted by postgresql as 'foo':* & 'bar':*
• type "and" → workbench sends "and:*" → postgresql matches nothing because "and" is a stop word (ditto "a")
• type "foo & bar" → workbench sends "foo & bar:*" → apiserver translates to "foo & & & bar:*" → postgresql syntax error (ditto "foo | bar")
• type "'foo'" → ok
• type "''foo''" → workbench sends "''foo'':*" → postgresql syntax error
• type "'foo & bar'" → workbench sends "'foo & bar':*" → apiserver translates to "'foo & & & bar':*" → matches same results as typing "foo bar"
• type "foo|bar" → workbench sends "foo|bar:*" → postgres searches "foo|bar:*" (with the "or" operator)
• "(foo)" → "(foo):*" → error
• "(foo)a" → "(foo)a:*" → error
• "(foo)&a" → "(foo)&a:*" → OK
• "(foo) a" → "(foo) & a:*" → OK

So, API clients can currently do arbitrary queries by avoiding spaces (delimiting words with &), and users can do this from Workbench by following the same rule and appending "&a" to avoid the implicit prefix-matching and (if the query ends with a parenthesis) syntax errors caused by ":*".

• Avoid "only stop words" notices: call to_tsquery(search_string), make sure the result isn't empty, and use the result as the @ operand. If the result is empty, we should ignore/skip the @ filter entirely. This would be consistent with "foo & and" being equivalent to "foo".
• Avoid parse errors: on the API side, instead of blindly replacing whitespace with "&" and expecting the result to be a valid input to to_tsquery(), the API server should either:
  • pass the caller's search string to to_tsquery() directly, or
  • rearrange the query more carefully, e.g.,
    • use something reliable like plainto_tsquery(), which reliably interprets "(''((foo |)&| bar:*" as "search for all lexemes and ignore punctuation", i.e., returns "'foo' & 'bar'".
    • add back the prefix-matching operator if given (this is a bit tricky, because plainto_tsquery("foo bar and")="'foo' & 'bar'" -- e.g., if someone types "foo bar and" in workbench, do we want "'foo' & 'bar'", or "'foo':* & 'bar':*"?.
  • ...or auto-detect: accept either a well formed query -- which must start with one of the three chars !'( -- or plain text to pass to plainto_tsquery() to match whole words.
• Meanwhile, Workbench should munge (or not munge) the query according to API expectations: Replace every non-word char with a space, trim trailing space, and (if the resulting query isn't empty) wrap tokens in single quotes, join with &, and append :*.
  • With all three of the API options, and the current API behavior, this would produce the same searches as the current implementation -- except that it can't (even secretly) do boolean "or", and it doesn't produce errors, even if API server hasn't been fixed.
  • The { "and" → "'and':*" → "only stop words" → match-nothing } case would still be a bit odd until/unless we update API to make a special case for that, but at least it wouldn't be a server error.

Split API improvement to #12251