phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andrew Khor (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-4982) Query Fails For Join with Order By that is fully qualified
Date Tue, 23 Oct 2018 17:54:00 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-4982?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Andrew Khor updated PHOENIX-4982:
---------------------------------
    Description: 
{code:java}
@Test public void testJoinWithOrderBy() throws Exception { String metadata = "metadata"; String
uuidMap = "uuidMap"; String properties = "properties"; String createMetadta = "CREATE TABLE
\"metadata\" (\n" + " \"groupKey\" VARCHAR PRIMARY KEY\n" + ")\n" + "SALT_BUCKETS = 256,\n"
+ "COLUMN_ENCODED_BYTES = 0"; String createUuidMap = "CREATE TABLE \"uuidMap\" (\n" + " \"uuid\"
VARCHAR PRIMARY KEY ,\n" + " \"typeKey\" VARCHAR ,\n" + " \"groupKey\" VARCHAR\n" + ")"; List<Column>
viewColumns = Arrays.asList( new Column("c_FQ","VARCHAR"), new Column("c_Fg","BIGINT"), new
Column("c_Fw","VARCHAR"), new Column("c_GA","BIGINT"), new Column("c_GQ","VARCHAR"), new Column("c_Gg","VARCHAR"),
new Column("c_Gw","VARCHAR")); StringBuilder viewColCommaSep = new StringBuilder("\"groupKey\"");
StringBuilder viewColCommaSepQualified = new StringBuilder("\"").append(properties).append("\".\"groupKey\"");
StringBuilder viewColWithTypeCommaSep = new StringBuilder("\"groupKey\" VARCHAR"); StringBuilder
valueStr = new StringBuilder(); viewColumns.forEach(col -> { viewColCommaSep.append(",\"").append(col.name).append("\"");
viewColCommaSepQualified.append(",\"").append(properties).append("\".\"").append(col.name).append("\"");
viewColWithTypeCommaSep.append(",\"").append(col.name).append("\"").append(" ").append(col.type);
valueStr.append(",").append(col.type.equalsIgnoreCase("BIGINT") ? System.currentTimeMillis()
: "'Some'"); }); String createProperties = String.format("CREATE VIEW \"%s\" (%s) AS SELECT
* FROM \"%s\" ", properties,viewColWithTypeCommaSep, metadata); List<String> columnNames
= viewColumns.stream().map(col-> col.name).collect(Collectors.toList()); List<String>
groupKeys= Arrays.asList("file_123","file_345"); Map<String,Object> valueMap=new HashMap<>();
try (Connection conn = DriverManager.getConnection(CONN_STRING); Statement stmt = conn.createStatement())
{ conn.setAutoCommit(true); assertFalse(stmt.execute("DROP TABLE IF EXISTS " + metadata));
assertFalse(stmt.execute("DROP TABLE IF EXISTS " + uuidMap)); assertFalse(stmt.execute("DROP
TABLE IF EXISTS " + properties)); assertFalse(stmt.execute(createMetadta)); assertFalse(stmt.execute(createUuidMap));
assertFalse(stmt.execute(createProperties)); groupKeys.forEach(grpKey ->{ try { stmt.execute("UPSERT
INTO \"" + properties + "\"(" +viewColCommaSep+ ") VALUES ('"+grpKey+"'"+valueStr+") "); stmt.execute("UPSERT
INTO \"" + uuidMap + "\"(\"uuid\",\"typeKey\",\"groupKey\") VALUES ('"+UUID.randomUUID()+"','properties','"+grpKey+"')
"); } catch (Exception e){ throw new RuntimeException(e); } }); Statement statement = null;
ResultSet rs = null; PreparedStatement ps = null; String sql = "SELECT "+viewColCommaSepQualified.toString()+"
"+ "FROM \"uuidMap\" JOIN \"properties\" ON (\"properties\".\"groupKey\" = \"uuidMap\".\"groupKey\")\n"
+ "WHERE \"uuidMap\".\"typeKey\" = 'properties'\n" + " ORDER BY \"c_GA\" ASC\n" + "LIMIT 100";
ps = conn.prepareStatement(sql); rs = ps.executeQuery(); assertTrue(rs.next()); } } class
Column { String name; String type; public Column(String name, String type) { this.name = name;
this.type = type; } }{code}

  was:
{code:java}
@Test
public void testJoinWithOrderBy() throws Exception {
    String tableA = "A";
    String tableB = "B";
    String createA = "CREATE TABLE \"" + tableA + "\" (\n" +
            "  \"a1\"   VARCHAR PRIMARY KEY ,\n" +
            "  \"a2\"   VARCHAR             \n" +
            ")";
    String createB = "CREATE TABLE \"" + tableB + "\" (\n" +
            "  \"b1\"   VARCHAR PRIMARY KEY ,\n" +
            "  \"b2\"   VARCHAR             \n" +
            ")\n";
    try (Connection conn = DriverManager.getConnection(CONN_STRING);
         Statement stmt = conn.createStatement()) {
        conn.setAutoCommit(false);
        assertFalse(stmt.execute("DROP TABLE IF EXISTS " + tableA));
        assertFalse(stmt.execute("DROP TABLE IF EXISTS " + tableB));
        assertFalse(stmt.execute(createA));
        assertFalse(stmt.execute(createB));
        Statement statement = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "SELECT \"" + tableA + "\".\"a2\" " +
                "FROM \"" + tableA + "\" JOIN \"" + tableB + "\" ON (\"" + tableA + "\".\"a1\"
= \"" + tableB + "\".\"b1\") " +
                "WHERE (\"" + tableB + "\".\"b2\" = ?) " + 
                "ORDER BY \""+ tableA + "\".\"a2\" ASC";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        assertFalse(rs.next());
    } 
}
{code}
See above for a test case.


> Query Fails For Join with Order By that is fully qualified
> ----------------------------------------------------------
>
>                 Key: PHOENIX-4982
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-4982
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: Andrew Khor
>            Priority: Major
>
> {code:java}
> @Test public void testJoinWithOrderBy() throws Exception { String metadata = "metadata";
String uuidMap = "uuidMap"; String properties = "properties"; String createMetadta = "CREATE
TABLE \"metadata\" (\n" + " \"groupKey\" VARCHAR PRIMARY KEY\n" + ")\n" + "SALT_BUCKETS =
256,\n" + "COLUMN_ENCODED_BYTES = 0"; String createUuidMap = "CREATE TABLE \"uuidMap\" (\n"
+ " \"uuid\" VARCHAR PRIMARY KEY ,\n" + " \"typeKey\" VARCHAR ,\n" + " \"groupKey\" VARCHAR\n"
+ ")"; List<Column> viewColumns = Arrays.asList( new Column("c_FQ","VARCHAR"), new Column("c_Fg","BIGINT"),
new Column("c_Fw","VARCHAR"), new Column("c_GA","BIGINT"), new Column("c_GQ","VARCHAR"), new
Column("c_Gg","VARCHAR"), new Column("c_Gw","VARCHAR")); StringBuilder viewColCommaSep = new
StringBuilder("\"groupKey\""); StringBuilder viewColCommaSepQualified = new StringBuilder("\"").append(properties).append("\".\"groupKey\"");
StringBuilder viewColWithTypeCommaSep = new StringBuilder("\"groupKey\" VARCHAR"); StringBuilder
valueStr = new StringBuilder(); viewColumns.forEach(col -> { viewColCommaSep.append(",\"").append(col.name).append("\"");
viewColCommaSepQualified.append(",\"").append(properties).append("\".\"").append(col.name).append("\"");
viewColWithTypeCommaSep.append(",\"").append(col.name).append("\"").append(" ").append(col.type);
valueStr.append(",").append(col.type.equalsIgnoreCase("BIGINT") ? System.currentTimeMillis()
: "'Some'"); }); String createProperties = String.format("CREATE VIEW \"%s\" (%s) AS SELECT
* FROM \"%s\" ", properties,viewColWithTypeCommaSep, metadata); List<String> columnNames
= viewColumns.stream().map(col-> col.name).collect(Collectors.toList()); List<String>
groupKeys= Arrays.asList("file_123","file_345"); Map<String,Object> valueMap=new HashMap<>();
try (Connection conn = DriverManager.getConnection(CONN_STRING); Statement stmt = conn.createStatement())
{ conn.setAutoCommit(true); assertFalse(stmt.execute("DROP TABLE IF EXISTS " + metadata));
assertFalse(stmt.execute("DROP TABLE IF EXISTS " + uuidMap)); assertFalse(stmt.execute("DROP
TABLE IF EXISTS " + properties)); assertFalse(stmt.execute(createMetadta)); assertFalse(stmt.execute(createUuidMap));
assertFalse(stmt.execute(createProperties)); groupKeys.forEach(grpKey ->{ try { stmt.execute("UPSERT
INTO \"" + properties + "\"(" +viewColCommaSep+ ") VALUES ('"+grpKey+"'"+valueStr+") "); stmt.execute("UPSERT
INTO \"" + uuidMap + "\"(\"uuid\",\"typeKey\",\"groupKey\") VALUES ('"+UUID.randomUUID()+"','properties','"+grpKey+"')
"); } catch (Exception e){ throw new RuntimeException(e); } }); Statement statement = null;
ResultSet rs = null; PreparedStatement ps = null; String sql = "SELECT "+viewColCommaSepQualified.toString()+"
"+ "FROM \"uuidMap\" JOIN \"properties\" ON (\"properties\".\"groupKey\" = \"uuidMap\".\"groupKey\")\n"
+ "WHERE \"uuidMap\".\"typeKey\" = 'properties'\n" + " ORDER BY \"c_GA\" ASC\n" + "LIMIT 100";
ps = conn.prepareStatement(sql); rs = ps.executeQuery(); assertTrue(rs.next()); } } class
Column { String name; String type; public Column(String name, String type) { this.name = name;
this.type = type; } }{code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message