Jooq: Warning "Ambiguous match found" when implicit join paths contain the same column name several times

[lukaseder]

Customer reported an issue with implicit join paths containing the same column name multiple times, which may lead to ambiguities.

Reproduction details still missing

This works:

CREATE TABLE t_8659_a (
  x INTEGER NOT NULL PRIMARY KEY
);
CREATE TABLE t_8659_b (
  x INTEGER NOT NULL PRIMARY KEY REFERENCES t_8659_a
);
CREATE TABLE t_8659_c (
  x INTEGER NOT NULL PRIMARY KEY REFERENCES t_8659_b
);

And then

@Test
public void testH2ImplicitJoinAmbiguitiesOnJoinPath() {
    clean(T_8659_A, T_8659_B, T_8659_C);

    assertEquals(1, create().insertInto(T_8659_A, T_8659_A.X).values(1).execute());
    assertEquals(1, create().insertInto(T_8659_B, T_8659_B.X).values(1).execute());
    assertEquals(1, create().insertInto(T_8659_C, T_8659_C.X).values(1).execute());

    Record3<Integer, Integer, Integer> result =
    create().select(T_8659_C.X, T_8659_C.t_8659B().X, T_8659_C.t_8659B().t_8659A().X)
            .from(T_8659_C)
            .fetchOne();

    assertEquals(asList(1, 1, 1), result.intoList());
}

[lukaseder]

This also works:

CREATE TABLE t_8659_x (
  a INTEGER NOT NULL,
  b INTEGER NOT NULL,

  PRIMARY KEY (a, b)
);
CREATE TABLE t_8659_y (
  a INTEGER NOT NULL,
  b INTEGER NOT NULL,
  c INTEGER NOT NULL,

  PRIMARY KEY (a, c),
  FOREIGN KEY (a, b) REFERENCES t_8659_x (a, b)
);
CREATE TABLE t_8659_z (
  x INTEGER NOT NULL,
  y INTEGER NOT NULL,
  a INTEGER NOT NULL,
  c INTEGER NOT NULL,

  PRIMARY KEY (x, y),
  FOREIGN KEY (a, c) REFERENCES t_8659_y (a, c)
);

And then, complete test:

@Test
public void testH2ImplicitJoinAmbiguitiesOnJoinPath() {
    clean(T_8659_A, T_8659_B, T_8659_C, T_8659_X, T_8659_Y, T_8659_Z);

    assertEquals(1, create().insertInto(T_8659_A, T_8659_A.X).values(1).execute());
    assertEquals(1, create().insertInto(T_8659_B, T_8659_B.X).values(1).execute());
    assertEquals(1, create().insertInto(T_8659_C, T_8659_C.X).values(1).execute());

    Record3<Integer, Integer, Integer> r1 =
    create().select(T_8659_C.X, T_8659_C.t_8659B().X, T_8659_C.t_8659B().t_8659A().X)
            .from(T_8659_C)
            .fetchOne();

    assertEquals(asList(1, 1, 1), r1.intoList());

    assertEquals(1, create().insertInto(T_8659_X, T_8659_X.A, T_8659_X.B).values(1, 2).execute());
    assertEquals(1, create().insertInto(T_8659_Y, T_8659_Y.A, T_8659_Y.B, T_8659_Y.C).values(1, 2, 3).execute());
    assertEquals(1, create().insertInto(T_8659_Z, T_8659_Z.A, T_8659_Z.C, T_8659_Z.X, T_8659_Z.Y).values(1, 3, 4, 5).execute());

    Record5<Integer, Integer, Integer, Integer, Integer> r2 =
    create().select(
                T_8659_Z.X,
                T_8659_Z.Y,
                T_8659_Z.t_8659Y().C,
                T_8659_Z.t_8659Y().t_8659X().A,
                T_8659_Z.t_8659Y().t_8659X().B)
            .from(T_8659_Z)
            .fetchOne();

    assertEquals(asList(4, 5, 3, 2, 1), r2.intoList());
}

The second query produces this valid SQL (on H2):

select 
  "PUBLIC"."T_8659_Z"."X", 
  "PUBLIC"."T_8659_Z"."Y", 
  "alias_94379464"."C", 
  "alias_113049611"."A", 
  "alias_113049611"."B"
from (
  "PUBLIC"."T_8659_Z"
    left outer join (
      "PUBLIC"."T_8659_Y" "alias_94379464"
        left outer join "PUBLIC"."T_8659_X" "alias_113049611"
          on (
            "alias_94379464"."A" = "alias_113049611"."A"
            and "alias_94379464"."B" = "alias_113049611"."B"
          )
    )
      on (
        "PUBLIC"."T_8659_Z"."A" = "alias_94379464"."A"
        and "PUBLIC"."T_8659_Z"."C" = "alias_94379464"."C"
      )
  )

