[jira] [Created] (PHOENIX-1182) UPSERT SELECT not working with joins

["Samarth Jain (JIRA)" <ji...@apache.org>]

Samarth Jain created PHOENIX-1182:
-------------------------------------

             Summary: UPSERT SELECT not working with joins
                 Key: PHOENIX-1182
                 URL: https://issues.apache.org/jira/browse/PHOENIX-1182
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 5.0.0, 3.1, 4.1
            Reporter: Samarth Jain
            Assignee: Maryann Xue


{code}

@Test
    public void testUpsertSelectWithJoin() throws Exception {
        String ddl1 = "CREATE TABLE IF NOT EXISTS SOURCE_TABLE(" +
                       "TID CHAR(3) NOT NULL," + 
                       "A UNSIGNED_INT NOT NULL," +
                       "B UNSIGNED_INT NOT NULL " + 
                       "CONSTRAINT pk PRIMARY KEY (TID, A, B))";
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl1);
        conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 1, 1)");
        conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 1, 2)");
        conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 1, 3)");
        conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 2, 1)");
        conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 2, 2)");
        conn.commit();
        
        String ddl2 = "CREATE TABLE IF NOT EXISTS JOIN_TABLE(" + 
                "TID CHAR(3) NOT NULL," + 
                "A UNSIGNED_INT NOT NULL," +  
                "B UNSIGNED_INT NOT NULL," +
                "COUNT UNSIGNED_INT " +  
                "CONSTRAINT pk PRIMARY KEY (TID, A, B))";
        conn.createStatement().execute(ddl2);
        
        String upsertSelect = "UPSERT INTO JOIN_TABLE(TID, A, B, COUNT)" + 
                              "SELECT t1.TID, " + 
                              "t1.A, " + 
                              "t2.A, " + 
                              "COUNT(*) " + 
                              "FROM SOURCE_TABLE t1 " + 
                              "INNER JOIN SOURCE_TABLE t2 ON t1.B = t2.B " + 
                              "WHERE t1.A != t2.A " + 
                              "AND t1.TID = '1' " + 
                              "AND t2.TID = '1' " + 
                              "GROUP BY " +
                              "t1.TID, t1.A,t2.A";
        
        conn.createStatement().execute(upsertSelect);

    }

{code}



--
This message was sent by Atlassian JIRA
(v6.2#6252)