Sql Metric Expression which uses % has % duped in tableView query

[scophotog]

I created a sql metric with the expression:

CAST(strftime("%s", max("timestamp")) AS INT)

When I used this metric to create a table view the values were not being populated. Looking into the JSON I found the query had duplicated the %:

query: "SELECT "serviceName" AS "serviceName", CAST(strftime("%%s", max("timestamp")) AS INT)
 AS latest_date FROM "testResults" WHERE timestamp >= '2016-03-29 15:57:43.000000' AND timestamp <= '2016-04-05 15:57:43.000000' GROUP BY "serviceName" ORDER BY 
CAST(strftime("%%s", max("timestamp")) AS INT) DESC LIMIT 50000 OFFSET 0"

Current end result is this column ends up displaying a 0 for each entry

[kingo55]

Does it need escaping? It's weird it's returning the quote marks unescaped too.

[scophotog]

I did try escaping around the % and the " characters surrounding the %s but I'm still getting the % duplicated.

query: "SELECT CAST(strftime("/_%%s", max("timestamp")) AS INT) AS latest_date FROM "testResults" WHERE timestamp >= '2016-03-30 08:36:01.000000' AND timestamp <= '2016-04-06 08:36:01.000000' LIMIT 50000 OFFSET 0"

query: "SELECT CAST(strftime("\%%s", max("timestamp")) AS INT) AS latest_date FROM "testResults" WHERE timestamp >= '2016-03-30 08:36:01.000000' AND timestamp <= '2016-04-06 08:36:01.000000' LIMIT 50000 OFFSET 0"

[mistercrunch]

Have you tried doubling the % as in %%? I think we had this bug before but forgot about the details/workaround.

[mistercrunch]

Also which DB / sqlalchemy dialect are you using?

[scophotog]

Have you tried doubling the % as in %%? I think we had this bug before but forgot about the details/workaround.

I'll give that a shot.

Also which DB / sqlalchemy dialect are you using?

I'm using sqllite3.

[scophotog]

Have you tried doubling the % as in %%? I think we had this bug before but forgot about the details/workaround.

I'll give that a shot.

It appears to make more % characters

query: "SELECT "serviceName" AS "serviceName", CAST(strftime("%%%%s", max("timestamp")) AS INT) AS latest_date FROM "testResults" WHERE timestamp >= '2016-03-31 11:20:59.000000' AND timestamp <= '2016-04-07 11:20:59.000000' GROUP BY "serviceName" ORDER BY CAST(strftime("%%%%s", max("timestamp")) AS INT) DESC LIMIT 50000 OFFSET 0"

[mtkage]

Is there a workaround for this problem?

[ratb3rt]

This seems refers to a SQLAlchemy bug, which should be fixed in v1.2. It's patched for DateTimes in models.py :Patch for sqlalchemy bug
Bug ref: https://bitbucket.org/zzzeek/sqlalchemy/issues/3740/clean-up-doubling-of-percent-signs-base-on

[mistercrunch]

in #2330 I started doubling the % in the WHERE clause and in the new LIKE clause of filters.