Jooq warning: Ambiguous match found for DB_OFFICE_ID. Both "CWMS_20"."AV_LOC"."DB_OFFICE_ID" and "CWMS_20"."AV_LOC_GRP_ASSGN"."DB_OFFICE_ID" match

[rma-rripken]

The following warning shows up in the server logs. It would be nice if we cleaned it up. I think the issue is that the LocaitonsDaoImpl.getLocationCatalog does:

SelectConditionStep query = dsl.select( AV_LOC.asterisk(), AV_LOC_GRP_ASSGN.asterisk() )...

And then does this: query.fetch().forEach(row -> { usace.cwms.db.jooq.codegen.tables.records.AV_LOC loc = row.into(AV_LOC); usace.cwms.db.jooq.codegen.tables.records.AV_LOC_ALIAS alias = row.into(AV_LOC_ALIAS); usace.cwms.db.jooq.codegen.tables.records.AV_LOC_GRP_ASSGN group = row.into(AV_LOC_GRP_ASSGN);

But both AV_LOC and AV_LOC_GRP_ASSGN have a column called DB_OFFICE_ID. The row.into technique apparently can't tell them apart.

This is also weird in that the query doesn't (explicitly) select from AV_LOC_ALIAS but then its doing a row.into(AV_LOC_ALIAS) which might work but seems wrong.

Here is the entire stacktrace:

Sep 20, 2022 12:50:06 PM org.jooq.tools.JooqLogger info INFO: Ambiguous match found for DB_OFFICE_ID. Both "CWMS_20"."AV_LOC"."DB_OFFICE_ID" and "CWMS_20"."AV_LOC_GRP_ASSGN"."DB_OFFICE_ID" match. java.sql.SQLWarning at org.jooq.impl.Fields.field(Fields.java:132) at org.jooq.impl.Fields.indexOf(Fields.java:270) at org.jooq.impl.RowImpl.indexOf(RowImpl.java:311) at org.jooq.impl.AbstractRecord$TransferRecordState.operate(AbstractRecord.java:750) at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:125) at org.jooq.impl.AbstractRecord.into(AbstractRecord.java:720) at cwms.radar.data.dao.LocationsDaoImpl.lambda$getLocationCatalog$4(LocationsDaoImpl.java:308) at java.lang.Iterable.forEach(Iterable.java:75) at cwms.radar.data.dao.LocationsDaoImpl.getLocationCatalog(LocationsDaoImpl.java:303) at cwms.radar.api.CatalogController.getOne(CatalogController.java:210) at io.javalin.apibuilder.CrudFunction$2.invoke$lambda-0(CrudHandler.kt:31) at io.javalin.apibuilder.CrudFunctionHandler.handle(CrudHandler.kt) at cwms.radar.security.CwmsAccessManager.manage(CwmsAccessManager.java:45) at io.javalin.http.JavalinServlet.addHandler$lambda-0(JavalinServlet.kt:96) at io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43) at io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43) at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:98) at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85) at java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:981) at java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2124) at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85) at io.javalin.http.JavalinServletHandler.executeNextTask$lambda-10$lambda-9(JavalinServletHandler.kt:112) at java.util.concurrent.CompletableFuture.uniApply(CompletableFuture.java:602) at java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:614) at java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:1983) at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:112) at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85) at java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:981) at java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2124) at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85) at io.javalin.http.JavalinServlet.service(JavalinServlet.kt:89) at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) at cwms.radar.ApiServlet.service(ApiServlet.java:332) at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:890) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1787) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748)

[rma-rripken]

Similar warning messages show up in https://jira.hecdev.net/browse/CWMS-1069

[rma-rripken]

The aliases should be collected in a LinkedHashSet b/c there are many duplicates

[rma-rripken]

The next-page in the Catalog should probably be determined by how many rows come back from the query and not how many "entries" are in the Catalog object. For RatingMetadataList I added a lastPage boolean to the Builder so that I could get the builder to build the next-page cursor even if the object didn't appear to have page-size number of items. I suspect we need something like that here.

[rma-rripken]

This issue was fixed and then the dao method got rewritten to use av_loc2.