From issues-return-4126-archive-asf-public=cust-asf.ponee.io@phoenix.apache.org Sun Jan 20 18:55:06 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id 15928180634 for ; Sun, 20 Jan 2019 18:55:05 +0100 (CET) Received: (qmail 17918 invoked by uid 500); 20 Jan 2019 17:55:05 -0000 Mailing-List: contact issues-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list issues@phoenix.apache.org Received: (qmail 17909 invoked by uid 99); 20 Jan 2019 17:55:05 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 20 Jan 2019 17:55:05 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id CEEC71808A3 for ; Sun, 20 Jan 2019 17:55:04 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -105.301 X-Spam-Level: X-Spam-Status: No, score=-105.301 tagged_above=-999 required=6.31 tests=[ENV_AND_HDR_SPF_MATCH=-0.5, KAM_SOMETLD_ARE_BAD_TLD=5, RCVD_IN_DNSWL_MED=-2.3, SPF_PASS=-0.001, USER_IN_DEF_SPF_WL=-7.5, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id iczMxvEH29Fz for ; Sun, 20 Jan 2019 17:55:01 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTP id 1FE9E5F23A for ; Sun, 20 Jan 2019 17:55:01 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id A0D65E0DAA for ; Sun, 20 Jan 2019 17:55:00 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 3619425671 for ; Sun, 20 Jan 2019 17:55:00 +0000 (UTC) Date: Sun, 20 Jan 2019 17:55:00 +0000 (UTC) From: "Hadoop QA (JIRA)" To: issues@phoenix.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (PHOENIX-5105) Push Filter through Sort for SortMergeJoin MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/PHOENIX-5105?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16747505#comment-16747505 ] Hadoop QA commented on PHOENIX-5105: ------------------------------------ {color:red}-1 overall{color}. Here are the results of testing the latest attachment http://issues.apache.org/jira/secure/attachment/12955559/PHOENIX-5015-4.x-HBase-1.4.patch against 4.x-HBase-1.4 branch at commit c8686ce73093b879bbda5f08f3e7933a9708e110. ATTACHMENT ID: 12955559 {color:green}+1 @author{color}. The patch does not contain any @author tags. {color:green}+1 tests included{color}. The patch appears to include 13 new or modified tests. {color:green}+1 javac{color}. The applied patch does not increase the total number of javac compiler warnings. {color:red}-1 release audit{color}. The applied patch generated 1 release audit warnings (more than the master's current 0 warnings). {color:red}-1 lineLengths{color}. The patch introduces the following lines longer than 100: + sql="select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from (select aid,age from "+tableName1+" where age >=11 and age<=33) a inner join "+ + "(select bid,code from "+tableName2+" order by code limit 2) b on a.aid=b.bid where b.code > 50"; + sql="select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.codesum from (select aid,sum(age) agesum from "+tableName1+" where age >=11 and age<=33 group by aid order by agesum limit 3) a inner join "+ + "(select bid,sum(code) codesum from "+tableName2+" group by bid order by codesum limit 2) b on a.aid=b.bid where b.codesum > 50"; + "(select a.aid,b.code from "+tableName1+" a inner join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 order by b.code limit 3) t1 inner join "+ + "(select a.aid,c.region from "+tableName1+" a inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 order by c.region desc limit 1) t2 on t1.aid=t2.aid "+ + "(select a.aid,sum(b.code) codesum from "+tableName1+" a inner join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 group by a.aid order by codesum limit 3) t1 inner join "+ + "(select a.aid,sum(c.region) regionsum from "+tableName1+" a inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 group by a.aid order by regionsum desc limit 2) t2 on t1.aid=t2.aid "+ + "(select a.aid,sum(b.code) codesum from "+tableName1+" a inner join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 group by a.aid order by a.aid,codesum limit 3) t1 inner join "+ + "(select a.aid,sum(c.region) regionsum from "+tableName1+" a inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 group by a.aid order by a.aid desc,regionsum desc limit 2) t2 on t1.aid=t2.aid "+ {color:red}-1 core tests{color}. The patch failed these unit tests: ./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.index.MutableIndexIT ./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.join.HashJoinMoreIT Test results: https://builds.apache.org/job/PreCommit-PHOENIX-Build/2264//testReport/ Release audit warnings: https://builds.apache.org/job/PreCommit-PHOENIX-Build/2264//artifact/patchprocess/patchReleaseAuditWarnings.txt Console output: https://builds.apache.org/job/PreCommit-PHOENIX-Build/2264//console This message is automatically generated. > Push Filter through Sort for SortMergeJoin > ------------------------------------------ > > Key: PHOENIX-5105 > URL: https://issues.apache.org/jira/browse/PHOENIX-5105 > Project: Phoenix > Issue Type: Improvement > Affects Versions: 4.14.1 > Reporter: chenglei > Assignee: chenglei > Priority: Major > Attachments: PHOENIX-5015-4.x-HBase-1.4.patch > > Time Spent: 10m > Remaining Estimate: 0h > > Given two tables: > {code} > CREATE TABLE merge1 ( > aid INTEGER PRIMARY KEY, > age INTEGER) > > CREATE TABLE merge2 ( > bid INTEGER PRIMARY KEY, > code INTEGER) > {code} > for following sql : > {code} > select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from > (select aid,age from merge1 where age >=11 and age<=33 order by age limit 3) a inner join > (select bid,code from merge2 order by code limit 1) b on a.aid=b.bid where b.code > 50 > {code} > For the RHS of SortMergeJoin, at first the where condition {{b.code > 50}} is pushed down to RHS as its {{JoinCompiler.Table.postFilters}}, and then {{order by b.bid}} is appended to RHS , finally the RHS is rewritten as > {{select bid,code from (select bid,code from merge2 order by code limit 1) order by bid}} by following line 211 in {{QueryCompiler.compileJoinQuery}}. > The rewritten sql is then compiled to ClientScanPlan by following line 221 ,and previously pushed down {{b.code > 50}} is compiled by {{table.compilePostFilterExpression}} method in following line 224 to filter the result of the preceding ClientScanPlan. The problem here is that we execute the {{order by bid}} first and then the postFilter {{b.code > 50}}, obviously it is inefficient. In fact, we can directly rewrite the RHS as > {{select bid,code from (select bid,code from merge2 order by code limit 1) order by bid where code > 50}} > to first filter {{b.code > 50}} and then execute the {{order by bid}} . > {code} > 208 protected QueryPlan compileJoinQuery(StatementContext context, List binds, JoinTable joinTable, boolean asSubquery, boolean projectPKColumns, List orderBy) throws SQLException { > 209 if (joinTable.getJoinSpecs().isEmpty()) { > 210 Table table = joinTable.getTable(); > 211 SelectStatement subquery = table.getAsSubquery(orderBy); > 212 if (!table.isSubselect()) { > 213 context.setCurrentTable(table.getTableRef()); > 214 PTable projectedTable = table.createProjectedTable(!projectPKColumns, context); > 215 TupleProjector projector = new TupleProjector(projectedTable); > 216 TupleProjector.serializeProjectorIntoScan(context.getScan(), projector); > 217 context.setResolver(FromCompiler.getResolverForProjectedTable(projectedTable, context.getConnection(), subquery.getUdfParseNodes())); > 218 table.projectColumns(context.getScan()); > 219 return compileSingleFlatQuery(context, subquery, binds, asSubquery, !asSubquery, null, projectPKColumns ? projector : null, true); > 220 } > 221 QueryPlan plan = compileSubquery(subquery, false); > 222 PTable projectedTable = table.createProjectedTable(plan.getProjector()); > 223 context.setResolver(FromCompiler.getResolverForProjectedTable(projectedTable, context.getConnection(), subquery.getUdfParseNodes())); > 224 return new TupleProjectionPlan(plan, new TupleProjector(plan.getProjector()), table.compilePostFilterExpression(context)); > 225 } > {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)