Quantcast

hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

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

hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

Marc de Hoop
Hello list,

We've made some other updates to hsqldb. We'll be posting them to the
patch tracker in a minute.
We've developed Right Join support, extracting a day of week of a
datetime field ( extract(dayofweek, <fieldname>) which returns the
values: sunday = 1, monday = 2, etc.) and a better version of our
bug-fix to aggregates / build-in types. In the files we'll post, our
previous feature (set [not] null ) is also included since we don't have
got any comment yet.
We'd appreciate it if someone would review our code.

Yours,

Marc de Hoop
Huug Ouwersloot
- TOPdesk


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
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: hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

Fred Toussi-2
Thanks for the patches.

I will review them later today and comment here.

Fred

----- Original Message -----
From: "Marc de Hoop" <[hidden email]>
To: <[hidden email]>
Sent: 15 August 2005 15:14
Subject: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Hello list,

We've made some other updates to hsqldb. We'll be posting them to the
patch tracker in a minute.
We've developed Right Join support, extracting a day of week of a
datetime field ( extract(dayofweek, <fieldname>) which returns the
values: sunday = 1, monday = 2, etc.) and a better version of our
bug-fix to aggregates / build-in types. In the files we'll post, our
previous feature (set [not] null ) is also included since we don't have
got any comment yet.
We'd appreciate it if someone would review our code.

Yours,

Marc de Hoop
Huug Ouwersloot
- TOPdesk


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers 



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
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: hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

Fred Toussi-2
In reply to this post by Marc de Hoop
Thanks for the effort. I have done the first review of the patches:

DOW: This looks fine.
SET NOT NULL: This has yet to check for values in existing rows. We have a
similar test for another alter command and can reuse it.
RIGHT OUTER: This looks fine. I need to check and test it further.
Aggregates and CASE WHEN: The change to Column.java is out of context, as
the Compare method is for comparison of column values of the same time.
Needs more work.

I will review the changes further and add some tests in the next couple of
days.

Fred



----- Original Message -----
From: "Marc de Hoop" <[hidden email]>
To: <[hidden email]>
Sent: 15 August 2005 15:14
Subject: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Hello list,

We've made some other updates to hsqldb. We'll be posting them to the
patch tracker in a minute.
We've developed Right Join support, extracting a day of week of a
datetime field ( extract(dayofweek, <fieldname>) which returns the
values: sunday = 1, monday = 2, etc.) and a better version of our
bug-fix to aggregates / build-in types. In the files we'll post, our
previous feature (set [not] null ) is also included since we don't have
got any comment yet.
We'd appreciate it if someone would review our code.

Yours,

Marc de Hoop
Huug Ouwersloot
- TOPdesk


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers 



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
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: hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

Fred Toussi-2
Thanks Marc and Huug,

I have incorporated your changes for DOW and RIGHT OUTER as submitted in the
patch.

For SET NOT NULL I wrote a new implementation that checks the existing rows
and primary key.

For Aggregates and CASE WHEN, I traced the problem to the original code and
fixed it (in Expression.updateAggregatingValue()).

Have just committed the changes to the CVS hsqldb-dev module

Fred

----- Original Message -----
From: "fredt" <[hidden email]>
To: <[hidden email]>
Sent: 16 August 2005 16:04
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Thanks for the effort. I have done the first review of the patches:

DOW: This looks fine.
SET NOT NULL: This has yet to check for values in existing rows. We have a
similar test for another alter command and can reuse it.
RIGHT OUTER: This looks fine. I need to check and test it further.
Aggregates and CASE WHEN: The change to Column.java is out of context, as
the Compare method is for comparison of column values of the same time.
Needs more work.

I will review the changes further and add some tests in the next couple of
days.

Fred



----- Original Message -----
From: "Marc de Hoop" <[hidden email]>
To: <[hidden email]>
Sent: 15 August 2005 15:14
Subject: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Hello list,

