MCS-1000 'not joined' on self-join via derived table over default-engine table when columnstore_innodb_queries_use_mcs=ON (devregression.mcs7132)

Summary

Self-join through a derived table over a default-engine (InnoDB) table fails with MCS-1000: 'bug3952, n1' and 'sub-query' are not joined. Same code path / error class as MCOL-5516 (cross-engine view join).

Repro (state-dependent — full devregression suite required)

USE tpch1;

CREATE TABLE bug3952 (id INT, name VARCHAR(25));   -- default engine = InnoDB

INSERT INTO bug3952 VALUES (1,'aaa'),(2,'bbb');

SELECT n1.id, n1.name FROM bug3952 n1, (SELECT id FROM bug3952) n2 WHERE n1.id = n2.id;

-- ERROR 1815: MCS-1000: 'bug3952, n1' and 'sub-query' are not joined.

Standalone mtr ... mcs7132_regression_bug3952 against a fresh server passes. Failure fires only as part of the full devregression suite (rocky9 11.8 + innodb_queries_use_mcs=ON + tpch1 schema preloaded). Reproduction container setup: see MCOL-6340.

Root cause (suspected)

With innodb_queries_use_mcs=ON the InnoDB bug3952 table goes through the ColumnStore plan builder. The CEJ join classifier (dbcon/joblist/jlf_execplantojoblist.cpp, search for not joined in joinTables / joinTablesAfterFiltering) fails to equate the base table use (bug3952 n1) with its appearance inside the derived sub-query (the n2 alias) because they receive different table-key/alias signatures.

Trigger / status

Pre-existing bug. Was exposed in CI by columnstore commit 09783833b (2026-04-17, which set innodb_queries_use_mcs=ON globally via run_mtr.sh). That approach was reverted in MCOL-6340 / PR #3989, which scopes the variable back to the future suite via suite.opt. As a result:

• devregression.mcs7132_regression_bug3952 is not disabled and passes in CI under the standard suite configuration.
• The bug is still reproducible by enabling innodb_queries_use_mcs=ON globally and running the full devregression suite.

Affected MTR test

columnstore/devregression.mcs7132_regression_bug3952 (artifacts attached: mcs7132_artifacts.tgz).