Multiple joins not follow SQL semantics

[fcannizzaro]

I have an issue with multiple joins, I need some joins between a main table and other 4 tables but it seems that the output on TanStack DB is different from the same query on SQL.

These are my collection and SQL query:

TanStack DB Collection

const collection = createLiveQueryCollection((q) => {
    return q
        .from({ p: player })
        .leftJoin({ challenge1 }, ({ p, challenge1 }) =>
          eq(p.id, challenge1.id)
        )
        .leftJoin({ challenge2 }, ({ p, challenge2 }) =>
          eq(p.id, challenge2.id)
        )
        .leftJoin({ challenge3 }, ({ p, challenge3 }) =>
          eq(p.id, challenge3.id)
        )
        .leftJoin({ challenge4 }, ({ p, challenge4 }) =>
          eq(p.id, challenge4.id)
        )
        .select(({ p, challenge1, challenge2, challenge3, challenge4 }) => ({
          id: p.id,
          category: p.category,
          challenge1,
          challenge2,
          challenge3,
          challenge4,
        }));
    });
}

SQL Query

SELECT
  player.id AS player_id,
  player.category AS player_category,
  challenge1.score as challenge1_score,
  challenge2.score as challenge2_score,
  challenge3.score as challenge3_score,
  challenge4.score as challenge4_score
FROM player
LEFT JOIN challenge1 ON player.id = challenge1.id
LEFT JOIN challenge2 ON player.id = challenge2.id
LEFT JOIN challenge3 ON player.id = challenge3.id
LEFT JOIN challenge4 ON player.id = challenge4.id

(or drizzle version used below)

const rows = await db
  .select({
    id: player.id,
    category: player.category,
    challenge1: challenge1.score,
    challenge2: challenge2.score,
    challenge3: challenge3.score,
    challenge4: challenge4.score,
  })
  .from(player)
  .leftJoin(challenge1, (t) => eq(t.id, challenge1.id))
  .leftJoin(challenge2, (t) => eq(t.id, challenge2.id))
  .leftJoin(challenge3, (t) => eq(t.id, challenge3.id))
  .leftJoin(challenge4, (t) => eq(t.id, challenge4.id))
  .where(eq(player.category, data))
  .orderBy(player.id);

I obtain from SQL N rows and M from the collection (with M >>> N), where each row is duplicated a variable number of times (with missing part of relation).

It seems that the multiple joins feature is not following the SQL semantics.

repo to reproduce the issue
https://github.com/fcannizzaro/ts-db-multiple-joins-issue/tree/main

[samwillis]

Thanks for this repo, it was super helpful in narrowing down what was happening. It turned out there was a very subtle race condition that manifested in broken state on joins with >1 left join. PR fixing it is about to be merged #451 and we will get a release out today.