feature: 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.CURRENCY_ID,
       a.account_id,
       sum(start_amount) start_amount,
       sum(in_amount) in_amount,
       sum(out_amount) out_amount,
       sum(end_amount) end_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,
               0 in_amount,
               0 out_amount,
               0 end_amount
          FROM (SELECT result.*
                  FROM (SELECT a.*,
                               @rownum3 := @rownum3 + 1 inde,
                               IF(@pxydm3 = a.account_id,
                                  @rankno3 := @rankno3 + 1,
                                  @rankno3 := 1) AS rankno,
                               @pxydm3 := 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.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
        UNION ALL
        SELECT b.CURRENCY_ID,
               a.account_id,
               0 start_amount,
               sum(a.in_amount) in_amount,
               sum(a.out_amount) out_amount,
               0 end_amount
          FROM C1AM_BANK_LIST a, C1MD_BANK_ACCT b
         WHERE a.account_id = b.ROW_ID
         GROUP BY a.account_id, b.CURRENCY_ID) a
 GROUP BY a.account_id, a.CURRENCY_ID;
ERROR 1105 (HY000): The query includes syntax that is not supported by the storage engine. Tianmu: UNION of non-matching columns (column no 0) .

[adofsauron]

ACK

[adofsauron]

在debug模式下出现崩溃

#0  0x00007f6d0d3e9acf in raise () from /lib64/libc.so.6
#1  0x00007f6d0d3bcea5 in abort () from /lib64/libc.so.6
#2  0x00007f6d0d3bcd79 in __assert_fail_base.cold.0 () from /lib64/libc.so.6
#3  0x00007f6d0d3e2426 in __assert_fail () from /lib64/libc.so.6
#4  0x0000000002ceddfb in TABLE_LIST::optimize_derived_for_tianmu (this=0x7f6a249bc430, thd=0x7f6a2402b560) at /root/work/stonedb-dev-20230606/storage/tianmu/core/engine_execute.cpp:817
#5  0x00000000023fa3dd in JOIN::optimize (this=0x7f6a2507f928, phase=OptimizePhase::Before_LOJ_Transform) at /root/work/stonedb-dev-20230606/sql/sql_optimizer.cc:211
#6  0x0000000002cedb03 in st_select_lex::optimize_select_for_tianmu (this=0x7f6a249b89b0, thd=0x7f6a2402b560) at /root/work/stonedb-dev-20230606/storage/tianmu/core/engine_execute.cpp:762
#7  0x0000000002cedbe2 in st_select_lex::optimize_select_for_tianmu (this=0x7f6a249b79d0, thd=0x7f6a2402b560) at /root/work/stonedb-dev-20230606/storage/tianmu/core/engine_execute.cpp:775
#8  0x0000000002cedeab in TABLE_LIST::optimize_derived_for_tianmu (this=0x7f6a249be000, thd=0x7f6a2402b560) at /root/work/stonedb-dev-20230606/storage/tianmu/core/engine_execute.cpp:828
#9  0x00000000023fa3dd in JOIN::optimize (this=0x7f6a2507e040, phase=OptimizePhase::Before_LOJ_Transform) at /root/work/stonedb-dev-20230606/sql/sql_optimizer.cc:211
#10 0x0000000002cedb03 in st_select_lex::optimize_select_for_tianmu (this=0x7f6a249b61e0, thd=0x7f6a2402b560) at /root/work/stonedb-dev-20230606/storage/tianmu/core/engine_execute.cpp:762
#11 0x0000000002cedeab in TABLE_LIST::optimize_derived_for_tianmu (this=0x7f6a249c23f0, thd=0x7f6a2402b560) at /root/work/stonedb-dev-20230606/storage/tianmu/core/engine_execute.cpp:828
#12 0x00000000023fa3dd in JOIN::optimize (this=0x7f6a2507dc20, phase=OptimizePhase::Before_LOJ_Transform) at /root/work/stonedb-dev-20230606/sql/sql_optimizer.cc:211
#13 0x0000000002ceb23f in Tianmu::core::optimize_select (thd=0x7f6a2402b560, select_options=2147748608, result=0x7f6a249c15d8, select_lex=0x7f6a2402ff20, 
    is_optimize_after_tianmu=@0x7f6d0623adbc: 1, tianmu_free_join=@0x7f6d0623adc0: 1) at /root/work/stonedb-dev-20230606/storage/tianmu/core/engine_execute.cpp:353
#14 0x0000000002ceaaf7 in Tianmu::core::Engine::HandleSelect (this=0x836bca0, thd=0x7f6a2402b560, lex=0x7f6a2402d888, result=@0x7f6d0623adc8: 0x7f6a249c15d8, setup_tables_done_option=0, 
    res=@0x7f6d0623adc4: 0, is_optimize_after_tianmu=@0x7f6d0623adbc: 1, tianmu_free_join=@0x7f6d0623adc0: 1, with_insert=0)
    at /root/work/stonedb-dev-20230606/storage/tianmu/core/engine_execute.cpp:236
#15 0x00000000030855bf in Tianmu::DBHandler::ha_my_tianmu_query (thd=0x7f6a2402b560, lex=0x7f6a2402d888, result_output=@0x7f6d0623adc8: 0x7f6a249c15d8, setup_tables_done_option=0, 
    res=@0x7f6d0623adc4: 0, is_optimize_after_tianmu=@0x7f6d0623adbc: 1, tianmu_free_join=@0x7f6d0623adc0: 1, with_insert=0)
    at /root/work/stonedb-dev-20230606/storage/tianmu/sql/ha_my_tianmu.cpp:95
#16 0x0000000002427bf8 in execute_sqlcom_select (thd=0x7f6a2402b560, all_tables=0x7f6a249c23f0) at /root/work/stonedb-dev-20230606/sql/sql_parse.cc:4872
#17 0x0000000002420f6e in mysql_execute_command (thd=0x7f6a2402b560, first_level=true) at /root/work/stonedb-dev-20230606/sql/sql_parse.cc:2672
#18 0x0000000002428c5d in mysql_parse (thd=0x7f6a2402b560, parser_state=0x7f6d0623bf90) at /root/work/stonedb-dev-20230606/sql/sql_parse.cc:5277
#19 0x000000000241dc54 in dispatch_command (thd=0x7f6a2402b560, com_data=0x7f6d0623c730, command=COM_QUERY) at /root/work/stonedb-dev-20230606/sql/sql_parse.cc:1399
#20 0x000000000241ca95 in do_command (thd=0x7f6a2402b560) at /root/work/stonedb-dev-20230606/sql/sql_parse.cc:976
#21 0x000000000254f005 in handle_connection (arg=0xa7b1650) at /root/work/stonedb-dev-20230606/sql/conn_handler/connection_handler_per_thread.cc:313
#22 0x0000000002c1e844 in pfs_spawn_thread (arg=0xa5c72b0) at /root/work/stonedb-dev-20230606/storage/perfschema/pfs.cc:2197
#23 0x00007f6d1055e1ca in start_thread () from /lib64/libpthread.so.0
#24 0x00007f6d0d3d4e73 in clone () from /lib64/libc.so.6

[adofsauron]

问题出在对于派生表继续做了重复的优化, 这是个惯常的问题

(gdb) p unit->is_optimized()
$3 = true

[adofsauron]

但是在release模式下，被成功的修复