ERROR 1105 (HY000): The query includes syntax that is not supported by the storage engine. Tianmu: UNION of non-matching columns (column no 2) .bug:

[haitaoguan]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

###SQL
select b.CREATE_TIME AS create_time
from (tb_finance_period_pay a join tb_finance_company_account_change b)
where ((a.id = b.period_pay_id) and (a.pay_status = 2) and
(a.fee_type = 'TARGET') and (b.type = 2))
union
select a.create_time AS create_time
from (tb_finance_period_account a join tb_finance_company_account_change b)
where ((a.id = b.period_account_id) and (b.type = 1) and (a.pay_type = 1) and
(a.period_status = 1))

###ERROR
ERROR 1105 (HY000): The query includes syntax that is not supported by the storage engine. Tianmu: UNION of non-matching columns (column no 2) .

###tianmu.log
[2022-11-03 03:22:45.817792] [9650] [WARN] [exception.cpp:42] MSG: Exception: UNION of non-matching columns (column no 0) ..

STACK TRACE BEGIN
/data/stonedb57/install/bin/mysqld(+0x89e3dc) [0x55c7586373dc]
/data/stonedb57/install/bin/mysqld(Tianmu::core::Query::Preexecute(Tianmu::core::CompiledQuery&, Tianmu::core::ResultSender*, bool)+0x1d4e) [0x55c759314a4e]
/data/stonedb57/install/bin/mysqld(Tianmu::core::Engine::Execute(THD*, LEX*, Query_result*, st_select_lex_unit*)+0x504) [0x55c7592fa824]
/data/stonedb57/install/bin/mysqld(Tianmu::core::Engine::HandleSelect(THD*, LEX*, Query_result*&, unsigned long, int&, int&, int&, int)+0x356) [0x55c7592fb496]
/data/stonedb57/install/bin/mysqld(Tianmu::dbhandler::TIANMU_HandleSelect(THD*, LEX*, Query_result*&, unsigned long, int&, int&, int&, int)+0x39) [0x55c75937a029]
/data/stonedb57/install/bin/mysqld(+0xf07908) [0x55c758ca0908]
/data/stonedb57/install/bin/mysqld(mysql_execute_command(THD*, bool)+0x463c) [0x55c758ca7bdc]
/data/stonedb57/install/bin/mysqld(mysql_parse(THD*, Parser_state*)+0x445) [0x55c758ca9d85]
/data/stonedb57/install/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xdc2) [0x55c758caabc2]
/data/stonedb57/install/bin/mysqld(do_command(THD*)+0x227) [0x55c758cac177]
/data/stonedb57/install/bin/mysqld(handle_connection+0x2c8) [0x55c758d6bb88]
/data/stonedb57/install/bin/mysqld(pfs_spawn_thread+0x168) [0x55c75926ffa8]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8609) [0x7f22ebec2609]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43) [0x7f22ebc96133]
STACK TRACE END

[2022-11-03 03:22:45.817925] [9650] [INFO] [engine_execute.cpp:455] MSG: Query terminated with exception: select b.CREATE_TIME AS create_time
from (tb_finance_period_pay a join tb_finance_company_account_change b)
where ((a.id = b.period_pay_id) and (a.pay_status = 2) and
(a.fee_type = 'TARGET') and (b.type = 2))
union
select a.create_time AS create_time
from (tb_finance_period_account a join tb_finance_company_account_change b)
where ((a.id = b.period_account_id) and (b.type = 1) and (a.pay_type = 1) and
(a.period_status = 1))

Expected behavior

No ERROR

How To Reproduce

No response

Environment

IP：192.168.30.122

login：/data/stonedb57/install/bin/mysql -uroot -pstonedb123 -S /data/stonedb57/install/tmp/mysql.sock -D db

./mysqld Ver 5.7.36-StoneDB 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: c91a29e
Last commit time: Date: Mon Oct 24 03:25:06 2022 +0000
Build time: Date: Mon 24 Oct 2022 11:09:00 AM UTC

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

