Bug #118148 Error When Deleting from MERGE Table with Subquery Condition
Submitted: 9 May 3:47 Modified: 10 Jun 13:21
Reporter: Chenglin Tian Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:9.2.0 OS:Linux
Assigned to: CPU Architecture:x86

[9 May 3:47] Chenglin Tian 
Description:
While attempting to execute a DELETE statement on the m_10m MERGE table, an error occurred indicating that the underlying table cannot be opened due to differing definitions or non-MyISAM types. The specific SQL command was designed to delete entries from m_10m where the c2 values do not meet certain criteria based on a subquery from t_10m. This suggests a limitation in handling subqueries for DELETE operations on MERGE tables, particularly when the referenced table's structure or engine type differs.

How to repeat:
CREATE TABLE m_10m (c1 INT NOT NULL, c2 VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'another_example_string!@#%$^&*()_+', UNIQUE INDEX uq_c1 (c1)) ENGINE=MERGE UNION=(t_10m);

Error executing SQL:
DELETE FROM m_10m WHERE c2 NOT IN (SELECT c2 FROM (SELECT c2 FROM t_10m WHERE CHAR_LENGTH(c2) > 10) AS subquery2 HAVING COUNT(c2) > 5)
Error Message:(1168, "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist")
[10 Jun 13:21] MySQL Verification Team 
Hello Chenglin Tian,

Thank you for the bug report.
Imho this is not a bug, quoting from the manual - 

The disadvantages of MERGE tables are:

    You can use only identical MyISAM tables for a MERGE table. 

For more info please see https://dev.mysql.com/doc/refman/9.2/en/merge-table-advantages.html

Regards,
Ashwini Patil