[
https://issues.apache.org/jira/browse/PHOENIX-4982?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Andrew Khor updated PHOENIX-4982:
---------------------------------
Comment: was deleted
(was: Fixed by https://issues.apache.org/jira/browse/PHOENIX-4933)
> 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)
