cube-js / cube-js/cube

CubeSQL fails to rewrite Metabase string filters using LIKE ... ESCAPE '\'

Open
#10,581 1 comment 4 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.