Jurgen Doll created CAY-2085:
--------------------------------
Summary: Duplicate JOIN in Cayenne SQL
Key: CAY-2085
URL: https://issues.apache.org/jira/browse/CAY-2085
Project: Cayenne
Issue Type: Bug
Components: Core Library
Affects Versions: 3.1.1, 3.1
Reporter: Jurgen Doll
I have 3 DbEntities related to one another:
RPT_TEMPLATE_GROUPS -> L_RPT_TEMPLATE_GROUPS -> RPT_TEMPLATE_OBJECTS
And I have 2 ObjEntities based on them:
ReportTemplate (RPT_TEMPLATE_GROUPS) with relationship getLines()
ReportTemplateLine (RPT_TEMPLATE_OBJECTS)
However an unusual feature is that ReportTemplateLine has an attribute "order"
that has a Db Path to a field in L_RPT_TEMPLATE_GROUPS
The result of this arrangement when invoking ReportTemplate.getLines() is the
following SQL, excluding select fields:
SELECT * FROM RPT_TEMPLATE_OBJECTS t0 LEFT
JOIN L_RPT_TEMPLATE_GROUPS t1 ON (t0.RPT_TEMP_ID = t1.RPT_TEMP_ID){color:red}
JOIN L_RPT_TEMPLATE_GROUPS t2 ON (t0.RPT_TEMP_ID = t2.RPT_TEMP_ID){color}
WHERE t2.RPT_TEMP_GROUP_ID = ?
Note the duplicate join which is unnecessary and results in the DB returning
incorrect rows.
Another issue related to this occurred while trying to create a work around by
using SQLTemplate with a corrected form of the above SQL. The query executes
correctly but when accessing each object Cayenne does a DB request (even though
all the required data is present):
SELECT * FROM PRT_TEMPLATE_OBJECTS t0
LEFT JOIN L_RPT_TEMPLATE_GROUPS t1 ON (t0.RPT_TEMP_ID = t1.RPT_TEMP_ID)
WHERE t0.RPT_T_OBJ_ID = ?
In summary then there is firstly a superfluous JOIN resulting in incorrect
data, and secondly a superfluous DB query when the data/field is already
present.
Here is my data map:
<?xml version="1.0" encoding="utf-8"?>
<data-map xmlns="http://cayenne.apache.org/schema/3.0/modelMap";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://cayenne.apache.org/schema/3.0/modelMap
http://cayenne.apache.org/schema/3.0/modelMap.xsd";
project-version="6">
<property name="defaultPackage" value="tables"/>
<property name="quoteSqlIdentifiers" value="true"/>
<db-entity name="L_RPT_TEMPLATE_GROUPS">
<db-attribute name="RPT_TEMP_GROUP_ID" type="BIGINT"
isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
<db-attribute name="RPT_TEMP_ID" type="BIGINT"
isPrimaryKey="true" isMandatory="true"/>
<db-attribute name="RPT_TEMP_ORDER" type="INTEGER"/>
</db-entity>
<db-entity name="RPT_TEMPLATE_OBJECTS">
<db-attribute name="RPT_TEMP_ID" type="BIGINT"/>
<db-attribute name="RPT_T_OBJ_ID" type="BIGINT"
isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
<db-attribute name="RPT_T_OBJ_SETTINGS" type="LONGVARCHAR"/>
<db-attribute name="RPT_T_OBJ_TEXT" type="LONGVARCHAR"/>
<db-attribute name="RPT_T_OBJ_TYPE" type="VARCHAR" length="20"/>
</db-entity>
<db-entity name="RPT_TEMPLATE_GROUPS">
<db-attribute name="DIC_LANG" type="VARCHAR" length="20"/>
<db-attribute name="FSTATUS" type="CHAR" isMandatory="true"
length="1"/>
<db-attribute name="RPT_TEMP_GROUP_DESC" type="VARCHAR"
length="30"/>
<db-attribute name="RPT_TEMP_GROUP_ID" type="BIGINT"
isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
</db-entity>
<obj-entity name="ReportTemplate" className="tables.ReportTemplate"
readOnly="true" dbEntityName="RPT_TEMPLATE_GROUPS">
<obj-attribute name="language" type="java.lang.String"
db-attribute-path="DIC_LANG"/>
<obj-attribute name="name" type="java.lang.String"
db-attribute-path="RPT_TEMP_GROUP_DESC"/>
</obj-entity>
<obj-entity name="ReportTemplateLine"
className="tables.ReportTemplateLine" readOnly="true"
dbEntityName="RPT_TEMPLATE_OBJECTS">
<obj-attribute name="order" type="int"
db-attribute-path="ReportGroupsRel.RPT_TEMP_ORDER"/>
<obj-attribute name="settings" type="java.lang.String"
db-attribute-path="RPT_T_OBJ_SETTINGS"/>
<obj-attribute name="text" type="java.lang.String"
db-attribute-path="RPT_T_OBJ_TEXT"/>
<obj-attribute name="type" type="java.lang.String"
db-attribute-path="RPT_T_OBJ_TYPE"/>
</obj-entity>
<db-relationship name="ReportTemplateObjRel"
source="L_RPT_TEMPLATE_GROUPS" target="RPT_TEMPLATE_OBJECTS" toMany="true">
<db-attribute-pair source="RPT_TEMP_ID" target="RPT_TEMP_ID"/>
</db-relationship>
<db-relationship name="ReportGroupsRel" source="RPT_TEMPLATE_OBJECTS"
target="L_RPT_TEMPLATE_GROUPS" toMany="true">
<db-attribute-pair source="RPT_TEMP_ID" target="RPT_TEMP_ID"/>
</db-relationship>
<db-relationship name="TemplatesRel" source="RPT_TEMPLATE_GROUPS"
target="L_RPT_TEMPLATE_GROUPS" toDependentPK="true" toMany="true">
<db-attribute-pair source="RPT_TEMP_GROUP_ID"
target="RPT_TEMP_GROUP_ID"/>
</db-relationship>
<obj-relationship name="lines" source="ReportTemplate"
target="ReportTemplateLine" deleteRule="Deny"
db-relationship-path="TemplatesRel.ReportTemplateObjRel"/>
</data-map>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