We've made some other updates to hsqldb. We'll be posting them to the
patch tracker in a minute.
We've developed Right Join support, extracting a day of week of a
datetime field ( extract(dayofweek, <fieldname>) which returns the
values: sunday = 1, monday = 2, etc.) and a better version of our
bug-fix to aggregates / build-in types. In the files we'll post, our
previous feature (set [not] null ) is also included since we don't have
got any comment yet.
We'd appreciate it if someone would review our code.

Yours,

Marc de Hoop
Huug Ouwersloot
- TOPdesk


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers 



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
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: hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

Marc de Hoop

Thank you Fred for looking to our code and implementing it in the newest
version of hsqldb. Nevertheless, we've encountered one little problem.
When using a left join, hsqldb gives an exception when NOT is used in
the join condition. In the last version of the files we sent, we fixed
this by changing Expression.java at line 3806 by adding || exprType ==
Expression.NOT. If you don't have a particular reason to not use this
code, could you please add this part to Expression.java (see also
attached file).
We think everything else works fine.
Thanks again,

Huug Ouwersloot
Marc de Hoop
- TOPdesk



>Thanks Marc and Huug,
>
>I have incorporated your changes for DOW and RIGHT OUTER as submitted in the
>patch.
>
>For SET NOT NULL I wrote a new implementation that checks the existing rows
>and primary key.
>
>For Aggregates and CASE WHEN, I traced the problem to the original code and
>fixed it (in Expression.updateAggregatingValue()).
>
>Have just committed the changes to the CVS hsqldb-dev module
>
>Fred
>
>----- Original Message -----
>From: "fredt" <[hidden email]>
>To: <[hidden email]>
>Sent: 16 August 2005 16:04
>Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join
>support, extract(dayofweek) and another bug-fix to the aggregates/build-in
>types problem
>
>
>Thanks for the effort. I have done the first review of the patches:
>
>DOW: This looks fine.
>SET NOT NULL: This has yet to check for values in existing rows. We have a
>similar test for another alter command and can reuse it.
>RIGHT OUTER: This looks fine. I need to check and test it further.
>Aggregates and CASE WHEN: The change to Column.java is out of context, as
>the Compare method is for comparison of column values of the same time.
>Needs more work.
>
>I will review the changes further and add some tests in the next couple of
>days.
>
>Fred
>
>
>
>----- Original Message -----
>From: "Marc de Hoop" <[hidden email]>
>To: <[hidden email]>
>Sent: 15 August 2005 15:14
>Subject: [Hsqldb-developers] hsqldb - added features like Right Join
>support, extract(dayofweek) and another bug-fix to the aggregates/build-in
>types problem
>
>
>Hello list,
>
>We've made some other updates to hsqldb. We'll be posting them to the
>patch tracker in a minute.
>We've developed Right Join support, extracting a day of week of a
>datetime field ( extract(dayofweek, <fieldname>) which returns the
>values: sunday = 1, monday = 2, etc.) and a better version of our
>bug-fix to aggregates / build-in types. In the files we'll post, our
>previous feature (set [not] null ) is also included since we don't have
>got any comment yet.
>We'd appreciate it if someone would review our code.
>
>Yours,
>
>Marc de Hoop
>Huug Ouwersloot
>- TOPdesk
>
>
>-------------------------------------------------------
>SF.Net email is Sponsored by the Better Software Conference & EXPO
>September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
>Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
>Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
>_______________________________________________
>hsqldb-developers mailing list
>[hidden email]
>https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
>
>
>-------------------------------------------------------
>SF.Net email is Sponsored by the Better Software Conference & EXPO
>September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
>Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
>Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
>_______________________________________________
>hsqldb-developers mailing list
>[hidden email]
>https://lists.sourceforge.net/lists/listinfo/hsqldb-developers 
>
>
>
>-------------------------------------------------------
>SF.Net email is Sponsored by the Better Software Conference & EXPO
>September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
>Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
>Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
>_______________________________________________
>hsqldb-developers mailing list
>[hidden email]
>https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
>  
>



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
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: hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

