Fix EST.ROWS drift from 5 to 6 for MERGE OUTER JOIN on varchar↔int equality condition
Open
Nobody has claimed this yet.
dima
type: bug
- Dominant language
- C++
- Stars
- 2.9k
- Forks
- 339
- Avg merge
- 1d 19h
- Merged PRs (30d)
- 52
Description
Description
In a MERGE OUTER JOIN scenario with an equality condition involving varchar ↔ int type conversion, the estimated row count (EST.ROWS) drifts between versions from 5 to 6.
Actual Result:

Expected Result:

Steps to Reproduce
- Access the machine.
- Navigate to the directory:
/obdata/data/AndroidTest/ob_opensource_test/tools/deploy.

- Execute the following commands to set up the test environment and run the specific test case:
export OCEANBASE_HOME=/obdata/data/AndroidTest/ob_opensource_test/ cd ${OCEANBASE_HOME}/tools/deploy source activate_obd.sh export OBD_SEEKDB_STATUS_FALLBACK_TCP=1 obd test mysqltest test_env --mysqltest-bin=${OCEANBASE_HOME}/deps/3rd/u01/obclient/bin/mysqltest --obclient-bin=${OCEANBASE_HOME}/deps/3rd/u01/obclient/bin/obclient --init-only --init-sql-dir=${OCEANBASE_HOME}/tools/deploy --init-sql-files='init.sql,init_user.sql|root@sys|test' obd test mysqltest test_env --mysqltest-bin=${OCEANBASE_HOME}/deps/3rd/u01/obclient/bin/mysqltest --obclient-bin=${OCEANBASE_HOME}/deps/3rd/u01/obclient/bin/obclient --disable-reboot --test-set=merge_join.mj_join_merge - Alternatively, the issue can be reproduced by running the following SQL, which corresponds to the
## bug:section (t2/t7 + RIGHT JOIN EXPLAIN) in themerge_join.mj_join_mergetest case:-- Consistent with the `## bug:` section t2/t7 + RIGHT JOIN EXPLAIN in merge_join.mj_join_merge USE test; SET @@session.explicit_defaults_for_timestamp = OFF; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t7; CREATE TABLE t2(a INT, b VARCHAR(20), c DECIMAL(20,10)); CREATE TABLE t7( a INT(10), b VARCHAR(10), c CHAR(20), d DECIMAL(20,10) ); INSERT INTO t2 VALUES (1, '222', 3.33), (2, '222', 3.33), (2, '333', 2.22), (3, '3333', 1.11), (5, '555', 5.55), (6, '6666', 5.5), (1, '1', 1), (1, '2', 2), (2, '2.00', 2.00001), (3, '3.0', 3.00001), (4, 'A', 4.33), (5, 'B', 5), (6, 'AB', 6); INSERT INTO t7 VALUES (65, '222', '333', 5), (65, '444', '5.550', 1), (66, '3.0', '555', 3.00001), (66, '2', 'B', 2), (67, 'A', '1', 1); -- Comparison point: EST.ROWS for the top-level SORT / MERGE OUTER JOIN (baseline is 5, some versions show 6) EXPLAIN SELECT /*+ use_merge(t2, t7) */ * FROM t2 RIGHT JOIN t7 ON t2.b = t7.a AND t2.c = t7.d ORDER BY t2.a, t2.b, t7.a, t7.b;
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the discrepancy with the merge_join.mj_join_merge test case or the supplied SQL and compare the top-level SORT/MERGE OUTER JOIN EST.ROWS. Read the ## bug: section for the t2/t7 RIGHT JOIN EXPLAIN case, then trace the estimate behavior in the relevant query-optimizer code. Done means the estimate consistently matches the expected baseline of 5.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100