maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, jerome!

On Dec 27, jerome brauge wrote:
> Sergei,
> 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
configuration parameter.

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
>> @@concat_null_is_null.
>> 
>> 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?

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx