MySQL : Using a function output with IN

Question

I have a MySQL function "GetChildren(id)", which returns a comma separated list of ids that are children of the id passed in to the function.

I would like to use the output of that function as follows:

SELECT * FROM table WHERE id IN (GetChildren(36))

and if id 36 had children ids 37,38 and 39, then I would get those three rows back.

What happens, however, is that I only get the first id.

SQL fiddle seemed to have difficulties with functions, but here is a simplified example that demonstrates the problem:

CREATE TABLE test (
  `ID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO test (ID) VALUES (1),(2),(3),(4);

DELIMITER $$

DROP FUNCTION IF EXISTS `TestFN` $$
CREATE FUNCTION `TestFN` (GivenID  VARCHAR(1024)) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN
  RETURN '2,3,4';
END $$
DELIMITER ;

Once setup, if I run SELECT TestFN(1), I get back 2,3,4 as expected.

If I run SELECT ID FROM test WHERE ID IN (TestFN1), I get back just one row, with ID 2. The same happens with SELECT ID FROM test WHERE ID = (TestFN1)

Is there a way I can use the output of this function for use as if I had typed the IDs in an ID IN (...) clause?

Uueerdo · Accepted Answer · 2018-09-19 18:29:52Z

2

I think MySQL would need to support table-valued functions for you to use one in this way. Otherwise, IN is behaving as expected, someInt IN (astring) casts the IN list members to ints, evaluating in your case to ID IN (2)

However, you can probably use FIND_IN_SET in a manner similar to what you are trying.

Something like FIND_IN_SET(ID, GetChildren(36)) > 0

answered Sep 19, 2018 at 18:29

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL : Using a function output with IN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related