bug: Order by leads to wrong results and performance degradation.

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

###tianmu
SELECT A.company_id, IFNULL(B.short_name, B.company_name) company_name
  FROM (SELECT B.company_id, 1 sort_no FROM C1FG_PL_NODE B) A
  LEFT JOIN C1MD_COMPANY B
    ON A.company_id = B.row_id
 ORDER BY A.company_id;
+------------------+--------------------+
| company_id       | company_name       |
+------------------+--------------------+
| 3000000000027247 | 深圳KRYK       |
| 3000000000027248 | 深圳KRYK       |
| 3000000000027249 | 深圳KRYK       |
+------------------+--------------------+
3 rows in set (0.97 sec)

exec_time=0.97s

###InnoDB
+------------------+--------------------+
| company_id       | company_name       |
+------------------+--------------------+
| 3000000000027247 | 集团本部           |
| 3000000000027248 | 南京KRYS       |
| 3000000000027249 | 深圳KRYK       |
+------------------+--------------------+
3 rows in set (0.00 sec)

exec_time=0.00s

Expected behavior

No response

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: 2bb2d48 Last commit time: Date: Thu Jul 13 10:04:56 2023 +0800 Build time: Date: Thu Jul 13 02:11:43 UTC 2023

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

[X] Yes, I will!

In temp_table_low.cpp, the value max_length set incorrect. In some expression column, there are more than one VarMap in var_map, the value max_length will minus (actual_size - precision) more than one times, then get a negative number.

void TempTable::VerifyAttrsSizes()  // verifies attr[i].field_size basing on the current multiindex contents
{
  for (uint i = 0; i < attrs.size(); i++) {
    ......
      int max_length = attrs[i]->term.vc->MaxStringSize();
      if (dynamic_cast<vcolumn::ExpressionColumn *>(vc)) {
        auto &var_map = dynamic_cast<vcolumn::ExpressionColumn *>(vc)->GetVarMap();
        for (auto &it : var_map) {
          PhysicalColumn *column = it.GetTabPtr()->GetColumn(it.col_ndx);
          ColumnType ct = column->Type();
          uint precision = ct.GetPrecision();
          if (precision >= STRING_LENGTH_THRESHOLD) {
            uint actual_size = column->MaxStringSize() * ct.GetCollation().collation->mbmaxlen;
            if (actual_size < precision)
              max_length += (actual_size - precision);
          }
        }
      }
      attrs[i]->OverrideStringSize(max_length);
    }
  }
}

stoneatom / stonedb