[
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)
