MySQL Bugs: #111169: Case Statement in JSON

Bug #111169	Case Statement in JSON_Object
Submitted:	26 May 2023 18:54	Modified:	23 Jun 2023 16:40
Reporter:	Madhav Jaligama	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hi, We are using MySQL 5.7 in QA and Prod mounted on Linux.
We use JSON_OBJECT Case condition in Select Statement. Parameter settings are same in both system.
----------------------
Original SQL statement
----------------------
Original Statement
-------------------
SELECT
    usersListSiteConfigs.*,
    JSON_OBJECT(
        'sms',
        MAX(
            CASE WHEN usersListSiteConfigs.notificationType = 2 THEN usersListSiteConfigs.content
        END
    ),
    'email',
    MAX(
        CASE WHEN usersListSiteConfigs.notificationType = 3 THEN usersListSiteConfigs.content
    END
),
'push',
MAX(
    CASE WHEN usersListSiteConfigs.notificationType = 1 THEN usersListSiteConfigs.content
END
)
) AS notificationContent,

---------------------------------------------
Result in QA and Prod with Original Statement
---------------------------------------------
QA System : {"sms": {"INAPP": "en_you have a new form
Prod System: {"sms": null,

-------------
Observation:
-------------
With the above sql statment, quality system is pulling up correct result, where as production system is not.Basically, we are getting different formats of results for the same query in lower environment & production.

--------------
Investigation:
--------------

When debugged further it seems, the issue is with CASE statement in JSON_OBJECT.

In Quality, this case statement in json_object is working as expected without else, and providing the desired results.

Whereas in prod, this case statement in json_object is not providing desired results. To fix this issue/as workaround, just introducing else condition

in the json_object case statement will provide , the desired results.

-----------------------
Modified SQL statement:
-----------------------

SELECT
    usersListSiteConfigs.*,
    JSON_OBJECT(
        'sms',
        MAX(
            CASE WHEN usersListSiteConfigs.notificationType = 2 THEN usersListSiteConfigs.content
        ELSE '' END
    ),
    'email',
    MAX(
        CASE WHEN usersListSiteConfigs.notificationType = 3 THEN usersListSiteConfigs.content
    ELSE '' END
),
'push',
MAX(
    CASE WHEN usersListSiteConfigs.notificationType = 1 THEN usersListSiteConfigs.content
ELSE '' END
)
) AS notificationContent,

-----------------------------------------
Resut in Prod System with modified Query:
-----------------------------------------

{"sms": "{\"INAPP\": \"en_you have a new form

How to repeat:
Statements as mentione above

Suggested fix:
ELSE ' ' worked in QA and Prod. ELSE NULL works fine in Quality but not in production. Kindly let us know why two systems behave differently.

Hi Mr. Jaligama,

Thank you for your bug report.

However, we can not repeat it. We do not have the entire test case. We need all the data that make this set of statements misbehave. Hence, we need all the tables which produce the wrong results in production.

We can not proceed without data that do not work correctly.

Can't repeat.

Hi,

I would like to schedule a meeting, where I can show the misbehaviour of Case in JSON Object. Kindly let me know your availability tomorrow, so that I can invite you. Please do share your email id.

Thanks & Regards
Madhav

Hi,

This is not how the bugs forum works.

It works with reporters sending repeatable test cases, which we then do not process.

If you would like some form of support, please, read the next comment.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.

Thanks for your email.

We are working on test case to create the set of sql statament, and table information and we will share with you.

Thanks & Regards,
Madhav

Production Global Variables

Attachment: Production_Global_Variables.csv (text/csv), 15.23 KiB.

Table Creation and INsert and Select Statements

Attachment: Jason_Object_Create_sample_Data.sql (application/octet-stream, text), 13.41 KiB.

I have attached the requested details/files. I have clearly mentioned the steps in the attached SQL file . Please let me know if you need any additional details.

Hi Mr. Jaligama,

We did inform you that 5.7 is in the maintenance mode, so that only crashing bugs are fixed.

With the latest 8.0, 8.0.33, we can not repeat your results. This is what we get:

ERROR 1103 (42000) at line 45: Incorrect table name ''
ERROR 1103 (42000) at line 46: Incorrect table name ''
ERROR 1103 (42000) at line 47: Incorrect table name ''
ERROR 1140 (42000) at line 50: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.usersListSiteConfigs.homeSiteId'; this is incompatible with sql_mode=only_full_group_by
ERROR 1140 (42000) at line 124: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.usersListSiteConfigs.homeSiteId'; this is incompatible with sql_mode=only_full_group_by

Can't repeat.

Hi,

We are working on 8.0 system to recreate the issue.

We will get back to you soon.

Thanks & Regards
Madhav

Hi ,

Closing this ticket as no longer required.

Thanks
Madhav

Thanks .......