Description:
The following uses markdown format
Oracle 5.7.35
```
root@localhost 07:09:23 [ripplesdb]> select version();
+------------+
| version() |
+------------+
| 5.7.35 |
+------------+
5.7.30 5.7.32 also has this problem
```
run this query, return 6 rows
```sql
SELECT DISTINCT
sr.*
FROM
t1 sr
INNER JOIN
t2 srr ON sr.id = srr.resource_id
INNER JOIN
t3 sprr ON sprr.role_id = srr.role_id
INNER JOIN
t4 smrr ON srr.resource_id = smrr.resource_id
AND sprr.post_id = 'fb1fcca2504b434094b5a31c7c17177d'
AND smrr.menu_id = 'c8ceb04e157342179cb56f4e6149afff'
AND smrr.deleted = 0
WHERE
sr.deleted = 0 AND sr.enabled = 1
AND sr.resource_type = 1
+----------------------------------+---------------+---------+---------+
| id | resource_type | deleted | enabled |
+----------------------------------+---------------+---------+---------+
| 439f9b8a8e4b410eb3a5ed44168329de | 1 | 0 | 1 |
| 7120fdf3ea3346beb85db8df9b7828e1 | 1 | 0 | 1 |
| 7462ed613ce945a5acacb0e3fdfbfc36 | 1 | 0 | 1 |
| 8fe9948c7f454bcb8b1a037f40cf609f | 1 | 0 | 1 |
| d3568cd6d4e343088f2fc920dfcb06c1 | 1 | 0 | 1 |
| f7ae2874177b44eb88a399b6df194b70 | 1 | 0 | 1 |
+----------------------------------+---------------+---------+---------+
6 rows in set (0.01 sec)
```
query explain
```sql
+----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+------+----------+--------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+------+----------+--------------------------------------------------------------+
| 1 | SIMPLE | smrr | NULL | ref | Index_resource_id,Index_menu_id_resource_id | Index_menu_id_resource_id | 131 | const | 25 | 10.00 | Using index condition; Using where; Using temporary |
| 1 | SIMPLE | sr | NULL | eq_ref | PRIMARY | PRIMARY | 130 | ripplesdb.smrr.resource_id | 1 | 5.00 | Using where |
| 1 | SIMPLE | srr | NULL | ref | IDX_RES_ID | IDX_RES_ID | 131 | ripplesdb.smrr.resource_id | 93 | 100.00 | Distinct |
| 1 | SIMPLE | sprr | NULL | ALL | NULL | NULL | NULL | NULL | 1136 | 1.00 | Using where; Distinct; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+------+----------+--------------------------------------------------------------+
```
删除t2表 resource_id列索引
drop index on t2.resource_id
```sql
drop index IDX_RES_ID on t2
```
run this query again, return 7 rows
```sql
SELECT DISTINCT
sr.*
FROM
t1 sr
INNER JOIN
t2 srr ON sr.id = srr.resource_id
INNER JOIN
t3 sprr ON sprr.role_id = srr.role_id
INNER JOIN
t4 smrr ON srr.resource_id = smrr.resource_id
AND sprr.post_id = 'fb1fcca2504b434094b5a31c7c17177d'
AND smrr.menu_id = 'c8ceb04e157342179cb56f4e6149afff'
AND smrr.deleted = 0
WHERE
sr.deleted = 0 AND sr.enabled = 1
AND sr.resource_type = 1
+----------------------------------+---------------+---------+---------+
| id | resource_type | deleted | enabled |
+----------------------------------+---------------+---------+---------+
| 7462ed613ce945a5acacb0e3fdfbfc36 | 1 | 0 | 1 |
| f7ae2874177b44eb88a399b6df194b70 | 1 | 0 | 1 |
| 439f9b8a8e4b410eb3a5ed44168329de | 1 | 0 | 1 |
| 7120fdf3ea3346beb85db8df9b7828e1 | 1 | 0 | 1 |
| 8fe9948c7f454bcb8b1a037f40cf609f | 1 | 0 | 1 |
| a2160712d9e14085b89ad81c2ad01e65 | 1 | 0 | 1 |
| d3568cd6d4e343088f2fc920dfcb06c1 | 1 | 0 | 1 |
+----------------------------------+---------------+---------+---------+
7 rows in set (0.59 sec)
```
query explain
```sql
+----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+--------+----------+--------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+--------+----------+--------------------------------------------------------------+
| 1 | SIMPLE | smrr | NULL | ref | Index_resource_id,Index_menu_id_resource_id | Index_menu_id_resource_id | 131 | const | 25 | 10.00 | Using index condition; Using where; Using temporary |
| 1 | SIMPLE | sr | NULL | eq_ref | PRIMARY | PRIMARY | 130 | ripplesdb.smrr.resource_id | 1 | 5.00 | Using where |
| 1 | SIMPLE | sprr | NULL | ALL | NULL | NULL | NULL | NULL | 1136 | 10.00 | Using where; Distinct; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | srr | NULL | ALL | NULL | NULL | NULL | NULL | 469011 | 1.00 | Using where; Distinct; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+--------+----------+--------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
```
difference `a2160712d9e14085b89ad81c2ad01e65`
create/drop index
```sql
ALTER TABLE t2 ADD INDEX `IDX_RES_ID`(resource_id);
drop index IDX_RES_ID on t2;
```
the sql of create these tables and data are in the zip file
How to repeat:
Import the dump file I provided, execute sql
Description: The following uses markdown format Oracle 5.7.35 ``` root@localhost 07:09:23 [ripplesdb]> select version(); +------------+ | version() | +------------+ | 5.7.35 | +------------+ 5.7.30 5.7.32 also has this problem ``` run this query, return 6 rows ```sql SELECT DISTINCT sr.* FROM t1 sr INNER JOIN t2 srr ON sr.id = srr.resource_id INNER JOIN t3 sprr ON sprr.role_id = srr.role_id INNER JOIN t4 smrr ON srr.resource_id = smrr.resource_id AND sprr.post_id = 'fb1fcca2504b434094b5a31c7c17177d' AND smrr.menu_id = 'c8ceb04e157342179cb56f4e6149afff' AND smrr.deleted = 0 WHERE sr.deleted = 0 AND sr.enabled = 1 AND sr.resource_type = 1 +----------------------------------+---------------+---------+---------+ | id | resource_type | deleted | enabled | +----------------------------------+---------------+---------+---------+ | 439f9b8a8e4b410eb3a5ed44168329de | 1 | 0 | 1 | | 7120fdf3ea3346beb85db8df9b7828e1 | 1 | 0 | 1 | | 7462ed613ce945a5acacb0e3fdfbfc36 | 1 | 0 | 1 | | 8fe9948c7f454bcb8b1a037f40cf609f | 1 | 0 | 1 | | d3568cd6d4e343088f2fc920dfcb06c1 | 1 | 0 | 1 | | f7ae2874177b44eb88a399b6df194b70 | 1 | 0 | 1 | +----------------------------------+---------------+---------+---------+ 6 rows in set (0.01 sec) ``` query explain ```sql +----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+------+----------+--------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+------+----------+--------------------------------------------------------------+ | 1 | SIMPLE | smrr | NULL | ref | Index_resource_id,Index_menu_id_resource_id | Index_menu_id_resource_id | 131 | const | 25 | 10.00 | Using index condition; Using where; Using temporary | | 1 | SIMPLE | sr | NULL | eq_ref | PRIMARY | PRIMARY | 130 | ripplesdb.smrr.resource_id | 1 | 5.00 | Using where | | 1 | SIMPLE | srr | NULL | ref | IDX_RES_ID | IDX_RES_ID | 131 | ripplesdb.smrr.resource_id | 93 | 100.00 | Distinct | | 1 | SIMPLE | sprr | NULL | ALL | NULL | NULL | NULL | NULL | 1136 | 1.00 | Using where; Distinct; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+------+----------+--------------------------------------------------------------+ ``` 删除t2表 resource_id列索引 drop index on t2.resource_id ```sql drop index IDX_RES_ID on t2 ``` run this query again, return 7 rows ```sql SELECT DISTINCT sr.* FROM t1 sr INNER JOIN t2 srr ON sr.id = srr.resource_id INNER JOIN t3 sprr ON sprr.role_id = srr.role_id INNER JOIN t4 smrr ON srr.resource_id = smrr.resource_id AND sprr.post_id = 'fb1fcca2504b434094b5a31c7c17177d' AND smrr.menu_id = 'c8ceb04e157342179cb56f4e6149afff' AND smrr.deleted = 0 WHERE sr.deleted = 0 AND sr.enabled = 1 AND sr.resource_type = 1 +----------------------------------+---------------+---------+---------+ | id | resource_type | deleted | enabled | +----------------------------------+---------------+---------+---------+ | 7462ed613ce945a5acacb0e3fdfbfc36 | 1 | 0 | 1 | | f7ae2874177b44eb88a399b6df194b70 | 1 | 0 | 1 | | 439f9b8a8e4b410eb3a5ed44168329de | 1 | 0 | 1 | | 7120fdf3ea3346beb85db8df9b7828e1 | 1 | 0 | 1 | | 8fe9948c7f454bcb8b1a037f40cf609f | 1 | 0 | 1 | | a2160712d9e14085b89ad81c2ad01e65 | 1 | 0 | 1 | | d3568cd6d4e343088f2fc920dfcb06c1 | 1 | 0 | 1 | +----------------------------------+---------------+---------+---------+ 7 rows in set (0.59 sec) ``` query explain ```sql +----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+--------+----------+--------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+--------+----------+--------------------------------------------------------------+ | 1 | SIMPLE | smrr | NULL | ref | Index_resource_id,Index_menu_id_resource_id | Index_menu_id_resource_id | 131 | const | 25 | 10.00 | Using index condition; Using where; Using temporary | | 1 | SIMPLE | sr | NULL | eq_ref | PRIMARY | PRIMARY | 130 | ripplesdb.smrr.resource_id | 1 | 5.00 | Using where | | 1 | SIMPLE | sprr | NULL | ALL | NULL | NULL | NULL | NULL | 1136 | 10.00 | Using where; Distinct; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | srr | NULL | ALL | NULL | NULL | NULL | NULL | 469011 | 1.00 | Using where; Distinct; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+--------+---------------------------------------------+---------------------------+---------+----------------------------+--------+----------+--------------------------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) ``` difference `a2160712d9e14085b89ad81c2ad01e65` create/drop index ```sql ALTER TABLE t2 ADD INDEX `IDX_RES_ID`(resource_id); drop index IDX_RES_ID on t2; ``` the sql of create these tables and data are in the zip file How to repeat: Import the dump file I provided, execute sql