Ключ jOOq производит плохой запрос
Кажется, в jOOq 3.7.3 есть ошибка, если мы дважды ссылаемся на константу Keys. Возьмите пример ниже:
final Select<Record10<BigDecimal, String, String, String, Timestamp, BigDecimal, BigDecimal, String, String, String>> query = readContext
.select(li.LIBRARY_ITEM_ID,
li.TITLE,
li.DETAILED_DESCRIPTION,
li.URL,
li.CREATE_DATE,
li.PORTFOLIO_ID,
li.ORGANIZATION_ID,
DSL.decode()
.when(o_aux_lead_center.ORGANIZATION_ID.isNotNull(),
o_aux_lead_center.ORGANIZATION_NAME)
.otherwise(o_lead_center.ORGANIZATION_NAME).as(ORG_NAME),
DSL.decode()
.when(o_aux_lead_center.ORGANIZATION_ID.isNotNull(),
o_aux_lead_center.ACRONYM).otherwise(o_lead_center.ACRONYM)
.as(ORG_ACRONYM), o_resp_mission_dir.ORGANIZATION_NAME)
.from(li
.join(lc.join(lct).onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE.equal(LkuCodeTypeLookup.LIBRARY_ITEM_TYPE)))
.onKey(Keys.LI_LC_LITI___FK)
.and(lc.CODE.equal(LkuCodeLookup.LIBRARY_ITEM_TYPE_NEWS_STORY))
// The Portfolio
.join(p.join(lc).onKey(Keys.P_LC_ATI___FK)
.and(lc.CODE.in(LkuCodeLookup.PORTFOLIO_ACTIVITY_TYPE_TECHNOLOGY))
.join(lct).onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE.equal(LkuCodeTypeLookup.ACTIVITY_TYPE)))
.on(li.PORTFOLIO_ID.equal(p.PORTFOLIO_ID))
// The CO for the Portfolio associated with the library item
.join(co
.join(lc.join(lct).onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE.equal(LkuCodeTypeLookup.OBJECT_TYPE)))
.onKey(Keys.CO_LC_OTI___FK)
.and(lc.CODE.equal(LkuCodeLookup.OBJECTTYPE_PORTFOLIOS))
.and(limitByRelease).join(tu).on(tu.USER_ID.equal(uid))
.and(limitBySensitivities))
.on(p.PORTFOLIO_ID.equal(co.OBJECT_ID))
// The CO for the Library Item
.join(co
.join(lc.join(lct).onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE.equal(LkuCodeTypeLookup.OBJECT_TYPE)))
.onKey(Keys.CO_LC_OTI___FK)
.and(lc.CODE.equal(LkuCodeLookup.OBJECTTYPE_LIBRARY_ITEMS))
.andExists(
readContext
.selectFrom(cos)
.where(cos.CORE_OBJECT_ID.equal(co.CORE_OBJECT_ID))
.and(cos.SECTION
.equal(BigDecimal
.valueOf(AccessControlConstants.CORE_OBJECT_CHAR_ARRAY_SELF))
.and(cos.SENSITIVITIES.equal(BigDecimal.ZERO)))))
.on(li.LIBRARY_ITEM_ID.equal(co.OBJECT_ID)))
// The Responsible Mission Directorate
.leftOuterJoin(
po
.join(o_resp_mission_dir)
.on(po.ORGANIZATION_ID.equal(o_resp_mission_dir.ORGANIZATION_ID))
.join(lc)
.onKey(Keys.PO_LC_ATI___FK)
.and(lc.CODE
.equal(LkuCodeLookup.ASSOCIATIONTYPE_RESPONSIBLE_MISSION_DIRECTORATE))
.join(lct)
.onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE
.equal(LkuCodeTypeLookup.PORTFOLIO_ORGANIZATION_ASSOCIATION_TYPE)))
.onKey(Keys.PO_P___FK)
// The Portfolio Organizations
.leftOuterJoin(
po
// Add in the organization data
.join(o_lead_center.leftOuterJoin(o_aux_lead_center).on(
o_lead_center.REPLACEMENT_ORGANIZATION_ID.equal(
o_aux_lead_center.ORGANIZATION_ID).and(
o_lead_center.IS_ACTIVE.equal(byteZero))))
.on(po.ORGANIZATION_ID.equal(o_lead_center.ORGANIZATION_ID))
.join(lc)
.onKey(Keys.PO_LC_ATI___FK)
.and(lc.CODE.equal(LkuCodeLookup.ASSOCIATIONTYPE_LEAD_CENTER))
.join(lct)
.onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE
.equal(LkuCodeTypeLookup.PORTFOLIO_ORGANIZATION_ASSOCIATION_TYPE)))
.onKey(Keys.PO_P___FK)
.orderBy(
DSL.decode().when(li.UPDATE_DATE.isNull(), li.CREATE_DATE)
.otherwise(li.UPDATE_DATE).desc());
Это создает неверный запрос SQL (Oracle 12C):
select
"SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_ID",
"SCHEMA_A"."LIBRARY_ITEMS"."TITLE",
"SCHEMA_A"."LIBRARY_ITEMS"."DETAILED_DESCRIPTION",
"SCHEMA_A"."LIBRARY_ITEMS"."URL",
"SCHEMA_A"."LIBRARY_ITEMS"."CREATE_DATE",
"SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ID",
"SCHEMA_A"."LIBRARY_ITEMS"."ORGANIZATION_ID",
case when "AuxLeadCenter"."ORGANIZATION_ID" is not null then "AuxLeadCenter"."ORGANIZATION_NAME"
else "LeadCenter"."ORGANIZATION_NAME"
end "ORG_NAME",
case when "AuxLeadCenter"."ORGANIZATION_ID" is not null then "AuxLeadCenter"."ACRONYM"
else "LeadCenter"."ACRONYM"
end "ORG_ACRONYM",
"RespMissionDir"."ORGANIZATION_NAME"
from "SCHEMA_A"."LIBRARY_ITEMS"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'LIBRARY_ITEM_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'NEWS_STORY'
)
join (
"SCHEMA_A"."LIBRARY"
join "SCHEMA_A"."LKU_CODE"
on (
"SCHEMA_A"."LIBRARY"."ACTIVITY_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" in (
'MISSION_WITH_TECHNOLOGY', 'TECHNOLOGY_ONLY'
)
)
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'ACTIVITY_TYPE'
)
)
on "SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ID" = "SCHEMA_A"."LIBRARY"."LIBRARY_ID"
join (
"SCHEMA_A"."AC"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'OBJECT_TYPE'
)
)
on (
"SCHEMA_A"."AC"."OBJECT_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'PORTFOLIOS'
and exists (
select
"SCHEMA_A"."AC_RELEASE_TYPES"."AC_ID",
"SCHEMA_A"."AC_RELEASE_TYPES"."SECTION",
"SCHEMA_A"."AC_RELEASE_TYPES"."RELEASE_TYPE",
"SCHEMA_A"."AC_RELEASE_TYPES"."CREATE_DATE",
"SCHEMA_A"."AC_RELEASE_TYPES"."CREATED_BY",
"SCHEMA_A"."AC_RELEASE_TYPES"."UPDATE_DATE",
"SCHEMA_A"."AC_RELEASE_TYPES"."UPDATED_BY"
from "SCHEMA_A"."AC_RELEASE_TYPES"
where (
"SCHEMA_A"."AC"."AC_ID" = "SCHEMA_A"."AC_RELEASE_TYPES"."AC_ID"
and "SCHEMA_A"."AC_RELEASE_TYPES"."SECTION" = 1
and "SCHEMA_A"."AC_RELEASE_TYPES"."RELEASE_TYPE" in (
6, 7
)
)
)
)
join "SCHEMA_A"."TP_USERS"
on (
"SCHEMA_A"."TP_USERS"."USER_ID" = 456920
and exists (
select
"SCHEMA_A"."AC_SENSITIVITIES"."AC_ID",
"SCHEMA_A"."AC_SENSITIVITIES"."SECTION",
"SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES",
"SCHEMA_A"."AC_SENSITIVITIES"."CREATE_DATE",
"SCHEMA_A"."AC_SENSITIVITIES"."CREATED_BY",
"SCHEMA_A"."AC_SENSITIVITIES"."UPDATE_DATE",
"SCHEMA_A"."AC_SENSITIVITIES"."UPDATED_BY"
from "SCHEMA_A"."AC_SENSITIVITIES"
where (
"SCHEMA_A"."AC"."AC_ID" = "SCHEMA_A"."AC_SENSITIVITIES"."AC_ID"
and "SCHEMA_A"."AC_SENSITIVITIES"."SECTION" = 0
and bitand(
"SCHEMA_A"."TP_USERS"."SENSITIVITIES",
"SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES") = "SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES"
)
)
)
)
on "SCHEMA_A"."LIBRARY"."LIBRARY_ID" = "SCHEMA_A"."AC"."OBJECT_ID"
join (
"SCHEMA_A"."AC"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'OBJECT_TYPE'
)
)
on (
"SCHEMA_A"."AC"."OBJECT_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'LIBRARY_ITEMS'
and exists (
select
"SCHEMA_A"."AC_SENSITIVITIES"."AC_ID",
"SCHEMA_A"."AC_SENSITIVITIES"."SECTION",
"SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES",
"SCHEMA_A"."AC_SENSITIVITIES"."CREATE_DATE",
"SCHEMA_A"."AC_SENSITIVITIES"."CREATED_BY",
"SCHEMA_A"."AC_SENSITIVITIES"."UPDATE_DATE",
"SCHEMA_A"."AC_SENSITIVITIES"."UPDATED_BY"
from "SCHEMA_A"."AC_SENSITIVITIES"
where (
"SCHEMA_A"."AC_SENSITIVITIES"."AC_ID" = "SCHEMA_A"."AC"."AC_ID"
and "SCHEMA_A"."AC_SENSITIVITIES"."SECTION" = 0
and "SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES" = 0
)
)
)
)
on "SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_ID" = "SCHEMA_A"."AC"."OBJECT_ID"
left outer join (
"SCHEMA_A"."LIBRARY_ORGANIZATIONS"
join "SCHEMA_A"."ORGANIZATIONS" "RespMissionDir"
on "SCHEMA_A"."LIBRARY_ORGANIZATIONS"."ORGANIZATION_ID" = "RespMissionDir"."ORGANIZATION_ID"
join "SCHEMA_A"."LKU_CODE"
on (
"SCHEMA_A"."LIBRARY_ORGANIZATIONS"."ASSOCIATION_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'RESPONSIBLE_MISSION_DIRECTORATE'
)
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'PORTFOLIO_ORGANIZATION_ASSOCIATION_TYPE'
)
)
on "SCHEMA_A"."LIBRARY_ORGANIZATIONS"."LIBRARY_ID" = "SCHEMA_A"."LIBRARY"."LIBRARY_ID"
left outer join (
"SCHEMA_A"."LIBRARY_ORGANIZATIONS"
join (
"SCHEMA_A"."ORGANIZATIONS" "LeadCenter"
left outer join "SCHEMA_A"."ORGANIZATIONS" "AuxLeadCenter"
on (
"LeadCenter"."REPLACEMENT_ORGANIZATION_ID" = "AuxLeadCenter"."ORGANIZATION_ID"
and "LeadCenter"."IS_ACTIVE" = 0
)
)
on "SCHEMA_A"."LIBRARY_ORGANIZATIONS"."ORGANIZATION_ID" = "LeadCenter"."ORGANIZATION_ID"
join "SCHEMA_A"."LKU_CODE"
on (
"SCHEMA_A"."LIBRARY_ORGANIZATIONS"."ASSOCIATION_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'LEAD_CENTER'
)
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'PORTFOLIO_ORGANIZATION_ASSOCIATION_TYPE'
)
)
on "SCHEMA_A"."LIBRARY_ORGANIZATIONS"."LIBRARY_ID" = "SCHEMA_A"."LIBRARY_ORGANIZATIONS"."LIBRARY_ID"
order by case when "SCHEMA_A"."LIBRARY_ITEMS"."UPDATE_DATE" is null then "SCHEMA_A"."LIBRARY_ITEMS"."CREATE_DATE"
else "SCHEMA_A"."LIBRARY_ITEMS"."UPDATE_DATE"
end desc
Обходной путь, который я нашел, чтобы удалить вторую ссылку на Keys.PO_P___FK и заменить его фактическим ключом, но кто-нибудь еще видит это? Кто-нибудь знает, почему он правильно устанавливает данные "SCHEMA_A"."LIBRARY_ORGANIZATIONS"."LIBRARY_ID" = "SCHEMA_A"."LIBRARY"."LIBRARY_ID" первый раз, но во второй раз он вызывает перекрестное соединение, потому что он устанавливает на "SCHEMA_A"."LIBRARY_ORGANIZATIONS"."LIBRARY_ID" = "SCHEMA_A"."LIBRARY_ORGANIZATIONS"."LIBRARY_ID"?
1 ответ
Решение
ON KEY метод объединения переводится на обычный ON присоединиться к предикату, найдя "лучшие" таблицы соответствия с обеих сторон. Существует ошибка ( # 5209), из-за которой таблица внешнего ключа в ключе сопоставляется первой, а при сопоставлении таблица первичного ключа не проверяется. Из-за этого ключ решен в неправильном направлении.
В вашем запросе "SCHEMA_A"."LIBRARY_ORGANIZATIONS" таблица объединяется дважды без переименования:
left outer join (
"SCHEMA_A"."LIBRARY_ORGANIZATIONS"
join "SCHEMA_A"."ORGANIZATIONS" "RespMissionDir"
А также:
left outer join (
"SCHEMA_A"."LIBRARY_ORGANIZATIONS"
join (
"SCHEMA_A"."ORGANIZATIONS" "LeadCenter"
Эта неоднозначность не может быть правильно решена с помощью jOOQ. Начиная с версии jOOQ 3.7.3 ( # 5047) вы должны увидеть предупреждение в файлах журнала, указывающее, что вы используете onKey() не может быть решено не однозначно.
В общем, это хорошая идея, чтобы избежать onKey() когда деревья таблицы соединений становятся слишком сложными.