-- release/opt
BugNumber=117105
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 &

## 8.0.39 / 8.0.40 - 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.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 bug_db_2;
Query OK, 1 row affected (0.01 sec)

mysql> USE bug_db_2;
Database changed
mysql> CREATE TABLE bug_table_0 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE bug_table_1 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_0_id INT,
    ->     FOREIGN KEY (bug_table_0_id) REFERENCES bug_table_0(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE bug_table_2 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_1_id_1 INT,
    ->     bug_table_1_id_2 INT,
    ->     FOREIGN KEY (bug_table_1_id_1) REFERENCES bug_table_1(id),
    ->     FOREIGN KEY (bug_table_1_id_2) REFERENCES bug_table_1(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE bug_table_3 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE bug_table_4 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_3_id INT,
    ->     bug_table_2_id INT,
    ->     value VARCHAR(255),
    ->     FOREIGN KEY (bug_table_2_id) REFERENCES bug_table_2(id),
    ->     FOREIGN KEY (bug_table_3_id) REFERENCES bug_table_3(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE bug_table_5 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_3_id INT,
    ->     bug_table_2_id INT,
    ->     value_id INT,
    ->     FOREIGN KEY (bug_table_2_id) REFERENCES bug_table_2(id),
    ->     FOREIGN KEY (bug_table_3_id) REFERENCES bug_table_3(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO bug_table_0 (id)
    -> VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO bug_table_1 (id, bug_table_0_id)
    -> VALUES (1, 1),
    ->     (2, 1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_2 (id, bug_table_1_id_1, bug_table_1_id_2)
    -> VALUES (1, 1, 2),
    ->     (2, 2, 1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_3 (id)
    -> VALUES (1),
    ->     (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_4 (id, bug_table_3_id, bug_table_2_id, value)
    -> VALUES (1, 1, 1, 'test'),
    ->     (2, 2, 1, 'test'),
    ->     (3, 1, 2, 'blue'),
    ->     (4, 2, 2, 'blue');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_5 (id, bug_table_3_id, bug_table_2_id, value_id)
    -> VALUES (1, 1, 1, 1),
    ->     (2, 2, 1, 2),
    ->     (3, 1, 2, 1),
    ->     (4, 2, 2, 2);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT count(res.id)
    -> FROM (
    ->         SELECT DISTINCT tt2.bug_table_1_id_2 as `id`
    ->         from bug_table_2 tt2
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt3
    ->                         WHERE tt3.bug_table_2_id = tt2.id
    ->                             AND tt3.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt3
    ->                                 WHERE tt3.bug_table_2_id = tt2.id
    ->                                     AND tt3.bug_table_3_id = 2
    ->                             )
    ->                             AND tt3.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->         LIMIT 3
    ->     ) as res;
+---------------+
| count(res.id) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT count(res.value)
    -> FROM (
    ->         SELECT DISTINCT tt4.value as `value`
    ->         from bug_table_4 tt4
    ->             JOIN bug_table_2 tt2 ON tt4.bug_table_2_id = tt2.id
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt4
    ->                         WHERE tt4.bug_table_2_id = tt2.id
    ->                             AND tt4.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt4
    ->                                 WHERE tt4.bug_table_2_id = tt2.id
    ->                                     AND tt4.bug_table_3_id = 2
    ->                             )
    ->                             AND tt4.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->             AND tt4.bug_table_3_id = 1
    ->         LIMIT 3
    ->     ) as res;
+------------------+
| count(res.value) |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql>

-- reconnect

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> USE bug_db_2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT count(res.id)
    -> FROM (
    ->         SELECT DISTINCT tt2.bug_table_1_id_2 as `id`
    ->         from bug_table_2 tt2
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt3
    ->                         WHERE tt3.bug_table_2_id = tt2.id
    ->                             AND tt3.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt3
    ->                                 WHERE tt3.bug_table_2_id = tt2.id
    ->                                     AND tt3.bug_table_3_id = 2
    ->                             )
    ->                             AND tt3.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->         LIMIT 3
    ->     ) as res;
+---------------+
| count(res.id) |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> SET SESSION optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(res.id)
    -> FROM (
    ->         SELECT DISTINCT tt2.bug_table_1_id_2 as `id`
    ->         from bug_table_2 tt2
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt3
    ->                         WHERE tt3.bug_table_2_id = tt2.id
    ->                             AND tt3.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt3
    ->                                 WHERE tt3.bug_table_2_id = tt2.id
    ->                                     AND tt3.bug_table_3_id = 2
    ->                             )
    ->                             AND tt3.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->         LIMIT 3
    ->     ) as res;
+---------------+
| count(res.id) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql>


-- Second query, regardless of semijoin ON/OFF

bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
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> use bug_db_2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT count(res.value)
    -> FROM (
    ->         SELECT DISTINCT tt4.value as `value`
    ->         from bug_table_4 tt4
    ->             JOIN bug_table_2 tt2 ON tt4.bug_table_2_id = tt2.id
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt4
    ->                         WHERE tt4.bug_table_2_id = tt2.id
    ->                             AND tt4.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt4
    ->                                 WHERE tt4.bug_table_2_id = tt2.id
    ->                                     AND tt4.bug_table_3_id = 2
    ->                             )
    ->                             AND tt4.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->             AND tt4.bug_table_3_id = 1
    ->         LIMIT 3
    ->     ) as res;
+------------------+
| count(res.value) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET SESSION optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(res.value)
    -> FROM (
    ->         SELECT DISTINCT tt4.value as `value`
    ->         from bug_table_4 tt4
    ->             JOIN bug_table_2 tt2 ON tt4.bug_table_2_id = tt2.id
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt4
    ->                         WHERE tt4.bug_table_2_id = tt2.id
    ->                             AND tt4.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt4
    ->                                 WHERE tt4.bug_table_2_id = tt2.id
    ->                                     AND tt4.bug_table_3_id = 2
    ->                             )
    ->                             AND tt4.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->             AND tt4.bug_table_3_id = 1
    ->         LIMIT 3
    ->     ) as res;
+------------------+
| count(res.value) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)


