MySQL SELECT IF with CTE not behaving as intended?

Question

I have a complex MySQL SELECT statement with a LOT of CTE frontloading. Note it works just fine, but it seems MySQL is doing something strange with the SELECT IF statement.

So the issue is our stored procedure has the ability to return just an array of IDs rather than the whole JSON structure. The problem is when I have p_array_only as TRUE, it takes forever and seems to be executing/comparing the get_json_data_for_id, which makes absolutely no sense to me. Below is the SQL:

WITH lots_of_data AS ( SELECT ID FROM LOTS OF DATA),
lots_of_data2 AS ( SELECT ID FROM LOTS OF DATA2),
lots_of_data3 AS ( SELECT ID FROM LOTS OF DATA3)

    SELECT 
    IF
    (
        p_array_only,
        JSON_ARRAYAGG(ccd.ID),        
        JSON_OBJECT
        (
            'data',  JSON_ARRAYAGG(get_json_data_for_id(ccd.ID))
        ) 
    )
    INTO var_JSON
    FROM 
    (            
        SELECT fs.ID
        FROM  final_sort fs
        LIMIT var_offset, var_rowcount
    ) ccd;

Now if I just say (with the CTE intact as above):

SELECT 
    JSON_ARRAYAGG(ccd.ID)    
INTO var_JSON
FROM 
(            
    SELECT fs.ID
    FROM  final_sort fs
    LIMIT var_offset, var_rowcount
) ccd;

It works flawlessly (and in < 1 second).

So what's going on here? Why is MySQL not just executing the JSON_ARRAYAGG(ccd.ID) code when p_array_only is true?

Answer 1

So I found out the way to do this is to embed within the initial CTE SELECT:

SELECT IF 
    (
        p_array_only,
        (
            SELECT JSON_ARRAYAGG(ccd.ID)        
            FROM 
            (            
                SELECT fs.ID
                FROM  final_sort fs
                LIMIT var_offset, var_rowcount
            ) ccd
        ),        
        (
            SELECT JSON_OBJECT
            (
                'data',  JSON_ARRAYAGG(get_json_data_for_id(ccd.ID))
            ) 
            FROM 
            (            
                SELECT fs.ID
                FROM  final_sort fs
                LIMIT var_offset, var_rowcount
            ) ccd
        )
    )
    INTO var_JSON;

Solved the problem!