Maryann Xue created PHOENIX-2815:
------------------------------------
Summary: Got wrong data using local view index on multi-tenant
tables
Key: PHOENIX-2815
URL: https://issues.apache.org/jira/browse/PHOENIX-2815
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.7.0
Reporter: Maryann Xue
1. The test used stats, so this bug might be stats related.
2. Global view index does NOT have this issue.
DDL and DML:
{code}
protected void initMultiTenantTables(String index) throws SQLException {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
conn.createStatement().execute(
"CREATE TABLE " + MULTI_TENANT_TABLE + " (tenant_id VARCHAR
NOT NULL, id VARCHAR NOT NULL, col0 INTEGER, col1 INTEGER, col2 INTEGER
CONSTRAINT pk PRIMARY KEY (tenant_id, id)) MULTI_TENANT=true");
PreparedStatement stmt = conn.prepareStatement(
"UPSERT INTO " + MULTI_TENANT_TABLE
+ " VALUES(?, ?, ?, ?, ?)");
DecimalFormat formatter = new DecimalFormat("0000");
for (int i = 0; i < 1000; i++) {
stmt.setString(1, "10");
stmt.setString(2, formatter.format(2 + i));
stmt.setInt(3, 3 + i);
stmt.setInt(4, 4 + i);
stmt.setInt(5, 5 + i);
stmt.execute();
}
for (int i = 0; i < 1000; i++) {
stmt.setString(1, "15");
stmt.setString(2, formatter.format(3 + i));
stmt.setInt(3, 4 + i);
stmt.setInt(4, 5 + i);
stmt.setInt(5, 6 + i);
stmt.execute();
}
for (int i = 0; i < 1000; i++) {
stmt.setString(1, "20");
stmt.setString(2, formatter.format(4 + i));
stmt.setInt(3, 5 + i);
stmt.setInt(4, 6 + i);
stmt.setInt(5, 7 + i);
stmt.execute();
}
conn.commit();
if (index != null) {
conn.createStatement().execute(
"CREATE " + index + " " + MULTI_TENANT_TABLE_INDEX
+ " ON " + MULTI_TENANT_TABLE + "(col1) INCLUDE (col0,
col2)");
conn.commit();
}
conn.close();
props.setProperty("TenantId", "10");
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("CREATE VIEW " + MULTI_TENANT_VIEW1
+ " AS select * from " + MULTI_TENANT_TABLE);
conn.commit();
if (index != null) {
conn.createStatement().execute(
"CREATE " + index + " " + MULTI_TENANT_VIEW1_INDEX
+ " ON " + MULTI_TENANT_VIEW1 + "(col0)");
conn.commit();
}
conn.close();
props.setProperty("TenantId", "20");
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("CREATE VIEW " + MULTI_TENANT_VIEW2
+ " AS select * from " + MULTI_TENANT_TABLE + " where col2
> 7");
conn.commit();
if (index != null) {
conn.createStatement().execute(
"CREATE " + index + " " + MULTI_TENANT_VIEW2_INDEX
+ " ON " + MULTI_TENANT_VIEW2 + "(col0)");
conn.commit();
}
} catch (TableAlreadyExistsException e) {
} finally {
conn.close();
}
}
{code}
SQL:
{code}
"select id, col0 from " + MULTI_TENANT_VIEW1 + " where col0 >= 1000"
{code}
ACTUAL RESULT:
{"0998", 1000},// This should not EXIST in the table at
all
{"0999", 1000},
{"1000", 1001},
{"1001", 1002}
EXPECTED:
{"0999", 1000},
{"1000", 1001},
{"1001", 1002}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
