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@mariadb.org