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 https://github.com/gemini-hlsw/gsp-graphql/commit/ef0b21e6648fc0e8d2973c8aee1604ba73e426b5 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.