Tesseract text-search filters generate `ILIKE` and non-literal `LIKE` patterns on Pinot

[0eu]

Describe the bug
When CUBEJS_TESSERACT_SQL_PLANNER=true, string filters (contains, notContains, startsWith, endsWith) on a Pinot-backed cube generate SQL that Pinot can't run, because of:

1. Cube emits ILIKE, which Pinot has no function for. It only has (LIKE)[https://docs.pinot.apache.org/build-with-pinot/querying-and-sql/sql-syntax/sql-reference#like].
2. The LIKE pattern is built with the || operator ('%' || ? || '%'), but Pinot requires the LIKE pattern to be a literal.

Generated SQL for a contains filter:

SELECT count("stats".playerID) "stats__count"
FROM baseballStats AS "stats"
WHERE (("stats".playerName ILIKE '%' || ? || '%'))
LIMIT 10000

Pinot errors:

QueryValidationError: No match found for function signature ILIKE

After rewriting ILIKE to LOWER(...) LIKE LOWER(...), the ||-built pattern still fails:

For LIKE predicate, the operands except for the first one must be literal

I looked at the codebase and see that BigQuery overrides tesseract.ilike to LOWER({{ expr }}) LIKE LOWER({{ pattern }}).

To Reproduce

1. Configure a Cube project with the Pinot driver.
2. Define a cube with a string dimension.
3. Enable Tesseract with CUBEJS_TESSERACT_SQL_PLANNER=true.
4. Query a measure with a contains (or startsWith / endsWith / notContains) filter on the string dimension.
5. Observe that the generated SQL uses ILIKE and a ||-built pattern, and that Pinot rejects it.

Expected behavior
The filter should use a Pinot-compatible, case-insensitive match with a literal pattern:

WHERE LOWER("stats".playerName) LIKE LOWER('%son%')

Minimally reproducible Cube Schema

cube(`Stats`, {
  sql_table: `baseballStats`,

  measures: {
    count: { type: `count` },
  },

  dimensions: {
    playerName: { sql: `playerName`, type: `string` },
  },
});

Query:

{
  "measures": ["Stats.count"],
  "filters": [
    { "member": "Stats.playerName", "operator": "contains", "values": ["son"] }
  ]
}

Version:

• Cube 1.7.1
• Pinot 1.6.0

Additional context
I cherry-picked this diff first: #11001

[ovr]

Thank you for bug reporting. I've made the fix and released a new version: v1.7.2.

Feel free to give it a try, and let me know if it doesn't work.