[
https://issues.apache.org/jira/browse/PHOENIX-1077?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14057707#comment-14057707
]
Eli Levine commented on PHOENIX-1077:
-------------------------------------
Dug around some more. This issue is not related to views or multi-tenancy but
looks like a bug in how multi-column IN clauses interact with AND clauses in
WHERE statements.
For example, the above test works fine if the IN clause contains a single
column:
{code}
public void testRVCOnTenantSpecificTable() throws Exception {
Connection conn = nextConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL);
try {
conn.setAutoCommit(true);
conn.createStatement().executeUpdate("upsert into " +
TENANT_TABLE_NAME + " (id, user) values (1, 'BonA')");
conn.createStatement().executeUpdate("upsert into " +
TENANT_TABLE_NAME + " (id, user) values (2, 'BonB')");
conn.createStatement().executeUpdate("upsert into " +
TENANT_TABLE_NAME + " (id, user) values (3, 'BonC')");
conn.close();
conn = nextConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL);
PreparedStatement stmt = conn.prepareStatement("select id from " +
TENANT_TABLE_NAME + " WHERE user IN (?, ?,?))");
stmt.setString(1, "BonA");
stmt.setString(2, "BonB");
stmt.setString(3, "BonC");
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
{code}
Additionally, I was able to reproduce the bug without views or multi-tenant
views with the following:
{code}
CREATE TABLE in_test ( user VARCHAR, tenant_id VARCHAR(5) NOT
NULL,tenant_type_id VARCHAR(3) NOT NULL, id INTEGER NOT NULL CONSTRAINT pk
PRIMARY KEY (tenant_id, tenant_type_id, id))
{code}
{code}
upsert into in_test (tenant_id, tenant_type_id, id, user) values ('a', 'a', 1,
'BonA')
upsert into in_test (tenant_id, tenant_type_id, id, user) values ('a', 'a', 2,
'BonB')
{code}
{code}
select id from in_test WHERE tenant_id = 'a' and tenant_type_id = 'a' and ((id,
user) IN ((1, 'BonA'),(1, 'BonA')))
{code}
The select is expected to return a single row but returns none. However, if
{{tenant_id = 'a' and}} is removed, the expected row is returned.
[~samarthjain] [~jamestaylor]
> IN list of row value constructors doesn't work for tenant specific views
> ------------------------------------------------------------------------
>
> Key: PHOENIX-1077
> URL: https://issues.apache.org/jira/browse/PHOENIX-1077
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 3.0.0, 4.0.0, 5.0.0
> Reporter: Samarth Jain
> Assignee: Eli Levine
>
> IN list of row value constructors doesn't work when queried against tenant
> views for multi-tenant phoenix tables. Consider this test (added in
> TenantSpecificTablesDMLIT.java)
> {code}
> public void testRVCOnTenantSpecificTable() throws Exception {
> Connection conn = nextConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL);
> try {
> conn.setAutoCommit(true);
> conn.createStatement().executeUpdate("upsert into " +
> TENANT_TABLE_NAME + " (id, user) values (1, 'BonA')");
> conn.createStatement().executeUpdate("upsert into " +
> TENANT_TABLE_NAME + " (id, user) values (2, 'BonB')");
> conn.createStatement().executeUpdate("upsert into " +
> TENANT_TABLE_NAME + " (id, user) values (3, 'BonC')");
> conn.close();
> conn = nextConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL);
> PreparedStatement stmt = conn.prepareStatement("select id from "
> + TENANT_TABLE_NAME + " WHERE (id, user) IN ((?, ?), (?, ?), (?, ?))");
> stmt.setInt(1, 1);
> stmt.setString(2, "BonA");
> stmt.setInt(3, 2);
> stmt.setString(4, "BonB");
> stmt.setInt(5, 3);
> stmt.setString(6, "BonC");
> ResultSet rs = stmt.executeQuery();
> assertTrue(rs.next());
> assertEquals(1, rs.getInt(1));
> assertTrue(rs.next());
> assertEquals(2, rs.getInt(1));
> assertTrue(rs.next());
> assertEquals(3, rs.getInt(1));
> assertFalse(rs.next());
> }
> finally {
> conn.close();
> }
> }
> {code}
> Replacing TENANT_TABLE_NAME with PARENT_TABLE_NAME (that is the base table),
> the test works fine.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
