Full Text HIGHLIGHT function not working in Stored Procedures

Anyone have any luck using the HIGHLIGHT feature in a Stored Procedure with a variable for search term? If I include an acutal value, ti runs fine, but since my search criteria changes, I need that same criteria included in the HIGHLIGHT Results.

(HIGHLIGHT(ocr) against (p_ocr_search_term) ocr_snippit,

Error Code: 1064. Unhandled exception Type: ER_PARSE_ERROR Message: Leaf Error (XXXXX18:3309): 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 ‘(‘FIELD1’:>varchar(4000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL))) AS C’ at line 1 Callstack: #0 Line 280 in xxx.GetResultsOCR

Hello,

Seems we support PL variables after the against, the following works for me:

create database if not exists db;
use db;
delimiter //
create or replace procedure foo() as
declare x text = 'gao';
      src text = 'hooo gao ‱   週   aga';
begin
    echo select highlight(src) against (x) as a;
end //
delimiter ;
call foo();```

I got this one to work. It must have something to do with my joins between columnstore and rowstore tables. I’ll keep digging to see if I can get code example.

delimiter //
CREATE or REPLACE PROCEDURE landdm.foo (p_argument varchar(50) )
RETURNS query(arguement varchar(200),highlights varchar(200))
AS declare
q query(arguement varchar(200), highlights varchar(200))
= select p_argument arguement, highlight(‘hooo gao ‱ 週 aga’) against (p_argument) as highlights;

BEGIN
start transaction;
return q;
end //
delimiter ;

echo foo(‘gao’);

image

So I’ve isolated the scenario in which this happens. In my case I’m running this query with additional joins, inserting into a temp table, then appending other information before returning the results to the customer.

  1. It appears that when I use the incoming parameter(argument) in the highlights function it throws and error.
  2. When the highlights function has a literal string as a value in the procedure, but the match is still using the incoming parameter it works.

assuming this argument:
echo foo(9275856,’“MARTIN”’);

This Procedure does not work:

delimiter //
CREATE or REPLACE PROCEDURE foo(p_id int(11),p_ocr_search_term varchar(4000) )
RETURNS query(ocr_snippit longtext)
AS declare q query(ocr_snippit longtext)
= select highlight(ocr) against (p_ocr_search_term) ocr_snippit
from ocr_table
where MATCH(ocr) against (p_ocr_search_term) and id = p_id ;
BEGIN
start transaction;
return q;
end //
delimiter ;

Error:
Error Code: 1064. Leaf Error (xxxxxx:3306): 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 ‘(’“MARTIN”’:>varchar(4000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL))):’ at line 1

This works:

delimiter //
CREATE or REPLACE PROCEDURE foo(p_id int(11),p_ocr_search_term varchar(4000) )
RETURNS query(ocr_snippit longtext)
AS declare q query(ocr_snippit longtext)
= select highlight(ocr) against (’“MARTIN”’) ocr_snippit
from ocr_table
where MATCH(ocr) against (p_ocr_search_term) and id = p_id ;
BEGIN
start transaction;
return q;
end //
delimiter ;

image

"ocr_table" Definition

CREATE TABLE ocr_table (
id int(11) NOT NULL,
version int(11) NOT NULL,
ocr longtext CHARACTER SET utf8 COLLATE utf8_general_ci,
update_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
json_key longtext CHARACTER SET utf8 COLLATE utf8_general_ci,
KEY id (id) /*!90619 USING CLUSTERED COLUMNSTORE /,
/
!90618 SHARD / KEY id_shard (id),
FULLTEXT KEY ocr (ocr)
) /
!90621 AUTOSTATS_ENABLED=TRUE */

I’d appreciate any help you can offer. Is there an easy way to bind the incoming parameter to a text variable then using that variable in the query? I tried using the declare against the paramter to do so without success.

Hey, just wanted to follow up that we’ve confirmed this is an engine bug. I don’t know of any workaround at this time :confused:

Thanks. Should I put in a support ticket?

I was really hoping I could bind it to a scalar variable and maybe the aggregator would force a push-down of the value to the nodes since the match and highlight is probably happening there. I suspect that’s the issue. That’s why a literal string has no issues being pushed down.

Hi sproksell

Thanks for asking such an in-depth question!

We’re up able to get the help that you needed? Did you get to file a support ticket?

No need to file a support ticket, we have a bug filed internally for it. Will be fixed in a future release :slight_smile:

1 Like

Awesome – thanks Evan :slight_smile: