Improve Query Parser with planning mechanics to make queries more performant and error-prone

[1pxone]

The problem

If we want to fetch some Records by specific related Records' criteria - we may face empty result. This happens because of early pagination for memory safety reasons.

For example, we want fetch all user's orders by user.__id.

We will probably expect, that something like that will work:

const { data } = await db.records.find({
  labels: ['ORDER'],
  where: {
    USER: {
      $id: id
    }
  },
  limit: 1000
});

this query will produce this CYPHER:

MATCH (record:__RUSHDB__LABEL__RECORD__:ORDER { __RUSHDB__KEY__PROJECT__ID__: $projectId })
ORDER BY record.`__RUSHDB__KEY__ID__` DESC SKIP 0 LIMIT 1000
OPTIONAL MATCH (record)--(record1:USER) WHERE (any(value IN record1.__RUSHDB__KEY__ID__ WHERE value = "uuidv7"))
WITH record, record1
WHERE record IS NOT NULL AND record1 IS NOT NULL
RETURN collect(DISTINCT record {.*, __RUSHDB__KEY__LABEL__: [label IN labels(record) WHERE label <> "__RUSHDB__LABEL__RECORD__"][0]}) AS records

---

But it won't work as expected if we have total amount of orders (made by many users) beyond the 1000.

This is the result of early pagination:

MATCH (record:__RUSHDB__LABEL__RECORD__:ORDER)
ORDER BY record.`__RUSHDB__KEY__ID__` DESC SKIP 0 LIMIT 1000

Pseudo code for this query will look like:

take Orders  
- sort by id in desc order
- take first 1000
check whether any of those orders has relation with specific user
return result

No guaranties can be given, that those 1000 Records has relation to specific user.

---

To resolve this - we have to turn the query inside out:

// Fetching orders, but traversing from USER Record
const { data } = await db.records.find({
  labels: ['USER'],
  where: {
    $id: 'id',
    ORDER: {
      $alias: '$order'
    }
  },
  aggregate: {
    orders: {
      fn: 'collect',
      alias: '$order'
    }
  },
  limit: 1000
});

This will produce the following CYPHER:

MATCH (record:__RUSHDB__LABEL__RECORD__:COMPANY { __RUSHDB__KEY__PROJECT__ID__: $projectId })
ORDER BY record.`__RUSHDB__KEY__ID__` DESC SKIP 0 LIMIT 1000
OPTIONAL MATCH (record)--(record1:departments)
WITH record, record1
WHERE record IS NOT NULL AND record1 IS NOT NULL
RETURN collect(DISTINCT record {__RUSHDB__KEY__ID__: record.__RUSHDB__KEY__ID__, __RUSHDB__KEY__PROPERTIES__META__: record.__RUSHDB__KEY__PROPERTIES__META__, __RUSHDB__KEY__LABEL__: [label IN labels(record) WHERE label <> "__RUSHDB__LABEL__RECORD__"][0], `departmentId`: record1.`__RUSHDB__KEY__ID__`}) AS records

---

This leads to more clumsy result extraction and extra mind labor to figure out what's going on.

Approach above makes sense only to serve rarely demanded feature of logical grouping Record relations:

await db.records.find({
  labels: ['USER'],
  where: {
    $or: [
      {
        CAR: {
          color: 'red',
          $xor: [
            {
              SPOUSE: {
                gender: 'male'
              }
            },
            {
              title: {
                $not: { $contains: 'Forester' }
              }
            }
          ]
        }
      },
      {
        JOB: {
          title: 'Manager'
        }
      }
    ]
  },
  limit: 1000
})

This will produce CYPHER:

MATCH (record:__RUSHDB__LABEL__RECORD__:USER { __RUSHDB__KEY__PROJECT__ID__: $projectId })
ORDER BY record.\`__RUSHDB__KEY__ID__\` DESC SKIP 0 LIMIT 1000
OPTIONAL MATCH (record)--(record1:CAR) WHERE (any(value IN record1.color WHERE value = "red"))
OPTIONAL MATCH (record1)--(record2:SPOUSE) WHERE (any(value IN record2.gender WHERE value = "male"))
OPTIONAL MATCH (record)--(record3:JOB) WHERE (any(value IN record3.title WHERE value = "Manager"))
WITH record, record1, record2, record3
WHERE record IS NOT NULL AND ((record1 IS NOT NULL AND (record2 IS NOT NULL XOR ((NOT(any(value IN record1.title WHERE value =~ '(?i).*Forester.*')))))) OR record3 IS NOT NULL)
RETURN collect(DISTINCT record {.*, __RUSHDB__KEY__LABEL__: [label IN labels(record) WHERE label <> "__RUSHDB__LABEL__RECORD__"][0]}) AS records`

This is why we have to make OPTIONAL matching more strict by this line:
WHERE record IS NOT NULL AND ((record1 IS NOT NULL AND (record2 IS NOT NULL XOR ((NOT(any(value IN record1.title WHERE value =~ '(?i).*Forester.*')))))) OR record3 IS NOT NULL)

But this is super-rare case that ruins all the benefits of Neo4j Query planner because we don't rely on whole path clause, but splitting graph paths into subgraphs and then filtering Records amongst matched data.

---

The solution:

Implement Query Planner that will seek for this Logical-RelatedMatching patterns in every query, and if there is nothing to do with it - simplify the query to win from Neo4j Query Planner.

The resulting query for our orders by user._id may then look like:

MATCH (record:__RUSHDB__LABEL__RECORD__:ORDER)--(record1:USER) 
WHERE (any(value IN record1.__RUSHDB__KEY__ID__ WHERE value = "uuidv7"))
ORDER BY record.`__RUSHDB__KEY__ID__` DESC SKIP 0 LIMIT 1000
RETURN collect(DISTINCT record {.*, __RUSHDB__KEY__LABEL__: [label IN labels(record) WHERE label <> "__RUSHDB__LABEL__RECORD__"][0]}) AS records

Note1: relation to USER is builtin to top-level matching clause
Note2: sort and pagination goes after where clause

[1pxone]

Alternative solution:

Introduce risky latePagination boolean parameter to SearchQuery to put pagination straight before aggregations.

It will definitely lead to differences between self-hosted and cloud env, and this is a shitty way IMO