Fred Toussi-2
We just need more tests for this. Please add some queries to the relevant
TestSelfLeftJoin.txt file that work with this change. Also, it was reported
and confirmed that the change to Select.java to aggregate handling has the
side effect of not allowing things like SELECT ... COALESCE(col, VAL), ...
GROUP BY  COALESCE(col, VAL), so this needs more work too.

Fred

----- Original Message -----
From: "Marc de Hoop" <[hidden email]>
To: <[hidden email]>
Sent: 24 August 2005 10:47
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem



Thank you Fred for looking to our code and implementing it in the newest
version of hsqldb. Nevertheless, we've encountered one little problem.
When using a left join, hsqldb gives an exception when NOT is used in
the join condition. In the last version of the files we sent, we fixed
this by changing Expression.java at line 3806 by adding || exprType ==
Expression.NOT. If you don't have a particular reason to not use this
code, could you please add this part to Expression.java (see also
attached file).
We think everything else works fine.
Thanks again,

Huug Ouwersloot
Marc de Hoop
- TOPdesk



>Thanks Marc and Huug,
>
>I have incorporated your changes for DOW and RIGHT OUTER as submitted in
>the
>patch.
>
>For SET NOT NULL I wrote a new implementation that checks the existing rows
>and primary key.
>
>For Aggregates and CASE WHEN, I traced the problem to the original code and
>fixed it (in Expression.updateAggregatingValue()).
>
>Have just committed the changes to the CVS hsqldb-dev module
>
>Fred
>
>----- Original Message -----
>From: "fredt" <[hidden email]>
>To: <[hidden email]>
>Sent: 16 August 2005 16:04
>Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join
>support, extract(dayofweek) and another bug-fix to the aggregates/build-in
>types problem
>
>
>Thanks for the effort. I have done the first review of the patches:
>
>DOW: This looks fine.
>SET NOT NULL: This has yet to check for values in existing rows. We have a
>similar test for another alter command and can reuse it.
>RIGHT OUTER: This looks fine. I need to check and test it further.
>Aggregates and CASE WHEN: The change to Column.java is out of context, as
>the Compare method is for comparison of column values of the same time.
>Needs more work.
>
>I will review the changes further and add some tests in the next couple of
>days.
>
>Fred
>
>
>
>----- Original Message -----
>From: "Marc de Hoop" <[hidden email]>
>To: <[hidden email]>
>Sent: 15 August 2005 15:14
>Subject: [Hsqldb-developers] hsqldb - added features like Right Join
>support, extract(dayofweek) and another bug-fix to the aggregates/build-in
>types problem
>
>
>Hello list,
>
>We've made some other updates to hsqldb. We'll be posting them to the
>patch tracker in a minute.
>We've developed Right Join support, extracting a day of week of a
>datetime field ( extract(dayofweek, <fieldname>) which returns the
>values: sunday = 1, monday = 2, etc.) and a better version of our
>bug-fix to aggregates / build-in types. In the files we'll post, our
>previous feature (set [not] null ) is also included since we don't have
>got any comment yet.
>We'd appreciate it if someone would review our code.
>
>Yours,
>
>Marc de Hoop
>Huug Ouwersloot
>- TOPdesk
>
>
>-------------------------------------------------------
>SF.Net email is Sponsored by the Better Software Conference & EXPO
>September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
>Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
>Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
>_______________________________________________
>hsqldb-developers mailing list
>[hidden email]
>https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
>
>
>-------------------------------------------------------
>SF.Net email is Sponsored by the Better Software Conference & EXPO
>September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
>Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
>Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
>_______________________________________________
>hsqldb-developers mailing list
>[hidden email]
>https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
>
>
>-------------------------------------------------------
>SF.Net email is Sponsored by the Better Software Conference & EXPO
>September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
>Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
>Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
>_______________________________________________
>hsqldb-developers mailing list
>[hidden email]
>https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
>
>



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers 



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
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: hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

