Why Mycat Fails and DBLE Succeeds in Global Table Left Join with Sharded Tables

Author: Guo Aomen, DBLE R&D member responsible for distributed database middleware development.

Problem: A community member asked whether a left join between a global table and a sharded table is supported, noting that Mycat produced incorrect results.

Scenario reproduction: A global table and a sharded table are created, each with two shard nodes. The global table has identical data on both nodes, while the sharded table stores rows with id=1,2 on one node and id=5000001 on another. The left join is performed on a.id and b.code .

Mycat implementation result (incorrect):

DBLE implementation result (correct):

Result analysis: Mycat forwards the original SQL to all shard instances and simply unions the results, causing duplicate rows because the global table exists on every node. This naive UNION ALL merging leads to incorrect data.

Mycat execution plan analysis (illustrated): The plan shows that Mycat sends the query unchanged to all configured instances and merges results by simple accumulation, which is flawed for global‑table joins.

DBLE execution plan analysis (illustrated): DBLE distinguishes the global table (sent to a single instance) from the sharded table (sent to all relevant shards) and merges results with a more sophisticated algorithm, ensuring correctness.

Speculation on future improvements: The article mentions a hypothetical "X algorithm" that could accurately merge results from all nodes, potentially improving performance, but such an algorithm is not yet proven.