https://github.com/MariaDB/server/pull/2016 that serg looked over and I'm pretty sure every comment was addressed.

I'll look at rebasing (10.9) and squashing commits once the release is over.

Pushed as preview-10.10-MDEV-25704-random_bytes

This is identical to the updated PR2016.

It's in this branch: preview-10.10-misc.

also in bb-10.10-MDEV-25704 without other preview features

In my opinion, the feature as of bb-10.10-MDEV-25704 bb1469017 can be pushed into 10.10 main and released with 10.10.1.

Note: The remark about NULL-ability of a resulting column upon CREATE TABLE .. SELECT RANDOM_BYTES(N), repeated twice in the pull request discussion, has not been addressed. I assume it was a conscious decision to keep it in line with existing functions, specifically REPEAT mentioned as a counter-argument.

nikitamalyavin, let's revive this NULL-ability question. You wrote (I'll quote from the PR to move the discussion here):

I've checked several Item_str_func descendants – they all call set_maybe_null, like the Item_func_repeat mentioned in the related comment on the test result.
I suggest to remain consistent – if we change this behavior, then we change it everywhere

Indeed, Item_func_repeat always is always nullable. Because, see Item_func_repeat::val_str() it returns NULL when length of the result is larger than the max_allowed_packet. That is, REPEAT() can return NULL when both arguments are not NULL.

As far as I can see, RANDOM_BYTES(N) cannot return NULL when its argument is not NULL.

serg I didn't know about that variable. Shouldn't RANDOM_BYTES do the same?
BTW sohuldn't the same apply to sql_mode=EMPTY_STRING_IS_NULL, or is it handled somewhere separately, too?

N in RANDOM_BYTES(N) must be between 1 and 1024, so RANDOM_BYTES can never return an empty string or anything larger than max_allowed_packet (which, itself, cannot be less than 1024)

Right... sorry, i didn't check Item_func_random_bytes::val_str before asking, only RAND_bytes function description, since I remembered something about the length restriction, but nothing was there.

So, why "if (length > 1024) then NULL", and "if (length > max_allowed_packet) then NULL" should behave differently?

EDIT: sorry, got the difference. One is empty, and anothe one is NULL.

Ok, so now I remember my thoughts around sql_mode=EMPTY_STRING_IS_NULL.

It is set in oracle mode, I guess it means that RANDOM_BYTES(1025) is NULL as well as empty in mysql (or is this mode about Oracle database, not mysql?)

We can either stick with current behavior, but change fix_length_and_dec to:

if (thd->variables.sql_mode & MODE_EMPTY_STRING_IS_NULL)

  set_maybe_null()

or change it to return NULL, which is, maybe, more conformant with our style (i guess)

I suggest:

• allow RANDOM_BYTES(0) and return an empty string. Why should this be disabled?
• return NULL for N > 1024, that's more in line with what other functions do for invalid argument values.

of course, the function should stay nullable, if we do the above.

As suggested, I implemented this at the end of bb-10.10-MDEV-25704 and tested for mysql 8,0 compatibility. select random_bytes(0) is our extension. Note the fixme on TIME conversion in the test. Suggestions on implementing this much appreciated.

bb-10.10-MDEV-25704 as of ce1c50665 is NOT OK to push into the main tree, it fails in buildbot and otherwise.

fixed most. Debug builds only of query_cache test failing because args[0]->can_eval_in_optimize() changes from false to true between Item_func_random_bytes::fix_length_and_dec and Item_func_random_bytes::val_str. This is due to args[0]->table->const_table becoming true.

Ok, buildbot happy again. serg, nikitamalyavin can I get a review on the last two commits on the bb-10.10-MDEV-25704 branch.

I have no further objections against pushing bb-10.10-MDEV-25704 as of 7f422495 into 10.10 main and releasing it with 10.10.1.