search

GIScience / ohsome-now-stats-service

This is the REST service for the ohsomeNow stats.
https://stats.now.ohsome.org/api/
GNU Affero General Public License v3.0
4 stars 1 forks source link

Warning about a maybe malformed SQL! #41

Closed rtroilo closed 1 year ago

rtroilo commented 1 year ago

In our logs, we got warning about a + sign encountered within our sql. I belive this SQL is still valid, as we produce the right? result. But maybe we could rewrite this query in a way, that the parser don't complain about it?

Aug 28 15:41:36 ohsome-now-stats bash[1043991]: 2023-08-28T15:41:36.160Z  WARN 1043991 --- [nio-8203-exec-7] c.c.jdbc.parser.ClickHouseSqlParser      : Parse error at line 20, column 65.  Encountered: +. If you believe the SQL is valid, please feel free to open an issue on Github with this warning and the following SQL attached.
Aug 28 15:41:36 ohsome-now-stats bash[1043991]: SELECT
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     count(distinct changeset_id) as changesets,
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     count(distinct user_id) as users,
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     ifNull(sum(road_length_delta)/1000, 0) as roads,
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     ifNull(sum(building_edit), 0) as buildings,
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     count(map_feature_edit) as edits,
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     toStartOfInterval(changeset_timestamp, INTERVAL ?)::DateTime as startdate,
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     (toStartOfInterval(changeset_timestamp, INTERVAL ?)::DateTime + INTERVAL ?) as enddate
Aug 28 15:41:36 ohsome-now-stats bash[1043991]: FROM "stats"
Aug 28 15:41:36 ohsome-now-stats bash[1043991]: WHERE
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     equals(hashtag, ?)
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     AND changeset_timestamp > parseDateTimeBestEffort(?)
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     AND changeset_timestamp < parseDateTimeBestEffort(?)
Aug 28 15:41:36 ohsome-now-stats bash[1043991]: GROUP BY
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     startdate
Aug 28 15:41:36 ohsome-now-stats bash[1043991]: ORDER BY startdate ASC
Aug 28 15:41:36 ohsome-now-stats bash[1043991]: WITH FILL
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     FROM toStartOfInterval(parseDateTimeBestEffort(?), INTERVAL ?)::DateTime
Aug 28 15:41:36 ohsome-now-stats bash[1043991]:     TO toStartOfInterval(parseDateTimeBestEffort(?), INTERVAL ?)::DateTime
Aug 28 15:41:36 ohsome-now-stats bash[1043991]: STEP INTERVAL ? Interpolate (enddate as (startdate + INTERVAL ? + INTERVAL ?))
Hagellach37 commented 1 year ago

We could write it like this by simply adding brackets around startdate + INTERVAL ?


 WITH FILL
     --FROM toStartOfInterval(parseDateTimeBestEffort('2022-12-15'), INTERVAL 1 DAY)::DateTime
     FROM toStartOfInterval(parseDateTimeBestEffort('2023-01-01'), INTERVAL 1 DAY)::DateTime
     TO toStartOfInterval(parseDateTimeBestEffort('2023-02-01'), INTERVAL 1 DAY)::DateTime
 STEP INTERVAL 1 DAY 
    Interpolate (enddate as ((startdate + INTERVAL 1 DAY) + INTERVAL 1 DAY))
``