[
https://issues.apache.org/jira/browse/PHOENIX-115?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Samarth updated PHOENIX-115:
----------------------------
Description:
Consider the following query:
SELECT * FROM TEST_TABLE WHERE TEST_ID IN ('9FIxx00000000oT',
'9FIxx00000000oS')
The rows returned are in the order 9FIxx00000000oS and 9FIxx00000000oT. This
could be problematic in cases when the IN clause is formed dynamically by
fetching the ids from another query and the output of the above query is
expected to be in the order of IDs provided in the IN clause.
Test case to exhibit the problem. It uses row value constructor. Although as
illustrated above, the problem is not confined to RVC.
Please add the test below in RowValueConstructorTest.java.
{code}
@Test
public void testQueryMoreWithInListRowValueConstructor() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
Date date = new Date(System.currentTimeMillis());
initEntityHistoryTableValues(tenantId, getDefaultSplits(tenantId),
date, ts);
Properties props = new Properties(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts +
2));
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
PreparedStatement statement = conn.prepareStatement("select parent_id
from " + ENTITY_HISTORY_TABLE_NAME +
" WHERE (organization_id, parent_id, created_date,
entity_history_id) IN ((?, ?, ?, ?),(?,?,?,?))");
statement.setString(1, tenantId);
statement.setString(2, PARENTID7);
statement.setDate(3, date);
statement.setString(4, ENTITYHISTID7);
statement.setString(5, tenantId);
statement.setString(6, PARENTID3);
statement.setDate(7, date);
statement.setString(8, ENTITYHISTID3);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(PARENTID7, rs.getString(1));
assertTrue(rs.next());
assertEquals(PARENTID3, rs.getString(1));
assertFalse(rs.next());
}
{code}
was:
Consider the following query:
SELECT * FROM TEST_TABLE WHERE TEST_ID IN ('9FIxx00000000oT',
'9FIxx00000000oS')
The rows returned are in the order 9FIxx00000000oS and 9FIxx00000000oT. This
could be problematic in cases when the IN clause is formed dynamically by
fetching the ids from another query and the output of the above query is
expected to be in the order of IDs provided in the IN clause.
Test case to exhibit the problem. It uses row value constructor. Although as
illustrated above, the problem is not confined to RVC.
Please add the test below in RowValueConstructorTest.java.
@Test
public void testQueryMoreWithInListRowValueConstructor() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
Date date = new Date(System.currentTimeMillis());
initEntityHistoryTableValues(tenantId, getDefaultSplits(tenantId),
date, ts);
Properties props = new Properties(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts +
2));
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
PreparedStatement statement = conn.prepareStatement("select parent_id
from " + ENTITY_HISTORY_TABLE_NAME +
" WHERE (organization_id, parent_id, created_date,
entity_history_id) IN ((?, ?, ?, ?),(?,?,?,?))");
statement.setString(1, tenantId);
statement.setString(2, PARENTID7);
statement.setDate(3, date);
statement.setString(4, ENTITYHISTID7);
statement.setString(5, tenantId);
statement.setString(6, PARENTID3);
statement.setDate(7, date);
statement.setString(8, ENTITYHISTID3);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(PARENTID7, rs.getString(1));
assertTrue(rs.next());
assertEquals(PARENTID3, rs.getString(1));
assertFalse(rs.next());
}
> IN clause doesn't return the matching rows in the order they were given in
> the clause
> -------------------------------------------------------------------------------------
>
> Key: PHOENIX-115
> URL: https://issues.apache.org/jira/browse/PHOENIX-115
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 3.0.0
> Reporter: Samarth
> Assignee: James Taylor
>
> Consider the following query:
> SELECT * FROM TEST_TABLE WHERE TEST_ID IN ('9FIxx00000000oT',
> '9FIxx00000000oS')
> The rows returned are in the order 9FIxx00000000oS and 9FIxx00000000oT. This
> could be problematic in cases when the IN clause is formed dynamically by
> fetching the ids from another query and the output of the above query is
> expected to be in the order of IDs provided in the IN clause.
> Test case to exhibit the problem. It uses row value constructor. Although as
> illustrated above, the problem is not confined to RVC.
> Please add the test below in RowValueConstructorTest.java.
> {code}
> @Test
> public void testQueryMoreWithInListRowValueConstructor() throws Exception
> {
> long ts = nextTimestamp();
> String tenantId = getOrganizationId();
> Date date = new Date(System.currentTimeMillis());
> initEntityHistoryTableValues(tenantId, getDefaultSplits(tenantId),
> date, ts);
> Properties props = new Properties(TEST_PROPERTIES);
> props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts
> + 2));
> Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL,
> props);
>
> PreparedStatement statement = conn.prepareStatement("select parent_id
> from " + ENTITY_HISTORY_TABLE_NAME +
> " WHERE (organization_id, parent_id, created_date,
> entity_history_id) IN ((?, ?, ?, ?),(?,?,?,?))");
> statement.setString(1, tenantId);
> statement.setString(2, PARENTID7);
> statement.setDate(3, date);
> statement.setString(4, ENTITYHISTID7);
> statement.setString(5, tenantId);
> statement.setString(6, PARENTID3);
> statement.setDate(7, date);
> statement.setString(8, ENTITYHISTID3);
>
> ResultSet rs = statement.executeQuery();
>
> assertTrue(rs.next());
> assertEquals(PARENTID7, rs.getString(1));
> assertTrue(rs.next());
> assertEquals(PARENTID3, rs.getString(1));
> assertFalse(rs.next());
> }
> {code}
--
This message was sent by Atlassian JIRA
(v6.2#6252)
