On 10/22/2012 07:54 AM, Igor Babaev wrote: > 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.
I've changed my mind: probably it's not a good idea. You can't reproduce it it with a much smaller test case because the min value for tmp_table_size=16384 is too big. I would recommend to add a long dummy field that would make the size of the row in the derived field long enough (~1L long) Regards, Igor. > > 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 : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
