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

[rgardette-lemonde]

Describe the bug

When Metabase is connected to Cube through the PostgreSQL / SQL API, a string filter using Contains generates SQL like:

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:

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.

[rakeshchirla]

Had the same issue.

After upgrading Metabase from v1.58.7 to v1.59.5, all questions using string filters (Contains, Starts with, etc.) that route through Cube's SQL API started failing with:

Error during rewrite: Can't detect Cube query and it may be not supported yet.

Metabase v1.59 adds ESCAPE '\' to all LIKE clauses generated by the query builder. Cube's SQL rewrite parser doesn't handle this syntax.

Questions without string filters or those not routed through Cube work fine. Rolling back to Metabase v1.58.7 (which doesn't emit ESCAPE '\') resolves the issue, but blocks upgrading Metabase until this is fixed on the Cube side.

Environment:

• Cube Cloud
• Metabase Enterprise v1.59.5 (broken) / v1.58.7 (working)
• Connected via PostgreSQL driver (SQL API)

Even just stripping/ignoring ESCAPE '\' during rewrite would work, since the default escape character is already backslash in PostgreSQL.