Unknown alias error when using Group By

[jasonsilvers-dispel]

Describe the bug

Not sure if it is a bug or just something pg-mem doesn't support yet.

QueryError: select "devices".*, COALESCE(json_agg(DISTINCT acl_rules.id) FILTER (WHERE acl_rules.id IS NOT NULL), '[]') as acls from "devices" left join "acl_rules" on "acl_rules"."device_id" = "devices"."id" where "facility_id" in ($1) group by "devices"."id" - Unknown alias "devices"
    
    🐜 This seems to be an execution error, which means that your request syntax seems okay,
        but the resulting statement cannot be executed → Probably not a pg-mem error.
    
    *️⃣ Failed SQL statement: select "devices".*, COALESCE(json_agg(DISTINCT acl_rules.id) FILTER (WHERE acl_rules.id IS NOT NULL), '[]') as acls from "devices" left join "acl_rules" on "acl_rules"."device_id" = "devices"."id" where "facility_id" in ('1') group by "devices"."id";
    
    👉 You can file an issue at https://github.com/oguimbal/pg-mem along with a way to reproduce this error (if you can), and  the stacktrace:
        at buildCols (/src/packages/capi/node_modules/pg-mem/src/transforms/selection.ts:91:27)
        at buildCols.next (<anonymous>)
        at /src/packages/capi/node_modules/pg-mem/src/transforms/selection.ts:133:55
        at StackOf.usingValue (/src/packages/capi/node_modules/pg-mem/src/parser/context.ts:11:20)
        at new Selection (/src/packages/capi/node_modules/pg-mem/src/transforms/selection.ts:133:38)
        at Object.buildSelection (/src/packages/capi/node_modules/pg-mem/src/transforms/selection.ts:33:12)
        at Aggregation.select (/src/packages/capi/node_modules/pg-mem/src/transforms/transform-base.ts:74:20)
        at buildRawSelect (/src/packages/capi/node_modules/pg-mem/src/execution/select.ts:200:15)
        at buildSelect (/src/packages/capi/node_modules/pg-mem/src/execution/select.ts:55:20)
        at buildWithable (/src/packages/capi/node_modules/pg-mem/src/execution/select.ts:35:20)
        at new SelectExec (/src/packages/capi/node_modules/pg-mem/src/execution/select.ts:270:67)
        at StatementExec._getExecutor (/src/packages/capi/node_modules/pg-mem/src/execution/statement-exec.ts:82:24)
        at /src/packages/capi/node_modules/pg-mem/src/execution/statement-exec.ts:181:52
        at StackOf.usingValue (/src/packages/capi/node_modules/pg-mem/src/parser/context.ts:11:20)
        at /src/packages/capi/node_modules/pg-mem/src/execution/statement-exec.ts:180:40
        at StackOf.usingValue (/src/packages/capi/node_modules/pg-mem/src/parser/context.ts:11:20)
        at /src/packages/capi/node_modules/pg-mem/src/execution/statement-exec.ts:179:36
        at StackOf.usingValue (/src/packages/capi/node_modules/pg-mem/src/parser/context.ts:11:20)
        at /src/packages/capi/node_modules/pg-mem/src/execution/statement-exec.ts:178:29
        at StatementExec.niceErrors (/src/packages/capi/node_modules/pg-mem/src/execution/statement-exec.ts:221:20)
        at StatementExec.compile (/src/packages/capi/node_modules/pg-mem/src/execution/statement-exec.ts:154:21)
        at DbSchema.queries (/src/packages/capi/node_modules/pg-mem/src/schema/schema.ts:122:36)
        at queries.next (<anonymous>)
        at DbSchema.query (/src/packages/capi/node_modules/pg-mem/src/schema/schema.ts:79:20)
        at MemPg.query (/src/packages/capi/node_modules/pg-mem/src/adapters/adapters.ts:104:76)
        at /src/packages/capi/node_modules/knex/lib/dialects/postgres/index.js:237:18
        at new Promise (<anonymous>)
        at Client_PG._query (/src/packages/capi/node_modules/knex/lib/dialects/postgres/index.js:236:12)
        at Client_PG.query (/src/packages/capi/node_modules/knex/lib/client.js:168:17)
        at Runner.query (/src/packages/capi/node_modules/knex/lib/runner.js:151:36)
        at /src/packages/capi/node_modules/knex/lib/runner.js:40:23
        at /src/packages/capi/node_modules/knex/lib/runner.js:277:24
        at processTicksAndRejections (node:internal/process/task_queues:95:5)
        at Object.search (/src/packages/capi/devices/oldServices.js:255:21) {
      data: { error: 'Unknown alias "devices"', code: undefined },
      code: undefined,
      location: { start: 0, end: 0 },
      [Symbol(errorDetailsIncluded)]: true
    }

To Reproduce

import { newDb } from "pg-mem";

const database = newDb();
const db = database.adapters.createKnex();

await db.schema.createTable('devices', (table) => {
      table.increments('id').notNullable().primary();
      table.json('protocols_ports');
      table.string('name');
      table.string('make');
      table.string('model');
      table.string('ip');
      table
        .uuid('wicket_id')
        .notNullable()
        .references('id')
        .inTable('wickets')
        .onUpdate('CASCADE');
      table.dateTime('created_at');
      table.dateTime('updated_at');
      table
        .integer('facility_id')
        .notNullable()
        .references('id')
        .inTable('facilities');
    });

    await db.schema.createTable('acl_rules', (table) => {
      table.increments('id').notNullable().primary();
      table.string('remote_id').notNullable();
      table.string('source_ip').notNullable();
      table.string('destination_ip').notNullable();
      table.string('protocol').notNullable();
      table.string('ports');
      table.dateTime('created_at');
      table.dateTime('updated_at');
      table.boolean('is_paused').notNullable().defaultTo(false);
      table.integer('region_id').references('id').inTable('regions');
      table.uuid('user_id').notNullable().references('id').inTable('users');
      table
        .integer('device_id')
        .notNullable()
        .references('id')
        .inTable('devices');
      table.boolean('manual').notNullable().defaultTo(true);
      table.unique(['device_id', 'user_id', 'protocol', 'ports']);
    });

knex.destroy();

select devices.*,
COALESCE(json_agg(DISTINCT acl_rules.id) FILTER (WHERE acl_rules.id IS NOT NULL), '[]') as acls
from devices
LEFT JOIN acl_rules ON devices.id = acl_rules.device_id
where facility_id in (38, 39)
group by devices.id

This query works on postgres but fails with pg-mem

pg-mem version

"version": "2.6.13",

[Thiamath]

Bumping this thread...

It is happening to me too. The query works perfectly fine when executing directly on PostgreSQL, but it's failing when using Knex.