Re: Bug: Not-Null Values are at top when fetching list from database, in Release 9.04

[Jacques Le Roux <ja...@les7arts.com>]

Hi Divesh,

Would be interested by testing this patch?

Thanks

Jacques

From: "Bob Morley" <rm...@emforium.com>
> Bob Morley wrote:
>>
>> Can you please create a jira issue and load your patch, so that
>> Committers can have a look and give their views on this.
>>
>
> Ok I have created a patch ->
> https://issues.apache.org/jira/browse/OFBIZ-3740.  We can continue any
> discussion on that patch; but it should be noted that this patch will not do
> anything unless an order-by is updated to include "NULLS FIRST" or "NULLS
> LAST".
> -- 
> View this message in context: 
> http://ofbiz.135035.n4.nabble.com/Bug-Not-Null-Values-are-at-top-when-fetching-list-from-database-in-Release-9-04-tp2062221p2064444.html
> Sent from the OFBiz - Dev mailing list archive at Nabble.com.
> 

Re: Bug: Not-Null Values are at top when fetching list from database, in Release 9.04

[Bob Morley <rm...@emforium.com>]

Adam Heath-2 wrote:
> 
>> If we can provide a generic solution, this will be a good add-on. Please
>> share your views.
> 
> Is it possible that some jdbc metadata parameter can specify whether
> the database supports this?
> 

The patch contains a parameter:

+        use-order-by-nulls="true">

for this purpose.  For the databases listed it will generate a database
native "NULLS FIRST/LAST" statement as part of the order by clause.  For the
other databases (the ones where this property is not set or is set to
"false") it will generate a case:

+        if ((nullsFirst != null) && (!datasourceInfo.useOrderByNulls)) {
+            sb.append("CASE WHEN ");
+            getValue().addSqlValue(sb, modelEntity, null,
includeTablenamePrefix, datasourceInfo);
+            sb.append(" IS NULL THEN ");
+            sb.append(nullsFirst ? "0" : "1");
+            sb.append(" ELSE ");
+            sb.append(nullsFirst ? "1" : "0");
+            sb.append(" END, ");
+        }
+        

It should be noted that this generation only occurs if the "nulls" is
explicitly specified on the order-by clause.  I thought about always
generating this snippet to support a "default sorting behaviour" across all
databases, but I did not want to alter existing sql statements without a
discussion on that.  So with this patch one can explicitly specify how they
want nulls sorted and it should work for all databases.
-- 
View this message in context: http://ofbiz.135035.n4.nabble.com/Bug-Not-Null-Values-are-at-top-when-fetching-list-from-database-in-Release-9-04-tp2062221p2134052.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.

Re: Bug: Not-Null Values are at top when fetching list from database, in Release 9.04

[Adam Heath <do...@brainfood.com>]

Divesh Dutta wrote:
> Hi Jacques and Bob,
> 
> Sorry to reply late on this. I tested this patch and found that this
> feature is only supported if we use the derby, postgres, and oracle
> databases. This is not supported for Mysql database.
> So what I think is this is not a generic solution. But yes it works as
> expected, for derby, postgres, and oracle databases. Also I have put my
> comments on https://issues.apache.org/jira/browse/OFBIZ-3740
> 
> If we can provide a generic solution, this will be a good add-on. Please
> share your views.

Is it possible that some jdbc metadata parameter can specify whether
the database supports this?

Re: Bug: Not-Null Values are at top when fetching list from database, in Release 9.04

[Divesh Dutta <di...@hotwaxmedia.com>]

Hi Jacques and Bob,

Sorry to reply late on this. I tested this patch and found that this 
feature is only supported if we use the derby, postgres, and oracle 
databases. This is not supported for Mysql database.
So what I think is this is not a generic solution. But yes it works as 
expected, for derby, postgres, and oracle databases. Also I have put my 
comments on https://issues.apache.org/jira/browse/OFBIZ-3740

If we can provide a generic solution, this will be a good add-on. Please 
share your views.

Thanks
--
Divesh Dutta.


Jacques Le Roux wrote:
> Hi Divesh,
>
> Would be interested by testing this patch?
>
> Thanks
>
> Jacques
>
> From: "Bob Morley" <rm...@emforium.com>
>> Bob Morley wrote:
>>>
>>> Can you please create a jira issue and load your patch, so that
>>> Committers can have a look and give their views on this.
>>>
>>
>> Ok I have created a patch ->
>> https://issues.apache.org/jira/browse/OFBIZ-3740.  We can continue any
>> discussion on that patch; but it should be noted that this patch will 
>> not do
>> anything unless an order-by is updated to include "NULLS FIRST" or 
>> "NULLS
>> LAST".
>> -- 
>> View this message in context: 
>> http://ofbiz.135035.n4.nabble.com/Bug-Not-Null-Values-are-at-top-when-fetching-list-from-database-in-Release-9-04-tp2062221p2064444.html 
>>
>> Sent from the OFBiz - Dev mailing list archive at Nabble.com.
>>
>
>