bug: The define variables were used in the query,then the first returned result is consistent, but the second returned result is different.

[haitaoguan]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

Note: Whether in InnoDB or Tianmu, the result set of the first execution is consistent, but the result set of the second execution of the two engines is different.

SELECT a.CURRENCY_ID, a.account_id, sum(start_amount) start_amount
  FROM (SELECT a.CURRENCY_ID,
               a.account_id,
               a.balance - sum(CASE
                                 WHEN b.in_amount IS NULL THEN
                                  0
                                 ELSE
                                  b.in_amount
                               END) + sum(CASE
                                            WHEN b.out_amount IS NULL THEN
                                             0
                                            ELSE
                                             b.out_amount
                                          END) start_amount
          FROM (SELECT result.*
                  FROM (SELECT a.*,
                               @rownum1 := @rownum1 + 1 inde,
                               IF(@pxydm1 = a.account_id,
                                  @rankno1 := @rankno1 + 1,
                                  @rankno1 := 1) AS rankno,
                               @pxydm1 := a.account_id
                          FROM (SELECT b.CURRENCY_ID,
                                       a.account_id,
                                       a.fiscal_date,
                                       a.balance
                                  FROM C1AM_ACCT_DAY a, C1MD_BANK_ACCT b
                                 WHERE a.deleted_flag = '0'
                                   AND b.deleted_flag = '0'
                                   AND a.account_id = b.ROW_ID
                                 ORDER BY a.account_id, a.fiscal_date) a) result
                 WHERE result.rankno = 1) a
          LEFT JOIN C1AM_BANK_LIST b
            ON b.deleted_flag = '0'
           AND b.account_id = a.account_id
           AND b.fiscal_date = a.fiscal_date
         GROUP BY a.account_id, a.balance, a.CURRENCY_ID) a
 GROUP BY a.account_id, a.CURRENCY_ID
 order by a.account_id

Expected behavior

No response

How To Reproduce

No response

Environment

./mysqld Ver 5.7.36-StoneDB-v1.0.1 for Linux on x86_64 (build-) build information as follow: Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev Branch name: stonedb-5.7-dev Last commit ID: f180323 Last commit time: Date: Wed Jun 14 15:44:47 2023 +0800 Build time: Date: Thu Jun 15 06:37:29 UTC 2023

Are you interested in submitting a PR to solve the problem?

• [X] Yes, I will!

[Double0101]

The problam derive from following subquery.

 SELECT 
   result.* 
 FROM (
     SELECT 
        a.*, 
        @rownum1 := @rownum1 + 1 inde,
        IF(@pxydm1 = a.account_id,@rankno1 := @rankno1 + 1,@rankno1 := 1) AS rankno,
        @pxydm1 := a.account_id 
     FROM (
         SELECT 
            b.CURRENCY_ID,
            a.account_id,
            a.fiscal_date,
            a.balance
         FROM 
            c1am_acct_day a, c1md_bank_acct b
         WHERE a.deleted_flag = '0'
                AND b.deleted_flag = '0'
                AND a.account_id = b.ROW_ID
         ORDER BY a.account_id, a.fiscal_date) a) result
WHERE result.rankno = 1;

Execute this sql and you can see T:-5 = TMP_TABLE(T:4294967292) in CompliedQuery. In the materialize() function when create tmptable, the value of elements will be calculated column by column. We can see it in the TempTable::FillMaterializedBuffers function in tmp_table_low.cpp

    utils::result_set<void> res;
    for (uint i = 1; i < attrs.size(); i++) {
      if (!skip_parafilloutput[i]) {
        res.insert(eng->query_thread_pool.add_task(&TempTable::FillbufferTask, this, attrs[i], current_txn_,
                                                   &page_start, start_row, page_end));
      }
    }
    res.get_all_with_except();

    for (uint i = 1; i < attrs.size(); i++)
      if (skip_parafilloutput[i])
        FillbufferTask(attrs[i], current_txn_, &page_start, start_row, page_end);

The value of rankno depend on value @pxydm1. But when we calculate the rankno the @pxydm1 is out of control. So we get the incorrect rankno. And we use the condition result.rankno = 1, the result absolute wrong.