| Bug #115296 | suggestion for query plan display of ref | ||
|---|---|---|---|
| Submitted: | 12 Jun 2024 9:38 | Modified: | 13 Jun 2024 12:49 |
| Reporter: | ximin liang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 8.4.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Jun 2024 7:39]
MySQL Verification Team
Hello ximin liang, Thank you for the enhancement request. regards, Umesh
[13 Jun 2024 7:40]
MySQL Verification Team
Thank you very much for your patch contribution, we appreciate it! In order for us to continue the process of reviewing your contribution to MySQL, please send us a signed copy of the Oracle Contributor Agreement (OCA) as outlined in https://oca.opensource.oracle.com Signing an OCA needs to be done only once and it's valid for all other Oracle governed Open Source projects as well. Getting a signed/approved OCA on file will help us facilitate your contribution - this one, and others in the future. Please let me know, if you have any questions. Thank you for your interest in MySQL.
[30 Sep 2024 16:36]
OCA Admin
Contribution submitted via Github - Bug #115296 and #115330 (*) Contribution by ximin liang (Github liangximin2046, mysql-server/pull/565#issuecomment-2380375145): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it
Contribution: git_patch_2094034636.txt (text/plain), 4.55 KiB.
Description: Hello mysql team: Here is a suggestion for query plan display of ref. Maybe not important but provided for your consideration. How to repeat: Follow sqls can be repeated in MySQL 8.4.0, notice that all index lookup displays c1=NULL CREATE TABLE `t1` ( `c1` int DEFAULT NULL, `c2` int DEFAULT NULL, KEY `idx` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; explain format=tree select c1 from t1 where c1 is null; -> Filter: (t1.c1 is null) (cost=0.35 rows=1) -> Covering index lookup on t1 using idx (c1=NULL) (cost=0.35 rows=1) explain format=tree select c1 from t1 where c1 <=> null; -> Filter: (t1.c1 <=> NULL) (cost=0.35 rows=1) -> Covering index lookup on t1 using idx (c1=NULL) (cost=0.35 rows=1) Suggested fix: It is about func `RefToString`, all the conds for deparsed as '='. May be null_rejecting attribute of struct Index_lookup should be considered. Here is my test patch: @@ -163,7 +163,11 @@ string RefToString(const Index_lookup &ref, const KEY &key, assert(!field->is_hidden_by_system()); ret += field->field_name; } - ret += "="; + if (ref.items[key_part_idx]->is_nullable() && + ((ref.null_rejecting & ((key_part_map)1 << key_part_idx)) == 0)) + ret += "<=>"; + else + ret += "="; ret += ItemToString(ref.items[key_part_idx]); // If we have ref_or_null access, find out if this keypart is the one that result: explain format=tree select c1 from t1 where c1 <=> null; -> Filter: (t1.c1 <=> NULL) (cost=0.35 rows=1) -> Covering index lookup on t1 using idx (c1<=>NULL) (cost=0.35 rows=1)