Description

Opening and updating an existing function that has a different return CHARSET than the default CHARSET for the table or database. On saving, the CHARSET for the function changes to the DB default. Note: this online happens when the function created also uses COLLATE.

Please describe your pull request.
Took a bit of time to diagnose and replicate but ultimately was only happening when a function was created that had both a CHARSET and COLLATE sets.

So starting off, created 2 functions that had a different return CHARSET than the database default. (so DB was ASCII., Functions Latin1)

  DROP FUNCTION IF EXISTS test_charset_latin1_collate;
  CREATE FUNCTION test_charset_latin1_collate()
  RETURNS VARCHAR(100) CHARACTER SET LATIN1 COLLATE latin1_general_ci
  DETERMINISTIC
  RETURN 'test';

  DROP FUNCTION IF EXISTS test_charset_latin1_no_collate;
  CREATE FUNCTION test_charset_latin1_no_collate()
  RETURNS VARCHAR(100) CHARACTER SET latin1
  DETERMINISTIC
  RETURN 'test';

On viewing the 2 functions in the UI, the function with no COLLATE was correctly showing the return CHARSET

where as the function with a COLLATE set was not, and the 'charset' from the dropdown as no option was selected..

So in this instance when you saved, it set the CHARSET of the function to the database default.

This was happening due to the $options value used here

            $retval['item_returnopts_num'] = implode(' ', $options);
            $retval['item_returnopts_text'] = implode(' ', $options);

returning an array of 2 items - the CHARSET and COLLATE, so the string for item_returnopts_text used in the twig template was in my example was "latin1 latin1_general_ci", and in turned used to compare with charset.getName

{{ routine.item_returnopts_text == charset.getName() ? ' selected' }}

So my fix separately works out the charset text and numeric values. I did contemplate doing a hackier fix in the twig template but better to return the actual CHARSET to the view

Fixes #19987 Reopening a function removes charset

Before submitting pull request, please review the following checklist:

• [ X] Make sure you have read our CONTRIBUTING.md document.
• [ X] Make sure you are making a pull request against the correct branch. For example, for bug fixes in a released version use the corresponding QA branch and for new features use the master branch. If you have a doubt, you can ask as a comment in the bug report or on the mailing list.
• [ X] Every commit has proper Signed-off-by line as described in our DCO. This ensures that the work you're submitting is your own creation.
• [ X] Every commit has a descriptive commit message.
• [ X] Every commit is needed on its own, if you have just minor fixes to previous commits, you can squash them.
• Any new functionality is covered by tests.