"WITH ... UPDATE ..." statement works with v1.0.90 but not with v1.0.161

[chrfriese123]

Hello,

some time ago I used the following statement successfully to update my HomeAssistant SQL database

WITH t (id) AS (SELECT rowid FROM "statistics_short_term" WHERE sum IS NULL AND metadata_id="118")
UPDATE "statistics_short_term" SET sum=(
  (SELECT sum FROM "statistics_short_term" WHERE metadata_id="118" AND sum !="null" ORDER BY "ID" DESC LIMIT 1) + 
  (state - (SELECT state FROM "statistics_short_term" WHERE metadata_id="118" AND sum !="null" ORDER BY "ID" DESC LIMIT 1))
) WHERE rowid IN (SELECT id FROM t);

Now I have to repeat the script. With the exact same statement, I get the following error:

near "UPDATE": syntax error in SELECT * FROM (WITH t (id) AS (SELECT rowid FROM "statistics_short_term" WHERE sum IS NULL AND metadata_id="118")
UPDATE "statistics_short_term" SET sum=(
  (SELECT sum FROM "statistics_short_term" WHERE metadata_id="118" AND sum !="null" ORDER BY "ID" DESC LIMIT 1) + 
  (state - (SELECT state FROM "statistics_short_term" WHERE metadata_id="118" AND sum !="null" ORDER BY "ID" DESC LIMIT 1))
) WHERE rowid IN (SELECT id FROM t)
) LIMIT 0 at offset 114
Query: SELECT * FROM (WITH t (id) AS (SELECT rowid FROM "statistics_short_term" WHERE sum IS NULL AND metadata_id="118")
UPDATE "statistics_short_term" SET sum=(
  (SELECT sum FROM "statistics_short_term" WHERE metadata_id="118" AND sum !="null" ORDER BY "ID" DESC LIMIT 1) + 
  (state - (SELECT state FROM "statistics_short_term" WHERE metadata_id="118" AND sum !="null" ORDER BY "ID" DESC LIMIT 1))
) WHERE rowid IN (SELECT id FROM t)
) LIMIT 0
Parameters: [] 

The plugin has latest version 1.0.161. When I downgrade the plugin to version 1.0.90 the statement works again.

Unfortunately I'm not an expert in SQL. Is there an issue with my initial statement or is there an issue with the implementation in the latest version?

[yy0931]

Thank you for reporting this! The issue is due to a bug in the extension. The extension checks whether the executed query is a SELECT statement, and if it is, the statement is wrapped with SELECT * FROM (<the original statement>) LIMIT 0 to get the list of columns. However, it seems that this logic broke in some version, and WITH ... UPDATE ... is incorrectly classified as a SELECT statement. I'll fix it later.

[chrfriese123]

Thanks for fast feedback.

[yy0931]

Resolved in version 1.0.162.