SQL Warehouse REST statement execution validation fails with DECLARE SET

[mathiaskvist]

Hi,

I'm using the REST API for SQL Warehouse in order to execute queries. I have experienced multiple times that query validation fails over the REST API, while executing the same query in the Databricks UI on the same cluster succeeds. An example:

[PARSE_SYNTAX_ERROR] Syntax error at or near 'SET': extra input 'SET'. SQLSTATE: 42601 (line 3, pos 8)

== SQL ==

DECLARE VARIABLE max_offset int;
SET VAR max_offset = (
--------^^^
select MAX(_Offset) from mycatalog.myschema.mytable
);

SELECT * from mycatalog.myschema.mytable
WHERE `_Offset` = max_offset

If I paste the same code into the UI it executes without issues.

I have had other types of queries failing in a similar manner when using the CACHE keyword over the REST API.

[adriennn]

The only times I have seen errors with SET VARIABLE is when the result of the query that is supposed to give the value of the variable is throwing an error; in this case the actual error is hidden by the error raised by SET.

[mathiaskvist]

Thanks for your reply. However, I don't think that is the case here, since I can manually run the exact same query without issue.

[adriennn]

Had to try for myself and it seems the sql execution context in the REST API is different than that of an *.sql script, notebook or query made against an sql warehouse through the ui. The error stems from the fact that the SET command can also be used to set a session parameter as well (without the var) and the runtime gets confused because there is the extra var between set and the value assignment. 

Obviously the are at least several other ways to achieve the above query without using an sql variable, but it would probably be nice to have a warning in the docs that the set var statement isn't supported through the rest api.

[mathiaskvist]

Okay, that does make some sense. However, it is strange that the execution context is different, I would expect it to be equivalent with the UI. 

Would be really great to document how the execution context differs. As mentioned I had the same issue with the CACHE statement. There might be more cases that are handled differently.