## 8.0.40

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 bug_db_2;
Query OK, 1 row affected (0.00 sec)

mysql> USE bug_db_2;
Database changed
mysql> CREATE TABLE bug_table_0 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE bug_table_1 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_0_id INT,
    ->     FOREIGN KEY (bug_table_0_id) REFERENCES bug_table_0(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE bug_table_2 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_1_id_1 INT,
    ->     bug_table_1_id_2 INT,
    ->     FOREIGN KEY (bug_table_1_id_1) REFERENCES bug_table_1(id),
    ->     FOREIGN KEY (bug_table_1_id_2) REFERENCES bug_table_1(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE bug_table_3 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE bug_table_4 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_3_id INT,
    ->     bug_table_2_id INT,
    ->     value VARCHAR(255),
    ->     FOREIGN KEY (bug_table_2_id) REFERENCES bug_table_2(id),
    ->     FOREIGN KEY (bug_table_3_id) REFERENCES bug_table_3(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE bug_table_5 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_3_id INT,
    ->     bug_table_2_id INT,
    ->     value_id INT,
    ->     FOREIGN KEY (bug_table_2_id) REFERENCES bug_table_2(id),
    ->     FOREIGN KEY (bug_table_3_id) REFERENCES bug_table_3(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO bug_table_0 (id)
    -> VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO bug_table_1 (id, bug_table_0_id)
    -> VALUES (1, 1),
    ->     (2, 1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_2 (id, bug_table_1_id_1, bug_table_1_id_2)
    -> VALUES (1, 1, 2),
    ->     (2, 2, 1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_3 (id)
    -> VALUES (1),
    ->     (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_4 (id, bug_table_3_id, bug_table_2_id, value)
    -> VALUES (1, 1, 1, 'test'),
    ->     (2, 2, 1, 'test'),
    ->     (3, 1, 2, 'blue'),
    ->     (4, 2, 2, 'blue');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_5 (id, bug_table_3_id, bug_table_2_id, value_id)
    -> VALUES (1, 1, 1, 1),
    ->     (2, 2, 1, 2),
    ->     (3, 1, 2, 1),
    ->     (4, 2, 2, 2);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT count(res.id)
    -> FROM (
    ->         SELECT DISTINCT tt2.bug_table_1_id_2 as `id`
    ->         from bug_table_2 tt2
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt3
    ->                         WHERE tt3.bug_table_2_id = tt2.id
    ->                             AND tt3.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt3
    ->                                 WHERE tt3.bug_table_2_id = tt2.id
    ->                                     AND tt3.bug_table_3_id = 2
    ->                             )
    ->                             AND tt3.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->         LIMIT 3
    ->     ) as res;
+---------------+
| count(res.id) |
+---------------+
|             2 |
+---------------+
1 row in set (0.01 sec)

mysql> \r
Connection id:    8
Current database: bug_db_2

mysql> SELECT count(res.id)
    -> FROM (
    ->         SELECT DISTINCT tt2.bug_table_1_id_2 as `id`
    ->         from bug_table_2 tt2
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt3
    ->                         WHERE tt3.bug_table_2_id = tt2.id
    ->                             AND tt3.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt3
    ->                                 WHERE tt3.bug_table_2_id = tt2.id
    ->                                     AND tt3.bug_table_3_id = 2
    ->                             )
    ->                             AND tt3.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->         LIMIT 3
    ->     ) as res;
+---------------+
| count(res.id) |
+---------------+
|             0 |
+---------------+
1 row in set (0.01 sec)

mysql> SET SESSION optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(res.id)
    -> FROM (
    ->         SELECT DISTINCT tt2.bug_table_1_id_2 as `id`
    ->         from bug_table_2 tt2
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt3
    ->                         WHERE tt3.bug_table_2_id = tt2.id
    ->                             AND tt3.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt3
    ->                                 WHERE tt3.bug_table_2_id = tt2.id
    ->                                     AND tt3.bug_table_3_id = 2
    ->                             )
    ->                             AND tt3.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->         LIMIT 3
    ->     ) as res;
+---------------+
| count(res.id) |
+---------------+
|             2 |
+---------------+
1 row in set (0.01 sec)

mysql> \r
Connection id:    9
Current database: bug_db_2

mysql> SELECT count(res.value)
    -> FROM (
    ->         SELECT DISTINCT tt4.value as `value`
    ->         from bug_table_4 tt4
    ->             JOIN bug_table_2 tt2 ON tt4.bug_table_2_id = tt2.id
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt4
    ->                         WHERE tt4.bug_table_2_id = tt2.id
    ->                             AND tt4.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt4
    ->                                 WHERE tt4.bug_table_2_id = tt2.id
    ->                                     AND tt4.bug_table_3_id = 2
    ->                             )
    ->                             AND tt4.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->             AND tt4.bug_table_3_id = 1
    ->         LIMIT 3
    ->     ) as res;
+------------------+
| count(res.value) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET SESSION optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(res.id)
    -> FROM (
    ->         SELECT DISTINCT tt2.bug_table_1_id_2 as `id`
    ->         from bug_table_2 tt2
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt3
    ->                         WHERE tt3.bug_table_2_id = tt2.id
    ->                             AND tt3.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt3
    ->                                 WHERE tt3.bug_table_2_id = tt2.id
    ->                                     AND tt3.bug_table_3_id = 2
    ->                             )
    ->                             AND tt3.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->         LIMIT 3
    ->     ) as res;
+---------------+
| count(res.id) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql>

## 8.4.3 - looks fine

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.4.3 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 bug_db_2;
Query OK, 1 row affected (0.00 sec)

mysql> USE bug_db_2;
Database changed
mysql> CREATE TABLE bug_table_0 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE bug_table_1 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_0_id INT,
    ->     FOREIGN KEY (bug_table_0_id) REFERENCES bug_table_0(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE bug_table_2 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_1_id_1 INT,
    ->     bug_table_1_id_2 INT,
    ->     FOREIGN KEY (bug_table_1_id_1) REFERENCES bug_table_1(id),
    ->     FOREIGN KEY (bug_table_1_id_2) REFERENCES bug_table_1(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE bug_table_3 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE bug_table_4 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_3_id INT,
    ->     bug_table_2_id INT,
    ->     value VARCHAR(255),
    ->     FOREIGN KEY (bug_table_2_id) REFERENCES bug_table_2(id),
    ->     FOREIGN KEY (bug_table_3_id) REFERENCES bug_table_3(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE bug_table_5 (
    ->     id INT PRIMARY KEY,
    ->     bug_table_3_id INT,
    ->     bug_table_2_id INT,
    ->     value_id INT,
    ->     FOREIGN KEY (bug_table_2_id) REFERENCES bug_table_2(id),
    ->     FOREIGN KEY (bug_table_3_id) REFERENCES bug_table_3(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO bug_table_0 (id)
    -> VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO bug_table_1 (id, bug_table_0_id)
    -> VALUES (1, 1),
    ->     (2, 1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_2 (id, bug_table_1_id_1, bug_table_1_id_2)
    -> VALUES (1, 1, 2),
    ->     (2, 2, 1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_3 (id)
    -> VALUES (1),
    ->     (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_4 (id, bug_table_3_id, bug_table_2_id, value)
    -> VALUES (1, 1, 1, 'test'),
    ->     (2, 2, 1, 'test'),
    ->     (3, 1, 2, 'blue'),
    ->     (4, 2, 2, 'blue');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bug_table_5 (id, bug_table_3_id, bug_table_2_id, value_id)
    -> VALUES (1, 1, 1, 1),
    ->     (2, 2, 1, 2),
    ->     (3, 1, 2, 1),
    ->     (4, 2, 2, 2);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT count(res.id)
    -> FROM (
    ->         SELECT DISTINCT tt2.bug_table_1_id_2 as `id`
    ->         from bug_table_2 tt2
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt3
    ->                         WHERE tt3.bug_table_2_id = tt2.id
    ->                             AND tt3.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt3
    ->                                 WHERE tt3.bug_table_2_id = tt2.id
    ->                                     AND tt3.bug_table_3_id = 2
    ->                             )
    ->                             AND tt3.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->         LIMIT 3
    ->     ) as res;
+---------------+
| count(res.id) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql> \r
Connection id:    8
Current database: bug_db_2

mysql> SELECT count(res.id)
    -> FROM (
    ->         SELECT DISTINCT tt2.bug_table_1_id_2 as `id`
    ->         from bug_table_2 tt2
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt3
    ->                         WHERE tt3.bug_table_2_id = tt2.id
    ->                             AND tt3.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt3
    ->                                 WHERE tt3.bug_table_2_id = tt2.id
    ->                                     AND tt3.bug_table_3_id = 2
    ->                             )
    ->                             AND tt3.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->         LIMIT 3
    ->     ) as res;
+---------------+
| count(res.id) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT count(res.value)
    -> FROM (
    ->         SELECT DISTINCT tt4.value as `value`
    ->         from bug_table_4 tt4
    ->             JOIN bug_table_2 tt2 ON tt4.bug_table_2_id = tt2.id
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt4
    ->                         WHERE tt4.bug_table_2_id = tt2.id
    ->                             AND tt4.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt4
    ->                                 WHERE tt4.bug_table_2_id = tt2.id
    ->                                     AND tt4.bug_table_3_id = 2
    ->                             )
    ->                             AND tt4.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->             AND tt4.bug_table_3_id = 1
    ->         LIMIT 3
    ->     ) as res;
+------------------+
| count(res.value) |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql> \r
Connection id:    9
Current database: bug_db_2

mysql> SELECT count(res.value)
    -> FROM (
    ->         SELECT DISTINCT tt4.value as `value`
    ->         from bug_table_4 tt4
    ->             JOIN bug_table_2 tt2 ON tt4.bug_table_2_id = tt2.id
    ->         WHERE (
    ->                 SELECT (
    ->                         SELECT 1
    ->                         from bug_table_4 tt4
    ->                         WHERE tt4.bug_table_2_id = tt2.id
    ->                             AND tt4.value IN (
    ->                                 SELECT value
    ->                                 from bug_table_4 tt4
    ->                                 WHERE tt4.bug_table_2_id = tt2.id
    ->                                     AND tt4.bug_table_3_id = 2
    ->                             )
    ->                             AND tt4.bug_table_3_id = 1
    ->                     ) IS NOT NULL
    ->             )
    ->             AND tt2.bug_table_1_id_1 IN (
    ->                 SELECT tt1.id
    ->                 FROM bug_table_1 tt1
    ->                 WHERE tt1.bug_table_0_id IN (1)
    ->             )
    ->             AND tt4.bug_table_3_id = 1
    ->         LIMIT 3
    ->     ) as res;
+------------------+
| count(res.value) |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql>