Description:
Database might get a wrong result when using ICP of a 'like' condition.
At first, we find 'select /*+JOIN_ORDER(M,T)*/count(*) from M,T where ... ' has different result with 'select /*+JOIN_ORDER(T,M)*/ count(*) from M,T where ...'.
After use sql below:
"select /*+JOIN_ORDER(M,T)*/* from M,T where ...
except
select /*+JOIN_ORDER(T,M)*/ count(*) from M,T where ... "
We get the difference.
The reason is, when using a different index, for example, an index start with the column in like condition, the like condition will be pushed to engine. For some reason, the IndexRangeScanIterator can't return the corrent value.
How to repeat:
mysql> CREATE TABLE tt2 (
mp_id varchar(64) COLLATE utf8mb4_bin NOT NULL,
emp_name varchar(160) COLLATE utf8mb4_bin DEFAULT NULL,
is_delete varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (emp_id),
KEY idx_mdm_employee_name (emp_name)
);
Query OK, 0 rows affected (0.18 sec)
mysql> insert into tt2 values('Y0071839','张
'> 凯','0');
Query OK, 1 row affected (0.06 sec)
--use primary
mysql> select emp_id, emp_name from tt2 T where t.emp_id = 'Y0071839';
+----------+----------+
| emp_id | emp_name |
+----------+----------+
| Y0071839 | 张
凯 |
+----------+----------+
1 row in set (0.06 sec)
--use primary
mysql> select emp_id, emp_name from tt2 T where t.emp_id = 'Y0071839' and t.emp_name like concat('张','%');
+----------+----------+
| emp_id | emp_name |
+----------+----------+
| Y0071839 | 张
凯 |
+----------+----------+
1 row in set (0.06 sec)
--use sec index ICP
mysql> select emp_id, emp_name from tt2 T where t.emp_name like concat('张','%') and T.is_delete='0';
Empty set (0.12 sec)
mysql> select version();
+-------------+
| version() |
+-------------+
| 8.3.0-debug |
+-------------+
1 row in set (0.01 sec)
Suggested fix:
The problem might be relevent to the varchar column with line breaks.
Description: Database might get a wrong result when using ICP of a 'like' condition. At first, we find 'select /*+JOIN_ORDER(M,T)*/count(*) from M,T where ... ' has different result with 'select /*+JOIN_ORDER(T,M)*/ count(*) from M,T where ...'. After use sql below: "select /*+JOIN_ORDER(M,T)*/* from M,T where ... except select /*+JOIN_ORDER(T,M)*/ count(*) from M,T where ... " We get the difference. The reason is, when using a different index, for example, an index start with the column in like condition, the like condition will be pushed to engine. For some reason, the IndexRangeScanIterator can't return the corrent value. How to repeat: mysql> CREATE TABLE tt2 ( mp_id varchar(64) COLLATE utf8mb4_bin NOT NULL, emp_name varchar(160) COLLATE utf8mb4_bin DEFAULT NULL, is_delete varchar(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (emp_id), KEY idx_mdm_employee_name (emp_name) ); Query OK, 0 rows affected (0.18 sec) mysql> insert into tt2 values('Y0071839','张 '> 凯','0'); Query OK, 1 row affected (0.06 sec) --use primary mysql> select emp_id, emp_name from tt2 T where t.emp_id = 'Y0071839'; +----------+----------+ | emp_id | emp_name | +----------+----------+ | Y0071839 | 张 凯 | +----------+----------+ 1 row in set (0.06 sec) --use primary mysql> select emp_id, emp_name from tt2 T where t.emp_id = 'Y0071839' and t.emp_name like concat('张','%'); +----------+----------+ | emp_id | emp_name | +----------+----------+ | Y0071839 | 张 凯 | +----------+----------+ 1 row in set (0.06 sec) --use sec index ICP mysql> select emp_id, emp_name from tt2 T where t.emp_name like concat('张','%') and T.is_delete='0'; Empty set (0.12 sec) mysql> select version(); +-------------+ | version() | +-------------+ | 8.3.0-debug | +-------------+ 1 row in set (0.01 sec) Suggested fix: The problem might be relevent to the varchar column with line breaks.