Bug #119088 sort with limit in hypergraph gets wrong result

• Submitted: 28 Sep 6:31
• Reporter: Malt Chen
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.2,9.4
• OS: Any
• CPU Architecture: Any

[28 Sep 6:31] Malt Chen

Description:
I got the wrong result when 'order by limit' in hypergraph.

When creating table use 'utf8mb4_general_ci' character, it will happen. The result lost the 'limit' and get redundant data.

How to repeat:
set sql_mode = '';
CREATE TABLE `t1` (
  `a` char(0) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
insert into t1 values(0),(0);

# In hypergraph, it will get 2 rows, 'limit 1' does not effective
set optimizer_switch='hypergraph_optimizer=on';
select * from t1 order by a limit 1;

# In non-hypergraph, an extra limit AccessPath on Sort AccessPath, it will get 1 row, result is ok.
set optimizer_switch='hypergraph_optimizer=off';
select * from t1 order by a limit 1;