Description:
Hi,
MySQL crashes in FT code when trying to do a query with a join.
Regards,
Jocelyn
How to repeat:
Get tables in ftp://ftp.mysql.com/pub/mysql/upload/crashft.tar.gz
Then execute the following query :
SELECT id_stock, prix, port, ref_mag, stocks.marque, nom_mag, dispo, magasins.id_mag FROM magasins_bom as magasins, stocks WHERE stocks.maj = 1 AND stocks.magasin = magasins.id_mag AND (MATCH (stocks.marque_ref) AGAINST ('+Asus +DRW-1604P' IN BOOLEAN MODE)) ORDER BY prix ASC LIMIT 0,20;
MySQL crashes with the following stack :
0x808bab3 handle_segfault + 423
0x82db038 pthread_sighandler + 184
0x8299c76 _ftb_climb_the_tree + 354
0x8299d82 ft_boolean_read_next + 174
0x80fba70 ha_myisam::ft_read(char *) + 60
0x80c8071 join_ft_read_first(st_join_table *) + 101
0x80c7039 sub_select(JOIN *, st_join_table *, bool) + 97
0x80c70ba sub_select(JOIN *, st_join_table *, bool) + 226
0x80c6d96 do_select(JOIN *, List<Item> *, st_table *, Procedure *) + 514
0x80bcbe8 JOIN::exec(void) + 1016
0x80bdd62 mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_orderT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 818
0x80badb6 handle_select(THD *, st_lex *, select_result *) + 150
0x809acea mysql_execute_command(THD *) + 1254
0x809f319 mysql_parse(THD *, char *, unsigned int) + 169
0x80999e8 dispatch_command(enum_server_command, THD *, char *, unsigned int) + 1572
0x80993b8 do_command(THD *) + 188
0x8098a87 handle_one_connection + 615
0x82d87ec pthread_start_thread + 220
0x83020ba thread_start + 4
mysql> EXPLAIN SELECT id_stock, prix, port, ref_mag, stocks.marque, nom_mag, dispo, magasins.id_mag FROM magasins_bom as magasins, stocks WHERE stocks.maj = 1 AND stocks.magasin = magasins.id_mag AND (MATCH (stocks.marque_ref) AGAINST ('+Asus +DRW-1604P' IN BOOLEAN MODE)) ORDER BY prix ASC LIMIT 0,20;
+----+-------------+----------+----------+------------------------+------------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+----------+------------------------+------------+---------+------+------+---------------------------------+
| 1 | SIMPLE | magasins | ALL | PRIMARY | NULL | NULL | NULL | 69 | Using temporary; Using filesort |
| 1 | SIMPLE | stocks | fulltext | magasin,maj,marque_ref | marque_ref | 0 | | 1 | Using where |
+----+-------------+----------+----------+------------------------+------------+---------+------+------+---------------------------------+
2 rows in set (0.00 sec)
If I modify the 'magasin' field of the table stocks to a varchar(40) so that an eq_ref join could be used, the crash doesn't occur anymore :
ALTER TABLE stocks MODIFY `magasin` varchar(40) NOT NULL;
SELECT id_stock, prix, port, ref_mag, stocks.marque, nom_mag, dispo, magasins.id_mag FROM magasins_bom as magasins, stocks WHERE stocks.maj = 1 AND stocks.magasin = magasins.id_mag AND (MATCH (stocks.marque_ref) AGAINST ('+Asus +DRW-1604P' IN BOOLEAN MODE)) ORDER BY prix ASC LIMIT 0,20;
[...]
10 rows in set (0.01 sec)
mysql> EXPLAIN SELECT id_stock, prix, port, ref_mag, stocks.marque, nom_mag, dispo, magasins.id_mag FROM magasins_bom as magasins, stocks WHERE stocks.maj = 1 AND stocks.magasin = magasins.id_mag AND (MATCH (stocks.marque_ref) AGAINST ('+Asus +DRW-1604P' IN BOOLEAN MODE)) ORDER BY prix ASC LIMIT 0,20;
+----+-------------+----------+----------+------------------------+------------+---------+----------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+----------+------------------------+------------+---------+----------------------+------+-----------------------------+
| 1 | SIMPLE | stocks | fulltext | magasin,maj,marque_ref | marque_ref | 0 | | 1 | Using where; Using filesort |
| 1 | SIMPLE | magasins | eq_ref | PRIMARY | PRIMARY | 40 | crash.stocks.magasin | 1 | |
+----+-------------+----------+----------+------------------------+------------+---------+----------------------+------+-----------------------------+
2 rows in set (0.00 sec)
