Description:
Hello, please consider the following test case:
```
mysql> CREATE TABLE t0 ( c0 INT, PRIMARY KEY ( c0 ) ) ;
Query OK, 0 rows affected (0.14 sec)
mysql> CREATE VIEW v0 AS SELECT ra1.c0 ca1 FROM t0 ra0 INNER JOIN t0 ra1 ON ra1.c0 IN ( SELECT ra1.c0 ca0 FROM t0 ra2 ) ;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t0 VALUES ( 1 ), ( -1 ) ;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT COUNT(*) FROM t0 ra5 LEFT JOIN v0 ra6 ON ra6.ca1 = ra5.c0;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT /*+ JOIN_ORDER(ra5, ra2, ra1, ra0) SEMIJOIN(DUPSWEEDOUT) */ COUNT(*) FROM t0 ra5 LEFT JOIN v0 ra6 ON ra6.ca1 = ra5.c0;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM t0 ra5 LEFT JOIN v0 ra6 ON ra6.ca1 = ra5.c0;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+------------------------------+
| 1 | SIMPLE | ra5 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index |
| 1 | SIMPLE | ra1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | mytest.ra5.c0 | 1 | 100.00 | Using index |
| 1 | SIMPLE | ra2 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index; FirstMatch(ra1) |
| 1 | SIMPLE | ra0 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+------------------------------+
4 rows in set, 2 warnings (0.00 sec)
mysql> EXPLAIN SELECT /*+ JOIN_ORDER(ra5, ra2, ra1, ra0) SEMIJOIN(DUPSWEEDOUT) */ COUNT(*) FROM t0 ra5 LEFT JOIN v0 ra6 ON ra6.ca1 = ra5.c0;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+------------------------------+
| 1 | SIMPLE | ra5 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index |
| 1 | SIMPLE | ra2 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index; Start temporary |
| 1 | SIMPLE | ra1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | mytest.ra5.c0 | 1 | 100.00 | Using index; End temporary |
| 1 | SIMPLE | ra0 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+------------------------------+
4 rows in set, 2 warnings (0.01 sec)
```
By adjusting join order and semi-join type, I got different results for the same statement.
How to repeat:
Test case:
```
CREATE TABLE t0 ( c0 INT, PRIMARY KEY ( c0 ) ) ;
CREATE VIEW v0 AS SELECT ra1.c0 ca1 FROM t0 ra0 INNER JOIN t0 ra1 ON ra1.c0 IN ( SELECT ra1.c0 ca0 FROM t0 ra2 ) ;
INSERT INTO t0 VALUES ( 1 ), ( -1 ) ;
SELECT COUNT(*) FROM t0 ra5 LEFT JOIN v0 ra6 ON ra6.ca1 = ra5.c0; -- result: 4
SELECT /*+ JOIN_ORDER(ra5, ra2, ra1, ra0) SEMIJOIN(DUPSWEEDOUT) */ COUNT(*) FROM t0 ra5 LEFT JOIN v0 ra6 ON ra6.ca1 = ra5.c0; -- result: 8
```
