Comments (7)
sherfert
commented on May 25, 2024
1
@alexanoid
The particular query you provided is indeed returning results in the wrong order in the parallel runtime.
We are working on fixing this bug.
from neo4j.
sherfert
commented on May 25, 2024
1
@alexanoid We have fixed the bug, but unfortunately it missed the deadline for 5.17. So the fix will be included in 5.18.
from neo4j.
alexanoid
commented on May 25, 2024
For example, in the following query, the result is dancing all of the time:
CYPHER runtime = parallel
MATCH (dg:DecisionGroup {
id: -2
})-[rdgd: CONTAINS ]-> ( childD:Profile )
MATCH (childD)-[mhvo:HAS_VOTE_ON]-(mc:Criterion)
WHERE mc.id IN [9760, 9761, 9757, 9758, 9759] + []
WITH childD , collect(mhvo) AS mhvos
WHERE size(mhvos) >= size([9760, 9761, 9757, 9758, 9759])
WITH childD
WHERE (childD.`active` = true )
WITH childD
WITH childD
MATCH (childDStat:JobableStatistic {
jobableId: childD.id
})
WITH childD, childDStat
UNWIND [9760, 9761, 9757, 9758, 9759] AS dCId
WITH childD, childDStat, dCId + coalesce({}[toString(dCId)], []) AS cGroup
WHERE NOT AlL(x IN cGroup
WHERE x IN childDStat.zeroCriterionIds )
WITH childD, childDStat, collect(cGroup) AS cGroups
WHERE size(cGroups) >= size([9760, 9761, 9757, 9758, 9759])
UNWIND cGroups AS cGroup
WITH childD, childDStat, cGroup
WHERE ANY(x IN cGroup
WHERE x IN childDStat.detailedCriterionIds)
WITH childD, childDStat, collect(cGroup) AS cGroups
WHERE size(cGroups) >= size([9760, 9761, 9757, 9758, 9759])
WITH childD, childDStat, size(cGroups) AS cGroupsSize, cGroups
UNWIND cGroups AS cGroup
WITH childD, childDStat, cGroups, cGroupsSize, cGroup
UNWIND cGroup AS cId
WITH childD, childDStat, cGroups, cGroupsSize, cGroup, cId, cGroup[0] AS cG0
WITH childD, childDStat, cGroups, cGroupsSize, cGroup, cId, cG0, {
`9759`:1.0, `9758`:1.0, `9760`:1.0, `9761`:1.0, `9757`:1.0
}[toString(cG0)] AS criterionAvgVoteWeight, {
`9759`:0, `9758`:0, `9760`:0, `9761`:0, `9757`:0
}[toString(cG0)] AS criterionExperienceMonth
WHERE (criterionAvgVoteWeight = 0 OR criterionAvgVoteWeight <= childDStat['criterionAvgVoteWeights.' + cId]) AND (criterionExperienceMonth = 0 OR criterionExperienceMonth <= childDStat['criterionExperienceMonths.' + cId])
WITH childD, childDStat, cGroups, cGroupsSize, collect(cId) AS cIds
UNWIND cGroups AS cGroup
WITH childD, childDStat, cGroup, cGroupsSize, cIds
WHERE ANY(x IN cIds
WHERE x IN cGroup)
WITH childD, childDStat, cGroupsSize, collect(cGroup) AS cGroups
WHERE size(cGroups) >= cGroupsSize
WITH childD, childDStat
UNWIND [9760, 9761, 9757, 9758, 9759] AS cId
WITH childD, childDStat, cId, {
`9759`:1.0, `9758`:1.0, `9760`:1.0, `9761`:1.0, `9757`:1.0
}[toString(cId)] AS criterionCoefficient
WITH childD, sum(criterionCoefficient * childDStat['criterionAvgVoteWeights.' + cId]) AS weight, sum(childDStat['criterionTotalVotes.' + cId]) AS totalVotes, sum(criterionCoefficient) AS criterionCoefficientSum
WITH childD, weight, totalVotes, criterionCoefficientSum
WITH collect({`childD`:childD , `weight`:weight, `totalVotes`: totalVotes }) AS aggregate
WITH aggregate, size(aggregate) AS count
UNWIND aggregate AS item
WITH count, item.childD AS childD , item.weight AS weight, item.totalVotes AS totalVotes
MATCH (dg:DecisionGroup {
id: -2
})-[rdgd: CONTAINS ]->(childD)
OPTIONAL MATCH (childD)-[ru:CREATED_BY]->(u:User)
OPTIONAL MATCH (jobable:Decision:Vacancy {
id: 4928
})
RETURN count, childD AS decision, dg, rdgd , u, ru , jobable.id AS jobableId , weight, totalVotes, [ (jobable)-[vg1:HAS_VOTE_ON]->(c1:Criterion) | {
criterion: c1, relationship: vg1
} ] AS jobableWeightedCriteria, [(jobable)-[:HAS_VOTE_ON]->(c1:Criterion)<-[vg1:HAS_VOTE_ON]-(childD)
WHERE c1.id IN childD.detailedCriterionIds | {
criterion: c1, relationship: vg1
} ] AS weightedCriteria , [ (c1t:Translation:BaseEntity)<-[rc1t: CONTAINS ]-(c1:Criterion)<-[vg1:HAS_VOTE_ON]-(childD)
WHERE EXISTS ((jobable)-[:HAS_VOTE_ON]->(c1)) AND c1t.iso6391 = 'uk' AND c1.id IN childD.detailedCriterionIds | {
entityId: toInteger(c1.id), translation: c1t
} ] AS weightedCriteriaTranslations , [ (jobable)-[:WORK_LOCATED_IN|EMPLOYMENT_TYPE_AS|READY_TO|EMPLOYMENT_AS|WORK_TIME_ZONE|BELONGS_TO|LOCATED_IN|COMPANY|WORK_PERMIT_IN|COMPANY_TYPE_OF]->(ce:CompositeEntity) | {
entity: ce
} ] AS jobableCompositeEntities, [ (childD)-[:WORK_LOCATED_IN|EMPLOYMENT_TYPE_AS|READY_TO|EMPLOYMENT_AS|WORK_TIME_ZONE|BELONGS_TO|LOCATED_IN|COMPANY|WORK_PERMIT_IN|COMPANY_TYPE_OF]->(ce:CompositeEntity) | {
entity: ce
} ] AS decisionCompositeEntities, [ (childD)-[:WORK_LOCATED_IN|EMPLOYMENT_TYPE_AS|READY_TO|EMPLOYMENT_AS|WORK_TIME_ZONE|BELONGS_TO|LOCATED_IN|COMPANY|WORK_PERMIT_IN|COMPANY_TYPE_OF]->(ce:CompositeEntity)-[: CONTAINS ]->(trans:Translation:BaseEntity)
WHERE trans.iso6391 = 'uk' | {
entityId: toInteger(id(ce)), translation: trans
} ] AS decisionCompositeEntitiesTranslations, [ (childD)-[: CONTAINS ]->(trans:Translation:BaseEntity)
WHERE trans.iso6391 = 'uk' | {
entityId: toInteger(childD.id), translation: trans
} ] AS decisionTranslations, [ (rc:Criterion)-[*0]->()
WHERE rc.id IN childD.replaceableCriterionIds | {
entity: rc
} ] AS decisionReplaceableCriteria, [ (rc:Criterion)-[: CONTAINS ]->(trans:Translation:BaseEntity)
WHERE rc.id IN childD.replaceableCriterionIds AND trans.iso6391 = 'uk' | {
entityId: toInteger(id(rc)), translation: trans
} ] AS decisionReplaceableCriteriaTranslations, COUNT {
(:Vacancy:Jobable:BaseEntity {status: 'APPROVED', active: true
})<-[:POTENTIAL_PROFILE]-(childD) } AS potentialJobablesCount , COUNT {
(:Vacancy:Jobable:BaseEntity {status: 'APPROVED', active: true
})<-[:RELEVANT_PROFILE]-(childD) } AS relevantJobablesCount
ORDER BY weight DESC, childD.createdAt DESC SKIP 0
LIMIT 100
When executed multiple times, this query returns records in arbitrary order with varying weights. One time weight=25 is on the first place.. another time - weight=10 on the first place. What could be the cause of this?
With CYPHER runtime = pipelined this query is working fine.
from neo4j.
alexanoid
commented on May 25, 2024
I may be wrong, but it looks like the pattern comprehensive or COUNT subquery somehow affects the query ordering, specifically this part:
, [ (jobable)-[vg1:HAS_VOTE_ON]->(c1:Criterion) | {
criterion: c1, relationship: vg1
} ] AS jobableWeightedCriteria, [(jobable)-[:HAS_VOTE_ON]->(c1:Criterion)<-[vg1:HAS_VOTE_ON]-(childD)
WHERE c1.id IN childD.detailedCriterionIds | {
criterion: c1, relationship: vg1
} ] AS weightedCriteria , [ (c1t:Translation:BaseEntity)<-[rc1t: CONTAINS ]-(c1:Criterion)<-[vg1:HAS_VOTE_ON]-(childD)
WHERE EXISTS ((jobable)-[:HAS_VOTE_ON]->(c1)) AND c1t.iso6391 = 'uk' AND c1.id IN childD.detailedCriterionIds | {
entityId: toInteger(c1.id), translation: c1t
} ] AS weightedCriteriaTranslations , [ (jobable)-[:WORK_LOCATED_IN|EMPLOYMENT_TYPE_AS|READY_TO|EMPLOYMENT_AS|WORK_TIME_ZONE|BELONGS_TO|LOCATED_IN|COMPANY|WORK_PERMIT_IN|COMPANY_TYPE_OF]->(ce:CompositeEntity) | {
entity: ce
} ] AS jobableCompositeEntities, [ (childD)-[:WORK_LOCATED_IN|EMPLOYMENT_TYPE_AS|READY_TO|EMPLOYMENT_AS|WORK_TIME_ZONE|BELONGS_TO|LOCATED_IN|COMPANY|WORK_PERMIT_IN|COMPANY_TYPE_OF]->(ce:CompositeEntity) | {
entity: ce
} ] AS decisionCompositeEntities, [ (childD)-[:WORK_LOCATED_IN|EMPLOYMENT_TYPE_AS|READY_TO|EMPLOYMENT_AS|WORK_TIME_ZONE|BELONGS_TO|LOCATED_IN|COMPANY|WORK_PERMIT_IN|COMPANY_TYPE_OF]->(ce:CompositeEntity)-[: CONTAINS ]->(trans:Translation:BaseEntity)
WHERE trans.iso6391 = 'uk' | {
entityId: toInteger(id(ce)), translation: trans
} ] AS decisionCompositeEntitiesTranslations, [ (childD)-[: CONTAINS ]->(trans:Translation:BaseEntity)
WHERE trans.iso6391 = 'uk' | {
entityId: toInteger(childD.id), translation: trans
} ] AS decisionTranslations, [ (rc:Criterion)-[*0]->()
WHERE rc.id IN childD.replaceableCriterionIds | {
entity: rc
} ] AS decisionReplaceableCriteria, [ (rc:Criterion)-[: CONTAINS ]->(trans:Translation:BaseEntity)
WHERE rc.id IN childD.replaceableCriterionIds AND trans.iso6391 = 'uk' | {
entityId: toInteger(id(rc)), translation: trans
} ] AS decisionReplaceableCriteriaTranslations, COUNT {
(:Vacancy:Jobable:BaseEntity {status: 'APPROVED', active: true
})<-[:POTENTIAL_PROFILE]-(childD) } AS potentialJobablesCount , COUNT {
(:Vacancy:Jobable:BaseEntity {status: 'APPROVED', active: true
})<-[:RELEVANT_PROFILE]-(childD) } AS relevantJobablesCount
from neo4j.
pontusmelke
commented on May 25, 2024
Hi @alexanoid, could you maybe run the query with EXPLAIN and paste the results in here? If you are using browser please use the Export as TXT option in the top-right corner.
Regards,
Pontus
from neo4j.
alexanoid
commented on May 25, 2024
Hi @pontusmelke , sure, this is the query:
EXPLAIN
CYPHER runtime = parallel
MATCH (dg:DecisionGroup {
id: -2
})-[rdgd: CONTAINS ]-> ( childD:Profile )
MATCH (childD)-[mhvo:HAS_VOTE_ON]-(mc:Criterion)
WHERE mc.id IN [9776, 9777, 9778, 9779, 9775] + []
WITH childD , collect(mhvo) AS mhvos
WHERE size(mhvos) >= size([9776, 9777, 9778, 9779, 9775])
WITH childD
WHERE (childD.`active` = true )
WITH childD
WITH childD
MATCH (childDStat:JobableStatistic {
jobableId: childD.id
})
WITH childD, childDStat
UNWIND [9776, 9777, 9778, 9779, 9775] AS dCId
WITH childD, childDStat, dCId + coalesce({}[toString(dCId)], []) AS cGroup
WHERE NOT AlL(x IN cGroup
WHERE x IN childDStat.zeroCriterionIds )
WITH childD, childDStat, collect(cGroup) AS cGroups
WHERE size(cGroups) >= size([9776, 9777, 9778, 9779, 9775])
UNWIND cGroups AS cGroup
WITH childD, childDStat, cGroup
WHERE ANY(x IN cGroup
WHERE x IN childDStat.detailedCriterionIds)
WITH childD, childDStat, collect(cGroup) AS cGroups
WHERE size(cGroups) >= size([9776, 9777, 9778, 9779, 9775])
WITH childD, childDStat, size(cGroups) AS cGroupsSize, cGroups
UNWIND cGroups AS cGroup
WITH childD, childDStat, cGroups, cGroupsSize, cGroup
UNWIND cGroup AS cId
WITH childD, childDStat, cGroups, cGroupsSize, cGroup, cId, cGroup[0] AS cG0
WITH childD, childDStat, cGroups, cGroupsSize, cGroup, cId, cG0, {
`9775`:1.0, `9777`:1.0, `9776`:1.0, `9779`:1.0, `9778`:1.0
}[toString(cG0)] AS criterionAvgVoteWeight, {
`9775`:0, `9777`:0, `9776`:0, `9779`:0, `9778`:0
}[toString(cG0)] AS criterionExperienceMonth
WHERE (criterionAvgVoteWeight = 0 OR criterionAvgVoteWeight <= childDStat['criterionAvgVoteWeights.' + cId]) AND (criterionExperienceMonth = 0 OR criterionExperienceMonth <= childDStat['criterionExperienceMonths.' + cId])
WITH childD, childDStat, cGroups, cGroupsSize, collect(cId) AS cIds
UNWIND cGroups AS cGroup
WITH childD, childDStat, cGroup, cGroupsSize, cIds
WHERE ANY(x IN cIds
WHERE x IN cGroup)
WITH childD, childDStat, cGroupsSize, collect(cGroup) AS cGroups
WHERE size(cGroups) >= cGroupsSize
WITH childD, childDStat
UNWIND [9776, 9777, 9778, 9779, 9775] AS cId
WITH childD, childDStat, cId, {
`9775`:1.0, `9777`:1.0, `9776`:1.0, `9779`:1.0, `9778`:1.0
}[toString(cId)] AS criterionCoefficient
WITH childD, sum(criterionCoefficient * childDStat['criterionAvgVoteWeights.' + cId]) AS weight, sum(childDStat['criterionTotalVotes.' + cId]) AS totalVotes, sum(criterionCoefficient) AS criterionCoefficientSum
WITH childD, weight, totalVotes, criterionCoefficientSum
WITH collect({`childD`:childD , `weight`:weight, `totalVotes`: totalVotes }) AS aggregate
WITH aggregate, size(aggregate) AS count
UNWIND aggregate AS item
WITH count, item.childD AS childD , item.weight AS weight, item.totalVotes AS totalVotes
MATCH (dg:DecisionGroup {
id: -2
})-[rdgd: CONTAINS ]->(childD)
OPTIONAL MATCH (childD)-[ru:CREATED_BY]->(u:User)
OPTIONAL MATCH (jobable:Decision:Vacancy {
id: 4958
})
RETURN count, childD AS decision, dg, rdgd , u, ru , jobable.id AS jobableId , weight, totalVotes, [ (jobable)-[vg1:HAS_VOTE_ON]->(c1:Criterion) | {
criterion: c1, relationship: vg1
} ] AS jobableWeightedCriteria, [(jobable)-[:HAS_VOTE_ON]->(c1:Criterion)<-[vg1:HAS_VOTE_ON]-(childD)
WHERE c1.id IN childD.detailedCriterionIds | {
criterion: c1, relationship: vg1
} ] AS weightedCriteria , [ (c1t:Translation:BaseEntity)<-[rc1t: CONTAINS ]-(c1:Criterion)<-[vg1:HAS_VOTE_ON]-(childD)
WHERE EXISTS ((jobable)-[:HAS_VOTE_ON]->(c1)) AND c1t.iso6391 = 'uk' AND c1.id IN childD.detailedCriterionIds | {
entityId: toInteger(c1.id), translation: c1t
} ] AS weightedCriteriaTranslations , [ (jobable)-[:READY_TO|EMPLOYMENT_TYPE_AS|WORK_LOCATED_IN|COMPANY_TYPE_OF|WORK_PERMIT_IN|COMPANY|LOCATED_IN|BELONGS_TO|WORK_TIME_ZONE|EMPLOYMENT_AS]->(ce:CompositeEntity) | {
entity: ce
} ] AS jobableCompositeEntities, [ (childD)-[:READY_TO|EMPLOYMENT_TYPE_AS|WORK_LOCATED_IN|COMPANY_TYPE_OF|WORK_PERMIT_IN|COMPANY|LOCATED_IN|BELONGS_TO|WORK_TIME_ZONE|EMPLOYMENT_AS]->(ce:CompositeEntity) | {
entity: ce
} ] AS decisionCompositeEntities, [ (childD)-[:READY_TO|EMPLOYMENT_TYPE_AS|WORK_LOCATED_IN|COMPANY_TYPE_OF|WORK_PERMIT_IN|COMPANY|LOCATED_IN|BELONGS_TO|WORK_TIME_ZONE|EMPLOYMENT_AS]->(ce:CompositeEntity)-[: CONTAINS ]->(trans:Translation:BaseEntity)
WHERE trans.iso6391 = 'uk' | {
entityId: toInteger(id(ce)), translation: trans
} ] AS decisionCompositeEntitiesTranslations, [ (childD)-[: CONTAINS ]->(trans:Translation:BaseEntity)
WHERE trans.iso6391 = 'uk' | {
entityId: toInteger(childD.id), translation: trans
} ] AS decisionTranslations, [ (rc:Criterion)-[*0]->()
WHERE rc.id IN childD.replaceableCriterionIds | {
entity: rc
} ] AS decisionReplaceableCriteria, [ (rc:Criterion)-[: CONTAINS ]->(trans:Translation:BaseEntity)
WHERE rc.id IN childD.replaceableCriterionIds AND trans.iso6391 = 'uk' | {
entityId: toInteger(id(rc)), translation: trans
} ] AS decisionReplaceableCriteriaTranslations, COUNT {
(:Vacancy:Jobable:BaseEntity {status: 'APPROVED', active: true
})<-[:POTENTIAL_PROFILE]-(childD) } AS potentialJobablesCount , COUNT {
(:Vacancy:Jobable:BaseEntity {status: 'APPROVED', active: true
})<-[:RELEVANT_PROFILE]-(childD) } AS relevantJobablesCount
ORDER BY weight DESC, childD.createdAt DESC SKIP 0
LIMIT 100
and the plan:
from neo4j.
alexanoid
commented on May 25, 2024
@alexanoid The particular query you provided is indeed returning results in the wrong order in the parallel runtime. We are working on fixing this bug.
Thank you!
from neo4j.
Related Issues (20)
- Query causes Stackoverflow HOT 1
- Unexpected SyntaxError in `toBoolean โ all` HOT 1
- `genai.vector.encode` doesn't allow for changing OpenAI API endpoints HOT 1
- Result rows do not work as expected. HOT 6
- The apoc.cypher.runSchemaFile() never finishes execution
- Node matched too early in `UNION` on Enterprise
- Node deleted failed about path of unknown length HOT 1
- One possible performance issue HOT 3
- Wrongful execution of `ELSE` branch in `CASE` expression HOT 2
- UnknownError in a DuplicatedSlotKey HOT 1
- One possible performance issue HOT 3
- toInteger and toFloat can't handle strings with common thousands delimiters HOT 2
- Neo.ClientError.Statement.RuntimeUnsupportedError HOT 6
- BLOCK storage format between AuraDB and Community editions HOT 8
- Allow lists with arbitrary strings in CVS import by escaping array delimiter HOT 5
- Neo4j 5.19.0 was released April 12th, 2024 but none of the source is available on this repo. Is it still under GPL? HOT 1
- Critical CVE in container image HOT 1
- Neo4j cypher ๏ผquery directed relationships HOT 2
- Importing csv quote issues even with "" escaping HOT 1
- Version 5.19.0 and 5.20.0 releases HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. ๐๐๐
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google โค๏ธ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from neo4j.