On 10/22/2012 12:53 AM, Timour Katchaounov wrote: > Igor, > > Great, the patch indeed fixes the bug both on 5.3 and 5.5, however the > reduced test case I produced still takes 21 MB, and I was not successful > in reducing it to a size that can be added to the regression test suite. > I already spent too much in reducing the test case. Would you agree that > I push your patch without a test case?
I think you still have to add the test case case (in a separate file) that is run with the --big option. Regards, Igor. > > Timour > > > On 20.10.2012 08:56, Igor Babaev wrote: >> 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) >> _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
