Bug #6523 Pb with subquery and fulltext
Submitted: 9 Nov 2004 15:01 Modified: 4 Dec 2004 0:10
Reporter: GilloS WIATR
Status: Closed
Category:Server Severity:S1 (Critical)
Version:4.1.17 OS:Microsoft Windows (Windows 2000)
Assigned to: Bugs System Target Version:

[9 Nov 2004 15: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 19:00] Victoria Reznichenko 
Verified with 4.1.8-debug-log
OS: Windows, Linux
[15 Nov 2004 13:12] Victoria Reznichenko 
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 22: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)
[4 Dec 2004 0:10] Oleksandr Byelkin 
Thank you for bugreort! Bug is fixed, patch is pushed into our internal repository.