@cap-js/sqlite: search with multiple words leads to error

[sjvans]

• customer reports error SQLite only supports single value arguments for $search.
• fine on postgres.
• should be avoided by concatenating multiple search clauses with OR

[danjoa]

Can you add an example?

[sjvans]

https://github.com/cap-js/cds-dbs/pull/132

[danjoa]

Thanks. We intentionally decided to provide only limited support for $search beyond simple strings, at least in SQLite, as the complexity and performance overhead would be significant, and the value rather low as most end users don't do advanced search expressions. But we can revisit that.

[sjvans]

actually, the error also occurs on postgres, i.e., the error message wrongly status "SQLite".

i think we should revisit, at least for postgres. issue occurs as soon as user input contains a whitespace as this is an implicit AND as per OData spec. reproducible, for example, in sflight.

i added a change to my pr that would support it. not sure it's the right way, though. i'm not really familiar with this coding. ;)

[ArtyomAD]

Hello.

Can someone please clarify if this issue/missing functionality plan to be released soon?

We might require to disable main Search functionality in our Fiori application which is running on @cap-js/posgres exactly due to this issue.

[gregorwolf]

Hi @ArtyomAD,

I would think that it would be better if you check if this issue also occurs when you use sqlite as then you could contribute the SQL statement that is created when you issue the query. Or you create a new issue which mentions the postgres driver.

[ArtyomAD]

Hello @gregorwolf.

We face identical error in both cases with sqlite and postgres.

SQLite: Trace without $search: Trace with $search:

Postgres in BTP: Confirmation from BTP as it's running with postgres:

[sebastianesch]

I can confirm that the issue also occurs with SQLite. The search input from Fiori Elements is passed directly to the search query - if the user enters a search string with whitespaces, search produces an error.

[sebastianesch]

This issue also breaks Fiori Elements apps, when users try to enter text with whitespaces in value help fields.