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

[Graham]

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!

[LandanG]

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

[Graham]

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.

[Graham · Accepted solution]

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.

[rafal_walisko]

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"
        }