Re: fulltextsearch causes infinitive loop in mysqld

Sergei Golubchik wrote:
> Hi!
> 
> On Oct 15, hurle wrote:
> 
>>Hi!
>>
>>I think I found a bug in mysql using version 4.0.15:
>>Using any database with or without fulltext indexes the mysqld seems to 
>>get in a infinit loop when I query something like:
>>
>>select * from <tableName> where match(<columnName>) against('T* A* B* 
>>T*' in boolean mode);
>>
>>select * from <tableName> where match(<columnName>) against('T* A* D* 
>>F*' in boolean mode);
>>
>>==> all rows with <columnName> beginning with "T" or "A" or "D" but NO 
>>rows beginning with "F".
> 
> 
> I don't think it's an infinite loop.
> Most probably your query matches huge number of rows and MySQL gets very
> busy removing duplicates (rows that match both e.g. 'T*' and 'A*').

Well, I tried it with a very small database...
I just created a very small testdb:
create table test (id integer primary key not null, lastname varchar(100));
INSERT INTO `test` (`id`, `lastname`) VALUES (0, 'Anders');
INSERT INTO `test` (`id`, `lastname`) VALUES (1, 'Tricky');
INSERT INTO `test` (`id`, `lastname`) VALUES (2, 'Tron');
INSERT INTO `test` (`id`, `lastname`) VALUES (3, 'Dickens');
INSERT INTO `test` (`id`, `lastname`) VALUES (4, 'Digger');
INSERT INTO `test` (`id`, `lastname`) VALUES (5, 'Duck');
INSERT INTO `test` (`id`, `lastname`) VALUES (6, 'Font');
INSERT INTO `test` (`id`, `lastname`) VALUES (7, 'Find');
INSERT INTO `test` (`id`, `lastname`) VALUES (8, 'Zone');
INSERT INTO `test` (`id`, `lastname`) VALUES (9, 'Xtra');
INSERT INTO `test` (`id`, `lastname`) VALUES (10, 'Yps');

> 
> But let's get some numbers.
> What are the results for queries:
> 
> SELECT COUNT(*) from <tableName>;
11
> SELECT COUNT(*) from <tableName> where
> match(<columnName>) against('T*' in boolean mode);
2
> SELECT COUNT(*) from <tableName> where
> match(<columnName>) against('A*' in boolean mode);
1
> SELECT COUNT(*) from <tableName> where
> match(<columnName>) against('D*' in boolean mode);
3
> SELECT COUNT(*) from <tableName> where
> match(<columnName>) against('F*' in boolean mode);
2

additional information:
SELECT COUNT(*) from <tableName> where
match(<columnName>) against('T* A* D* F*' in boolean mode);
6

but the entries in <columnName> only contain single words without any 
whitespace characters, commas, points, etc.
so the last query should return 8 (2+1+3+2=8)

another very interesting thing:
SELECT COUNT(*) from <tableName> where
match(<columnName>) against('T* A* D* T*' in boolean mode);
6

*BUT*

SELECT COUNT(*) from <tableName> where
match(<columnName>) against('A* T* D* A*' in boolean mode);
mysqld is still running...


On another table the last query succeded but
SELECT COUNT(*) from <tableName> where
match(<columnName>) against('A* T* D* Y* A*' in boolean mode);
lead to the loop (or a very very long query time)

Regards,
hurle

>  
> Regards,
> Sergei
> 


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]