On 10/19/2012 12:43 PM, Timour Katchaounov wrote:
Hi Igor,
Could you please look at this bug https://mariadb.atlassian.net/browse/MDEV-3801, and read my last comment. The bug may be (just a guess ATM) related to index creation on the temporary table created for the derived table. Since this is your code, I'd like you to tell me if you have seen anything similar before, or if you have any hints.
If not, I will continue with the bug and will trace the cause for the wrong key information.
Thanks, Timour
Timour, The following patch fixes this problem Regards, Igor. === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2012-10-10 05:21:22 +0000 +++ sql/sql_select.cc 2012-10-20 05:49:13 +0000 @@ -8981,7 +8981,7 @@ void JOIN::drop_unused_derived_keys() JOIN_TAB *tab; for (tab= first_linear_tab(this, WITHOUT_CONST_TABLES); tab; - tab= next_linear_tab(this, tab, WITHOUT_BUSH_ROOTS)) + tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { TABLE *table=tab->table; MariaDB [test]> set @@tmp_table_size=16384; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test Query OK, 0 rows affected (5.48 sec) MariaDB [test]> set @@tmp_table_size=16384; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> EXPLAIN SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 8488 | | | 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 1 | PRIMARY | t10 | ref | PRIMARY,full_id | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 8488 | | | 3 | DERIVED | t4 | system | PRIMARY | NULL | NULL | NULL | 1 | Using temporary | | 3 | DERIVED | t3 | ref | PRIMARY,feid,exchange,deal_feid,feid_date | exchange | 3 | const | 8488 | | | 3 | DERIVED | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t3.deal_id | 1 | Using index | | 3 | DERIVED | t2 | eq_ref | PRIMARY,full_id | PRIMARY | 8 | test.t3.deal_id,test.t3.feid | 1 | Using index; Distinct | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ 8 rows in set (4.22 sec) MariaDB [test]> SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +---------+ | deal_id | +---------+ | 30 | | 82 | | 83 | | 108 | | 155 | | 156 | | 162 | | 184 | | 186 | | 195 | +---------+ 10 rows in set (10.62 sec)