Marc de Hoop
We've made a test that works with the adjustment in Expression.java. It's:
[
drop table credits if exists;
drop table accounts if exists;
drop table owners if exists;
create table owners(
 id int not null primary key,
 name varchar(100)
 );
create table accounts(
 id int not null primary key,
 accountname varchar(100),
 accountnr int,
 accounttype int,
 ownerid int,
 constraint fk_accounts foreign key (ownerid) references owners(id)
 );
create table credits(
 id int,
 accountid int,
 amount float,
 constraint fk_credits foreign key (accountid) references accounts(id) on update cascade
 );
insert into owners values(1,'John');
insert into owners values(2,'Mary');
insert into owners values(3,'Jane');
insert into accounts values(1,'Bank',5101,0,1);
insert into accounts values(2,'Cash',5202,0,1);
insert into accounts values(3,'Giro',5303,0,2);
insert into accounts values(4,'Invoice',7505,1,1);
insert into credits values(1,1,1000);
insert into credits values(2,1,2000);
insert into credits values(3,2,100);
insert into credits values(4,2,200);
]
select ow.name,ac.accountname from owners ow left join accounts ac on ((ow.id=ac.ownerid)) and (not(ow.name='Mary'));
The part between [] is code that's already inculded in TestSelfLeftJoin.txt. The last line is our test. We're not quite sure how you want us to insert it in the file, so we send it by mail.
The result should be:

NAME | ACCOUNTNAME
-----+------------
John | Bank
John | Cash
John | Invoice
Jane | [Null]


We encountered something strange, though. When we use the statement select ow.name,ac.accountname from owners ow left join accounts ac on ((ow.id=ac.ownerid)) and (not(ac.accountname='Giro')), the row with the value null in the column accountname isn't displayed. It seems not(ac.accountname='Giro') returns false when ac.accountname = null. Is this correct or did we find another bug?

Huug and Marc



We just need more tests for this. Please add some queries to the relevant 
TestSelfLeftJoin.txt file that work with this change. Also, it was reported 
and confirmed that the change to Select.java to aggregate handling has the 
side effect of not allowing things like SELECT ... COALESCE(col, VAL), ... 
GROUP BY  COALESCE(col, VAL), so this needs more work too.

Fred

----- Original Message ----- 
From: "Marc de Hoop" [hidden email]
To: [hidden email]
Sent: 24 August 2005 10:47
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join 
support, extract(dayofweek) and another bug-fix to the aggregates/build-in 
types problem



Thank you Fred for looking to our code and implementing it in the newest
version of hsqldb. Nevertheless, we've encountered one little problem.
When using a left join, hsqldb gives an exception when NOT is used in
the join condition. In the last version of the files we sent, we fixed
this by changing Expression.java at line 3806 by adding || exprType ==
Expression.NOT. If you don't have a particular reason to not use this
code, could you please add this part to Expression.java (see also
attached file).
We think everything else works fine.
Thanks again,

Huug Ouwersloot
Marc de Hoop
- TOPdesk



  
Thanks Marc and Huug,

I have incorporated your changes for DOW and RIGHT OUTER as submitted in 
the
patch.

For SET NOT NULL I wrote a new implementation that checks the existing rows
and primary key.

For Aggregates and CASE WHEN, I traced the problem to the original code and
fixed it (in Expression.updateAggregatingValue()).

Have just committed the changes to the CVS hsqldb-dev module

Fred

----- Original Message ----- 
From: "fredt" [hidden email]
To: [hidden email]
Sent: 16 August 2005 16:04
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Thanks for the effort. I have done the first review of the patches:

DOW: This looks fine.
SET NOT NULL: This has yet to check for values in existing rows. We have a
similar test for another alter command and can reuse it.
RIGHT OUTER: This looks fine. I need to check and test it further.
Aggregates and CASE WHEN: The change to Column.java is out of context, as
the Compare method is for comparison of column values of the same time.
Needs more work.

