-- 8.0.40 - not affected

bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.40 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table test4(col1 varchar(40), col2 int);
Query OK, 0 rows affected (0.02 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test4
    -> values
    ->  ('3134'        , 88663422)
    -> ,('3135'        , 88663423)
    -> ,('3137'        , 88663425)
    -> ,('38866001'    , 88663425)
    -> ,('388660010001', 162023)
    -> ,('388660010002', 88664116)
    -> ,('38866002'    , 88663697)
    -> ,('388660020001', 88663697)
    -> ,('388660020002', 88664117)
    -> ,('38866003'    , 88663724)
    -> ,('388660030001', 88663724)
    -> ,('388660030002', 88664247)
    -> ,('38866004'    , 88663763)
    -> ,('388660040001', 88663763)
    -> ,('388660040002', 88664238)
    -> ,('38866005'    , 88663972)
    -> ,('388660050001', 88663972)
    -> ,('388660050002', 88664163)
    -> ,('388660050003', 88664222)
    -> ,('38866006'    , 88664031)
  , 88663456)
,('3170'        , 88663458)
,('3172'        , 88663460)
,('3183'        , 88663471)
,('3185'        , 88663473)
,('3187'        , 88663475)
,('3189'        , 88663477)
,('3193'        , 88663481)
,('3194'        , 88663482)
,('3198'        , 88663486)
,('3199'        , 88663487)
,('3202'        , 88663490)
,('3204'        , 88663492)
,('3207'        , 88663495)
,('3213'        , 88663501)
,('3216'        , 88663504)
,('3218'        , 88663506)
,('3220'        , 88663508)
,('3224'        , 88663512)
,('3227'        , 88663515)
,('3236'        , 88663524)
,('3238'        , 88663526)
,('3243'        , 88663531)
,('3245'        , 88663533)
,('3246'        , 88663534)
,('3247'        , 88663535)
,('3248'        , 88663536)
,('3249'        , 88663537)
,('3256'        , 88663544)
,('3257'        , 88663545)
,('3259'        , 88663547)
,('3263'        , 88663551)
,('3265'        , 88663553)
,('3267'        , 88663555)
,('3271'        , 88663559)
,('3275'        , 88663563)
,('3280'        , 88663568)
,(    -> ,('388660060001', 88664031)
    -> ,('388660060002', 88664183)
    -> ,('3138'        , 88663426)
    -> ,('3140'        , 88663428)
    -> ,('3145'        , 88663433)
    -> ,('3147'        , 88663435)
    -> ,('3148'        , 88663436)
    -> ,('3149'        , 88663437)
    -> ,('3150'        , 88663438)
    -> ,('3151'        , 88663439)
    -> ,('3154'        , 88663442)
    -> ,('3155'        , 88663443)
    -> ,('3158'        , 88663446)
    -> ,('3160'        , 88663448)
    -> ,('3165'        , 88663453)
    -> ,('3168'        , 88663456)
    -> ,('3170'        , 88663458)
    -> ,('3172'        , 88663460)
    -> ,('3183'        , 88663471)
    -> ,('3185'        , 88663473)
    -> ,('3187'        , 88663475)
    -> ,('3189'        , 88663477)
    -> ,('3193'        , 88663481)
    -> ,('3194'        , 88663482)
    -> ,('3198'        , 88663486)
    -> ,('3199'        , 88663487)
    -> ,('3202'        , 88663490)
    -> ,('3204'        , 88663492)
    -> ,('3207'        , 88663495)
    -> ,('3213'        , 88663501)
    -> ,('3216'        , 88663504)
    -> ,('3218'        , 88663506)
    -> ,('3220'        , 88663508)
    -> ,('3224'        , 88663512)
    -> ,('3227'        , 88663515)
    -> ,('3236'        , 88663524)
    -> ,('3238'        , 88663526)
    -> ,('3243'        , 88663531)
    -> ,('3245'        , 88663533)
    -> ,('3246'        , 88663534)
    -> ,('3247'        , 88663535)
    -> ,('3248'        , 88663536)
    -> ,('3249'        , 88663537)
    -> ,('3256'        , 88663544)
    -> ,('3257'        , 88663545)
    -> ,('3259'        , 88663547)
    -> ,('3263'        , 88663551)
    -> ,('3265'        , 88663553)
    -> ,('3267'        , 88663555)
    -> ,('3271'        , 88663559)
    -> ,('3275'        , 88663563)
    -> ,('3280'        , 88663568)
    -> ,('3284'        , 88664531)
    -> ,('3285'        , 88664532)
    -> ,('3286'        , 88664533)
    -> ,('3292'        , 88664849);
Query OK, 76 rows affected (0.01 sec)
Records: 76  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+--------------+
| col1     | rcol1    | col2     | col1         |
+----------+----------+----------+--------------+
| 38866001 | 38866001 | 88663425 | 38866001     |
| 38866001 | 38866001 |   162023 | 388660010001 |
| 38866001 | 38866001 | 88664116 | 388660010002 |
+----------+----------+----------+--------------+
3 rows in set (0.00 sec)

mysql> explain format=tree with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop antijoin
    -> Filter: (a.col1 like concat(t.col1,'%'))
        -> Inner hash join (no condition)
            -> Table scan on a  (cost=0.137 rows=76)
            -> Hash
                -> Nested loop inner join
                    -> Nested loop inner join
                        -> Filter: (a.col1 like concat(t.col1,'%'))
                            -> Inner hash join (a.col1 = t.col1)
                                -> Table scan on a  (cost=0.175 rows=76)
                                -> Hash
                                    -> Filter: (t.col1 is not null)
                                        -> Materialize table function
                        -> Materialize table function
                    -> Materialize table function
    -> Nested loop inner join
        -> Materialize table function
        -> Filter: ((a.col2 = a.col2) and (a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1) and (a.col1 like concat(t.col1,'%')))  (cost=46.6 rows=76)
            -> Table scan on a  (cost=46.6 rows=76)

1 row in set, 4 warnings (0.00 sec)

mysql> create index ind_test4_01 on test4(col1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ind_test4_02 on test4(col2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+--------------+
| col1     | rcol1    | col2     | col1         |
+----------+----------+----------+--------------+
| 38866001 | 38866001 | 88663425 | 38866001     |
| 38866001 | 38866001 |   162023 | 388660010001 |
| 38866001 | 38866001 | 88664116 | 388660010002 |
+----------+----------+----------+--------------+
3 rows in set (0.00 sec)

mysql> explain format=tree with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop antijoin
    -> Inner hash join (t.col1 = t.col1)
        -> Materialize table function
        -> Hash
            -> Filter: (a.col1 like concat(t.col1,'%'))
                -> Inner hash join (t.col1 = t.col1)
                    -> Materialize table function
                    -> Hash
                        -> Filter: (a.col1 like concat(t.col1,'%'))
                            -> Inner hash join (no condition)
                                -> Table scan on a  (cost=0.547 rows=76)
                                -> Hash
                                    -> Nested loop inner join
                                        -> Filter: (t.col1 is not null)
                                            -> Materialize table function
                                        -> Covering index lookup on a using ind_test4_01 (col1=t.col1)  (cost=0.3 rows=1)
    -> Limit: 1 row(s)
        -> Nested loop inner join
            -> Filter: ((a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1))  (cost=0.278 rows=1.09)
                -> Index lookup on a using ind_test4_02 (col2=a.col2)  (cost=0.278 rows=1.09)
            -> Filter: ((t.col1 = t.col1) and (a.col1 like concat(t.col1,'%')))
                -> Materialize table function

1 row in set, 4 warnings (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

-- 8.0.18 -- affected

bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql>
mysql> create table test4(col1 varchar(40), col2 int);
663448)
,('3165'        , 88663453)
,('3168'        , 88663456)
,('3170'        , 88663458)
,('3172'        , 88663460)
,('3183'        , 88663471)
,('3185'        , 88663473)
,('3187'        , 88663475)
,('3189'        , 88663477)
,('3193'        , 88663481)
,('3194'        , 88663482)
,('3198'        , 88663486)
,('3199'        , 88663487)
,('3202'        , 88663490)
,('3204'        , 88663492)
,('3207'        , 88663495)
,('3213'        , 88663501)
,('3216'        , 88663504)
,('3218'        , 88663506)
,('3220'        , 88663508)
,('3224'        , 88663512)
,('3227'        , 88663515)
,('3236'        , 88663524)
,('3238'        , 88663526)
,('3243'        , 88663531)
,('3245'        , 88663533)
,('3246'        , 88663534)
,('3247'        , 88663535)
,('3248'        , 88663536)
,('3249'        , 88663537)
,('3256'        , 88663544)
,('3257'        , 88663545)
,('3259'        , 88663547)
,('3263'        , 88663551)
,('3265'        , 88663553)
,('3267'        , 88663555)
,('3271'        , 88663559)
,('3275'Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test4
    -> values
    ->  ('3134'        , 88663422)
    -> ,('3135'        , 88663423)
    -> ,('3137'        , 88663425)
    -> ,('38866001'    , 88663425)
    -> ,('388660010001', 162023)
    -> ,('388660010002', 88664116)
    -> ,('38866002'    , 88663697)
    -> ,('388660020001', 88663697)
    -> ,('388660020002', 88664117)
    -> ,('38866003'    , 88663724)
    -> ,('388660030001', 88663724)
    -> ,('388660030002', 88664247)
    -> ,('38866004'    , 88663763)
    -> ,('388660040001', 88663763)
    -> ,('388660040002', 88664238)
    -> ,('38866005'    , 88663972)
    -> ,('388660050001', 88663972)
    -> ,('388660050002', 88664163)
    -> ,('388660050003', 88664222)
    -> ,('38866006'    , 88664031)
    -> ,('388660060001', 88664031)
    -> ,('388660060002', 88664183)
    -> ,('3138'        , 88663426)
    -> ,('3140'        , 88663428)
        , 88663563)
,('3280'        , 88663568)
,('3284'        , 88664531)
,('3285'        , 88664532)
,('3286'        , 88664533)
,('3292'        , 88664849);


commit;
    -> ,('3145'        , 88663433)
    -> ,('3147'        , 88663435)
    -> ,('3148'        , 88663436)
    -> ,('3149'        , 88663437)
    -> ,('3150'        , 88663438)
    -> ,('3151'        , 88663439)
    -> ,('3154'        , 88663442)
    -> ,('3155'        , 88663443)
    -> ,('3158'        , 88663446)
    -> ,('3160'        , 88663448)
    -> ,('3165'        , 88663453)
    -> ,('3168'        , 88663456)
    -> ,('3170'        , 88663458)
    -> ,('3172'        , 88663460)
    -> ,('3183'        , 88663471)
    -> ,('3185'        , 88663473)
    -> ,('3187'        , 88663475)
    -> ,('3189'        , 88663477)
    -> ,('3193'        , 88663481)
    -> ,('3194'        , 88663482)
    -> ,('3198'        , 88663486)
    -> ,('3199'        , 88663487)
    -> ,('3202'        , 88663490)
    -> ,('3204'        , 88663492)
    -> ,('3207'        , 88663495)
    -> ,('3213'        , 88663501)
    -> ,('3216'        , 88663504)
    -> ,('3218'        , 88663506)
    -> ,('3220'        , 88663508)
    -> ,('3224'        , 88663512)
    -> ,('3227'        , 88663515)
    -> ,('3236'        , 88663524)
    -> ,('3238'        , 88663526)
    -> ,('3243'        , 88663531)
    -> ,('3245'        , 88663533)
    -> ,('3246'        , 88663534)
    -> ,('3247'        , 88663535)
    -> ,('3248'        , 88663536)
    -> ,('3249'        , 88663537)
    -> ,('3256'        , 88663544)
    -> ,('3257'        , 88663545)
    -> ,('3259'        , 88663547)
    -> ,('3263'        , 88663551)
    -> ,('3265'        , 88663553)
    -> ,('3267'        , 88663555)
    -> ,('3271'        , 88663559)
    -> ,('3275'        , 88663563)
    -> ,('3280'        , 88663568)
    -> ,('3284'        , 88664531)
    -> ,('3285'        , 88664532)
    -> ,('3286'        , 88664533)
    -> ,('3292'        , 88664849);
Query OK, 76 rows affected (0.00 sec)
Records: 76  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+--------------+
| col1     | rcol1    | col2     | col1         |
+----------+----------+----------+--------------+
| 38866001 | 38866001 | 88663425 | 38866001     |
| 38866001 | 38866001 |   162023 | 388660010001 |
| 38866001 | 38866001 | 88664116 | 388660010002 |
+----------+----------+----------+--------------+
3 rows in set (0.00 sec)

mysql> explain format=tree with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop anti-join
    -> Filter: (a.col1 like concat(t.col1,'%'))
        -> Inner hash join
            -> Table scan on a  (cost=0.14 rows=76)
            -> Hash
                -> Nested loop inner join
                    -> Nested loop inner join
                        -> Filter: (a.col1 like concat(t.col1,'%'))
                            -> Inner hash join (a.col1 = t.col1)
                                -> Table scan on a  (cost=0.18 rows=76)
                                -> Hash
                                    -> Filter: (t.col1 is not null)
                                        -> Materialize table function
                        -> Materialize table function
                    -> Materialize table function
    -> Nested loop inner join
        -> Materialize table function
        -> Filter: ((a.col2 = a.col2) and (a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1) and (a.col1 like concat(t.col1,'%')))  (cost=46.55 rows=76)
            -> Table scan on a  (cost=46.55 rows=76)

1 row in set, 4 warnings (0.00 sec)

mysql> create index ind_test4_01 on test4(col1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ind_test4_02 on test4(col2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+----------+
| col1     | rcol1    | col2     | col1     |
+----------+----------+----------+----------+
| 38866001 | 38866001 | 88663425 | 38866001 |
+----------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)


-- 8.0.19 -- affected

bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql>
mysql> create table test4(col1 varchar(40), col2 int);
663448)
,('3165'        , 88663453)
,('3168'        , 88663456)
,('3170'        , 88663458)
,('3172'        , 88663460)
,('3183'        , 88663471)
,('3185'        , 88663473)
,('3187'        , 88663475)
,('3189'        , 88663477)
,('3193'        , 88663481)
,('3194'        , 88663482)
,('3198'        , 88663486)
,('3199'        , 88663487)
,('3202'        , 88663490)
,('3204'        , 88663492)
,('3207'        , 88663495)
,('3213'        , 88663501)
,('3216'        , 88663504)
,('3218'        , 88663506)
,('3220'        , 88663508)
,('3224'        , 88663512)
,('3227'        , 88663515)
,('3236'        , 88663524)
,('3238'        , 88663526)
,('3243'        , 88663531)
,('3245'        , 88663533)
,('3246'        , 88663534)
,('3247'        , 88663535)
,('3248'        , 88663536)
,('3249'        , 88663537)
,('3256'        , 88663544)
,('3257'        , 88663545)
,('3259'        , 88663547)
,('3263'        , 88663551)
,('3265'        , 88663553)
,('3267'        , 88663555)
,('3271'        , 88663559)
,('3275'Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test4
    -> values
    ->  ('3134'        , 88663422)
    -> ,('3135'        , 88663423)
    -> ,('3137'        , 88663425)
    -> ,('38866001'    , 88663425)
    -> ,('388660010001', 162023)
    -> ,('388660010002', 88664116)
    -> ,('38866002'    , 88663697)
    -> ,('388660020001', 88663697)
    -> ,('388660020002', 88664117)
    -> ,('38866003'    , 88663724)
    -> ,('388660030001', 88663724)
    -> ,('388660030002', 88664247)
    -> ,('38866004'    , 88663763)
    -> ,('388660040001', 88663763)
    -> ,('388660040002', 88664238)
    -> ,('38866005'    , 88663972)
    -> ,('388660050001', 88663972)
    -> ,('388660050002', 88664163)
    -> ,('388660050003', 88664222)
    -> ,('38866006'    , 88664031)
    -> ,('388660060001', 88664031)
        , 88663563)
,('3280'        , 88663568)
,('3284'        , 88664531)
,('3285'        , 88664532)
,('3286'        , 88664533)
,('3292'        , 88664849);


commit;
    -> ,('388660060002', 88664183)
    -> ,('3138'        , 88663426)
    -> ,('3140'        , 88663428)
    -> ,('3145'        , 88663433)
    -> ,('3147'        , 88663435)
    -> ,('3148'        , 88663436)
    -> ,('3149'        , 88663437)
    -> ,('3150'        , 88663438)
    -> ,('3151'        , 88663439)
    -> ,('3154'        , 88663442)
    -> ,('3155'        , 88663443)
    -> ,('3158'        , 88663446)
    -> ,('3160'        , 88663448)
    -> ,('3165'        , 88663453)
    -> ,('3168'        , 88663456)
    -> ,('3170'        , 88663458)
    -> ,('3172'        , 88663460)
    -> ,('3183'        , 88663471)
    -> ,('3185'        , 88663473)
    -> ,('3187'        , 88663475)
    -> ,('3189'        , 88663477)
    -> ,('3193'        , 88663481)
    -> ,('3194'        , 88663482)
    -> ,('3198'        , 88663486)
    -> ,('3199'        , 88663487)
    -> ,('3202'        , 88663490)
    -> ,('3204'        , 88663492)
    -> ,('3207'        , 88663495)
    -> ,('3213'        , 88663501)
    -> ,('3216'        , 88663504)
    -> ,('3218'        , 88663506)
    -> ,('3220'        , 88663508)
    -> ,('3224'        , 88663512)
    -> ,('3227'        , 88663515)
    -> ,('3236'        , 88663524)
    -> ,('3238'        , 88663526)
    -> ,('3243'        , 88663531)
    -> ,('3245'        , 88663533)
    -> ,('3246'        , 88663534)
    -> ,('3247'        , 88663535)
    -> ,('3248'        , 88663536)
    -> ,('3249'        , 88663537)
    -> ,('3256'        , 88663544)
    -> ,('3257'        , 88663545)
    -> ,('3259'        , 88663547)
    -> ,('3263'        , 88663551)
    -> ,('3265'        , 88663553)
    -> ,('3267'        , 88663555)
    -> ,('3271'        , 88663559)
    -> ,('3275'        , 88663563)
    -> ,('3280'        , 88663568)
    -> ,('3284'        , 88664531)
    -> ,('3285'        , 88664532)
    -> ,('3286'        , 88664533)
    -> ,('3292'        , 88664849);
Query OK, 76 rows affected (0.01 sec)
Records: 76  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+--------------+
| col1     | rcol1    | col2     | col1         |
+----------+----------+----------+--------------+
| 38866001 | 38866001 | 88663425 | 38866001     |
| 38866001 | 38866001 |   162023 | 388660010001 |
| 38866001 | 38866001 | 88664116 | 388660010002 |
+----------+----------+----------+--------------+
3 rows in set (0.00 sec)

mysql> explain format=tree with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop anti-join
    -> Filter: (a.col1 like concat(t.col1,'%'))
        -> Inner hash join
            -> Table scan on a  (cost=0.14 rows=76)
            -> Hash
                -> Nested loop inner join
                    -> Nested loop inner join
                        -> Filter: (a.col1 like concat(t.col1,'%'))
                            -> Inner hash join (a.col1 = t.col1)
                                -> Table scan on a  (cost=0.18 rows=76)
                                -> Hash
                                    -> Filter: (t.col1 is not null)
                                        -> Materialize table function
                        -> Materialize table function
                    -> Materialize table function
    -> Nested loop inner join
        -> Materialize table function
        -> Filter: ((a.col2 = a.col2) and (a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1) and (a.col1 like concat(t.col1,'%')))  (cost=46.55 rows=76)
            -> Table scan on a  (cost=46.55 rows=76)

1 row in set, 4 warnings (0.01 sec)

mysql>
mysql> create index ind_test4_01 on test4(col1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ind_test4_02 on test4(col2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+----------+
| col1     | rcol1    | col2     | col1     |
+----------+----------+----------+----------+
| 38866001 | 38866001 | 88663425 | 38866001 |
+----------+----------+----------+----------+
1 row in set (0.00 sec)

mysql>
mysql> explain format=tree with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Remove duplicate (t, a, a, t, t) rows using temporary table (weedout)
    -> Nested loop anti-join
        -> Inner hash join (t.col1 = t.col1)
            -> Materialize table function
            -> Hash
                -> Filter: (a.col1 like concat(t.col1,'%'))
                    -> Inner hash join (t.col1 = t.col1)
                        -> Materialize table function
                        -> Hash
                            -> Filter: (a.col1 like concat(t.col1,'%'))
                                -> Inner hash join
                                    -> Table scan on a  (cost=0.55 rows=76)
                                    -> Hash
                                        -> Nested loop inner join
                                            -> Filter: (t.col1 is not null)
                                                -> Materialize table function
                                            -> Index lookup on a using ind_test4_01 (col1=t.col1)  (cost=0.30 rows=1)
        -> Nested loop inner join
            -> Filter: ((a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1))  (cost=0.28 rows=1)
                -> Index lookup on a using ind_test4_02 (col2=a.col2)  (cost=0.28 rows=1)
            -> Filter: ((t.col1 = t.col1) and (a.col1 like concat(t.col1,'%')))
                -> Materialize table function

1 row in set, 4 warnings (0.00 sec)

mysql>

-- 8.0.20 - not affected

bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table test4(col1 varchar(40), col2 int);
63448)
,('3165'        , 88663453)
,('3168'        , 88663456)
,('3170'        , 88663458)
,('3172'        , 88663460)
,('3183'        , 88663471)
,('3185'        , 88663473)
,('3187'        , 88663475)
,('3189'        , 88663477)
,('3193'        , 88663481)
,('3194'        , 88663482)
,('3198'        , 88663486)
,('3199'        , 88663487)
,('3202'        , 88663490)
,('3204'        , 88663492)
,('3207'        , 88663495)
,('3213'        , 88663501)
,('3216'        , 88663504)
,('3218'        , 88663506)
,('3220'        , 88663508)
,('3224'        , 88663512)
,('3227'        , 88663515)
,('3236'        , 88663524)
,('3238'        , 88663526)
,('3243'        , 88663531)
,('3245'        , 88663533)
,('3246'        , 88663534)
,('3247'        , 88663535)
,('3248'        , 88663536)
,('3249'        , 88663537)
,('3256'        , 88663544)
,('3257'        , 88663545)
,('3259'        , 88663547)
,('3263'        , 88663551)
,('3265'        , 88663553)
,('3267'        , 88663555)
,('3271'        , 88663559)
,('3275Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test4
    -> values
    ->  ('3134'        , 88663422)
    -> ,('3135'        , 88663423)
    -> ,('3137'        , 88663425)
    -> ,('38866001'    , 88663425)
    -> ,('388660010001', 162023)
    -> ,('388660010002', 88664116)
    -> ,('38866002'    , 88663697)
'     -> ,('388660020001', 88663697)
    -> ,('388660020002', 88664117)
    -> ,('38866003'    , 88663724)
    -> ,('388660030001', 88663724)
    -> ,('388660030002', 88664247)
    -> ,('38866004'    , 88663763)
    -> ,('388660040001', 88663763)
    -> ,('388660040002', 88664238)
    -> ,('38866005'    , 88663972)
    -> ,('388660050001', 88663972)
    -> ,('388660050002', 88664163)
    -> ,('388660050003', 88664222)
    -> ,('38866006'    , 88664031)
    -> ,('388660060001', 88664031)
    -> ,('388660060002', 88664183)
       , 88663563)
,('3280'        , 88663568)
,('3284'        , 88664531)
,('3285'        , 88664532)
,('3286'        , 88664533)
,('3292'        , 88664849);


commit;
    -> ,('3138'        , 88663426)
    -> ,('3140'        , 88663428)
    -> ,('3145'        , 88663433)
    -> ,('3147'        , 88663435)
    -> ,('3148'        , 88663436)
    -> ,('3149'        , 88663437)
    -> ,('3150'        , 88663438)
    -> ,('3151'        , 88663439)
    -> ,('3154'        , 88663442)
    -> ,('3155'        , 88663443)
    -> ,('3158'        , 88663446)
    -> ,('3160'        , 88663448)
    -> ,('3165'        , 88663453)
    -> ,('3168'        , 88663456)
    -> ,('3170'        , 88663458)
    -> ,('3172'        , 88663460)
    -> ,('3183'        , 88663471)
    -> ,('3185'        , 88663473)
    -> ,('3187'        , 88663475)
    -> ,('3189'        , 88663477)
    -> ,('3193'        , 88663481)
    -> ,('3194'        , 88663482)
    -> ,('3198'        , 88663486)
    -> ,('3199'        , 88663487)
    -> ,('3202'        , 88663490)
    -> ,('3204'        , 88663492)
    -> ,('3207'        , 88663495)
    -> ,('3213'        , 88663501)
    -> ,('3216'        , 88663504)
    -> ,('3218'        , 88663506)
    -> ,('3220'        , 88663508)
    -> ,('3224'        , 88663512)
    -> ,('3227'        , 88663515)
    -> ,('3236'        , 88663524)
    -> ,('3238'        , 88663526)
    -> ,('3243'        , 88663531)
    -> ,('3245'        , 88663533)
    -> ,('3246'        , 88663534)
    -> ,('3247'        , 88663535)
    -> ,('3248'        , 88663536)
    -> ,('3249'        , 88663537)
    -> ,('3256'        , 88663544)
    -> ,('3257'        , 88663545)
    -> ,('3259'        , 88663547)
    -> ,('3263'        , 88663551)
    -> ,('3265'        , 88663553)
    -> ,('3267'        , 88663555)
    -> ,('3271'        , 88663559)
    -> ,('3275'        , 88663563)
    -> ,('3280'        , 88663568)
    -> ,('3284'        , 88664531)
    -> ,('3285'        , 88664532)
    -> ,('3286'        , 88664533)
    -> ,('3292'        , 88664849);
Query OK, 76 rows affected (0.00 sec)
Records: 76  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+--------------+
| col1     | rcol1    | col2     | col1         |
+----------+----------+----------+--------------+
| 38866001 | 38866001 | 88663425 | 38866001     |
| 38866001 | 38866001 |   162023 | 388660010001 |
| 38866001 | 38866001 | 88664116 | 388660010002 |
+----------+----------+----------+--------------+
3 rows in set (0.01 sec)

mysql> explain format=tree with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop antijoin
    -> Filter: (a.col1 like concat(t.col1,'%'))
        -> Inner hash join (no condition)
            -> Table scan on a  (cost=0.14 rows=76)
            -> Hash
                -> Nested loop inner join
                    -> Nested loop inner join
                        -> Filter: (a.col1 like concat(t.col1,'%'))
                            -> Inner hash join (a.col1 = t.col1)
                                -> Table scan on a  (cost=0.18 rows=76)
                                -> Hash
                                    -> Filter: (t.col1 is not null)
                                        -> Materialize table function
                        -> Materialize table function
                    -> Materialize table function
    -> Nested loop inner join
        -> Materialize table function
        -> Filter: ((a.col2 = a.col2) and (a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1) and (a.col1 like concat(t.col1,'%')))  (cost=46.55 rows=76)
            -> Table scan on a  (cost=46.55 rows=76)

1 row in set, 4 warnings (0.00 sec)

mysql>
mysql> create index ind_test4_01 on test4(col1);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ind_test4_02 on test4(col2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+--------------+
| col1     | rcol1    | col2     | col1         |
+----------+----------+----------+--------------+
| 38866001 | 38866001 | 88663425 | 38866001     |
| 38866001 | 38866001 |   162023 | 388660010001 |
| 38866001 | 38866001 | 88664116 | 388660010002 |
+----------+----------+----------+--------------+
3 rows in set (0.01 sec)

mysql> explain format=tree with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop antijoin
    -> Inner hash join (t.col1 = t.col1)
        -> Materialize table function
        -> Hash
            -> Filter: (a.col1 like concat(t.col1,'%'))
                -> Inner hash join (t.col1 = t.col1)
                    -> Materialize table function
                    -> Hash
                        -> Filter: (a.col1 like concat(t.col1,'%'))
                            -> Inner hash join (no condition)
                                -> Table scan on a  (cost=0.55 rows=76)
                                -> Hash
                                    -> Nested loop inner join
                                        -> Filter: (t.col1 is not null)
                                            -> Materialize table function
                                        -> Index lookup on a using ind_test4_01 (col1=t.col1)  (cost=0.30 rows=1)
    -> Limit: 1 row(s)
        -> Nested loop inner join
            -> Filter: ((a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1))  (cost=0.28 rows=1)
                -> Index lookup on a using ind_test4_02 (col2=a.col2)  (cost=0.28 rows=1)
            -> Filter: ((t.col1 = t.col1) and (a.col1 like concat(t.col1,'%')))
                -> Materialize table function

1 row in set, 4 warnings (0.00 sec)

mysql>

-- 8.0.21 - not affected

bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql>
mysql> create table test4(col1 varchar(40), col2 int);
663448)
,('3165'        , 88663453)
,('3168'        , 88663456)
,('3170'        , 88663458)
,('3172'        , 88663460)
,('3183'        , 88663471)
,('3185'        , 88663473)
,('3187'        , 88663475)
,('3189'        , 88663477)
,('3193'        , 88663481)
,('3194'        , 88663482)
,('3198'        , 88663486)
,('3199'        , 88663487)
,('3202'        , 88663490)
,('3204'        , 88663492)
,('3207'        , 88663495)
,('3213'        , 88663501)
,('3216'        , 88663504)
,('3218'        , 88663506)
,('3220'        , 88663508)
,('3224'        , 88663512)
,('3227'        , 88663515)
,('3236'        , 88663524)
,('3238'        , 88663526)
,('3243'        , 88663531)
,('3245'        , 88663533)
,('3246'        , 88663534)
,('3247'        , 88663535)
,('3248'        , 88663536)
,('3249'        , 88663537)
,('3256'        , 88663544)
,('3257'        , 88663545)
,('3259'        , 88663547)
,('3263'        , 88663551)
,('3265'        , 88663553)
,('3267'        , 88663555)
,('3271'        , 88663559)
,('3275'Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test4
    -> values
    ->  ('3134'        , 88663422)
    -> ,('3135'        , 88663423)
    -> ,('3137'        , 88663425)
    -> ,('38866001'    , 88663425)
    -> ,('388660010001', 162023)
    -> ,('388660010002', 88664116)
    -> ,('38866002'    , 88663697)
    -> ,('388660020001', 88663697)
    -> ,('388660020002', 88664117)
    -> ,('38866003'    , 88663724)
    -> ,('388660030001', 88663724)
    -> ,('388660030002', 88664247)
    -> ,('38866004'    , 88663763)
    -> ,('388660040001', 88663763)
    -> ,('388660040002', 88664238)
    -> ,('38866005'    , 88663972)
    -> ,('388660050001', 88663972)
    -> ,('388660050002', 88664163)
    -> ,('388660050003', 88664222)
    -> ,('38866006'    , 88664031)
    -> ,('388660060001', 88664031)
    -> ,('388660060002', 88664183)
    -> ,('3138'        , 88663426)
    -> ,('3140'        , 88663428)
    -> ,('3145'        , 88663433)
    -> ,('3147'        , 88663435)
    -> ,('3148'        , 88663436)
    -> ,('3149'        , 88663437)
    -> ,('3150'        , 88663438)
    -> ,('3151'        , 88663439)
    -> ,('3154'        , 88663442)
    -> ,('3155'        , 88663443)
    -> ,('3158'        , 88663446)
    -> ,('3160'        , 88663448)
    -> ,('3165'        , 88663453)
    -> ,('3168'        , 88663456)
    -> ,('3170'        , 88663458)
    -> ,('3172'        , 88663460)
    -> ,('3183'        , 88663471)
    -> ,('3185'        , 88663473)
    -> ,('3187'        , 88663475)
    -> ,('3189'        , 88663477)
    -> ,('3193'        , 88663481)
    -> ,('3194'        , 88663482)
    -> ,('3198'        , 88663486)
    -> ,('3199'        , 88663487)
    -> ,('3202'        , 88663490)
    -> ,('3204'        , 88663492)
    -> ,('3207'        , 88663495)
    -> ,('3213'        , 88663501)
    -> ,('3216'        , 88663504)
    -> ,('3218'        , 88663506)
    -> ,('3220'        , 88663508)
    -> ,('3224'        , 88663512)
    -> ,('3227'        , 88663515)
    -> ,('3236'        , 88663524)
    -> ,('3238'        , 88663526)
    -> ,('3243'        , 88663531)
    -> ,('3245'        , 88663533)
    -> ,('3246'        , 88663534)
    -> ,('3247'        , 88663535)
    -> ,('3248'        , 88663536)
    -> ,('3249'        , 88663537)
    -> ,('3256'        , 88663544)
    -> ,('3257'        , 88663545)
    -> ,('3259'        , 88663547)
    -> ,('3263'        , 88663551)
    -> ,('3265'        , 88663553)
    -> ,('3267'        , 88663555)
    -> ,('3271'        , 88663559)
    -> ,('3275'        , 88663563)
    -> ,('3280'        , 88663568)
    -> ,('3284'        , 88664531)
    -> ,('3285'        , 88664532)
    -> ,('3286'        , 88664533)
    -> ,('3292'        , 88664849);
Query OK, 76 rows affected (0.00 sec)
Records: 76  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+--------------+
| col1     | rcol1    | col2     | col1         |
+----------+----------+----------+--------------+
| 38866001 | 38866001 | 88663425 | 38866001     |
| 38866001 | 38866001 |   162023 | 388660010001 |
| 38866001 | 38866001 | 88664116 | 388660010002 |
+----------+----------+----------+--------------+
3 rows in set (0.00 sec)

mysql> explain format=tree with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop antijoin
    -> Filter: (a.col1 like concat(t.col1,'%'))
        -> Inner hash join (no condition)
            -> Table scan on a  (cost=0.14 rows=76)
            -> Hash
                -> Nested loop inner join
                    -> Nested loop inner join
                        -> Filter: (a.col1 like concat(t.col1,'%'))
                            -> Inner hash join (a.col1 = t.col1)
                                -> Table scan on a  (cost=0.18 rows=76)
                                -> Hash
                                    -> Filter: (t.col1 is not null)
                                        -> Materialize table function
                        -> Materialize table function
                    -> Materialize table function
    -> Nested loop inner join
        -> Materialize table function
        -> Filter: ((a.col2 = a.col2) and (a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1) and (a.col1 like concat(t.col1,'%')))  (cost=46.55 rows=76)
            -> Table scan on a  (cost=46.55 rows=76)

1 row in set, 4 warnings (0.00 sec)

mysql>
mysql>
mysql> create index ind_test4_01 on test4(col1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ind_test4_02 on test4(col2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> with temp_itemcode as (
    ->    select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
    -> ), temp_data as (
    ->    select b.col1 as rcol1, a.col2, a.col1
    ->      from test4 a
    ->      join temp_itemcode b
    ->        on a.col1 like concat(b.col1,'%')
    -> ), temp_data2 as (
    ->    select d.col1
    ->      from temp_data c
    ->      join temp_itemcode d
    ->        on c.col1 = d.col1 and c.rcol1=d.col1
    -> )
    -> select e.*, f.*
    ->   from temp_data2 e
    ->   join temp_data f
    ->     on f.rcol1 = e.col1
    ->  where not exists (select null
    ->         from temp_data g
    ->         where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+--------------+
| col1     | rcol1    | col2     | col1         |
+----------+----------+----------+--------------+
| 38866001 | 38866001 | 88663425 | 38866001     |
| 38866001 | 38866001 |   162023 | 388660010001 |
| 38866001 | 38866001 | 88664116 | 388660010002 |
+----------+----------+----------+--------------+
3 rows in set (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)