Incorrect result with outer join and full text match

[Timour Katchaounov]

The following test case derived from fulltext.test produces wrong result:

CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1));
INSERT INTO t1 VALUES ('test');

CREATE TABLE t2(f2 VARCHAR(6) NOT NULL, FULLTEXT KEY(f2), UNIQUE(f2));
INSERT INTO t2 VALUES ('test');

-- Correct result: the ON predicate is FALSE
SELECT * FROM t2 LEFT OUTER JOIN t1 ON (f1 = "");
+------+------+
| f2 | f1 |
+------+------+
| test | NULL |
+------+------+

SELECT * FROM t1 RIGHT OUTER JOIN t2 ON (f1 = "");
+------+------+
| f1 | f2 |
+------+------+
| NULL | test |
+------+------+

-- Wrong result: the ON predicate is FALSE as above, but the result is different:
SELECT * FROM t2 LEFT OUTER JOIN t1 ON (MATCH(f1) against (""));
+------+------+
| f2 | f1 |
+------+------+
| test | test |
+------+------+

SELECT * FROM t1 RIGHT OUTER JOIN t2 ON (MATCH(f1) against (""));
+------+------+
| f1 | f2 |
+------+------+
| test | test |
+------+------+

[Timour Katchaounov (timour)]

The above wrong result is part of a more complicated query from the
patch below. After fixing this bug, please check the complete test case
for MySQL Bug#54484 in fulltext.test.

------------------------------------------------------------
revno: 2502.1086.9
committer: Sergey Glukhov <email address hidden>
branch nick: mysql-5.1-security
timestamp: Mon 2010-10-18 14:47:26 +0400
message:
  Bug#54484 explain + prepared statement: crash and Got error -1 from storage engine
  Subquery executes twice, at top level JOIN::optimize and ::execute stages.
  At first execution create_sort_index() function is called and
  FT_SELECT object is created and destroyed. HANDLER::ft_handler is cleaned up
  in the object destructor and at second execution FT_SELECT::get_next() method
  returns error.
  The fix is to reinit HANDLER::ft_handler field before re-execution of subquery.

[Sergey Petrunia (sergefp)]

Repeatable in MySQL 5.5

[Sergey Petrunia (sergefp)]

The fix is in commits@. I did not push it because I doubt it is important for the release.

[Sergey Petrunia (sergefp)]

This was fixed in MariaDB 5.3.3