Why is the function not returning the value correctly in the WHERE clause?

Question

MySQL

Hello, why does the function not always work as it should?
By this team

SELECT * FROM `wp_terms`
 WHERE term_id IN (272,273,160)

3 records are displayed from the database:

$wp_terms = array(
  array('term_id' => '160','name' => '...',
  array('term_id' => '272','name' => '...',
  array('term_id' => '273','name' => '...',
);

Team

SELECT * FROM `wp_terms`
 WHERE term_id IN (Select br_get_parent_cat(273))

Should output the same 3 records, in it the function returns 3 values ​​(272,273,160) , but only one line is displayed (with term_id = value passed to the function)

Here is the function itself:

DELIMITER //  
  
CREATE FUNCTION br_get_parent_cat(var1 INT) RETURNS CHAR(100)
 
DETERMINISTIC  
SQL SECURITY DEFINER  
COMMENT 'Get parent category procedure'
BEGIN
  DECLARE parent_id INT;  
    DECLARE result, result_while, parent_id_vchar  CHAR(100);
    SET parent_id = 0;
    SET parent_id_vchar = '';
    SET result_while = '';
    SET result = var1;     
    
    SET parent_id = (SELECT `parent` FROM wp_term_taxonomy  WHERE `term_taxonomy_id` = var1);  
  SET result = concat(result, ", ", parent_id);
      
  	WHILE parent_id <> 0 DO  
       SET parent_id = ( SELECT `parent` FROM wp_term_taxonomy  WHERE `parent` <> 0 AND `term_taxonomy_id` = parent_id );      
       IF (parent_id IS NOT NULL) THEN 
        	 SET parent_id_vchar = CONVERT(parent_id, CHAR(100));   
             SET result_while = concat(result_while, ", ", parent_id_vchar);
    	END IF; 
    END WHILE;  
    
    SET result = concat(result, result_while);
    RETURN result;
    
END//  

DELIMITER ;

SELECT br_get_parent_cat(273);
Execution result:
273,272,160
What could be the problem? Thank you.

Answer 1

'273,272,160' ≠ 273,272,160
Your function returns a string, and a string is not a list. To search within a string, use FIND_IN_SET() . Naturally, the index by `term_id` will not work in this case.