Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Kathey Marsden writes: > After reviewing everyone's input I think that I'd be ok if we go ahead > with the extension in this particular case, if we have such a > disclaimer in the documentation. Is this acceptable to all? +1 Dag
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Mike Matrigali wrote: It would be good to see clear documentation of this feature as a non-standard implementation, maybe with some note that if a standard comes in this area we may change the behavior to match it. After reviewing everyone's input I think that I'd be ok if we go ahead with the extension in this particular case, if we have such a disclaimer in the documentation. Is this acceptable to all? It does feed temptation to do something similar with DERBY-728 (DRDA UNICODEMGR), so maybe it does grease the slope a bit, but we can discuss that *later* if needed. Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
On Jul 10, 2009, at 10:47 AM, Kathey Marsden wrote: Lance Andersen wrote: 3) Does this create a slippery slope for violation of our standards based charter? I do not see how. Every database vendor has their own extensions which are above and beyond standards Such a liberal approach to extensions would certainly require a change to our charter which specifically states migration to other databases as a reason for our standards adherence. Being standards based does not necessarily mean you cannot have extensions, nor does stating our charter is standards based have complete meaning. For example, there are many optional features described by the SQL Standard just like there is for JDBC. Derby certainly is not supporting all features in the JDBC or the latest SQL standard (nor does any other DB). So even if you support a feature in a standard, it does not mean you can migrate a Derby application to a database which does not support that feature, meaning you have non-standard standards :-) Most of the databases have been around long before the SQL Standard. Some databases have added features supported by other databases to aid in migration (such as in this case with TOP/LIMIT) because the feature is popular. This can be more important that just deciding that every feature must be in a spec. Do not get my wrong, standards are important (I am a spec lead and an alternate on the SQL standard committee), but should not be the only factor which decides whether something is worthwhile to add to your product. Looking at the Derby charter, it just talks about standards which is at a high level. It is not specific about levels of compliance or which versions of standards. Also from time to time, standards remove a feature so must you immediately drop it from your product? Of course not. A balanced approach is best especially when at the value of the feature. When JDBC 4.1 completes the escape syntax will be there as we closed on this ages ago in the EG. Regardless of the fact, it is Escape syntax to provide a standard way for JDBC apps to utilize the varying functionality in the ways different DBs provide support for this feature. If this doesn't require interface changes and if the JDBC committee can publish the syntax and guarantee it will be there and not be changed for 4.1, I think it would be ok to implement this now at least for the embedded driver. (The client might be harder). We could just leave it out of our documentation and have a Wiki pointer to the JDBC spec draft. It is escape syntax to allow the equivalent of LIMIT to be done in a portable way. Derby could do this today with the windowing feature and this really has nothing to do with requiring JDBC syntax for what we are talking about. My point for mentioning the JDBC escape syntax was that there are enough vendors with this type of functionality, it is worth adding to Derby in a fashion easier than using windowing... Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Dag H. Wanvik wrote: I think the mitigating circumstance here is that Rick says that this is an *omission* on part of the SQL standards committee. Naturally, they might change the syntax entirely for 2011, or choose *not* to allow this anyway, but that hardly seems likely. The OFFSET, FETCH FIRST/NEXT syntax is now fixed in three places (at least: the standard, PostgresQL and DB2). To me it seems a very low risk to take; we *are* supporting the standard syntax: In addition we are fixing the committee's omission in expectation that this will be included. I guess this all boils down how strictly we interpret our charter of following the standard. I worry our users will see our barring this as being overly careful at the cost of usability. Anyway, I'm out for 2 weeks, so this won't make 10.5.2 in any case ;) Dag After reading the subsequent info in this thread I think allowing this specific non-standard extension is ok. In general I still do believe that Derby should continue it's charter as a standard's based DB, and that just because other db's have implemented non-standard behavior that is not enough to implement them in Derby. The main points that convinced me are: 1) Work has been done to interact with he standard committee and it seems this is in keeping with the intent of the standard, and very likely to appear in a standard in the future. 2) The oversight of the standard has made the feature unusable in some applications, and the use of ? parameters in this case is the normal way Derby applications get performance. 3) This is an extension, not a breaking of the existing standard. It would be good to see clear documentation of this feature as a non-standard implementation, maybe with some note that if a standard comes in this area we may change the behavior to match it.
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Dag H. Wanvik wrote: Anyway, I'm out for 2 weeks, so this won't make 10.5.2 in any case ;) I am glad we are not under the gun for this for 10.5.2. Have a great vacation Dag and Rick! Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
I think the mitigating circumstance here is that Rick says that this is an *omission* on part of the SQL standards committee. Naturally, they might change the syntax entirely for 2011, or choose *not* to allow this anyway, but that hardly seems likely. The OFFSET, FETCH FIRST/NEXT syntax is now fixed in three places (at least: the standard, PostgresQL and DB2). To me it seems a very low risk to take; we *are* supporting the standard syntax: In addition we are fixing the committee's omission in expectation that this will be included. I guess this all boils down how strictly we interpret our charter of following the standard. I worry our users will see our barring this as being overly careful at the cost of usability. Anyway, I'm out for 2 weeks, so this won't make 10.5.2 in any case ;) Dag
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Lance Andersen wrote: 3) Does this create a slippery slope for violation of our standards based charter? I do not see how. Every database vendor has their own extensions which are above and beyond standards Such a liberal approach to extensions would certainly require a change to our charter which specifically states migration to other databases as a reason for our standards adherence. When JDBC 4.1 completes the escape syntax will be there as we closed on this ages ago in the EG. Regardless of the fact, it is Escape syntax to provide a standard way for JDBC apps to utilize the varying functionality in the ways different DBs provide support for this feature. If this doesn't require interface changes and if the JDBC committee can publish the syntax and guarantee it will be there and not be changed for 4.1, I think it would be ok to implement this now at least for the embedded driver. (The client might be harder). We could just leave it out of our documentation and have a Wiki pointer to the JDBC spec draft. Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
On Jul 10, 2009, at 9:12 AM, Kathey Marsden wrote: Rick Hillegas wrote: Other forms of parameterization are allowed by the standard. It is just that ? parameters are not explicitly included. The consensus of the committee members who discussed this was that this was an oversight, and no-one could explain why ? parameters had been omitted. The ? parameters would be, technically, an extension to what's in the standard--an extension which is compatible with the standard and which clearly fits the standard's intent. Thanks Rick for the clarification. My questions on this now are: 1) What would we do if for some reason this didn't make the standard? Would we back it out and break existing applications or remain forked from the standard? 2) What is the impact on users migrating to other database products? Often Derby is used as a development database and/or as one of many database options. How would we mitigate portability concerns? There are always going to be things that come up in migrations. Regardless of whether you are coming to/from Java DB to/from Oracle, MySQL, Postgresql, SQL Anywhere, ASE, Informix... documentation is how you mitigate these types of things documenting which features of Derby are not part of the standard 3) Does this create a slippery slope for violation of our standards based charter? I do not see how. Every database vendor has their own extensions which are above and beyond standards I think all three of these could be mitigated if we could get a public commitment from the SQL committee that this *will* be included in the 2011 spec or get a commitment from the JDBC committee on the escape syntax that will be supported in JDBC 4.1 and implement that. Is it possible to get such a commitment? When JDBC 4.1 completes the escape syntax will be there as we closed on this ages ago in the EG. Regardless of the fact, it is Escape syntax to provide a standard way for JDBC apps to utilize the varying functionality in the ways different DBs provide support for this feature. Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Rick Hillegas wrote: Other forms of parameterization are allowed by the standard. It is just that ? parameters are not explicitly included. The consensus of the committee members who discussed this was that this was an oversight, and no-one could explain why ? parameters had been omitted. The ? parameters would be, technically, an extension to what's in the standard--an extension which is compatible with the standard and which clearly fits the standard's intent. Thanks Rick for the clarification. My questions on this now are: 1) What would we do if for some reason this didn't make the standard? Would we back it out and break existing applications or remain forked from the standard? 2) What is the impact on users migrating to other database products? Often Derby is used as a development database and/or as one of many database options. How would we mitigate portability concerns? 3) Does this create a slippery slope for violation of our standards based charter? I think all three of these could be mitigated if we could get a public commitment from the SQL committee that this *will* be included in the 2011 spec or get a commitment from the JDBC committee on the escape syntax that will be supported in JDBC 4.1 and implement that. Is it possible to get such a commitment? Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Kathey Marsden wrote: Rick Hillegas wrote: I think that this discussion has gotten seriously off-track. It is the intent of the standard that the offset and window length values be parameterized. This is clear from the standard language Hmmm, I thought the problem was that the standard did not allow for parameters and that is why we were having this discussion. Dag said: On the contra side, we have the fact that dynamic arguments are not allowed by the SQL standard for this construct, at least not yet. I have to admit I haven't had time to research the standard myself, but am a bit confused. Can you resolve your statement with Dag's? Other forms of parameterization are allowed by the standard. It is just that ? parameters are not explicitly included. The consensus of the committee members who discussed this was that this was an oversight, and no-one could explain why ? parameters had been omitted. The ? parameters would be, technically, an extension to what's in the standard--an extension which is compatible with the standard and which clearly fits the standard's intent. I believe this is a serious usability defect of our OFFSET/FETCH implementation. As it stands today, you can only scroll one of these windows forward by sacrificing the performance benefits of prepared statements. It would be a shame if this feature had to remain unusable until the next rev of the standard in 2011. If the committee approves some other language at that time, then we can implement that extension. I agree this would potentially improve performance but don't see it as a bug. Hopefully the statement cache will help. The statement cache does not help. Without the ? parameters, each window has to be constructed by a separate prepared statement. This is the crux of the problem. If people wish to veto this proposal, then I would ask them to propose an alternative solution which makes this feature usable and which they believe fits more comfortably within the intention of the standard. Hopefully it won't come down to a veto. Hopefully we can reach consensus in the community. Yes, please. I'm looking forward to consensus when I get back from vacation! Cheers, -Rick Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Rick Hillegas wrote: I think that this discussion has gotten seriously off-track. It is the intent of the standard that the offset and window length values be parameterized. This is clear from the standard language and I confirmed this with the SQL committee in May. For the record, Lance and I sit on the SQL committee as alternate delegates from Sun. Dynamic ? parameters are Derby's model for specifying parameters. I believe this is a serious usability defect of our OFFSET/FETCH implementation. As it stands today, you can only scroll one of these windows forward by sacrificing the performance benefits of prepared statements. It would be a shame if this feature had to remain unusable until the next rev of the standard in 2011. If the committee approves some other language at that time, then we can implement that extension. I agree with you Rick and I feel that we should implement this feature If people wish to veto this proposal, then I would ask them to propose an alternative solution which makes this feature usable and which they believe fits more comfortably within the intention of the standard. no veto from me, I am for it. -Lance Thanks, -Rick Dag H. Wanvik wrote: Hi folks, I have a working patch sitting on DERBY-4208. I am wondering if this is a fix we should consider including for 10.5.2? The pro argument is that this is a usability issue, and to the extent it forces the app to construct SQL on the fly, makes the app more vulnerable to injection attacks, at least in theory. A user has asked for it. On the contra side, we have the fact that dynamic arguments are not allowed by the SQL standard for this construct, at least not yet. Personally I think it's a nice extension. Thoughts? Dag
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Rick Hillegas wrote: I think that this discussion has gotten seriously off-track. It is the intent of the standard that the offset and window length values be parameterized. This is clear from the standard language Hmmm, I thought the problem was that the standard did not allow for parameters and that is why we were having this discussion. Dag said: On the contra side, we have the fact that dynamic arguments are not allowed by the SQL standard for this construct, at least not yet. I have to admit I haven't had time to research the standard myself, but am a bit confused. Can you resolve your statement with Dag's? I believe this is a serious usability defect of our OFFSET/FETCH implementation. As it stands today, you can only scroll one of these windows forward by sacrificing the performance benefits of prepared statements. It would be a shame if this feature had to remain unusable until the next rev of the standard in 2011. If the committee approves some other language at that time, then we can implement that extension. I agree this would potentially improve performance but don't see it as a bug. Hopefully the statement cache will help. If people wish to veto this proposal, then I would ask them to propose an alternative solution which makes this feature usable and which they believe fits more comfortably within the intention of the standard. Hopefully it won't come down to a veto. Hopefully we can reach consensus in the community. Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
I think that this discussion has gotten seriously off-track. It is the intent of the standard that the offset and window length values be parameterized. This is clear from the standard language and I confirmed this with the SQL committee in May. For the record, Lance and I sit on the SQL committee as alternate delegates from Sun. Dynamic ? parameters are Derby's model for specifying parameters. I believe this is a serious usability defect of our OFFSET/FETCH implementation. As it stands today, you can only scroll one of these windows forward by sacrificing the performance benefits of prepared statements. It would be a shame if this feature had to remain unusable until the next rev of the standard in 2011. If the committee approves some other language at that time, then we can implement that extension. If people wish to veto this proposal, then I would ask them to propose an alternative solution which makes this feature usable and which they believe fits more comfortably within the intention of the standard. Thanks, -Rick Dag H. Wanvik wrote: Hi folks, I have a working patch sitting on DERBY-4208. I am wondering if this is a fix we should consider including for 10.5.2? The pro argument is that this is a usability issue, and to the extent it forces the app to construct SQL on the fly, makes the app more vulnerable to injection attacks, at least in theory. A user has asked for it. On the contra side, we have the fact that dynamic arguments are not allowed by the SQL standard for this construct, at least not yet. Personally I think it's a nice extension. Thoughts? Dag
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Kathey Marsden wrote:
Mike Matrigali wrote:
I would rather wait for an approved standard so that we don't later
get caught with apps depending on a non-standard behavior that we
might want to change in the future to meet a standard.
From the provided info it does not even look like there is a defacto
standard adopted by multiple db's.
I tend to agree that it is better to wait for a standard. This still
seems all over the place for the different database product
implementations and not yet even in a draft standard.
Well it is in the standard for 2008
::=
OFFSET { ROW | ROWS }
::=
FETCH { FIRST | NEXT } [ ] { ROW | ROWS } ONLY
So why not support it?
personally, if you can easily support some of the other variants, i
would do that as well. Just because something is not in an official
standard, it indirectly becomes a standard when implemented by multiple
vendors...
Don't get me wrong, standards are important, but so is making
applications easier to use and migrate from one platform to another
.02
Regards
Lance
Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Mike Matrigali wrote: I would rather wait for an approved standard so that we don't later get caught with apps depending on a non-standard behavior that we might want to change in the future to meet a standard. From the provided info it does not even look like there is a defacto standard adopted by multiple db's. I tend to agree that it is better to wait for a standard. This still seems all over the place for the different database product implementations and not yet even in a draft standard. Kathey
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
I would rather wait for an approved standard so that we don't later get caught with apps depending on a non-standard behavior that we might want to change in the future to meet a standard. From the provided info it does not even look like there is a defacto standard adopted by multiple db's. /mikem Dag H. Wanvik wrote: Hi folks, I have a working patch sitting on DERBY-4208. I am wondering if this is a fix we should consider including for 10.5.2? The pro argument is that this is a usability issue, and to the extent it forces the app to construct SQL on the fly, makes the app more vulnerable to injection attacks, at least in theory. A user has asked for it. On the contra side, we have the fact that dynamic arguments are not allowed by the SQL standard for this construct, at least not yet. Personally I think it's a nice extension. Thoughts? Dag
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Hi Dag, We are also adding support for this via a JDBC ESCAPE in JDBC 4.1 The current versions of Sybase support TOP and SQL Anywhere and MS SQL Server supports it as well INFORMIX IDS supports Limit Regards lance On Jul 9, 2009, at 10:06 AM, Dag H. Wanvik wrote: Kathey Marsden writes: I am hesitant to introduce behavior that is not standard compliant, but may be less hesitant if it is a sort of implied industry standard. What other database products do/do not support this syntax? * MySQL allows it in their LIMIT construct, cf. http://dev.mysql.com/doc/refman/5.0/en/select.html * DB2 has a syntax similar to the standard, but doesn't appear to allow dynamic parameters: http://bytes.com/groups/ibm-db2/185741-jdbc-fetch-first-rows-only http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0005280.htm * PostGreSQL has both a LIMIT and the new OFFSET/FETCH FIRST syntax: http://www.postgresql.org/docs/current/static/sql-select.html It appears to allow dynamic evaluation, c.f this quote: "If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit. If start evaluates to NULL, it is treated the same as OFFSET 0." in other words it can be an expression, not just a literal as the standard currently requires. * Sybase has a special statement called SET ROWCOUNT, it seems. It's not equivalent, I think, since "A limit violation occurs when the number of rows returned by a select statement exceeds the limit value", says the documentation: http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.sag2/html/sag2/sag234.htm but presumably this can be set before each execution of SELECT, although the docs are not explicit about this, at least not in the cited section. * SQL Server has a similar statement to Sybase, but the semantics seem more benign: "Causes SQL Server to stop processing the query after the specified number of rows are returned." (no talk of violation or error here). http://msdn.microsoft.com/en-us/library/ms188774.aspx Looking at this example, it seems work dynamically: http://authors.aspalliance.com/stevesmith/articles/sprowcount.asp * Oracle has a ROWNUM builtin that can be used in dynamic queries, akin to the standard ROW_NUMBER(). This can be used for DERBY also, but the current implementation of ROW_NUMBER has some bugs and users seems to balk at the WINDOWing complexity for something as simple as limiting the number of rows returned. Significantly, it still doesn't work in conjunction with ORDER BY (DERBY-3634). Note that the Oracle version requires a subquery, but does not require a windowing syntax: select * from ( select * from emp order by sal desc ) where ROWNUM <= 5; http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html In summary, the record is a bit mixed. Hearsay from the SQL committee indicates that the 2011 version will allow dynamic parameters for OFFSET/FETCH NEXT, and it seem the PostGreSQL people have had similar thoughts. Thanks, Dag
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Kathey Marsden writes: > I am hesitant to introduce behavior that is not standard compliant, > but may be less hesitant if it is a sort of implied industry standard. > What other database products do/do not support this syntax? * MySQL allows it in their LIMIT construct, cf. http://dev.mysql.com/doc/refman/5.0/en/select.html * DB2 has a syntax similar to the standard, but doesn't appear to allow dynamic parameters: http://bytes.com/groups/ibm-db2/185741-jdbc-fetch-first-rows-only http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0005280.htm * PostGreSQL has both a LIMIT and the new OFFSET/FETCH FIRST syntax: http://www.postgresql.org/docs/current/static/sql-select.html It appears to allow dynamic evaluation, c.f this quote: "If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit. If start evaluates to NULL, it is treated the same as OFFSET 0." in other words it can be an expression, not just a literal as the standard currently requires. * Sybase has a special statement called SET ROWCOUNT, it seems. It's not equivalent, I think, since "A limit violation occurs when the number of rows returned by a select statement exceeds the limit value", says the documentation: http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.sag2/html/sag2/sag234.htm but presumably this can be set before each execution of SELECT, although the docs are not explicit about this, at least not in the cited section. * SQL Server has a similar statement to Sybase, but the semantics seem more benign: "Causes SQL Server to stop processing the query after the specified number of rows are returned." (no talk of violation or error here). http://msdn.microsoft.com/en-us/library/ms188774.aspx Looking at this example, it seems work dynamically: http://authors.aspalliance.com/stevesmith/articles/sprowcount.asp * Oracle has a ROWNUM builtin that can be used in dynamic queries, akin to the standard ROW_NUMBER(). This can be used for DERBY also, but the current implementation of ROW_NUMBER has some bugs and users seems to balk at the WINDOWing complexity for something as simple as limiting the number of rows returned. Significantly, it still doesn't work in conjunction with ORDER BY (DERBY-3634). Note that the Oracle version requires a subquery, but does not require a windowing syntax: select * from ( select * from emp order by sal desc ) where ROWNUM <= 5; http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html In summary, the record is a bit mixed. Hearsay from the SQL committee indicates that the 2011 version will allow dynamic parameters for OFFSET/FETCH NEXT, and it seem the PostGreSQL people have had similar thoughts. Thanks, Dag
Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Dag H. Wanvik wrote: Hi folks, I have a working patch sitting on DERBY-4208. I am wondering if this is a fix we should consider including for 10.5.2? The pro argument is that this is a usability issue, and to the extent it forces the app to construct SQL on the fly, makes the app more vulnerable to injection attacks, at least in theory. A user has asked for it. On the contra side, we have the fact that dynamic arguments are not allowed by the SQL standard for this construct, at least not yet. Personally I think it's a nice extension. Thoughts? I am hesitant to introduce behavior that is not standard compliant, but may be less hesitant if it is a sort of implied industry standard. What other database products do/do not support this syntax? Kathey
Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Hi folks, I have a working patch sitting on DERBY-4208. I am wondering if this is a fix we should consider including for 10.5.2? The pro argument is that this is a usability issue, and to the extent it forces the app to construct SQL on the fly, makes the app more vulnerable to injection attacks, at least in theory. A user has asked for it. On the contra side, we have the fact that dynamic arguments are not allowed by the SQL standard for this construct, at least not yet. Personally I think it's a nice extension. Thoughts? Dag
