title: MySQL 8.0.23 tmp table keeps filling up
body: <p>I have not had this issue with older versions of MySQL including up to 8.0.21 which I run in production within AWS RDS.  I have a query that gets run only once a year.  Here is the relevant code:</p>
  <pre><code>create table medicare_fee_history (
          year int unsigned,
          mac int unsigned,
          locality int unsigned,
          hcpcs varchar(10),
          modifier varchar(10),
          index (mac, locality, hcpcs, modifier),
          non_facility decimal(17, 4),
          facility decimal(17, 4)
  ) engine = myisam;
  
  load data local infile 'PFALL.csv'
  into table medicare_fee_history
  fields terminated by ',' enclosed by '&quot;'
  (year, mac, locality, hcpcs, modifier, non_facility, facility);
  
  create table medicare_fee_first (
          year int unsigned,
          hcpcs varchar(10),
          modifier varchar(10),
          index (hcpcs, modifier),
          facility decimal(17, 4),
          non_facility decimal(17, 4)
  ) engine = myisam;
  
  insert into medicare_fee_first (year, hcpcs, modifier, facility, non_facility)
  (
          select min(year), hcpcs, modifier, avg(facility), avg(non_facility)
          from medicare_fee_history group by hcpcs, modifier
  );
  </code></pre>
  <p>During the insert select I get the following error:</p>
  <p>ERROR 1114 (HY000): The table '/tmp/#sql4984_9_3' is full</p>
  <p>Table medicare_fee_history has 16042724 rows.  To reproduce this, the dataset can be found at <a href="https://drive.google.com/file/d/1p7Yf7wsCnBXl7UaxeFC1AP0youl-KCdZ/view?usp=sharing" rel="nofollow noreferrer">https://drive.google.com/file/d/1p7Yf7wsCnBXl7UaxeFC1AP0youl-KCdZ/view?usp=sharing</a></p>
  <p>The query generally returns 10823 rows.  If you eliminate avg(facility) and avg(non_facility) it seems to work.  There is plenty of space in /tmp.  92% of 100G is free.  I set tmp_table_size to max.  Here are the current server settings:</p>
  <pre><code>mysql&gt; show variables like '%tmp%';
  +---------------------------------+----------------------+
  | Variable_name                   | Value                |
  +---------------------------------+----------------------+
  | default_tmp_storage_engine      | InnoDB               |
  | innodb_tmpdir                   |                      |
  | internal_tmp_mem_storage_engine | TempTable            |
  | slave_load_tmpdir               | /tmp                 |
  | tmp_table_size                  | 18446744073709551615 |
  | tmpdir                          | /tmp                 |
  +---------------------------------+----------------------+
  6 rows in set (0.00 sec)
  
  mysql&gt; show variables like '%temp%';
  +-----------------------------+-----------------------+
  | Variable_name               | Value                 |
  +-----------------------------+-----------------------+
  | avoid_temporal_upgrade      | OFF                   |
  | innodb_temp_data_file_path  | ibtmp1:12M:autoextend |
  | innodb_temp_tablespaces_dir | ./#innodb_temp/       |
  | show_old_temporals          | OFF                   |
  | temptable_max_mmap          | 1073741824            |
  | temptable_max_ram           | 1073741824            |
  | temptable_use_mmap          | ON                    |
  +-----------------------------+-----------------------+
  7 rows in set (0.00 sec)
  </code></pre>
  <p>Any ideas on how to work around this?</p>