Description:
A debug assertion can fail in handler::ha_index_init() when a subselect can use a KEY to compute MIN() or MAX():
DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE ||
m_lock_type != F_UNLCK);
This is repeatable in 5.6 onwards with ENGINE=MyISAM. Not with MEMORY or InnoDB.
Because an index is necessary for repeating the bug, the tables cannot be created with ENGINE=CSV nor ENGINE=ARCHIVE.
How to repeat:
Each SELECT below would trigger the assertion, also when prefixed with EXPLAIN.
The same assertion failures will occur if PRIMARY KEY is used instead of KEY.
CREATE TABLE t1 (k INT, KEY(k)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (k INT, KEY(k)) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1);
SELECT * FROM t1 WHERE (SELECT MIN(t1.k) FROM t1 s1);
SELECT * FROM t1 WHERE (SELECT MIN(t1.k) FROM t2);
SELECT * FROM t1 WHERE EXISTS (SELECT MIN(t1.k) FROM t1 s1);
SELECT * FROM t1 WHERE EXISTS (SELECT MIN(t1.k) FROM t2);
DROP TABLE t1;
CREATE TABLE t1 (nk INT, k INT, KEY(k)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,1);
SELECT * FROM t1 WHERE nk = (SELECT MIN(t1.k) FROM t1 s1);
SELECT * FROM t1 WHERE nk = (SELECT MIN(t1.k) FROM t2);
DROP TABLE t1;
DROP TABLE t2;
Suggested fix:
The problem seems to be that const_tables refers to a table that is used later in JOIN::optimize():
if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds)))
With this work-around, the tests will pass.
diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc
index 5e75a09..c5d5a83 100644
--- a/sql/sql_optimizer.cc
+++ b/sql/sql_optimizer.cc
@@ -400,7 +400,7 @@ JOIN::optimize()
error= -1;
DBUG_RETURN(1);
}
- if (const_tables && !thd->locked_tables_mode &&
+ if (0 && const_tables && !thd->locked_tables_mode &&
!(select_options & SELECT_NO_UNLOCK))
{
TABLE *ct[MAX_TABLES];
Description: A debug assertion can fail in handler::ha_index_init() when a subselect can use a KEY to compute MIN() or MAX(): DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK); This is repeatable in 5.6 onwards with ENGINE=MyISAM. Not with MEMORY or InnoDB. Because an index is necessary for repeating the bug, the tables cannot be created with ENGINE=CSV nor ENGINE=ARCHIVE. How to repeat: Each SELECT below would trigger the assertion, also when prefixed with EXPLAIN. The same assertion failures will occur if PRIMARY KEY is used instead of KEY. CREATE TABLE t1 (k INT, KEY(k)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1); CREATE TABLE t2 (k INT, KEY(k)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1); SELECT * FROM t1 WHERE (SELECT MIN(t1.k) FROM t1 s1); SELECT * FROM t1 WHERE (SELECT MIN(t1.k) FROM t2); SELECT * FROM t1 WHERE EXISTS (SELECT MIN(t1.k) FROM t1 s1); SELECT * FROM t1 WHERE EXISTS (SELECT MIN(t1.k) FROM t2); DROP TABLE t1; CREATE TABLE t1 (nk INT, k INT, KEY(k)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1); SELECT * FROM t1 WHERE nk = (SELECT MIN(t1.k) FROM t1 s1); SELECT * FROM t1 WHERE nk = (SELECT MIN(t1.k) FROM t2); DROP TABLE t1; DROP TABLE t2; Suggested fix: The problem seems to be that const_tables refers to a table that is used later in JOIN::optimize(): if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds))) With this work-around, the tests will pass. diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc index 5e75a09..c5d5a83 100644 --- a/sql/sql_optimizer.cc +++ b/sql/sql_optimizer.cc @@ -400,7 +400,7 @@ JOIN::optimize() error= -1; DBUG_RETURN(1); } - if (const_tables && !thd->locked_tables_mode && + if (0 && const_tables && !thd->locked_tables_mode && !(select_options & SELECT_NO_UNLOCK)) { TABLE *ct[MAX_TABLES];