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