Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24767

Wrong result when forced BNLH is used for join supported by compound index

    XMLWordPrintable

Details

    Description

      SET JOIN_CACHE_LEVEL=4
      CREATE TABLE `t1` (
      `bill_no` bigint(20) not NULL,
      `resource_id` bigint(20) not NULL,
      `item_code` int(11) not NULL,
      `acct_id` bigint(20) not NULL
      ) ENGINE=innodb ;

      CREATE TABLE `t2` (
      `ACCT_ID` bigint(20) NOT NULL,
      `RESOURCE_ID` bigint(20) NOT NULL,
      `BILL_NO` bigint(20) NOT NULL,
      `ITEM_CODE` int(11) NOT NULL,
      `BILL_FEE` bigint(20) NOT NULL,
      PRIMARY KEY (`ACCT_ID`,`RESOURCE_ID`,`BILL_NO`,`ITEM_CODE`,`BILL_FEE`)
      ) ENGINE=innodb DEFAULT CHARSET=utf8 ;
      insert into t1 values(200003728758091,1000040460,1314831,10000026910);
      insert into t2 values(10000026910,1000040460,200003728758091,1314831,100);

      SELECT a.bill_no, b.bill_no,a.item_code, b.item_code ,a.acct_id, b.acct_id, a.resource_id,b.resource_id FROM t2 a , t1 b where a.bill_no = b.bill_no and a.item_code = b.item_code AND a.acct_id = b.acct_id AND a.resource_id = b.resource_id limit 1;

      Empty set (0.004 sec)

      SELECT a.bill_no, b.bill_no,a.item_code, b.item_code ,a.acct_id, b.acct_id, a.resource_id,b.resource_id FROM t2 a , t1 b where a.bill_no = b.bill_no and a.item_code = b.item_code AND a.resource_id = b.resource_id limit 1;

      bill_no bill_no item_code item_code acct_id acct_id resource_id resource_id
      200003728758091 200003728758091 1314831 1314831 10000026910 10000026910 1000040460 1000040460

      Attachments

        Activity

          People

            igor Igor Babaev
            cstarc chu huaxing
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.