Error in Documentation? (Subqueries in CHECK Constraints / SQL92-Assertions)

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Error in Documentation? (Subqueries in CHECK Constraints / SQL92-Assertions)

Timmy Turner
Hi,


"A CHECK constraint consists of a <search condition> that must not be false (can be unknown) for each row of the table. The <search condition> can reference all the columns of the current row, and if it contains a <subquery>, other tables and views in the database (excluding its own table)."

Is this actually true or an error in the documentation? Trying a subquery in a check constraint gives me a FeatureNotSupportedException.

Are there any plans to support this functionality (or Assertions in general) in the near future?



------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Error in Documentation? (Subqueries in CHECK Constraints / SQL92-Assertions)

Fred Toussi-2
The documentation states the Standard and mentions any difference in implementation. In this case it misses the fact that subqueries are not yet supported in check constraints.
 
We will probably add some support in this area later this year.
 
Fred
 
On Sat, Feb 23, 2013, at 20:31, Timmy Turner wrote:
Hi,
 
 
"A CHECK constraint consists of a <search condition> that must not be false (can be unknown) for each row of the table. The <search condition> can reference all the columns of the current row, and if it contains a <subquery>, other tables and views in the database (excluding its own table)."
 
Is this actually true or an error in the documentation? Trying a subquery in a check constraint gives me a FeatureNotSupportedException.
 
Are there any plans to support this functionality (or Assertions in general) in the near future?
 
 
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Error in Documentation? (Subqueries in CHECK Constraints / SQL92-Assertions)

Timmy Turner
We will probably add some support in this area later this year.

Wow, this was unexpected. Can you provide any further details on what this might look like, or what amount of functionality you will be trying to cover?

That would add HSQLDB to the list of currently only 2 SQL-based DBMSes that somewhat support this (one being MS Access/JET(1) and the other MS SQLServer(2)).



