maria-developers team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

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)