set

  @tmp_switch_sarg_casefold=@@optimizer_switch,

  optimizer_switch='sargable_casefold=on';

create table t3 (

  a varchar(32),

  b varchar(32),

  key(a),

  key(b)

) collate utf8mb3_general_ci;

insert into t3 values ('abc','ABC'), ('xyz','XYZ'), ('123','abc'), ('456','xyz');

explain format=json

select* from t3 where upper(b)='ABC';

select* from t3 where upper(b)='ABC';

explain format=json

update t3 set a='00' where upper(b)='ABC';

update t3 set a='00' where upper(b)='ABC';

select a from t3;

explain format=json

delete from t3 where upper(b)='ABC';

delete from t3 where upper(b)='ABC';

drop table t3;

set optimizer_switch=@tmp_switch_sarg_casefold;

select* from t3 where upper(b)='ABC';

EXPLAIN

{

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "t3",

      "access_type": "ref",

      "possible_keys": ["b"],

      "key": "b",

      "key_length": "99",

      "used_key_parts": ["b"],

      "ref": ["const"],

      "rows": 2,

      "filtered": 100,

      "index_condition": "t3.b = 'ABC'"

    }

  }

}

update t3 set a='00' where upper(b)='ABC';

EXPLAIN

{

  "query_block": {

    "select_id": 1,

    "table": {

      "update": 1,

      "table_name": "t3",

      "access_type": "ALL",

      "rows": 4,

      "attached_condition": "ucase(t3.b) = 'ABC'"

    }

  }

}

delete from t3 where upper(b)='ABC';

EXPLAIN

{

  "query_block": {

    "select_id": 1,

    "table": {

      "delete": 1,

      "table_name": "t3",

      "access_type": "ALL",

      "rows": 4,

      "attached_condition": "ucase(t3.b) = 'ABC'"

    }

  }

}