8000 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) . · Issue #1864 · stoneatom/stonedb · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

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) . #1864

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
haitaoguan opened this issue Jun 8, 2023 · 4 comments
Closed

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) . #1864

haitaoguan opened this issue Jun 8, 2023 · 4 comments
Labels
A-feature feature with good idea prio: high High priority

Comments

@haitaoguan
Copy link
Collaborator 
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) .
@haitaoguan haitaoguan added A-feature feature with good idea prio: high High priority labels Jun 8, 2023
@adofsauron adofsauron self-assigned this Jun 14, 2023
@adofsauron
Copy link
Collaborator

ACK

@adofsauron
Copy link
Collaborator

在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
Copy link
Collaborator

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

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

@adofsauron
Copy link
Collaborator

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

3ee9c96f1a3a63612fd3373fb8cc267f

@adofsauron adofsauron mentioned this issue Jun 14, 2023
Merged
14 tasks
@adofsauron adofsauron removed their assignment Jun 14, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
A-feature feature with good idea prio: high High priority
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@adofsauron @haitaoguan
0