Explicit lock mode not taken into account while choosing implicit algorithm

When the algorithm is not specified in an ALTER operation (and alter_algorithm variable value remains default DEFAULT), ALTER is supposed to be executed with the best algorithm available for the operation.

However, if locking mode is specified as a part of the operation, it is apparently ignored at the time when the algorithm is being selected. It makes ALTER later be rejected even if there is an algorithm available for the operation and for the specified locking mode.

create table t (a int);

alter table t rename to t2, lock=shared, algorithm=copy;

alter table t2 rename to t, lock=shared;

# Cleanup

drop table if exists t, t2;

alter table t rename to t2, lock=shared, algorithm=copy;

alter table t2 rename to t, lock=shared;

mysqltest: At line 3: query 'alter table t2 rename to t, lock=shared' failed: 1845: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE

So, ALTER ... RENAME is executed fine when ALGORITHM=COPY is specified explicitly along with LOCK=SHARED; but if only LOCK=SHARED is specified, ALTER doesn't consider COPY as a viable algorithm option and refuses operation claiming that LOCK=SHARED is impossible.

I think a case can be made that in some situations it might be preferable to allow ALTER take longer if necessary, but reduce the amount of locking to minimal, so using LOCK=SHARED would be a reasonable choice. With implementation of online alter (MDEV-16329) the case becomes stronger, as LOCK=NONE can make bigger difference and be more frequently desired than LOCK=SHARED.

Adding explicit ALGORITHM=COPY in this case is a weak workaround, as it enforces the copying even if the user doesn't need it (and it may happen that at some point a better algorithm emerges which could be used if COPY wasn't explicitly specified).