(Found those links on http://stackoverflow.com/questions/6368349/why-dont-dbmss-support-assertion )

2013/2/23 Fred Toussi <[hidden email]>
The documentation states the Standard and mentions any difference in implementation. In this case it misses the fact that subqueries are not yet supported in check constraints.
 
We will probably add some support in this area later this year.
 
Fred
 
On Sat, Feb 23, 2013, at 20:31, Timmy Turner wrote:
Hi,
 
 
"A CHECK constraint consists of a <search condition> that must not be false (can be unknown) for each row of the table. The <search condition> can reference all the columns of the current row, and if it contains a <subquery>, other tables and views in the database (excluding its own table)."
 
Is this actually true or an error in the documentation? Trying a subquery in a check constraint gives me a FeatureNotSupportedException.
 
Are there any plans to support this functionality (or Assertions in general) in the near future?
 
 
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user



------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Error in Documentation? (Subqueries in CHECK Constraints / SQL92-Assertions)

Fred Toussi-2
It will support a wide range of subquries but not necessarily all possible subqueries, as a check condition must hold when referenced tables are modified.
 
Fred
 
On Sat, Feb 23, 2013, at 20:56, Timmy Turner wrote:
We will probably add some support in this area later this year.
 
Wow, this was unexpected. Can you provide any further details on what this might look like, or what amount of functionality you will be trying to cover?
 
That would add HSQLDB to the list of currently only 2 SQL-based DBMSes that somewhat support this (one being MS Access/JET(1) and the other MS SQLServer(2)).
 
 
 
(Found those links on http://stackoverflow.com/questions/6368349/why-dont-dbmss-support-assertion )

2013/2/23 Fred Toussi <[hidden email]>
The documentation states the Standard and mentions any difference in implementation. In this case it misses the fact that subqueries are not yet supported in check constraints.
 
We will probably add some support in this area later this year.
 
Fred
 
On Sat, Feb 23, 2013, at 20:31, Timmy Turner wrote:
Hi,
 
 
"A CHECK constraint consists of a <search condition> that must not be false (can be unknown) for each row of the table. The <search condition> can reference all the columns of the current row, and if it contains a <subquery>, other tables and views in the database (excluding its own table)."
 
Is this actually true or an error in the documentation? Trying a subquery in a check constraint gives me a FeatureNotSupportedException.
 
Are there any plans to support this functionality (or Assertions in general) in the near future?
 
 
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Error in Documentation? (Subqueries in CHECK Constraints / SQL92-Assertions)

Timmy Turner
Yes it's a complicated matter.

If you are still accepting feature requests for this, the 2 things that Microsoft's solutions currently don't offer are:

1. Deferring the constraint evaluation to the end of the transaction
2. Evaluating the constraint on a statement/table-level and not for each individual row

It would be great if you could include those.

2013/2/23 Fred Toussi <[hidden email]>
It will support a wide range of subquries but not necessarily all possible subqueries, as a check condition must hold when referenced tables are modified.
 
Fred
 
On Sat, Feb 23, 2013, at 20:56, Timmy Turner wrote:
We will probably add some support in this area later this year.
 
Wow, this was unexpected. Can you provide any further details on what this might look like, or what amount of functionality you will be trying to cover?
 
That would add HSQLDB to the list of currently only 2 SQL-based DBMSes that somewhat support this (one being MS Access/JET(1) and the other MS SQLServer(2)).
 
 
 
(Found those links on http://stackoverflow.com/questions/6368349/why-dont-dbmss-support-assertion )

2013/2/23 Fred Toussi <[hidden email]>
The documentation states the Standard and mentions any difference in implementation. In this case it misses the fact that subqueries are not yet supported in check constraints.
 
We will probably add some support in this area later this year.
 
Fred
 
On Sat, Feb 23, 2013, at 20:31, Timmy Turner wrote:
Hi,
 
 
"A CHECK constraint consists of a <search condition> that must not be false (can be unknown) for each row of the table. The <search condition> can reference all the columns of the current row, and if it contains a <subquery>, other tables and views in the database (excluding its own table)."
 
Is this actually true or an error in the documentation? Trying a subquery in a check constraint gives me a FeatureNotSupportedException.
 
Are there any plans to support this functionality (or Assertions in general) in the near future?
 
 
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user



------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Error in Documentation? (Subqueries in CHECK Constraints / SQL92-Assertions)

Fred Toussi-2
Whatever we support will be correct according to the Standard. This type of constraint would be enforced at statement level or at commit.
 
Fred
 
On Sat, Feb 23, 2013, at 21:13, Timmy Turner wrote:
Yes it's a complicated matter.
 
If you are still accepting feature requests for this, the 2 things that Microsoft's solutions currently don't offer are:
 
1. Deferring the constraint evaluation to the end of the transaction
2. Evaluating the constraint on a statement/table-level and not for each individual row
 
It would be great if you could include those.

2013/2/23 Fred Toussi <[hidden email]>
It will support a wide range of subquries but not necessarily all possible subqueries, as a check condition must hold when referenced tables are modified.
 
Fred
 
On Sat, Feb 23, 2013, at 20:56, Timmy Turner wrote:
We will probably add some support in this area later this year.
 
Wow, this was unexpected. Can you provide any further details on what this might look like, or what amount of functionality you will be trying to cover?
 
That would add HSQLDB to the list of currently only 2 SQL-based DBMSes that somewhat support this (one being MS Access/JET(1) and the other MS SQLServer(2)).
 
 
 
(Found those links on http://stackoverflow.com/questions/6368349/why-dont-dbmss-support-assertion )

2013/2/23 Fred Toussi <[hidden email]>
The documentation states the Standard and mentions any difference in implementation. In this case it misses the fact that subqueries are not yet supported in check constraints.
 
We will probably add some support in this area later this year.
 
Fred
 
On Sat, Feb 23, 2013, at 20:31, Timmy Turner wrote:
Hi,
 
 
"A CHECK constraint consists of a <search condition> that must not be false (can be unknown) for each row of the table. The <search condition> can reference all the columns of the current row, and if it contains a <subquery>, other tables and views in the database (excluding its own table)."
 
Is this actually true or an error in the documentation? Trying a subquery in a check constraint gives me a FeatureNotSupportedException.
 
Are there any plans to support this functionality (or Assertions in general) in the near future?
 
 
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Error in Documentation? (Subqueries in CHECK Constraints / SQL92-Assertions)

Thomas Kellerer
In reply to this post by Timmy Turner
Timmy Turner, 23.02.2013 21:56:

> That would add HSQLDB to the list of currently only 2 SQL-based
> DBMSes that somewhat support this (one being MS Access/JET(1) and the
> other MS SQLServer(2)).
>
> (1) http://support.microsoft.com/kb/201888/en-us
>
> (2)
> http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/25/scalar-udfs-wrapped-in-check-constraints-are-very-slow-and-may-fail-for-multirow-updates.aspx
>
> Found those links on http://stackoverflow.com/questions/6368349/why-dont-dbmss-support-assertion
>

If you are willing to "hide" the check behind a stored function, then the list is a bit longer than just MS Access and SQL Server.
At least in Postgres and Oracle you can do the same thing.

Thomas


------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Error in Documentation? (Subqueries in CHECK Constraints / SQL92-Assertions)

Fred Toussi-2
A check constraint conforming to SQL Standard is not any arbitrary
constraint that you can hide in a function.

If must be "retrospectively deterministic", which means deterministic at
any future time. For example, HSQLDB supports CHECK (COL <
CURRENT_DATE), but rejects CHECK (COL > CURRENT_DATE). There are very
strict rules on what is allowed and HSQLDB supports a subset of
allowable check constraints.

Fred

On Mon, Feb 25, 2013, at 8:29, Thomas Kellerer wrote:

> Timmy Turner, 23.02.2013 21:56:
> > That would add HSQLDB to the list of currently only 2 SQL-based
> > DBMSes that somewhat support this (one being MS Access/JET(1) and the
> > other MS SQLServer(2)).
> >
> > (1) http://support.microsoft.com/kb/201888/en-us
> >
> > (2)
> > http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/25/scalar-udfs-wrapped-in-check-constraints-are-very-slow-and-may-fail-for-multirow-updates.aspx
> >
> > Found those links on http://stackoverflow.com/questions/6368349/why-dont-dbmss-support-assertion
> >
>
> If you are willing to "hide" the check behind a stored function, then the
> list is a bit longer than just MS Access and SQL Server.
> At least in Postgres and Oracle you can do the same thing.
>
> Thomas
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_feb
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user