Prisma1: Nested `where` fails with PostgresSQL

Created on 17 May 2018 · 4Comments · Source: prisma/prisma1

Current behavior
The following query:

query givers($organizationId: ID!) {
    persons(where: {transactions_some: {organization: {id: $organizationId}}}) {
      id
      transactions(where:{organization:{id:$organizationId}}) {
        amount
      }
    }
  }

...produces a Prisma server error when executed against a PostgreSQL DB (running locally in a docker container), specifically for the transactions node (its able to query for persons okay). It works when run against a Prisma cloud demo service (with MySQL). I'll provide the full error log from the docker container below - it looks to me like there's a suggestion in the error message about what's wrong!

Reproduction
I've got a sample repo set up with my SDL + some seed data which demonstrates the issue, though it'd need to be deployed somewhere with a PostgreSQL DB to replicate. The working version deployed to the demo service is available here: https://us1.prisma.sh/kyle-goggin-08ba7b/node-bug/dev.

Expected behavior?
PostgreSQL should work the same as MySQL (query succeeds)

Log entry for error request from docker logs:

{"key":"error/unhandled","requestId":"local:api:cjhatkl05008j0926jxjexgmc","clientId":"default$default","payload":{"exception":"org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table \"FinancialTransaction\"\n  Hint: Perhaps you meant to reference the table alias \"modeltable\".\n  Position: 703","query":"query givers($organizationId: ID!) {\n  persons(where: {transactions_some: {organization: {id: $organizationId}}}) {\nid\n    transactions(where: {organization: {id: \"cjhaqupwb006c0926qgyync0v\"}}) {\n      amount\n      organization {\n        id\n      }\n    }\n  }\n}\n","variables":"{\"organizationId\":\"cjhaqupwb006c0926qgyync0v\"}","code":"0","stack_trace":"org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)\\n org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)\\n org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)\\n org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)\\n org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)\\n org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)\\n org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)\\n com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)\\n com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)\\n slick.jdbc.StatementInvoker.results(StatementInvoker.scala:39)\\n slick.jdbc.StatementInvoker.iteratorTo(StatementInvoker.scala:22)\\n slick.jdbc.Invoker.foreach(Invoker.scala:48)\\n slick.jdbc.Invoker.foreach$(Invoker.scala:47)\\n slick.jdbc.StatementInvoker.foreach(StatementInvoker.scala:16)\\n slick.jdbc.StreamingInvokerAction.run(StreamingInvokerAction.scala:22)\\n slick.jdbc.StreamingInvokerAction.run$(StreamingInvokerAction.scala:20)\\n slick.jdbc.SQLActionBuilder$$anon$1.run(StaticQuery.scala:98)\\n slick.jdbc.SQLActionBuilder$$anon$1.run(StaticQuery.scala:98)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:240)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:240)\\n java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\\n java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\\n java.lang.Thread.run(Thread.java:748)","message":"ERROR: invalid reference to FROM-clause entry for table \"FinancialTransaction\"\n  Hint: Perhaps you meant to reference the table alias \"modeltable\".\n  Position: 703"}}
[Bugsnag - local / testing] Error report: com.bugsnag.Report@1d7ed2b3
org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "FinancialTransaction"
  Hint: Perhaps you meant to reference the table alias "modeltable".
  Position: 703
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at slick.jdbc.StatementInvoker.results(StatementInvoker.scala:39)
        at slick.jdbc.StatementInvoker.iteratorTo(StatementInvoker.scala:22)
        at slick.jdbc.Invoker.foreach(Invoker.scala:48)
        at slick.jdbc.Invoker.foreach$(Invoker.scala:47)
        at slick.jdbc.StatementInvoker.foreach(StatementInvoker.scala:16)
        at slick.jdbc.StreamingInvokerAction.run(StreamingInvokerAction.scala:22)
        at slick.jdbc.StreamingInvokerAction.run$(StreamingInvokerAction.scala:20)
        at slick.jdbc.SQLActionBuilder$$anon$1.run(StaticQuery.scala:98)
        at slick.jdbc.SQLActionBuilder$$anon$1.run(StaticQuery.scala:98)
        at slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:240)
        at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:240)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

bu2-confirmed areconnectopostgres

Source

kgoggin

All 4 comments

Hey, thanks for reporting this and posting a detailed reproduction. I can reproduce it on master. But it already seems to be fixed on the alpha channel. The error stems from not escaping an alias in a query. We already caught on to this and adjusted the Postgres queries and that change is already in the alpha channel. Here is a post about our release process where the different channels are explained : https://www.prisma.io/blog/improving-prismas-release-process-yaey8deiwaex/
I'll close this issue once the changes are on master.