--source include/have_sequence.inc

create table t1 (

  a int,

  col1 varchar(100),

  col2 varchar(100),

  col3 varchar(100)

);

insert into t1 select

  seq/100,

  concat('col1-', seq),

  concat('col1-', seq),

  concat('col1-', seq)

from seq_1_to_10000;

create table t2 (

  a int,

  col1 varchar(100),

  col2 varchar(100),

  col3 varchar(100)

);

insert into t2 select

  seq/100,

  concat('col1-', seq),

  concat('col1-', seq),

  concat('col1-', seq)

from seq_1_to_20000;

analyze table t1,t2;

set join_cache_level=6;

analyze format=json

select * from t1, t2 

where 

  t1.a=t2.a

  and concat(t1.col1, t1.col2, t1.col3)= concat(t2.col1, t2.col2, t2.col3);

drop table t1,t2;

ANALYZE

{

  "query_optimization": {

    "r_total_time_ms": 0.274550914

  },

  "query_block": {

    "select_id": 1,

    "cost": 1865.900178,

    "r_loops": 1,

    "r_total_time_ms": 1258.322335,

    "nested_loop": [

      {

        "table": {

          "table_name": "t1",

          "access_type": "ALL",

          "loops": 1,

          "r_loops": 1,

          "rows": 10000,

          "r_rows": 10000,

          "cost": 1.597164,

          "r_table_time_ms": 8.369960408,

          "r_other_time_ms": 10.40811593,

          "filtered": 100,

          "r_filtered": 100,

          "attached_condition": "t1.a is not null"

        }

      },

      {

        "block-nl-join": {

          "table": {

            "table_name": "t2",

            "access_type": "hash_ALL",

            "key": "#hash#$hj",

            "key_length": "5",

            "used_key_parts": ["a"],

            "ref": ["test.t1.a"],

            "loops": 10000,

            "r_loops": 2,

            "rows": 20000,

            "r_rows": 20000,

            "cost": 1864.303014,

            "r_table_time_ms": 57.63327534,

            "r_other_time_ms": 16.21655048,

            "filtered": 100,

            "r_filtered": 100

          },

          "buffer_type": "flat",

          "buffer_size": "256Kb",

          "join_type": "BNLH",

          "attached_condition": "t2.a = t1.a and concat(t1.col1,t1.col2,t1.col3) = concat(t2.col1,t2.col2,t2.col3)",

          "r_filtered": 1.002505261,

          "r_unpack_time_ms": 135.8873441

        }

      }

    ]

  }

}

  "query_block": {

    "r_total_time_ms": 1258.322335,

    "nested_loop": [

      {

        "table": {

          "table_name": "t1",

          "r_table_time_ms": 8.369960408,

          "r_other_time_ms": 10.40811593,

        }

      },

      {

        "block-nl-join": {

          "table": {

            "table_name": "t2",

            "r_table_time_ms": 57.63327534,

            "r_other_time_ms": 16.21655048,

          },

          "r_unpack_time_ms": 135.8873441

        }

      }

    ]

  }