.distinct() with join does not produce results even though debugged sql query does.

[nelsonjd]

Package version

    "@adonisjs/core": "^5.9.0",
    "@adonisjs/drive-s3": "^1.3.2",
    "@adonisjs/lucid": "^18.3.0",

Node.js and npm version

% npm -v
9.5.0
% node -v
v18.15.0

Sample Code (to reproduce the issue)

  let query = await db.from(this.strategies)
    //.join('StrategyVersions', 'StrategyVersions.strategyId', '=', 'Strategies.id')
    .distinct('uid')
    .debug(true)

  console.log('yoyoyo')  
  console.log(query)

produces

select distinct "uid" from "Strategies" []
yoyoyo
[
  { uid: 'zEhac' },
  { uid: 'IBf9m' },
  { uid: '2_J76' },
  { uid: 'sIrGY' },
  { uid: 'f9JK0' },
  { uid: 'gfK79' },
  { uid: 'jW6LI' },
  { uid: 'HeCt-' },
  { uid: 'KUlYO' }

  let query = await db.from(this.strategies)
    .join('StrategyVersions', 'StrategyVersions.strategyId', '=', 'Strategies.id')
    .select('accountId')
    .debug(true)

  console.log('yoyoyo')  
  console.log(query)

produces

select "accountId" from "Strategies" inner join "StrategyVersions" on "StrategyVersions"."strategyId" = "Strategies"."id" []
yoyoyo
[
  { accountId: 15 },
  { accountId: 15 },
  { accountId: 15 },
  { accountId: 57 },
  { accountId: 15 },
  { accountId: 67 },
  { accountId: 67 },
  { accountId: 67 },
  { accountId: 15 }

But

  let query = await db.from(this.strategies)
    .join('StrategyVersions', 'StrategyVersions.strategyId', '=', 'Strategies.id')
    .distinct('accountId')
    .debug(true)

  console.log('yoyoyo')  
  console.log(query)

produces

select distinct "accountId" from "Strategies" inner join "StrategyVersions" on "StrategyVersions"."strategyId" = "Strategies"."id" where "weighting" is not null []
yoyoyo

Notice debugged query produces results

BONUS (a sample repo to reproduce the issue)

[thetutlage]

Since the generate query is correct, its hard to know why the result is empty. Can you please create a fresh project with this example that I can use to reproduce the issue?

[RomainLanz]

Closing since no answer from issue reporter.