| Bug #113350 | SELECT contain NULL values，not return result | ||
|---|---|---|---|
| Submitted: | 6 Dec 2023 9:58 | Modified: | 6 Dec 2023 11:09 |
| Reporter: | HULONG CUI | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.26 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[6 Dec 2023 11:09]
MySQL Verification Team
Hi Mr. CUI, Thank you for your bug report. However, this is not a bug. We ran your test case and we have got the following results: ------------------------------------------ id parent_role_id 15 0 roleId ( SELECT @r := parent_role_id FROM cms_role WHERE id = parent_role_id ) 15 NULL Level Code Message Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. ------------------------------------------ Our Reference clearly states that the evaluation of fields in the SELECT list is NOT guaranteed. That is how SQL Standard stipulates handling of user variables. However, if you follow the standard and initalize the local variable (which is what our warnings have clearly instructed you to do, you get the following output: ------------------------------------- id parent_role_id 15 0 roleId ( SELECT parent_role_id FROM cms_role WHERE id = parent_role_id ) 1 NULL ------------------------------------ Simply, when writing query follow SQL standard and our Reference Manual. Also, show all warnings after each SQL statement. Not a bug.
Description: At mysql8.0.26 version ,when select contain column values NULL have no result。 mysql> SELECT @r AS roleId, ( SELECT @r := parent_role_id FROM cms_role WHERE id = parent_role_id ) FROM ( SELECT @r := 15 ) vars, cms_role h WHERE @r != 0; Query OK, 0 rows affected (0.02 sec) At mysql5.7.44 version ,when select contain column values NULL have result。 mysql> SELECT @r AS roleId, -> ( SELECT @r := parent_role_id FROM cms_role WHERE id = parent_role_id ) -> FROM ( SELECT @r := 15 ) vars, cms_role h -> WHERE @r != 0; +--------+---------------------------------------------------------------------+ | roleId | ( SELECT @r := parent_role_id FROM cms_role WHERE id = parent_role_id ) | +--------+---------------------------------------------------------------------+ | 15 | NULL | +--------+---------------------------------------------------------------------+ I think this is bug.At sql when “SELECT @r := parent_role_id FROM cms_role WHERE id = parent_role_id” no result ，multi select result is NULL。 For details please see repeat。 How to repeat: Server version: 8.0.26 MySQL Community Server - GPL CREATE TABLE cms_role ( id int NOT NULL AUTO_INCREMENT, name varchar(100) , org_id int NOT NULL , parent_role_id int DEFAULT 0, note varchar(1000) , is_delete tinyint DEFAULT 0, create_by bigint , create_time datetime DEFAULT CURRENT_TIMESTAMP, update_by bigint DEFAULT NULL , update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY (id) ); INSERT INTO cms_role(id,org_id,parent_role_id) values(15,1,0); root@localhost: 17:28: [demo]> select id,parent_role_id from cms_role where id=15; +----+----------------+ | id | parent_role_id | +----+----------------+ | 15 | 0 | +----+----------------+ 1 row in set (0.00 sec) root@localhost: 17:28: [demo]> SELECT parent_role_id FROM cms_role WHERE id = parent_role_id; Empty set (0.00 sec) mysql> SELECT @r AS roleId, ( SELECT @r := parent_role_id FROM cms_role WHERE id = parent_role_id ) FROM ( SELECT @r := 15 ) vars, cms_role h WHERE @r != 0; Empty set, 2 warnings (0.01 sec)