Postgres filters use incorrect escape characters

[jmaslek-ica]

Describe the bug
In the postgres driver (newest - v1.3.72,, with Postgres 16.9) when using filters like contains or equals, the SQL is generated with ESCAPE '\\' . This results in an error:

 "error": "Error: syntax error at or near \")\"\n 

It seems that this is because there should be a single backslash.

To Reproduce
Steps to reproduce the behavior:
Create a postgres cube and execute a query using a contains filter.

Expected behavior
A clear and concise description of what you expected to happen.

We should be able to use contains/equals filters on a postgres connection.

Screenshots
If applicable, add screenshots to help explain your problem.

Minimally reproducible Cube Schema
In case your bug report is data modelling related please put your minimally reproducible Cube Schema here.
You can use selects without tables in order to achieve that as follows.

cube(`Users`, {
  sql: `
  SELECT 1 as id, 'john.doe@example.com' as email
  UNION ALL
  SELECT 2 as id, 'jane.smith@example.com' as email
  `,
  
  dimensions: {
    email: {
      sql: `email`,
      type: `string`
    }
  },
  
  measures: {
    count: {
      type: `count`
    }
  }
});

And then query with:

{
  "dimensions": ["Users.email"],
  "filters": [{
    "member": "Users.email",
    "operator": "contains",
    "values": ["example"]
  }]
}

Version:
Using 1.3.72 for the server/core/postgres-driver with Postgres 16.9

Additional context
Add any other context about the problem here.

[igorlukanin]

Hey @jmaslek-ica 👋

This is the generated SQL I get for your query in 1.3.74, the data source is Postgres:

What is the generated SQL that you're getting?

My wild guess would be that your data source type is misconfigured. Maybe Cube thinks that you're querying Presto or something else, not Postgres.

[jmaslek-ica]

Thanks for the reply. I do notice some weird behavior (I initially thought it was a configuration issue, but we are using the PostgresDriver).

Anyways -- in the web UI for the query I have ehat we expect (which is a single '') -- but it is not what you see:

And then what seems to get sent to the database is not the generated SQL (logs here):

cube-1  | Error querying db: {"query":"SELECT \"users\".email \"users__email\" FROM ( SELECT 1 as id, 'john.doe@example.com' as email UNION ALL SELECT 2 as id, 'jane.smith@example.com' as email ) AS \"users\" WHERE (LOWER(\"users\".email) LIKE CONCAT('%', LOWER(?) , '%') ESCAPE '\\') GROUP BY 1 ORDER BY 1 ASC LIMIT 10000","params":["example"],"error":"Error: syntax error at or near \")\"\n    at QueryQueue.parseResult (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.ts:384:13)\n    at QueryQueue.executeInQueue (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.ts:361:19)\n    at processTicksAndRejections (node:internal/process/task_queues:105:5)\n    at /cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryCache.ts:764:17\n    at QueryOrchestrator.fetchQuery (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryOrchestrator.ts:267:20)\n    at OrchestratorApi.executeQuery (/cube/node_modules/@cubejs-backend/server-core/src/core/OrchestratorApi.ts:98:20)\n    at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1672:21\n    at async Promise.all (index 0)\n    at ApiGateway.getSqlResponseInternal (/cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1670:31)\n    at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1875:28","requestId":"dcb18309-8bec-4e12-aacd-b69aedb3f20a-span-1"}
cube-1  | Orchestrator error: {"query":{"dimensions":["Users.email"],"filters":[{"values":["example"],"member":"Users.email","operator":"contains"}]},"error":"Error: syntax error at or near \")\"","duration":8174,"requestId":"dcb18309-8bec-4e12-aacd-b69aedb3f20a-span-1"}

Which has this "Error: syntax error at or near \")\""