[OFBIZ-2976] view-entity with condition-expr joined with another view-entity as rel-optional=true is treated as rel-optional=false

• Type: Bug
• Status: Patch Available
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: Trunk, Upcoming Branch
• Component/s: framework

Environment

Java v1.5.0_19-b02-298 MacOS X 10.5.8

• Sprint: Bug Crush Event - 21/2/2015
• Assignee: Jacques Le Roux
• Reporter: Jennifer Weston
• Created: 28/Sep/09 21:11
• Updated: 11/Jan/22 08:48

Description

If a view-entity is defined with condition-exprs and used as a member-entity in a second view-entity and the view-link is rel-optional=true, the link is treated as rel-optional=false.

In the following example, the view-entity PPRMinQty is a list of all PriceBreakRules that have a minimum quantity defined. The view-entity ProductPriceBreakMinQty uses PPRMinQty with ProductCategoryMember and ProductPriceCond (this time used for product categories) to create a list of productIds with their productPriceRuleIds and their minimum quantities (if any). The rel-optional=true should mean that even productPriceRuleids that don't have a minimum quantity defined should be included in the result set.

<view-entity entity-name="PPRMinQty"
        package-name="com.mavericklabel"
        title="Minimum qty for a product price rule">
    <member-entity entity-alias="PPC" entity-name="ProductPriceCond"/>
    <alias name="productPriceRuleId" entity-alias="PPC" field="productPriceRuleId"/>
    <alias name="minQty" entity-alias="PPC" field="condValue"/>
    <alias name="inputParamEnumId" entity-alias="PPC" field="inputParamEnumId"/>
    <alias name="operatorEnumId" entity-alias="PPC" field="operatorEnumId"/>
    <entity-condition>
        <condition-list>
            <condition-expr entity-alias="PPC" field-name="inputParamEnumId" operator="equals" value="PRIP_QUANTITY"/>
            <condition-expr entity-alias="PPC" field-name="operatorEnumId" operator="equals" value="PRC_GTE"/>
        </condition-list>
    </entity-condition>
</view-entity>

<view-entity entity-name="ProductPriceBreakMinQty"
        package-name="com.mavericklabel"
        title="Price breaks for each product with their minimum quantity (min qty is optional)">
    <member-entity entity-alias="PPRCAT" entity-name="ProductPriceCond"/>
    <member-entity entity-alias="PCM" entity-name="ProductCategoryMember"/>
    <member-entity entity-alias="MINQTY" entity-name="PPRMinQty"/>
    <alias name="productId" entity-alias="PCM" field="productId"/>
    <alias name="productPriceRuleId" entity-alias="PPRCAT" field="productPriceRuleId"/>
    <alias name="minQty" entity-alias="MINQTY" field="minQty"/>
    <alias name="inputParamEnumId" entity-alias="PPRCAT" field="inputParamEnumId"/>
    <alias name="operatorEnumId" entity-alias="PPRCAT" field="operatorEnumId"/>
    <view-link entity-alias="PPRCAT" rel-entity-alias="PCM">
        <key-map field-name="condValue" rel-field-name="productCategoryId"/>
    </view-link>
    <view-link entity-alias="PPRCAT" rel-entity-alias="MINQTY" rel-optional="true">
        <key-map field-name="productPriceRuleId"/>
    </view-link>
    <entity-condition>
        <condition-list>
            <condition-expr entity-alias="PPRCAT" field-name="inputParamEnumId" operator="equals" value="PRIP_PROD_CAT_ID"/>
            <condition-expr entity-alias="PPRCAT" field-name="operatorEnumId" operator="equals" value="PRC_EQ"/>
        </condition-list>
    </entity-condition>
</view-entity>

The resulting SQL looks like this:

SELECT PCM.PRODUCT_ID, PPRCAT.PRODUCT_PRICE_RULE_ID, MINQTY.PPC_COND_VALUE, PPRCAT.INPUT_PARAM_ENUM_ID, PPRCAT.OPERATOR_ENUM_ID FROM (OFBIZ.PRODUCT_PRICE_COND PPRCAT INNER JOIN OFBIZ.PRODUCT_CATEGORY_MEMBER PCM ON PPRCAT.COND_VALUE = PCM.PRODUCT_CATEGORY_ID) LEFT OUTER JOIN (SELECT PPC.PRODUCT_PRICE_RULE_ID AS PPC_PRODUCT_PRICE_RULE_ID, PPC.COND_VALUE AS PPC_COND_VALUE, PPC.INPUT_PARAM_ENUM_ID AS PPC_INPUT_PARAM_ENUM_ID, PPC.OPERATOR_ENUM_ID AS PPC_OPERATOR_ENUM_ID FROM OFBIZ.PRODUCT_PRICE_COND PPC) MINQTY ON PPRCAT.PRODUCT_PRICE_RULE_ID = MINQTY.PPC_PRODUCT_PRICE_RULE_ID WHERE ((PPRCAT.INPUT_PARAM_ENUM_ID = 'PRIP_PROD_CAT_ID' AND PPRCAT.OPERATOR_ENUM_ID = 'PRC_EQ') AND (PPC.INPUT_PARAM_ENUM_ID = 'PRIP_QUANTITY' AND PPC.OPERATOR_ENUM_ID = 'PRC_GTE'))

instead of

SELECT PCM.PRODUCT_ID, PPRCAT.PRODUCT_PRICE_RULE_ID, MINQTY.PPC_COND_VALUE, PPRCAT.INPUT_PARAM_ENUM_ID, PPRCAT.OPERATOR_ENUM_ID FROM (OFBIZ.PRODUCT_PRICE_COND PPRCAT INNER JOIN OFBIZ.PRODUCT_CATEGORY_MEMBER PCM ON PPRCAT.COND_VALUE = PCM.PRODUCT_CATEGORY_ID) LEFT OUTER JOIN (SELECT PPC.PRODUCT_PRICE_RULE_ID AS PPC_PRODUCT_PRICE_RULE_ID, PPC.COND_VALUE AS PPC_COND_VALUE, PPC.INPUT_PARAM_ENUM_ID AS PPC_INPUT_PARAM_ENUM_ID, PPC.OPERATOR_ENUM_ID AS PPC_OPERATOR_ENUM_ID FROM OFBIZ.PRODUCT_PRICE_COND PPC WHERE ((PPC.INPUT_PARAM_ENUM_ID = 'PRIP_QUANTITY' AND PPC.OPERATOR_ENUM_ID = 'PRC_GTE'))) MINQTY ON PPRCAT.PRODUCT_PRICE_RULE_ID = MINQTY.PPC_PRODUCT_PRICE_RULE_ID WHERE ((PPRCAT.INPUT_PARAM_ENUM_ID = 'PRIP_PROD_CAT_ID' AND PPRCAT.OPERATOR_ENUM_ID = 'PRC_EQ'))

The fact that the conditions from the subquery in the from have been moved to the top level query create the effect of an inner join even though an outer join is specified.

The top level where is built by GenericDAO through ModelViewEntity.populateViewEntityConditionInformation. The change to ModelViewEntity.populateViewEntityConditionInformation moved this condition to the top was committed in r805519.

The code that builds the subquery view in the from is SqlJdbcUtil.makeViewWhereClause. It looks like it's never added conditions (only joins) going back to v4.0.