anmot.•2mo ago

q.lte() query includes records with null or undefined fields, causing inconsistent filter results

When using q.lte() condition, the query returns documents where the indexed field is null or undefined, in addition to valid numeric values. This results in inconsistent and incorrect query outputs compared to gte(), which behaves as expected. LTE

export default query({
  handler: async (ctx) => {
    return await ctx.db.query("test")
    .withIndex('field', (q) => q.lte('field', 100))
    .collect();
  },
})

export default query({
  handler: async (ctx) => {
    return await ctx.db.query("test")
    .withIndex('field', (q) => q.lte('field', 100))
    .collect();
  },
})

Output

[
  {
    _creationTime: 1759779015108.4048,
    _id: "n9710b0cwt755bvrr25wfa8a5d7rzyz2",
    id: "2",
  },
  {
    _creationTime: 1759779015108.4045,
    _id: "n972amwf06yv59x4nr6m3w1zrx7rz2ks",
    field: null,
    id: "1",
  },
  {
    _creationTime: 1759779148359.3567,
    _id: "n974mtj87nnxz7fvsg4sv6bgch7rzm7j",
    field: 100,
    id: "3",
  },
]

[
  {
    _creationTime: 1759779015108.4048,
    _id: "n9710b0cwt755bvrr25wfa8a5d7rzyz2",
    id: "2",
  },
  {
    _creationTime: 1759779015108.4045,
    _id: "n972amwf06yv59x4nr6m3w1zrx7rz2ks",
    field: null,
    id: "1",
  },
  {
    _creationTime: 1759779148359.3567,
    _id: "n974mtj87nnxz7fvsg4sv6bgch7rzm7j",
    field: 100,
    id: "3",
  },
]

GTE

export default query({
  handler: async (ctx) => {
    return await ctx.db.query("test")
    .withIndex('field', (q) => q.gte('field', 100))
    .collect();
  },
})

export default query({
  handler: async (ctx) => {
    return await ctx.db.query("test")
    .withIndex('field', (q) => q.gte('field', 100))
    .collect();
  },
})

Output:

[
  {
    _creationTime: 1759779148359.3567,
    _id: "n974mtj87nnxz7fvsg4sv6bgch7rzm7j",
    field: 100,
    id: "3",
  },
  {
    _creationTime: 1759779185160.5444,
    _id: "n974x48sfqejr4ed3ar2phezex7rzgk0",
    field: 200,
    id: "4",
  },
]

[
  {
    _creationTime: 1759779148359.3567,
    _id: "n974mtj87nnxz7fvsg4sv6bgch7rzm7j",
    field: 100,
    id: "3",
  },
  {
    _creationTime: 1759779185160.5444,
    _id: "n974x48sfqejr4ed3ar2phezex7rzgk0",
    field: 200,
    id: "4",
  },
]

6 Replies

Clever Tagline•2mo ago

Please review the docs for using indexes. In short, the first check should always use .eq(). Other checks can be used after that. The main point of using an index is to precisely identify records that match a given value first (which is why .eq() is required as the first check), and then (if necessary) use other criteria to whittle down the list. It's not really designed primarily for grabbing documents that match less-than/greater-than comparisons. If you're unable to alter your withIndex syntax to use .eq() first, then you might need to swap out .withIndex for .filter. It'll mean a full table read

anmot.OP•2mo ago

Can you please point me to the documentation that states we need to include at least one .eq() condition before using .lte()? If this scenario isn’t supported, shouldn’t the query fail instead of returning inconsistent results? Additionally, why does .gte() works as expected, while .lte() returns documents with null or undefined values along with those that actually satisfy the condition? https://docs.convex.dev/database/reading-data/indexes

Clever Tagline•2mo ago

My apologies. I was mis-remembering the wording of that part of the docs. I thought it was 1 or more, not 0 or more. As for why .lte() returns records where the value is null or undefined, to me it kinda makes sense. In plain JS, null <= 1 returns true. While undefined <= 1 returns false in JS, I'm guessing that there's some other logic in the .lte() definition that makes it behave similar to the null comparison.

anmot.OP•2mo ago

Yeah. Hope someone from convex team confirm this behaviour.

erquhart•2mo ago

This is by design - lte has to work against all field types, not just numbers, and this behavior is actually handy for strings and booleans, or any type where you want the field optional but want to query on defined fields (via .gte). You'll want to maybe make the field strictly numbers only to get the behavior you're looking for. You can also get pretty powerful filtering with streams: https://stack.convex.dev/merging-streams-of-convex-data

anmot.OP•5w ago

Thanks for the clarification. I added a filter condition with not null and undefined. .filter((q) => q.and( q.neq(q.field('field'), null), q.neq(q.field('field'), undefined), )

q.lte() query includes records with null or undefined fields, causing inconsistent filter results

Did you find this page helpful?