[ https://issues.apache.org/jira/browse/CALCITE-3762?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
hezhang updated CALCITE-3762: ----------------------------- Description: the sql: {code:java} select * FROM( SELECT plat, category, rid, populary_num FROM panda_com.crawler_anchor WHERE par_date = '20180819' AND plat = 'huya' AND rid = 'meijiao' ) a JOIN ( SELECT DISTINCT 'huya' plat , edwin.privatehost , edwin.profileroom FROM panda_com.ol_huya_isOnline edwin WHERE par_date = '20180819' ) m9 ON a.rid= m9.privatehost AND a.plat = m9.plat{code} the result: {code:java} huya yule meijiao 30 huya 10001242 meijiao {code} but the desired result is: {code:java} huya yule meijiao 30 huya meijiao 10001242 {code} *cause:* hepPlanner use AggregateProjectPullUpConstantsRule !image-2020-02-01-01-29-49-479.png! [ISO|http://dict.youdao.com/search?q=ISO&keyfrom=chrome.extension] [ˈaɪsəʊ; ˌaɪ es ˈəʊ] [详细|http://dict.youdao.com/search?q=ISO&keyfrom=chrome.extension]X 基本翻译 abbr. 国际标准化组织(International Standardization Organization);国际科学组织(International Science Organization) 网络释义 [ISO:|http://dict.youdao.com/search?q=ISO&keyfrom=chrome.extension&le=eng] 国际标准化组织 [ISO Toolkit:|http://dict.youdao.com/search?q=ISO%20Toolkit&keyfrom=chrome.extension&le=eng] 映像格式处理 [ISO Auto:|http://dict.youdao.com/search?q=ISO%20Auto&keyfrom=chrome.extension&le=eng] 自动感光度篇 was: the sql: {code:java} select * FROM( SELECT plat, category, rid, populary_num FROM panda_com.crawler_anchor WHERE par_date = '20180819' AND plat = 'huya' AND rid = 'meijiao' ) a JOIN ( SELECT DISTINCT 'huya' plat , edwin.privatehost , edwin.profileroom FROM panda_com.ol_huya_isOnline edwin WHERE par_date = '20180819' ) m9 ON a.rid= m9.privatehost AND a.plat = m9.plat{code} the result: {code:java} huya yule meijiao 30 huya 10001242 meijiao {code} but the desired result is: {code:java} huya yule meijiao 30 huya meijiao 10001242 {code} *cause:* hepPlanner use AggregateProjectPullUpConstantsRule : {code:java} HiveProject(plat=[$0], category=[$1], rid=[$2], populary_num=[$3], plat1=[$4], privatehost=[$5], profileroom=[$6]) HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(plat=[CAST(_UTF-16LE'huya'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], category=[$3], rid=[CAST(_UTF-16LE'meijiao'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], populary_num=[$5]) HiveFilter(condition=[AND(=($12, _UTF-16LE'20180819'), =($7, _UTF-16LE'huya'), =($0, _UTF-16LE'meijiao'))]) HiveTableScan(table=[[panda_com.crawler_anchor]], table:alias=[crawler_anchor]) HiveProject(plat=[_UTF-16LE'huya'], privatehost=[$1], profileroom=[$2]) HiveProject($f0=[_UTF-16LE'huya'], $f1=[$0], $f2=[$1]) HiveProject($f1=[$0], $f2=[CAST(_UTF-16LE'meijiao'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"]) HiveAggregate(group=[{2}]) HiveProject($f0=[_UTF-16LE'huya'], $f1=[CAST(_UTF-16LE'meijiao'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], $f2=[$4]) HiveFilter(condition=[AND(=($19, _UTF-16LE'20180819'), =(CAST(_UTF-16LE'meijiao'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary", $8))]) HiveTableScan(table=[[panda_com.ol_huya_isonline]], table:alias=[edwin]) {code} > AggregateProjectPullUpConstantsRule causes fields to be out of order > -------------------------------------------------------------------- > > Key: CALCITE-3762 > URL: https://issues.apache.org/jira/browse/CALCITE-3762 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.10.0, 1.16.0 > Reporter: hezhang > Priority: Major > Fix For: 1.16.0 > > Attachments: image-2020-02-01-01-29-49-479.png > > > the sql: > {code:java} > select * FROM( SELECT plat, category, rid, populary_num FROM > panda_com.crawler_anchor WHERE > par_date = '20180819' > AND plat = 'huya' > AND rid = 'meijiao' > ) a > JOIN > ( > SELECT DISTINCT > 'huya' plat , > edwin.privatehost , > edwin.profileroom > FROM > panda_com.ol_huya_isOnline edwin > WHERE > par_date = '20180819' ) m9 > ON > a.rid= m9.privatehost > AND a.plat = m9.plat{code} > the result: > > {code:java} > huya yule meijiao 30 huya 10001242 meijiao > {code} > > but the desired result is: > > {code:java} > huya yule meijiao 30 huya meijiao 10001242 > {code} > > *cause:* > hepPlanner use AggregateProjectPullUpConstantsRule > !image-2020-02-01-01-29-49-479.png! > [ISO|http://dict.youdao.com/search?q=ISO&keyfrom=chrome.extension] [ˈaɪsəʊ; > ˌaɪ es ˈəʊ] > [详细|http://dict.youdao.com/search?q=ISO&keyfrom=chrome.extension]X > 基本翻译 > abbr. 国际标准化组织(International Standardization > Organization);国际科学组织(International Science Organization) > 网络释义 > [ISO:|http://dict.youdao.com/search?q=ISO&keyfrom=chrome.extension&le=eng] > 国际标准化组织 > [ISO > Toolkit:|http://dict.youdao.com/search?q=ISO%20Toolkit&keyfrom=chrome.extension&le=eng] > 映像格式处理 > [ISO > Auto:|http://dict.youdao.com/search?q=ISO%20Auto&keyfrom=chrome.extension&le=eng] > 自动感光度篇 -- This message was sent by Atlassian Jira (v8.3.4#803005)