SQL mapping does not work for recursive queries to DB tables with qualified names

[satorg]

Summary

If DB tables with qualified names are used in a mapping, then recursive GraphQL queries do not work and fail with a cryptic error.

Note: checked for DoobieMapping only, not checked for SkunkMapping.

How to reproduce

1. Apply this bug-reproducer patch ef0b21e to the current main branch.
2. Run Demo server (reStart)
3. Try this GraphQL request in the playground (or any other way of preference):

   {
     country(code: "CAN") {
       name
       cities {
         name
         country {
           name
         }
       }
     }
   }

Expected result

The query completes successfully. It does, actually, without the bug-reproducer patch applied. Or it may work if a query does not contain recursive items, like country / cities / country.

Actual result

The query that contains recursive items (like one shown above) fails to execute. In fact, it fails with 500 without any meaningful explanation.

By default, there's only a couple of messages in the logs:

demo [io-compute-2] INFO  o.h.s.m.Logger - service raised an error: class org.postgresql.util.PSQLException 
demo [io-compute-0] ERROR o.h.s.service-errors - Error servicing request: POST /world from ::1 
demo org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
demo   Position: 315
demo 	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)
demo 	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364)
demo 	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354)

which unfortunately do not make a lot of sense.

However, the reproducer patch also adds doobie.util.log.LogHandler.jdkLogHandler to DB query builder. It helps to reveal a root-cause of the error:

demo[ERROR] Feb 02, 2023 3:47:59 PM doobie.util.log$LogHandler$ $anonfun$jdkLogHandler$1
demo[ERROR] SEVERE: Failed Statement Execution:
demo[ERROR] 
demo[ERROR]   SELECT  demo.city.id ,  demo.city.name ,  demo.country.code ,  demo.country.name AS name_alias_0 ,  demo.country_alias_1.code AS code_alias_2 ,  demo.country_alias_1.name AS name_alias_3  FROM  demo.country  LEFT JOIN  demo.city  ON  (demo.city.countrycode  =  demo.country.code )  INNER JOIN  demo.country AS demo.country_alias_1  ON  (demo.country_alias_1.code  =  demo.city.countrycode )   WHERE (( demo.country.code  =  ?) ) 
demo[ERROR] 
demo[ERROR]  arguments = [CAN]
demo[ERROR]    elapsed = 26 ms exec (failed)
demo[ERROR]    failure = ERROR: syntax error at or near "."
demo[ERROR]   Position: 315
demo[ERROR]           

Apparently, the compiled SELECT statement contains an alias (demo.country_alias_1) that is prepended with the DB schema name, which is not allowed and in fact is an SQL syntax violation.

Work-around

In some cases it is possible to use the before strategy to work this issue around, e.g.:

val transactor: Transactor[F] = ???
val fixedTransactor =
  Transactor.before.modify(transactor, Update0("SET search_path TO demo").run *> _)

After that it becomes possible to use table names without the demo schema name in the mappings.

Not a real solution, of course.

[milessabin]

Thanks for this. It's borderline whether this should really be classed as a bug or an enhancement. Right now Grackle knows nothing about qualified names hence it treats the qualifier as part of the table name ... that part is the bug. The enhancement is to support qualifiers.