maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04934
Re: Please look at and advise about MDEV-3801
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)
>>
Follow ups
References