BugNumber=116334
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- Follow steps from the report

bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.39 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 `tbl` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `user_id` int DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> CREATE TABLE `fp_tbl` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `user_id` int DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> CREATE TABLE `fp_pol` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `lead_id` int unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.02 sec)

mysql>

- Insert rows into all three tables, please make sure to insert large number of rows in fp_pol table.

[umshastr@support-cluster03:~/work/binaries/utils]$ ./mysql_random_data_load --host=127.0.0.1 --user=root test tbl 10
INFO[2024-10-11T09:43:45+02:00] Starting
   0s [====================================================================] 100%
INFO[2024-10-11T09:43:45+02:00] 10 rows inserted
[umshastr@support-cluster03:~/work/binaries/utils]$
[umshastr@support-cluster03:~/work/binaries/utils]$ ./mysql_random_data_load --host=127.0.0.1 --user=root test fp_tbl 10
INFO[2024-10-11T09:44:05+02:00] Starting
   0s [====================================================================] 100%
INFO[2024-10-11T09:44:05+02:00] 10 rows inserted
[umshastr@support-cluster03:~/work/binaries/utils]$ ./mysql_random_data_load --host=127.0.0.1 --user=root test fp_pol 1000000
INFO[2024-10-11T09:44:22+02:00] Starting
   9s [====================================================================] 100%
INFO[2024-10-11T09:44:32+02:00] 1000000 rows inserted
[umshastr@support-cluster03:~/work/binaries/utils]$




- Delete rows with id 1,2 from fp_tbl, fp_pol tables.


mysql> delete from fp_tbl WHERE id in ('1','2');
Query OK, 2 rows affected (0.00 sec)

mysql> delete from fp_pol WHERE id in ('1','2');
Query OK, 2 rows affected (0.00 sec)


- Verify that row id 1,2 are present in tbl only.


mysql> select * from tbl where id in ('1','2');
+----+------------+
| id | user_id    |
+----+------------+
|  1 |  583532949 |
|  2 | 1342458479 |
+----+------------+
2 rows in set (0.00 sec)

mysql> select * from fp_tbl where id in ('1','2');
Empty set (0.00 sec)

mysql> select * from fp_pol where id in ('1','2');
Empty set (0.00 sec)


- Run the query and confirm it doesn't return any matching rows.

mysql> SELECT a.id, COUNT(*) AS n
    -> FROM tbl AS a
    -> LEFT JOIN fp_tbl AS f ON f.id = a.id
    -> LEFT JOIN fp_pol AS p ON p.lead_id   = f.id
    -> WHERE a.id IN (1,2) AND p.id IS NULL
    -> GROUP BY a.id;
Empty set (0.29 sec)

- Run the explain and confirm that query uses join buffer for fp_pol table as there is no index on lead_id column.

mysql> explain SELECT a.id, COUNT(*) AS n FROM tbl AS a LEFT JOIN fp_tbl AS f ON f.id = a.id LEFT JOIN fp_pol AS p ON p.lead_id   = f.id WHERE a.
    -> id IN (1,2) AND p.id IS NULL GROUP BY a.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows   | filtered | Extra                                                  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL      |      2 |   100.00 | Using where; Using index; Using temporary              |
|  1 | SIMPLE      | f     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.id |      1 |   100.00 | Using index                                            |
|  1 | SIMPLE      | p     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL      | 998566 |    10.00 | Using where; Not exists; Using join buffer (hash join) |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+--------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

-

mysql> set session join_buffer_size=64*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT a.id, COUNT(*) AS n
    -> FROM tbl AS a
    -> LEFT JOIN fp_tbl AS f ON f.id = a.id
    -> LEFT JOIN fp_pol AS p ON p.lead_id   = f.id
    -> WHERE a.id IN (1,2) AND p.id IS NULL
    -> GROUP BY a.id;
+----+---+
| id | n |
+----+---+
|  1 | 1 |
|  2 | 1 |
+----+---+
2 rows in set (0.25 sec)