cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jurgen Doll (JIRA)" <j...@apache.org>
Subject [jira] [Created] (CAY-2085) Duplicate JOIN in Cayenne SQL
Date Mon, 23 May 2016 20:06:12 GMT
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)

Mime
View raw message