Wrong types generated for full joins

This issue has been tracked since 2022-10-19.

Consider the following example:

Users have skills. There is 1 user in total. There are 7 skills in total. The user has 3 skills. Therefore there are 3 records in users_skills join table.

Consider the following code:

  const innerJoin = await sql<
    { id: string; user_id: string }[]
  >`SELECT S.id, US.user_id FROM skills S INNER JOIN users_skills US ON S.id = US.skill_id`;

  const leftJoin = await sql<
    { id: string; user_id: string | null }[]
  >`SELECT S.id, US.user_id FROM skills S LEFT JOIN users_skills US ON S.id = US.skill_id`;

  const rightJoin = await sql<
    { id: string; user_id: string }[]
  >`SELECT S.id, US.user_id FROM skills S RIGHT JOIN users_skills US ON S.id = US.skill_id`;

  const fullJoin = await sql<
    { id: string; user_id: string }[]
  >`SELECT S.id, US.user_id FROM skills S FULL JOIN users_skills US ON S.id = US.skill_id`;

  console.log({ innerJoin, leftJoin, rightJoin, fullJoin });

And the following logs:

{
  innerJoin: Result(3) [
    { id: '3', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '4', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '5', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' }
  ],
  leftJoin: Result(7) [
    { id: '1', user_id: null },
    { id: '2', user_id: null },
    { id: '3', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '4', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '5', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '6', user_id: null },
    { id: '7', user_id: null }
  ],
  rightJoin: Result(3) [
    { id: '3', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '4', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '5', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' }
  ],
  fullJoin: Result(7) [
    { id: '1', user_id: null },
    { id: '2', user_id: null },
    { id: '3', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '4', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '5', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '6', user_id: null },
    { id: '7', user_id: null }
  ]
}

As you can see the user_id type for the full join query does not include the null value, whereas there are such records in the results.

.eslintrc.json

{
  "parser": "@typescript-eslint/parser",
  "plugins": ["@typescript-eslint", "@ts-safeql/eslint-plugin"],
  "parserOptions": {
    "project": "./tsconfig.json"
  },
  "rules": {
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": [
          {
            "databaseUrl": "xxx", // hidden url
            "tagName": "sql",
            "transform": "{type}[]"
          }
        ]
      }
    ]
  }
}

SELECT version();

PostgreSQL 14.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1~20.04) 10.3.0, 64-bit

Newbie012 wrote this answer on 2022-10-19

Thanks for reporting!

I can't remember the last time I had to write full/right joins 😅

If I understand correctly, these should be the actual types:

  const innerJoin = await sql<
    { id: string; user_id: string }[]
  >`SELECT S.id, US.user_id FROM skills S INNER JOIN users_skills US ON S.id = US.skill_id`;

  const leftJoin = await sql<
    { id: string; user_id: string | null }[]
  >`SELECT S.id, US.user_id FROM skills S LEFT JOIN users_skills US ON S.id = US.skill_id`;

  const rightJoin = await sql<
-    { id: string; user_id: string }[]
+    { id: string | null; user_id: string }[]
  >`SELECT S.id, US.user_id FROM skills S RIGHT JOIN users_skills US ON S.id = US.skill_id`;

  const fullJoin = await sql<
-    { id: string; user_id: string }[]
+   { id: string | null; user_id: string | null }[]
  >`SELECT S.id, US.user_id FROM skills S FULL JOIN users_skills US ON S.id = US.skill_id`;

I'll take a look at it later this day.

mzalevski wrote this answer on 2022-10-19

@Newbie012 yeah, this seems about right. Thanks :)

Your Name

Your Comment

More Details About Repo

Owner Name	ts-safeql
Repo Name	safeql
Full Name	ts-safeql/safeql
Language	TypeScript
Created Date	2022-09-08
Updated Date	2023-03-16
Star Count	795
Watcher Count	5
Fork Count	14
Issue Count	7

YOU MAY BE INTERESTED

Issue Title	Created Date	Updated Date