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
}
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
Describe the bug
Not sure if it is a bug or just something pg-mem doesn't support yet.
To Reproduce
This query works on postgres but fails with pg-mem
pg-mem version
"version": "2.6.13",