8000 Multiple COLLECT gives wrong results with 3.8, works in 3.7.10 · Issue #14672 · arangodb/arangodb · GitHub
[go: up one dir, main page]
Skip to content

Multiple COLLECT gives wrong results with 3.8, works in 3.7.10 #14672

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
matcho opened this issue Aug 23, 2021 · 3 comments
Closed

Multiple COLLECT gives wrong results with 3.8, works in 3.7.10 #14672

matcho opened this issue Aug 23, 2021 · 3 comments
Labels
1 Bug 2 Fixed Resolution 3 AQL Query language related 3 Optimizer AQL query optimizer is involved workaround available
Milestone
3.8.2

Comments

@matcho
Copy link
matcho commented Aug 23, 2021 •
edited by Simran-B
Loading

My Environment

  • ArangoDB Version: 3.8.0
  • Storage Engine: RocksDB
  • Deployment Mode: Leader/Follower ("Master/Slave")
  • Deployment Strategy: systemd
  • Configuration: Leader 3.7.10 replicated to follower 3.8.0
  • Infrastructure: own
  • Operating System: Ubuntu 18.04 (L) and 20.04 (F)
  • Total RAM in your machine: 256GB (L) and 32 GB (F)
  • Disks in use: HDD
  • Used Package: Ubuntu .deb

Component, Query & Data

Affected feature:
AQL query, COLLECT statement.

AQL query (if applicable):

FOR q IN queries
    SORT q.date DESC
    LIMIT 1000
    COLLECT resultStatus = q.result.status, requestStatus = q.request.status WITH COUNT INTO nb
    RETURN { resultStatus, requestStatus, nb }

AQL explain and/or profile (if applicable):

Explain on 3.8.0

Query String (191 chars, cacheable: true):
 FOR q IN queries
     SORT q.date DESC
     LIMIT 1000
     COLLECT resultStatus = q.result.status, requestStatus = q.request.status WITH COUNT INTO nb 
 RETURN { resultStatus, requestStatus, nb }

Execution plan:
 Id   NodeType               Est.   Comment
  1   SingletonNode             1   * ROOT
 12   IndexNode         450882273     - FOR q IN queries   /* reverse skiplist index scan, projections: `request` */    
  5   LimitNode              1000       - LIMIT 0, 1000
  6   CalculationNode        1000       - LET #6 = q.`result`.`status`   /* attribute expression */   /* collections used: q : queries */
  7   CalculationNode        1000       - LET #8 = q.`request`.`status`   /* attribute expression */   /* collections used: q : queries */
  8   CollectNode             800       - COLLECT resultStatus = #6, requestStatus = #8 AGGREGATE nb = LENGTH()   /* hash */
 11   SortNode                800       - SORT resultStatus ASC, requestStatus ASC   /* sorting strategy: standard */
  9   CalculationNode         800       - LET #10 = { "resultStatus" : resultStatus, "requestStatus" : requestStatus, "nb" : nb }   /* simple expression */
 10   ReturnNode              800       - RETURN #10

Indexes used:
 By   Name        Type       Collection   Unique   Sparse   Selectivity   Fields       Ranges
 12   idx_49159   skiplist   queries      false    false        99.64 %   [ `date` ]   *

Optimization rules applied:
 Id   RuleName
  1   move-calculations-up
  2   move-calculations-up-2
  3   use-indexes
  4   use-index-for-sort
  5   remove-unnecessary-calculations-2
  6   move-calculations-down
  7   reduce-extraction-to-projection

Optimization rules with highest execution times:
 RuleName                                    Duration [s]
 use-indexes                                      0.00009
 specialize-collect                               0.00003
 reduce-extraction-to-projection                  0.00002
 remove-unnecessary-calculations-2                0.00001
 use-index-for-sort                               0.00001

76 rule(s) executed, 2 plan(s) created

Explain on 3.7.10

Query String (191 chars, cacheable: true):
 FOR q IN queries
     SORT q.date DESC
     LIMIT 1000
     COLLECT resultStatus = q.result.status, requestStatus = q.request.status WITH COUNT INTO nb 
 RETURN { resultStatus, requestStatus, nb }

