From user-return-8007-apmail-phoenix-user-archive=phoenix.apache.org@phoenix.apache.org Thu May 17 23:45:42 2018 Return-Path: X-Original-To: apmail-phoenix-user-archive@minotaur.apache.org Delivered-To: apmail-phoenix-user-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id DBCC8186BE for ; Thu, 17 May 2018 23:45:42 +0000 (UTC) Received: (qmail 42814 invoked by uid 500); 17 May 2018 23:45:37 -0000 Delivered-To: apmail-phoenix-user-archive@phoenix.apache.org Received: (qmail 42747 invoked by uid 500); 17 May 2018 23:45:37 -0000 Mailing-List: contact user-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@phoenix.apache.org Delivered-To: mailing list user@phoenix.apache.org Received: (qmail 42737 invoked by uid 99); 17 May 2018 23:45:37 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 May 2018 23:45:37 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id 01ED8C0033 for ; Thu, 17 May 2018 23:45:37 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.889 X-Spam-Level: * X-Spam-Status: No, score=1.889 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001, T_KAM_HTML_FONT_INVALID=0.01] autolearn=disabled Authentication-Results: spamd4-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id lb50K-B561DA for ; Thu, 17 May 2018 23:45:32 +0000 (UTC) Received: from mail-wm0-f65.google.com (mail-wm0-f65.google.com [74.125.82.65]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id C52BF5F2AA for ; Thu, 17 May 2018 23:45:31 +0000 (UTC) Received: by mail-wm0-f65.google.com with SMTP id a8-v6so11203239wmg.5 for ; Thu, 17 May 2018 16:45:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=s7JaKf8mdajSyOQU7zcUPOVOZbsQS0lbf5Yamx9CkGM=; b=HQYhjSraJ8w4KChq/UIDuNmEPhKXGqFwLiTmJFnqfzGmP5BVKhs0DpbSDxeNfgMGtv 5y3W/CnjIb9ZK6AviBSX/qYG4d+VYpbBBlj32jBwlizF8AdkvxhucEermX+9cO79tleT YKcnjii/vIyjNGTUZ1y0HvQaKNUS9vt+tHgQpzv3opehePdYX7cqUft15g3nVaOp8bHs 0/PTw+s9DXYvrOPo3wEwn5uMou8jF5tNSI4kxPzVXlQKFSUSZeZbuFPbpNJKwjegtosd XS8HdfwxWdw0f9apM/tG6FIpRbUSDax1qktN+DK31dSfoL8t9pwIBV5ifodxDptwjafu +C/w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=s7JaKf8mdajSyOQU7zcUPOVOZbsQS0lbf5Yamx9CkGM=; b=aZojMc3pXMo7//6PnjypAUbpjNcLobcejNCQpRQv5CrA3oCPkY5/COogvwNi30Q9/E HMSxRI1jgXQHBgGOmKjWmiL9IYz+y4KUKmjBnZH4t6XiGbsckorM2Gu4ySSWnG6v4J5P DB/lyl9436+E/a82dDV3RtSYwJ93VS+IOWwequ+xLuQ6K/gXeoexBphJVJciDRxabR8w 3WwBN66o7aH7W/TLjAhyFVO1Jxig3M1z46YMHExDxcPK0qLCRAQF0s49vvSDE5LwPn3i VNpMMQJpDLV9xph252iJaqZDEdSU2DSn8+82myZd3KaKXH788L1ELQaBNrDZKNci+G+c +q6w== X-Gm-Message-State: ALKqPwfIecwHSfg6OP2zZXzjCpczKWQo3Sdwfk0jPSoT8g9pfOFBbpGc /Ph4Z9U8LmSWZ+w0H2khZW6mmbfO8RNPX5lYGOU= X-Google-Smtp-Source: AB8JxZoeHQCci9B3eYePyggJaGo9dp3Q1jJ/YCdrOBZ+qE326yGw/nwqF0GHZYDV8zNj3SYbY7kIH46opFMp2hhJwoc= X-Received: by 2002:a1c:5451:: with SMTP id p17-v6mr2970372wmi.26.1526600731295; Thu, 17 May 2018 16:45:31 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.166.84 with HTTP; Thu, 17 May 2018 16:44:50 -0700 (PDT) In-Reply-To: References: From: Maryann Xue Date: Thu, 17 May 2018 16:44:50 -0700 Message-ID: Subject: Re: SORT_MERGE_JOIN on non-leading key: server-side sorting To: Gerald Sangudi Cc: user@phoenix.apache.org Content-Type: multipart/alternative; boundary="0000000000004d8faf056c6f6e0e" --0000000000004d8faf056c6f6e0e Content-Type: text/plain; charset="UTF-8" Hey, this is a simple experiment I did based on an existing test case. Look for "query3". The test verifies that there is no "SERVER SORT" in the entire query plan (both salted and unsalted tables have been covered): @Test public void testBug4508() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); props = PropertiesUtil.deepCopy(TEST_PROPERTIES); props.setProperty("TenantId", "010"); Connection conn010 = DriverManager.getConnection(getUrl(), props); try { // Salted tables String peopleTable = generateUniqueName(); String myTable = generateUniqueName(); conn.createStatement().execute("CREATE TABLE " + peopleTable + " (\n" + "PERSON_ID VARCHAR NOT NULL,\n" + "NAME VARCHAR\n" + "CONSTRAINT PK_TEST_PEOPLE PRIMARY KEY (PERSON_ID)) SALT_BUCKETS = 3"); conn.createStatement().execute("CREATE TABLE " + myTable + " (\n" + "LOCALID VARCHAR NOT NULL,\n" + "DSID VARCHAR(255) NOT NULL, \n" + "EID CHAR(40),\n" + "HAS_CANDIDATES BOOLEAN\n" + "CONSTRAINT PK_MYTABLE PRIMARY KEY (LOCALID, DSID)) SALT_BUCKETS = 3"); verifyQueryPlanAndResultForBug4508(conn, peopleTable, myTable); // Salted multi-tenant tables String peopleTable2 = generateUniqueName(); String myTable2 = generateUniqueName(); conn.createStatement().execute("CREATE TABLE " + peopleTable2 + " (\n" + "TENANT_ID VARCHAR NOT NULL,\n" + "PERSON_ID VARCHAR NOT NULL,\n" + "NAME VARCHAR\n" + "CONSTRAINT PK_TEST_PEOPLE PRIMARY KEY (TENANT_ID, PERSON_ID))\n" + "SALT_BUCKETS = 3, MULTI_TENANT=true"); conn.createStatement().execute("CREATE TABLE " + myTable2 + " (\n" + "TENANT_ID VARCHAR NOT NULL,\n" + "LOCALID VARCHAR NOT NULL,\n" + "DSID VARCHAR(255) NOT NULL, \n" + "EID CHAR(40),\n" + "HAS_CANDIDATES BOOLEAN\n" + "CONSTRAINT PK_MYTABLE PRIMARY KEY (TENANT_ID, LOCALID, DSID))\n" + "SALT_BUCKETS = 3, MULTI_TENANT=true"); verifyQueryPlanAndResultForBug4508(conn010, peopleTable2, myTable2); } finally { conn.close(); conn010.close(); } } private static void verifyQueryPlanAndResultForBug4508( Connection conn, String peopleTable, String myTable) throws Exception { PreparedStatement peopleTableUpsertStmt = conn.prepareStatement( "UPSERT INTO " + peopleTable + " VALUES(?, ?)"); peopleTableUpsertStmt.setString(1, "X001"); peopleTableUpsertStmt.setString(2, "Marcus"); peopleTableUpsertStmt.execute(); peopleTableUpsertStmt.setString(1, "X002"); peopleTableUpsertStmt.setString(2, "Jenny"); peopleTableUpsertStmt.execute(); peopleTableUpsertStmt.setString(1, "X003"); peopleTableUpsertStmt.setString(2, "Seymour"); peopleTableUpsertStmt.execute(); conn.commit(); PreparedStatement myTableUpsertStmt = conn.prepareStatement( "UPSERT INTO " + myTable + " VALUES(?, ?, ?, ?)"); myTableUpsertStmt.setString(1, "X001"); myTableUpsertStmt.setString(2, "GROUP"); myTableUpsertStmt.setString(3, null); myTableUpsertStmt.setBoolean(4, false); myTableUpsertStmt.execute(); myTableUpsertStmt.setString(1, "X001"); myTableUpsertStmt.setString(2, "PEOPLE"); myTableUpsertStmt.setString(3, null); myTableUpsertStmt.setBoolean(4, false); myTableUpsertStmt.execute(); myTableUpsertStmt.setString(1, "X003"); myTableUpsertStmt.setString(2, "PEOPLE"); myTableUpsertStmt.setString(3, null); myTableUpsertStmt.setBoolean(4, false); myTableUpsertStmt.execute(); myTableUpsertStmt.setString(1, "X002"); myTableUpsertStmt.setString(2, "PEOPLE"); myTableUpsertStmt.setString(3, "Z990"); myTableUpsertStmt.setBoolean(4, false); myTableUpsertStmt.execute(); conn.commit(); String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ COUNT(*)\n" + "FROM " + peopleTable + " ds JOIN " + myTable + " l\n" + "ON ds.PERSON_ID = l.LOCALID\n" + "WHERE l.EID IS NULL AND l.DSID = 'PEOPLE' AND l.HAS_CANDIDATES = FALSE"; String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*)\n" + "FROM (SELECT LOCALID FROM " + myTable + "\n" + "WHERE EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = FALSE) l\n" + "JOIN " + peopleTable + " ds ON ds.PERSON_ID = l.LOCALID"; String query3 = "SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*)\n" + "FROM " + myTable + " t1 JOIN " + myTable + " t2\n" + "ON t1.DSID = t2.DSID\n" + "WHERE t1.LOCALID = 'X001' AND t2.LOCALID = 'X002'"; for (String q : new String[]{query1, query2, query3}) { ResultSet rs = conn.createStatement().executeQuery("explain " + q); String plan = QueryUtil.getExplainPlan(rs); assertFalse("Tables should not be sorted over their PKs:\n" + plan, plan.contains("SERVER SORTED BY")); rs = conn.createStatement().executeQuery(q); assertTrue(rs.next()); //assertEquals(2, rs.getInt(1)); assertFalse(rs.next()); } } On Thu, May 17, 2018 at 3:48 PM, Gerald Sangudi wrote: > Hi Maryann, > > Would you mind sharing the EXPLAIN output you are getting? > > I'm not able to find a download for phoenix-4.13.2-HBase or 4.14.0-HBase. > The *-cdh downloads do not work with Amazon EMR. I tried building against > the 4.14.0-rc0 tag. This produced 4.14.0 for phoenix-core.jar, but all the > other jars produced are 4.13.1, including the client jar. When I deploy > these jars on EMR and run queries via sqlline, I get a NoClassDefFoundError. > > Are the plans to provide 4.13.2-HBase as a public download? > > Thanks, > Gerald > > On Wed, May 16, 2018 at 10:40 AM, Maryann Xue > wrote: > >> Hi Gerald, >> >> I checked again. Unfortunately this fix is included with 4.13.2 but not >> 4.13.1. Would you mind upgrading your library to 4.13.2? >> >> Thanks, >> Maryann >> >> On Wed, May 16, 2018 at 9:41 AM, Maryann Xue >> wrote: >> >>> Sorry for the late response. Yes, sure, I will try it right away. >>> >>> On Wed, May 16, 2018 at 9:40 AM Gerald Sangudi >>> wrote: >>> >>>> Hi Maryann, >>>> >>>> Following up again -- I ran EXPLAIN on 4.13.1 and saw the same >>>> behavior, a sort on the RHS. Is it possible for you to try it on 4.13.1? >>>> >>>> Thanks, >>>> Gerald >>>> >>>> On Fri, May 11, 2018 at 10:52 AM, Gerald Sangudi >>>> wrote: >>>> >>>>> Hi Maryann, >>>>> >>>>> Thanks for verifying against latest. However, I did not detect the fix >>>>> in Phoenix 4.13.1. AWS EMR currently provides Phoenix 4.13.0. I manually >>>>> upgraded an AWS EMR cluster to Phoenix 4.13.1 and rebooted the cluster. The >>>>> EXPLAIN plan still shows the same issue. >>>>> >>>>> Thanks, >>>>> Gerald >>>>> >>>>> On Wed, May 9, 2018 at 11:51 AM, Maryann Xue >>>>> wrote: >>>>> >>>>>> Hi Gerald, >>>>>> >>>>>> I have verified against latest Phoenix code that this problem has >>>>>> been fixed. I have also checked Phoenix 4.13 release tags. Looks like all >>>>>> versions of 4.13 packages now include that fix. Would you mind getting the >>>>>> latest Phoenix-4.13 package and testing it again? Thank you! >>>>>> >>>>>> >>>>>> Thanks, >>>>>> Maryann >>>>>> >>>>>> On Tue, May 8, 2018 at 12:24 PM, Gerald Sangudi >>>>> > wrote: >>>>>> >>>>>>> Hello, >>>>>>> >>>>>>> I'm running Phoenix 4.13 on AWS EMR and getting the following >>>>>>> EXPLAIN plan: >>>>>>> >>>>>>> Table: >>>>>>> >>>>>>> *CREATE TABLE salted ( keyA BIGINT NOT NULL, keyB BIGINT >>>>>>> NOT NULL, val SMALLINT, CONSTRAINT pk PRIMARY KEY (keyA, >>>>>>> keyB))SALT_BUCKETS = 64;* >>>>>>> EXPLAIN: >>>>>>> >>>>>>> *EXPLAINSELECT /*+ USE_SORT_MERGE_JOIN */COUNT(*) cFROM salted t1 >>>>>>> JOIN salted t2ON (t1.keyB = t2.keyB)WHERE t1.keyA = 10AND t2.keyA = 20;* >>>>>>> >>>>>>> *+------------------------------------------------------------------------------+-----------------+-+| >>>>>>> PLAN | EST_BYTES_READ | >>>>>>> |+------------------------------------------------------------------------------+-----------------+-+| >>>>>>> SORT-MERGE-JOIN (INNER) TABLES >>>>>>> | null | || CLIENT >>>>>>> 64-CHUNK PARALLEL 64-WAY RANGE SCAN OVER SALTED [0,10] - [63,10] | null | >>>>>>> || SERVER FILTER BY FIRST KEY ONLY >>>>>>> | null | || CLIENT MERGE SORT >>>>>>> | null | || AND >>>>>>> (SKIP MERGE) | >>>>>>> null | || CLIENT 64-CHUNK PARALLEL 64-WAY RANGE SCAN OVER SALTED [0,20] >>>>>>> - [63,20] | null | || SERVER FILTER BY FIRST KEY ONLY >>>>>>> | null | || SERVER SORTED BY >>>>>>> [T2.KEYB] | null | || CLIENT >>>>>>> MERGE SORT | null | >>>>>>> || CLIENT AGGREGATE INTO SINGLE ROW >>>>>>> | null | >>>>>>> |+------------------------------------------------------------------------------+-----------------+-+* >>>>>>> >>>>>>> In the EXPLAIN output, I see "SERVER SORTED BY [T2.KEYB]". Is this >>>>>>> sort necessary? For both JOIN terms T1 and T2, the value of keyA, the >>>>>>> leading part of the primary key, is fixed. Furthermore, there is no >>>>>>> corresponding sort of T1.KEYB. >>>>>>> >>>>>>> When I EXPLAIN the same query on a non-salted table, neither T1.KEYB >>>>>>> nor T2.KEYB is re-sorted. I'm hoping the sort is unnecessary. If so, is >>>>>>> there an open ticket? I would be happy to file a ticket and to contribute >>>>>>> to a fix. I would appreciate any guidance. >>>>>>> >>>>>>> Thanks, >>>>>>> Gerald >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >> > --0000000000004d8faf056c6f6e0e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hey, this is a simple experiment I did based on an existin= g test case. Look for "query3". The test verifies that there is n= o "SERVER SORT" in the entire query plan (both salted and unsalte= d tables have been covered):

@Test
public void testBug4508() throws Exception {    Properties props =3D PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn =3D DriverMa=
nager.getConnection(getUrl(), props);
    props =3D PropertiesUti=
l.deepCopy(TEST_PROPERTIES);<=
br>    props.setProperty("TenantId", "010");
    Connection conn010 =3D DriverManage=
r.getConnection(getUrl(), props);
    try {
        // Salted tables
        String peopleTable =
=3D generateUniqueName();
     =
   String myTable =3D generateUniqueName<=
/span>();
        conn.createStatement().execute("CREATE TABLE " + peopleTable=
 + " (\n" +
                "PERSON_ID VARCHAR NOT NU=
LL,\n" +
        =
        "NAME VARC=
HAR\n" +
        =
        "CONSTRAIN=
T PK_TEST_PEOPLE PRIMARY KEY (PERSON_ID)) SALT_BUCKETS =3D 3");=

        conn.createStatement().execute("CREATE TABLE " + myTable + " (\n" +
                "LOCALID VARCHAR NOT NULL,\n" +
                "DSID VARCHAR(255) NOT NU=
LL, \n" +
       =
         "EID CHAR=
(40),\n" +
      =
          "HAS_CAN=
DIDATES BOOLEAN\=
n" +=

                &qu=
ot;CONSTRAINT PK_MYTABLE PRIMARY KEY (LOCALID, DSID)) SALT_BUCKETS =3D 3&qu=
ot;);
        verifyQueryPlanAn=
dResultForBug4508(conn, peopleTable, myTable);

        // Salted multi-tenant t=
ables
  =
      String peopleTable2 =3D gene=
rateUniqueName();
        String myTable2 =3D generateUniqueName();
        conn.createStatement=
().execute("CREATE=
 TABLE " + peopleTable2 + " (\n" =
+
                "TENANT_ID VARCHAR NOT NULL,\n" +
                "PERSON_ID VARCHAR NOT NULL,\n" +
                "NAME VARCHAR\n" +
                "CONSTRAINT PK_TEST_PEOPLE PRIMARY =
KEY (TENANT_ID, PERSON_ID))\n&qu=
ot; +
                "SALT_BUCKETS =3D 3, MULTI_TENANT=3Dtrue");
  =
      conn.createStatement().execute("CREATE TABLE " + myTable2 + " (\n" +
                "TENANT_ID VARCHAR NOT NULL,\n" +
                "LOCALID VARCHAR NOT NULL,\n" +
             =
   "DSID VARCHAR(2=
55) NOT NULL, \n=
" +<=
br>                &quo=
t;EID CHAR(40),\=
n" +=

                &qu=
ot;HAS_CANDIDATES BOOLEAN\n"=
; +
                "CONSTRAINT PK_MYTABLE PRIMARY KEY (TENANT_ID, LOCALID, DSID))<=
/span>\n" +
            =
    "SALT_BUCKETS =
=3D 3, MULTI_TENANT=3Dtrue");
        verifyQueryPlanAndResultForBug4508(conn010, peopleTable2=
, myTable2);
    } f=
inally {
        conn.close();
        conn010.close();
   =
 }
}

