Hi Varun, On Mon, Jan 29, 2018 at 12:27:35PM +0530, Varun wrote:

revision-id: e1b0a8f9622ab8c2bab988cb71225f992fec320a (mariadb-10.0.30-286-ge1b0a8f9622) parent(s): d01dbe66a8bf9cb6031f95159c49100f9299a768 author: Varun Gupta committer: Varun Gupta timestamp: 2018-01-29 12:23:31 +0530 message:

MDEV-6453: Assertion `inited==NONE || (inited==RND && scan)' failed in handler::ha_rnd_init(bool) with InnoDB, joins, AND/OR conditions

The inited parameter handler is not initialised when we do a quick_select after a table scan.

As far I understand, the problem only shows with "Range Checked for each record". The failure scenario is as follows: - range checked for each record plan in coonstructed - the first check picks to a full table scan. - the second check picks to do a QUICK_ROR_INTERSECT_SELECT scan - QUICK_ROR_INTERSECT_SELECT starts to initialize the quick select. - and it hits an assertion, because the handle object is already initialized - index merge finds the handler not to be initialized correctly. That is, the cause of handler object not being correctly initialized is "range checked for each record" feature. Because of that, I think it should be fixed in that feature as well. A more suitable location would be in range-checked-for-each-record's code, in test_if_quick_select(): diff --git a/sql/sql_select.cc b/sql/sql_select.cc index db3ed8a1aa9..6634554ee6a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -19637,6 +19637,10 @@ test_if_quick_select(JOIN_TAB *tab) delete tab->select->quick; tab->select->quick=0; + + if (tab->table->file->inited != handler::NONE) + tab->table->file->ha_index_or_rnd_end(); + int res= tab->select->test_quick_select(tab->join->thd, tab->keys, (table_map) 0, HA_POS_ERROR, 0, FALSE, /*remove where parts*/FALSE); What do you think? (If you agree, let's use the above variant)

--- mysql-test/r/range_innodb.result | 18 ++++++++++++++++++ mysql-test/t/range_innodb.test | 17 +++++++++++++++++ sql/opt_range.cc | 2 ++ 3 files changed, 37 insertions(+)

diff --git a/mysql-test/r/range_innodb.result b/mysql-test/r/range_innodb.result index 794e6c7b3cc..8bb1c833a56 100644 --- a/mysql-test/r/range_innodb.result +++ b/mysql-test/r/range_innodb.result @@ -37,3 +37,21 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 1 SIMPLE t2 range a,b b 5 NULL 201 Using where; Using join buffer (flat, BNL join) drop table t0,t1,t2; +CREATE TABLE t1 ( +pk INT PRIMARY KEY, f1 INT, f2 CHAR(1), f3 CHAR(1), +KEY(f1), KEY(f2) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(1,4,'v',NULL),(2,6,'v',NULL),(3,7,'c',NULL),(4,1,'e',NULL),(5,0,'x',NULL), +(6,7,'i',NULL),(7,7,'e',NULL),(8,1,'p',NULL),(9,7,'s',NULL),(10,1,'j',NULL), +(11,5,'z',NULL),(12,2,'c',NULL),(13,0,'a',NULL),(14,1,'q',NULL),(15,8,'y',NULL), +(16,1,'m',NULL),(17,1,'r',NULL),(18,9,'v',NULL),(19,1,'n',NULL); +CREATE TABLE t2 (f4 INT, f5 CHAR(1)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (4,'q'),(NULL,'j'); +SELECT * FROM t1 AS t1_1, t1 AS t1_2, t2 +WHERE f5 = t1_2.f2 AND ( t1_1.f1 = 103 AND t1_1.f2 = 'o' OR t1_1.pk < f4 ); +pk f1 f2 f3 pk f1 f2 f3 f4 f5 +1 4 v NULL 14 1 q NULL 4 q +2 6 v NULL 14 1 q NULL 4 q +3 7 c NULL 14 1 q NULL 4 q +drop table t1,t2; diff --git a/mysql-test/t/range_innodb.test b/mysql-test/t/range_innodb.test index f76794814ef..605006587cc 100644 --- a/mysql-test/t/range_innodb.test +++ b/mysql-test/t/range_innodb.test @@ -45,3 +45,20 @@ explain select * from t0 left join t2 on t2.a <t0.a and t2.b between 50 and 250;

drop table t0,t1,t2;

+CREATE TABLE t1 ( + pk INT PRIMARY KEY, f1 INT, f2 CHAR(1), f3 CHAR(1), + KEY(f1), KEY(f2) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES +(1,4,'v',NULL),(2,6,'v',NULL),(3,7,'c',NULL),(4,1,'e',NULL),(5,0,'x',NULL), +(6,7,'i',NULL),(7,7,'e',NULL),(8,1,'p',NULL),(9,7,'s',NULL),(10,1,'j',NULL), +(11,5,'z',NULL),(12,2,'c',NULL),(13,0,'a',NULL),(14,1,'q',NULL),(15,8,'y',NULL), +(16,1,'m',NULL),(17,1,'r',NULL),(18,9,'v',NULL),(19,1,'n',NULL); + +CREATE TABLE t2 (f4 INT, f5 CHAR(1)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (4,'q'),(NULL,'j'); + +SELECT * FROM t1 AS t1_1, t1 AS t1_2, t2 +WHERE f5 = t1_2.f2 AND ( t1_1.f1 = 103 AND t1_1.f2 = 'o' OR t1_1.pk < f4 ); +drop table t1,t2; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f1d84e5c623..30b7f43ef28 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3003,6 +3003,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, quick=0; needed_reg.clear_all(); quick_keys.clear_all(); + if (head->file->inited != handler::NONE) + head->file->ha_index_or_rnd_end(); DBUG_ASSERT(!head->is_filled_at_execution()); if (keys_to_use.is_clear_all() || head->is_filled_at_execution()) DBUG_RETURN(0); _______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog