add list_add / list_drop to sys

gistfile1.txt

diff --git a/README.md b/README.md
index 1f375c6..6249404 100644
--- a/README.md
+++ b/README.md
@@ -3294,6 +3294,88 @@ mysql> select format_time(34234);
 1 row in set (0.00 sec)
 ```

+#### list_add
+
+##### Description
+
+Takes a list, and a value to add to the list, and returns the resulting list.
+
+Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.
+
+##### Parameters
+
+in_list (TEXT): The comma separated list to add a value to
+
+in_add_value (TEXT): The value to add to the input list
+
+##### Returns
+
+TEXT
+
+##### Example
+
+```SQL
+mysql> select @@sql_mode;
++-----------------------------------------------------------------------------------+
+| @@sql_mode                                                                        |
++-----------------------------------------------------------------------------------+
+| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
++-----------------------------------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql> set sql_mode = sys.list_add(@@sql_mode, 'ANSI_QUOTES');
+Query OK, 0 rows affected (0.06 sec)
+
+mysql> select @@sql_mode;
++-----------------------------------------------------------------------------------------------+
+| @@sql_mode                                                                                    |
++-----------------------------------------------------------------------------------------------+
+| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
++-----------------------------------------------------------------------------------------------+
+1 row in set (0.00 sec)
+```
+
+#### list_drop
+
+##### Description
+
+Takes a list, and a value to attempt to remove from the list, and returns the resulting list.
+
+Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.
+
+##### Parameters
+
+in_list (TEXT): The comma separated list to drop a value from
+
+in_drop_value (TEXT): The value to drop from the input list
+
+##### Returns
+
+TEXT
+
+##### Example
+
+```SQL
+mysql> select @@sql_mode;
++-----------------------------------------------------------------------------------------------+
+| @@sql_mode                                                                                    |
++-----------------------------------------------------------------------------------------------+
+| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
++-----------------------------------------------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql> set sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');
+Query OK, 0 rows affected (0.03 sec)
+
+mysql> select @@sql_mode;
++----------------------------------------------------------------------------+
+| @@sql_mode                                                                 |
++----------------------------------------------------------------------------+
+| ANSI_QUOTES,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
++----------------------------------------------------------------------------+
+1 row in set (0.00 sec)
+```
+
 #### ps_is_account_enabled

 ##### Description
diff --git a/functions/list_add.sql b/functions/list_add.sql
new file mode 100644
index 0000000..0d4eeb9
--- /dev/null
+++ b/functions/list_add.sql
@@ -0,0 +1,90 @@
+-- Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+DROP FUNCTION IF EXISTS list_add;
+
+DELIMITER $$
+
+CREATE DEFINER='root'@'localhost' FUNCTION list_add (
+        in_list TEXT,
+        in_add_value TEXT
+    )
+    RETURNS TEXT
+    COMMENT '
+             Description
+             -----------
+
+             Takes a list, and a value to add to the list, and returns the resulting list.
+
+             Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.
+
+             Parameters
+             -----------
+
+             in_list (TEXT):
+               The comma separated list to add a value to
+
+             in_add_value (TEXT):
+               The value to add to the input list
+
+             Returns
+             -----------
+
+             TEXT
+
+             Example
+             --------
+
+             mysql> select @@sql_mode;
+             +-----------------------------------------------------------------------------------+
+             | @@sql_mode                                                                        |
+             +-----------------------------------------------------------------------------------+
+             | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+             +-----------------------------------------------------------------------------------+
+             1 row in set (0.00 sec)
+
+             mysql> set sql_mode = sys.list_add(@@sql_mode, ''ANSI_QUOTES'');
+             Query OK, 0 rows affected (0.06 sec)
+
+             mysql> select @@sql_mode;
+             +-----------------------------------------------------------------------------------------------+
+             | @@sql_mode                                                                                    |
+             +-----------------------------------------------------------------------------------------------+
+             | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+             +-----------------------------------------------------------------------------------------------+
+             1 row in set (0.00 sec)
+
+            '
+    SQL SECURITY INVOKER
+    DETERMINISTIC
+    CONTAINS SQL
+BEGIN
+
+    IF (in_add_value IS NULL) THEN
+        SIGNAL SQLSTATE '02200'
+           SET MESSAGE_TEXT = 'Function sys.list_add: in_add_value input variable should not be NULL',
+               MYSQL_ERRNO = 1138;
+    END IF;
+
+    IF (in_list IS NULL OR LENGTH(in_list) = 0) THEN
+        -- return the new value as a single value list
+        RETURN in_add_value;
+    END IF;
+
+    RETURN (SELECT CONCAT(TRIM(BOTH ',' FROM TRIM(in_list)), ',', in_add_value));
+
+END$$
+
+DELIMITER ;
diff --git a/functions/list_drop.sql b/functions/list_drop.sql
new file mode 100644
index 0000000..6229893
--- /dev/null
+++ b/functions/list_drop.sql
@@ -0,0 +1,91 @@
+-- Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+DROP FUNCTION IF EXISTS list_drop;
+
+DELIMITER $$
+
+CREATE DEFINER='root'@'localhost' FUNCTION list_drop (
+        in_list TEXT,
+        in_drop_value TEXT
+    )
+    RETURNS TEXT
+    COMMENT '
+             Description
+             -----------
+
+             Takes a list, and a value to attempt to remove from the list, and returns the resulting list.
+
+             Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.
+
+             Parameters
+             -----------
+
+             in_list (TEXT):
+               The comma separated list to drop a value from
+
+             in_drop_value (TEXT):
+               The value to drop from the input list
+
+             Returns
+             -----------
+
+             TEXT
+
+             Example
+             --------
+
+             mysql> select @@sql_mode;
+             +-----------------------------------------------------------------------------------------------+
+             | @@sql_mode                                                                                    |
+             +-----------------------------------------------------------------------------------------------+
+             | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+             +-----------------------------------------------------------------------------------------------+
+             1 row in set (0.00 sec)
+
+             mysql> set sql_mode = sys.list_drop(@@sql_mode, ''ONLY_FULL_GROUP_BY'');
+             Query OK, 0 rows affected (0.03 sec)
+
+             mysql> select @@sql_mode;
+             +----------------------------------------------------------------------------+
+             | @@sql_mode                                                                 |
+             +----------------------------------------------------------------------------+
+             | ANSI_QUOTES,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+             +----------------------------------------------------------------------------+
+             1 row in set (0.00 sec)
+
+            '
+    SQL SECURITY INVOKER
+    DETERMINISTIC
+    CONTAINS SQL
+BEGIN
+
+    IF (in_drop_value IS NULL) THEN
+        SIGNAL SQLSTATE '02200'
+           SET MESSAGE_TEXT = 'Function sys.list_drop: in_drop_value input variable should not be NULL',
+               MYSQL_ERRNO = 1138;
+    END IF;
+
+    IF (in_list IS NULL OR LENGTH(in_list) = 0) THEN
+        -- return the list as it was passed in
+        RETURN in_list;
+    END IF;
+
+    -- ensure that leading / trailing commas are remove, support values with either spaces or not between commas
+    RETURN (SELECT TRIM(BOTH ',' FROM REPLACE(REPLACE(CONCAT(',', in_list), CONCAT(',', in_drop_value), ''), CONCAT(', ', in_drop_value), '')));
+
+END$$
+
+DELIMITER ;
diff --git a/mysql-test/suite/sysschema/r/all_sys_objects_exist.result b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result
index b2c0365..a21b489 100644
--- a/mysql-test/suite/sysschema/r/all_sys_objects_exist.result
+++ b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result
@@ -103,6 +103,8 @@ format_bytes        FUNCTION
 format_path    FUNCTION
 format_statement       FUNCTION
 format_time    FUNCTION
+list_add       FUNCTION
+list_drop      FUNCTION
 ps_is_account_enabled  FUNCTION
 ps_is_consumer_enabled FUNCTION
 ps_is_instrument_default_enabled       FUNCTION
diff --git a/mysql-test/suite/sysschema/r/fn_list_add.result b/mysql-test/suite/sysschema/r/fn_list_add.result
new file mode 100644
index 0000000..c4607a7
--- /dev/null
+++ b/mysql-test/suite/sysschema/r/fn_list_add.result
@@ -0,0 +1,14 @@
+SELECT sys.list_add(NULL, 'foo');
+sys.list_add(NULL, 'foo')
+foo
+SELECT sys.list_add('', 'foo');
+sys.list_add('', 'foo')
+foo
+SELECT sys.list_add('bar', 'foo');
+sys.list_add('bar', 'foo')
+bar,foo
+SELECT sys.list_add('bar, ', 'foo');
+sys.list_add('bar, ', 'foo')
+bar,foo
+SELECT sys.list_add('foo', NULL);
+ERROR 02200: Function sys.list_add: in_add_value input variable should not be NULL
diff --git a/mysql-test/suite/sysschema/r/fn_list_drop.result b/mysql-test/suite/sysschema/r/fn_list_drop.result
new file mode 100644
index 0000000..03c3efd
--- /dev/null
+++ b/mysql-test/suite/sysschema/r/fn_list_drop.result
@@ -0,0 +1,20 @@
+SELECT sys.list_drop('1,2,3,4,5', '1');
+sys.list_drop('1,2,3,4,5', '1')
+2,3,4,5
+SELECT sys.list_drop('1,2,3,4,5', '3');
+sys.list_drop('1,2,3,4,5', '3')
+1,2,4,5
+SELECT sys.list_drop('1,2,3,4,5', '5');
+sys.list_drop('1,2,3,4,5', '5')
+1,2,3,4
+SELECT sys.list_drop('1, 2, 3, 4, 5', '1');
+sys.list_drop('1, 2, 3, 4, 5', '1')
+ 2, 3, 4, 5
+SELECT sys.list_drop('1, 2, 3, 4, 5', '3');
+sys.list_drop('1, 2, 3, 4, 5', '3')
+1, 2, 4, 5
+SELECT sys.list_drop('1, 2, 3, 4, 5', '5');
+sys.list_drop('1, 2, 3, 4, 5', '5')
+1, 2, 3, 4
+SELECT sys.list_drop('1,2,3,4,5', NULL);
+ERROR 02200: Function sys.list_drop: in_drop_value input variable should not be NULL
diff --git a/mysql-test/suite/sysschema/t/fn_list_add.test b/mysql-test/suite/sysschema/t/fn_list_add.test
new file mode 100644
index 0000000..d795eec
--- /dev/null
+++ b/mysql-test/suite/sysschema/t/fn_list_add.test
@@ -0,0 +1,19 @@
+-- source include/not_embedded.inc
+# Tests for sys schema
+# Verify the sys.list_add() function perfoms as expected
+
+# Should init with a single value
+SELECT sys.list_add(NULL, 'foo');
+
+# Should init with a single value
+SELECT sys.list_add('', 'foo');
+
+# Should add to the list with a comma
+SELECT sys.list_add('bar', 'foo');
+
+# Should add to the list with a single comma
+SELECT sys.list_add('bar, ', 'foo');
+
+# Should return an error with a NULL value to add
+--error 1138
+SELECT sys.list_add('foo', NULL);
diff --git a/mysql-test/suite/sysschema/t/fn_list_drop.test b/mysql-test/suite/sysschema/t/fn_list_drop.test
new file mode 100644
index 0000000..d4844b8
--- /dev/null
+++ b/mysql-test/suite/sysschema/t/fn_list_drop.test
@@ -0,0 +1,21 @@
+-- source include/not_embedded.inc
+# Tests for sys schema
+# Verify the sys.sql_mode_drop() function perfoms as expected
+
+# Remove from front of list
+SELECT sys.list_drop('1,2,3,4,5', '1');
+
+# Remove from middle of list
+SELECT sys.list_drop('1,2,3,4,5', '3');
+
+# Remove from end of list
+SELECT sys.list_drop('1,2,3,4,5', '5');
+
+# Make sure spaces are appropriately dealt with
+SELECT sys.list_drop('1, 2, 3, 4, 5', '1');
+SELECT sys.list_drop('1, 2, 3, 4, 5', '3');
+SELECT sys.list_drop('1, 2, 3, 4, 5', '5');
+
+# Should return an error with a NULL value to drop
+--error 1138
+SELECT sys.list_drop('1,2,3,4,5', NULL);
diff --git a/sys_56.sql b/sys_56.sql
index 77db615..55841d9 100644
--- a/sys_56.sql
+++ b/sys_56.sql
@@ -27,6 +27,8 @@ SOURCE ./functions/format_bytes.sql
 SOURCE ./functions/format_path.sql
 SOURCE ./functions/format_statement.sql
 SOURCE ./functions/format_time.sql
+SOURCE ./functions/list_add.sql
+SOURCE ./functions/list_drop.sql
 SOURCE ./functions/ps_is_account_enabled.sql
 SOURCE ./functions/ps_is_consumer_enabled.sql
 SOURCE ./functions/ps_is_instrument_default_enabled.sql
diff --git a/sys_57.sql b/sys_57.sql
index 9f9e340..ebb7ef0 100644
--- a/sys_57.sql
+++ b/sys_57.sql
@@ -27,6 +27,8 @@ SOURCE ./functions/format_bytes.sql
 SOURCE ./functions/format_path.sql
 SOURCE ./functions/format_statement.sql
 SOURCE ./functions/format_time.sql
+SOURCE ./functions/list_add.sql
+SOURCE ./functions/list_drop.sql
 SOURCE ./functions/ps_is_account_enabled_57.sql
 SOURCE ./functions/ps_is_consumer_enabled.sql
 SOURCE ./functions/ps_is_instrument_default_enabled.sql