From issues-return-5255-archive-asf-public=cust-asf.ponee.io@phoenix.apache.org Fri Mar 15 15:54:50 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 01A4F180627 for ; Fri, 15 Mar 2019 16:54:49 +0100 (CET) Received: (qmail 48147 invoked by uid 500); 15 Mar 2019 15:54:49 -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 48132 invoked by uid 99); 15 Mar 2019 15:54:49 -0000 Received: from ec2-52-202-80-70.compute-1.amazonaws.com (HELO gitbox.apache.org) (52.202.80.70) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 15 Mar 2019 15:54:49 +0000 From: GitBox To: issues@phoenix.apache.org Subject: [GitHub] [phoenix] comnetwork commented on a change in pull request #444: PHOENIX-5148 Message-ID: <155266528853.22283.17083335968613864865.gitbox@gitbox.apache.org> Date: Fri, 15 Mar 2019 15:54:48 -0000 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit comnetwork commented on a change in pull request #444: PHOENIX-5148 URL: https://github.com/apache/phoenix/pull/444#discussion_r266027910 ########## File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/join/SubqueryUsingSortMergeJoinIT.java ########## @@ -516,8 +516,8 @@ public void testAnyAllComparisonSubquery() throws Exception { assertEquals(rs.getString(2), "T6"); assertFalse(rs.next()); - - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + tableName4 + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ANY(SELECT quantity FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\" GROUP BY quantity)"; + //add order by to make the query result stable + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + tableName4 + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ANY(SELECT quantity FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\" GROUP BY quantity) order by \"order_id\""; Review comment: If we don't add the order by , the order of the query result is reverse after this patch, just as: assertTrue (rs.next()); assertEquals(rs.getString(1), "000000000000004"); assertEquals(rs.getString(2), "T6"); assertTrue (rs.next()); assertEquals(rs.getString(1), "000000000000002"); assertEquals(rs.getString(2), "T6"); That is because after the `SubqueryRewriter.transform`,the original sql is rewritten as : ``` SELECT ORDER_ID,NAME FROM ORDERTABLE O Inner JOIN ITEMTABLE I ON (O.ITEM_ID = I.ITEM_ID) Inner JOIN (SELECT COLLECTDISTINCT($3.$4) $5,$3.$6 $2 FROM (SELECT QUANTITY $4,Q.ITEM_ID $6 FROM ORDERTABLE Q GROUP BY Q.ITEM_ID,QUANTITY) $3 GROUP BY $3.$6) $1 ON (O.ITEM_ID = $1.$2) WHERE QUANTITY != ANY($1.$5) ``` Without this patch, after the following first `SortMergeJoin` is completed, ``` SELECT ORDER_ID,NAME FROM ORDERTABLE O Inner JOIN ITEMTABLE I ON (O.ITEM_ID = I.ITEM_ID) ``` an additional `CLIENT SORTED BY [O.ITEM_ID]` is added in order to complete the second `SortMergeJoin`, which unfortunately changes the order of two tuples with the same `O.ITEM_ID` which is `0000000006`. With this patch, the unnecessary `CLIENT SORTED BY [O.ITEM_ID]` is avoid, the order of tuple is untouched after the first `SortMergeJoin`, so the final SortMergeJoin result is not same as without this patch. To make the query result stable regardless of any optimization, order by \"order_id\" is added. ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: users@infra.apache.org With regards, Apache Git Services