MySQL JSON_EXTRACT subquery not working when enclosed in IFNULL

Question

Goal
Select a value based on a value returned by a subquery that is using JSON_EXTRACT which either returns a 'value' or NULL. IFNULL should be able to allow you to set a default value when the JSON_EXTRACT does not return a result.

Problem
When using a subquery that uses JSON_EXTRACT and returns a result, will return nothing when enclosed in IFNULL ignoring the default value.

Consider the following case
We want to select a SubscriptionPlan.name based on an identifier that is the result of a subquery using JSON_EXTRACT.

SELECT 
    subscriptionplan.name
FROM
    SubscriptionPlan AS subscriptionplan
WHERE
    subscriptionplan.identifier = (
                        SELECT 
                            JSON_EXTRACT(product.data, '$.identifier')
                        FROM
                            Subscription AS subscription
                        JOIN
                            Product AS product ON product.productid = subscription.productid
                        WHERE
                            subscription.status = 'ACTIVE'
                        AND
                            subscription.ownerid = :userId
                    )

Case 1. SUCCESS without IFNULL

Subscription exists with status 'ACTIVE' and 'userId'
- Subquery result: 'PRO' and thus finds the SubscriptionPlan
- Final result: 'Professional'

Case 2. NOT FOUND without IFNULL

Subscription not found with status 'ACTIVE' and 'userId'
- Subquery result: NULL and thus does not find a SubscriptionPlan
- Final result: NULL

Now we add the IFNULL operation to default to 'FREE' subscription plan:

subscriptionplan.identifier = IFNULL(
                                  ( SELECT JSON_EXTRACT ... ),
                                  'FREE'
                              )

Case 3. SUCCESS with IFNULL

Subscription exists with status 'ACTIVE' and 'userId'
 - Subquery result: NULL even though the subscription was found !???
 - Final result: NULL

Case 4. NOT FOUND with IFNULL

Subscription not found with status 'ACTIVE' and 'userId'
 - Subquery result: FREE and thus finds the FREE SubscriptionPlan
 - Final result: 'Free'

The IFNULL expression nullifies the subquery result, and it does not default to 'FREE'.

My thoughts are as follows:

• Case 4: is using the IFNULL default value string 'FREE' and therefore works as intended
• Case 3: subquery should return PRO and even if it returns NULL, it should default to 'FREE', neither happens
• Maybe adding the IFNULL query adds another nesting layer where

What i've tried and did not work:

• IF( () IS NULL, 'do stuff', 'do other stuff')
• IF( ISNULL(<query<), 'do stuff', 'do other stuff')
• IF( () = null , ..., ...)
• IF( () = NULL , ..., ...)
• IF( () = 'null' , ..., ...)
• IF( () = 'NULL' , ..., ...)

Also according to Can't detect null value from JSON_EXTRACT:

• IF ( JSON_EXTRACT(product.data, '$.identifier') = CAST('null' AS JSON), ...., ... )

All failures! Why is JSON_EXTRACT subquery not working when enclosed in IFNULL?

Answer 1

I've found out what caused the error.

JSON_EXTRACT adds quotes around its result resulting in "PRO" instead of PRO and therefore the IFNULL default is not triggered and the subscription plan, which is PRO, is not found using "PRO".

Add JSON_UNQUOTE around the JSON_EXTRACT solves the issue.