Bug #105794 Regression in FIND_IN_SET() between 8.0.21 and 8.0.22
Submitted: 3 Dec 2021 17:14 Modified: 21 Dec 2021 20:26
Reporter: David Landgren Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.22, 8.0.27 OS:Ubuntu (20.04)
Assigned to: CPU Architecture:x86
Tags: regression

[3 Dec 2021 17:14] David Landgren 
Description:
When using a variable as the first argument to FIND_IN_SET(), the resulting index returned is based on the its location in the second argument, instead of the table definition.

How to repeat:
Create a table with a SET column:

CREATE database IF NOT EXISTS bug;
SHOW CREATE DATABASE bug;
USE bug;

DROP TABLE IF EXISTS bug;
CREATE TABLE bug (
    payload set('alpha','bravo','charlie','delta','echo','foxtrot','golf') NOT NULL DEFAULT 'alpha'
);
INSERT INTO bug (payload) VALUES ('bravo,delta,foxtrot'), ('foxtrot');

-- and run the following statements:

SET @a = 'foxtrot';

SELECT @@version as v, 'by str' as param, FIND_IN_SET('foxtrot', payload) as retval, payload FROM bug
UNION
SELECT @@version, 'by var', FIND_IN_SET(@a, payload), payload FROM bug

In 8.0.21 and below, as well as 5.7, the following queries return the same results.

v	param	retval	payload
8.0.21	by str	6	bravo,delta,foxtrot
8.0.21	by str	6	foxtrot
8.0.21	by var	6	bravo,delta,foxtrot
8.0.21	by var	6	foxtrot

This can be interpreted as meaning 'foxtrot' is the sixth (6) element, as per the table definition.

In 8.0.22, the following results are produced:

8.0.22	by str	6	bravo,delta,foxtrot
8.0.22	by str	6	foxtrot
8.0.22	by var	3	bravo,delta,foxtrot
8.0.22	by var	1	foxtrot

The values change when calling FIND_IN_SET(@a, payload). In this case, the returned value refers to where the element is found in the row, instead of referring to the table definition.

Forcing a cast FIND_IN_SET(CAST(@a AS CHAR), payload) does not change the output.

The problem is still present in 8.0.27

Suggested fix:
SET @a = 'foxtrot';
SELECT FIND_IN_SET(@a, payload) FROM bug;

should return

6
6
[3 Dec 2021 17:19] MySQL Verification Team 
Hello David Landgren,

Thank you for the report and test case.

regards,
Umesh
[21 Dec 2021 20:26] Jon Stephens 
Documented fix as follows in the MySQL 8.0.29 changelog:

    When searching a SET column value, a user variable used as the
    first argument to FIND_IN_SET() did not produce the same result
    as a literal value. Example: Consider the table t1 created and
    populated as shown here:

CREATE TABLE t1 (c1 SET('a', 'b', 'c', 'd'));

INSERT INTO t1 (c1) VALUES ('a, c, d'), ('c');

    The values shown in var and str should be the same in both 
    rows of the output from the following query, but were not. 

SET @a = 'c';

SELECT FIND_IN_SET(@a, c1) AS var, FIND_IN_SET('c', c1) AS str FROM t1;

    Now we make sure in such cases to return the position of the
    match within the set used for the column definition whether the 
    value sought is a constant or a column value.