bug: 当存在decimal数据类型时且派生表中包含union all的查询语句不支持

[haitaoguan]

create table t1(c1 decimal(5,2));
insert into t1 values(111.22);

create table t2(id int);
insert into t2 values(11122);

select xxx.x from (select c1 as x from t1 union all select 1 as x from t2) xxx;
ERROR 1105 (HY000): The query includes syntax that is not supported by the storage engine. Tianmu: UNION of non-matching columns (column no 0) .

SELECT a.start_amount
  FROM (SELECT a.balance start_amount
          FROM (SELECT a.account_id, a.balance
                  FROM C1AM_ACCT_DAY a, C1MD_BANK_ACCT b
                 WHERE a.account_id = b.ROW_ID) a
          LEFT JOIN C1AM_BANK_LIST b
            on b.account_id = a.account_id
        UNION ALL
        SELECT 0 start_amount
          FROM C1AM_BANK_LIST a, C1MD_BANK_ACCT b
         WHERE a.account_id = b.ROW_ID) a
ERROR 1105 (HY000): The query includes syntax that is not supported by the storage engine. Tianmu: UNION of non-matching columns (column no 0) .

[haitaoguan]

SELECT a.start_amount
  FROM (SELECT a.balance start_amount
          FROM (SELECT a.account_id, a.balance
                  FROM C1AM_ACCT_DAY a, C1MD_BANK_ACCT b
                 WHERE a.account_id = b.ROW_ID) a
          LEFT JOIN C1AM_BANK_LIST b
            on b.account_id = a.account_id
        UNION ALL
        SELECT 1.00 start_amount
          FROM C1AM_BANK_LIST a, C1MD_BANK_ACCT b
         WHERE a.account_id = b.ROW_ID) a

......
|   469026770.70 |
|   469047549.00 |
|   469047387.53 |
|   469046299.18 |
|   469043774.80 |
......

[adofsauron]

如果是客户POC场景的问题，直接提成BUG单高优先级处理客户问题。非客户场景，转为feature

[adofsauron]

好，那就先搞这个

[adofsauron]

是的，问题出在decimal这个数据类型上，把decimal给换成其他类型就可以了

[adofsauron]

Decimal: sign: 0 intg: 1 frac: 0 { 000000000 }
Record: 80 00 00 00 00 00 00 00 00

[adofsauron]

Decimal: sign: 0 intg: 3 frac: 2 { 000000111, 220000000 }
Record: 80 00 00 00 00 00 00 6F 16

[adofsauron]

对派生表的临时表中的decimal属性做记录

$10 = (Field_new_decimal) {
  <Field_num> = {
    <Field> = {
      <Proto_field> = {
        _vptr.Proto_field = 0x433dbd8 <vtable for Field_new_decimal+16>
      }, 
      members of Field: 
      ptr = 0x7fc1f401b039 "\200", 
      m_null_ptr = 0x7fc1f401b038 "\377\200", 
      m_is_tmp_nullable = false, 
      m_is_tmp_null = false, 
      m_count_cuted_fields_saved = (CHECK_FIELD_WARN | CHECK_FIELD_ERROR_FOR_NULL | unknown: 2408550284), 
      table = 0x7fc1f49da580, 
      orig_table = 0x7fc1f49da580, 
      table_name = 0x7fc1f49da670, 
      field_name = 0x7fc1f4006a10 "x", 
      comment = {
        str = 0x326f0f9 "", 
        length = 0
      }, 
      key_start = {
        map = 0
      }, 
      part_of_key = {
        map = 0
      }, 
      part_of_sortkey = {
        map = 0
      }, 
      part_of_key_not_extended = {
        map = 0
      }, 
      unireg_check = Field::NONE, 
      field_length = 20, 
      flags = 0, 
      field_index = 0, 
      null_bit = 2 '\002', 
      is_created_from_null_item = false, 
      m_indexed = false, 
      m_warnings_pushed = 0, 
      gcol_info = 0x0, 
      stored_in_db = true
    }, 
    members of Field_num: 
    dec = 2 '\002', 
    zerofill = false, 
    unsigned_flag = false
  }, 
--Type <RET> for more, q to quit, c to continue without paging--
  members of Field_new_decimal: 
  precision = 18, 
  bin_size = 9
}

[adofsauron]

在序列化的时候做检测：

        if (!encoder_created) {
          std::stringstream ss;
          ss << "UNION of non-matching columns (column no " << i << ") .";
          throw common::NotImplementedException(ss.str());
        }

[adofsauron]

问题也出在这么一个投影上, 这倒是一个不新鲜的问题

select 1 as x from t2

[adofsauron]

一个非常有趣的问题，如果不对界限设置边界，那么就会被这种无穷无尽的sql的方言给拖死进去

[adofsauron]

保持在thd中的错误信息

  m_message_text = "The query includes syntax that is not supported by the storage engine. Tianmu: UNION of non-matching columns (column no 0) .", '\000' <repeats 387 times>, 

[adofsauron]

除此之外还有另一个惊奇的代码逻辑:

    if (max_ints + max_scale > 18) {
      std::stringstream ss;
      ss << "UNION of non-matching columns (column no " << 0 << ") .";
      throw common::NotImplementedException(ss.str());
    }

[adofsauron]

(gdb) p max_ints
$1 = 19
(gdb) p max_scale
$2 = 2

[adofsauron]

decimal的精度限定在18位以内

[adofsauron]

这块也有一些恶心的做法，但是没那么明显，可以分析这里的临时表创建的列属性的限制，对于后续运算使用，这里存在魔数并不奇怪，但是关键地方就是在于条件本身的处理

[adofsauron]

列属性使用了bigint

(gdb) p type2
$3 = {
  type = Tianmu::common::ColumnType::BIGINT, 
  unsigned_flag_ = false, 
  precision = 19, 
  scale = 0, 
  internal_size = 8, 
  display_size = 20, 
  collation = {
    collation = 0x44674e0 <my_charset_bin>, 
    derivation = DERIVATION_NONE, 
    repertoire = 3
  }, 
  fmt = Tianmu::common::PackFmt::DEFAULT, 
  flag = std::bitset
}

[adofsauron]

bigint和int的精度限度不同，这里区别对待

[adofsauron]

[adofsauron]

至此，客户POC的这个场景被成功的解决，遗留的问题就是何时彻底处理bigint，这里仅做备忘录，不处理bigint