[MDEV-2144] LP:906357 - Incorrect result with outer join and full text match Created: 2011-12-19  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Timour Katchaounov (Inactive) Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug906357.xml    

 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

----------+

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-12-19 ]

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.

Comment by Sergei Petrunia [ 2011-12-19 ]

Re: Incorrect result with outer join and full text match
Repeatable in MySQL 5.5

Comment by Sergei Petrunia [ 2011-12-19 ]

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.

Comment by Sergei Petrunia [ 2012-01-11 ]

Re: Incorrect result with outer join and full text match
This was fixed in MariaDB 5.3.3

Comment by Rasmus Johansson (Inactive) [ 2012-02-20 ]

Launchpad bug id: 906357

Generated at Thu Feb 08 06:39:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.