Execution plan:
 Id   NodeType               Est.   Comment
  1   SingletonNode             1   * ROOT
 12   IndexNode         450916894     - FOR q IN queries   /* reverse skiplist index scan, projections: `request`, `result` */    
  5   LimitNode              1000       - LIMIT 0, 1000
  6   CalculationNode        1000       - LET #6 = q.`result`.`status`   /* attribute expression */   /* collections used: q : queries */
  7   CalculationNode        1000       - LET #8 = q.`request`.`status`   /* attribute expression */   /* collections used: q : queries */
  8   CollectNode             800       - COLLECT resultStatus = #6, requestStatus = #8 WITH COUNT INTO nb   /* hash */
 11   SortNode                800       - SORT resultStatus ASC, requestStatus ASC   /* sorting strategy: standard */
  9   CalculationNode         800       - LET #10 = { "resultStatus" : resultStatus, "requestStatus" : requestStatus, "nb" : nb }   /* simple expression */
 10   ReturnNode              800       - RETURN #10

Indexes used:
 By   Name        Type       Collection   Unique   Sparse   Selectivity   Fields       Ranges
 12   idx_49159   skiplist   queries      false    false        99.43 %   [ `date` ]   *

Optimization rules applied:
 Id   RuleName
  1   move-calculations-up
  2   move-calculations-up-2
  3   use-indexes
  4   use-index-for-sort
  5   remove-unnecessary-calculations-2
  6   move-calculations-down
  7   reduce-extraction-to-projection

Dataset:
queries collection has 450M docs. In the 1000 docs selected in the query above, q.request.status is always null.

Size of your Dataset on disk:
A few hundred GBs

Steps to reproduce

  1. Run query above on 3.7.10. Result, as expected, is
resultStatus requestStatus nb
null null 1
ok null 893
reject null 22
wrong_referential null 84
  1. Run same query on 3.8.0, result is
resultStatus requestStatus nb
null null 1000
  1. Edit the query to collect only one column on 3.8
FOR q IN queries
    SORT q.date DESC
    LIMIT 1000
    COLLECT resultStatus = q.result.status WITH COUNT INTO nb RETURN { resultStatus, nb }

and result is as expected

resultStatus nb
null 1
ok 893
reject 22
wrong_referential 84

Problem:
Multiple collect seems to break results in 3.8.0.
Is it because of q.request.status being always null in this subset of data ? Or because an index is used to retrieve subset of documents ? Rules applied seem quite different in respective explains.

Thanks a lot !

Expected result:
Same result as in 3.7.10.
@Simran-B
Copy link
Contributor

Thanks for reporting this! I was able to narrow it down to a problem with the reduce-extraction-to-projection optimizer rule and nested attribute access with the same attribute name at the same level (here: status):

db._drop("coll");
db._create("coll");
db.coll.save({ result: {} });
db.coll.save({ result: {status: "ok" } });

/* wrong with result.status / request.status (both "status") */
db._query(`FOR d IN coll COLLECT resultStatus = d.result.status, requestStatus = d.request.status WITH COUNT INTO count RETURN { resultStatus, requestStatus, count }`);

/* fine with optimizer rule disabled */
db._query(`FOR d IN coll COLLECT resultStatus = d.result.status, requestStatus = d.request.status WITH COUNT INTO count RETURN { resultStatus, requestStatus, count }`,
  null, { optimizer: { rules: ["-reduce-extraction-to-projection"] }});

/* fine with result.status / request.other (different nested attribute names) */
db._query(`FOR d IN coll COLLECT resultStatus = d.result.status, requestOther = d.request.other WITH COUNT INTO count RETURN { resultStatus, requestOther, count }`);

I created an internal ticket BTS-562.

@Simran-B Simran-B added 1 Bug 3 AQL Query language related 3 Optimizer AQL query optimizer is involved labels Aug 25, 2021
@Simran-B Simran-B mentioned this issue Sep 17, 2021
Open
This was referenced Sep 19, 2021
Merged
Merged
@jsteemann
Copy link
Contributor

Fixed via #14801

@jsteemann jsteemann added the 2 Fixed Resolution label Sep 19, 2021
@jsteemann jsteemann added this to the 3.8.2 milestone Sep 19, 2021
@matcho
Copy link
Author
matcho commented Nov 26, 2021

Hi. We can confirm that the bug is fixed in v3.8.3

Many thanks !

@matcho matcho closed this as completed Nov 26, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
1 Bug 2 Fixed Resolution 3 AQL Query language related 3 Optimizer AQL query optimizer is involved workaround available
Projects
None yet
Milestone
3.8.2
Development

No branches or pull requests
3 participants
@jsteemann @matcho @Simran-B
0