-- Environment

[umshastr@bug117867:~/work/binaries/utils]$ cat /etc/*release
Oracle Linux Server release 8.10
NAME="Oracle Linux Server"
VERSION="8.10"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="8.10"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Oracle Linux Server 8.10"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:8:10:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
ORACLE_BUGZILLA_PRODUCT_VERSION=8.10
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=8.10
Red Hat Enterprise Linux release 8.10 (Ootpa)
Oracle Linux Server release 8.10
[umshastr@bug117867:~/work/binaries/utils]$ ./pt-summary
# System Summary Report ######################################
        Date | 2025-04-09 07:12:55 UTC (local TZ: CEST +0200)
    Hostname | bug117867
      Uptime |  1:07,  5 users,  load average: 0.00, 0.00, 0.00
    Platform | Linux
     Release | Red Hat Enterprise Linux release 8.10 (Ootpa)
      Kernel | 5.15.0-306.177.4.1.el8uek.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
   Threading | NPTL 2.28
    Compiler | GNU CC version 8.5.0 20210514 (Red Hat 8.5.0-26.0.1).
     SELinux | Disabled
 Virtualized | No virtualization detected
# Processor ##################################################
  Processors | physical = 1, cores = 8, virtual = 16, hyperthreading = yes
      Speeds | 16x2245.780
      Models | 16xAMD EPYC 7742 64-Core Processor
      Caches | 16x512 KB
# Memory #####################################################
       Total | 125.3G
        Free | 114.9G
        Used | physical = 2.5G, swap allocated = 4.0G, swap used = 0.0, virtual = 2.5G
      Shared | 16.7M
     Buffers | 7.9G
      Caches | 121.6G
       Dirty | 536 kB
     UsedRSS | 2.8G
  Swappiness | 60
 DirtyPolicy | 20, 10
 DirtyStatus | 0, 0

-
[umshastr@bug117867:~/work/binaries/utils]$ df -Th /bv/myVolume
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/sdb1      xfs   500G  3.6G  497G   1% /bv/myVolume

-- 

-- Builds - binary tar ball used

mysql-8.0.41-linux-glibc2.28-x86_64.tar
mysql-8.4.4-linux-glibc2.28-x86_64.tar
mysql-9.2.0-linux-glibc2.28-x86_64.tar

CREATE DATABASE IF NOT EXISTS test;
use test;

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
	id INT NOT NULL AUTO_INCREMENT,
	a_id INT NOT NULL,
	p_id INT NOT NULL,
	l_id INT NULL DEFAULT NULL,
	prev_self_id INT NULL DEFAULT NULL,
	next_self_id INT NULL DEFAULT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY k1 (a_id,id),
	UNIQUE KEY k2 (a_id,p_id,id),
	UNIQUE KEY k3 (prev_self_id),
	UNIQUE KEY k4 (next_self_id),
	UNIQUE KEY k5 (a_id,p_id,l_id,id)
);


## 8.0.x 


-- release/opt

BugNumber=117907_8.0.41
rm -rf /bv/myVolume/$BugNumber
rm -rf /bv/myVolume/$BugNumber/tmp

bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=/bv/myVolume/$BugNumber --log-error-verbosity=3
mkdir -p /bv/myVolume/$BugNumber/tmp
bin/mysqld --no-defaults --basedir=$PWD --datadir=/bv/myVolume/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=/bv/myVolume/$BugNumber/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 --tmpdir=/bv/myVolume/$BugNumber/tmp 2>&1 &


bin/mysql -uroot -S/tmp/mysql.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.41 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 IF NOT EXISTS test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS tbl;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS tbl;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE tbl (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> a_id INT NOT NULL,
    -> p_id INT NOT NULL,
    -> l_id INT NULL DEFAULT NULL,
    -> prev_self_id INT NULL DEFAULT NULL,
    -> next_self_id INT NULL DEFAULT NULL,
    -> PRIMARY KEY (id),
    -> UNIQUE KEY k1 (a_id,id),
    -> UNIQUE KEY k2 (a_id,p_id,id),
    -> UNIQUE KEY k3 (prev_self_id),
    -> UNIQUE KEY k4 (next_self_id),
    -> UNIQUE KEY k5 (a_id,p_id,l_id,id)
    -> );
Query OK, 0 rows affected (0.03 sec)


-- provision data

[umshastr@bug117867:~/work/binaries/utils]$ ./mysql_random_data_load test tbl 1000000 --user=root
INFO[2025-04-09T09:38:51+02:00] Starting
  58s [====================================================================] 100%
INFO[2025-04-09T09:39:50+02:00] 1000000 rows inserted
[umshastr@bug117867:~/work/binaries/utils]$

or use something like


SET @@cte_max_recursion_depth  = 1000001;
insert into tbl
WITH RECURSIVE nrows(id) AS (
SELECT 1 as id UNION ALL
SELECT id + 1 from grows WHERE  id<=1000000
)
SELECT id, id+1, id+2, id+3, id+4, id+5 FROM nrows;

-- Use queries provided in the report



mysql> select count(*) from tbl;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.16 sec)

mysql> -- Query #1. Has a single "IN" condition.
mysql> EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> -- Query #2. Same as query #1 but adds "AND a_id = 1" condition. Notice that the value of "a_id" checked by this new condition is the same as in old "IN" condition.
mysql> EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL)) AND a_id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> --  Query #3. Same as query #1 but adds another value "(1, 111, 22222, 55)" in which "tbl.id" is not NULL.
mysql> EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL), (1, 111, 22222, 55));
+----+-------------+-------+------------+-------+------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys    | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+------------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl   | NULL       | range | PRIMARY,k1,k2,k5 | PRIMARY | 4       | NULL |    1 |     5.00 | Using where |
+----+-------------+-------+------------+-------+------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> --  Query #4. Same as query #2 but adds another value "(1, 111, 22222, 55)" in which "tbl.id" is not NULL.
mysql> EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL), (1, 111, 22222, 55)) AND a_id = 1;
+----+-------------+-------+------------+-------+------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys    | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+------------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl   | NULL       | range | PRIMARY,k1,k2,k5 | PRIMARY | 4       | NULL |    1 |     5.00 | Using where |
+----+-------------+-------+------------+-------+------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql>



-- Query #1. Has a single "IN" condition.
EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL));

-- Query #2. Same as query #1 but adds "AND a_id = 1" condition. Notice that the value of "a_id" checked by this new condition is the same as in old "IN" condition.
EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL)) AND a_id = 1;

-- Query #3. Same as query #1 but adds another value "(1, 111, 22222, 55)" in which "tbl.id" is not NULL.
EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL), (1, 111, 22222, 55));

-- Query #4. Same as query #2 but adds another value "(1, 111, 22222, 55)" in which "tbl.id" is not NULL.
EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL), (1, 111, 22222, 55)) AND a_id = 1;




## 8.4.4 / 9.2.0


-- release/opt

BugNumber=117907_8.4.44
rm -rf /bv/myVolume/$BugNumber
rm -rf /bv/myVolume/$BugNumber/tmp

bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=/bv/myVolume/$BugNumber --log-error-verbosity=3
mkdir -p /bv/myVolume/$BugNumber/tmp
bin/mysqld --no-defaults --basedir=$PWD --datadir=/bv/myVolume/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=/bv/myVolume/$BugNumber/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 --tmpdir=/bv/myVolume/$BugNumber/tmp 2>&1 &



 bin/mysql -uroot -S/tmp/mysql.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.4.4 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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>
mysql> DROP TABLE IF EXISTS tbl;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE tbl (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> a_id INT NOT NULL,
    -> p_id INT NOT NULL,
    -> l_id INT NULL DEFAULT NULL,
    -> prev_self_id INT NULL DEFAULT NULL,
    -> next_self_id INT NULL DEFAULT NULL,
    -> PRIMARY KEY (id),
    -> UNIQUE KEY k1 (a_id,id),
    -> UNIQUE KEY k2 (a_id,p_id,id),
    -> UNIQUE KEY k3 (prev_self_id),
    -> UNIQUE KEY k4 (next_self_id),
    -> UNIQUE KEY k5 (a_id,p_id,l_id,id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> -- Query #1. Has a single "IN" condition.
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Query #2. Same as query #1 but adds "AND a_id = 1" condition. Notice that the value of "a_id" checked by this new condition is the same as in old "IN" condition.
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL)) AND a_id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tbl   | NULL       | ref  | k1,k2,k5      | k1   | 4       | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Query #3. Same as query #1 but adds another value "(1, 111, 22222, 55)" in which "tbl.id" is not NULL.
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL), (1, 111, 22222, 55));
+----+-------------+-------+------------+-------+------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys    | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+------------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl   | NULL       | range | PRIMARY,k1,k2,k5 | PRIMARY | 4       | NULL |    1 |     5.00 | Using where |
+----+-------------+-------+------------+-------+------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Query #4. Same as query #2 but adds another value "(1, 111, 22222, 55)" in which "tbl.id" is not NULL.
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM tbl WHERE (a_id, p_id, l_id, id) IN ((1, 111, NULL, NULL), (1, 111, 22222, NULL), (1, 111, 22222, 55)) AND a_id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tbl   | NULL       | ref  | k1,k2,k5      | k1   | 4       | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)