7 replies

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",
  },
]

Convex Community•7mo ago•

7 replies

anmot.

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

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",
  },
]

Continue the conversation

Join the Discord to ask follow-up questions and connect with the community

Convex Community

Join the Convex Discord! Explore Convex, the backend platform that keeps your app in sync.

15,960 Members

Join

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

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

Continue the conversation

Convex Community

Continue the conversation

Convex Community

Similar Threads