[ https://issues.apache.org/jira/browse/HIVE-13602?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15255914#comment-15255914 ]
Nemon Lou commented on HIVE-13602: ---------------------------------- Another information : after rewrite q16 into the following form,CBO can return right result: {noformat} select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt from partsupp left outer join ( select s_suppkey from supplier where s_comment like '%Customer%Complaints%' ) sq_1 on partsupp.ps_suppkey = sq_1.s_suppkey join ( select count(*) as cn from (select * from supplier where s_comment like '%Customer%Complaints%' ) b where s_suppkey is null) a on a.cn= 0 join part where p_partkey = ps_partkey and p_brand <> 'Brand#34' and p_type not like 'ECONOMY BRUSHED%' and p_size in (22, 14, 27, 49, 21, 33, 35, 28) and 1 = 1 and sq_1.s_suppkey is null group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size; {noformat} > TPCH q16 return wrong result when CBO is on > ------------------------------------------- > > Key: HIVE-13602 > URL: https://issues.apache.org/jira/browse/HIVE-13602 > Project: Hive > Issue Type: Bug > Components: CBO, Logical Optimizer > Affects Versions: 1.2.1 > Reporter: Nemon Lou > Assignee: Pengcheng Xiong > Attachments: explain_cbo_bad_part1.out, explain_cbo_bad_part2.out, > explain_cbo_bad_part3.out > > > Running tpch with factor 2, > q16 returns 1,160 rows when CBO is on, > while returns 59,616 rows when CBO is off. > See attachment for detail . -- This message was sent by Atlassian JIRA (v6.3.4#6332)