T
TanStack•2mo ago
ambitious-aqua

SubQuery doesn't seem to work

The locksAgg query returns results results on it's own, but when added as a subquery, I get zero results. Am I doing something wrong with how I am using subqueries? 
  const votes = useLiveQuery((q) => {
    const locksAgg = q
      .from({ lock: c.locksCollection })
      .join({ vote: c.votesCollection }, ({ lock, vote }) =>
        eq(lock._id, vote.lockId),
      )
      .join({ org: c.orgsCollection }, ({ lock, org }) =>
        eq(lock?.orgId, org._id),
      )
      .where(({ vote }) => eq(vote?.epochNumber, EPOCH_NUMBER))
      .groupBy(({ lock, org }) => [lock._id, lock.name, org?.color])
      .select(({ lock, vote, org }) => ({
        _id: lock._id,
        totalPercent: sum(vote?.percent),
        lockName: lock?.name,
        lockColor: org?.color,
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ lock: locksAgg }, ({ vote, lock }) => eq(vote.lockId, lock._id))
      .where(({ vote }) =>
        and(
          eq(vote.poolAddress, poolAddress),
          eq(vote.epochNumber, EPOCH_NUMBER),
        ),
      )
      .orderBy(({ vote }) => vote._creationTime, 'asc')
      .select(({ vote, lock }) => ({
        ...vote,
        totalVotes: 0,
        status: 'planning',
        lockName: lock?.lockName,
        lockColor: lock?.lockColor,
        overallPercent: lock?.totalPercent,
      }))
  })
  const votes = useLiveQuery((q) => {
    const locksAgg = q
      .from({ lock: c.locksCollection })
      .join({ vote: c.votesCollection }, ({ lock, vote }) =>
        eq(lock._id, vote.lockId),
      )
      .join({ org: c.orgsCollection }, ({ lock, org }) =>
        eq(lock?.orgId, org._id),
      )
      .where(({ vote }) => eq(vote?.epochNumber, EPOCH_NUMBER))
      .groupBy(({ lock, org }) => [lock._id, lock.name, org?.color])
      .select(({ lock, vote, org }) => ({
        _id: lock._id,
        totalPercent: sum(vote?.percent),
        lockName: lock?.name,
        lockColor: org?.color,
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ lock: locksAgg }, ({ vote, lock }) => eq(vote.lockId, lock._id))
      .where(({ vote }) =>
        and(
          eq(vote.poolAddress, poolAddress),
          eq(vote.epochNumber, EPOCH_NUMBER),
        ),
      )
      .orderBy(({ vote }) => vote._creationTime, 'asc')
      .select(({ vote, lock }) => ({
        ...vote,
        totalVotes: 0,
        status: 'planning',
        lockName: lock?.lockName,
        lockColor: lock?.lockColor,
        overallPercent: lock?.totalPercent,
      }))
  })
12 Replies
genetic-orange
genetic-orange•2mo ago
it looks fine... perhaps keep modifying the second query part to see if something gets it to return data
ambitious-aqua
ambitious-aquaOP•2mo ago
OK. I think I may have found a bug. This query shows results: 
  const votesSimple = useLiveQuery((q) => {
    const locksAgg = q
      .from({ lock: c.locksCollection })
      // .join({ vote: c.votesCollection }, ({ lock, vote }) =>
      //   eq(lock._id, vote.lockId),
      // )
      .select(({ lock }) => ({
        _id: lock._id,
        lockName: lock?.name,
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ lock: locksAgg }, ({ vote, lock }) => eq(lock._id, vote.lockId))
      .select(({ vote, lock }) => ({
        ...vote,
        lockName: lock?.lockName,
      }))
  })
  console.log('votesSimple', votesSimple.data)
  const votesSimple = useLiveQuery((q) => {
    const locksAgg = q
      .from({ lock: c.locksCollection })
      // .join({ vote: c.votesCollection }, ({ lock, vote }) =>
      //   eq(lock._id, vote.lockId),
      // )
      .select(({ lock }) => ({
        _id: lock._id,
        lockName: lock?.name,
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ lock: locksAgg }, ({ vote, lock }) => eq(lock._id, vote.lockId))
      .select(({ vote, lock }) => ({
        ...vote,
        lockName: lock?.lockName,
      }))
  })
  console.log('votesSimple', votesSimple.data)
but when I include the join, it returns no results. 
  const votesSimple = useLiveQuery((q) => {
    const locksAgg = q
      .from({ lock: c.locksCollection })
      .join({ vote: c.votesCollection }, ({ lock, vote }) =>
        eq(lock._id, vote.lockId),
      )
      .select(({ lock }) => ({
        _id: lock._id,
        lockName: lock?.name,
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ lock: locksAgg }, ({ vote, lock }) => eq(lock._id, vote.lockId))
      .select(({ vote, lock }) => ({
        ...vote,
        lockName: lock?.lockName,
      }))
  })
  console.log('votesSimple', votesSimple.data)
  const votesSimple = useLiveQuery((q) => {
    const locksAgg = q
      .from({ lock: c.locksCollection })
      .join({ vote: c.votesCollection }, ({ lock, vote }) =>
        eq(lock._id, vote.lockId),
      )
      .select(({ lock }) => ({
        _id: lock._id,
        lockName: lock?.name,
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ lock: locksAgg }, ({ vote, lock }) => eq(lock._id, vote.lockId))
      .select(({ vote, lock }) => ({
        ...vote,
        lockName: lock?.lockName,
      }))
  })
  console.log('votesSimple', votesSimple.data)
Is there some kind of bug with including the same collection in the subquery as the main query?
genetic-orange
genetic-orange•2mo ago
Shouldn't be but tagging @samwillis
ambitious-aqua
ambitious-aquaOP•2mo ago
If I flip the query around and use votes in the from clause, it returns results: 
  const votesSimple = useLiveQuery((q) => {
    const votesAgg = q
      .from({ vote: c.votesCollection })
      .groupBy(({ vote }) => [vote.lockId])
      .select(({ vote }) => ({
        lockId: vote.lockId,
        overallPercent: sum(vote.percent),
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ voteAgg: votesAgg }, ({ vote, voteAgg }) =>
        eq(vote.lockId, voteAgg.lockId),
      )
      .select(({ vote, voteAgg }) => ({
        ...vote,
        overallPercent: voteAgg?.overallPercent,
      }))
  })
  console.log('votesSimple', votesSimple.data)
  const votesSimple = useLiveQuery((q) => {
    const votesAgg = q
      .from({ vote: c.votesCollection })
      .groupBy(({ vote }) => [vote.lockId])
      .select(({ vote }) => ({
        lockId: vote.lockId,
        overallPercent: sum(vote.percent),
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ voteAgg: votesAgg }, ({ vote, voteAgg }) =>
        eq(vote.lockId, voteAgg.lockId),
      )
      .select(({ vote, voteAgg }) => ({
        ...vote,
        overallPercent: voteAgg?.overallPercent,
      }))
  })
  console.log('votesSimple', votesSimple.data)
Seems like there is some kind of cross leaking in the where clause as well for example this query the overallPercent ends up the same as the vote.percent 
  const votesIncorrectSum = useLiveQuery((q) => {
    const lockAgg = q
      .from({ vote: c.votesCollection })
      .join({ lock: c.locksCollection }, ({ lock, vote }) =>
        eq(lock._id, vote.lockId),
      )
      .where(({ vote }) => eq(vote.epochNumber, EPOCH_NUMBER))
      .groupBy(({ vote, lock }) => [vote.lockId, lock?.name])
      .select(({ vote, lock }) => ({
        _id: vote.lockId,
        name: lock?.name,
        overallPercent: sum(vote.percent),
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ lockAgg }, ({ vote, lockAgg }) => eq(lockAgg._id, vote.lockId))
      .where(({ vote }) =>
        and(
          eq(vote.poolAddress, poolAddress),
          eq(vote.epochNumber, EPOCH_NUMBER),
        ),
      )
      .orderBy(({ vote }) => vote._creationTime, 'asc')
      .select(({ vote, lockAgg }) => ({
        _id: vote._id,
        _creationTime: vote._creationTime,
        lockId: vote.lockId,
        epochNumber: vote.epochNumber,
        percent: vote.percent,
        poolAddress: vote.poolAddress,
        totalVotes: 0,
        status: 'planning',
        lockName: lockAgg?.name,
        overallPercent: lockAgg?.overallPercent,
        lockColor: '#FFF',
      }))
  })
  const votesIncorrectSum = useLiveQuery((q) => {
    const lockAgg = q
      .from({ vote: c.votesCollection })
      .join({ lock: c.locksCollection }, ({ lock, vote }) =>
        eq(lock._id, vote.lockId),
      )
      .where(({ vote }) => eq(vote.epochNumber, EPOCH_NUMBER))
      .groupBy(({ vote, lock }) => [vote.lockId, lock?.name])
      .select(({ vote, lock }) => ({
        _id: vote.lockId,
        name: lock?.name,
        overallPercent: sum(vote.percent),
      }))

    return q
      .from({ vote: c.votesCollection })
      .join({ lockAgg }, ({ vote, lockAgg }) => eq(lockAgg._id, vote.lockId))
      .where(({ vote }) =>
        and(
          eq(vote.poolAddress, poolAddress),
          eq(vote.epochNumber, EPOCH_NUMBER),
        ),
      )
      .orderBy(({ vote }) => vote._creationTime, 'asc')
      .select(({ vote, lockAgg }) => ({
        _id: vote._id,
        _creationTime: vote._creationTime,
        lockId: vote.lockId,
        epochNumber: vote.epochNumber,
        percent: vote.percent,
        poolAddress: vote.poolAddress,
        totalVotes: 0,
        status: 'planning',
        lockName: lockAgg?.name,
        overallPercent: lockAgg?.overallPercent,
        lockColor: '#FFF',
      }))
  })
But with this query the sum is correct 
  const votes = useLiveQuery((q) => {
    const lockAgg = q
      .from({ vote: c.votesCollection })
      .join({ lock: c.locksCollection }, ({ lock, vote }) =>
        eq(lock._id, vote.lockId),
      )
      .where(({ vote }) => eq(vote.epochNumber, EPOCH_NUMBER))
      .groupBy(({ vote, lock }) => [vote.lockId, lock?.name])
      .select(({ vote, lock }) => ({
        _id: vote.lockId,
        name: lock?.name,
        overallPercent: sum(vote.percent),
      }))

    const votesQ = q
      .from({ vote: c.votesCollection })
      .join({ lockAgg }, ({ vote, lockAgg }) => eq(lockAgg._id, vote.lockId))
      .where(
        ({ vote }) =>
          // and(
          // eq(vote.poolAddress, poolAddress),
          eq(vote.epochNumber, EPOCH_NUMBER),
        // ),
      )
      .orderBy(({ vote }) => vote._creationTime, 'asc')
      .select(({ vote, lockAgg }) => ({
        _id: vote._id,
        _creationTime: vote._creationTime,
        lockId: vote.lockId,
        epochNumber: vote.epochNumber,
        percent: vote.percent,
        poolAddress: vote.poolAddress,
        totalVotes: 0,
        status: 'planning',
        lockName: lockAgg?.name,
        overallPercent: lockAgg?.overallPercent,
        lockColor: '#FFF',
      }))

    return q
      .from({ votesQ })
      .where(({ votesQ }) => eq(votesQ.poolAddress, poolAddress))
  })
  console.log('votes for row', votes.data)
  const votes = useLiveQuery((q) => {
    const lockAgg = q
      .from({ vote: c.votesCollection })
      .join({ lock: c.locksCollection }, ({ lock, vote }) =>
        eq(lock._id, vote.lockId),
      )
      .where(({ vote }) => eq(vote.epochNumber, EPOCH_NUMBER))
      .groupBy(({ vote, lock }) => [vote.lockId, lock?.name])
      .select(({ vote, lock }) => ({
        _id: vote.lockId,
        name: lock?.name,
        overallPercent: sum(vote.percent),
      }))

    const votesQ = q
      .from({ vote: c.votesCollection })
      .join({ lockAgg }, ({ vote, lockAgg }) => eq(lockAgg._id, vote.lockId))
      .where(
        ({ vote }) =>
          // and(
          // eq(vote.poolAddress, poolAddress),
          eq(vote.epochNumber, EPOCH_NUMBER),
        // ),
      )
      .orderBy(({ vote }) => vote._creationTime, 'asc')
      .select(({ vote, lockAgg }) => ({
        _id: vote._id,
        _creationTime: vote._creationTime,
        lockId: vote.lockId,
        epochNumber: vote.epochNumber,
        percent: vote.percent,
        poolAddress: vote.poolAddress,
        totalVotes: 0,
        status: 'planning',
        lockName: lockAgg?.name,
        overallPercent: lockAgg?.overallPercent,
        lockColor: '#FFF',
      }))

    return q
      .from({ votesQ })
      .where(({ votesQ }) => eq(votesQ.poolAddress, poolAddress))
  })
  console.log('votes for row', votes.data)
pleasant-yellow
pleasant-yellow•2mo ago
Yep, it looks like a bug in the optimiser, could you file an issue? I'll try and take a good look asap.
genetic-orange
genetic-orange•2mo ago
I had Claude Code run at it for a while https://github.com/TanStack/db/pull/719
GitHub
[Will be closed]: Investigate Discord Bug Report by KyleAMathews ·...
Investigated Discord bug report where using the same collection with the same alias in both a subquery and main query causes: Empty results when subquery has joins Aggregation cross-leaking (wrong...
pleasant-yellow
pleasant-yellow•2mo ago
Ah! You reuse the alias between a sub query and main query. If you rename the "vote" in one to "vote2" it should work.
genetic-orange
genetic-orange•2mo ago
heh that's an easier fix! Should we validate against this? Or is something we can fix internally?
pleasant-yellow
pleasant-yellow•2mo ago
Validate for now, but we should make it work with duplicates at some point.
genetic-orange
genetic-orange•2mo ago
cool, I'll have claude code whip up something 😎
genetic-orange
genetic-orange•2mo ago
ready https://github.com/TanStack/db/pull/719
GitHub
fix: validate against duplicate collection aliases in subqueries by...
Summary Fixes a Discord bug where using the same collection alias in both a parent query and subquery causes empty results or incorrect aggregation values. Problem When both parent and subquery use...
ambitious-aqua
ambitious-aquaOP•2mo ago
Just confirmed that renaming the alias fixes the issue. Thanks, all!

Did you find this page helpful?