Pinot: contains/startsWith/endsWith filters never match (LIKE against a CONCAT pattern)

[paveltiunov]

Summary

On Pinot, every LIKE-based filter — contains, notContains, startsWith, endsWith — returns no rows, regardless of the value. This is independent of wildcard escaping and predates #11569.

It has gone unnoticed because the driver-suite snapshots record the empty results as the expected values.

Evidence

packages/cubejs-testing-drivers/test/__snapshots__/pinot-full.test.ts.snap has 21 LIKE-family entries recorded as Array []:

filtering Products:   contains + dimensions + order, first/second/third
                      contains with special chars + dimensions
                      startsWith …, endsWith …
filtering Customers:  contains first/second/third, startsWith …, endsWith …
filtering ECommerce:  contains first/second/third, startsWith …, endsWith …

Two of those are conclusive on their own:

• contains 'able' against a dataset containing "Tables" — no wildcard involved.
• contains 'di_Novo' — every other engine records the Logitech di_Novo Edge Keyboard row; Pinot records Array [].

Cause

Cube emits, for Pinot:

LOWER(col) LIKE CONCAT('%', LOWER(?), '%')

Pinot does not match a non-constant LIKE pattern. Probed directly against apachepinot/pinot:1.4.0 (QuickStart baseballStats, 97,889 rows):

LOWER(playerName) LIKE '%aaron%'                          -> 311
LOWER(playerName) LIKE CONCAT('%', LOWER('aaron'), '%')   -> 120   -- same predicate
LOWER(playerID)   LIKE CONCAT('%', LOWER('a'), '%')       -> 0     -- must match

A literal pattern behaves correctly, including escaping:

'a_b' LIKE '%\_%'   -> matches
'axb' LIKE '%\_%'   -> no match

Separately: the ESCAPE clause is also wrong for Pinot

PinotFilter.likeIgnoreCase emits ESCAPE '\'. Pinot errors on that clause:

'a_b' LIKE '%\_%' ESCAPE '\'   -> Query execution error
'a_b' LIKE '%\_%'              -> matches

Pinot already treats backslash as the escape character, so the clause should simply be removed. That fix was written and verified in #11569 and then reverted out of it, because on its own it does not fix the matching problem above — the driver job still failed with the snapshots unchanged (Snapshots: 102 passed, 102 total). It is a correct, independent change worth carrying into whatever fixes this.

A further Pinot limitation

Even with a literal pattern, an escaped % immediately before a trailing wildcard is mis-parsed:

'50% off' LIKE '%\% off'   -> matches
'50% off' LIKE '%\%%'      -> no match     -- the shape `contains` builds

So contains '%' cannot match a literal percent sign on Pinot even once the CONCAT issue is resolved.

Suggested direction

Emit a literal LIKE pattern for Pinot rather than building it with CONCAT, i.e. compose the wildcards into the parameter value instead of into the SQL. That touches the filters/like_pattern / parameter-allocation contract for this dialect, so it needs a working Pinot to validate.

Current state in the test suite

packages/cubejs-testing-drivers/fixtures/pinot.json skips one case with this reason recorded inline:

filtering Products: contains a literal underscore (no pre-aggregation)

The other four LIKE-escaping cases still run on Pinot, but they pass only because they expect an empty result — they are not evidence that Pinot is correct. Anything fixing this should un-skip the underscore case and expect the other four to start being meaningful.

[paveltiunov]

One more detail for whoever picks this up, surfaced in review of #11569.

Pinot's two planners currently disagree on the ESCAPE clause. The legacy path (PinotFilter.likeIgnoreCase) emits ESCAPE '\'; the native path does not, because it inherits the base filters/like_escape_char added in #11569 without a clause. That is the pre-existing master state — #11569 briefly made them agree and then reverted, since the change was unverifiable on its own.

It is unobservable today only because no LIKE filter matches anything on Pinot, so both paths return empty regardless. Once the CONCAT pattern problem above is fixed, the disagreement becomes visible:

• native path: value escaped with backslash, no clause → correct, per the probes above;
• legacy path: value escaped with backslash and ESCAPE '\' → execution error.

So a complete fix is two parts, not one:

1. emit a literal LIKE pattern instead of CONCAT(...), so Pinot matches at all;
2. drop ESCAPE '\' from PinotFilter.likeIgnoreCase, so the legacy path stops erroring and the two planners agree.

Part 2 is written and was verified against Pinot 1.4.0 in #11569 before being reverted — the diff is in commit b6a6465 on claude/trino-percent-filter-escape-s01hoz if it is useful to lift.

---

Generated by Claude Code

[paveltiunov]

Thanks for the detailed report. Looking at the driver source, the contains/startsWith/endsWith filters do indeed generate LOWER(col) LIKE CONCAT('%', LOWER(?), '%') ESCAPE '\' for Pinot (both the legacy and Tesseract planners — see packages/cubejs-pinot-driver/src/PinotQuery.ts), since Pinot doesn't support ||/ILIKE and the driver substitutes CONCAT() + LOWER()+LIKE instead. That part is intentional.

What we can't confirm without a live Pinot cluster is whether Pinot's LIKE predicate compiler actually rejects/mis-evaluates a computed pattern (a CONCAT(...) expression) versus a literal string — there's no existing test coverage in this repo exercising that combination against real Pinot.

Could you share:

• Your Pinot version
• Any error or warning Pinot logs for the query (or the raw SQL Cube sent, which you can get with CUBEJS_LOG_LEVEL=trace)
• Whether a manually-run SELECT ... WHERE LOWER(col) LIKE CONCAT('%', LOWER('term'), '%') against the same table in the Pinot query console returns the expected rows

That'll let us confirm whether this is a Pinot dialect limitation we need to work around (e.g. via REGEXP_LIKE) or something else.

---

Generated by Claude Code