| Bug #115296 | suggestion for query plan display of ref | ||
|---|---|---|---|
| Submitted: | 12 Jun 9:38 | Modified: | 13 Jun 12:49 |
| Reporter: | ximin liang | 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 7:39]
MySQL Verification Team
Hello ximin liang, Thank you for the enhancement request. regards, Umesh
[13 Jun 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.
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)