OFBiz › OFBiz - Dev

[jira] Created: (OFBIZ-3740) Support for order by nulls first/last

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

6 messages Options

Nicolas Malin (Jira)

[jira] Created: (OFBIZ-3740) Support for order by nulls first/last

Support for order by nulls first/last
-------------------------------------

Key: OFBIZ-3740
URL: https://issues.apache.org/jira/browse/OFBIZ-3740
Project: OFBiz
Issue Type: Bug
Components: framework
Affects Versions: SVN trunk
Reporter: Bob Morley
Priority: Minor
Fix For: SVN trunk
Attachments: OFBIZ-3740_SupportOrderByNulls.patch

This patch provides the fundamentals to resolve an issue where the sorting of null values were inconsistent (sometimes sorting first and other times sorting last). Specifically, this patch allows one to create an order-by clause via any of the order-by elements of "-myField NULLS LAST" where this text parses out the - (as descending) and the "nulls last".

Since not all databases support the "nulls" argument on an order by clause (it was introduced as part of the OLAP support specification) , our entity-engine.xml file allows each data source to indicate if it has this support. If the support does not exist and nulls first/last is specified, the sql that is generated uses native sql to simulate the nulls first/last intent. At this time, the derby, postgres, and oracle databases are marked to use the nulls first grammar.

Right now, if you do not specify "NULLS XXX" in the field-name for the order-by it makes no change whatsever (naturally this assumption could be changed to have a default).

It should be noted, that my intent here was ultimately to "properly" model the order by into something whose xml representation could look something like ...

<entity-order-by field-name="fieldName" ascending="true" nulls-first="true" />

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Nicolas Malin (Jira)

[jira] Updated: (OFBIZ-3740) Support for order by nulls first/last

[ https://issues.apache.org/jira/browse/OFBIZ-3740?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bob Morley updated OFBIZ-3740:
------------------------------

Attachment: OFBIZ-3740_SupportOrderByNulls.patch

> Support for order by nulls first/last
> -------------------------------------
>
> Key: OFBIZ-3740
> URL: https://issues.apache.org/jira/browse/OFBIZ-3740
> Project: OFBiz
> Issue Type: Bug
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Bob Morley
> Priority: Minor
> Fix For: SVN trunk
>
> Attachments: OFBIZ-3740_SupportOrderByNulls.patch
>
>
> This patch provides the fundamentals to resolve an issue where the sorting of null values were inconsistent (sometimes sorting first and other times sorting last). Specifically, this patch allows one to create an order-by clause via any of the order-by elements of "-myField NULLS LAST" where this text parses out the - (as descending) and the "nulls last".
> Since not all databases support the "nulls" argument on an order by clause (it was introduced as part of the OLAP support specification) , our entity-engine.xml file allows each data source to indicate if it has this support. If the support does not exist and nulls first/last is specified, the sql that is generated uses native sql to simulate the nulls first/last intent. At this time, the derby, postgres, and oracle databases are marked to use the nulls first grammar.
> Right now, if you do not specify "NULLS XXX" in the field-name for the order-by it makes no change whatsever (naturally this assumption could be changed to have a default).
> It should be noted, that my intent here was ultimately to "properly" model the order by into something whose xml representation could look something like ...
> <entity-order-by field-name="fieldName" ascending="true" nulls-first="true" />

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Nicolas Malin (Jira)

[jira] Commented: (OFBIZ-3740) Support for order by nulls first/last

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-3740?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12865033#action_12865033 ]

Divesh Dutta commented on OFBIZ-3740:
-------------------------------------

HI Bob,

This patch works as expected for derby, postgres, and oracle databases as you have already mentioned. Cant we have some generic solution which supports all the databases. Or we can have some conditional code, where we can check which datasource is being used, and then generate the sql accordingly.

Please let me know your views on this.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Nicolas Malin (Jira)

[jira] Commented: (OFBIZ-3740) Support for order by nulls first/last

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-3740?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12865116#action_12865116 ]

Bob Morley commented on OFBIZ-3740:
-----------------------------------

Hi Divesh,

I added a more detailed description in the forum thread; but in brief this is a generic solution which supports all databases which generates the sql accordingly. What this patch does _not_ do is apply a standard default order for all databases; it just adds support for one to dictate how they want the nulls sorted.

For example, when one sets the order-by they can set it to "lastName DESC NULLS FIRST" which should apply to all databases.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Nicolas Malin (Jira)

[jira] Updated: (OFBIZ-3740) Support for order by nulls first/last

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-3740?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Scott Gray updated OFBIZ-3740:
------------------------------

Issue Type: New Feature (was: Bug)

> Support for order by nulls first/last
> -------------------------------------
>
> Key: OFBIZ-3740
> URL: https://issues.apache.org/jira/browse/OFBIZ-3740
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Bob Morley
> Priority: Minor
> Fix For: SVN trunk
>
> Attachments: OFBIZ-3740_SupportOrderByNulls.patch
>
>
> This patch provides the fundamentals to resolve an issue where the sorting of null values were inconsistent (sometimes sorting first and other times sorting last). Specifically, this patch allows one to create an order-by clause via any of the order-by elements of "-myField NULLS LAST" where this text parses out the - (as descending) and the "nulls last".
> Since not all databases support the "nulls" argument on an order by clause (it was introduced as part of the OLAP support specification) , our entity-engine.xml file allows each data source to indicate if it has this support. If the support does not exist and nulls first/last is specified, the sql that is generated uses native sql to simulate the nulls first/last intent. At this time, the derby, postgres, and oracle databases are marked to use the nulls first grammar.
> Right now, if you do not specify "NULLS XXX" in the field-name for the order-by it makes no change whatsever (naturally this assumption could be changed to have a default).
> It should be noted, that my intent here was ultimately to "properly" model the order by into something whose xml representation could look something like ...
> <entity-order-by field-name="fieldName" ascending="true" nulls-first="true" />

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Nicolas Malin (Jira)

[jira] Closed: (OFBIZ-3740) Support for order by nulls first/last

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-3740?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jacques Le Roux closed OFBIZ-3740.
----------------------------------

Assignee: Jacques Le Roux
Resolution: Fixed

Thanks Bob and Divesh,

Bob, your patch is in trunk at r950901

BTW the related thread ends at http://markmail.org/message/47z7fwtawehuem6b

> Support for order by nulls first/last
> -------------------------------------
>
> Key: OFBIZ-3740
> URL: https://issues.apache.org/jira/browse/OFBIZ-3740
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Bob Morley
> Assignee: Jacques Le Roux
> Priority: Minor
> Fix For: SVN trunk
>
> Attachments: OFBIZ-3740_SupportOrderByNulls.patch
>
>
> This patch provides the fundamentals to resolve an issue where the sorting of null values were inconsistent (sometimes sorting first and other times sorting last). Specifically, this patch allows one to create an order-by clause via any of the order-by elements of "-myField NULLS LAST" where this text parses out the - (as descending) and the "nulls last".
> Since not all databases support the "nulls" argument on an order by clause (it was introduced as part of the OLAP support specification) , our entity-engine.xml file allows each data source to indicate if it has this support. If the support does not exist and nulls first/last is specified, the sql that is generated uses native sql to simulate the nulls first/last intent. At this time, the derby, postgres, and oracle databases are marked to use the nulls first grammar.
> Right now, if you do not specify "NULLS XXX" in the field-name for the order-by it makes no change whatsever (naturally this assumption could be changed to have a default).
> It should be noted, that my intent here was ultimately to "properly" model the order by into something whose xml representation could look something like ...
> <entity-order-by field-name="fieldName" ascending="true" nulls-first="true" />

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.