oceanbase / oceanbase/seekdb

Fix EST.ROWS drift from 5 to 6 for MERGE OUTER JOIN on varchar↔int equality condition

Open
#755 1 comment 0 reactions 0 assignees View on GitHub

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 varcharint type conversion, the estimated row count (EST.ROWS) drifts between versions from 5 to 6.

Actual Result:
Actual Result

Expected Result:
Expected Result

Steps to Reproduce

  1. Access the machine.
  2. Navigate to the directory: /obdata/data/AndroidTest/ob_opensource_test/tools/deploy.
    Directory
  3. 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
    
  4. Alternatively, the issue can be reproduced by running the following SQL, which corresponds to the ## bug: section (t2/t7 + RIGHT JOIN EXPLAIN) in the merge_join.mj_join_merge test 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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.