[jira] [Issue Comment Deleted] (PHOENIX-3451) Secondary index and query using distinct: LIMIT doesn't return the first rows
[
https://issues.apache.org/jira/browse/PHOENIX-3451?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-3451:
--
Comment: was deleted
(was: [~jamestaylor].thank your for your suggestion, my considerations are as
follows:
1. If GroupBy is "GROUP BY pkCol1 + 1, TRUNC(pkCol2)", the OrderBy must be
"ORDER BY pkCol1 + 1" or "ORDER BY TRUNC(pkCol2)", the OrderBy columns must
match the GroupBy columns.
2. Only when all GROUP BY/Order BY expressions are simple RowKey Columns (i.e.
GROUP BY pkCol1, pkCol2 or OrderBy BY pkCol1, pkCol2) , we have necessary to go
further to check if the GROUP BY/Order BY is "isOrderPreserving". If GROUP
BY/Order BY expressions are not simple RowKey Columns(i.e.GROUP BY pkCol1 + 1,
TRUNC(pkCol2) or ORDER BY pkCol1 + 1, TRUNC(pkCol2)), surely the GROUP
BY/Order BY should not be "isOrderPreserving".
Take the following SQL as a example,the GROUP BY and ORDER BY are certainly not
"isOrderPreserving" :
select pkCol1 + 1,TRUNC(pkCol2) from table group by pkCol1 + 1, TRUNC(pkCol2)
order by pkCol1 + 1, TRUNC(pkCol2)
So I think my patch is Ok, just as the following code explained, it just needs
to only conside the RowKeyColumnExpression. RowKeyColumnExpression is enough
for checking if the Order BY is "isOrderPreserving",for other type of
Expression, the following visit method return null, and the
OrderPreservingTracker.isOrderPreserving method will return false,which is as
expected.
{code:borderStyle=solid}
@Override
public Info visit(RowKeyColumnExpression node) {
if(groupBy==null || groupBy.isEmpty()) {
return new Info(node.getPosition());
}
int pkPosition=node.getPosition();
assert pkPosition < groupBy.getExpressions().size();
Expression
groupByExpression=groupBy.getExpressions().get(pkPosition);
if(!(groupByExpression instanceof RowKeyColumnExpression)) {
return null;
}
int
orginalPkPosition=((RowKeyColumnExpression)groupByExpression).getPosition();
return new Info(orginalPkPosition);
}
{code}
By the way, I had already considered the modification as same as your
suggestion when I made my patch, finally I select current patch because it is
more simpler ,and the modification is just restricted in the single
OrderPreservingTracker class,FYI.)
> Secondary index and query using distinct: LIMIT doesn't return the first rows
> -
>
> Key: PHOENIX-3451
> URL: https://issues.apache.org/jira/browse/PHOENIX-3451
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.8.0
>Reporter: Joel Palmert
>Assignee: chenglei
> Attachments: PHOENIX-3451.diff
>
>
> This may be related to PHOENIX-3452 but the behavior is different so filing
> it separately.
> Steps to repro:
> CREATE TABLE IF NOT EXISTS TEST.TEST (
> ORGANIZATION_ID CHAR(15) NOT NULL,
> CONTAINER_ID CHAR(15) NOT NULL,
> ENTITY_ID CHAR(15) NOT NULL,
> SCORE DOUBLE,
> CONSTRAINT TEST_PK PRIMARY KEY (
> ORGANIZATION_ID,
> CONTAINER_ID,
> ENTITY_ID
> )
> ) VERSIONS=1, MULTI_TENANT=TRUE, REPLICATION_SCOPE=1, TTL=31536000;
> CREATE INDEX IF NOT EXISTS TEST_SCORE ON TEST.TEST (CONTAINER_ID, SCORE DESC,
> ENTITY_ID DESC);
> UPSERT INTO test.test VALUES ('org2','container2','entityId6',1.1);
> UPSERT INTO test.test VALUES ('org2','container1','entityId5',1.2);
> UPSERT INTO test.test VALUES ('org2','container2','entityId4',1.3);
> UPSERT INTO test.test VALUES ('org2','container1','entityId3',1.4);
> UPSERT INTO test.test VALUES ('org2','container3','entityId7',1.35);
> UPSERT INTO test.test VALUES ('org2','container3','entityId8',1.45);
> EXPLAIN
> SELECT DISTINCT entity_id, score
> FROM test.test
> WHERE organization_id = 'org2'
> AND container_id IN ( 'container1','container2','container3' )
> ORDER BY score DESC
> LIMIT 2
> OUTPUT
> entityId51.2
> entityId31.4
> The expected out out would be
> entityId81.45
> entityId31.4
> You will get the expected output if you remove the secondary index from the
> table or remove distinct from the query.
> As described in PHOENIX-3452 if you run the query without the LIMIT the
> ordering is not correct. However, the 2first results in that ordering is
> still not the onces returned by the limit clause, which makes me think there
> are multiple issues here and why I filed both separately. The rows being
> returned are the ones assigned to container1. It looks like Phoenix is first
> getting the rows from the first container and when it finds that to be enough
> it stops the scan. What it should be doing is getting 2 results for each
> container and then merge then and then limit again.
--
This
[jira] [Issue Comment Deleted] (PHOENIX-3451) Secondary index and query using distinct: LIMIT doesn't return the first rows
[
https://issues.apache.org/jira/browse/PHOENIX-3451?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-3451:
--
Comment: was deleted
(was: I wrote the IT Test as [~jpalmert] described, but It seems in 4.8.0 the
test result is ok.I can not reproduce the bug.
My explain is(T01 is the data table,and T02 is index) :
{code:borderStyle=solid}
explain SELECT DISTINCT entity_id, score FROM T01 WHERE organization_id =
'org2' AND container_id IN ( 'container1','container2','container3' ) ORDER BY
score DESC LIMIT 2;
+---+
| PLAN
|
+---+
| CLIENT 1-CHUNK PARALLEL 1-WAY SKIP SCAN ON 3 KEYS OVER T02 ['container1
'] - ['container3 '] |
| SERVER FILTER BY FIRST KEY ONLY AND "ORGANIZATION_ID" = 'org2'
|
| SERVER AGGREGATE INTO DISTINCT ROWS BY ["ENTITY_ID", "SCORE"]
|
| CLIENT MERGE SORT
|
| CLIENT TOP 2 ROWS SORTED BY ["SCORE" DESC]
|
+---+
{code}
PHOENIX-3452 indeed can reproduce, I fixed the PHOENIX-3452)
> Secondary index and query using distinct: LIMIT doesn't return the first rows
> -
>
> Key: PHOENIX-3451
> URL: https://issues.apache.org/jira/browse/PHOENIX-3451
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.8.0
>Reporter: Joel Palmert
>Assignee: chenglei
>
> This may be related to PHOENIX-3452 but the behavior is different so filing
> it separately.
> Steps to repro:
> CREATE TABLE IF NOT EXISTS TEST.TEST (
> ORGANIZATION_ID CHAR(15) NOT NULL,
> CONTAINER_ID CHAR(15) NOT NULL,
> ENTITY_ID CHAR(15) NOT NULL,
> SCORE DOUBLE,
> CONSTRAINT TEST_PK PRIMARY KEY (
> ORGANIZATION_ID,
> CONTAINER_ID,
> ENTITY_ID
> )
> ) VERSIONS=1, MULTI_TENANT=TRUE, REPLICATION_SCOPE=1, TTL=31536000;
> CREATE INDEX IF NOT EXISTS TEST_SCORE ON TEST.TEST (CONTAINER_ID, SCORE DESC,
> ENTITY_ID DESC);
> UPSERT INTO test.test VALUES ('org2','container2','entityId6',1.1);
> UPSERT INTO test.test VALUES ('org2','container1','entityId5',1.2);
> UPSERT INTO test.test VALUES ('org2','container2','entityId4',1.3);
> UPSERT INTO test.test VALUES ('org2','container1','entityId3',1.4);
> UPSERT INTO test.test VALUES ('org2','container3','entityId7',1.35);
> UPSERT INTO test.test VALUES ('org2','container3','entityId8',1.45);
> EXPLAIN
> SELECT DISTINCT entity_id, score
> FROM test.test
> WHERE organization_id = 'org2'
> AND container_id IN ( 'container1','container2','container3' )
> ORDER BY score DESC
> LIMIT 2
> OUTPUT
> entityId51.2
> entityId31.4
> The expected out out would be
> entityId81.45
> entityId31.4
> You will get the expected output if you remove the secondary index from the
> table or remove distinct from the query.
> As described in PHOENIX-3452 if you run the query without the LIMIT the
> ordering is not correct. However, the 2first results in that ordering is
> still not the onces returned by the limit clause, which makes me think there
> are multiple issues here and why I filed both separately. The rows being
> returned are the ones assigned to container1. It looks like Phoenix is first
> getting the rows from the first container and when it finds that to be enough
> it stops the scan. What it should be doing is getting 2 results for each
> container and then merge then and then limit again.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Issue Comment Deleted] (PHOENIX-3451) Secondary index and query using distinct: LIMIT doesn't return the first rows
[
https://issues.apache.org/jira/browse/PHOENIX-3451?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-3451:
--
Comment: was deleted
(was: It seems PHOENIX-3451 is ok in Phoenix4.8.0,
I wrote the IT Test as [~jpalmert] described,but the result is ok in
Phoenix4.8.0 .
PHOENIX-3452 indeed can reproduce, I fixed the PHOENIX-3452.)
> Secondary index and query using distinct: LIMIT doesn't return the first rows
> -
>
> Key: PHOENIX-3451
> URL: https://issues.apache.org/jira/browse/PHOENIX-3451
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.8.0
>Reporter: Joel Palmert
>Assignee: chenglei
>
> This may be related to PHOENIX-3452 but the behavior is different so filing
> it separately.
> Steps to repro:
> CREATE TABLE IF NOT EXISTS TEST.TEST (
> ORGANIZATION_ID CHAR(15) NOT NULL,
> CONTAINER_ID CHAR(15) NOT NULL,
> ENTITY_ID CHAR(15) NOT NULL,
> SCORE DOUBLE,
> CONSTRAINT TEST_PK PRIMARY KEY (
> ORGANIZATION_ID,
> CONTAINER_ID,
> ENTITY_ID
> )
> ) VERSIONS=1, MULTI_TENANT=TRUE, REPLICATION_SCOPE=1, TTL=31536000;
> CREATE INDEX IF NOT EXISTS TEST_SCORE ON TEST.TEST (CONTAINER_ID, SCORE DESC,
> ENTITY_ID DESC);
> UPSERT INTO test.test VALUES ('org2','container2','entityId6',1.1);
> UPSERT INTO test.test VALUES ('org2','container1','entityId5',1.2);
> UPSERT INTO test.test VALUES ('org2','container2','entityId4',1.3);
> UPSERT INTO test.test VALUES ('org2','container1','entityId3',1.4);
> UPSERT INTO test.test VALUES ('org2','container3','entityId7',1.35);
> UPSERT INTO test.test VALUES ('org2','container3','entityId8',1.45);
> EXPLAIN
> SELECT DISTINCT entity_id, score
> FROM test.test
> WHERE organization_id = 'org2'
> AND container_id IN ( 'container1','container2','container3' )
> ORDER BY score DESC
> LIMIT 2
> OUTPUT
> entityId51.2
> entityId31.4
> The expected out out would be
> entityId81.45
> entityId31.4
> You will get the expected output if you remove the secondary index from the
> table or remove distinct from the query.
> As described in PHOENIX-3452 if you run the query without the LIMIT the
> ordering is not correct. However, the 2first results in that ordering is
> still not the onces returned by the limit clause, which makes me think there
> are multiple issues here and why I filed both separately. The rows being
> returned are the ones assigned to container1. It looks like Phoenix is first
> getting the rows from the first container and when it finds that to be enough
> it stops the scan. What it should be doing is getting 2 results for each
> container and then merge then and then limit again.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Issue Comment Deleted] (PHOENIX-3451) Secondary index and query using distinct: LIMIT doesn't return the first rows
[
https://issues.apache.org/jira/browse/PHOENIX-3451?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-3451:
--
Comment: was deleted
(was: I think the problem is cause by the GroupByCompiler, when GroupBy.compile
method called the OrderPreservingTracker.track method to track the groupBy
expression's order,just as following(in GroupByCompiler.java):
{code:borderStyle=solid}
144if (isOrderPreserving) {
145 OrderPreservingTracker tracker = new
OrderPreservingTracker(context, GroupBy.EMPTY_GROUP_BY, Ordering.UNORDERED,
expressions.size(), tupleProjector);
146 for (int i = 0; i < expressions.size(); i++) {
147Expression expression = expressions.get(i);
148tracker.track(expression);
149 }
{code}
The track method inappropriately used the sortOrder != SortOrder.getDefault()
as the thrid "isNullsLast" parameter as following(in
OrderPreservingTracker.java):
{code:borderStyle=solid}
101 public void track(Expression node) {
102 SortOrder sortOrder = node.getSortOrder();
103 track(node, sortOrder, sortOrder != SortOrder.getDefault());
104 }
105
106public void track(Expression node, SortOrder sortOrder, boolean
isNullsLast) {
{code}
Once the node's SortOrder is SortOrder.DESC, the "isNullsLast" is true. it
affected the GroupBy 's isOrderPreserving as following(in
OrderPreservingTracker.java) :
{code:borderStyle=solid}
141if (node.isNullable()) {
142if (!Boolean.valueOf(isNullsLast).equals(isReverse)) {
143 isOrderPreserving = false;
144 isReverse = false;
145 return;
146}
147 }
{code}
Actually, the "isNullsLast" parameter is just related to orderBy ,it should
just affected the display order of "Null " in the sorted results , groupBy
should not be affetced by "isNullsLast". I wrote a simple unit test to
reproduce this problem in my patch:
{code:borderStyle=solid}
@Test
public void testGroupByDesc() throws Exception {
Connection conn = DriverManager.getConnection(getUrl());
try {
conn.createStatement().execute("DROP TABLE IF EXISTS
GROUPBYDESC_TEST");
String sql="CREATE TABLE IF NOT EXISTS GROUPBYDESC_TEST ( "+
"ORGANIZATION_ID VARCHAR,"+
"CONTAINER_ID VARCHAR,"+
"CONSTRAINT TEST_PK PRIMARY KEY ( "+
"ORGANIZATION_ID DESC,"+
"CONTAINER_ID DESC"+
"))";
conn.createStatement().execute(sql);
sql="SELECT ORGANIZATION_ID, CONTAINER_ID,count(*) FROM
GROUPBYDESC_TEST group by ORGANIZATION_ID, CONTAINER_ID";
PhoenixPreparedStatement statement =
conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
QueryPlan queryPlan = statement.optimizeQuery(sql);
queryPlan.iterator();
assertTrue(queryPlan.getGroupBy().isOrderPreserving());
} finally {
conn.close();
}
}
{code}
I uploaded my patch, [~jamestaylor], please review.
)
> Secondary index and query using distinct: LIMIT doesn't return the first rows
> -
>
> Key: PHOENIX-3451
> URL: https://issues.apache.org/jira/browse/PHOENIX-3451
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.8.0
>Reporter: Joel Palmert
>Assignee: chenglei
>
> This may be related to PHOENIX-3452 but the behavior is different so filing
> it separately.
> Steps to repro:
> CREATE TABLE IF NOT EXISTS TEST.TEST (
> ORGANIZATION_ID CHAR(15) NOT NULL,
> CONTAINER_ID CHAR(15) NOT NULL,
> ENTITY_ID CHAR(15) NOT NULL,
> SCORE DOUBLE,
> CONSTRAINT TEST_PK PRIMARY KEY (
> ORGANIZATION_ID,
> CONTAINER_ID,
> ENTITY_ID
> )
> ) VERSIONS=1, MULTI_TENANT=TRUE, REPLICATION_SCOPE=1, TTL=31536000;
> CREATE INDEX IF NOT EXISTS TEST_SCORE ON TEST.TEST (CONTAINER_ID, SCORE DESC,
> ENTITY_ID DESC);
> UPSERT INTO test.test VALUES ('org2','container2','entityId6',1.1);
> UPSERT INTO test.test VALUES ('org2','container1','entityId5',1.2);
> UPSERT INTO test.test VALUES ('org2','container2','entityId4',1.3);
> UPSERT INTO test.test VALUES ('org2','container1','entityId3',1.4);
> UPSERT INTO test.test VALUES ('org2','container3','entityId7',1.35);
> UPSERT INTO test.test VALUES ('org2','container3','entityId8',1.45);
> EXPLAIN
> SELECT DISTINCT entity_id, score
> FROM test.test
> WHERE organization_id = 'org2'
> AND container_id IN ( 'container1','container2','container3' )
> ORDER BY score DESC
> LIMIT 2
> OUTPUT
> entityId51.2
> entityId31.4
> The expected out out would be
> entityId81.45
> entityId31.4
> You will get the
