CubeSQL fails to rewrite Metabase string filters using LIKE ... ESCAPE '\'
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 10h
- Merged PRs (30d)
- 203
Description
**Describe the bug**
When Metabase is connected to Cube through the PostgreSQL / SQL API, a string filter using `Contains` generates SQL like:
```sql
SELECT "public"."Orders"."count" AS "count"
FROM "public"."Orders"
WHERE lower("public"."Orders"."customer_gender") LIKE '%female%' ESCAPE '\'
LIMIT 10
```
CubeSQL fails to rewrite this query and returns:
**Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.**
The same query works if the ESCAPE '\' clause is removed.
So the bug seems specific to the SQL rewrite path for literal LIKE ... ESCAPE patterns generated by Metabase.
To Reproduce
- Expose a simple Cube with a string dimension through Cube SQL API / PostgreSQL protocol.
- Connect Metabase to Cube using the PostgreSQL driver.
- Build a question in the Metabase query builder on that table.
- Add a Contains filter on a string field such as customer_gender.
- Run the query.
- Check Cube logs.
- Metabase sends a query like:
```sql
SELECT "public"."Orders"."count" AS "count"
FROM "public"."Orders"
WHERE lower("public"."Orders"."customer_gender") LIKE '%female%' ESCAPE '\'
LIMIT 10
```
Cube logs show:
Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.
Expected behavior
CubeSQL should accept this query and rewrite it the same way it already rewrites the equivalent query without ESCAPE, i.e. as a normal string contains filter.
In other words, this should work:
```
SELECT "public"."Orders"."count" AS "count"
FROM "public"."Orders"
WHERE lower("public"."Orders"."customer_gender") LIKE '%female%' ESCAPE '\'
LIMIT 10
```
just like this already works:
```
SELECT "public"."Orders"."count" AS "count"
FROM "public"."Orders"
WHERE lower("public"."Orders"."customer_gender") LIKE '%female%'
LIMIT 10
```
Minimally reproducible Cube Schema
```
cube(`Orders`, {
sql: `
select 1 as id, 'female' as customer_gender
UNION ALL
select 2 as id, 'male' as customer_gender
UNION ALL
select 3 as id, 'female manager' as customer_gender
UNION ALL
select 4 as id, 'other' as customer_gender
`,
measures: {
count: {
type: `count`,
},
},
dimensions: {
id: {
sql: `id`,
type: `number`,
primaryKey: true,
},
customerGender: {
sql: `customer_gender`,
type: `string`,
},
},
});
```
**Version:**
Reproduced on:
Cube v1.6.14
Cube v1.6.29
Metabase version:
v1.59.4
Additional context
A few details that may help narrow this down:
The failure happens only with ESCAPE '\'.
lower(column) LIKE '%value%' works.
lower(column) LIKE '%value%' ESCAPE '\' fails.
This looks like a CubeSQL rewrite limitation rather than a data modeling issue.
There is already support for some LIKE ... ESCAPE cases (for example ThoughtSpot-related paths), but this literal Metabase form still seems unsupported.
Contributor guide
Research direction
Trace the CubeSQL rewrite path used by the PostgreSQL protocol, starting with handling for literal LIKE predicates and the existing LIKE ... ESCAPE cases mentioned in the report. Reproduce the Metabase query and verify that the form with ESCAPE '\' is rewritten as the equivalent string-contains filter without ESCAPE.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, rust, sql
- Domain
- api, backend
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100