MySQL Bugs: #113529: Unexpectedly exceeding memory capacity after adding a unique index

Bug #113529	Unexpectedly exceeding memory capacity after adding a unique index
Submitted:	30 Dec 2023 16:04	Modified:	2 Jan 8:56
Reporter:	John Jove	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.2.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
I run the following two cases, in which the case 2 is constructed from the case 1 by adding a unique index on column c0 in table t1.
In case 1, the query returns nothing.
In case 2, the same query returns an error with message "ERROR 34 (HY000): Memory capacity exceeded (capacity 8388608 bytes)".
I expect the same query in the two cases can return the same results.

How to repeat:
-- case 1
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
INSERT INTO t0(c0) VALUES(0);
SELECT ALL t0.c0 FROM t0, t1 WHERE t1.c0 <= t0.c0; -- Empty set (0.001 sec)

-- case 2
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT UNIQUE);
INSERT INTO t0(c0) VALUES(0);
SELECT ALL t0.c0 FROM t0, t1 WHERE t1.c0 <= t0.c0; -- ERROR 34 (HY000): Memory capacity exceeded (capacity 8388608 bytes)

Hello John Jove,

Thank you for the report and feedback.
I quickly tried your test case on a 8.2.0 instance(with default settings) but not seeing any issues. Is there anything I'm missing here(share config details if not running with default)? Please let us know.

-- start up

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

 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.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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> -- case 1
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1(c0 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t0(c0) VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT ALL t0.c0 FROM t0, t1 WHERE t1.c0 <= t0.c0; -- Empty set (0.001 sec)
Empty set (0.00 sec)

mysql> drop table if exists t0;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> -- case 2
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1(c0 INT UNIQUE);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t0(c0) VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT ALL t0.c0 FROM t0, t1 WHERE t1.c0 <= t0.c0; -- ERROR 34 (HY000): Memory capacity exceeded (capacity 8388608 bytes)
Empty set (0.00 sec)

mysql> system cat docs/INFO_SRC
commit: 9427907870ffee26c739565e059c8184126707c6
date: 2023-10-12 13:28:37 +0200
build-date: 2023-10-12 11:42:14 +0000
short: 9427907870f
branch: mysql-8.2.0-release

MySQL source 8.2.0

regards,
Umesh