Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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 |
----------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Incorrect result with outer join and full text match
Repeatable in MySQL 5.5
Re: Incorrect result with outer join and full text match
The fix is in commits@. I did not push it because I doubt it is important for the release.
Re: Incorrect result with outer join and full text match
This was fixed in MariaDB 5.3.3
Launchpad bug id: 906357
Re: Incorrect result with outer join and full text match
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 <Sergey.Glukhov@sun.com>
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.