From 31683648a16eb62b8ff312a2fd2a35e2d064a098 Mon Sep 17 00:00:00 2001 From: ximinliang Date: Wed, 12 Jun 2024 17:48:25 +0800 Subject: [PATCH] Bug #115296 and #115330 Fix display of Ref using "<=>" and display of access path `unqualified_count` --- mysql-test/r/explain_tree.result | 24 ++++++++++++++++++++++- mysql-test/t/explain_tree.test | 16 +++++++++++++++ sql/join_optimizer/explain_access_path.cc | 6 +++++- sql/sql_executor.cc | 6 +++++- 4 files changed, 49 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/explain_tree.result b/mysql-test/r/explain_tree.result index 8f9da5443cd1..e314dec28386 100644 --- a/mysql-test/r/explain_tree.result +++ b/mysql-test/r/explain_tree.result @@ -882,7 +882,7 @@ EXPLAIN -> Limit: 1 row(s) (rows=1) -> Filter: (outer_field_is_not_null, (d.a), true) (rows=1) -> Alternative plans for IN subquery: Index lookup unless a IS NULL (rows=4) - -> Covering index lookup on d using (a=(t1.a) or NULL) (rows=4) + -> Covering index lookup on d using (a<=>(t1.a) or NULL) (rows=4) -> Materialize (rows=1) -> Table scan on t1 (rows=1) -> Table scan on d (rows=2) @@ -1329,3 +1329,25 @@ EXPLAIN Warnings: Note 1276 Field or reference 'test.x1.a' of SELECT #2 was resolved in SELECT #1 DROP TABLE t1; +# +# Bug#115296 Fix display of Ref using "<=>" +# Bug#115330 Fix display of access path `unqualified_count` +# +CREATE TABLE t1 ( c1 int DEFAULT NULL, c2 int DEFAULT NULL, KEY idx (c1) ); +CREATE TABLE t2 LIKE t1; +CREATE TABLE t3 LIKE t1; +EXPLAIN FORMAT = tree SELECT c1 FROM t1 WHERE c1 IS NULL; +EXPLAIN +-> 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; +EXPLAIN +-> Filter: (t1.c1 <=> NULL) (cost=0.35 rows=1) + -> Covering index lookup on t1 using idx (c1<=>NULL) (cost=0.35 rows=1) + +EXPLAIN FORMAT = tree SELECT count(*) FROM t1, t2, t3; +EXPLAIN +-> Count rows in t1, t2, t3 + +DROP TABLE t1, t2, t3; diff --git a/mysql-test/t/explain_tree.test b/mysql-test/t/explain_tree.test index 5c88d3f62faf..29b262d50f8f 100644 --- a/mysql-test/t/explain_tree.test +++ b/mysql-test/t/explain_tree.test @@ -841,3 +841,19 @@ EXPLAIN FORMAT=TREE SELECT LAST_VALUE((SELECT x1.a FROM t1)) OVER (PARTITION BY b) FROM t1 x1; DROP TABLE t1; + +--echo # +--echo # Bug#115296 Fix display of Ref using "<=>" +--echo # Bug#115330 Fix display of access path `unqualified_count` +--echo # + +CREATE TABLE t1 ( c1 int DEFAULT NULL, c2 int DEFAULT NULL, KEY idx (c1) ); +CREATE TABLE t2 LIKE t1; +CREATE TABLE t3 LIKE t1; + +EXPLAIN FORMAT = tree SELECT c1 FROM t1 WHERE c1 IS NULL; +EXPLAIN FORMAT = tree SELECT c1 FROM t1 WHERE c1 <=> NULL; +EXPLAIN FORMAT = tree SELECT count(*) FROM t1, t2, t3; + +DROP TABLE t1, t2, t3; + diff --git a/sql/join_optimizer/explain_access_path.cc b/sql/join_optimizer/explain_access_path.cc index 2a76d92a740b..5273f82a5f5b 100644 --- a/sql/join_optimizer/explain_access_path.cc +++ b/sql/join_optimizer/explain_access_path.cc @@ -1400,7 +1400,11 @@ static unique_ptr SetObjectMembers( case AccessPath::UNQUALIFIED_COUNT: error |= AddMemberToObject(obj, "access_type", "count_rows"); error |= AddTableInfoToObject(obj, join->qep_tab->table()); - description = "Count rows in " + string(join->qep_tab->table()->alias); + description = "Count rows in "; + for (uint i = 0; i < join->primary_tables; i++) { + description += string(join->qep_tab[i].table()->alias); + if (i + 1 != join->primary_tables) description += string(", "); + } break; case AccessPath::NESTED_LOOP_JOIN: { string join_type = JoinTypeToString(path->nested_loop_join().join_type); diff --git a/sql/sql_executor.cc b/sql/sql_executor.cc index ff31b7959936..052ae9ed2390 100644 --- a/sql/sql_executor.cc +++ b/sql/sql_executor.cc @@ -164,7 +164,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