【BUG】doris2.1.6版本 对union all之后再过滤无法获取第二段结果

Viewed 64
2.1 query

如以下sql，结果返回为空：

-- 结果为空，不符合预期
select * from (
    select '1' as c_flag,'one' as c_result
    union all
    select '2' as c_flag,'two' as c_result
              ) t
where t.c_flag = '2'

如果更改条件，则返回正确的一条结果，如下sql：

-- 结果正确，返回一条
select * from (
    select '1' as c_flag,'one' as c_result
    union all
    select '2' as c_flag,'two' as c_result
              ) t
where t.c_flag = '1'
Niklauswhu3
6 Answers

在selectdb-doris-2.1.6-rc04-3052e3ae26版本上未复现该问题。

可以执行命令来获取你的当前doris版本并更新到提问内容上：
select @@version_comment

黄海军@SelectDB3718

一样的版本号，从2.1.5升级上来的。我发现如果这么写结果就是对的

select * from (
    select '1' as c_flag,'one' as c_result
    union all
    select '2' as c_flag,'two' as c_result
              ) t
where t.c_flag = cast('2' as varchar(1))

是否是某个参数影响了？

Niklauswhu3

附上执行计划：

-- 第一段执行结果正确
explain verbose
select * from 
( select '1' as c_flag,'one' as c_result
union all
  select '2' as c_flag,'two' as c_result
) t where t.c_flag = '1';

PLAN FRAGMENT 0
  OUTPUT EXPRS:
    c_flag
    c_result
  PARTITION: UNPARTITIONED

  HAS_COLO_PLAN_NODE: false

  VRESULT SINK
     MYSQL_PROTOCAL

  1:VSELECT
  |  predicates: (c_flag = '1')
  |  tuple ids: 0 
  |  
  0:VUNION
     constant exprs: 
         '1' | 'one'
         '2' | 'two'
     tuple ids: 0 

Tuples:
TupleDescriptor{id=0, tbl=null}
  SlotDescriptor{id=0, col=null, colUniqueId=null, type=varchar(65533), nullable=false, isAutoIncrement=false, subColPath=null}
  SlotDescriptor{id=1, col=null, colUniqueId=null, type=varchar(65533), nullable=false, isAutoIncrement=false, subColPath=null}

-- 第二段，执行结果异常
explain verbose
select * from 
( select '1' as c_flag,'one' as c_result
union all
  select '2' as c_flag,'two' as c_result
) t where t.c_flag = '2'  
  
  
PLAN FRAGMENT 0
  OUTPUT EXPRS:
    c_flag
    c_result
  PARTITION: UNPARTITIONED

  HAS_COLO_PLAN_NODE: false

  VRESULT SINK
     MYSQL_PROTOCAL

  4:VEMPTYSET
     tuple ids: 0 

Tuples:
TupleDescriptor{id=0, tbl=null}
  SlotDescriptor{id=0, col=null, colUniqueId=null, type=varchar(65533), nullable=false, isAutoIncrement=false, subColPath=null}
  SlotDescriptor{id=1, col=null, colUniqueId=null, type=varchar(65533), nullable=false, isAutoIncrement=false, subColPath=null}
Niklauswhu3

image.png
我这边2.1.6这个版本是ok的

FishMAN13

2.1.6版本执行正常，可能是你原地升级上来的参数跟2.1.6默认参数不一样，建议自己对比一下有出入的变量参数，然后变更测试一下。

endlesscyl43

找到问题了，调整服务器攒批模式为off_mode就正常了，但是这个很不合理，确实是个bug

-- 关闭服务器攒批，结果正常
set group_commit='off_mode';
select * from
    ( select '1' as c_flag,'one' as c_result
      union all
      select '2' as c_flag,'two' as c_result
    ) t where t.c_flag = '2';

-- 设置同步模式，无数据，结果异常
set group_commit='sync_mode';
select * from
    ( select '1' as c_flag,'one' as c_result
      union all
      select '2' as c_flag,'two' as c_result
    ) t where t.c_flag = '2';

-- 设置异步模式，无数据，结果异常
set group_commit='async_mode';
select * from
    ( select '1' as c_flag,'one' as c_result
      union all
      select '2' as c_flag,'two' as c_result
    ) t where t.c_flag = '2';
Niklauswhu3