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 18:00: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 createMetadata = "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(createMetadata));
        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 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}


> 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 createMetadata = "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(createMetadata));
>         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