CODEWITHSUNDEEP
javasqljooq
user3960875
user3960875

Reputation: 1005

Missing table alias falls back to using only field name

My tables

N
ID|T_ID
1|1
2|2

T
ID|NAME
1|T1
2|T2

Using the tables as follows

com.db.N N_TABLE = N.as("N_TABLE");
com.db.T T_TABLE = T.as("T_TABLE");
com.db.T T2_TABLE = T.as("T2_TABLE"); //Random alias, not used in query

SelectQuery selectQuery = create.selectQuery();
selectQuery.addFrom(N_TABLE);
selectQuery.addJoin(T_TABLE, JoinType.LEFT_OUTER_JOIN, T_TABLE.ID.eq(N_TABLE.T_ID));

Result<Record> result = selectQuery.fetch();
for (Record record : result) {
  System.out.println(record.get(T2_TABLE.NAME));
}

It gives a ambiguity warning, but still gets the value even though alias is wrong. I would expect it to return "null", I guess it falls back to using only field name.

Any idea how should I use it to get "null" in case of a wrong alias?

EDIT

I'll try to provide a more concrete example

My table is as follows

CREATE TABLE user
(
  id bigserial NOT NULL,
  username character varying(200) NOT NULL,
  last_name character varying(100),
  created_user_id bigint NOT NULL,
  modified_user_id bigint NOT NULL,
  CONSTRAINT pk_user PRIMARY KEY (id),
  CONSTRAINT user_username_key UNIQUE (username)
)

Data in tables

3;"admin";"admin";3;3
4;"test";"test";4;3

Code 

//Input params
Long userId = 4L;
boolean includeModifiedUser = false;

User userTable = USER.as("userTable");
User modifiedUserTable = USER.as("modifiedUserTable");

SelectQuery selectQuery = create.selectQuery();
selectQuery.addFrom(userTable);

//In some cases I want to include the last modifier in the query
if (includeModifiedUser) {
    selectQuery.addJoin(modifiedUserTable, JoinType.LEFT_OUTER_JOIN, modifiedUserTable.ID.eq(userTable.MODIFIED_USER_ID));
}

selectQuery.addConditions(userTable.ID.eq(userId));
Record record = selectQuery.fetchOne();

System.out.println(record.get(userTable.LAST_NAME)); //prints "test1"
System.out.println(record.get(modifiedUserTable.LAST_NAME)); //prints "test1", would expect null as modifiedUserTable is currently not joined

Tested on jooq 3.9.3 and 3.9.5

Upvotes: 1

Views: 725

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221145

Works as designed

In SQL, there is no such thing as a qualified column name in a result set. Instead, a result set (like any other table) has a set of columns and each column has a name, which is described by jOOQ's Field.getName(). Now, "unfortunately", in top-level SELECT statements, you are allowed to have duplicate column names, in all SQL dialects, and also in jOOQ. This is useful when you join two tables and both tables have e.g. an ID column. That way, you don't have to rename each column just because an ambiguity arises.

If you do have duplicate column names in a table / result, jOOQ will apply the algorithm described in TableLike.field(Field)

This will return:

  • A field that is the same as the argument field (by identity comparison).
  • A field that is equal to the argument field (exact matching fully qualified name).
  • A field that is equal to the argument field (partially matching qualified name).
  • A field whose name is equal to the name of the argument field.
  • null otherwise.

If several fields have the same name, the first one is returned and a warning is logged.

As you can see, the rationale here is that if there is no full or partial identity or qualified name equality between a field in the result set and the field you're looking up in the result set, then the field name as in Field.getName() is used to look up the field.

Side note on column match ambiguity

At first, you've mentioned that there was an "ambiguous match" warning in the logs, which then disappeared. That warning is there to indicate that two columns go by the same Field.getName(), but neither of them is an "exact" match as described before. In that case, you will get the first column as a match (for historic reasons), and that warning, because that might not be what you wanted to do.

Upvotes: 1

Related Questions