Issue

Using the SET statement inside a stored procedure returns an error. For example, consider a stored procedure that contains statements like

BEGIN
  SET @@sql_mode = PIPES_AS_CONCAT;
...
<some_statement_that_uses_pipes_for_concatenation>

When you run this stored procedure for the first time, it returns a syntax error. However, if you run the same stored procedure again, in the same session, it runs successfully.

When a SingleStore query is run, its plan is generated and then compiled. However, the stored procedure definition is compiled right away (like other DDL queries), when the procedure is created. The queries inside the stored procedure are not compiled until the stored procedure is run for the first time. For SET queries, this delay in compiling means that the stored procedure will be generated as though the variable specified in the SET statement was not set.

On the first run, these queries are compiled, which causes the stored procedure to recompile. Once the stored procedure has been recompiled, the queries inside it have been compiled as well. Therefore, when the stored procedure is executed for a second time, it runs successfully.

Solution

Avoid using SET statements inside a stored procedure, especially for setting those variables that can potentially affect query execution. Set the variables globally instead.