Parallel Query: Use temporary work tables to ship results: basic test

Current idea about shipping results from worker threads to the main thread is to use temporary (work) tables.

In a situation with N workers:
The primary thread creates N temporary tables, passes them over to the workers (each gets its own).
Each worker writes the results of its query portion into its temporary table.
Then, it notifies the primary thread that it is done.
After that, the worker doesn't access the table anymore.
The primary thread reads the data from the temp table and either does further processing or just sends it back to the client.

Questions

Need to verify that the following works:
Q1: Verify that temp.table can be created in thread1 and used in thread2
Assume that here is no concurrent access. Just check that no assert fires/etc.

Q2: How can one create multiple tmp. tables? How does one do that?
Consider code implementing SELECT SQL_BUFFER_RESULT. It creates a temp.table. Can we create multiple temporary tables with this? (Or we'll need a multiple TMP_TABLE_PARAM structures? )

MS 0.1: Create/Free multiple temp.tables

Make use of this property: SQL_BUFFER_RESULT causes the server to buffer the query output in a temporary table. Make it so that

set parallel_worker_threads=N;

select SQL_BUFFER_RESULT * from t1;

will create (and eventually free) N temporary tables.
Only the first created table should be used for storing the rows.

MS 0.2: Test that putting data into a temp. table works

The idea is to test that another thread can write data into a temp table.
Create a dummy function to write a row into a table. Let it write either default values or some other pre-defined values (denote this TEST-ROW).
Use that function in worker threads to write a row into the table.
Then, the main query should collect data from the temp.tables and send them to the output.
Thus, the query output with N workers will have N extra rows with values of TEST-ROW.

Problems with Aria conversion from Heap in worker threads

(based on Slack conversation between SergeiP and Rex)

1. The converted table gets opened on the worker thread (not easily fixable (DOUBT THIS))

create_internal_tmp_table_from_heap() calls open_tmp_table(&new_table) → maria_open(), which ends with:

m_info->stack_end_ptr= &my_thread_var->stack_ends_here; // ma_open.c:1201

Every Aria key and record path then does alloc_on_stack(*info->stack_end_ptr, …). That's a TLS lookup, so the handle captures whichever thread called open.

The worker then finishes, and destroy_background_thd() → my_thread_end() frees that st_my_thread_var. The manager afterwards scans the container through a handle whose stack_end_ptr points into freed
memory.

That's the invariant the conversion breaks. The HEAP container never had this problem because layout.make_container() runs on the manager thread — whatever thread-local it captures belongs to a thread that outlives every worker. The conversion re-opens the table on the wrong thread, at the worst possible moment: just before that thread dies.

SergeiP:
agree that stack_end_ptr needs to be updated when the table is "passed" from one thread to another.
AFAIU alloc_on_stack() is not a TLS lookup. It is a "safe alloca call" - it looks at the current stack pointer, end of the stack, and allocates buffer space on the stack.

We WILL need to update stack_end_ptr when we pass the temp table between the threads. Good catch. Looks easy to fix.

2. …and re-opens it as an internal table (not easily fixable by me)

bool open_tmp_table(TABLE *table, bool cross_thread= false); // sql_select.h:2715
...
if (open_tmp_table(&new_table)) // sql_select.cc:23887

The conversion takes the default, so it passes HA_OPEN_INTERNAL_TABLE — the exact flag make_container() deliberately omits by passing cross_thread=true. Inside maria_open() that sets share->internal_table= 1, which skips THR_LOCK_maria and the global share list. It declares "private to one thread" for a table two threads use.

3. Wrong THD for the decisions, (fixable)

The conversion reads thd->lex->first_select_lex()>options | thd>variables.option_bits, and ha_lock_engine(thd, …) records a plugin ref on thd->lex->plugins for release at statement end. A worker's background THD never parsed the statement and doesn't inherit the session's variables, so the on-disk table would be built under the wrong options and disk limits, against a LEX nobody ends.

Aria itself is fine.

Can we create the tmp table on the worker thread to bypass this issue?

Memory accounting — immediately fatal. create_tmp_table() gives the table its own root:
Thread-specific allocations are charged to current_thd at alloc and credited at free (mysqld.cc:3811-3816). Worker allocates, manager frees, and you get it from both ends: the worker's local_memory_used never comes back down, and ~THD() asserts.

Can we delegate tmp table conversion to the manager?

Yes, but it's quite intrusive and the manager will block during conversion, extra signalling, mutexes etc.

Agree it doesn't look like a good idea.

Best Option.

Manager looks at our expected number of rows in our tmp table and creates an empty per-worker Aria table to use if we might need it.