Bug #6523 Pb with subquery and fulltext
Submitted: 9 Nov 2004 14:01 Modified: 3 Dec 2004 23:10
Reporter: GilloS WIATR Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.17 OS:Windows (Windows 2000)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[9 Nov 2004 14:01] GilloS WIATR 
Description:
Hello,

We found a critical problem (for us) with subqueries. Currently it doesn't work when using IN and subquery with MATCH condition. Example :

select count(distinct id_msg) from messages
where
    id_msg IN (select id_msg from parts where match (contenu) against('mysql' in boolean mode))

The subquery returns some "id_msg" from table "parts" which exist in table "messages" but the full query returns an error :

[MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]Got error -1 from storage engine

We tried to execute this query in "MySQL Query Browser" but it doesn't return any result ; insted of, we get an error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Thanks for replying.

How to repeat:
Create two tables and fill them.

Create a fulltext index on a table and execute a query with an IN condition on a subquery using fulltext index.
[9 Nov 2004 18:00] MySQL Verification Team 
Verified with 4.1.8-debug-log
OS: Windows, Linux
[15 Nov 2004 12:12] MySQL Verification Team 
Test case:

create table t1 (id int);
create table t2 (id int, body text, fulltext (body));
insert into t1 values(1),(2),(3);
insert into t2 values
(1,'test'),
(2,'mysql'),
(3,'test'),
(4,'test');
select count(distinct id) from t1 where id in (select id from t2 where match(body) against ('mysql' in boolean mode));
[16 Nov 2004 21:03] Oleksandr Byelkin 
ChangeSet 
  1.2108 04/11/16 22:58:02 bell@sanja.is.com.ua +6 -0 
  backport Serg's fix of FT interface (BUG#6523)
[3 Dec 2004 23:10] Oleksandr Byelkin 
Thank you for bugreort! Bug is fixed, patch is pushed into our internal repository.