Re: Order by which supports database defaults for nulls last

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Order by which supports database defaults for nulls last

Jean-Noël Rouvignac-2
Oops, I forgot to use the new Database.sqlSyntaxDb2 field in SortAndSlice.java
This is now included in this new patch.

Thanks,
Jean-Noel


-----Original Message-----
From: Jean-Noël Rouvignac
Sent: 02 December 2011 08:40
To: '[hidden email]'
Subject: Order by which supports database defaults for nulls last

Hello,

Here is a patch changing the default sorting for nulls when using the order by clause. The patch was generated from hsqldb-2.2.6.zip available on Sourceforge.

According to https://hibernate.onjira.com/browse/HHH-465 :

        In different DBs, the sorting of nulls relative to other values is handled differently.
       
        PostgreSQL - Nulls are considered HIGHER than non-nulls.
        DB2 - Higher
        MSSQL - Lower
        MySQL - Lower
        Oracle - Higher

This patch is making sure that when using the Oracle or PostgreSQL, we are using the same ordering for nulls.
I also added a Boolean for sqlSyntaxDb2 since I had the information available. I leave it to someone interested to expand on it.

Please let me know if this patch is ok with you.

Thanks,
Jean-Noël Rouvignac


______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

orderBySupportsDatabaseDefaultsForNullsLast v2.patch (17K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Order by which supports database defaults for nulls last

Fred Toussi-2
Hi Jean-Noel,

Thanks for sharing the patch. The default NULLS LAST is a useful
enhancements.

The correct place to implement this feature is in the ParserDQL, which I
have done and committed to SVN /base/trunk. The SQL setting for DB2
compatibility mode has also been added, to be used for future syntax
compatibility features.

Default null ordering is controlled with a new setting, SET DATABASE SQL
NULLS FIRST { TRUE | FALSE } and the connection property
sql.nulls_first.

You can modify ParserDQL further to use NULLS LAST by default for the
three dialects.

Fred Toussi

On Friday, December 02, 2011 8:49 AM, "Jean-Noël Rouvignac"
<[hidden email]> wrote:

> Oops, I forgot to use the new Database.sqlSyntaxDb2 field in
> SortAndSlice.java This is now included in this new patch.
>
> Thanks, Jean-Noel
>
>
> -----Original Message----- From: Jean-Noël Rouvignac Sent: 02 December
> 2011 08:40 To: '[hidden email]' Subject:
> Order by which supports database defaults for nulls last
>
> Hello,
>
> Here is a patch changing the default sorting for nulls when using the
> order by clause. The patch was generated from hsqldb-2.2.6.zip
> available on Sourceforge.
>
> According to https://hibernate.onjira.com/browse/HHH-465 :
>
>       In different DBs, the sorting of nulls relative to other values
>       is handled differently.
>
>       PostgreSQL - Nulls are considered HIGHER than non-nulls. DB2 -
>       Higher MSSQL - Lower MySQL - Lower Oracle - Higher
>
> This patch is making sure that when using the Oracle or PostgreSQL, we
> are using the same ordering for nulls. I also added a Boolean for
> sqlSyntaxDb2 since I had the information available. I leave it to
> someone interested to expand on it.
>
> Please let me know if this patch is ok with you.
>
> Thanks, Jean-Noël Rouvignac
>
>
> ______________________________________________________________________
> This email has been scanned by the Symantec Email Security.cloud
> service. For more information please visit
> http://www.symanteccloud.com
> ______________________________________________________________________
> ----------------------------------------------------------------------
> --------
> All the data continuously generated in your IT infrastructure contains
> a definitive record of customers, application performance, security
> threats, fraudulent activity, and more. Splunk takes this data and
> makes sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> hsqldb-developers mailing list [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
> Email had 1 attachment:
> + orderBySupportsDatabaseDefaultsForNullsLast v2.patch 17k (application/octet-
>   stream)

------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Order by which supports database defaults for nulls last

Jean-Noël Rouvignac-2
Hi Fred,

I was not very comfortable with adding that code were I added it (such deep if statements are never good).
Thanks for making it better.

> You can modify ParserDQL further to use NULLS LAST by default for the three dialects.
Do you mean that if I submit a patch that does this you will integrate it?

Thanks,
Jean-Noel


-----Original Message-----
From: Fred Toussi [mailto:[hidden email]]
Sent: 05 December 2011 11:50
To: Developer discussion for HyperSQL Database Engine.
Subject: Re: [Hsqldb-developers] Order by which supports database defaults for nulls last

Hi Jean-Noel,

Thanks for sharing the patch. The default NULLS LAST is a useful enhancements.

The correct place to implement this feature is in the ParserDQL, which I have done and committed to SVN /base/trunk. The SQL setting for DB2 compatibility mode has also been added, to be used for future syntax compatibility features.

Default null ordering is controlled with a new setting, SET DATABASE SQL NULLS FIRST { TRUE | FALSE } and the connection property sql.nulls_first.

You can modify ParserDQL further to use NULLS LAST by default for the three dialects.

Fred Toussi

On Friday, December 02, 2011 8:49 AM, "Jean-Noël Rouvignac"
<[hidden email]> wrote:

> Oops, I forgot to use the new Database.sqlSyntaxDb2 field in
> SortAndSlice.java This is now included in this new patch.
>
> Thanks, Jean-Noel
>
>
> -----Original Message----- From: Jean-Noël Rouvignac Sent: 02 December
> 2011 08:40 To: '[hidden email]' Subject:
> Order by which supports database defaults for nulls last
>
> Hello,
>
> Here is a patch changing the default sorting for nulls when using the
> order by clause. The patch was generated from hsqldb-2.2.6.zip
> available on Sourceforge.
>
> According to https://hibernate.onjira.com/browse/HHH-465 :
>
>       In different DBs, the sorting of nulls relative to other values
>       is handled differently.
>
>       PostgreSQL - Nulls are considered HIGHER than non-nulls. DB2 -
>       Higher MSSQL - Lower MySQL - Lower Oracle - Higher
>
> This patch is making sure that when using the Oracle or PostgreSQL, we
> are using the same ordering for nulls. I also added a Boolean for
> sqlSyntaxDb2 since I had the information available. I leave it to
> someone interested to expand on it.
>
> Please let me know if this patch is ok with you.
>
> Thanks, Jean-Noël Rouvignac
>
>
> ______________________________________________________________________
> This email has been scanned by the Symantec Email Security.cloud
> service. For more information please visit
> http://www.symanteccloud.com 
> ______________________________________________________________________
> ----------------------------------------------------------------------
> --------
> All the data continuously generated in your IT infrastructure contains
> a definitive record of customers, application performance, security
> threats, fraudulent activity, and more. Splunk takes this data and
> makes sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> hsqldb-developers mailing list [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
> Email had 1 attachment:
> + orderBySupportsDatabaseDefaultsForNullsLast v2.patch 17k
> + (application/octet-
>   stream)

------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Order by which supports database defaults for nulls last

Fred Toussi-2
At the moment I don't want to enforce default NULLS LAST when one of the
three dialects is used. This is very easy to implement now if you need
it for your own use.

There are some other semantic compatibility settings that are
independent of the dialects, such as CONCAT NULLS for concatenation of
null and not-null values. At some point in the future I might add a
setting to automatically change the semantics when a syntax
compatibility mode is selected.

You are welcome to suggest any other compatibilty setting and I will
implement it if it's a commonly used feature.

Fred

On Monday, December 05, 2011 12:06 PM, "Jean-Noël Rouvignac"
<[hidden email]> wrote:

> Hi Fred,
>
> I was not very comfortable with adding that code were I added it (such
> deep if statements are never good). Thanks for making it better.
>
> > You can modify ParserDQL further to use NULLS LAST by default for
> > the three dialects.
> Do you mean that if I submit a patch that does this you will
> integrate it?
>
> Thanks, Jean-Noel
>
>
> -----Original Message----- From: Fred Toussi
> [mailto:[hidden email]] Sent: 05 December 2011 11:50 To:
> Developer discussion for HyperSQL Database Engine. Subject: Re: [Hsqldb-
> developers] Order by which supports database defaults for nulls last
>
> Hi Jean-Noel,
>
> Thanks for sharing the patch. The default NULLS LAST is a useful
> enhancements.
>
> The correct place to implement this feature is in the ParserDQL, which
> I have done and committed to SVN /base/trunk. The SQL setting for DB2
> compatibility mode has also been added, to be used for future syntax
> compatibility features.
>
> Default null ordering is controlled with a new setting, SET DATABASE
> SQL NULLS FIRST { TRUE | FALSE } and the connection property
> sql.nulls_first.
>
> You can modify ParserDQL further to use NULLS LAST by default for the
> three dialects.
>
> Fred Toussi
>
> On Friday, December 02, 2011 8:49 AM, "Jean-Noël Rouvignac"
> <[hidden email]> wrote:
> > Oops, I forgot to use the new Database.sqlSyntaxDb2 field in
> > SortAndSlice.java This is now included in this new patch.
> >
> > Thanks, Jean-Noel
> >
> >
> > -----Original Message----- From: Jean-Noël Rouvignac Sent: 02
> > December 2011 08:40 To: '[hidden email]'
> > Subject: Order by which supports database defaults for nulls last
> >
> > Hello,
> >
> > Here is a patch changing the default sorting for nulls when using
> > the order by clause. The patch was generated from hsqldb-2.2.6.zip
> > available on Sourceforge.
> >
> > According to https://hibernate.onjira.com/browse/HHH-465 :
> >
> >       In different DBs, the sorting of nulls relative to other
> >       values is handled differently.
> >
> >       PostgreSQL - Nulls are considered HIGHER than non-nulls. DB2 -
> >       Higher MSSQL - Lower MySQL - Lower Oracle - Higher
> >
> > This patch is making sure that when using the Oracle or PostgreSQL,
> > we are using the same ordering for nulls. I also added a Boolean for
> > sqlSyntaxDb2 since I had the information available. I leave it to
> > someone interested to expand on it.
> >
> > Please let me know if this patch is ok with you.
> >
> > Thanks, Jean-Noël Rouvignac
> >
> >
> > ________________________________________________________________-
> > ______
> > This email has been scanned by the Symantec Email Security.cloud
> > service. For more information please visit
> > http://www.symanteccloud.com
> > ________________________________________________________________-
> > ______
> > ----------------------------------------------------------------
> > ------
> > --------
> > All the data continuously generated in your IT infrastructure
> > contains a definitive record of customers, application performance,
> > security threats, fraudulent activity, and more. Splunk takes this
> > data and makes sense of it. IT sense. And common sense.
> > http://p.sf.net/sfu/splunk-novd2d
> > _______________________________________________
> > hsqldb-developers mailing list hsqldb-
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
> >
> > Email had 1 attachment:
> > + orderBySupportsDatabaseDefaultsForNullsLast v2.patch 17k
> > + (application/octet- stream)
>
> ----------------------------------------------------------------------
> --------
> All the data continuously generated in your IT infrastructure contains
> a definitive record of customers, application performance, security
> threats, fraudulent activity, and more. Splunk takes this data and
> makes sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> hsqldb-developers mailing list [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
> ______________________________________________________________________
> This email has been scanned by the Symantec Email Security.cloud
> service. For more information please visit
> http://www.symanteccloud.com
> ______________________________________________________________________
>
> ______________________________________________________________________
> This email has been scanned by the Symantec Email Security.cloud
> service. For more information please visit
> http://www.symanteccloud.com
> ______________________________________________________________________
>
> ----------------------------------------------------------------------
> --------
> All the data continuously generated in your IT infrastructure contains
> a definitive record of customers, application performance, security
> threats, fraudulent activity, and more. Splunk takes this data and
> makes sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> hsqldb-developers mailing list [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>

------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Order by which supports database defaults for nulls last

Jean-Noël Rouvignac-2
I understand your position.
I believe adding DB specific semantics in the future will be a plus in addition to DB specific syntax.
What you just did will do for me in the meantime.

If I come across other issues, then I will let you know.

Thanks again,
Jean-Noel


-----Original Message-----
From: Fred Toussi [mailto:[hidden email]]
Sent: 05 December 2011 12:26
To: Developer discussion for HyperSQL Database Engine.
Subject: Re: [Hsqldb-developers] Order by which supports database defaults for nulls last

At the moment I don't want to enforce default NULLS LAST when one of the three dialects is used. This is very easy to implement now if you need it for your own use.

There are some other semantic compatibility settings that are independent of the dialects, such as CONCAT NULLS for concatenation of null and not-null values. At some point in the future I might add a setting to automatically change the semantics when a syntax compatibility mode is selected.

You are welcome to suggest any other compatibilty setting and I will implement it if it's a commonly used feature.

Fred

On Monday, December 05, 2011 12:06 PM, "Jean-Noël Rouvignac"
<[hidden email]> wrote:

> Hi Fred,
>
> I was not very comfortable with adding that code were I added it (such
> deep if statements are never good). Thanks for making it better.
>
> > You can modify ParserDQL further to use NULLS LAST by default for
> > the three dialects.
> Do you mean that if I submit a patch that does this you will integrate
> it?
>
> Thanks, Jean-Noel
>
>
> -----Original Message----- From: Fred Toussi
> [mailto:[hidden email]] Sent: 05 December 2011 11:50 To:
> Developer discussion for HyperSQL Database Engine. Subject: Re:
> [Hsqldb- developers] Order by which supports database defaults for
> nulls last
>
> Hi Jean-Noel,
>
> Thanks for sharing the patch. The default NULLS LAST is a useful
> enhancements.
>
> The correct place to implement this feature is in the ParserDQL, which
> I have done and committed to SVN /base/trunk. The SQL setting for DB2
> compatibility mode has also been added, to be used for future syntax
> compatibility features.
>
> Default null ordering is controlled with a new setting, SET DATABASE
> SQL NULLS FIRST { TRUE | FALSE } and the connection property
> sql.nulls_first.
>
> You can modify ParserDQL further to use NULLS LAST by default for the
> three dialects.
>
> Fred Toussi
>
> On Friday, December 02, 2011 8:49 AM, "Jean-Noël Rouvignac"
> <[hidden email]> wrote:
> > Oops, I forgot to use the new Database.sqlSyntaxDb2 field in
> > SortAndSlice.java This is now included in this new patch.
> >
> > Thanks, Jean-Noel
> >
> >
> > -----Original Message----- From: Jean-Noël Rouvignac Sent: 02
> > December 2011 08:40 To: '[hidden email]'
> > Subject: Order by which supports database defaults for nulls last
> >
> > Hello,
> >
> > Here is a patch changing the default sorting for nulls when using
> > the order by clause. The patch was generated from hsqldb-2.2.6.zip
> > available on Sourceforge.
> >
> > According to https://hibernate.onjira.com/browse/HHH-465 :
> >
> >       In different DBs, the sorting of nulls relative to other
> >       values is handled differently.
> >
> >       PostgreSQL - Nulls are considered HIGHER than non-nulls. DB2 -
> >       Higher MSSQL - Lower MySQL - Lower Oracle - Higher
> >
> > This patch is making sure that when using the Oracle or PostgreSQL,
> > we are using the same ordering for nulls. I also added a Boolean for
> > sqlSyntaxDb2 since I had the information available. I leave it to
> > someone interested to expand on it.
> >
> > Please let me know if this patch is ok with you.
> >
> > Thanks, Jean-Noël Rouvignac
> >
> >
> > ________________________________________________________________-
> > ______
> > This email has been scanned by the Symantec Email Security.cloud
> > service. For more information please visit
> > http://www.symanteccloud.com
> > ________________________________________________________________-
> > ______
> > ----------------------------------------------------------------
> > ------
> > --------
> > All the data continuously generated in your IT infrastructure
> > contains a definitive record of customers, application performance,
> > security threats, fraudulent activity, and more. Splunk takes this
> > data and makes sense of it. IT sense. And common sense.
> > http://p.sf.net/sfu/splunk-novd2d
> > _______________________________________________
> > hsqldb-developers mailing list hsqldb-
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
> >
> > Email had 1 attachment:
> > + orderBySupportsDatabaseDefaultsForNullsLast v2.patch 17k
> > + (application/octet- stream)
>
> ----------------------------------------------------------------------
> --------
> All the data continuously generated in your IT infrastructure contains
> a definitive record of customers, application performance, security
> threats, fraudulent activity, and more. Splunk takes this data and
> makes sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> hsqldb-developers mailing list [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
> ______________________________________________________________________
> This email has been scanned by the Symantec Email Security.cloud
> service. For more information please visit
> http://www.symanteccloud.com 
> ______________________________________________________________________
>
> ______________________________________________________________________
> This email has been scanned by the Symantec Email Security.cloud
> service. For more information please visit
> http://www.symanteccloud.com 
> ______________________________________________________________________
>
> ----------------------------------------------------------------------
> --------
> All the data continuously generated in your IT infrastructure contains
> a definitive record of customers, application performance, security
> threats, fraudulent activity, and more. Splunk takes this data and
> makes sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> hsqldb-developers mailing list [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>

------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
Loading...