pr=
ivate static void verifyQueryPlanAndResultForBug4508(
        Con=
nection conn, String peopleTable, String myTable) throws Exception {
    PreparedStateme=
nt peopleTableUpsertStmt =3D conn.prepareStatement(
            "UPSERT INTO " + peopleTable + "=
 VALUES(?, ?)");
    peopleTableUpsertStmt.setString(1, "X001");
    peopleTableUpsertStmt.setSt=
ring(2, "Marcus");
    peopleTableUp=
sertStmt.execute();
    peopleTableUpsertStmt.setString(1, "X002");
    peopleTableUpsertStmt.setString(2, "Jenny");
    peopleTableUpsertStmt.e=
xecute();
    peopleTableUpsertStmt.setString(1, &quo=
t;X003");
    peopleTableUpsertStmt.setString(2, "Seymour");
    peopleTableUpsertStmt.execute()=
;
    conn.commit();

    PreparedStatement myTableUpsertStmt =3D =
conn.prepareStatement(
            "UPSERT INTO " + myTable + " VALUES(?, ?, ?, ?)")=
;
    myTableUpsertStmt.setString(1, "X001"=
);
    myTableUpsertStmt.setString(2, "GR=
OUP");
    myTableUpsertStmt.setString(3, null);
    myTableUpsertStmt.setBoolean(4, fa=
lse);
    myTableUpsertStmt.execute();
    myTableUpsertStmt.s=
etString(1, "X001");
    myTableUpse=
rtStmt.setString(2, "PEOPLE");
   =
 myTableUpsertStmt.setString(3, <=
span style=3D"color:rgb(0,0,128);font-weight:bold">null);
    myT=
ableUpsertStmt.setBoolean(4, false);
    myTab=
leUpsertStmt.execute();
    myTableUpsertStmt.setString(1, "X003");
    myTableUpsertStmt.setString(2, "PEOPLE");
    myTableUpsertStmt.setStrin=
g(3, null);
    myTableUpsertStmt.setBoolean(<=
span style=3D"color:rgb(0,0,255)">4, false);
    myTableUpsertStmt.execute();
=
    myTableUpsertStmt.setString(1=
, "X002");
    myTableUpsertStmt.setString(=
2, "PEOPLE&=
quot;);
    myTableUpsertStmt.setString(3, &qu=
ot;Z990");
    myTableUpsertStmt.setBoolean(4, false);
    myTableUpsertStmt.execute();
    conn.commit=
();

    String query1 =3D "SELECT /*+ USE_SORT_MERGE_JOIN*/ COUNT(*)\n" +
            "FROM " + peopleTable +=
 " ds JOIN " =
+ myTable + &quo=
t; l\n" +
       =
     "ON ds.PERSON=
_ID =3D l.LOCALID\n" +
            "=
;WHERE l.EID IS NULL AND l.DSID =3D 'PEOPLE' AND l.HAS_CANDIDATES =
=3D FALSE";
    String query2 =3D "SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*)<=
/span>\n" +
            =
"FROM (SELECT LOCA=
LID FROM " + myTable + "\n" +
            &quo=
t;WHERE EID IS NULL AND DSID =3D 'PEOPLE' AND HAS_CANDIDATES =3D FA=
LSE) l\n<=
span style=3D"color:rgb(0,128,0);font-weight:bold">" +
     =
       "JOIN "=
; + peopleTable + " ds ON ds.PERSON_ID =3D l.LOCALID";
    String query=
3 =3D "SELECT /*+ =
USE_SORT_MERGE_JOIN */ COUNT(*)\n" +
            "FROM " + myTable + " t1 JOIN " + myTable + " t2\n" +
            "ON t1.DSID =3D t2.DSID\n" +
            "WHERE t1.LOCALID =3D 'X001'=
; AND t2.LOCALID =3D 'X002'";

    for (String q : new String[]{query1, query2, q=
uery3}) {
        ResultSet rs =3D conn.createStatement().executeQuery(<=
span style=3D"color:rgb(0,128,0);font-weight:bold">"explain " + q);
        String plan =3D QueryUtil.getExplainPlan(rs);
        assertFalse(&=
quot;Tables should not be sorted over their PKs:\n" + plan,
                plan.contains("SERVER SORTED BY&qu=
ot;));

        rs =3D conn.createStatement().executeQuery(q);=

        assertTrue(rs.next());=

        //asse=
rtEquals(2, rs.getInt(1));
        assertF=
alse(rs.next());
    }
}


On Thu, May 17, 2018 at 3:4= 8 PM, Gerald Sangudi <gsangudi@23andme.com> wrote:
Hi Maryann,

Would you mind sharing the EXPLAIN output you are getting?

<= /div>
I'm not able to find a download for phoenix-4.13.2-HBase or 4= .14.0-HBase. The *-cdh downloads do not work with Amazon EMR. I tried build= ing against the 4.14.0-rc0 tag. This produced 4.14.0 for phoenix-core.jar, = but all the other jars produced are 4.13.1, including the client jar. When = I deploy these jars on EMR and run queries via sqlline, I get a NoClassDefF= oundError.

Are the plans to provide 4.13.2-HBase a= s a public download?

Thanks,
Gerald
<= br>
On Wed, May 16, 2018 at 10:40 AM, Maryann Xue= <maryann.xue@gmail.com> wrote:
Hi Gerald,

I checked again. U= nfortunately this fix is included with 4.13.2 but not 4.13.1. Would you min= d upgrading your library to 4.13.2?

Thanks,
<= div>Maryann

On Wed, May 16, 2018 at 9:41 AM, Maryann Xue <maryann.xue= @gmail.com> wrote:
Sorry fo= r the late response. Yes, sure, I will try it right away.

On Wed, May 16, 2018 at 9:40 AM Gerald Sangudi <gsangudi@23andme.com> wrote:=
Hi Maryann,
<= br>
Following up again -- I ran EXPLAIN on 4.13.1 and saw the sam= e behavior, a sort on the RHS. Is it possible for you to try it on 4.13.1?<= /div>

Thanks,
Gerald

On Fri, May 11, 2018 at 10:52 A= M, Gerald Sangudi <gsangudi@23andme.com> wrote:
Hi Maryann,

Th= anks for verifying against latest. However, I did not detect the fix in Pho= enix 4.13.1. AWS EMR currently provides Phoenix 4.13.0. I manually upgraded= an AWS EMR cluster to Phoenix 4.13.1 and rebooted the cluster. The EXPLAIN= plan still shows the same issue.

Thanks,
Gerald

On Wed, May = 9, 2018 at 11:51 AM, Maryann Xue <maryann.xue@gmail.com>= wrote:
Hi Gerald,
<= br>
I have verified against latest Phoenix code that this problem= has been fixed. I have also checked Phoenix 4.13 release tags. Looks like = all versions of 4.13 packages now include that fix. Would you mind getting = the latest Phoenix-4.13 package and testing it again? Thank you!
=

Thanks,
Maryann

On Tue, May 8, 2018= at 12:24 PM, Gerald Sangudi <gsangudi@23andme.com> wrote= :
Hello,

I'm running Phoenix 4.13 on AWS EMR and getting the follow= ing EXPLAIN plan:

Table:

CREATE TABLE salted (

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0keyA BIGINT NOT NULL,<= /span>

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0keyB BIGINT NOT NULL,=

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0val SMALLINT= ,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0CONSTRAINT p= k PRIMARY KEY (keyA, keyB)

)

EXPLAIN:


EXPLAIN

SELECT /*+ USE_SORT_MERGE_JOIN */

COUNT(*) c

FROM salted t1 JOIN salted t2

ON (t1.keyB =3D t2.k= eyB)

WHERE t1.keyA = =3D 10

AND t2.keyA = =3D 20;


<= /font>

+--= -----------------------------------------------------------------= -----------+-----------------+-+

| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0PLAN =C2=A0=C2=A0| EST_= BYTES_READ | |

+--= -----------------------------------------------------------------= -----------+-----------------+-+

| SORT-MERGE-JOIN (INNER) TABLES =C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| null | |

| =C2=A0=C2=A0=C2=A0=C2=A0CLIENT 64= -CHUNK PARALLEL 64-WAY RANGE SCAN OVER SALTED [0,10] - [63,10] =C2=A0| null= | |

| = =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0SERVER FILTER BY FIRST KEY = ONLY =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0| null | |

| =C2=A0=C2=A0=C2=A0=C2=A0CLIENT MERGE SORT =C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| null | |

| AND (SKIP MERGE) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| null | |

| =C2=A0=C2=A0=C2=A0=C2=A0CLIENT 6= 4-CHUNK PARALLEL 64-WAY RANGE SCAN OVER SALTED [0,20] - [63,20] =C2=A0| nul= l | |

| = =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0SERVER FILTER BY FIRST KEY = ONLY =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0| null | |

| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0SERVER SORTED BY [T2.KEYB] =C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| null | |

| =C2=A0=C2=A0=C2=A0=C2=A0CLIENT MERGE SORT =C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0| null | |

| CLIENT AGGREGATE INTO SINGLE ROW =C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0| null | |

+------------------------------------------= ------------------------------------+-----------------+-+<= /p>

In the EXPLAIN output, I see "SERVER SORTED BY [T2.KEYB]". = Is this sort necessary? For both JOIN terms T1 and T2, the value of keyA, t= he leading part of the primary key, is fixed. Furthermore, there is no corr= esponding sort of T1.KEYB.


When I EXPLAIN the same query on = a non-salted table, neither T1.KEYB nor T2.KEYB is re-sorted. I'm hopin= g the sort is unnecessary. If so, is there an open ticket? I would be happy= to file a ticket and to contribute to a fix. I would appreciate any guidan= ce.

Thanks,
Gerald







--0000000000004d8faf056c6f6e0e--