maria-developers team mailing list archive
Mailing list archive
On Dec 27, jerome brauge wrote:
> On SQLServer these kind of option (ANSI_NULLS, CONCAT_NULL_YIELDS_NULL
> and so on) can be set at session level or inside stored procedure (but
> not in function). In the last case, the scope of the setting is
> limited to the stored procedure.
That's not what I mean.
When you create a stored routine or, say, a trigger in MariaDB (or in
MySQL, fwiw), it remembers the current value of sql_mode and
automatically sets it temporarity when parsing or executing the routine.
Because if you'd create a routine in the ANSI_QUOTES mode, it may not
even parse without it. If you create it with PIPES_AS_CONCAT or
HIGH_NOT_PRECEDENCE mode, it'll parse, but will work incorrectly.
That is, to preserve the original semantics of a stored routine, it
needs to work in the sql_mode in which it was created.
On the other hand, say, join_buffer_size setting is not stored, it does
not normally affect the semantics of a routine, it's just a tuning
So, sql_mode is not something you can set per routine, it's something
that the server automatically needs to save/restore per routine to
preserve this routine's semantics.
In your case, I think, CONCAT_NULL_YIELDS_NULL fits in this category
and thus it shoud be in the sql_mode.
>> Yes, precisely. I thought today what should be the criteria for
>> choosing between a new sql mode or a separate variable, like
>> And I realized that one such criteria could be - does it need to be
>> stored *per routine*? Saved with every storage procedure, function,
>> trigger and event definition?
Chief Architect MariaDB