I will review the changes further and add some tests in the next couple of
days.

Fred



----- Original Message ----- 
From: "Marc de Hoop" [hidden email]
To: [hidden email]
Sent: 15 August 2005 15:14
Subject: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Hello list,

We've made some other updates to hsqldb. We'll be posting them to the
patch tracker in a minute.
We've developed Right Join support, extracting a day of week of a
datetime field ( extract(dayofweek, <fieldname>) which returns the
values: sunday = 1, monday = 2, etc.) and a better version of our
bug-fix to aggregates / build-in types. In the files we'll post, our
previous feature (set [not] null ) is also included since we don't have
got any comment yet.
We'd appreciate it if someone would review our code.

Yours,

Marc de Hoop
Huug Ouwersloot
- TOPdesk


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



    



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers 



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

  

------------------------------------------------------- SF.Net email is Sponsored by the Better Software Conference & EXPO September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf _______________________________________________ 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: hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

Fred Toussi-2
You should insert the statements in the file, adding the control comments that check for the results in the given format. You also need to add tests for expressions that do not work as they should. If you don't know how it works, please read the appendix in the Guide on the test suite.
 
Fred
----- Original Message -----
Sent: 24 August 2005 14:30
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

We've made a test that works with the adjustment in Expression.java. It's:
[
drop table credits if exists;
drop table accounts if exists;
drop table owners if exists;
create table owners(
 id int not null primary key,
 name varchar(100)
 );
create table accounts(
 id int not null primary key,
 accountname varchar(100),
 accountnr int,
 accounttype int,
 ownerid int,
 constraint fk_accounts foreign key (ownerid) references owners(id)
 );
create table credits(
 id int,
 accountid int,
 amount float,
 constraint fk_credits foreign key (accountid) references accounts(id) on update cascade
 );
insert into owners values(1,'John');
insert into owners values(2,'Mary');
insert into owners values(3,'Jane');
insert into accounts values(1,'Bank',5101,0,1);
insert into accounts values(2,'Cash',5202,0,1);
insert into accounts values(3,'Giro',5303,0,2);
insert into accounts values(4,'Invoice',7505,1,1);
insert into credits values(1,1,1000);
insert into credits values(2,1,2000);
insert into credits values(3,2,100);
insert into credits values(4,2,200);
]
select ow.name,ac.accountname from owners ow left join accounts ac on ((ow.id=ac.ownerid)) and (not(ow.name='Mary'));
The part between [] is code that's already inculded in TestSelfLeftJoin.txt. The last line is our test. We're not quite sure how you want us to insert it in the file, so we send it by mail.
The result should be:

NAME | ACCOUNTNAME
-----+------------
John | Bank
John | Cash
John | Invoice
Jane | [Null]


We encountered something strange, though. When we use the statement select ow.name,ac.accountname from owners ow left join accounts ac on ((ow.id=ac.ownerid)) and (not(ac.accountname='Giro')), the row with the value null in the column accountname isn't displayed. It seems not(ac.accountname='Giro') returns false when ac.accountname = null. Is this correct or did we find another bug?

Huug and Marc



We just need more tests for this. Please add some queries to the relevant 
TestSelfLeftJoin.txt file that work with this change. Also, it was reported 
and confirmed that the change to Select.java to aggregate handling has the 
side effect of not allowing things like SELECT ... COALESCE(col, VAL), ... 
GROUP BY  COALESCE(col, VAL), so this needs more work too.

Fred

----- Original Message ----- 
From: "Marc de Hoop" [hidden email]
To: [hidden email]
Sent: 24 August 2005 10:47
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join 
support, extract(dayofweek) and another bug-fix to the aggregates/build-in 
types problem