• Yes, I will!

[haitaoguan]

This SQL statement can be successsfully executed in InnoDB.

[RingsC]

To check: core/temp_table.cpp:

void TempTable::Union(TempTable *t, int all) {
...
bool encoder_created{false};
if (NumOfObj() == 0)
    encoder_created = encoder[i].PrepareEncoder(sec_vcs[i].get());
else if (t->NumOfObj() == 0)
    encoder_created = encoder[i].PrepareEncoder(first_vcs[i].get());
else
   encoder_created = encoder[i].PrepareEncoder(first_vcs[i].get(), sec_vcs[i].get());

[isredstar]

ACK

[isredstar]

Stack Info：

Tianmu::core::TempTable::Union(Tianmu::core::TempTable * const this, Tianmu::core::TempTable * t, int all) (\home\src\stonedb\storage\tianmu\core\temp_table.cpp:1325)
Tianmu::core::Query::Preexecute(Tianmu::core::Query * const this, Tianmu::core::CompiledQuery & qu, Tianmu::core::ResultSender * sender, bool display_now) (\home\src\stonedb\storage\tianmu\core\query.cpp:882)
Tianmu::core::Engine::Execute(Tianmu::core::Engine * const this, THD * thd, LEX * lex, Query_result * result_output, SELECT_LEX_UNIT * unit_for_union) (\home\src\stonedb\storage\tianmu\core\engine_execute.cpp:418)
Tianmu::core::Engine::HandleSelect(Tianmu::core::Engine * const this, THD * thd, LEX * lex, Query_result *& result, ulong setup_tables_done_option, int & res, int & optimize_after_tianmu, int & tianmu_free_join, int with_insert) (\home\src\stonedb\storage\tianmu\core\engine_execute.cpp:191)
Tianmu::handler::ha_my_tianmu_query(THD * thd, LEX * lex, Query_result *& result_output, ulong setup_tables_done_option, int & res, int & optimize_after_tianmu, int & tianmu_free_join, int with_insert) (\home\src\stonedb\storage\tianmu\handler\ha_rcengine.cpp:82)
execute_sqlcom_select(THD * thd, TABLE_LIST * all_tables) (\home\src\stonedb\sql\sql_parse.cc:5184)
mysql_execute_command(THD * thd, bool first_level) (\home\src\stonedb\sql\sql_parse.cc:2831)
mysql_parse(THD * thd, Parser_state * parser_state) (\home\src\stonedb\sql\sql_parse.cc:5622)
dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (\home\src\stonedb\sql\sql_parse.cc:1495)
do_command(THD * thd) (\home\src\stonedb\sql\sql_parse.cc:1034)
handle_connection(void * arg) (\home\src\stonedb\sql\conn_handler\connection_handler_per_thread.cc:313)
pfs_spawn_thread(void * arg) (\home\src\stonedb\storage\perfschema\pfs.cc:2197)

Source code：

if (NumOfObj() == 0)
  encoder_created = encoder[i].PrepareEncoder(sec_vcs[i].get());
else if (t->NumOfObj() == 0)
  encoder_created = encoder[i].PrepareEncoder(first_vcs[i].get());
else
  encoder_created = encoder[i].PrepareEncoder(first_vcs[i].get(), sec_vcs[i].get());
if (!encoder_created) {
  std::stringstream ss;
  ss << "UNION of non-matching columns (column no " << i << ") .";
  throw common::NotImplementedException(ss.str());
}

Timestamp

  bool is_timestamp1 = (this->vc_type.GetTypeName() == common::CT::TIMESTAMP);
  bool is_timestamp2 = (vc->Type().GetTypeName() == common::CT::TIMESTAMP);
  if (is_timestamp1 || (is_timestamp2 && !(is_timestamp1 && is_timestamp2)))
    return false;  // cannot compare timestamp with anything different than timestamp