bug: The outermost query returned wrong result.

[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

SELECT a.*
  FROM (SELECT '合计' total, a.CURRENCY_ID, 'aaaa' inner_code
          FROM (SELECT b.CURRENCY_ID, a.account_id, a.fiscal_date, a.balance
                  FROM C1AM_ACCT_DAY a, C1MD_BANK_ACCT b
                 WHERE a.account_id = b.ROW_ID) a
          JOIN C1MD_BANK_ACCT b
            ON b.row_id = a.account_id
          JOIN C1MD_COMPANY c
            ON c.row_id = b.company_id
         WHERE 1 = 1
           AND c.row_id IN (SELECT t1.row_id
                              FROM C1MD_COMPANY t1, C1MD_COMPANY t2
                             WHERE t1.sys_id = t2.sys_id)
         GROUP BY a.CURRENCY_ID) a
+-------+-------------+------------+
| total | CURRENCY_ID | inner_code |
+-------+-------------+------------+
|       |          -1 |            |
+-------+-------------+------------+

Expected behavior

+--------+-------------+------------+
| total  | CURRENCY_ID | inner_code |
+--------+-------------+------------+
| 合计   |           1 | aaaa       |
| 合计   |           5 | aaaa       |
| 合计   |          13 | aaaa       |
+--------+-------------+------------+

How To Reproduce

No response

Environment

./mysqld Ver 5.7.36-StoneDB-v1.0.4 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: 7321c63 Last commit time: Date: Wed Jul 5 17:01:29 2023 +0800 Build time: Date: Thu Jul 6 01:48:21 UTC 2023

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

• [X] Yes, I will!

[haitaoguan]

SELECT '合计' total, a.CURRENCY_ID, 'aaaa' inner_code
          FROM (SELECT b.CURRENCY_ID, a.account_id, a.fiscal_date, a.balance
                  FROM C1AM_ACCT_DAY a, C1MD_BANK_ACCT b
                 WHERE a.account_id = b.ROW_ID) a
          JOIN C1MD_BANK_ACCT b
            ON b.row_id = a.account_id
          JOIN C1MD_COMPANY c
            ON c.row_id = b.company_id
         WHERE 1 = 1
           AND c.row_id IN (SELECT t1.row_id
                              FROM C1MD_COMPANY t1, C1MD_COMPANY t2
                             WHERE t1.sys_id = t2.sys_id)
         GROUP BY a.CURRENCY_ID;
+--------+-------------+------------+
| total  | CURRENCY_ID | inner_code |
+--------+-------------+------------+
| 合计   |           1 | aaaa       |
| 合计   |           5 | aaaa       |
| 合计   |          13 | aaaa       |
+--------+-------------+------------+

[Double0101]

Remove the condition where-in, result is ok. The problem derive from where-in.

mysql> SELECT a.*
    ->   FROM (SELECT '合计' total, a.CURRENCY_ID, 'aaaa' inner_code
    ->           FROM (SELECT b.CURRENCY_ID, a.account_id, a.fiscal_date, a.balance
    ->                   FROM c1am_acct_day a, c1md_bank_acct b
    ->                  WHERE a.account_id = b.ROW_ID) a
    ->           JOIN c1md_bank_acct b
    ->             ON b.row_id = a.account_id
    ->           JOIN c1md_company c
    ->             ON c.row_id = b.company_id
    -> GROUP BY a.CURRENCY_ID) a;
+--------+-------------+------------+
| total  | CURRENCY_ID | inner_code |
+--------+-------------+------------+
| 合计   |           1 | aaaa       |
| 合计   |           5 | aaaa       |
| 合计   |          13 | aaaa       |
+--------+-------------+------------+
3 rows in set, 1 warning (11.28 sec)