From user-return-8009-apmail-phoenix-user-archive=phoenix.apache.org@phoenix.apache.org Fri May 18 00:20:55 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 323D4187F3 for ; Fri, 18 May 2018 00:20:55 +0000 (UTC) Received: (qmail 36114 invoked by uid 500); 18 May 2018 00:20:54 -0000 Delivered-To: apmail-phoenix-user-archive@phoenix.apache.org Received: (qmail 36042 invoked by uid 500); 18 May 2018 00:20:54 -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 36032 invoked by uid 99); 18 May 2018 00:20:54 -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; Fri, 18 May 2018 00:20:54 +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 2492FC0033 for ; Fri, 18 May 2018 00:20:54 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.909 X-Spam-Level: * X-Spam-Status: No, score=1.909 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, 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=23andme.com Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id nhw-fQYru7-B for ; Fri, 18 May 2018 00:20:48 +0000 (UTC) Received: from mail-pg0-f43.google.com (mail-pg0-f43.google.com [74.125.83.43]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTPS id 62AC75F1B9 for ; Fri, 18 May 2018 00:20:48 +0000 (UTC) Received: by mail-pg0-f43.google.com with SMTP id e1-v6so2518556pga.6 for ; Thu, 17 May 2018 17:20:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=23andme.com; s=google; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=aJf2r5dLPyn4TuVk2HKLT5C7MS9FL4pbJoWLMW8oPY4=; b=OCrEWBAhAH/nWhSjqAv2ZDwcX1ZT8YVGxJTIGUAjR4f2kt0CkEKMIt5iBTcySZnAkF x6KZC7TEU+PtlLkIrGzX1+Y/hyUPJz9rnuI0Z/4ENUFBu3Kad6rOX8eNq8qYn9u/nHUz ZJISoxqTkjrQF0y+mr5SxYNeTQ50g/HOUO2o+rSLibDE2xXfMmOZ+M/TGtiE9oIfAZj0 Y1abVDg16z7tSioA0eauGjsGtfQ2PStL/Ulj1tQ/gX4S/m58lkZ4TGMPCUvi3GE+0hL7 +uouQypZD5/tnZXX6N73vtcq5wgV/I2Di4ieRZ+R5qDKqMdCL8OPkJFlFTpbbaPcPejT L5rg== 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=aJf2r5dLPyn4TuVk2HKLT5C7MS9FL4pbJoWLMW8oPY4=; b=JojBKEUO6rdGYhwb7aWU52N498V49KH9/i04Bf7RYfkQVAZg197UnODouZGxthRUHe z7zFtO3vvd4jLGWFINGWsqTyHaxufn7NxWTam3pHSAxx4xcutGIjn17ldxuZ3tUv5FgH 4UySTjpmAxMsqCZWn3JyWihzwLK2raUzw5Dawgl8rWTDUT1x0pnWCPe3/UhAq/Z00QFA qrW7hxRwxy53/qvCk9vqb+oT82fVC3wBYAeYN3OG8ojWS+D0XwTQ+pPfzk8rb/HzT0rQ MmtXCITHxQkNlQ2vhymrx/R5Yb1aCUbYN/unvWx0b5HwDTX6gbPEkQt/OeRAkWU2dkuU QEVA== X-Gm-Message-State: ALKqPwcSgOyQu+w47wkxGxaVPwXodW0xyrLknzr62uxGTb5ihiGCLXKR ZoLAaXo6XVYGwzJA7pqW7wy0uRL4SqiGrUygHS+nfg== X-Google-Smtp-Source: AB8JxZotzyCYq5ZJWUgsr+rUwkEzfsr4h9SJbk/4JjPSaRBQJEZgqo5LrmwMGwtPQ0ppOISxde4ex4ZG8NWZuNkrSfw= X-Received: by 2002:a63:7145:: with SMTP id b5-v6mr5686053pgn.45.1526602847431; Thu, 17 May 2018 17:20:47 -0700 (PDT) MIME-Version: 1.0 Received: by 10.91.213.131 with HTTP; Thu, 17 May 2018 17:20:46 -0700 (PDT) In-Reply-To: References: From: Gerald Sangudi Date: Thu, 17 May 2018 17:20:46 -0700 Message-ID: Subject: Re: SORT_MERGE_JOIN on non-leading key: server-side sorting To: James Taylor Cc: user Content-Type: multipart/alternative; boundary="0000000000006f44ce056c6fec2d" --0000000000006f44ce056c6fec2d Content-Type: text/plain; charset="UTF-8" Thanks Maryann and James. I'll verify the RC as soon as it's available. Gerald On Thu, May 17, 2018 at 5:01 PM, James Taylor wrote: > Hi Gerald, > The fix for PHOENIX-4508 will appear in the 4.14.0 release which we're > working on now. We should have a second RC up shortly that you can use to > verify. The fix isn't in 4.13 since it was checked in after the release. > > Thanks, > James > > On Thu, May 17, 2018 at 4:44 PM, Maryann Xue > wrote: > >> 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 < >>>>>> gsangudi@23andme.com> 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 < >>>>>>>> gsangudi@23andme.com> 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 >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>> >>> >> > --0000000000006f44ce056c6fec2d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks Maryann and James.

I'll veri= fy the RC as soon as it's available.

Gerald


On Thu, May 17, 2018 at 5:01 PM, James Taylor <= jamestaylor@apa= che.org> wrote:
Hi Gerald,
The fix for PHOENIX-4508 will appear in the 4.14.0 rel= ease which we're working on now. We should have a second RC up shortly = that you can use to verify. The fix isn't in 4.13 since it was checked = in after the release.

Thanks,
James
<= br>
On Thu, May 17, 2018 at 4:44 PM, Maryann Xue = <maryann.xue@gmail.com> wrote:
Hey, this is a simple experiment I did based on a= n existing test case. Look for "query3". The test verifies that t= here is no "SERVER SORT" in the entire query plan (both salted an= d unsalted tables have been covered):

@Test
public void testBug45=
08() throws Exce=
ption {
    Properties props =3D PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection con=
n =3D DriverManager.getConnection(=
getUrl(), props);
    prop=
s =3D PropertiesUtil.deepCopy(TEST_P=
ROPERTIES);
    props.setProperty("TenantId", "010");
    Connection =
conn010 =3D DriverManager.getConnection(getUrl(), props);
  =
  try {
     =
   // Salted table=
s
      =
  String peopleTable =3D generateU=
niqueName();
        String myTable =3D generateUniqueName();
        conn.createStatement().exe=
cute("CREATE =
TABLE " + peopleTable + " (\n" +
                "PERSON_ID VARCHAR NOT NULL,\n" +
                "NAME VARCHAR\n" +
                "CONSTRAINT PK_TEST_PEOPLE PRIMARY KEY (PERSON_ID)) =
SALT_BUCKETS =3D 3");
        conn.createStatement().execute=
("CREATE TABL=
E " + myTable + " (\n<=
/span>" +                "=
;LOCALID VARCHAR NOT NULL,\n&quo=
t; +
                "DSID VARCHAR(255) NOT NULL, \n" +
                "EID CHAR(40),\n" +
                "HAS_CANDIDATES BOOLEAN\n" +
                "CONSTRAINT PK_MYTABLE PRIMARY KEY =
(LOCALID, DSID)) SALT_BUCKETS =3D 3");
        verifyQueryPlanAndResultForBug4508(conn,=
 peopleTable, myTable);

        // Salted multi-tenant tables
        String peopleTabl=
e2 =3D generateUniqueName();
  =
      String myTable2 =3D generateUniqueN=
ame();
        conn.createStatement().execute("CREATE TABLE " + pe=
opleTable2 + " (\n" +
             =
   "TENANT_ID VARC=
HAR NOT NULL,\n<=
/span>" +                "=
;PERSON_ID VARCHAR NOT NULL,\n&q=
uot; +
                "NAME VARCHAR\n&q=
uot; +
                "CONSTRAINT PK_TEST_PEOPLE PRIMARY KEY (TENANT_ID, PERSON_ID=
))\n" +
         =
       "SALT_BUCKE=
TS =3D 3, MULTI_TENANT=3Dtrue");
        conn.createStatemen=
t().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=
" +
    =
            "CONST=
RAINT PK_MYTABLE PRIMARY KEY (TENANT_ID, LOCALID, DSID))\n" +
                "SALT_BUCKETS =3D 3, MULTI_TE=
NANT=3Dtrue");
        ver=
ifyQueryPlanAndResultForBug4508(conn010, peopleTable2, myTable2=
);
    } finally {
        conn.close();
        conn010.close();
    }
}

private stat=
ic void verifyQueryPlanAndResultForBug4508(
        Connecti=
on conn, String peopleTable, String myTable) throws Exception {
    PreparedStatement pe=
opleTableUpsertStmt =3D conn.prepareStatement(
            "UPSERT INTO " + =
peopleTable + " VA=
LUES(?, ?)");
    peopleTableUpsertStmt.setString(1, "X001");
    peopleTableUpsertStmt.set=
String(2, "Marcus");
    people=
TableUpsertStmt.execute();
    peopleTableUpsertStmt.setString=
(1, "X002");
    peopleTableUpsertSt=
mt.setString(2, "Jenny");
    =
peopleTableUpsertStmt.execute();
    peopleTableUpsertStmt.setStrin=
g(1, "X003");
    peopleTableUp=
sertStmt.setString(2, "Seymour");<=
br>    peopleTableUpsertStmt.execute();
    conn.commit();

 =
   PreparedStatement myTableUpsertStmt =3D conn.prepareStatement(
      =
      "UPSERT INTO=
 " + myTable + " VALUES(?, ?, ?, ?)");
    myTableUpsertStmt.setSt=
ring(1, "X001");
    myTableUpsertSt=
mt.setString(2, "GROUP");
    myTabl=
eUpsertStmt.setString(3, null);
    myTableUps=
ertStmt.setBoolean(4, false);
    myTable=
UpsertStmt.execute();
    myTableUpsertStmt.setString(1, "X001");
    myTableUpsertStmt.setString(2, "PEOPLE");
    myTableUpsertStmt.setString(=
3, null);
    myTableUpsertStmt.setBoolean(4, false);
    myTableUpsertStmt.execute();<=
br>    myTableUpsertStmt.setString(1, "X003");
    myTableUpsertStmt.setString(2, "PEOP=
LE");
    myTableUpsertStmt.setString(3, =
null);
    myTableUpsertStmt.setBoolean(4, false);
    myTableUpsertStmt.execute();
    myTableUpsertSt=
mt.setString(1, "X002");
    myTable=
UpsertStmt.setString(2, "PEOPLE");
 =
   myTableUpsertStmt.setString(3,=
 "Z990");
    myTableUpsertStmt.setBoolean(=
4, false);
    myTableUpsertStmt.execute();
    conn.commit();

   =
 String query1 =3D &quo=
t;SELECT /*+ USE_SORT_MERGE_JOIN*/ COUNT(*)\n" +
            "FROM " + peopleTable + " ds JOIN " + myT=
able + " l<=
span style=3D"color:rgb(0,0,128);font-weight:bold">\n" +
            "ON ds.PERSON_ID =3D l.LO=
CALID\n" +
      =
      "WHERE l.EID=
 IS NULL AND l.DSID =3D 'PEOPLE' AND l.HAS_CANDIDATES =3D FALSE&quo=
t;;
    String query2 =3D "SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*)\n" +
            "FROM (SELECT LOCALID FROM &q=
uot; + myTable + "\n=
" +
    =
        "WHERE EID=
 IS NULL AND DSID =3D 'PEOPLE' AND HAS_CANDIDATES =3D FALSE) l\n" +
            "JOIN " +=
 peopleTable + " d=
s ON ds.PERSON_ID =3D l.LOCALID";
    String query3 =3D "SELECT /*+ USE_SORT_M=
ERGE_JOIN */ COUNT(*)\n" +
            &=
quot;FROM " + myTable + " t1 JOIN " + myTable + " t2\n" +
            "ON t1.DSID =3D t2.DSID\n" +
            "WHERE t1.LOCALID =3D 'X001' AND t2.LO=
CALID =3D 'X002'";

    for (String q : new String[]{query1, query2, query3}) {        ResultSet rs =3D conn.createStatement().executeQuery("explain " + =
q);
        String plan =3D QueryUtil.=
getExplainPlan(rs);
        ass=
ertFalse("T=
ables should not be sorted over their PKs:\n" + plan,
                plan.contains("SERVER SORTED BY"));

        rs =3D conn.createStatement().executeQuery(q);<=
br>        assertTrue(rs.next());<=
br>        //asser=
tEquals(2, rs.getInt(1));
        assertFa=
lse(rs.next());
    }
}


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

=
Would you mind sharing the EXPLAIN output you are getting?
<= br>
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 b= uilding against the 4.14.0-rc0 tag. This produced 4.14.0 for phoenix-core.j= ar, but all the other jars produced are 4.13.1, including the client jar. W= hen I deploy these jars on EMR and run queries via sqlline, I get a NoClass= DefFoundError.

Are the plans to provide 4.13.2-HBa= se as a public download?

Thanks,
Gerald<= /div>

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

I c= hecked 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, Mar= yann Xue <maryann.xue@gmail.com> wrote:
Sorry for the late response. Yes, sure, I will try it rig= ht away.

<= div class=3D"gmail_quote">
On Wed, May 16, 2018 at 9:40 AM = Gerald Sangudi <gsangudi@23andme.com> 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 p= ossible for you to try it on 4.13.1?

Thanks,
=
Gerald

On Fri, May 11, 2018 at 10:52 AM, Gerald Sangudi &l= t;gsangudi@23andm= e.com> wrote:
Hi Maryann,

Thanks for verifying against latest. How= ever, I did not detect the fix in Phoenix 4.13.1. AWS EMR currently provide= s 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, Maryan= n Xue <maryann.xue@gmail.com> wrote:
Hi Gerald,

I have verifi= ed against latest Phoenix code that this problem has been fixed. I have als= o checked Phoenix 4.13 release tags. Looks like all versions of 4.13 packag= es now include that fix. Would you mind getting the latest Phoenix-4.13 pac= kage and testing it again? Thank you!


Thanks,
Maryann

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

I'm running Phoenix 4.13 on AWS EMR and ge= tting the following EXPLAIN plan:

Table:

CREATE TABLE salted (

=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0keyA BIGINT NOT NULL,

=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 pk PRIMARY KEY (ke= yA, keyB)

)

SALT_BUCKETS = =3D 64;


EXPLAIN= :

EXPLAIN

SELECT /*+ USE_SORT_MERGE_JOIN */

COUNT(*) c

FROM salted t1 JOIN salted t2

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

WHERE t1.keyA =3D = 10

AND t2.keyA =3D = 20;


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

| =C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=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| n= ull | |

= | =C2=A0=C2=A0=C2=A0=C2=A0CLIENT 64-CHUNK PARALLEL 64-WAY RANGE SCAN OVER S= ALTED [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=A0CL= IENT 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 64-CHUNK PARALLEL 64-WAY RANGE SCAN OVER= SALTED [0,20] - [63,20] =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= =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 AGGREG= ATE 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 = | |

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



In the EXPLAIN o= utput, 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.K= EYB.

When I EXPLAIN the same query on a non-salted table, ne= ither T1.KEYB nor T2.KEYB is re-sorted. I'm hoping the sort is unnecess= ary. 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.

Tha= nks,
Gerald









--0000000000006f44ce056c6fec2d--