[jira] Created: (OFBIZ-336) Improve complex-alias to handle nulls and defaults

[Nicolas Malin (Jira)]

Improve complex-alias to handle nulls and defaults
--------------------------------------------------

                 Key: OFBIZ-336
                 URL: http://issues.apache.org/jira/browse/OFBIZ-336
             Project: OFBiz (The Open for Business Project)
          Issue Type: Improvement
          Components: framework
            Reporter: Leon Torres
            Priority: Minor


The idea behind complex-alias is useful for certain things, but the way it works now is not useful.  Suppose you build a simple addition rule.  If any variable in the equation is null, the entire alias resolves to null.

For example, suppose you create a view entity to report quantities on OrderItem.  The query would be:

select product_id, (quantity - cancel_quantity) from order_item;

However if you run this, you'll see that where quantity or cancel_quantity are null, the result is null.  A solution in postgresql is to use the coalesce function to specify a default value:

select product_id, (coalesce(quantity, 0) - coalesce(cancel_quantity, 0)) from order_item;

Now the query works.

So the fix I think is to add another attribute default-value="something".  If the default-value is present, then add the coalesce function.  Not sure whether this is standard sql or not.

I also encountered this issue for concatenation ||  operator, so I think it affects most operations.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[Nicolas Malin (Jira)]

    [ http://issues.apache.org/jira/browse/OFBIZ-336?page=comments#action_12438173 ]
           
Leon Torres commented on OFBIZ-336:
-----------------------------------

Right now I'm trying to fix some of the order report view entities.  They don't subtract the cancelQuantity, so I tried adding complex-alias to resolve that problem.  For instance, in OrderReportSalesGroupByProduct, you can try replacing the "quantity" alias with the following:

      <alias entity-alias="OI" name="quantity" function="sum">
          <complex-alias operator="-">
              <complex-alias-field entity-alias="OI" field="quantity"/>
              <complex-alias-field entity-alias="OI" field="cancelQuantity"/>
          </complex-alias>
      </alias>

If you run the sales by store report, you'll see that only OrderItems with both quantity and cancelQuantity not null would get resolved.

> Improve complex-alias to handle nulls and defaults
> --------------------------------------------------
>
>                 Key: OFBIZ-336
>                 URL: http://issues.apache.org/jira/browse/OFBIZ-336
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Improvement
>          Components: framework
>            Reporter: Leon Torres
>            Priority: Minor
>
> The idea behind complex-alias is useful for certain things, but the way it works now is not useful.  Suppose you build a simple addition rule.  If any variable in the equation is null, the entire alias resolves to null.
> For example, suppose you create a view entity to report quantities on OrderItem.  The query would be:
> select product_id, (quantity - cancel_quantity) from order_item;
> However if you run this, you'll see that where quantity or cancel_quantity are null, the result is null.  A solution in postgresql is to use the coalesce function to specify a default value:
> select product_id, (coalesce(quantity, 0) - coalesce(cancel_quantity, 0)) from order_item;
> Now the query works.
> So the fix I think is to add another attribute default-value="something".  If the default-value is present, then add the coalesce function.  Not sure whether this is standard sql or not.
> I also encountered this issue for concatenation ||  operator, so I think it affects most operations.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[David E Jones-2]

It would be great to see this issue addressed, but a couple of  
thoughts about it:

- we need to find out if without the coalesce function this happens  
in _any_ other databases, ie some databases (well, all databases)  
have funny quirks like this of some sort or other

- it would be good to find out of the coalesce function exists in any  
other databases; I have a good book on this topic at home (the SQL  
reference from Oreilly that covers 5 different databases), but it  
will be about three weeks before I'll within a few thousand miles of  
that...

- chances are this is a PostgreSQL quirk in which case we'll probably  
want to just have this be the default behavior if an attribute  
specifically for it is set on the datasource element in the  
entityengine.xml file, like various other attributes we have set  
there to make things work more consistently on the application side  
of the entity engine

-David


On Sep 27, 2006, at 6:00 PM, Leon Torres (JIRA) wrote:

> Improve complex-alias to handle nulls and defaults
> --------------------------------------------------
>
>                  Key: OFBIZ-336
>                  URL: http://issues.apache.org/jira/browse/OFBIZ-336
>              Project: OFBiz (The Open for Business Project)
>           Issue Type: Improvement
>           Components: framework
>             Reporter: Leon Torres
>             Priority: Minor
>
>
> The idea behind complex-alias is useful for certain things, but the  
> way it works now is not useful.  Suppose you build a simple  
> addition rule.  If any variable in the equation is null, the entire  
> alias resolves to null.
>
> For example, suppose you create a view entity to report quantities  
> on OrderItem.  The query would be:
>
> select product_id, (quantity - cancel_quantity) from order_item;
>
> However if you run this, you'll see that where quantity or  
> cancel_quantity are null, the result is null.  A solution in  
> postgresql is to use the coalesce function to specify a default value:
>
> select product_id, (coalesce(quantity, 0) - coalesce
> (cancel_quantity, 0)) from order_item;
>
> Now the query works.
>
> So the fix I think is to add another attribute default-
> value="something".  If the default-value is present, then add the  
> coalesce function.  Not sure whether this is standard sql or not.
>
> I also encountered this issue for concatenation ||  operator, so I  
> think it affects most operations.
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the  
> administrators: http://issues.apache.org/jira/secure/ 
> Administrators.jspa
> -
> For more information on JIRA, see: http://www.atlassian.com/ 
> software/jira
>
>

[Leon Torres-2]

Hi David and all,

I looked around and it COALESCE is an ANSI SQL feature that is supported by
oracle, postgresql, mysql and sql server.  I tried directly by hand using
postgresql and mysql.

Still it is necessary to test this across every possible database.  I have a
patch ready that implements the feature.  In particular, the way it is
implemented (surrounds the default value in single quotations) needs to be tested.

I'll update the jira with a patch and notes on the implementation quirk.

This could be used for regular <alias> fields as well, but for now the patch
implements it for <complex-alias> only.

- Leon


David E Jones wrote:

>
> It would be great to see this issue addressed, but a couple of thoughts
> about it:
>
> - we need to find out if without the coalesce function this happens in
> _any_ other databases, ie some databases (well, all databases) have
> funny quirks like this of some sort or other
>
> - it would be good to find out of the coalesce function exists in any
> other databases; I have a good book on this topic at home (the SQL
> reference from Oreilly that covers 5 different databases), but it will
> be about three weeks before I'll within a few thousand miles of that...
>
> - chances are this is a PostgreSQL quirk in which case we'll probably
> want to just have this be the default behavior if an attribute
> specifically for it is set on the datasource element in the
> entityengine.xml file, like various other attributes we have set there
> to make things work more consistently on the application side of the
> entity engine
>
> -David
>
>
> On Sep 27, 2006, at 6:00 PM, Leon Torres (JIRA) wrote:
>
>> Improve complex-alias to handle nulls and defaults
>> --------------------------------------------------
>>
>>                  Key: OFBIZ-336
>>                  URL: http://issues.apache.org/jira/browse/OFBIZ-336
>>              Project: OFBiz (The Open for Business Project)
>>           Issue Type: Improvement
>>           Components: framework
>>             Reporter: Leon Torres
>>             Priority: Minor
>>
>>
>> The idea behind complex-alias is useful for certain things, but the
>> way it works now is not useful.  Suppose you build a simple addition
>> rule.  If any variable in the equation is null, the entire alias
>> resolves to null.
>>
>> For example, suppose you create a view entity to report quantities on
>> OrderItem.  The query would be:
>>
>> select product_id, (quantity - cancel_quantity) from order_item;
>>
>> However if you run this, you'll see that where quantity or
>> cancel_quantity are null, the result is null.  A solution in
>> postgresql is to use the coalesce function to specify a default value:
>>
>> select product_id, (coalesce(quantity, 0) - coalesce(cancel_quantity,
>> 0)) from order_item;
>>
>> Now the query works.
>>
>> So the fix I think is to add another attribute
>> default-value="something".  If the default-value is present, then add
>> the coalesce function.  Not sure whether this is standard sql or not.
>>
>> I also encountered this issue for concatenation ||  operator, so I
>> think it affects most operations.
>>
>> --This message is automatically generated by JIRA.
>> -
>> If you think it was sent incorrectly contact one of the
>> administrators: http://issues.apache.org/jira/secure/Administrators.jspa
>> -
>> For more information on JIRA, see: http://www.atlassian.com/software/jira
>>
>>
>
>

[Nicolas Malin (Jira)]

     [ http://issues.apache.org/jira/browse/OFBIZ-336?page=all ]

Leon Torres updated OFBIZ-336:
------------------------------

    Attachment: complex-alias-default-value.patch

Here's an implementation of default-value for complex alias.  I removed two constructor methods in ComplexAliasField that weren't being used, one of which would cause confusion with the new field.

To use, try something like the following:

      <alias entity-alias="OI" name="quantity" function="sum">
          <complex-alias operator="-">
              <complex-alias-field entity-alias="OI" field="quantity" default-value="0"/>
              <complex-alias-field entity-alias="OI" field="cancelQuantity" default-value="0"/>
          </complex-alias>
      </alias>

This should be tested across as many databases as possible.

There is an implementation quirk that absolutely needs review.  The coalesce function is generated as follows:

COALESCE(${fieldName},'${defaultValue}')

Note the single quotations around defaultVaue.  In the example, this gets resolved as COALESCE(OI.QUANTITY,'0').  This actually works on postgresql and mysql, but I don't know if this will cause issues on other databases. If it does, then we'll need to detect the field type and set it up accordingly.

> Improve complex-alias to handle nulls and defaults
> --------------------------------------------------
>
>                 Key: OFBIZ-336
>                 URL: http://issues.apache.org/jira/browse/OFBIZ-336
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Improvement
>          Components: framework
>            Reporter: Leon Torres
>            Priority: Minor
>         Attachments: complex-alias-default-value.patch
>
>
> The idea behind complex-alias is useful for certain things, but the way it works now is not useful.  Suppose you build a simple addition rule.  If any variable in the equation is null, the entire alias resolves to null.
> For example, suppose you create a view entity to report quantities on OrderItem.  The query would be:
> select product_id, (quantity - cancel_quantity) from order_item;
> However if you run this, you'll see that where quantity or cancel_quantity are null, the result is null.  A solution in postgresql is to use the coalesce function to specify a default value:
> select product_id, (coalesce(quantity, 0) - coalesce(cancel_quantity, 0)) from order_item;
> Now the query works.
> So the fix I think is to add another attribute default-value="something".  If the default-value is present, then add the coalesce function.  Not sure whether this is standard sql or not.
> I also encountered this issue for concatenation ||  operator, so I think it affects most operations.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[Nicolas Malin (Jira)]

     [ http://issues.apache.org/jira/browse/OFBIZ-336?page=all ]

Leon Torres updated OFBIZ-336:
------------------------------

    Attachment: complex-alias-default-value2.patch

Derby did not like the single quotations ''.  It complained that CHAR value (the '0') was not compatible with DOUBLE field (quantity).

Here's a patch that does not put the single quotes around defaultValue.  Now, the query gets rendered as COALESCE(OI.QUANTITY,0).  This works on Derby, postgresql, and mysql.

If the user wants to put a default value that's a string, for concatenation purposes, then they would have to include single quotations in the default-value parameter:

default-value="'stringValue'"

I think this is a good setup, the use of string default values is only necessary when using concatenation || operator, but that would be rare.

> Improve complex-alias to handle nulls and defaults
> --------------------------------------------------
>
>                 Key: OFBIZ-336
>                 URL: http://issues.apache.org/jira/browse/OFBIZ-336
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Improvement
>          Components: framework
>            Reporter: Leon Torres
>            Priority: Minor
>         Attachments: complex-alias-default-value.patch, complex-alias-default-value2.patch
>
>
> The idea behind complex-alias is useful for certain things, but the way it works now is not useful.  Suppose you build a simple addition rule.  If any variable in the equation is null, the entire alias resolves to null.
> For example, suppose you create a view entity to report quantities on OrderItem.  The query would be:
> select product_id, (quantity - cancel_quantity) from order_item;
> However if you run this, you'll see that where quantity or cancel_quantity are null, the result is null.  A solution in postgresql is to use the coalesce function to specify a default value:
> select product_id, (coalesce(quantity, 0) - coalesce(cancel_quantity, 0)) from order_item;
> Now the query works.
> So the fix I think is to add another attribute default-value="something".  If the default-value is present, then add the coalesce function.  Not sure whether this is standard sql or not.
> I also encountered this issue for concatenation ||  operator, so I think it affects most operations.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[Nicolas Malin (Jira)]

     [ http://issues.apache.org/jira/browse/OFBIZ-336?page=all ]

Si Chen updated OFBIZ-336:
--------------------------

    Attachment:     (was: complex-alias-default-value.patch)

> Improve complex-alias to handle nulls and defaults
> --------------------------------------------------
>
>                 Key: OFBIZ-336
>                 URL: http://issues.apache.org/jira/browse/OFBIZ-336
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Improvement
>          Components: framework
>            Reporter: Leon Torres
>            Priority: Minor
>         Attachments: complex-alias-default-value2.patch
>
>
> The idea behind complex-alias is useful for certain things, but the way it works now is not useful.  Suppose you build a simple addition rule.  If any variable in the equation is null, the entire alias resolves to null.
> For example, suppose you create a view entity to report quantities on OrderItem.  The query would be:
> select product_id, (quantity - cancel_quantity) from order_item;
> However if you run this, you'll see that where quantity or cancel_quantity are null, the result is null.  A solution in postgresql is to use the coalesce function to specify a default value:
> select product_id, (coalesce(quantity, 0) - coalesce(cancel_quantity, 0)) from order_item;
> Now the query works.
> So the fix I think is to add another attribute default-value="something".  If the default-value is present, then add the coalesce function.  Not sure whether this is standard sql or not.
> I also encountered this issue for concatenation ||  operator, so I think it affects most operations.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[Nicolas Malin (Jira)]

     [ http://issues.apache.org/jira/browse/OFBIZ-336?page=all ]

David E. Jones closed OFBIZ-336.
--------------------------------

    Fix Version/s: SVN trunk
       Resolution: Fixed
         Assignee: David E. Jones

Thanks for your work on this Leon. I applied your patch in SVN rev 451767.

Not having the single quots for non-numeric strings may cause problems, but I guess we'll see how it goes. If necessary we might have to change to something that uses a ? style parameter on the prepared statement.

> Improve complex-alias to handle nulls and defaults
> --------------------------------------------------
>
>                 Key: OFBIZ-336
>                 URL: http://issues.apache.org/jira/browse/OFBIZ-336
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Improvement
>          Components: framework
>            Reporter: Leon Torres
>         Assigned To: David E. Jones
>            Priority: Minor
>             Fix For: SVN trunk
>
>         Attachments: complex-alias-default-value2.patch
>
>
> The idea behind complex-alias is useful for certain things, but the way it works now is not useful.  Suppose you build a simple addition rule.  If any variable in the equation is null, the entire alias resolves to null.
> For example, suppose you create a view entity to report quantities on OrderItem.  The query would be:
> select product_id, (quantity - cancel_quantity) from order_item;
> However if you run this, you'll see that where quantity or cancel_quantity are null, the result is null.  A solution in postgresql is to use the coalesce function to specify a default value:
> select product_id, (coalesce(quantity, 0) - coalesce(cancel_quantity, 0)) from order_item;
> Now the query works.
> So the fix I think is to add another attribute default-value="something".  If the default-value is present, then add the coalesce function.  Not sure whether this is standard sql or not.
> I also encountered this issue for concatenation ||  operator, so I think it affects most operations.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira