COUNT(DISTINCT) over a blob column fails with ER_DUP_UNIQUE when its temporary table is converted to an on-disk table

COUNT(DISTINCT) over a blob or text column aborts the statement with

ERROR 1169 (23000): Can't write, because of unique constraint, to table '(temporary)'

when the temporary table that collects the distinct values outgrows the in-memory limit and is converted to an on-disk table. The same statement returns the correct count on upstream main, where the temporary table for a blob column is created on disk to begin with and never converts.

How to repeat

SET SESSION max_recursive_iterations=100000;

CREATE TABLE tc (v TEXT);

INSERT INTO tc

WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i+1 FROM n WHERE i < 2000)

SELECT LPAD((i+1) DIV 2, 6, '0') FROM n;

SELECT COUNT(*), COUNT(DISTINCT v) FROM tc;

SET SESSION tmp_memory_table_size=16384, max_heap_table_size=16384;

SELECT COUNT(DISTINCT v) FROM tc;

DROP TABLE tc;

The table holds 2000 rows forming 1000 distinct values, each value present twice, with the two copies adjacent. The first COUNT(DISTINCT v) runs at the default sizes and answers 1000. The second differs only in that the temporary table no longer fits in memory.

Result

SELECT COUNT(*), COUNT(DISTINCT v) FROM tc;

+----------+-------------------+

| COUNT(*) | COUNT(DISTINCT v) |

+----------+-------------------+

|     2000 |              1000 |

+----------+-------------------+

SET SESSION tmp_memory_table_size=16384, max_heap_table_size=16384;

SELECT COUNT(DISTINCT v) FROM tc;

ERROR 1169 (23000): Can't write, because of unique constraint, to table '(temporary)'

A VARCHAR column in place of the TEXT column answers 1000 in both runs.

Analysis

The distinct values of the aggregate are collected in a temporary table. Where that table is created decides whether the failure is reachable.

On upstream main the table has a blob column, so it is created on the on-disk engine immediately and is never converted. The status counters show one temporary table and one on-disk temporary table, that is a single table created on disk.

With blob columns supported in the in-memory engine the same table is created in memory, overflows, and is converted. The counters then show two temporary tables and one on-disk temporary table, which is the signature of a table created in memory and then converted:

                          Created_tmp_tables  Created_tmp_disk_tables  result

upstream main, TEXT                        1                        1  1000

blob-in-HEAP build, TEXT                   2                        1  ER_DUP_UNIQUE

upstream main, VARCHAR(300)                1                        0  1000

blob-in-HEAP build, VARCHAR(300)           1                        0  1000

The conversion writes the row that overflowed the in-memory table into the new table. That row is a duplicate of a value already collected, which for a deduplicating table is the ordinary case and not an error. Whether it is treated as an error is decided by the ignore_last_dupp_key_error argument the caller passes, and Aggregator_distinct::add() passes zero:

      bool is_duplicate;

      if (!table->file->is_fatal_error(error, HA_CHECK_DUP))

        return FALSE;                           // duplicate, not an error

      ...

      if (create_internal_tmp_table_from_heap(table->in_use, table,

                                              tmp_table_param->start_recinfo,

                                              &tmp_table_param->recinfo,

                                              error, 0, &is_duplicate, NULL))

        return TRUE;

Three lines above the call the same function returns FALSE for the same condition, with the comment "duplicate, not an error", and the is_duplicate value the call fills in is never read afterwards. So the duplicate that arrives through the ordinary write path is discarded as intended, while the identical duplicate that arrives through the conversion aborts the statement.

The argument is the same on upstream main. Only the reachability of the conversion differs, which is why the statement is correct there and fails here.

Versions tested

All four builds are debug builds, and in each the compiled SOURCE_REVISION was checked against the checked out revision. The same script was run against all of them.

a1b3f980ddd   upstream main, no blob-in-HEAP                     1000, correct

4411ba183b5   preview branch without the blob-in-HEAP work       1000, correct

45a5bc3ee51   bb-blob-main-monty ancestor, blob-in-HEAP          ER_DUP_UNIQUE

47f0242d888   bb-blob-main-monty tip                             ER_DUP_UNIQUE

45a5bc3ee51 is an ancestor of the branch tip, confirmed with git merge-base --is-ancestor. It carries the blob-in-HEAP work but not the later temporary table changes, and it fails, so the failure comes from the blob-in-HEAP work and not from anything after it.