[MDEV-8645] Mariadb match against a view (fulltext search) fails; succeeds in MySql Created: 2015-08-18  Updated: 2016-02-15  Resolved: 2016-02-15

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.3.12, 10.0.21, 5.5, 10.0, 10.1
Fix Version/s: 5.5.47, 10.0.23, 10.1.10

Type: Bug Priority: Major
Reporter: Henrik Bechmann Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Environment:

ubuntu 14.04 lts (digital ocean)
compared sql version 5.1.73-cll (which is why I'm migrating)

Attachments: Zip Archive createdb.sql.zip    
Sprint: 10.0.24

 Description   

I was invited by sergii on mariadb.com kb to submit the following bug (steps to reproduce are at bottom):

DESCRIPTION:

The fulltext search query below fails in mariadb, but succeeds in mysql (mariadb database imported from mysql database using phpmyadmin).

If a base table is substituted, the query succeeds in both mariadb and mysql. So apparently mariadb cannot match against views.

Is there a solution to this? It's messing up my migration. Using 10.0.21.

Thanks.

SELECT Title FROM Nodes_View WHERE (MATCH(Title,Caption,Description,Location,Body) AGAINST ('something'))

TO REPRODUCE:

1. Create test database with attached sql script in both mariadb and mysql.

2. in mariadb run the folliowing query:

SELECT `Title` FROM `Nodes_View` WHERE (MATCH(`Title`,`Caption`,`Description`,`Location`,`Body`) AGAINST('something'))

note that this is run against a view

3. observe that this generates the following error

#1210 - Incorrect arguments to AGAINST

4. run the same query in mysql

5. observe that there is no error

6. run the following query in the mariadb database:

SELECT `Title` FROM `Articles` WHERE (MATCH(`Title`,`Caption`,`Description`,`Location`,`Body`) AGAINST('something'))

note that this is run against a base table

7. observe that the query succeeds

8. run the same query in mysql

9. observe that the query succeeds

Therefore, it appears that fulltext search fails in mariadb against a view

 Comments   
Comment by Elena Stepanova [ 2015-08-18 ]

Thanks for the report and the test case.

Reproducible both with InnoDB and MyISAM tables.
Not reproducible on 5.2, reproducible on 5.3.

Comment by Henrik Bechmann [ 2015-08-18 ]

you're very welcome, and thanks for your quick response!

Comment by Oleksandr Byelkin [ 2016-02-14 ]

I can't reproduce it any more, if you can (on modern version) feel free to reopen it.

Comment by Oleksandr Byelkin [ 2016-02-14 ] 

s_Extensions`.`RepositoryID`)));
 
SELECT `Title` FROM `Nodes_View` WHERE (MATCH(`Title`,`Caption`,`Description`,`Location`,`Body`) AGAINST('something'));
 
Title
 
drop view Articles_View, Nodes_View;
 
drop tables Access_Users, Articles, Articles_Extensions, Binders, Binders_Tree, System_Lookup_Markup_Types_OBSOLETE, System_Repositories;
 
main.test2                               [ pass ]     47

Comment by Elena Stepanova [ 2016-02-15 ]

The problem disappeared in 5.5.47 after this merge

commit 1a72c6fefdb48642f66dc6f32d8bc7a4cc607437 dac3149f3f3cd79c8b2066f1030782eefb62ad15 abf9d35213ba482a3927651ddb98baa47aeb34fd
 
Merge: dac3149 abf9d35
 
Author: Sergei Golubchik <serg@mariadb.org>
 
Date:   Wed Dec 9 11:51:59 2015 +0100
 
 
 
    Merge branch 'bb-5.5-serg' into 5.5

more exactly by the merge of MySQL 5.5

commit abf9d35213ba482a3927651ddb98baa47aeb34fd 50a796dcba2abe5f25c1e4cd8a69d7ea43343a8d 40ae1b9b618fbbc3b494a896a9d074b74e414337
 
Merge: 50a796d 40ae1b9
 
Author: Sergei Golubchik <serg@mariadb.org>
 
Date:   Wed Dec 9 10:00:49 2015 +0100
 
 
 
    Merge branch 'mysql/5.5' into 5.5

more exactly but this commit

commit f92dd6ae6fb8922122049957b6ab7e07b6518bc2
 
Author: Mithun C Y <mithun.c.y@oracle.com>
 
Date:   Mon Oct 12 12:56:36 2015 +0530
 
 
 
    Bug #20007383: HANDLE_FATAL_SIGNAL (SIG=11) IN UPDATE_REF_AND_KEYS.
 
    
    Issue:
 
    ======
 
    The fulltext predicate is inside a subquery and involves
 
    an outer reference; it thus cannot be used for FT index look-up,
 
    but MySQL does not see it, which causes a illegal access.
 
    
    Solution:
 
    =========
 
    Solution is backported from bug#21140088. Outer reference can
 
    not be used as argument of the MATCH function. Added check for
 
    outer reference.

particularly by this change

diff --git a/sql/item_func.cc b/sql/item_func.cc
 
index 9a38fc6..252ca9e 100644
 
--- a/sql/item_func.cc
 
+++ b/sql/item_func.cc
 
@@ -1,4 +1,4 @@
 
-/* Copyright (c) 2000, 2014, Oracle and/or its affiliates.
 
+/* Copyright (c) 2000, 2015, Oracle and/or its affiliates.
 
    Copyright (c) 2009, 2015, MariaDB
 
 
    This program is free software; you can redistribute it and/or modify
 
@@ -6280,9 +6280,7 @@ bool Item_func_match::fix_fields(THD *thd, Item **ref)
 
   table= 0;
 
   for (uint i=1 ; i < arg_count ; i++)
 
   {
 
-    item=args[i];
 
-    if (item->type() == Item::REF_ITEM)
 
-      args[i]= item= *((Item_ref *)item)->ref;
 
+    item= args[i]= args[i]->real_item();
 
     /*
 
       When running in PS mode, some Item_field's can already be replaced
 
       to Item_func_conv_charset during PREPARE time. This is possible
 
@@ -6295,7 +6293,7 @@ bool Item_func_match::fix_fields(THD *thd, Item **ref)
 
     if (!thd->stmt_arena->is_stmt_execute() &&
 
         item->type() != Item::FIELD_ITEM)
 
     {
 
-      my_error(ER_WRONG_ARGUMENTS, MYF(0), "AGAINST");
 
+      my_error(ER_WRONG_ARGUMENTS, MYF(0), "MATCH");
 
       return TRUE;
 
     }
 
     /*

So, it does look like the bug was actually fixed, and not just masked.

Generated at Thu Feb 08 07:28:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.