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
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 ;
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.
It appears that when I use the incoming parameter(argument) in the highlights function it throws and error.
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 ;
“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.
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.
