topic Re: Inline comment next to un-tickmarked SET statement = Syntax error in Data Engineering

Inline comment next to un-tickmarked SET statement = Syntax error

Graham — Tue, 01 Nov 2022 18:38:00 GMT

Running this code in databricks SQL works great:

SET USE_CACHED_RESULT = FALSE;
 
-- Result:
-- key                                      value
-- USE_CACHED_RESULT     FALSE

If I add an inline comment, however, I get a syntax error:

SET USE_CACHED_RESULT = FALSE; -- this comment causes issues
 
-- Result:
-- Expected format is 'SET', 'SET key', or 'SET key=value'. If you want to include special characters in key, or include semicolon in value, please use quotes, e.g., SET `key`=`value`.line 1, pos 0

Edit: it's been helpfully mentioned that adding tickmarks resolves the issue. That's great to know. However, tickmarks are optional for this kind of statement--in other words, default behavior is that it should still work without tickmarks! This issue is, simply stated, a bug in the Databricks SQL runtime.

There are other similar solutions:

Just remove the inline comment
Convert the inline comment to a block comment
Don't run this SET statement and find another way to avoid caching

No matter which of these we try (with great results!), this bug will persist in the Databricks SQL runtime.

This post, then, is less of a request for workarounds--and more of a bug alert. If there is a better place for this kind of feedback, please let me know so I can put it there!

Re: Inline comment next to un-tickmarked SET statement = Syntax error

LandanG — Tue, 01 Nov 2022 23:05:58 GMT

Hi Graham,

Does using backticks (`) like below fix the issue?

SET `USE_CACHED_RESULT` = `FALSE`; --this comment causes issues
 
-- Result:
-- key                                      value
-- USE_CACHED_RESULT     FALSE

Re: Inline comment next to un-tickmarked SET statement = Syntax error

Graham — Wed, 09 Nov 2022 17:15:57 GMT

This works! Thank you! however, it should still work without tick marks, so I'm leaving this question up here. Again, thanks for your find.

Re: Inline comment next to un-tickmarked SET statement = Syntax error

Graham — Wed, 09 Nov 2022 17:19:47 GMT

Thanks Kaniz! I sent a reply to Landan for his insightful answer. His is a great workaround. However, it should still work without his workaround (adding tickmarks). So I'm leaving this up here for now; I think this is just a bug that needs to be brought before the appropriate internal Databricks team.

Re: Inline comment next to un-tickmarked SET statement = Syntax error

rafal_walisko — Mon, 09 Sep 2024 12:00:45 GMT

Hi, I'm getting the same error when trying to execute statement through API

"statement": "SET `USE_CACHED_RESULT` = FALSE; SELECT COUNT(*) FROM TABLE"

Every combination fail

"status": { "state": "FAILED", "error": { "error_code": "BAD_REQUEST", "message": "\n[INVALID_PROPERTY_VALUE] \"FALSE; SELECT COUNT(*) FROM TABLE\" is an invalid property value, please use quotes, e.g. SET \"USE_CACHED_RESULT\"=\"FALSE; SELECT COUNT(*) FROM TABLE\" SQLSTATE: 42602 (line 1, pos 0)\n\n== SQL ==\nSET `USE_CACHED_RESULT` = FALSE; SELECT COUNT(*) FROM TABLE \n^^^\n" }