maria-developers team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

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)
>>>
>