But there is a SQLWarning in the log file:

13:59:48,684  INFO [org.jooq.impl.Fields                              ] - Ambiguous match found for A. Both "alias_94379464"."A" and "alias_113049611"."A" match.
java.sql.SQLWarning: null
    at org.jooq.impl.Fields.field0(Fields.java:147) [classes/:?]
    at org.jooq.impl.Fields.field(Fields.java:86) [classes/:?]
    at org.jooq.impl.RowImpl.field(RowImpl.java:235) [classes/:?]
    at org.jooq.impl.AbstractTable.field(AbstractTable.java:244) [classes/:?]
    at org.jooq.impl.JoinTable.onKey(JoinTable.java:673) [classes/:?]
    at org.jooq.impl.JoinTable.onKey(JoinTable.java:636) [classes/:?]
    at org.jooq.impl.JoinTable.onKey(JoinTable.java:1) [classes/:?]
    at org.jooq.impl.AbstractContext$JoinNode.joinTree(AbstractContext.java:774) [classes/:?]
    at org.jooq.impl.DefaultRenderContext.scopeEnd0(DefaultRenderContext.java:285) [classes/:?]
    at org.jooq.impl.AbstractContext.scopeEnd(AbstractContext.java:584) [classes/:?]
    at org.jooq.impl.SelectQueryImpl.accept0(SelectQueryImpl.java:873) [classes/:?]
    at org.jooq.impl.SelectQueryImpl.accept(SelectQueryImpl.java:510) [classes/:?]
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:561) [classes/:?]
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:446) [classes/:?]
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:207) [classes/:?]
    at org.jooq.impl.DefaultRenderContext.render(DefaultRenderContext.java:336) [classes/:?]
    at org.jooq.test.all.listeners.PrettyPrinter.renderEnd(PrettyPrinter.java:105) [classes/:?]
    at org.jooq.impl.ExecuteListeners.renderEnd(ExecuteListeners.java:142) [classes/:?]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:327) [classes/:?]
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:490) [classes/:?]
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:477) [classes/:?]
    at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:642) [classes/:?]
    at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2885) [classes/:?]
    at org.jooq.test.H2Test.testH2ImplicitJoinAmbiguitiesOnJoinPath(H2Test.java:2418) [test-classes/:?]

[lukaseder]

The first query also produces the same warning:

13:59:48,514  INFO [org.jooq.impl.Fields                              ] - Ambiguous match found for X. Both "alias_79009191"."X" and "alias_59870291"."X" match.
java.sql.SQLWarning: null
    at org.jooq.impl.Fields.field0(Fields.java:147) [classes/:?]
    at org.jooq.impl.Fields.field(Fields.java:86) [classes/:?]
    at org.jooq.impl.RowImpl.field(RowImpl.java:235) [classes/:?]
    at org.jooq.impl.AbstractTable.field(AbstractTable.java:244) [classes/:?]
    at org.jooq.impl.JoinTable.onKey(JoinTable.java:673) [classes/:?]
    at org.jooq.impl.JoinTable.onKey(JoinTable.java:636) [classes/:?]
    at org.jooq.impl.JoinTable.onKey(JoinTable.java:1) [classes/:?]
    at org.jooq.impl.AbstractContext$JoinNode.joinTree(AbstractContext.java:774) [classes/:?]
    at org.jooq.impl.DefaultRenderContext.scopeEnd0(DefaultRenderContext.java:285) [classes/:?]
    at org.jooq.impl.AbstractContext.scopeEnd(AbstractContext.java:584) [classes/:?]
    at org.jooq.impl.SelectQueryImpl.accept0(SelectQueryImpl.java:873) [classes/:?]
    at org.jooq.impl.SelectQueryImpl.accept(SelectQueryImpl.java:510) [classes/:?]
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:561) [classes/:?]
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:446) [classes/:?]
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:207) [classes/:?]
    at org.jooq.impl.DefaultRenderContext.render(DefaultRenderContext.java:336) [classes/:?]
    at org.jooq.test.all.listeners.PrettyPrinter.renderEnd(PrettyPrinter.java:105) [classes/:?]
    at org.jooq.impl.ExecuteListeners.renderEnd(ExecuteListeners.java:142) [classes/:?]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:327) [classes/:?]
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:490) [classes/:?]
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:477) [classes/:?]
    at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:642) [classes/:?]
    at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2885) [classes/:?]
    at org.jooq.test.H2Test.testH2ImplicitJoinAmbiguitiesOnJoinPath(H2Test.java:2402) [test-classes/:?]

[lukaseder]

This is quite difficult to fix. Perhaps we need to review the field resolution logic in org.jooq.impl.Fields in general.

[lukaseder]

Duplicate of #10603. Fixed with jOOQ 3.14.0

[lukaseder]

Wasn't so difficult after all...