Thank you Fred for looking to our code and implementing it in the newest
version of hsqldb. Nevertheless, we've encountered one little problem.
When using a left join, hsqldb gives an exception when NOT is used in
the join condition. In the last version of the files we sent, we fixed
this by changing Expression.java at line 3806 by adding || exprType ==
Expression.NOT. If you don't have a particular reason to not use this
code, could you please add this part to Expression.java (see also
attached file).
We think everything else works fine.
Thanks again,

Huug Ouwersloot
Marc de Hoop
- TOPdesk



  
Thanks Marc and Huug,

I have incorporated your changes for DOW and RIGHT OUTER as submitted in 
the
patch.

For SET NOT NULL I wrote a new implementation that checks the existing rows
and primary key.

For Aggregates and CASE WHEN, I traced the problem to the original code and
fixed it (in Expression.updateAggregatingValue()).

Have just committed the changes to the CVS hsqldb-dev module

Fred

----- Original Message ----- 
From: "fredt" [hidden email]
To: [hidden email]
Sent: 16 August 2005 16:04
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Thanks for the effort. I have done the first review of the patches:

DOW: This looks fine.
SET NOT NULL: This has yet to check for values in existing rows. We have a
similar test for another alter command and can reuse it.
RIGHT OUTER: This looks fine. I need to check and test it further.
Aggregates and CASE WHEN: The change to Column.java is out of context, as
the Compare method is for comparison of column values of the same time.
Needs more work.

I will review the changes further and add some tests in the next couple of
days.

Fred



----- Original Message ----- 
From: "Marc de Hoop" [hidden email]
To: [hidden email]
Sent: 15 August 2005 15:14
Subject: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Hello list,

We've made some other updates to hsqldb. We'll be posting them to the
patch tracker in a minute.
We've developed Right Join support, extracting a day of week of a
datetime field ( extract(dayofweek, <fieldname>) which returns the
values: sunday = 1, monday = 2, etc.) and a better version of our
bug-fix to aggregates / build-in types. In the files we'll post, our
previous feature (set [not] null ) is also included since we don't have
got any comment yet.
We'd appreciate it if someone would review our code.

Yours,

Marc de Hoop
Huug Ouwersloot
- TOPdesk


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



    



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers 



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

  

------------------------------------------------------- SF.Net email is Sponsored by the Better Software Conference & EXPO September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf _______________________________________________ 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

count column

Stephen Ince
I am trying to migrate from hsqldb1.7 to 1.8 and hsqldb complains about a column with the name "count".
 
I have tried the following:
  'count'          int default 0,
  "count"         int default 0,
 
Is there any work around for this.

----- Original Message -----
Sent: Wednesday, August 24, 2005 11:55 AM
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

You should insert the statements in the file, adding the control comments that check for the results in the given format. You also need to add tests for expressions that do not work as they should. If you don't know how it works, please read the appendix in the Guide on the test suite.
 
Fred
----- Original Message -----
Sent: 24 August 2005 14:30
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join support, extract(dayofweek) and another bug-fix to the aggregates/build-in types problem

We've made a test that works with the adjustment in Expression.java. It's:
[
drop table credits if exists;
drop table accounts if exists;
drop table owners if exists;
create table owners(
 id int not null primary key,
 name varchar(100)
 );
create table accounts(
 id int not null primary key,
 accountname varchar(100),
 accountnr int,
 accounttype int,
 ownerid int,
 constraint fk_accounts foreign key (ownerid) references owners(id)
 );
create table credits(
 id int,
 accountid int,
 amount float,
 constraint fk_credits foreign key (accountid) references accounts(id) on update cascade
 );
insert into owners values(1,'John');
insert into owners values(2,'Mary');
insert into owners values(3,'Jane');
insert into accounts values(1,'Bank',5101,0,1);
insert into accounts values(2,'Cash',5202,0,1);
insert into accounts values(3,'Giro',5303,0,2);
insert into accounts values(4,'Invoice',7505,1,1);
insert into credits values(1,1,1000);
insert into credits values(2,1,2000);
insert into credits values(3,2,100);
insert into credits values(4,2,200);
]
select ow.name,ac.accountname from owners ow left join accounts ac on ((ow.id=ac.ownerid)) and (not(ow.name='Mary'));
The part between [] is code that's already inculded in TestSelfLeftJoin.txt. The last line is our test. We're not quite sure how you want us to insert it in the file, so we send it by mail.
The result should be:

NAME | ACCOUNTNAME
-----+------------
John | Bank
John | Cash
John | Invoice
Jane | [Null]


We encountered something strange, though. When we use the statement select ow.name,ac.accountname from owners ow left join accounts ac on ((ow.id=ac.ownerid)) and (not(ac.accountname='Giro')), the row with the value null in the column accountname isn't displayed. It seems not(ac.accountname='Giro') returns false when ac.accountname = null. Is this correct or did we find another bug?

Huug and Marc



We just need more tests for this. Please add some queries to the relevant 
TestSelfLeftJoin.txt file that work with this change. Also, it was reported 
and confirmed that the change to Select.java to aggregate handling has the 
side effect of not allowing things like SELECT ... COALESCE(col, VAL), ... 
GROUP BY  COALESCE(col, VAL), so this needs more work too.

Fred

----- Original Message ----- 
From: "Marc de Hoop" [hidden email]
To: [hidden email]
Sent: 24 August 2005 10:47
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join 
support, extract(dayofweek) and another bug-fix to the aggregates/build-in 
types problem



Thank you Fred for looking to our code and implementing it in the newest
version of hsqldb. Nevertheless, we've encountered one little problem.
When using a left join, hsqldb gives an exception when NOT is used in
the join condition. In the last version of the files we sent, we fixed
this by changing Expression.java at line 3806 by adding || exprType ==
Expression.NOT. If you don't have a particular reason to not use this
code, could you please add this part to Expression.java (see also
attached file).
We think everything else works fine.
Thanks again,

Huug Ouwersloot
Marc de Hoop
- TOPdesk



  
Thanks Marc and Huug,

I have incorporated your changes for DOW and RIGHT OUTER as submitted in 
the
patch.

For SET NOT NULL I wrote a new implementation that checks the existing rows
and primary key.

For Aggregates and CASE WHEN, I traced the problem to the original code and
fixed it (in Expression.updateAggregatingValue()).

Have just committed the changes to the CVS hsqldb-dev module

Fred

----- Original Message ----- 
From: "fredt" [hidden email]
To: [hidden email]
Sent: 16 August 2005 16:04
Subject: Re: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Thanks for the effort. I have done the first review of the patches:

DOW: This looks fine.
SET NOT NULL: This has yet to check for values in existing rows. We have a
similar test for another alter command and can reuse it.
RIGHT OUTER: This looks fine. I need to check and test it further.
Aggregates and CASE WHEN: The change to Column.java is out of context, as
the Compare method is for comparison of column values of the same time.
Needs more work.

I will review the changes further and add some tests in the next couple of
days.

Fred



----- Original Message ----- 
From: "Marc de Hoop" [hidden email]
To: [hidden email]
Sent: 15 August 2005 15:14
Subject: [Hsqldb-developers] hsqldb - added features like Right Join
support, extract(dayofweek) and another bug-fix to the aggregates/build-in
types problem


Hello list,

We've made some other updates to hsqldb. We'll be posting them to the
patch tracker in a minute.
We've developed Right Join support, extracting a day of week of a
datetime field ( extract(dayofweek, <fieldname>) which returns the
values: sunday = 1, monday = 2, etc.) and a better version of our
bug-fix to aggregates / build-in types. In the files we'll post, our
previous feature (set [not] null ) is also included since we don't have
got any comment yet.
We'd appreciate it if someone would review our code.

Yours,

Marc de Hoop
Huug Ouwersloot
- TOPdesk


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers



    



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers 



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

  

------------------------------------------------------- SF.Net email is Sponsored by the Better Software Conference & EXPO September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf _______________________________________________ hsqldb-developers mailing list [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
Loading...