Re: [doc] What should the DITA source produce and how should it be produced?
Jean T. Anderson wrote: I'm starting a new topic for questions raised on the thread "[doc] Where should the DITA source files be checked in?" -- for the start of that thread, see http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200503.mbox/[EMAIL PROTECTED] Andrew McIntyre asked: This brings up several other question: should we have a copy of the built documentation checked in with the source for developer reference or only keep a copy in derby/site? It seems sensible to keep a copy of the build doc with the source it goes with, but would you just keep the built doc for the Developer's Guide? Usually you avoid checking in both source and objects built from the source. There is difficulty of keeping the two in sync. I know approximately nothing about DITA and building documentation from it. Can't we just check in the DITA source and build the documentation with each build? The documentation should be as much a part of a build as the jar files. Jack
Re: [PATCH] BackingStoreHashtable
Mike Matrigali wrote:
Thanks for the reply, I'll work with you to get this committed. I will
wait on the change you are working on. I think that is the best short
term solution, as you point out there is more work later on to improve
the work you have done. I would appreciate it if at least one other
person with experience on the language side take a look at this also.
It has been awhile since I looked at jvm memory stuff, but it use to be
a problem that totalMemory() would return the memory that the jvm
currently has, not the amount of memory that it is allowed to have. So
if you called it after just starting it might return a very small
number, say 1 meg, even if the jvm was started and told to grow to a max
of 100 meg. Worse was that the behavior was not consistent across
JVM/OS combinations.
This memory issue is a real problem as there are a number of things
that derby could do faster if it knew it could do the whole thing in
memory, but once you run out of memory it is hard to recover without
failing the current operation (and quite possibly other derby threads
and in a server environment other non derby threads).
At one point sun was proposing some jvm interfaces so one could tell if
you were getting "close" to running out of memory - so that applications
could take action before errors happened. If such a thing existed then
something like BackingStoreHashTable could grow in memory more
aggressively and then if it noticed the impending problem it could spill
everything to disk, and free up it's current usage.
I have modified my patch so that the optimizer and BackingStoreHashtable
use the same decision about when a hash table will spill to disk. The
optimizer calls the JoinStrategy.maxCapacity method to find the maximum
number of rows that the JoinStrategy can handle in a given number of
bytes. It rejects the strategy if the estimated row count is larger.
(Currently the optimizer limits each join to 1M of memory). The
HashJoinStrategy.maxCapacity method divides the maximum byte count by
the sum of the size of one row plus the size of a Hashtable entry. The
NestedLoopJoinStrategy.maxCapacity method always returns
Interer.MAX_VALUE. The HashJoinStrategy.getScanArgs method passes the
maximum capacity to the ResultSetFactory.|getHashScanResultSet method,
so that the actual BackingStoreHashtable will spill to disk when the
optimizer thought that it would. This means that hash joins will not
spill to disk unless the inner table has more rows than the optimizer
estimated.
I also changed the DiskHashtable implementation to pass its
keepAfterCommit parameter on to the
TransactionController.openConglomerate method. Previously DiskHashtable
only used keepAfterCommit to construct the temporaryFlag argument of
||TransactionController.createConglomerate and always passed "false" as
the hold argument of ||TransactionController.openConglomerate.
Since I made changes to the optimizer and hash join code generator I
hope that a Derby language expert can review at least that part of my
updated patch.
||I have not changed the way that BackingStoreHashtable decides when to
spill when its max_inmemory_rowcnt parameter is negative. (Only hash
joins pass a non-negative ||max_inmemory_rowcnt||). As Mike pointed out,
spilling when the in memory hash table grows larger than 1% of
Runtime.totalMemory() is not completely satisfactory. The JVM may be
able to get more memory and totalMemory() is likely to be small soon
after the JVM starts up. However, I do not know of anything that is
better. If totalMemory() grows subsequent ||BackingStoreHashtables will
be able to use more memory. Since ||BackingStoreHashtables are
temporary, this does not seem so bad to me.|
|
Regards
Jack Klebanoff
|
Index: java/engine/org/apache/derby/impl/sql/compile/FromTable.java
===
--- java/engine/org/apache/derby/impl/sql/compile/FromTable.java
(revision 155691)
+++ java/engine/org/apache/derby/impl/sql/compile/FromTable.java
(working copy)
@@ -95,6 +95,8 @@
private FormatableBitSet refCols;
+private double perRowUsage = -1;
+
private boolean considerSortAvoidancePath;
//this flag tells you if all the columns from this table are projected using
* from it.
@@ -660,16 +662,54 @@
}
/** @see Optimizable#maxCapacity */
- public int maxCapacity()
+ public int maxCapacity( JoinStrategy joinStrategy, int
maxMemoryPerTable) throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.THROWASSERT("Not expected to be called");
- }
-
- return 0;
+return joinStrategy.maxCapacity( maxCapacity, maxMemoryPerTable,
getPerRowUsage());
}
+private double getPerRowUsage() throws StandardExcep
Re: About improvement of DERBY-134
Jeremy Boynes wrote: Jack Klebanoff wrote: 1. sqlgrammar.jj. I think that creating a new method, isNonReservedKeyword() to determine whether a token is a non-reserved keyword or not, is a maintenance problem. Whenever we add a new non-reserved keyword we must add it to the list of tokens, to nonReservedKeyword(), and now to isNonReservedKeyword(). Having to add it in two places is difficult enough to discover or remember. If we need isNonReservedKeyword then we should find a way of combining nonReservedKeyword and isNonReservedKeyword so that only one of them keeps the list of non-reserved key words. It is not necessary for the parser to recognize 3 cases of ORDER BY sort key type. A column name is just one kind of . If the parser treats it as an expression we should still get the right ordering. I think that it would better if the parser did so. The OrderByColumn class can special case a simple column reference expression, as an optimization. This considerably simplifies parsing sort keys. The only sort key type that has to be handled specially is that of an integer constant. That specifies one of the select list columns as the sort key. This case can be recognized in the parser, as is done in the patch, or it can be recognized in OrderByColumn. In this alternative the parser always creates OrderByColumn nodes with the sort key given by an expression (a ValueNode). At bind time OrderByColumn can determine whether the sort key expression is an integer constant, and if so treat it as a column position. The two alternatives differ in the way that they treat constant integer expressions like "ORDER BY 2-1". The patch orders the rows by the constant 1, which is not usefull. With the patch "ORDER BY 2-1 ASC" and "ORDER BY 2-1 DESC" produce the same ordering. If OrderByColumn treated an integer constant sort key expression as a result column position then "ORDER BY 2-1" would cause the rows to be ordered on the first result column, which I think is more usefull. From the SQL spec, the is simply a value expression evaluated for each row. Hence "ORDER BY 2-1" means that all rows are peers and the ordering is implementation-dependent. I think allowing the value-expressions to evaluate to column position is potentially confusing. For example, suppose you have "ORDER BY ?" - do we order by the supplied value (which would be the same for all rows making the actual ordering implementation-dependent) or do we order by the column position (with the potential need to re-select indexes used to assist in ordering). I believe it is simple and SQL-compliant to have very simple rules here: 1) if all s are integer literals, then sort by the column position they imply. This is SQL-compliant as the comparison criteria for each row will be the same, making all rows peers and the actual order implementation dependent (we just define ours as using the appropriate column value). 2) if any is not an integer literal then we treat all of them as and compare rows accordingly. -- Jeremy I think that most people would be surprised to find that "ORDER BY 1 + 1" behaves differently than "ORDER BY 2". I also think that it is simpler for us to treat all integer constants the same. It is hard to get the parser to distinguish between a literal constant and a complex expression that starts with an integer literal. That is why Tomohito Nakayama needed to use complex lookahead in his parser change. I think that it is better to distinguish between integer constants and other expressions in the OrderByColumn class. ValueNodes have isConstantExpression(), getTypeID(), and getConstantValueAsObject() methods that OrderByColumn can use to see if the expression is an integer constant and, if so, what the value is. OrderByColumn can test whether the order by expression is an instanceOf ColumnReference or ResultColumn to special case a simple column reference. This simplifies the parser significantly and causes us to treat "ORDER BY 1 + 1" the same as "ORDER BY 2". I don't feel strongly enough about this to block a patch that is well tested and otherwise works correctly. Jack Klebanoff
Re: About improvement of DERBY-134
The new patch looks much better. However, I found two problems, one (Bserious and the other minor. (B (BThe serious problem is that INTERSECT no longer works. The (Blang/intersect.sql test (part of the derbylang suite) fails. The problem (Bis in the (Borg.apache.derby.impl.sql.compile.IntersectOrExceptNode.pushOrderingDown (Bmethod. It attempts to create OrderByColumns by calling (Bnf.getNode( C_NodeTypes.ORDER_BY_COLUMN, (BReuseFactory.getInteger( intermediateOrderByColumns[i] + 1), (Bcm) (BThis used to work. Now OrderByColumn.init throws a ClassCastException (Bbecause it expects to be passed a ValueNode, not an Integer. (B (BIntersectOrExceptNode.pushOrderingDown has to be changed to pass a (BValueNode. I think that (Bnf.getNode( C_NodeTypes.ORDER_BY_COLUMN, (Bnf.getNode( C_NodeTypes.INT_CONSTANT_NODE, (BReuseFactory.getInteger( intermediateOrderByColumns[i] + 1), (Bcm), (Bcm) (Bworks. (B (BThe minor problem is that the javadoc for OrderByColumn.init( Object (Bexpression) documents a "dummy" parameter that no longer exists. (B (BJack Klebanoff (B (BTomohitoNakayama wrote: (B (B> Hello. (B> (B> I have finished coding and testing in orderby.sql. (B> I'm not sure test is enough. (B> (B> Would you please review it ? (B> (B> Best regards. (B> (B> /* (B> (B> Tomohito Nakayama (B> [EMAIL PROTECTED] (B> [EMAIL PROTECTED] (B> (B> Naka (B> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B> (B> */ (B> - Original Message - From: "Satheesh Bandaram" (B> <[EMAIL PROTECTED]> (B> To: "Derby Development" (B> Sent: Saturday, March 12, 2005 6:59 AM (B> Subject: Re: About improvement of DERBY-134 (B> (B> (B>> Hi Tomohito Nakayama, (B>> (B>> Just wanted to check how you are progressing on the patch update, (B>> following comments by myself and Jack. I do think you are working on an (B>> important enhancement that not only yourself but other developpers have (B>> expressed interest in. I strongly encourage you to continue working on (B>> this and post any questions or comments you might have. You are pretty (B>> close to addressing all issues. (B>> (B>> I am willing to help, if you need any, to continue taking this further. (B>> (B>> Satheesh (B>> (B>> TomohitoNakayama wrote: (B>> (B>>> Hello. (B>>> Thanks for your reviewing. (B>>> (B>>> About 1: (B>>> Handling any sortKey as expression is better structure. (B>>> A little challenging but worth for it. (B>>> I will try. (B>>> (B>>> About 2: (B>>> Uh oh. (B>>> Bug about starting value of element indexing in ResultColumnList (B>>> Test of comma separated lists of ORDER BY expressions in orderby.sql (B>>> was needed. (B>>> (B>>> About 3: (B>>> I see. (B>>> It seems that it is certainly needed to add test case . (B>>> (B>>> I will continue this issue. (B>>> Best regards. (B>>> (B>>> /* (B>>> (B>>> Tomohito Nakayama (B>>> [EMAIL PROTECTED] (B>>> [EMAIL PROTECTED] (B>>> (B>>> Naka (B>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B>>> (B>>> */ (B>>> - Original Message - From: "Jack Klebanoff" (B>>> <[EMAIL PROTECTED]> (B>>> To: "Derby Development" (B>>> Sent: Sunday, February 20, 2005 8:37 AM (B>>> Subject: Re: About improvement of DERBY-134 (B>>> (B>>> (B>>>> TomohitoNakayama wrote: (B>>>> (B>>>>> Hello. (B>>>>> (B>>>>> I have put some LOOKAHEAD to sqlgrammer.jj and (B>>>>> add some test pattern to orderby.sql. (B>>>>> (B>>>>> Would someone review patch please ? (B>>>>> (B>>>>> Best regards. (B>>>>> (B>>>>> /* (B>>>>> (B>>>>> Tomohito Nakayama (B>>>>> [EMAIL PROTECTED] (B>>>>> [EMAIL PROTECTED] (B>>>>> (B>>>>> Naka (B>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B>>>>> (B>>>>> */ (B>>>>> - Original Message - From: "TomohitoNakayama" (B>>>>> <[EMAIL PROTECTED]> (B>>>>> To: "Derby Development" (B>>>>> Sent: Sunday, February 13, 2005 4:09 PM (B>>>>> Subject: Re: About improvement of DERBY-134 (B>>>>> (B>>>>>
Re: Using DOTS as Derby System Test
Ramandeep Kaur wrote: Hi, Database Open Source Test Suite (DOTS) is a test suite designed for stress testing on database systems. Apache Derby was tested with DOTS to investigate its usefulness as a system test. After several iterations of the test, it was found that DOTS may provide some value to Derby as a system test. I have written a document to provide information to the Derby community regarding the use of DOTS as a system test suite. I am attaching the document to this message. Thanks, Raman ([EMAIL PROTECTED]) In her attached PDF file Ramandeep wrote "the jvm memory size will depend on how long the test cases will be executed for". This sounds like Derby has a memory leak, which is a real problem. The memory usage should quickly rise to a peak and then level off, unless the test driver has a memory leak itself, or it steadily increases the number of open connections, PreparedStatements, etc. Jack Klebanoff
Re: About improvement of DERBY-134
The derbyall test suite found a problem. The lang/wisconsin.sql test (Bfailed. The problem output was: (B (Bij> -- Values clause is a single-row result set, so should not cause (Boptimizer (B-- to require sort. (Bget cursor c as (B'select * from TENKTUP1, (values 1) as t(x) (Bwhere TENKTUP1.unique1 = t.x (Border by TENKTUP1.unique1, t.x'; (BERROR 42X10: 'T' is not an exposed table name in the scope in which it (Bappears. (B (BThis error is incorrect. (B (BThere must be a problem in the way that the patch binds the ORDER BY (Bexpressions. I don't have time to look more deeply into it now. (B (BYou should probably run at least the derbylang test suite before (Bsubmitting a patch for ORDER BY. (B (BTo do this, change into an empty directory and run (Bjava org.apache.derbyTesting.functionTests.harness.RunSuite derbylang (BThe derbylang suite takes about 90 minutes on my laptop. The derbyall (Bsuite takes 5 or 6 hours. (B (BIn order to run just the wisconsin.sql test change into an empty (Bdirectory and run (Bjava org.apache.derbyTesting.functionTests.harness.RunTest (Blang/wisconsin.sql (B (BJack Klebanoff (B (BTomohitoNakayama wrote: (B (B> Hello. (B> (B> Thank for your checking. (B> I have solved the 2 problems. (B> Attached file is new patch. (B> (B> Best regards. (B> (B> /* (B> (B> Tomohito Nakayama (B> [EMAIL PROTECTED] (B> [EMAIL PROTECTED] (B> (B> Naka (B> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B> (B> */ (B> - Original Message - From: "Jack Klebanoff" (B> <[EMAIL PROTECTED]> (B> To: "Derby Development" (B> Sent: Tuesday, March 15, 2005 10:51 AM (B> Subject: Re: About improvement of DERBY-134 (B> (B> (B>> The new patch looks much better. However, I found two problems, one (B>> serious and the other minor. (B>> (B>> The serious problem is that INTERSECT no longer works. The (B>> lang/intersect.sql test (part of the derbylang suite) fails. The problem (B>> is in the (B>> org.apache.derby.impl.sql.compile.IntersectOrExceptNode.pushOrderingDown (B>> method. It attempts to create OrderByColumns by calling (B>> nf.getNode( C_NodeTypes.ORDER_BY_COLUMN, (B>> ReuseFactory.getInteger( intermediateOrderByColumns[i] + 1), (B>> cm) (B>> This used to work. Now OrderByColumn.init throws a ClassCastException (B>> because it expects to be passed a ValueNode, not an Integer. (B>> (B>> IntersectOrExceptNode.pushOrderingDown has to be changed to pass a (B>> ValueNode. I think that (B>> nf.getNode( C_NodeTypes.ORDER_BY_COLUMN, (B>> nf.getNode( C_NodeTypes.INT_CONSTANT_NODE, (B>> ReuseFactory.getInteger( intermediateOrderByColumns[i] + 1), (B>> cm), (B>> cm) (B>> works. (B>> (B>> The minor problem is that the javadoc for OrderByColumn.init( Object (B>> expression) documents a "dummy" parameter that no longer exists. (B>> (B>> Jack Klebanoff (B>> (B>> TomohitoNakayama wrote: (B>> (B>>> Hello. (B>>> (B>>> I have finished coding and testing in orderby.sql. (B>>> I'm not sure test is enough. (B>>> (B>>> Would you please review it ? (B>>> (B>>> Best regards. (B>>> (B>>> /* (B>>> (B>>> Tomohito Nakayama (B>>> [EMAIL PROTECTED] (B>>> [EMAIL PROTECTED] (B>>> (B>>> Naka (B>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B>>> (B>>> */ (B>>> - Original Message - From: "Satheesh Bandaram" (B>>> <[EMAIL PROTECTED]> (B>>> To: "Derby Development" (B>>> Sent: Saturday, March 12, 2005 6:59 AM (B>>> Subject: Re: About improvement of DERBY-134 (B>>> (B>>> (B>>>> Hi Tomohito Nakayama, (B>>>> (B>>>> Just wanted to check how you are progressing on the patch update, (B>>>> following comments by myself and Jack. I do think you are working (B>>>> on an (B>>>> important enhancement that not only yourself but other developpers (B>>>> have (B>>>> expressed interest in. I strongly encourage you to continue working on (B>>>> this and post any questions or comments you might have. You are pretty (B>>>> close to addressing all issues. (B>>>> (B>>>> I am willing to help, if you need any, to continue taking this (B>>>> further. (B>>>> (B>>>> Satheesh (B>>>> (B>>>> TomohitoNakayama wrote: (B>>>> (B>>>
Re: About improvement of DERBY-134
java org.apache.derbyTesting.functionTests.harness.RunSuite suiteName (Bwrites a test report in suiteName_report.txt. This describes the (Benvironment, prints a counts of tests that passed and failed, and lists (Ball the differences from expected in the failed tests. You can also find (Blists of passed and failed tests in suiteName_pass.txt and (BsuiteName_fail.txt. You can also find outputs, diffs, databases, and (Bderby.log files for the failed tests, but you have to dig deeper into (Bthe directories. (B (BWhen I ran the lang/wisconsin.sql test with your patch it failed. The query (Bget cursor c as (B'select * from TENKTUP1, (values 1) as t(x) (Bwhere TENKTUP1.unique1 = t.x (Border by TENKTUP1.unique1, t.x'; (Bclose c; (Bfailed to compile, but the test expected it to run. It worked before (Bapplying the patch, and I believe that it should work. (B (BI boiled the problem down to a small SQL file, which I have attached. (BThat file should run without error under ij as long as database "testdb" (Bdoes not exist when you start ij. (B (BI believe that the problem is with the updated bind method in (BOrderByNode. It does not seem to be able to handle correlation names (Bfrom the FROM list. In the example that failed "t" is not the name of an (Bactual table, but a correlation name used to name the "(values 1)" (Bvirtual table. (B (BI tried changing OrderByColumn.bindOrderByColumn to call (Bexpression.bindExcpression and then eliminating most of the code in (BresolveColumnReference. However this does not work either. Then the (Bstatement (Bvalues (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by "SQLCol1" (B(from the lang/orderby.sql test) fails. (B (BI will work on this some more. Perhaps you can continue looking at it also. (B (BJack (B (BTomohitoNakayama wrote: (B (B> I have tried derbylang test suite , but could not found error which (B> was reported . (B> (B> What I found was just difference around "lang/floattypes.sql". (B> I 'm not sure this is error or not yet. (B> (B> Back to reported bug, the next is the test sql in my wisconsin.sql. (B> (B> -- Values clause is a single-row result set, so should not cause (B> optimizer (B> -- to require sort. (B> (B> get cursor c as (B> 'select * from TENKTUP1, (values 1) as t(x) (B> where TENKTUP1.unique1 = t.x (B> order by TENKTUP1.unique1, t.x'; (B> close c; (B> (B> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); (B> (B> commit; (B> (B> -- Try with a join on unique column and order on non-unique column (B> === (B> I couldn't find difference between what in your mail. (B> (B> (B> (B> Next is svn-status of my wisconsin.sql. (B> === (B> $ svn status -v wisconsin.sql (B> 157254 122528 djd wisconsin.sql (B> === (B> (B> Is this caused by versioning problem of wisconsin.sql ...? (B> (B> /* (B> (B> Tomohito Nakayama (B> [EMAIL PROTECTED] (B> [EMAIL PROTECTED] (B> (B> Naka (B> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B> (B> */ (B> - Original Message - From: "TomohitoNakayama" (B> <[EMAIL PROTECTED]> (B> To: "Derby Development" (B> Sent: Saturday, March 19, 2005 3:42 PM (B> Subject: Re: About improvement of DERBY-134 (B> (B> (B>> Thank you for your checking. (B>> (B>> I did'nt know way to test whole sqls. (B>> Sorry for insufficient test. (B>> (B>> Now I will try whole test. (B>> (B>> Best regards. (B>> (B>> /* (B>> (B>> Tomohito Nakayama (B>> [EMAIL PROTECTED] (B>> [EMAIL PROTECTED] (B>> (B>> Naka (B>> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B>> (B>> */ (B>> - Original Message - From: "Jack Klebanoff" (B>> <[EMAIL PROTECTED]> (B>> To: "Derby Development" (B>> Sent: Saturday, March 19, 2005 9:04 AM (B>> Subject: Re: About improvement of DERBY-134 (B>> (B>> (B>>> The derbyall test suite found a problem. The lang/wisconsin.sql test (B>>> failed. The problem output was: (B>>> (B>>> ij> -- Values clause is a single-row result set, so should not cause (B>>> optimizer (B>>> -- to require sort. (B>>> get cursor c as (B>>> 'select * from TENKTUP1, (values 1) as t(x) (B>>> where TENKTUP1.unique1 = t.x (B>>> order by TENKTUP1.unique1, t.x'; (B>>> ERROR 42X10: 'T' is not an exposed table name in the scope in which it (B>>> appears. (B>>> (B>>> This error is incorrect. (B>
Re: About improvement of DERBY-134
I tried re-applying your patch, rebuilding, and re-running the derbylang (Bsuite. The result was the same: the lang/wisconsin.sql test failed. (BInterestingly my derbylang suite ran 1 more test than was reported in (Byour derbylang_report.txt at (Bhttp://www5.ocn.ne.jp/~tomohito/derbylang_report.txt. I do not know why (Bit worked for you. The stack trace from the derby.log file was: (B (BStatement is: select * from TENKTUP1, (values 1) as t(x) (Bwhere TENKTUP1.unique1 = t.x (Border by TENKTUP1.unique1, t.x (BERROR 42X10: 'T' is not an exposed table name in the scope in which it (Bappears. (Bat (Borg.apache.derby.iapi.error.StandardException.newException(StandardException.java:311) (Bat (Borg.apache.derby.impl.sql.compile.OrderByColumn.resolveColumnReference(OrderByColumn.java:312) (Bat (Borg.apache.derby.impl.sql.compile.OrderByColumn.bindOrderByColumn(OrderByColumn.java:147) (Bat (Borg.apache.derby.impl.sql.compile.OrderByList.bindOrderByColumns(OrderByList.java:153) (Bat org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java:255) (Bat (Borg.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:332) (Bat (Borg.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107) (Bat (Borg.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688) (Bat (Borg.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501) (Bat (Borg.apache.derby.impl.jdbc.EmbedStatement.executeQuery(EmbedStatement.java:130) (Bat org.apache.derby.impl.tools.ij.ij.GetCursorStatement(ij.java:1102) (Bat org.apache.derby.impl.tools.ij.ij.ijStatement(ij.java:550) (Bat org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:289) (Bat org.apache.derby.impl.tools.ij.Main.go(Main.java:209) (Bat org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:175) (Bat org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55) (Bat org.apache.derby.tools.ij.main(ij.java:60) (B (BI have attached my derbylang_report.txt file. (B (BJack (B (BTomohitoNakayama wrote: (B (B> I have tried your small.sql and result was as next. (B> (B> --These are evidence for improvement of 134 (B> ij> select * from test_number order by abs(value); (B> VALUE (B> --- (B> 1 (B> 2 (B> 3 (B> (B> 3 rows selected (B> ij> select * from test_number order by value * -1; (B> VALUE (B> --- (B> 3 (B> 2 (B> 1 (B> (B> 3 rows selected (B> (B> --This is what was written in small.sql (B> ij> create table TENKTUP1 ( (B> unique1 int not null, (B> unique2 int not null, (B> two int, (B> four int, (B> ten int, (B> twenty int, (B> onePercent int, (B> tenPercent int, (B> twentyPercent int, (B> fiftyPercent int, (B> unique3 int, (B> evenOnePercent int, (B> oddOnePercent int, (B> stringu1 char(52) not null, (B> stringu2 char(52) not null, (B> string4 char(52) (B> ); (B> 0 rows inserted/updated/deleted (B> ij> get cursor c as (B> 'select * from TENKTUP1, (values 1) as t(x) (B> where TENKTUP1.unique1 = t.x (B> order by TENKTUP1.unique1, t.x'; (B> ij> (B> (B> Unfortunately, I could not found any ... (B> (B> And I uploaded derbylang_report.txt to next url. (B> (B> http://www5.ocn.ne.jp/~tomohito/derbylang_report.txt (B> (B> Can you find any clue in it ? (B> Are there any difference between yours ? (B> (B> If could. I want to yourr derbylang_report... (B> (B> Best regards. (B> (B> /* (B> (B> Tomohito Nakayama (B> [EMAIL PROTECTED] (B> [EMAIL PROTECTED] (B> (B> Naka (B> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B> (B> */ (B> - Original Message - From: "Jack Klebanoff" (B> <[EMAIL PROTECTED]> (B> To: "Derby Development" (B> Sent: Tuesday, March 22, 2005 7:33 AM (B> Subject: Re: About improvement of DERBY-134 (B> (B> (B>> java org.apache.derbyTesting.functionTests.harness.RunSuite suiteName (B>> writes a test report in suiteName_report.txt. This describes the (B>> environment, prints a counts of tests that passed and failed, and lists (B>> all the differences from expected in the failed tests. You can also find (B>> lists of passed and failed tests in suiteName_pass.txt and (B>> suiteName_fail.txt. You can also find outputs, diffs, databases, and (B>> derby.log files for the failed tests, but you have to dig deeper into (B>> the directories. (B>> (B>> When I ran the lang/wisconsin.sql test with your patch it failed. The (B>> query (B>> get cursor c as (B>> 'select * from TENKTUP1, (values 1) as t(x) (B>> where TENKTUP1.unique1 = t.x (B>> order by TENKT
Re: About improvement of DERBY-134
You may have to do an svn update to bring in the lastest version of the source. I think that there has been some sort of change in the way the Derby handles binding when there are correlation names. Perhaps it is the combination of your changes and these other changes that cause the failure in wisconsin.sql. It must have been made about a week ago. It has affected some other stuff I am working on. I do not know who made the change, why, or exactly when. (Hopefully you will not have to do any merging). Perhaps the lang/orderby.sql tests need to be improved with some cases that use table correlation names in the order by clause. e.g. select * from (values (2),(1)) as t(x) orderby t.x select t1.id,t2.c3 from ta as t1 join tb as t2 on t1.id = t2.id order by t2.c2,t1.id,t2.c3 This is a test of functionality that existed before your changes. Test cases like these probably should have been in lang/orderby.sql before you started. Jack Klebanoff TomohitoNakayama wrote: I have tried your small.sql and result was as next. --These are evidence for improvement of 134 ij> select * from test_number order by abs(value); VALUE --- 1 2 3 3 rows selected ij> select * from test_number order by value * -1; VALUE --- 3 2 1 3 rows selected --This is what was written in small.sql ij> create table TENKTUP1 ( unique1 int not null, unique2 int not null, two int, four int, ten int, twenty int, onePercent int, tenPercent int, twentyPercent int, fiftyPercent int, unique3 int, evenOnePercent int, oddOnePercent int, stringu1 char(52) not null, stringu2 char(52) not null, string4 char(52) ); 0 rows inserted/updated/deleted ij> get cursor c as 'select * from TENKTUP1, (values 1) as t(x) where TENKTUP1.unique1 = t.x order by TENKTUP1.unique1, t.x'; ij> Unfortunately, I could not found any ... And I attached derbylang_report.txt to this mail. Can you find any clue in it ? Are there any difference between yours ? If could. I want to yourr derbylang_report... Best regards. /* Tomohito Nakayama [EMAIL PROTECTED] [EMAIL PROTECTED] Naka http://www5.ocn.ne.jp/~tomohito/TopPage.html */ - Original Message ----- From: "Jack Klebanoff" <[EMAIL PROTECTED]> To: "Derby Development" Sent: Tuesday, March 22, 2005 7:33 AM Subject: Re: About improvement of DERBY-134 java org.apache.derbyTesting.functionTests.harness.RunSuite suiteName writes a test report in suiteName_report.txt. This describes the environment, prints a counts of tests that passed and failed, and lists all the differences from expected in the failed tests. You can also find lists of passed and failed tests in suiteName_pass.txt and suiteName_fail.txt. You can also find outputs, diffs, databases, and derby.log files for the failed tests, but you have to dig deeper into the directories. When I ran the lang/wisconsin.sql test with your patch it failed. The query get cursor c as 'select * from TENKTUP1, (values 1) as t(x) where TENKTUP1.unique1 = t.x order by TENKTUP1.unique1, t.x'; close c; failed to compile, but the test expected it to run. It worked before applying the patch, and I believe that it should work. I boiled the problem down to a small SQL file, which I have attached. That file should run without error under ij as long as database "testdb" does not exist when you start ij. I believe that the problem is with the updated bind method in OrderByNode. It does not seem to be able to handle correlation names from the FROM list. In the example that failed "t" is not the name of an actual table, but a correlation name used to name the "(values 1)" virtual table. I tried changing OrderByColumn.bindOrderByColumn to call expression.bindExcpression and then eliminating most of the code in resolveColumnReference. However this does not work either. Then the statement values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by "SQLCol1" (from the lang/orderby.sql test) fails. I will work on this some more. Perhaps you can continue looking at it also. Jack TomohitoNakayama wrote: I have tried derbylang test suite , but could not found error which was reported . What I found was just difference around "lang/floattypes.sql". I 'm not sure this is error or not yet. Back to reported bug, the next is the test sql in my wisconsin.sql. -- Values clause is a single-row result set, so should not cause optimizer -- to require sort. get cursor c as 'select * from TENKTUP1, (values 1) as t(x) where TENKTUP1.unique1 = t.x order by TENKTUP1.unique1, t.x'; close c; values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); commit; -- Try w
Re: [jira] Commented: (DERBY-147) ERROR 42X79 not consistant ? - same column name specified twice
It is not so simple. The order by specification may be ambiguous. Derby allows the following: select t.c1 as a, t.c2 as a from t What should we do if you add "order by a" to the above select? "a" is truly ambiguous. I can think of several alternatives: 1. Prohibit duplicate select list column names, whether or not there is an order by clause. 2. Change ORDER BY processing. When it finds an ambiguous column name check whether all the columns with that name are really the same. Allow the ORDER BY if so. Remember that Tomohito Nakayama is working on allowing general expressions in the ORDER BY clause, so this is not so easy. 3. Change ORDER BY processing. When it finds an ambiguous column name check whether the sort key names a column in an underlying table (not a correlation name). Allow the ORDER BY if so. Throw an SQLException if not, even if all the possible columns have the same value. This fixes the case in the bug report. 4. Keep the error checking as is but improve the error message. Something like "ORDER BY column 'xx' is ambiguous". 5. Do nothing. Jack Klebanoff Diljeet Dhillon (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-147?page=comments#action_61483 ] Diljeet Dhillon commented on DERBY-147: --- Hi, Have we established whether a possible patch/fix will be provided for this issue. and what the possible timescales may be? ERROR 42X79 not consistant ? - same column name specified twice --- Key: DERBY-147 URL: http://issues.apache.org/jira/browse/DERBY-147 Project: Derby Type: Bug Reporter: Bernd Ruehlicke This happens from JDBC or ij. Here the output form ij> ij version 10.0 CONNECTION0* - jdbc:derby:phsDB * = current connection ij> select a1.XXX_foreign, a1.native, a1.kind, a1.XXX_foreign FROM slg_name_lookup a1 ORDER BY a1.XXX_foreign; ERROR 42X79: Column name 'XXX_FOREIGN' appears more than once in the result of the query expression. But when removing the ORDER BY and keeping the 2 same column names it works ij> select a1.XXX_foreign, a1.native, a1.kind, a1.XXX_foreign FROM slg_name_lookup a1; XXX_FOREIGN |NATIVE |KIND|XXX_FOREIGN --- 0 rows selected ij> So - it seams to be OK to specify the same column twice - as long as you do not add the ORDER BY clause. I woul dof course like that the system allows this - but at leats it should be consistant and either allow both or none of the two queries above.
Re: Questions about "getApproximateLengthInBytes()"
Army wrote: RPost wrote: I also noticed that the 'estimated MemoryUsage() method in DataTypeDescriptor.java has similar, but in some cases different, numbers. Thanks for the reply--and so the plot thickens. I did notice that values for date/time/timestamp are all 12 in the estimateMemoryUsage method, and that the phrase "I think" shows up once while "Who knows?" shows up three times, which makes me wonder about the accuracy there. On the other hand, the method "getApproximateLengthInBytes()" has the word "approximate" in it, which isn't exactly confidence-boosting, either. And since "getMaximumWidth()" (which is what is currently used for metadata LENGTH) isn't correct either, one is forced to wonder if metadata shouldn't perhaps be doing it's own thing entirely...? Hmmm, Army The DataTypeDescriptor.estimated MemoryUsage() method estimates the amount of RAM used by the data value. It is supposed to include of Java object overhead and the space taken by pointers (references). It is likely to be different than the amount of disk space used by the data value. I am not sure whether DatabaseMetaData.getProcedureColumns() is supposed to return the size on disk or the RAM size of the column. I would guess that it is supposed to return the size on disk. Jack
Re: [Patch] Re: About improvement of DERBY-134
TomohitoNakayama wrote: Hello. I have added some test to orderby.sql (and correspond result to orderby.out) , which was suggested by Jack Klebanoff. And I have executed derbylang test and coulud not found error exept for lang/floattypes.sql. I think this error is not caused by my patch. derbylang_report.txt is attached to this mail. Please check this patch from a point of other person's view. best regards. /* Tomohito Nakayama [EMAIL PROTECTED] [EMAIL PROTECTED] Naka http://www5.ocn.ne.jp/~tomohito/TopPage.html */ The latest patch looks good to me. I looked at the changes, applied the patch to recently updated Derby source, and successfully ran the derbylang test suite. (I did not even see the failure in lang/floattypes.sql that Tomohito saw. It was probably fixed in Derby updates I picked up today). Jack Klebanoff
Re: In-memory database
Øystein Grøvlen wrote: [snip] In the Derby TODO-list it says that your prototype is an "implementation of the org.apache.derby.io package that provides an in-memory database". Does that mean that the Raw Store (e.g. RAFContainer) thinks that it is writing to a file, but that the io-system is basically doing dummy operations? That does not sound very efficient to me compared to supporting the in-memory database directly at the Raw Store level. I would except an in-memory implementation to have in-memory specific implementations of for example Logger and BaseContainer, but I can see that that is probably much more work. Is that the main reason for doing it this way? Yes, in my prototype the Raw Store thinks that it is writing to a file. (Actually it thinks that it is writing to the StorageFactory and related interfaces). The raw store is unchanged from the one in the normal Derby implementation. The prototype was originally written to test the StorageFactory interface and the Raw Store's use of it. As you guessed, the reason for doing it this way is to reduce the amount of work required. We only have one version of the Raw Store to implement, test, and maintain. Furthermore, we can plug in other StorageFactory implementations beyond simple disk file or memory implementations. It is the usual trade-off with program modularization: you make it easier to plug in new components, but make some optimizations difficult or impossible. Jack Klebanoff
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Kathey Marsden wrote: Shreyas Kaushik wrote: Hi Kathey, Thanks for all the guidance and help. Yes got one of my implicit questions right :-) of how to debug the tests in the framework. I would definitely do a wrie up for this and put it up on the web when I am comfortable doing this. Yes this sort of information would definitely hep users debug the tests in the framework. Evan after running the tests with the properties that you had mentioned the tmp file did not have any major changes ( no stack tarces for the error I got ), except for a few warning message relating to empty tables. One interesting I noticed is that the select statement in the test that is prior to this is, does a similar operation and the result fetches two rows. So I presume the delte should delete two rows am I missing something obvious here ? I did a pass of code that I have changed, I just pull the correlation name from the query and pass it to the DeleteNode. Could this be a binding problem? Looks like that from the error I am getting. I will look at this aspect more, any more pointers/comments on my code changes will definitely help. ~ Shreyas Kathey Marsden wrote: Hi Shreyas, I am glad you are back on the trail with this issue and have some ideas.I really can't answer your specific question at all because I don't know *what* SQL we are talking about here, but if after some more research, you are still stuck, feel free to post a question. Don't forget to include the five key ingredients to getting answers from busy people on the list. 1) The small bit of sql or java code in question. 2) A description of the old and new behaviour and what you think the right behavior should be. 3) The stack trace if there is one. 4) What you have learned so far from your own evaluation of 1-3 above. 5) A specific question that is not going to take a lot of research for someone to answer that you think might send you back along your way if answered. For this post I think you would have done a pretty good job with 4 and 5 if 1, 2, and 3 were here too. I think you forgot 1 and 2. below are some tips for step 3 to get a stack trace. a) Create a small repro.sql script Take just enough sql to get the specific sql from step 1 working. (Usually some ddl some inserts and then the trouble maker) b)start ij with -Dij.exceptionTrace=true and execute the sql. Here is an example.Looks like I need help with my select statement #:) $ java -Dij.exceptionTrace=true org.apache.derby.tools.ij ij version 10.1 ij> run 'repro.sql'; ij> connect 'jdbc:derby:wombat;create=true'; ij> create table mytab(i int); 0 rows inserted/updated/deleted ij> insert into mytab values(1); 1 row inserted/updated/deleted ij> selectoops from mytab; ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1. ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:311) at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(ParserImpl.java:156) at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:298) at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:475) at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:299) at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433) at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310) at org.apache.derby.impl.tools.ij.Main.go(Main.java:209) at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:175) at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55) at org.apache.derby.tools.ij.main(ij.java:60) ij> As for reviewing your code so far, I am sorry, but I don't think I have time to do that right now. Just too busy, but perhaps someone else from the community can. I have noticed others from your company on the list. I know that a lot of times when I need a review, I ping someone that I work with and have pretty good results that way. Kathey Another good place to look for debugging information is the derb.log file. When a test is run using the Derby test harness this file is found one level down from the test output. If the test output is in file dir/test.out then the log file is in dir/test/derby.log. The derby.log file has a record of the SQLExceptions, the statement that caused them, and the stack traces. I often look in derby.log first, though if the test does a lot of negative testing derby.log may be cluttered with a lot of expected SQLExceptions. Jack Klebanoff
[PATCH] Re: [jira] Updated: (DERBY-219) EXCEPT/INTERSECT fails in views
I have made a fix for the problem. Actually the bug affects any
subquery, not just a view.
The fix includes an expanded lang/intersect.sql test. The fix passes the
derbylang test suite.
The patch is included here and in Jira.
Jack Klebanoff
Index: java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
===
--- java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
(revision 161138)
+++ java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
(working copy)
@@ -224,6 +224,18 @@
RowOrdering rowOrdering)
throws StandardException
{
+ leftResultSet = optimizeSource(
+ optimizer,
+ leftResultSet,
+ (PredicateList) null,
+ outerCost);
+
+ rightResultSet = optimizeSource(
+ optimizer,
+ rightResultSet,
+ (PredicateList) null,
+ outerCost);
+
CostEstimate costEstimate = getCostEstimate(optimizer);
CostEstimate leftCostEstimate = leftResultSet.getCostEstimate();
CostEstimate rightCostEstimate = rightResultSet.getCostEstimate();
Index:
java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
===
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
(revision 161138)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
(working copy)
@@ -143,3 +143,20 @@
-- Invalid order by
select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by t1.i1;
select id,i1,i2 from t1 except select id,i1,i2 from t2 order by t1.i1;
+
+-- views using intersect and except
+create view view_intr_uniq as select id,i1,i2 from t1 intersect select
id,i1,i2 from t2;
+select * from view_intr_uniq order by 1 DESC,2,3;
+
+create view view_intr_all as select id,i1,i2 from t1 intersect all select
id,i1,i2 from t2;
+select * from view_intr_all order by 1,2,3;
+
+create view view_ex_uniq as select id,i1,i2 from t1 except select id,i1,i2
from t2;
+select * from view_ex_uniq order by 1,2,3;
+
+create view view_ex_all as select id,i1,i2 from t1 except all select id,i1,i2
from t2;
+select * from view_ex_all order by 1 DESC,2,3;
+
+-- intersect joins
+select t1.id,t1.i1,t2.i1 from t1 join t2 on t1.id = t2.id
+intersect select t1.id,t1.i2,t2.i2 from t1 join t2 on t1.id = t2.id;
Index: java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
===
--- java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
(revision 161138)
+++ java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
(working copy)
@@ -350,4 +350,44 @@
ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY
clause.
ij> select id,i1,i2 from t1 except select id,i1,i2 from t2 order by t1.i1;
ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY
clause.
+ij> -- views using intersect and except
+create view view_intr_uniq as select id,i1,i2 from t1 intersect select
id,i1,i2 from t2;
+0 rows inserted/updated/deleted
+ij> select * from view_intr_uniq order by 1 DESC,2,3;
+ID |I1 |I2
+---
+5 |NULL |NULL
+2 |1 |2
+1 |1 |1
+ij> create view view_intr_all as select id,i1,i2 from t1 intersect all select
id,i1,i2 from t2;
+0 rows inserted/updated/deleted
+ij> select * from view_intr_all order by 1,2,3;
+ID |I1 |I2
+---
+1 |1 |1
+2 |1 |2
+5 |NULL |NULL
+ij> create view view_ex_uniq as select id,i1,i2 from t1 except select id,i1,i2
from t2;
+0 rows inserted/updated/deleted
+ij> select * from view_ex_uniq order by 1,2,3;
+ID |I1 |I2
+---
+3 |1 |3
+4 |1 |3
+6 |NULL |NULL
+ij> create view view_ex_all as select id,i1,i2 from t1 except all select
id,i1,i2 from t2;
+0 rows inserted/updated/deleted
+ij> select * from view_ex_all order by 1 DESC,2,3;
+ID |I1 |I2
+---
+6 |NULL |NULL
+4 |1
Re: Formatting patch
I vote for using spaces for indentation. There is no universally accepted standard for tab width. If a file uses tabs then the indentation looks off if your editor has a different tab width setting. Things get really bad if a file is edited using different tab width settings: then no tab width setting works correctly for the whole file. Jack Klebanoff David Van Couvering wrote: I opened the files in vi and set tabstop to 4 and things do look a lot better. I will adjust NetBeans to this formatting. Do you want tabs for indentation, or spaces? I can't tell, but it sounds like you won't be committing this patch? I need to know so I can know whether to apply my logical changes to the files with the old formatting or the new formatting... Thanks, David Mike Matrigali wrote: Unfortunately from the beginning there was no imposed standard on the original cloudscape code. Predominantly most of the code is 4 space indentation, with 8 space tabs. Unfortunately there are some files that have been screwed up by IDE's. I like braces on a new line, but would say there is a large percentage of code with the other convention. Worst are files with both, changing code to be consistent in one file seems reasonable to me. I just took a look at RunSuite.java and the indentation did not look that off to me (line 91-93 looks off, but a quick look did not find any other problems). (I use vim and others use emacs so we could provide settings if necessary). The test suite code is one of the worst offenders and had been scheduled for a complete rewrite, but just never got the work allocated. It seemed better to contribute the tests in their existing state since they worked, rather than wait to fix them up. David Van Couvering wrote: Hi. I am working on supporting tests with multiple databases, and have been working with org.apache.derbyTesing.harness.RunSuite and RunList. Both of these have some pretty "odd" formatting which has actually made it hard for me to read and understand the logic, so I ran "reformat" in NetBeans to get everything lined up. I tried to follow the standard formatting that I seem to be seeing in the Derby code, in particular braces on a new line, two spaces (no tabs) for indentation. Scanning the mailing list, I don't see any votes about a specific coding standard... I'd like to submit this as a separate patch so you don't get a huge number of diffs when I submit the patch that makes logical changes.
Re: Adding new documentation for debugging test failures in the test framework
I noticed two typos:
Steps to be followed
1. Frist the test/s have to be run. The details fro running the tests can be found at
"First" is mispelled "Frist". "For" is mispelled "fro".
I think that you should also suggest looking in the derby.log file when
you cannot understand the diffs. It is found in mytest/derby.log.
Jack Klebanoff
Shreyas Kaushik wrote:
Hi all,
Here is the initial draft as per Apache Forrest 0.6. Please review
this doc and let me know of improvements.
When I was testing this I was unable to see the Samples tab in the web
site I built.The build went through successfully but,
I had to type the link in the browser window to view the page, this
despite adding the following entry in
${derby.site.root}src/documentation/content/xdocs/site.xml,
Should I do anything more to see the tab?
~ Shreyas
Jean T. Anderson wrote:
Hi, Shreyas,
Writeups are so very much appreciated! Especially writeups that can
be integrated into the derby web site with a minimum of fuss.
For adding new content to the derby web site, the writeup below is
intended to help people test new content and also help committers
understand how to commit changes:
http://incubator.apache.org/derby/papers/derby_web.html
Currently the site uses forrest 0.6. The forrest project will release
0.7 soon, so I opened a task to upgrade it to 0.7 -- see
http://issues.apache.org/jira/browse/DERBY-188 -- but I haven't
started looking at 0.7 yet.
For improvements to the DITA doc source, see
http://incubator.apache.org/derby/manuals/dita.html .
regards,
-jean
Shreyas Kaushik wrote:
Hi all,
As people on this alias might know there was a thread running
where we discussed about debugging the test failures in the Derby
harness. I plan to do a write up of my learnings in the process (
also has some valuable suggestions from Kathey ).
I was wondering where to start? Things like,
~ How do I work with the new Apache Forrest ? Is it like adding
stuff another HTML document ?
~ Where to find the actaul docs source to start playing with ?
~ What section to put this write up in ?
~ How do I test my write up ? ( Formatting, font size..etc )
Any pointers on these would help. I hope this document will a be a
good one for beginners and people not so familiar with the Derby
test framework ( I am also learning ).
~ Shreyas
http://forrest.apache.org/dtd/document-v20.dtd";>
Debugging test failures with the Derby test framework
This document gives details of how to debug test failures. This is
targeted at developers who contribute code to Derby and would want to
investigate test failures caused by their fixes. lease post questions, comments,
and corrections to [EMAIL PROTECTED]
Introduction
The contents in this document are mostly inputs I received from Kathey Marsden
The Derby codebase has a slightly complicated test framework suite. Although using the
framewrok to run tests is very simple and the framework itself give extensive results of the
tests that passed and failed, it does get really tough to debug these test failures. The
following sections give a step by step insight into debugging test failure.
Steps to be followed
1. Frist the test/s have to be run. The details fro running the tests can be found at
${derby.source}/java/testing/README.htm.
The command for running the test/s would something like this,
java -Dij.exceptionTrace=true -Dkeepfiles=true org.apache.derbyTesting.functionTests.harness.RunTest lang/"mytest".sql
For the netwrok server you would need to add the following property -Dframework=DerbyNet
2. Do a visual diff of the ouptut with the canon.
It will give you more context in approaching the problem.
In the test output directory you will see "mytest".out (filitered test output) and
"mytest".tmp (unfiltered test output - this one should have a trace).
To get the most information, it is advised to diff the tmp file with the canon which is checked in under
java/testing/org/apache/derbyTesting/functionTests/master/ or appropriate framework or jdk subdirectory.
3. Identify the sql statement or java code causing the diff.
For sql scripts this is usually pretty obvious. For java programs you have to look at the test source to
figure out what is going on.
4. Evaluate the diff.
Here of course starts the tricky and interesting part.
Look carefully at the sql command or java code that caused the diff.
Think about what it should do and how it relates to your change.
[PATCH] Conversions to Date and Timestamp
The attached path contains some fixes to conversions to dates and
timestamps.
1. According to the Derby documentation the DATE and TIMESTAMP functions
provide some conversions beyond those handled by the normal casts. This
patch implements them. The DATE function converts a numeric argument to
a date by taking the integer portion of the number and considering it as
the number of days since Jan. 1 1970. The DATE function also handles a
string of length 7 in the format 'ddd'. It is taken to indicate the
ddd'th day of year . The TIMESTAMP function handles string arguments
of length 14 in the format 'MMddhhmmss'. Any other arguments to the
DATE and TIMESTAMP functions are handled as normal casts to date or
timestamp.
(The TIME function does not do any special conversions. It is the same
as the CAST function).
2. The string to time cast has been made more lenient by making the
seconds field optional.
Previously the DATE and unary TIMESTAMP functions were implemented by
converting them to calls to CAST. This was done in the parser. The patch
changes this. The parser now generates a UnaryDateTimestampOperatorNode
to represent these functions. The generated code calls new
DataValueFactory methods. Most of the actual work is done in the SQLDate
and SQLTimestamp classes.
The patch passed the derbyall suite.
Jack Klebanoff
Index: java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
===
--- java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
(revision 164436)
+++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
(working copy)
@@ -554,6 +554,9 @@
case C_NodeTypes.SAVEPOINT_NODE:
return C_NodeNames.SAVEPOINT_NODE_NAME;
+ case C_NodeTypes.UNARY_DATE_TIMESTAMP_OPERATOR_NODE:
+return C_NodeNames.UNARY_DATE_TIMESTAMP_OPERATOR_NODE_NAME;
+
case C_NodeTypes.TIMESTAMP_OPERATOR_NODE:
return C_NodeNames.TIMESTAMP_OPERATOR_NODE_NAME;
Index:
java/engine/org/apache/derby/impl/sql/compile/UnaryDateTimestampOperatorNode.java
===
---
java/engine/org/apache/derby/impl/sql/compile/UnaryDateTimestampOperatorNode.java
(revision 0)
+++
java/engine/org/apache/derby/impl/sql/compile/UnaryDateTimestampOperatorNode.java
(revision 0)
@@ -0,0 +1,196 @@
+/*
+
+ Derby - Class
org.apache.derby.impl.sql.compile.UnaryDateTimestampOperatorNode
+
+ Copyright 2004 The Apache Software Foundation or its licensors, as
applicable.
+
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+
+packageorg.apache.derby.impl.sql.compile;
+
+import org.apache.derby.iapi.types.DataValueFactory;
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.types.DateTimeDataValue;
+import org.apache.derby.iapi.services.compiler.MethodBuilder;
+import org.apache.derby.iapi.error.StandardException;
+
+import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+
+import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
+
+import org.apache.derby.iapi.reference.ClassName;
+import org.apache.derby.iapi.reference.SQLState;
+
+import org.apache.derby.iapi.services.classfile.VMOpcode;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+import java.sql.Types;
+
+import java.util.Vector;
+
+/**
+ * This class implements the timestamp( x) and date(x) functions.
+ *
+ * These two functions implement a few special cases of string conversions
beyond the normal string to
+ * date/timestamp casts.
+ */
+public class UnaryDateTimestampOperatorNode extends UnaryOperatorNode
+{
+private static final String TIMESTAMP_METHOD_NAME = "getTimestamp";
+private static final String DATE_METHOD_NAME = "getDate";
+
+/**
+ * @param operand The operand of the function
+ * @param targetType The type of the result. Timestamp or Date.
+ *
+* @exception StandardException Thrown on error
+*/
+
+ public void init( Object operand, Object targetType)
+ throws StandardException
+ {
+ setType( (DataTypeDescriptor) targetType);
+switch( getTypeServices().getJDBCTypeId())
+{
+case Types
Re: [jira] Assigned: (DERBY-12) Quoted table names mishandled in select list expansion
My mistake. It still has a status of "Open" in Jira. I will remove myself as owner. Whoever applied the patch should mark the bug as fixed. Jack Klebanoff Shreyas Kaushik wrote: I thought this also was patched? ~ Shreyas Jack Klebanoff (JIRA) wrote:
Re: [jira] Updated: (DERBY-243) connection toString should uniquely identify the connection
The simplest thing would be to use the java.lang.System.identityHashCode
method on the connection object. I believe that it is unique in a 32 bit
system. In practice it will almost always be unique in a 64 bit system.
I don't think that the Monitor or UUIDFactory classes are available in
the client.They are part of the Derby server.
Jack Klebanoff
||David Van Couvering wrote:
One thought I had about the UUID approach, after proposing it, is that
if you're trying to correlate connections and trace messages and you
have these huge long UUID strings, it can be a bit challenging.
If it were a simple static long that starts at 1 and increments each
time a new connection instance is created (yes, the increment would
have to be synchronized), then it would be much more readable. This
would also be more portable to the client code, and we wouldn't have
to cut/paste the UUID class to the client packages...
Any thoughts?
Thanks,
David
David Van Couvering wrote:
Hi, Kathey. Currently the connection classes don't appear to have a
unique identifier that could be made available in toString(). Do I
take it you would like me to find an approach that generates one?
I noticed Derby has a UUID service (very nice!). Is it OK if I use
that here to generate a UUID for the connection? If I don't hear
otherwise, I'll assume this approach is OK, e.g.
public class EmbedConnection
{
private UUID UUIDValue;
private String UUIDString;
public EmbedConnection()
{
UUIDFactory uuidFactory = Monitor.getMonitor().getUUIDFactory();
UUIDValue = uuidFactory.createUUID();
UUIDString = this.getClass().getName() + ":" + UUIDValue.toString();
...
}
public String toString()
{
UUIDString;
}
}
=
The connection classes I found are as follows. Please let me know if
I missed any. An indented class implies it extends the unindented
class above it.
EMBEDDED (org.apache.derby.engine.*)
BrokeredConnection (implements java.sql.Connection)
BrokeredConnection30
EmbedConnection (implements java.sql.Connection)
EmbedConnection30
EmbedPooledConnection (implements java.sql.PooledConnection)
EmbedXAConnection
CLIENT (org.apache.derby.client.*_
Connection (abstract class, implements java.sql.Connection))
NetConnection
NetXAConnection
ClientXAConnection (implements java.sql.XAConnection)
ClientPooledConnection (implements java.sql.PooledConnection)
LogicalConnection (implements java.sql.Connection)
On the client side, I first need to understand: is derbyclient.jar
supposed to be standalone (meaning it can't depend upon things in
derby.jar like the Monitor and the UUID class). If so, I suppose I
could cut/paste the BasicUUID class into the client packages for use
on the client side (shiver). Alternately we could have a
derbyutils.jar that is shared between client and server (Big Change,
not sure if I want to take that on). Advice here would be most
appreciated.
Thanks,
David
Kathey Marsden (JIRA) wrote:
[ http://issues.apache.org/jira/browse/DERBY-243?page=all ]
Kathey Marsden updated DERBY-243:
-
Summary: connection toString should uniquely identify the
connection (was: connection toString doesn't give enough information)
Description: The toString() on the Derby connection doesn't
print unique information.
for example System.out.println(conn) prints:
EmbedConnection in the case of derby embedded
It would be great if the toString() method for connections could be
used to differentiate one connection from another.
was:
The toString() on the Derby connection doesn't print unique
information.
for example System.out.println(conn) prints:
EmbedConnection in the case of derby embedded
I am not sure if XA Connections and Pooled Connections have the same
issue. I didn't immediately see an override of the toString()
method in BrokeredConnection.java like there is for EmbedConnection
connection toString should uniquely identify the connection
---
Key: DERBY-243
URL: http://issues.apache.org/jira/browse/DERBY-243
Project: Derby
Type: Improvement
Components: JDBC
Reporter: Kathey Marsden
Assignee: David Van Couvering
Priority: Trivial
Fix For: 10.0.2.1, 10.0.2.0, 10.0.2.2, 10.1.0.0
The toString() on the Derby connection doesn't print unique
information.
for example System.out.println(conn) prints:
EmbedConnection in the case of derby embedded
It would be great if the toString() method for connections could be
used to differentiate one connection from another.
Re: [jira] Updated: (DERBY-243) connection toString should uniquely identify the connection
If the Derby client absolutely requires that its Connection class
toString methods return unique, that is if the client would fail to work
if toString() were not unique, then we could toString could not use
identityHashCode. However, I do not believe that this is the case. I
think that uniqueness is very helpful but not required.
It is true that identityHashCode is not guaranteed to be unique.
However, in practice it almost always is unique.
My understanding is that toString is used for debugging. I don't think
that we should burden the production with debug code when we have
simpler methods that, in practice if not in theory, will work just fine.
Jack Klebanoff
David Van Couvering wrote:
Hm, I always thought a hash-code was not unique. I got excited when
you mentioned this method, identityHashCode(), which I hadn't heard
of, but it basically delegates to Object.hashCode(), it just ensures
that the hash code returned is the base Object hash code and not one
returned by an overriding method.
The documentattion for hashCode() says
"It is not required that if two objects are unequal according to the
equals(java.lang.Object) method, then calling the hashCode method on
each of the two objects must produce distinct integer results.
However, the programmer should be aware that producing distinct
integer results for unequal objects may improve the performance of
hashtables.
As much as is reasonably practical, the hashCode method defined by
class Object does return distinct integers for distinct objects. (This
is typically implemented by converting the internal address of the
object into an integer, but this implementation technique is not
required by the JavaTM programming language.)"
So, we can depend on it *most* of the time, but that makes me a bit
nervous...
David
Jack Klebanoff wrote:
The simplest thing would be to use the
java.lang.System.identityHashCode method on the connection object. I
believe that it is unique in a 32 bit system. In practice it will
almost always be unique in a 64 bit system.
I don't think that the Monitor or UUIDFactory classes are available
in the client.They are part of the Derby server.
Jack Klebanoff
||David Van Couvering wrote:
One thought I had about the UUID approach, after proposing it, is
that if you're trying to correlate connections and trace messages
and you have these huge long UUID strings, it can be a bit challenging.
If it were a simple static long that starts at 1 and increments each
time a new connection instance is created (yes, the increment would
have to be synchronized), then it would be much more readable. This
would also be more portable to the client code, and we wouldn't have
to cut/paste the UUID class to the client packages...
Any thoughts?
Thanks,
David
David Van Couvering wrote:
Hi, Kathey. Currently the connection classes don't appear to have
a unique identifier that could be made available in toString(). Do
I take it you would like me to find an approach that generates one?
I noticed Derby has a UUID service (very nice!). Is it OK if I use
that here to generate a UUID for the connection? If I don't hear
otherwise, I'll assume this approach is OK, e.g.
public class EmbedConnection
{
private UUID UUIDValue;
private String UUIDString;
public EmbedConnection()
{
UUIDFactory uuidFactory = Monitor.getMonitor().getUUIDFactory();
UUIDValue = uuidFactory.createUUID();
UUIDString = this.getClass().getName() + ":" +
UUIDValue.toString();
...
}
public String toString()
{
UUIDString;
}
}
=
The connection classes I found are as follows. Please let me know
if I missed any. An indented class implies it extends the
unindented class above it.
EMBEDDED (org.apache.derby.engine.*)
BrokeredConnection (implements java.sql.Connection)
BrokeredConnection30
EmbedConnection (implements java.sql.Connection)
EmbedConnection30
EmbedPooledConnection (implements java.sql.PooledConnection)
EmbedXAConnection
CLIENT (org.apache.derby.client.*_
Connection (abstract class, implements java.sql.Connection))
NetConnection
NetXAConnection
ClientXAConnection (implements java.sql.XAConnection)
ClientPooledConnection (implements java.sql.PooledConnection)
LogicalConnection (implements java.sql.Connection)
On the client side, I first need to understand: is derbyclient.jar
supposed to be standalone (meaning it can't depend upon things in
derby.jar like the Monitor and the UUID class). If so, I suppose I
could cut/paste the BasicUUID class into the client packages for
use on the client side (shiver). Alternately we could have a
derbyutils.jar that is shared between client and server (Big
Change, not sure if I want to take that on). Advice here would be
most appreciated.
Thanks,
David
Kathey Marsden (JIRA) wrote:
[ http://issues.apache.org/
[PATCH] Derby-127
I have attached a patch that fixes Jira bug Derby-127
(http://issues.apache.org/jira/browse/DERBY-127). The problem is with
select statements that use a correlation name in the select list, a
group by clause, and an order by clause that refers to a column by its
database name instead of its correlation name. e.g.
select c1 as x from t where ... group by x order by c1
Derby throws an exception with SQLState 42x04 complaining that it cannot
resolve "c1".
The underlying problem is that the Derby parser transforms the select
into a query tree for the following statement:
select * from (select c1 as x from t where ...) order by c1
The code in class OrderByColumn did not take this into account. I
changed methods pullUpOrderByColumn and bindOrderByColumn to handle this
case specially. pullUpOrderByColumn adds the sort key to the
ResultColumnList if it cannot find it there. It is called before binding
and before select list wildcards ("*") are expanded. I changed it to
pull the sort key into the ResultColumnList of the subselect generated
to handle GROUP BY. I also changed it to remember where it was added.
This simplifies the bindOrderByColumn, which is called after
pullUpOrderByColumn.
I also fixed the handling of table names in class OrderByColumn. It
treated them as strings, which does not work when the schema or table
name is a quoted string containing a period. I changed OrderByColumn to
use class TableName to represent a table name. The
ResultColumnList.getOrderByColumn methods where changed accordingly
Jack Klebanoff.
Index: java/engine/org/apache/derby/impl/sql/compile/TableName.java
===
--- java/engine/org/apache/derby/impl/sql/compile/TableName.java
(revision 168148)
+++ java/engine/org/apache/derby/impl/sql/compile/TableName.java
(working copy)
@@ -206,6 +206,9 @@
*/
public boolean equals(TableName otherTableName)
{
+if( otherTableName == null)
+return false;
+
String fullTableName = getFullTableName();
if (fullTableName == null)
{
Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
===
--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (revision
168148)
+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (working copy)
@@ -7305,6 +7305,12 @@
*
* RESOLVE - someday we should try to find matching
aggregates
* instead of just adding them.
+ *
+ * NOTE: This rewriting of the query tree makes the handling of an
ORDER BY
+ * clause difficult. See OrderByColumn.pullUpOrderByColumn. It
makes specific
+ * assumptions about the structure of the generated query tree. Do
not make
+ * any changes to this transformation without carefully
considering the
+ * OrderByColumn pullUpOrderByColumn and bindOrderByColumn methods.
*/
if (havingClause != null)
{
Index: java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
===
--- java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
(revision 168148)
+++ java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
(working copy)
@@ -30,6 +30,8 @@
import org.apache.derby.iapi.sql.compile.NodeFactory;
import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+import org.apache.derby.iapi.util.ReuseFactory;
+
/**
* An OrderByColumn is a column in the ORDER BY clause. An OrderByColumn
* can be ordered ascending or descending.
@@ -44,6 +46,12 @@
private ResultColumnresultCol;
private boolean ascending = true;
private ValueNode expression;
+/**
+ * If this sort key is added to the result column list then it is at
result column position
+ * 1 + resultColumnList.size() - resultColumnList.getOrderBySelect() +
addedColumnOffset
+ * If the sort key is already in the reault colum list then
addedColumnOffset < 0.
+ */
+private int addedColumnOffset = -1;
/**
@@ -161,31 +169,23 @@
}
}else{
- ResultColumnList targetCols = target.getResultColumns();
- ResultColumn col = null;
- int i = 1;
-
- for(i = 1;
- i <= targetCols.size();
- i ++){
-
- col = targetCols.getOrderByColumn(i);
- if(col != null &&
-
Re: [PATCH] Derby-127
Army wrote: Jack Klebanoff wrote: I have attached a patch that fixes Jira bug Derby-127 (http://issues.apache.org/jira/browse/DERBY-127). I reviewed this patch, applied it to a clean codeline without problem, and ran the orderby.sql test that was included. From what I can tell, everything looks good here. My only minor comment is that it might be nice to add a case to the orderby.sql test to make sure things work if _multiple_ columns are provided in an order by clause. Ex. ij> select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by c1, c2; ij> select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by c1, y; I tried these and they both work fine--no problems there. But it was something I was wondering while I was reviewing, so it might be nice to include it in the test...*shrug* In any event, the patch gets my +1, Army I agree with Army's suggestion for an extra test. Should I wait for my original patch to be committed and submit the new test in a new patch, or should I revise and re-submit my patch? Since Army has shown that the patch does handle his test case I suggest committing my current patch first. This will get things moving. Adding a few cases to an existing test results in a small, easily reviewed patch. If I revise the submitted patch it will be more difficult to review the submission. Jack Klebanoff
Re: [PATCH] (DERBY-251) DISTINCT query is returning duplicate rows
The patch does not completely fix the problem. It does not handle the
case where the exists table column is embedded in an expression. Try the
following variation on the test select:
select distinct q1."NO1" from IDEPT q1, IDEPT q2
where ( q2."DISCRIM_DEPT" = 'HardwareDept')
and ( q1."DISCRIM_DEPT" = 'SoftwareDept') and ( q1."NO1" <> ALL
(select q3."NO1" from IDEPT q3 where ( ABS(q3."REPORTTO_NO") = q2."NO1")))
Because q3."REPORTTO_NO" is inside a call to ABS the code added to
FromList.returnsAtMostSingleRow does not see it.
I would suggest using
JBitSet referencedTables =
and.getLeftOperand().getTablesReferenced();
if( referencedTables.get( existsTableNumber))
{
predicatesTemp.removeElementAt(predicatesTempIndex);
break;
}
instead of
BinaryRelationalOperatorNode beon =
(BinaryRelationalOperatorNode)
and.getLeftOperand();
ValueNode left = beon.getLeftOperand();
ValueNode right = beon.getRightOperand();
/* If left or right side of predicate refer to
exists base table,
then remove it */
if ((left instanceof ColumnReference) &&
((ColumnReference) left).getTableNumber() ==
existsTableNumber)
{
predicatesTemp.removeElementAt(predicatesTempIndex);
break;
}
else if ((right instanceof ColumnReference) &&
((ColumnReference) right).getTableNumber()
== existsTableNumber)
{
predicatesTemp.removeElementAt(predicatesTempIndex);
break;
}
I have tried it out and it seems to work.
Jack Klebanoff
Mamta Satoor wrote:
Hi,
I have a patch for this optimizer bug. Basically, the issue turned out
to be the logic for DISTINCT elimination. During the optimization
phase, if a query has DISTINCT clause, then impl.sql.compile.FromList
class's returnsAtMostSingleRow() method gets called. This method
returns true if the method concludes that DISTINCT in the query is
redundant (based on a complex logic that decides that the query is
going to return distinct rows on its own without the DISTINCT clause.
The details of the current logic for DISTINCT elimination can be found
in the comments at the method level.)
For the query in question in this bug, the method returned true for
DISTINCT elimination which is wrong. The explanation is as follows.
First of all, I was able to simplify the query reported in the bug to
following query.
select distinct q1."NO1" from IDEPT q1, IDEPT q2
where ( q2."DISCRIM_DEPT" = 'HardwareDept')
and ( q1."DISCRIM_DEPT" = 'SoftwareDept') and ( q1."NO1" <> ALL
(select q3."NO1" from IDEPT q3 where (q3."REPORTTO_NO" = q2."NO1")))
This query gets converted to following during optimization
select distinct q1."NO1" from IDEPT q1, IDEPT q2
where ( q2."DISCRIM_DEPT" = 'HardwareDept')
and ( q1."DISCRIM_DEPT" = 'SoftwareDept') and not exists (
(select q3."NO1" from IDEPT q3 where
( q3."REPORTTO_NO" = q2."NO1" and q3."NO1" = q1."NO1") ) ) ;
This optimized query has 4 predicates associated with it
q3.reportto_no = q2.no1
q2.discrim_dept = 'HardwareDept'
q1.descrim_dept = 'SoftwareDept'
q1.no1 = q3.no1
Next, on this optimized query(since it has DISTINCT clause in it), the
returnsAtMostSingleRow() method gets called. The method incorrectly
returns true indicating that DISTINCT can be eliminated. The reason
for this is that method is looking at predicates that belong to the
inside query with the exists clause (which is on table IDEPT q3) to
determine DISTINCT elimination for the outer level.
The fix is that the predicates from the exists query, (in this
particular case, q3."NO1" = q1."NO1" and q3.reportto_no = q2.no1)
should not be considered when deciding elimination of DISTINCT in the
outer query. That is what the attached patch does.
Hope this helps understand the problem and the proposed fix for it.
The files impacted by the change are as follows
svn stat
M java\engine\org\apache\derby\impl\sql\compile\FromList.java
M
java\testing\org\apache\derbyTesting\functionTests\tests\lang\distinctElimination.sql
M
java\testing\org\apache\derbyTesting\functionTests\master\distinctElimination.out
Please send in comments you may have. I have run the existing tests
and the patch didn't cause any failures.
thanks,
Mamta
Re: [PATCH] (DERBY-251) DISTINCT query is returning duplicate rows
+1
The revised patch looks OK to me. I think that it should be submitted if
derbyall passes. Probably derbylang is enough.
Jack Klebanoff
Mamta Satoor wrote:
Hi Jack,
Appreciate you taking the time to do the review and catching the
predicate with expression. I have changed the code and also added a
test case for it. I have fired the derbyall suite on my codeline to
make sure everything else runs smoothly. Attached is the updated patch
anyways.
thanks,
Mamta
On 5/11/05, Jack Klebanoff <[EMAIL PROTECTED]> wrote:
The patch does not completely fix the problem. It does not handle the
case where the exists table column is embedded in an expression. Try the
following variation on the test select:
select distinct q1."NO1" from IDEPT q1, IDEPT q2
where ( q2."DISCRIM_DEPT" = 'HardwareDept')
and ( q1."DISCRIM_DEPT" = 'SoftwareDept') and ( q1."NO1" <> ALL
(select q3."NO1" from IDEPT q3 where ( ABS(q3."REPORTTO_NO") = q2."NO1")))
Because q3."REPORTTO_NO" is inside a call to ABS the code added to
FromList.returnsAtMostSingleRow does not see it.
I would suggest using
JBitSet referencedTables =
and.getLeftOperand().getTablesReferenced();
if( referencedTables.get( existsTableNumber))
{
predicatesTemp.removeElementAt(predicatesTempIndex);
break;
}
instead of
BinaryRelationalOperatorNode beon =
(BinaryRelationalOperatorNode)
and.getLeftOperand();
ValueNode left = beon.getLeftOperand();
ValueNode right = beon.getRightOperand();
/* If left or right side of predicate refer to
exists base table,
then remove it */
if ((left instanceof ColumnReference) &&
((ColumnReference) left).getTableNumber() ==
existsTableNumber)
{
predicatesTemp.removeElementAt(predicatesTempIndex);
break;
}
else if ((right instanceof ColumnReference) &&
((ColumnReference) right).getTableNumber()
== existsTableNumber)
{
predicatesTemp.removeElementAt(predicatesTempIndex);
break;
}
I have tried it out and it seems to work.
Jack Klebanoff
Re: Strange behaviour when combining column alias and group by
Bernt M. Johnsen wrote: I stumbeled across this strange behaviour when combining coumn alias and group by: ij> select * from tt; I |J --- 1 |2 2 |3 1 |2 2 |3 2 |3 5 rows selected ij> select i, count(*) as cnt from tt group by i; I |CNT --- 1 |2 2 |3 2 rows selected ij> select i, count(*) as i from tt group by i; I |I --- 1 |1 2 |2 2 rows selected The last select is obviously wrong! This might be related to DERBY-127, but if it's not I'll file it asa separate issue. I think that this is a different issue than Derby-127. Derby-127 concerned the combination of group by with order by. The two are probably related. Please file a separate Jira-report for this one. Jack Klebanoff
Re: [PATCH] Timestamp Arithmetic
Jeff Levitt wrote: If this gets committed, I'd like to place these new functions in the docs. Would they go in the built-in functions section of the Reference Manual? The reference manual has a "JDBC Escape Syntax" section. These functions belong there since they are JDBC escape functions. However, readers looking for datetime arithmetic support may not think of looking there. Perhaps we need some cross-references in the DATE, TIME, and TIMESTAMP datatype sections and in the built-in function section.
Re: [PATCH] Timestamp Arithmetic
Daniel John Debrunner wrote: Jack, could you provide some reasoning why your new TimestampArithmeticFnNode node doesn't fall into the existing heirachy for arithmetic operators, namely BinaryArithmeticOperatorNode? I would like to see Derby consolidate more compilation nodes, rather than create new ones. New nodes increase the static and runtime footprint, but don't usually add much value if the logic could be in an exisiting node. +public class TimestampArithmeticFnNode extends ValueNode Dan. The timestamp escape functions are ternary functions, not binary operators. I looked at trying to consolidate them with the binary or unary operator nodes, but there was not that much commonality. It was an awkard fit. Jack
Re: [PATCH] Timestamp Arithmetic
Daniel John Debrunner wrote:
Jack wrote ...
A few items in your patch & description gave me pause for thought ...
If a date is used in ts1 or ts2 it is converted to a timestamp by using time 00:00:00.
If a time is used in ts1 or ts2 it is converted to a timestamp by using the current date.
a) the lack of symmetry in these statements, current date but not
current time
My copy of the JDBC 3.0 spec does not say what TIMESTAMPADD and
TIMESTAMPDIFF are supposed to do when the input is a date or time.
However the ODBC spec on Microsoft's web site specifies the above
behavior. My understanding is that JDBC follows ODBC here.
b) what did you mean by current time, as there is "SQL current
timestamp" and "real time now"? I see you implemented as real time. [SQL
has the CURRENT_TIMESTAMP fixed for the lifetime of a statement]
c) current date in which timezone?
[I see that Derby uses this rule for casting from a TIME to a TIMESTAMP,
and the manuals state CURRENT_DATE, which would be fixed for the
lifetime of the statement.]
Good point. I will look into changing the time to timestamp conversion
to use the same logic as CURRENT_DATE.
a) means that TIMESTAMPADD() can return different values for a given
TIME argument value at different times, e.g. TIME + 1 DAY will depend on
which day the function is executed.
That combined with b) means that within a single query TIMESTAMPADD()
can return different values for a given TIME argument value, i.e. if a
query's execution spans midnight.
That behaviour for a SQL function just seems wrong.
Then the naming of the new methods in DateTimeDataValue seemed wrong,
I'm a great believer in ensuring such items and method names correctly
reflect their purpose. This makes the code more readable.
So we have
+DateTimeDataValue timestampAdd( int intervalType, NumberDataValue intervalCount, DateTimeDataValue resultHolder)
+throws StandardException;
If I have a expression equivalent to TIME + 1 MIN, then I'm not adding a
timestamp, I'm adding one minute to the current value of the
DateTimeDataValue object. This is an interface method, declaration of
behaviour, not implementation.
Similar for
+NumberDataValue timestampDiff( int intervalType, DateTimeDataValue time1, NumberDataValue resultHolder)
+throws StandardException;
If I'm diffing two TIME values and returning interval in minutes then no
timestamp is involved.
It seems a more correct reflection of their names would be intervalAdd
and intervalDiff.
The methods implement the TIMESTAMPADD and TIMESTAMPDIFF functions, so
their names seem appropriate to me.
So all this thinking got me to the key point is I think there are
combinations of interval and SQL types that should not be allowed.
- Since SQL TIME is not associated with a date then I believe that
adding or diffing any date related intervals should not be allowed.
- Since SQL DATE is not associated with a time then I believe that
adding or diffing any time related intervals should not be allowed.
- And diffing TIME against a DATE etc. should not be allowed.
[this matches the existing documentation that says different date time
types cannot be mixed in an expression]
So with JDBC escape functions equivalent to these expressions
TIME + 1 DAY // not allowed
DATE + 1 MIN // not allowed
DATE - TIME // not allowed
Of course this leads to either (and similar for DATE)
{fn TIMESTAMPADD( interval, count, )}
returns a TIME value, or is not supported altogether.
I think that this is a reasonable argument, but I think that we should
follow the JDBC/ODBC standard, warts and all.
Dan.
Jack
Re: [PATCH] Timestamp Arithmetic
I thought that I read somewhere that TIMESTAMPADD was supposed to take
the integer part of its count argument. I think that I was wrong. The
Microsoft ODBC documentation says "Arguments denoted as /integer_exp/
can be the name of a column, the result of another scalar function, or a
/numeric-literal/, where the underlying data type can be represented as
SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, or SQL_BIGINT". I will change
the timestampAddBind method to check that the count argument is an
integer type.
Jack
Satheesh Bandaram wrote:
TimestampAdd seems to allow adding non-integer intervals, but the
behavior doesn't seem right. Either the result should be "10:10:20.9"
or the statement should error. Though JDBC documenation is not very
clear, I thought the "count" intervals need to be an integer?
ij> values {fn timestampadd(SQL_TSI_SECOND, 10.9, time('10:10:10'))};
1
--
2005-05-18 10:10:20.0
If we only allow integers for count, then the following needs to change:
+if( ! bindParameter( rightOperand, Types.INTEGER))
+{
+if( ! rightOperand.getTypeId().isNumericTypeId())
+throw
StandardException.newException(SQLState.LANG_INVALID_FUNCTION_ARG_TYPE,
+
rightOperand.getTypeId().getSQLTypeName(),
+
ReuseFactory.getInteger(2),
+ operator);
+}
Satheesh
Re: Regression: .fail:store/backupRestore1.java
Ole Solberg - Sun Norway wrote: In my tests runs I see that store/backupRestore1.java has failed in suites derbyall, storeall and storemore since ~revision 171143 (2005-05-22 20:56:57 CEST). See http://www.multinet.no/~solberg/public/Apache/Derby/index.html (or via http://incubator.apache.org/derby/derby_downloads.html#How+to+test+Derby) Anyone else seeing this or is it only here?. If it is a genuine problem I will file a JIRA issue. It is a genuine problem. Jack Klebanoff
Re: All of derby_all fails when environment corresponding derbyLocale_**.jar exists in CLASSPATH
Øystein Grøvlen wrote: "DJD" == Daniel John Debrunner <[EMAIL PROTECTED]> writes: DJD> I think the test suite has to have a known environment to allow it to DJD> run successfully on widely different machines. Examples of this known DJD> environment are the expected locale, character set encoding for the DJD> scripts etc. Ideally the test harness should set this environment itself. Asa long as you have a test harness that is based on evaluating the output to file from each test program, I think you are right. However, I think it should be possible to build a locale independent test harness in JUnit by instead evaluating the return values from API calls (e.g., message ids instead of message strings). Error messages are important. We should test them. Remember that the messages are generated by substituting argument values into strings fetched from the messages files. There may be bugs in the message generation system, more often Derby programmers make mistakes in messages and arguments. So if a test just looks at the message IDs and not at the message strings we will miss some bugs. Jack Klebanoff
Re: [PATCH] Referential Constraints
Andrew McIntyre wrote: On 6/1/05, Jack Klebanoff <[EMAIL PROTECTED]> wrote: The attached patch fixes a problem that Derby had with conflicting referential constraints. Is there a JIRA entry for this issue? I couldn't seem to find one. If there's not already one, could you please enter a new one? Thanks, andrew There is no Jira entry for this that I know of. Jack
Re: [PATCH] Referential Constraints
Daniel John Debrunner wrote: Jack wrote: [snip] The patch changes iapi/sql/dictionary/DDUtils.java to remove the DDL time checks [snip] I think all those checks need to remain for soft upgrade mode, otherwise your changes allow referential actions to be created that will not be handled correctly by 10.0 engines. The runtime code could probably remain as-is, in soft upgrade mode it would just handle cases that wouldn't exist. So only the DDL code would need checkVersion() calls. Dan. I have modified my patch to incorporate Dan's request. The new patch looks at the database version and applies the V10.0 checks during referential constraint DDL if the database is V10.0 (soft upgrade). The new patch is attached to Derby-338 (http://issues.apache.org/jira/browse/DERBY-338). Jack
Re: [jira] Updated: (DERBY-338) Move checks for referential constraints from DDL execution time to DML execution time
Daniel John Debrunner wrote: Jack Klebanoff (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-338?page=all ] Jack Klebanoff updated DERBY-338: - Attachment: refConstraint2005-06-07.diff [PATCH] This patch supersedes my previous patch. It incorporates Dan's request that Derby behave as it did in V10.0 on databases that have been soft upgraded from V10.0. That is, if the database is a V10.0 database then Derby 10.1 will not allow the creation of referential constraints with potentially conflicting actions. Derby 10.0 did not allow creation of such constraints and is not prepared to handle the case where one referential constraint action requires that a column be set to null while another referential constraint action requires that the row be deleted. Is the hash table you build at execution time for the affected rows always built, or only if there is a potential for conflicting actions? Dan. The hash table is built if there are dependencies and at least one of of them calls for a delete. Jack
Re: [jira] Updated: (DERBY-278) Document INTERSECT and EXCEPT operations
Jeff Levitt (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-278?page=all ] Jeff Levitt updated DERBY-278: -- Attachment: intersectdocsupdate.zip The new zip file attached (intersectdocsupdate.zip) includes a new patch incorporating all of Jack's feedback. It also includes new html output files for review. Please let me know if you would like any more changes, and if there are no further changes from anyone, can a committer please commit this? Thanks! I did a quick scan. It looked OK to me. Jack
Re: [PATCH] Referential Constraints
Suresh Thalamati wrote: Jack Klebanoff wrote: The attached patch fixes a problem that Derby had with conflicting referential constraints. Consider the following DDL: create table t2( ref1 int references t1(id) on delete cascade, ref2 int references t1(id) on delete set null) If both the ref1 and ref2 columns of the same t2 row refer to the same t1 row and that t1 row is deleted then the two referential constraint actions conflict. One says that the t2 row should be deleted, the other says that the ref2 column should be set to null. According to the SQL2003 spec an exception should be thrown when the t1 row is deleted. That is what Derby does after the attached patch is applied. Hi Jack, What is the SQL 2003 take on conflicting delete actions like CASCADE , RESTRICT , NOACTION. For example : create table t1(a int not null unique, b int not null unique); create table t2(x int references t1(a) ON DELETE CASCADE, y int references t1(b) ON DELETE RESTRICT); insert into t1 values(1 , 1) ; insert into t2 values(1, 1) ; What error should be thrown on following statement execution ? delete from t1 ; Thanks -suresht According to my copy of the SQL2003 spec "then an exception condition is raised: triggered data change violation". Thanks for your question. I realized that I am not using the SQLState specified by SQL2003. I will make a new patch. Jack .
Re: [jira] Commented: (DERBY-355) Document TIMESTAMPADD and TIMESTAMPDIFF functions
Jeff Levitt wrote: --- "Jack Klebanoff (JIRA)" wrote: [ http://issues.apache.org/jira/browse/DERBY-355?page=comments#action_12313548 ] Jack Klebanoff commented on DERBY-355: -- The TIMESTAMPADD and TIMESTAMPDIFF functions are JDBC escape functions, they are not like the other functions in the list of built-ins and they do not have equivalent built-in functions. It is probably good to document TIMESTAMPADD and TIMESTAMPDIFF in the list of built-in functions, so that users can find them, but the documentation must stress that they are only accessible using the JDBC escape function syntax. Perhaps we should put an asterisk beside them in the list of built-in functions. What if we put a note in each of the function topics mentioning this? I hesitate to put in an asterisk on each of the function titles, since it may be confused as syntax. Would that work? I think that that would work. Another possibility is to use some symbol that is not used by SQL instead of an asterisk. Jack
Re: [jira] Commented: (DERBY-310) Document and/or change Derby client code to match behavior with Embedded driver where possible.
David Van Couvering (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-310?page=comments#action_12313949 ] David Van Couvering commented on DERBY-310: --- Sorry I didn't respond sooner.. JACK SAID In regards to a separate section, this document has been placed as a subsection to a larger section called [snip] JACK SAID - Of the other items linked to this issue, both were unsettled. I wanted to at least get the differences about updatable result sets into the docs. [snip] Did you mean "Jeff said"? I don't recall saying any of those things, nor do I recall any other Jacks on this list. Jack
Re: PLEASE RESPOND: RSVP for Derby lunch during JavaOne
I will come. Jack Klebanoff
Trigger Bug Fix
I would like to submit a fix for a bug in triggers.
The bug is that a trigger of the form:
create trigger ... values myFunction();
has no effect. MyFunction is not called even if the trigger is fired.
Side effects of myFunction do not happen. Derby does not allow a "CALL"
statement inside a trigger action so a values statement is the only way
to call a function/procedure in a trigger action.
The cause of the bug is that since the values are not actually used by
the trigger, the trigger code does not fetch the row(s) returned by the
trigger action. The fix is simple: change class
org.apache.derby.impl.sql.execute.GenericTriggerExecutor to fetch (and
discard) the rows returned by a trigger action.
Please review the change. The diff file is attached.
I am an IBM employee and I have worked on Cloudscape for several years.
However I am not a Derby committer. This is my first submission. I am
learning the process.
Jack Klebanoff
Index: java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
===
--- java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
(revision 37092)
+++ java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
(working copy)
@@ -157,7 +157,17 @@
*/
try
{
- ps.execute(spsActivation, false, false, false);
+ ResultSet rs = ps.execute(spsActivation, false,
false, false);
+if( rs.returnsRows())
+{
+// Fetch all the data to ensure that functions in the
select list or values statement will
+// be evaluated and side effects will happen. Why else
would the trigger action return
+// rows, but for side effects?
+// The result set was opened in ps.execute()
+while( rs.getNextRow() != null)
+{
+}
+}
}
catch (StandardException e)
{
Re: Trigger Bug Fix
Daniel John Debrunner wrote:
Jack Klebanoff wrote:
| I would like to submit a fix for a bug in triggers.
|
| The bug is that a trigger of the form:
| create trigger ... values myFunction();
| has no effect. MyFunction is not called even if the trigger is fired.
| Side effects of myFunction do not happen. Derby does not allow a "CALL"
| statement inside a trigger action so a values statement is the only way
| to call a function/procedure in a trigger action.
|
| The cause of the bug is that since the values are not actually used by
| the trigger, the trigger code does not fetch the row(s) returned by the
| trigger action. The fix is simple: change class
| org.apache.derby.impl.sql.execute.GenericTriggerExecutor to fetch (and
| discard) the rows returned by a trigger action.
|
| Please review the change. The diff file is attached.
I think you need to close the ResultSet (rs). Other locations in the
code where a ResultSet is processed terminate with an rs.close(). Eg.
see DeleteCascadeResultSet, ConstraintConstantAction.
Dan.
I changed the code to close the ResultSet. The diff file is attached.
Jack
Index: java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
===
--- java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
(revision 37092)
+++ java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
(working copy)
@@ -157,7 +157,18 @@
*/
try
{
- ps.execute(spsActivation, false, false, false);
+ ResultSet rs = ps.execute(spsActivation, false,
false, false);
+if( rs.returnsRows())
+{
+// Fetch all the data to ensure that functions in the
select list or values statement will
+// be evaluated and side effects will happen. Why else
would the trigger action return
+// rows, but for side effects?
+// The result set was opened in ps.execute()
+while( rs.getNextRow() != null)
+{
+}
+}
+rs.close();
}
catch (StandardException e)
{
[PATCH] Extension Packaging
I am an IBM employee working on extensions to Derby. I would like to change Derby to simplify the packaging of extensions. An example of a potential extension is RAM based storage. There are several goals for the packaging mechanism. 1. It should be simple for a user/DBA to set up. Ideally all that should be required is to plunk the jar file containing the extension into the Java class path. 2. It should be possible to release extensions independently of base Derby releases, unless base Derby interfaces used by the extension change. The user should be able to use new Derby versions/fixes without updating the extension jar. This largely precludes combining the extension classes and resources with the base Derby classes and resources in one jar. 3. The user should be able to select any number of extensions. This also precludes combining the extension classes and resources with the base Derby classes and resources in one jar. 4. The footprint should be small. The packaging solution proposed in this paper meets the first three goals when there is no security manager. When a security manager is used set up is not quite so simple: the security manager must configured to permit the base Derby to read the extension jar files. I think that this is unavoidable. The packaging does a reasonable, though perhaps not optimal, job of keeping the footprint small. The top level extension classes are loaded before they are used and kept in the JVM even if the extension is not used. It might be possible to remedy this with some work on the Monitor. Under this packaging scheme an extension's compiled classes and a properties file named "org/apache/derby/modules.properties" are packaged in a jar file. The modules.properties file describes the extension to the Derby monitor. When Derby starts up it reads all the modules.properties files in its class path and uses the merged properties lists as the description of the modules implementing the system. The properties come in two flavors. The first is of the form: derby.module./extension-name/./id/=/module-class-name/ where /extension-name/ is the name of the extension, /id/ is a suffix that makes the property name unique, and /module-class-name/ is the name of a class that implements a module. The monitor will use the named class as a candidate implementation when it searches for factory implementations. See the javadoc for class org.apache.derby.iapi.services.monitor.Monitor. The second property flavor describes the implementation of a sub-sub-protocol. It is of the form: derby.subSubProtocol./subSubProtocol-name/=/class-name/ where /subSubProtocol-name/ is the name of a sub-sub-protocol and /class-name/ is the name of a class that implements either the org.apache.derby.iapi.services.monitor.PersistentService interface or the org.apache.derby.io.StorageFactory interface. For instance, suppose that you implemented RAM storage with class com.mycom.ramStorageImpl. You would provide a modules.properties file with the line "derby.subSubProtocol.ram=com.mycom.ramStorageImpl". Then databases opened with URLs starting with "jdbc:derby:ram:" would use com.mycom.ramStorageImpl to implement their storage. Currently the monitor reads one monitor.properties file which is provided in the derby jar file. It understands the first flavor of property. Currently sub-sub-protocol implementations are built in or come from system properties. This patch makes three changes. 1. The monitor is changed to read multiple module.properties files, using method ClassLoader.getSystemResources. 2. The monitor reads sub-sub-protocol properties from the modules.properties files, instead of just the system properties. 3. Sub-sub-protocol property values can name either a StorageFactory or a PersistentService implementation. Previously it could only name a StorageFactory implementation. svn diff: Index: java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java === --- java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java (revision 46949) +++ java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java (working copy) @@ -43,6 +43,7 @@ import org.apache.derby.iapi.services.io.FormatableInstanceGetter; import org.apache.derby.iapi.error.ExceptionSeverity; +import org.apache.derby.io.StorageFactory; import org.apache.derby.iapi.services.context.ErrorStringBuilder; @@ -80,6 +81,8 @@ import java.security.PrivilegedExceptionAction; import java.security.PrivilegedActionException; +import java.net.URL; + /** Implementation of the monitor that uses the class loader that the its was loaded in for all class loading. @@ -1052,8 +1055,6 @@ Vector implementations = actualModuleList ? new Vector(moduleList.size()) : new Vector(0,1); - Class persistentServiceClass = PersistentService.class; -
Re: release status as of 9/28/04
There is one bug fix that was not applied. Being new to Apache I did not
include "[PATCH]" in the subject.
My original email is:
| I would like to submit a fix for a bug in triggers.
|
| The bug is that a trigger of the form:
| create trigger ... values myFunction();
| has no effect. MyFunction is not called even if the trigger is fired.
| Side effects of myFunction do not happen. Derby does not allow a "CALL"
| statement inside a trigger action so a values statement is the only way
| to call a function/procedure in a trigger action.
|
| The cause of the bug is that since the values are not actually used by
| the trigger, the trigger code does not fetch the row(s) returned by the
| trigger action. The fix is simple: change class
| org.apache.derby.impl.sql.execute.GenericTriggerExecutor to fetch (and
| discard) the rows returned by a trigger action.
|
| Please review the change. The diff file is attached.
Index: java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
===
--- java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java (revision 37092)
+++ java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java (working copy)
@@ -157,7 +157,18 @@
*/
try
{
-ps.execute(spsActivation, false, false, false);
+ResultSet rs = ps.execute(spsActivation, false, false, false);
+if( rs.returnsRows())
+{
+// Fetch all the data to ensure that functions in the select list or values statement will
+// be evaluated and side effects will happen. Why else would the trigger action return
+// rows, but for side effects?
+// The result set was opened in ps.execute()
+while( rs.getNextRow() != null)
+{
+}
+}
+rs.close();
}
catch (StandardException e)
{
===
Samuel Andrew McIntyre wrote:
Hello all,
Here's a summary of the movement on the various issues concerning
getting a first Derby release out there.
1 - IP/copyright concerns - It appears this has ballooned into a more
serious issue than previously thought. Discussion of the proper way to
handle the transition of the code to the ASF license and in what way
IBM copyright is retained, if any, is occurring at
[EMAIL PROTECTED] I am sure we will hear from the Incubator PMC as
soon as this issue is settled.
2 - Apply all currently pending bug fixes. I have the following list
of submitted patches:
[APPLIED] [PATCH] Optimization of
org.apache.derby.impl.services.uuid.BasicUUID.toByteArray()
[APPLIED] [PATCH] Set Derby's build number to be the subversion
revision number
[PATCH] derby.war file build
[PATCH] Fix to prevent empty log file switches that could cause
recovery failures
[PATCH] minor bugs in dblook
[PATCH] Extension Packaging
[PATCH] retrieveMessages... true by default in ij
[PATCH] Network servlet display only message key
[PATCH] added 3 more parser generated files to the clobber target in
main build.xml
[PATCH] Various fixes to javadoc generation
Please let me know if there are any I am missing here. Note that the
change copyright to ASF patch is not included here because of issue #1.
3 - Now that we have bug tracking, please speak up if you consider any
of the currently logged bugs as a showstopper for the release.
4 - Please comment on what the content and form of the release should
be. I think jars, javadoc and HTML documentation should be included,
since that is what we have at the moment, packaged as a zip and tar.gz
file. Please speak up if you are interested in rpms for this first
release.
5 - Consensus on Derby's upgrade policy/version scheme. There is
currently a vote pending in the "[VOTE] Derby upgrade policy" thread.
Please post other concerns if you have them.
Thanks,
andrew
Re: [PATCH] derby.log file error message
Jonas S Karlsson wrote: A small "cosmetic" patch for derby. When ij/derby is started it creates the file derby.log and this file contains an error message as follows: 2004-09-28 23:11:50.428 GMT Thread[main,5,main] Class com.ibm.derby.impl.BasicServices.TestService.Basic.BasicUnitTestManager java.lang.ClassNotFoundException: com.ibm.derby.impl.BasicServices.TestService.Basic.BasicUnitTestManager, module ignored. No class com.ibm.derby... exists so it seems to be a misnomer. The message doesn't seem to cause any other errors but it doesn't look good in the log file. So here is a small patch: -- begin patch --- Index: java/engine/org/apache/derby/modules.properties === --- java/engine/org/apache/derby/modules.properties (revision 47434) +++ java/engine/org/apache/derby/modules.properties (working copy) @@ -123,7 +123,6 @@ derby.module.lockManager=org.apache.derby.impl.services.locks.SinglePool cloudscape.config.lockManager=all -derby.module.testManager=com.ibm.derby.impl.BasicServices.TestService.Basic.BasicUnitTestManager cloudscape.config.testManager=none derby.module.classManagerJ2=org.apache.derby.impl.services.reflect.ReflectClassesJava2 -- end patch --- BTW, this file is full of "cloudscape.config" strings, my guess is that this too should be fixed somewhere and somehow, or removed. /Jonas The "cloudscape.config.testManager=none" line should also be removed. It is used to describe the configurations that should contain the corresponding derby.module.testManager module. Since the derby.module.testManager property is being removed the cloudscape.config.testManager property should also be removed; it is meaningless without the derby.module.testManager property. Jack Klebanoff
[PATCH] ExternalSortFactory Bug Fix
Class org.apache.derby.impl.store.access.sort.ExternalSortFactory has a
latent bug. Its canSupport method throws an exception if it is called
with a null startParams argument. The patch follows.
Jack Klebanoff
Index: ExternalSortFactory.java
===
--- ExternalSortFactory.java(revision 47313)
+++ ExternalSortFactory.java(working copy)
@@ -322,6 +322,8 @@
public boolean canSupport(Properties startParams) {
+if( startParams == null)
+return false;
String impl =
startParams.getProperty("derby.access.Conglomerate.type");
if (impl == null)
return false;
Re: svn commit: rev 53834 - incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort
[EMAIL PROTECTED] wrote:
Author: mikem
Date: Tue Oct 5 16:43:37 2004
New Revision: 53834
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/ExternalSortFactory.java
Log:
committing for [EMAIL PROTECTED]
Class org.apache.derby.impl.store.access.sort.ExternalSortFactory has a latent
bug. Its canSupport method throws an exception if it is called with a null
startParams argument.
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/ExternalSortFactory.java
==
---
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/ExternalSortFactory.java
(original)
+++
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/ExternalSortFactory.java
Tue Oct 5 16:43:37 2004
@@ -322,6 +322,9 @@
public boolean canSupport(Properties startParams) {
+if (startParams == null)
+return false;
+
String impl = startParams.getProperty("derby.access.Conglomerate.type");
if (impl == null)
return false;
It looks like the patch program did not do what you wanted. The source
that I got from Subversion contains:
<<< .mine
if( startParams == null)
return false;
===
if (startParams == null)
return false;
>>> .r53839
The Java compiler does not like "<<< .mine", etc. :-)
Jack
Re: svn commit: rev 53834 - incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort
I apologize. The patch checked into Subversion is correct. I am new to
Subversion. I mistook conflict markers inserted by Subversion for the
real contents of a file. (Svn update was run by a script, so I did not
realize what happened).
Jack
Jack Klebanoff wrote:
[EMAIL PROTECTED] wrote:
Author: mikem
Date: Tue Oct 5 16:43:37 2004
New Revision: 53834
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/ExternalSortFactory.java
Log:
committing for [EMAIL PROTECTED]
Class org.apache.derby.impl.store.access.sort.ExternalSortFactory has
a latent bug. Its canSupport method throws an exception if it is
called with a null startParams argument.
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/ExternalSortFactory.java
==
---
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/ExternalSortFactory.java
(original)
+++
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/sort/ExternalSortFactory.java
Tue Oct 5 16:43:37 2004
@@ -322,6 +322,9 @@
public boolean canSupport(Properties startParams) {
+if (startParams == null)
+return false; +
String impl =
startParams.getProperty("derby.access.Conglomerate.type");
if (impl == null)
return false;
It looks like the patch program did not do what you wanted. The source
that I got from Subversion contains:
<<<<<<< .mine
if( startParams == null)
return false;
===
if (startParams == null)
return false;
>>>>>>> .r53839
The Java compiler does not like "<<<<<<< .mine", etc. :-)
Jack
Re: [jira] Created: (DERBY-36) Following the provided documentation and running ij results in a java.lang.InternalException
derby-dev@db.apache.org wrote: Message: A new issue has been created in JIRA. - View the issue: http://issues.apache.org/jira/browse/DERBY-36 Here is an overview of the issue: - Key: DERBY-36 Summary: Following the provided documentation and running ij results in a java.lang.InternalException Type: Bug (snip) Assignee: Reporter: Vess Ivanov Created: Fri, 8 Oct 2004 5:30 PM Updated: Fri, 8 Oct 2004 5:30 PM Environment: set DERBY_INSTALL=e:\Derby set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbytools.jar;%CLASSPATH% Description: In a commad window type: set DERBY_INSTALL=e:\Derby set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbytools.jar;%CLASSPATH% java -cp %CLASSPATH% org.apache.derby.tools.ij Yelds the following result : Exception in thread "main" java.lang.InternalError: No run method at java.security.AccessController.doPrivileged(Native Method) at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java) at org.apache.derby.impl.tools.ij.Main.go(Main.java) at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java) at org.apache.derby.impl.tools.ij.Main14.main(Main14.java) at org.apache.derby.tools.ij.main(ij.java) According to the Sun documentation java.lang.InternalError is "thrown to indicate some unexpected internal error has occurred in the Java Virtual Machine". What JVM where you using?
Re: [PATCH] Extension Packaging
Daniel John Debrunner wrote:
>Date: Tue, 21 Sep 2004 15:05:44 -0700
>From: Jack Klebanoff <[EMAIL PROTECTED]>
>Subject: [PATCH] Extension Packaging
>Content-Type: text/plain; charset=us-ascii; format=flowed
>I am an IBM employee working on extensions to Derby. I would like to
>change Derby to simplify the packaging of extensions. An example of a
>potential extension is RAM based storage.
Can you please re-submit the patch as an attachment, I haven't been able
to apply the in-line version.
Thanks,
Dan.
Index: java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java
===
--- java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java
(revision 54147)
+++ java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java
(working copy)
@@ -43,6 +43,7 @@
import org.apache.derby.iapi.services.io.FormatableInstanceGetter;
import org.apache.derby.iapi.error.ExceptionSeverity;
+import org.apache.derby.io.StorageFactory;
import org.apache.derby.iapi.services.context.ErrorStringBuilder;
@@ -80,6 +81,8 @@
import java.security.PrivilegedExceptionAction;
import java.security.PrivilegedActionException;
+import java.net.URL;
+
/**
Implementation of the monitor that uses the class loader
that the its was loaded in for all class loading.
@@ -1052,8 +1055,6 @@
Vector implementations = actualModuleList ? new
Vector(moduleList.size()) : new Vector(0,1);
- Class persistentServiceClass = PersistentService.class;
-
// Get my current JDK environment
int theJDKId = JVMInfo.JDK_ID;
@@ -1113,17 +1114,8 @@
Class possibleModule =
Class.forName(className);
// Look for the monitors special
modules, PersistentService ones.
- if
(persistentServiceClass.isAssignableFrom(possibleModule)) {
- PersistentService ps =
(PersistentService) newInstance(possibleModule);
- if (ps == null) {
- report("Class " +
className + " cannot create instance, module ignored.");
- continue;
- }
- if (serviceProviders == null)
- serviceProviders = new
Hashtable(3, (float) 1.0);
-
serviceProviders.put(ps.getType(), ps);
- continue;
- }
+ if
(getPersistentServiceImplementation(possibleModule))
+continue;
// If this is a specific JDK version
(environment) module
// then it must be ordered in the
implementation list by envJDKId.
@@ -1199,8 +1191,35 @@
report("Class " + className + " " +
le.toString() + ", module ignored.");
}
}
- }
+else if( key.startsWith( Property.SUB_SUB_PROTOCOL_PREFIX)) {
+String subSubProtocol = key.substring(
Property.SUB_SUB_PROTOCOL_PREFIX.length());
+String className = moduleList.getProperty(key);
+ if (SanityManager.DEBUG && reportOn) {
+ report("Accessing module " + className
+ " to run initializers at boot time");
+ }
+try {
+Class possibleImplementation = Class.forName(className);
+ // Look for the monitors special
classes, PersistentService and StorageFactory ones.
+if( getPersistentServiceImplementation(
possibleImplementation))
+continue;
+if( StorageFactory.class.isAssignableFrom(
possibleImplementation)) {
+if( newInstance( possibleImplementation) == null)
+report("Class " + className + " cannot create
instance, StorageFactory ignored.");
+else
+storageFactories.put( subSubProtocol, className);
+continue;
+}
+}
+ catch (ClassNotFoundException cnfe) {
+ report("Class " + className + " " +
cnfe.toString() + ", module ignored.");
Re: How to optimize the query
You can find derby.properties in the directory named by system property derby.system.home, or the working directory where you started derby if derby.system.home is not defined. Jack Klebanoff David Zonsheine wrote: Sunitha Hi, Where can I find the derby.properties? Thanks, David -Original Message- From: Sunitha Kambhampati [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 6:25 PM To: Derby Development Subject: Re: How to optimize the query David Zonsheine wrote: We tried to see the index usage but couldn't. did you try to set the property derby.language.logQueryPlan=true in your derby.properties file or you can also set it as a java property -Dderby.language.logQueryPlan=true This property when set will print the query plans in derby.log If an index is used, you will be able to see IndexScan ResultSet an example : here my tablename is SCANFIXED and index is SCANFIXEDX... [snip] Index Scan ResultSet for SCANFIXED using index SCANFIXEDX at read committed isolation level using instantaneous share row locking chosen by the optimizer [snip] Sunitha. -- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the originator of the message. Scanning of this message is performed by SurfControl E-mail Filter software in conjunction with virus detection software.
[PATCH] Derby and Custom ClassLoader
Derby may fail to start when it is loaded through a custom ClassLoader.
The reason is that the static java.lang.ClassLoader.getSystemResources
method may not find the resources loaded by a custom ClassLoader. The
patch fixes that by getting the current ClassLoader and calling its
getResources method.
Jack Klebanoff
Index: java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java
===
--- java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java
(revision 55637)
+++ java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java
(working copy)
@@ -1247,9 +1247,10 @@
Properties moduleList = new Properties();
boolean firstList = true;
+ClassLoader cl = getClass().getClassLoader();
try {
-for( Enumeration e = ClassLoader.getSystemResources(
"org/apache/derby/modules.properties");
+for( Enumeration e = cl.getResources(
"org/apache/derby/modules.properties");
e.hasMoreElements() ;) {
URL modulesPropertiesURL = (URL) e.nextElement();
InputStream is = null;
Jira Developers Group
Please add me to the Jira developers group.
Re: Should patches also update functional tests?
A good test suite is invaluable. It is very important that it be kept working. Then when one makes a code change that causes a test to fail you can be fairly sure that your change broke something. You can fix it before the change is submitted, keeping Derby's quality up. If the test suite is weak or if it shows failures then Derby's quality will deteriorate. It will be very hard to get it back up. I think that we should never accept a patch that breaks a test. If the test is in error then the patch should include a fix for the test. We should strongly encourage all patches to include tests. A committer should think twice, or three times, before committing a patch without tests. Jack Satheesh Bandaram wrote: I would like to seek suggestions or comments regarding submitting patches with appropriate test changes. Since Derby now has functional tests, should we suggest or even require patches to be submitted along with a test case added? This will only apply if a test case is available and doesn't require undue amount of time and resources to test the patch. Obvious advantages are: 1. We can be sure we are indeed addressing the problem. Unless committers write a test case based on the code changed, we may not know for sure we did fix the problem correctly. 2. Can prevent future regressions in the code, where the same defect is reintroduced because of another patch or enhancement. 3. Strengthens Derby funtional test suites and test coverage. This may introduce additional burden on patch submissions, hence, we could make this voluntary to start with. How do other Apache projects handle this? Satheesh
Intersect
I plan on working on the INTERSECT operator in Derby. It is a table operator, syntactically like UNION, e.g. select a,b from t1 INTERSECT select d,e from t2 Like UNION, it requires that the two source tables have compatible columns. It returns the rows that are in both the source tables. By default duplicates are removed. If "INTERSECT ALL" is specified then duplicates are not removed. In that case if row R occurs m times in one source table and n times in the other then row R will appear min(m,n) times in the result. (This is specified by the SQL standard). The full syntax is: ::= INTERSECT [ALL] According to the SQL standard, the INTERSECT operator takes precedence over the UNION operator. Thus query1 INTERSECT query2 UNION query3 is equivalent to (query1 INTERSECT query2) UNION query3 The INTERSECT operator is supported in DB2, Oracle, and Postgres. Comments? Objections? Jack Klebanoff
Re: [jira] Commented: (DERBY-13) Quoted names with embedded period mishandled in from list
Jan Hlavat? (JIRA) wrote: [ http://nagoya.apache.org/jira/browse/DERBY-13?page=comments#action_56090 ] Jan Hlavatý commented on DERBY-13: -- I think you need to store table name as a pair , not as a single String. As delimited identifiers can contain arbitrary characters, you dont have a reliable delimiter character to separate, and any escaping mechanism would be costly. Quoted names with embedded period mishandled in from list - Key: DERBY-13 URL: http://nagoya.apache.org/jira/browse/DERBY-13 Project: Derby Type: Bug Components: SQL Versions: 10.0.2.0 Reporter: Ramandeep Kaur Priority: Minor Attachments: Derby-13.patch Opening this bug on behalf of Satheesh Bandaram - The compiler mishandles quoted names with embedded periods when checking uniqueness of table names in the from list of a SELECT. Consider the following SQL: create table "S1.T1" (id int not null primary key, d1 int); create schema s1; create table s1.t1 (id int not null primary key, d2 int); select * from s1.t1, "S1.T1" where s1.t1.id = "S1.T1".id; Derby throws an error on the final SELECT statement: "ERROR 42X09: The table or alias name 'S1.T1' is used more than once in the FROM list". However s1.t1 and "S1.T1" are different tables. I agree with Jan. Representing table names as a (schema, table) pair is more straightforward, if we are to handle the corner cases correctly. It captures the fact that a table name is not a simple string and that a simple string comparison does not always correctly compute name equivalence. Another consideration is the equivalence of delimited and non-delimited identifiers. For instance the undelimited identifier id, and the delimited identifier "ID" are supposed to be identical. The SQL standard says "A and a are equivalent if the of the (with every letter that is a lower-case letter replaced by the corresponding upper-case letter or letters) and the of the (with all occurrences of replaced by and all occurrences of replaced by ), considered as the repetition of a that specifies a of SQL_IDENTIFIER and an implementationdefined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, ‘‘’’". (That is one long sentence). If we store delimited identifiers with double quotes and undelimited identifiers without then a simple string comparison will miss this equivalence. I think that it is important that we get this equivalence right. Some DML is generated programmatically from metadata and always uses delimited identifiers, even if the DDL did not. Changing Derby to use a (schema, table) pair instead of a simple string for table names is a lot of work. A large number of files must be changed. Jack
Re: [jira] Commented: (DERBY-13) Quoted names with embedded period mishandled in from list
Daniel John Debrunner wrote: Jack Klebanoff wrote: >Changing Derby to use a (schema, table) pair instead of a simple string >for table names is a lot of work. A large number of files must be changed. But Derby doesn't use a simple string for a table name, it uses an object called TableName, which correctly handles the schema, table name and quoting. Only in this incorrect 'exposed' table name case is a string used. Dan. TableName is a compiler object, and, as an extension of QueryTreeNode, it is a fairly heavy duty object tied to a connection. Outside of the compiler transient data structures, and perhaps even some places inside them, we need other mechanisms to represent table names. Jack
[PATCH] Intersect and Except
Attached is a patch that implements the SQL INTERSECT and EXCEPT
operators. The INTERSECT operator constructs the intersection of two
tables. The EXCEPT operator finds all rows in one table but not in the
other. The syntax is (roughly):
INTERSECT [ALL]
EXCEPT [ALL]
By default these operators remove duplicates, which can occur if there
are duplicates in the inputs. If ALL is specified then duplicates are
not returned. If t1 has m copies of row R and t2 has n copies then t1
INTERSECT ALL t2 returns min(m,n) copies of R, and t1 EXCEPT ALL t2
returns max( 0, m-n) copies of R.
The EXCEPT operator has the same precedence as UNION. INTERSECT has
higher precedence.
This follows the SQL-92 spec. (At least it follows my understanding of
the spec. Spec lawyers are invited to comment).
The implementation uses sorting. The two input tables are sorted and
then scanned together. The appropriate rows from the left input are output.
The compiler binds INTERSECT and EXCEPT like UNION. Therefore a new
class, org.apache.derby.impl.sql.compile.SetOperatorNode, was carved out
of UnionNode. It mainly contains bind methods. Classes UnionNode and
IntersectOrExceptNode extend SetOperatorNode. Classes IntersectNode and
ExceptNode extend IntersectOrExceptNode. IntersectOrExceptNode does most
of the optimization and code generation work. It puts OrderBy nodes in
front of its inputs.
The generated code creates a SetOpProjectRestrictResultSet that reads
its sorted inputs to produce the required output table.
Jack Klebanoff
Index: java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
===
--- java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
(revision 111283)
+++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
(working copy)
@@ -512,6 +512,12 @@
case C_NodeTypes.UNION_NODE:
return C_NodeNames.UNION_NODE_NAME;
+ case C_NodeTypes.EXCEPT_NODE:
+ return C_NodeNames.EXCEPT_NODE_NAME;
+
+ case C_NodeTypes.INTERSECT_NODE:
+ return C_NodeNames.INTERSECT_NODE_NAME;
+
case C_NodeTypes.CREATE_TRIGGER_NODE:
return C_NodeNames.CREATE_TRIGGER_NODE_NAME;
Index: java/engine/org/apache/derby/impl/sql/compile/IntersectNode.java
===
--- java/engine/org/apache/derby/impl/sql/compile/IntersectNode.java
(revision 0)
+++ java/engine/org/apache/derby/impl/sql/compile/IntersectNode.java
(revision 0)
@@ -0,0 +1,60 @@
+/*
+
+ Derby - Class org.apache.derby.impl.sql.compile.IntersectNode
+
+ Copyright 1997, 2004 The Apache Software Foundation or its licensors, as
applicable.
+
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+
+packageorg.apache.derby.impl.sql.compile;
+
+import org.apache.derby.iapi.services.compiler.MethodBuilder;
+
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+import org.apache.derby.iapi.error.StandardException;
+
+import java.lang.Math;
+
+/**
+ * A IntersectNode represents an INTERSECT DML statement.
+ *
+ * @author Jack Klebanoff
+ */
+
+public class IntersectNode extends IntersectOrExceptNode
+{
+int getOpType()
+{
+return INTERSECT_OP;
+}
+
+String getOperatorName()
+{
+return "INTERSECT";
+}
+
+double getRowCountEstimate( double leftRowCount, double rightRowCount)
+{
+// The result has at most min( leftRowCount, rightRowCount). Estimate
the actual row count at
+// half that.
+return Math.min( leftRowCount, rightRowCount)/2;
+}
+
+double getSingleScanRowCountEstimate( double leftSingleScanRowCount,
double rightSingleScanRowCount)
+{
+return Math.min( leftSingleScanRowCount, rightSingleScanRowCount)/2;
+}
+}
Index: java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
===
--- java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
(revision 111283)
+++ java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
(working copy)
@@ -258,6 +258,10 @@
static final String UNION_NODE_NAME =
"org.apache.derby.impl.sql.compile.UnionNode";
+
BackingStoreHashtable
Derby uses class BackingStoreHashtable to implement a hash table used in hash joins. Despite the class's name it does not spill to disk; the hash table is always in memory. The optimizer tries to avoid hash joins when the input is large because BackingStoreHashtable would use too much memory. The optimizer just works with row count estimates, so there is no guarantee that BackingStoreHashtable will not blow up. No customer has complained about this yet. I would like to work on this, changing BackingStoreHashtable to spill to disk when the hash table gets large, and changing the optimizer to understand that large hash tables are allowed, but more costly. The advantages of doing this are that 1. hash joins will not blow up when the compiler's row count estimate was low, 2. the optmizer can choose hash joins on large inputs when that is the least costly join implementation, and 3. we can use BackingStoreHashtable to implement features such as INTERSECT and GROUP BY, though I am not proposing to do so now. I am not proposing to implement a hash table that can be used as an alternative to Btrees for a secondary index. BackingStoreHashtable is used for transient data structures that are only accessed by a single thread. A secondary index implementation must deal with locking and must implement hashCode methods that are JVM independent. This is much more work and would yield a slower implementation. I propose that BackingStoreHashtable should start off using an in-memory hash table even if the estimated row count is large. That way it will use an in-memory hash table when the actual row count is small enough for the table to fit in memory. When it finds that spilling to disk is necessary BackingStoreHashtable will use the estimated row count to determine the initial number of buckets and move the in-memory entries to disk. The disk based hash table will use a linear hashing algorithm, see "External Memory Algorithms and Data Structures: Dealing withMassive Data", Jeffrey Scott Vitter, ACM Computing Surveys, Vol. 33, No. 2, June 2001, pp. 209–271. It grows the hash table one bucket at a time when the average number of entries per bucket grows beyond a threshold. The disadvantage of growing by a larger number of buckets is that the last expansion may be unnecessarity large, wasting time and space. I would appreciate it if anyone can point me to a better external hash table algorithm. The disk hash table will use overflow pages because an imperfect hash function will cause some buckets to get more than their share of entries, and because we may have duplicate keys. I have not yet looked at how the optimizer handles hash joins, so I do not yet have a proposal for how to change that. Can anyone explain how to generate a cost for the Derby optimizer? How do you compute it from an estimated number of disk accesses? Does an estimate of 2 disk accesses mean a cost of 2.0? Should the cost estimate include CPU time? If so, how do you estimate it relative to disk access cost? Putting it another way, what does a cost estimate of 1.0 (or x) mean? Comments, criticisms, questions? Jack Klebanoff
Re: [PATCH] Intersect and Except
RPost wrote: Your text says 'If ALL is specified then duplicates are not returned'. You also say that by default duplicates are removed. So is the default ALL? Or did you mean that if ALL is specified then duplicates are not removed? If you don't need to remove duplicates then doesn't that mean that you only need to sort one of the data sets? If so, this would improve performance. If statistics are available then the smaller dataset should be the one that is sorted. I am sorry, my typo. My message should have read "If ALL is specified then duplicates *are* returned". The default is to remove duplicates, ALL is not the default. Sorting both inputs makes the final selection easier and faster even when duplicates are not removed. As RPost suggests, we could implement INTERSECT ALL and EXCEPT ALL by just sorting one of the inputs, avoiding part of the sort cost. Then we would scan through each row in the unsorted input and use a binary search to see if it is in the sorted input. In order to output the correct number of duplicates we must mark the row as removed from the sorted input. I don't think that the org.apache.derby.iapi.sql.ResultSet interface or the SortedResultSet class currently implement row deletion or marking. If the number of rows in the two inputs are m and n the cost of sorting both is O(n*log(n) + m*log(m)). The final select step has a cost of O(n+m). If we only sort one input, say the second one, then the sort cost is O(n*log(n)) and the cost of the final select step is O(m*log(n)), for a total cost O((m+n)*log(n)). If n < m this is asymptotically better. The binary searches will have poor locality of reference and deleting rows from a temporary table that has spilled to disk may be expensive, so in practice sorting just one input may only be better when the two inputs have significantly different sizes. Perhaps in the future we can implement both methods and pick the better one at optimize time. I wanted to make an initial implementation that is simple, handles both INTERSECT and EXCEPT, and that fits into the current Derby implementation fairly easily. If the performance of INTERSECT or EXCEPT proves to be important then we should revisit this. In fact there are a number of related implementation alternatives that may be optimal under different conditions. 1. Just sort the smaller input and use a binary search to see if each row in the unsorted input is also in the sorted input. INTERSECT is commutative, so it does not matter which of its inputs is sorted. EXCEPT is not commutative. If we sort its left input and scan through the unsorted right input we have to remove/mark the intersection rows in the left (sorted) input and output the unremoved/marked rows at the end. 2. We can also handle duplicate elimination when just sorting one input by removing duplicates when doing the sort, but not removing/marking rows from the sorted input as they are found in the other input. 3. We can use a hash table instead of sorting. Unfortunately Derby does not yet have a hash table implementation that spills to disk. Jack Jack Klebanoff
Re: [PATCH] Intersect and Except
Satheesh Bandaram wrote:
I believe this change is also needed to the original patch submitted.
Minor change, but the original patch was allowing the following
statement, when it should have failed:
ij> select * from app.t intersect select * from app.t1 order by t.i;
I
---
0 rows selected
Satheesh
[bandaram:satheesh] svn diff OrderByColumn.java
Index: OrderByColumn.java
===
--- OrderByColumn.java (revision 111541)
+++ OrderByColumn.java (working copy)
@@ -172,7 +172,7 @@
ResultColumnListtargetCols =
target.getResultColumns();
//bug 5716 - for db2 compatibility - no qualified names
allowed in order by clause when union/union all operator is used
- if (target instanceof UnionNode && correlationName !=
null)
+ if (target instanceof SetOperatorNode && correlationName
!= null
)
{
String fullName = (schemaName != null) ?
(schemaName + "." + correlationName +
"." + columnName) :
[bandaram:satheesh]
Good catch Satheesh! Actually there is another bug with order by in
intersect -- it is not implemented! I will post an update to my patch
when I have fixed this and some other issues.
Jack
Re: [PATCH] Intersect and Except
I have attached an update to my previous INTERSECT/EXCEPT patch. It addresses a number of concerns brought up in this thread. 1. It includes the fix for order by syntax checking posted by Satheesh. 2. It fixes the execution of an order by clause in the statement. Previously an order by clause was ignored. 3. The intersect/except is still implemented by sorting its two inputs, but optimizer is given a chance to perform sort avoidance on the inputs. 4. If there is an order by clause on the output of the intersect/except then that ordering is used for the inputs, avoiding a separate sort on the intersect/except output. 5. Test cases were added to cover the above code. 6. The copyright notices for the new files were changed to 2004. 7. The SetOpProjectRestrictResultSet class was renamed to SetOpResultSet, which is more appropriate. 8. The IntersectNode and ExceptNode classes were removed and subsumed in the IntersectOrExceptNode class. Some of the concerns about optimization were not entirely addressed. There is still just the one execution strategy: sort the two inputs and scan them together. I did not implement other strategies and an optimizer that picks the best of them. I think that my implementation strategy performs decently in all cases, and is the best in many cases. I don't think that it is wise to write a lot of code to optimize an operation that is probably only used infrequently. (Cloudscape customers have gotten along without it for all these years). While this update allows the optimizer to avoid sorting the intersect/except inputs it does not try to pick an ordering that is more likely to avoid a sort. For instance, suppose one of the inputs is a single table select that selects the columns of a unique key. Then you only have to order that input on the key columns. Depending on the where clause the optimizer might decide to use the unique key index to traverse the table in sort order, avoiding a separate sort. The other input to the intersect/except may be a different story. Those same columns may not specify a unique key in the other input, in which case that input must be ordered on more columns. Unfortunately the Derby compiler architecture does not make it easy for the IntersectOrExceptNode class to determine a column ordering that is likely to avoid a sort on its inputs. Its inputs are represented as ResultSetNodes. It is not easy to determine a ResultSetNode represents a single table select, and if so whether the selected columns contain a unique key. One would like to try avoiding a sort on the larger input, and then try a column ordering that might avoid a sort on the smaller input if the optimizer cannot avoid sorting the larger input. Unfortunately this does not work: the architecture requires that the order by clause be pushed down by the start of optimization. It is easy to see if the output of the intersect/except must be ordered and use this ordering to order the inputs, avoiding a separate sort on the output of the intersect/except. I did this in the attached patch update. Jack Jack Klebanoff wrote: Attached is a patch that implements the SQL INTERSECT and EXCEPT operators. The INTERSECT operator constructs the intersection of two tables. The EXCEPT operator finds all rows in one table but not in the other. The syntax is (roughly): INTERSECT [ALL] EXCEPT [ALL] By default these operators remove duplicates, which can occur if there are duplicates in the inputs. If ALL is specified then duplicates are not returned. If t1 has m copies of row R and t2 has n copies then t1 INTERSECT ALL t2 returns min(m,n) copies of R, and t1 EXCEPT ALL t2 returns max( 0, m-n) copies of R. The EXCEPT operator has the same precedence as UNION. INTERSECT has higher precedence. This follows the SQL-92 spec. (At least it follows my understanding of the spec. Spec lawyers are invited to comment). The implementation uses sorting. The two input tables are sorted and then scanned together. The appropriate rows from the left input are output. The compiler binds INTERSECT and EXCEPT like UNION. Therefore a new class, org.apache.derby.impl.sql.compile.SetOperatorNode, was carved out of UnionNode. It mainly contains bind methods. Classes UnionNode and IntersectOrExceptNode extend SetOperatorNode. Classes IntersectNode and ExceptNode extend IntersectOrExceptNode. IntersectOrExceptNode does most of the optimization and code generation work. It puts OrderBy nodes in front of its inputs. The generated code creates a SetOpProjectRestrictResultSet that reads its sorted inputs to produce the required output table. Jack Klebanoff Index: java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java === --- java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java (revision 111907) +++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java (working
Re: [PATCH] Change all Identifier Limitations to 128
Daniel John Debrunner wrote: Philipp Hug wrote: >this patch changes maximum length of column names, index/contraint >names, cursor names and schema names to 128 and keeps MaxUserNameLength >at 30. >it also fixes the metadata to return correct values it also fails to compile :-( Any contributor should check that their patch compiles after an ant clobber (see BUILDING.txt for details). [javac] Found 2 semantic errors compiling "C:/work/p4/os/cqc/java/com/ibm/cq c/language/SQLParser.java": [javac] 15828. schemaName = identifier(DB2Limit.DB2_MAX_IDENTIFIER_ LENGTH30, true); [javac] Dan, com/ibm/cqc is not part of Derby. Jack
Re: Optimizer hints?
RPost wrote:
>Do you have any specific examples of queries that could benefit from an
>optimizer hint?
This thread started with an example of a query that could benefit from
an optimizer hint. Gerald Khin has a query that blows up because the
Derby optimizer made the wrong choice. See
http://nagoya.apache.org/jira/browse/DERBY-106
Most database systems that have been around for a while have implemented
optimizer hints. Sooner or later they come across performance critical
queries where the optimizer makes a bad choice, but the optimizer cannot
be fixed easily or quickly. (Adding an optimizer hint is generally
faster than waiting for a new optimizer release).
Jack
>
>Does anyone know of any documented instances where Derby has selected an
>execution plan that is ineffective or suboptimal? Were these instances
able
>to be corrected by tuning the system using existing
>properties?
>
>Oracle allows hints to be provided as SQL comments.
>
>Re providing an offline tool. Does this method imply that the statements
>exist independent of any given running database instance. If so are you
>proposing that a prepared statement be saved and made available for future
>execution without preparing it again?
>
>- Original Message -
>From: "Satheesh Bandaram" <[EMAIL PROTECTED]>
>To: "Derby Development"
>Sent: Friday, December 17, 2004 2:41 PM
>Subject: Optimizer hints?
>
>
I have been thinking about adding optimizer hints to Derby. Though Derby
optimizer does perform a reasonable job, it may be useful to have
optimizer hints for cases 1) when updated index statistics is not
available, or even incorrect 2) Rare cases, like this one?, when the
optimizer doesn't do the job right 3) when applications issue queries
not designed for Derby (like very large number of tables).
Derby optimizer is primarily tasked to handle 1) access method (whether
to use table scan or index) 2) join order and 3) join strategy (nested
loop or HashJoin) A complete optimizer hints (should they actually be
called optimizer overrides?) should be able to provide means of
specifying all these. There are several ways to do this, including
1. Enhance the SQL to recognize additional keywords, like properties.
These properties could specify optimizer hints. That is what Cloudscape
had before, but this causes non-portable SQL to be written. Attempts to
run these statements against any other SQL server could cause syntax
errors. Not an ideal solution, according to me.
2. Provide optimizer hints as SQL comments. These comments are
recognized only by Derby parser and other SQL engines would simply
ignore these. There are some limitations in Derby parser currently to
implement this option.
3. Provide an offline tool which registers hints for SQL statements.
When those SQL statements are executed, Derby could look up previously
registered hints for those statements and automatically apply them.
Advantage of this scheme is that applications don't need to be modified
to add hints and they can easily be removed when not needed. How exactly
to register the hints could be worked out. I personally prefer this
approach.
Any comments?
Satheesh
Gerald Khin (JIRA) wrote:
>HashJoinStrategy leads to java.lang.OutOfMemoryError
>
>Key: DERBY-106
>URL: http://nagoya.apache.org/jira/browse/DERBY-106
>Project: Derby
> Type: Bug
> Reporter: Gerald Khin
>My application is running out of memory: I encounterd a
java.lang.OutOfMemoryError. I used -Xmx256M. Unfortunatley, I cannot
spend an arbitrary amount of JVM memory.
>Then, I commented out the line in class OptimizerFactoryImpl which was
adding the HashJoinStrategy to the set of Join strategies:
> if (joinStrategySet == null)
> {
>//JoinStrategy[] jss = new JoinStrategy[2];
> JoinStrategy[] jss = new JoinStrategy[1];
> jss[0] = new NestedLoopJoinStrategy();
>//jss[1] = new HashJoinStrategy();
> joinStrategySet = jss;
> }
>And with these changes the OutOfMemoryError has gone away! And it works
even with -Xmx128M!!!
>So I guess that there is a major memory issue with this HashJoin
strategy implementation.
>If it turns out to be too complicated to make the memory consumption
more predicatble or even bounded to some configurable limit, then I need
at least as a workaround a way to turn off the HashJoin strategy
completely: I did it by patching and building my own derby.jar, but if
there would be an official solution with some kind of switch like a
system property, it would be great!
Re: [VOTE] [PATCH] Intersect and Except
Satheesh Bandaram wrote: I am submitting this patch for a VOTE. It has been pending for about a week. My vote is "+1", with the following comments. Since this is a new feature, I think, three +1 votes are requied. Here is the status of this patch. I am basically waiting for the final +1 vote 1. It passed build and all tests. 2. Mike and myself have voted +1. 3. Dan provided a suggestion, with some syntax improvement. Any response from the contributor? I am assuming Dan's vote is a +1. If not, please speak up.. :-) Here are my comments: 1. IntersectOrExceptNode still refers to SetOpProjectRestrict. Should this be SetOpResultSet? 2. Doesn't tableConstructor logic apply only to UnionNode? If so, should the fields like tableConstructor, topTableConstructor and methods like setTableConstructorTypes() be moved to UnionNode? Current code in SetOperatorNode refers to subclass UnionNode a lot, which could be improved? Satheesh, I think that you are right on both points. IntersectOrExceptNode should refer to SetOpResultSet instead of SetOpProjectRestrict. From reading the code I gather that the tableConstructor field is used to mark a Union node that is generated from a VALUES expression that has more than one row. So, while the expression (t1 INTERSECT t2) sonstructs a table, it will never be a "tableConstructor" in the narrower sense used by our code. I will change the code accordingly and submit a new patch. It should be ready today or first thing tomorrow morning. Jack
Re: [VOTE] [PATCH] Intersect and Except
Satheesh Bandaram wrote: I am submitting this patch for a VOTE. It has been pending for about a week. My vote is "+1", with the following comments. Since this is a new feature, I think, three +1 votes are requied. Here is the status of this patch. I am basically waiting for the final +1 vote 1. It passed build and all tests. 2. Mike and myself have voted +1. 3. Dan provided a suggestion, with some syntax improvement. Any response from the contributor? I am assuming Dan's vote is a +1. If not, please speak up.. :-) Here are my comments: 1. IntersectOrExceptNode still refers to SetOpProjectRestrict. Should this be SetOpResultSet? 2. Doesn't tableConstructor logic apply only to UnionNode? If so, should the fields like tableConstructor, topTableConstructor and methods like setTableConstructorTypes() be moved to UnionNode? Current code in SetOperatorNode refers to subclass UnionNode a lot, which could be improved? I have revised my patch to address Satheesh and Dan's comments. The revision changes IntersectOrExceptNode.java, sqlgrammar.jj, SetOpResultSet.java, TableOperatorNode.java, UnionNode.java, and SetOperatorNode.java since my last submission. It passed the derbyall test suite. Jack Index: java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java === --- java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java (revision 124163) +++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java (working copy) @@ -512,6 +512,9 @@ case C_NodeTypes.UNION_NODE: return C_NodeNames.UNION_NODE_NAME; + case C_NodeTypes.INTERSECT_OR_EXCEPT_NODE: + return C_NodeNames.INTERSECT_OR_EXCEPT_NODE_NAME; + case C_NodeTypes.CREATE_TRIGGER_NODE: return C_NodeNames.CREATE_TRIGGER_NODE_NAME; Index: java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java === --- java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java (revision 124163) +++ java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java (working copy) @@ -258,6 +258,8 @@ static final String UNION_NODE_NAME = "org.apache.derby.impl.sql.compile.UnionNode"; + static final String INTERSECT_OR_EXCEPT_NODE_NAME = "org.apache.derby.impl.sql.compile.IntersectOrExceptNode"; + static final String UNTYPED_NULL_CONSTANT_NODE_NAME = "org.apache.derby.impl.sql.compile.UntypedNullConstantNode"; static final String UPDATE_NODE_NAME = "org.apache.derby.impl.sql.compile.UpdateNode"; Index: java/engine/org/apache/derby/impl/sql/compile/UnionNode.java === --- java/engine/org/apache/derby/impl/sql/compile/UnionNode.java (revision 124163) +++ java/engine/org/apache/derby/impl/sql/compile/UnionNode.java (working copy) @@ -20,15 +20,12 @@ packageorg.apache.derby.impl.sql.compile; -import org.apache.derby.iapi.services.context.ContextManager; - import org.apache.derby.iapi.services.compiler.MethodBuilder; import org.apache.derby.iapi.services.sanity.SanityManager; import org.apache.derby.iapi.error.StandardException; -import org.apache.derby.iapi.sql.compile.CompilerContext; import org.apache.derby.iapi.sql.compile.Optimizable; import org.apache.derby.iapi.sql.compile.OptimizablePredicate; import org.apache.derby.iapi.sql.compile.OptimizablePredicateList; @@ -37,33 +34,18 @@ import org.apache.derby.iapi.sql.compile.RowOrdering; import org.apache.derby.iapi.sql.compile.C_NodeTypes; -import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor; -import org.apache.derby.iapi.sql.dictionary.DataDictionary; -import org.apache.derby.iapi.sql.dictionary.DefaultDescriptor; -import org.apache.derby.iapi.sql.dictionary.TableDescriptor; import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor; -import org.apache.derby.iapi.types.DataTypeDescriptor; - import org.apache.derby.iapi.reference.SQLState; import org.apache.derby.iapi.reference.ClassName; -import org.apache.derby.iapi.sql.Activation; -import org.apache.derby.iapi.types.DataTypeDescriptor; -import org.apache.derby.iapi.sql.ResultSet; -import org.apache.derby.iapi.sql.Row; - -import org.apache.derby.iapi.types.TypeId; - import org.apache.derby.impl.sql.compile.ActivationClassBuilder; +import org.apache.derby.iapi.types.DataTypeDescriptor; + import org.apache.derby.iapi.util.JBitSet; import org.apache.derby.iapi.services.classfile.VMOpcode; -import org.apache.derby.catalog.types.DefaultInfoImpl; - -import java.util.Properties; - /** * A UnionNode represents a UNION in a DML statement. It contains a boolean * telling whether the union operation should eliminate duplicate rows. @@ -71,26 +53,19 @@ * @author Jeff Lichtman */ -
Re: On DERBY-107 : ODBC Metadata functions
Army wrote: [snip] DISCUSSION: The question is now this: what's the best/preferred way to propagate this ODBC/JDBC duality from the "SystemProcedures.java" file to the corresponding methods in org.apache.derby.impl.jdbc.EmbedDatabaseMetadata.java (hereafter referred to as "EDM")? Option I: Add new SQL statements, such as "getColumnsForODBC", to the existing metadata.properties file, as described in the proposal for DERBY-107. Then, since EDM has to know which version of a given SQL statement to execute--for example, should it call the regular "getColumns" version, or should it call the new "getColumnsForODBC" version?--we could add new methods (such as "setForODBC()") to EDM that could be used by SystemProcedures to indicate (to EDM) that ODBC metadata should be returned, intead of JDBC metadata. Note that, since SystemProcedures is in a different package than EDM, the new methods on EDM would (I think) have to be _public_. Regarding this approach, one must ask: [ #1 COMMUNITY INPUT? ] What's the general attitude toward adding public methods to a Derby class that is implementing a specific JDBC class? In the context of this discussion, is it or is it not acceptable/desireable to add Derby-specific public methods to a class like EmbedDatabaseMetadata.java, which is an implementation of java.sql.DatabaseMetaData? Technically speaking, I don't think the addition of public classes breaks the JDBC standard (so long as we aren't telling people that they can import EmbedDatabaseMetadata in their apps--which we aren't), but I'm curious as to whether there's a "good programming practice" here that the Derby community would like to (or already does?) hold to? [ #1 End ] I would prefer that the ODBC support not be put in the EmbedDatabaseMetadata class. Then applications that do not use ODBC do not have to load ODBC support into their JVM. Option II: Add new SQL statements, such as "getColumnsForODBC", to the existing metadata.properties file, as described in the proposal for DERBY-107. Then we could extend the EDM class with a new, simple class that sets ODBC-related state, and modify EDM to check the state and execute the appropriate statements. For example, we could add a protected variable "forODBC" to EDM, default it to "false", and then set it to true in the extended class for ODBC. EDM would then check the flag and execute the corresponding metadata statement. The presumption here is that SystemProcedures would check for the ODBC indicator and, if found, use an instance of the new subclass for the metadata calls, instead of using an instance of the existing EDM. This approach allows us to avoid adding new (non-JDBC) public classes to EDM, at the cost of creating another (albeit fairly simple) metadata class. With this approach, we could even go further and add another file, say "odbc_metadata.properties" that holds the ODBC metadata statements (instead of adding them to the existing metadata.properties file). The new subclass could then load _that_ file instead of the current metadata.properties file, which gives us a nice separation of functionality: all of the ODBC code cleanly separated from the JDBC code. Of course, that could be a bad thing, too, since 1) we'd then have TWO metadata files to worry about in the codeline, instead of just one, which introduces room for error if/when metadata-related processing changes occur in Derby, and 2) we'd have to duplicate any SQL statements that are the same for ODBC and JDBC (ex. several of the "getBestRowIdentifier" queries) in both files. So I'm guessing we wouldn't want to create another metadata file...but I thought I'd bring it up, just in case. Option III: Create some kind of internal VTI for ODBC metadata and use that. I have to admit that I don't know too much about how VTIs work, but Kathey gave me some places to look, so I'm going to read up. Apparently, we can execute the same metadata SQL statements that already exist for JDBC, then use a VTI to "massage" the result set into something that complies with ODBC specifications. This might be a good choice given that most of the differences between ODBC and JDBC are in the types of the columns returned. For example, JDBC might say that any String will do, whereas ODBC will say it has to be VARCHAR. In that case, a literal value ' ' will be fine for JDBC, but since it's treated as a CHAR value by Derby, it would be breaking ODBC standard. With a VTI, we could theoretically accomplish the same things that we'd be doing with new SQL statements--such as casting ' ' to VARCHAR in this particular case. Other modifications we'd have to implement include casting certain integer columns to smallints, and replacing null values in JDBC (such as for "sql_data_type" and "buffer_length" columns) to legal values (neither column is supposed to be null for ODBC). Upside to this is that we still only have a single me
Re: JDBC spec question for ResultSet.updateObject method
Mamta Satoor wrote: Hi, The JDBC API Tutorial and Reference book says that "The method setObject (on PreparedStatement) can take a null value only if the JDBC type is specified". But I couldn't find anything similar for ResultSet.updateObject(int columnIndex, Object x). Should this method throw an exception if null is passed for the Object since there is no way to specify JDBC type for this particular signature of updateObject? thanks, Mamta I suspect that a PreparedStatement may not always be able to decide on the type of a parameter when it compiles a statement, or it may not be able to do so easily. So PreparedStatement.setObject( i, null) may have trouble deciding how to handle the null. However a ResultSet knows the types of all of its columns, so ResultSet.updateObject( i, null) should treat the null as being of the type of the underlying column, which it knows. Jack
Re: BackingStoreHashtable
The optimizer decides when to implement a join using hash joins. If it estimates that one side of the join is small enough to fit in memory it may choose a hash join. The nature of estimates is that sometimes they are wrong. This is true for the optimizer's size estimates. Therefore, sometimes the hash table created for a hash join does not fit in memory. Currently the result is that the JVM terminates with an OutOfMemoryError, or it thrashes. See http://nagoya.apache.org/jira/browse/DERBY-106. I think that Derby should handle underestimates more gracefully. When a hash join is executed a BackingStoreHashtable object is constructed to implement the join. One of its constructor parameters is "max_inmemory_rowcnt", the maximum number of rows to insert into the in-memory hash table before overflowing to disk. Despite this constructor parameter (and the class name) the current implementation of BackingStoreHashtable never spills to disk. Hence Jira 106. I propose that we change BackingStoreHashtable to spill to disk. The goals of the change are as follows: 1. Allow hash joins to work on any input size, up to the amount of available disk. 2. The execution time for hash joins should be reasonable even if the size estimates prove to be low. 3. Have at most a moderate impact on Derby's size. 4. Use a low amount of development effort. The basic idea is as follows. When the number of rows in a BackingStoreHashtable exceeds max_inmemory_rowcnt a disk based associative data structure will be created and all new rows will be inserted into the disk structure. Existing rows will be left in memory. When BackingStoreHashtable is asked to find rows with a given key it will first look in the in memory Hashtable, then in the disk structure. If duplicate keys are allowed in the BackingStoreHashtable then it must look in both places. (There are several reasonable variations on this scheme dealing with the movement of rows between the in-memory hash table and the disk structure. They will be discussed later. However, I think that the simplest scheme is best). There are two main alternatives for implementing the disk based associative data structure. One is to use Btrees, the other is to implement a dynamic hash table. Btree Implementation In the Btree implementation we essentially create a temporary table, a HeapConglomerate, to hold the overflow rows and create a Btree index on the key columns. The only hitch is that our Btree implementation has a limit on the total size of a key. Therefore, instead of indexing on the key columns we will index on the key hash code. In order to search for all the rows with a given key we compute the key hash code and scan the Btree for the locations of all the rows with that hash code. We then fetch the candidate rows from the HeapConglomerate and return those with matching keys. This implementation can use the existing heap and Btree conglomerate implementations, so it should not increase the Derby footprint by much, nor should it take much development time. Hash Table Implementation - In the hash table implementation we write a new conglomerate type that implements a dynamic hash table on disk. I would use the dynamic hash algorithm of P.-A. Larson, "Dynamic Hash Tables", Communications of the ACM, 31(1988). With this algorithm a hash table of N rows is built in time O(N*log(N)) and only one disk access is needed to find a row if all the rows of its hash bucket fit on one page. Furthermore it grows the hash table by one bucket at a time, as needed, so it does not waste very much space. The hash conglomerate would be implemented using two containers: one for the first page of each bucket and the second for overflow pages. Two containers are necessary so that we can use the hash code as a page number to find the first page of a bucket. We cannot put overflow pages in the primary container because this mapping would break if we had to increase the number of buckets after creating an overflow page. The overflow container is used when a bucket has too many rows to fit on one page and when a row is too large to fit comfortably in a page. A long row is represented as a (big) hash code and a pointer to the overflow page holding the long row. Access to a long row will always take at least two disk accesses, unless one of the pages is in the cache. Note that there are two related hash functions in use, a big one and a small one. The big hash function produces a 32 bit hash code, the one used by the standard in memory hash table. The small hash code is the low order n bits of the big hash code. The small hash code is used to index the disk buckets. Under the Larson algorithm there are between 2**(n-1)+1 and 2**n buckets. Comparison -- We are interested in 4 measures: 1. the time required to get all the rows with a given key, 2. the time required to build the data structure, 3. the d
Re: On DERBY-107 : ODBC Metadata functions
I think that it is best to remain strictly compatible with JDBC. There always seem to be a few programs that depend on corner cases in the spec. More comments below. Army wrote: Having seen no other posts on this since my most recent one, this is where we stand right now with ODBC metadata support for ODBC clients running against Derby Network Server: > 1) Changes that could be made to our metadata.properties that would make > it continue to be JDBC compliant but would satisfy the ODBC > requirements. [ snip ] For these I think we change the metadata.properties > as much as possible to conform to both. I'll plan to do that. For example, if JDBC says a column is INT and ODBC says it should be SMALLINT, I'll change the metadata function in question to return SMALLINT for _BOTH_ ODBC and JDBC clients, since doing so will 1) satisfy the ODBC requirement and 2) still be compatible with JDBC apps, since a call to "ResultSet.getInt()" on a SMALLINT column will still return the correct value. Some programs look at ResultSet metadata and behave differently depending on what they see there. For instance, SMALLINT probably has a smaller display width than INT. The difference will cause the output of some programs to change. There may be some programs that look at the column type and behave differently with SMALLINTs than INTs. > 3) Extra columns in ODBC. > For getProcedureColumns, the spec explicitly says it's ok to have extra > columns. I wonder if that would be ok for other methods too. It turns out that, aside from "getProcedureColumns", the only other metadata function for which ODBC specifies columns that JDBC does not have is "getTypeInfo". That said, do people think it's okay to add an extra column to the result set of this function, or not? The Java spec doesn't explicitly say that it's okay, so it's not as clean as it is with getProcedureColumns... I don't think that we should add extra columns to the JDBC metadata ResultSets. It may cause a problem for a few programs now and it will put us in a real bind later if new columns are added to getTypeInfo in later versions of JDBC. Programs that look at the ResultSetMetadata to decide how to handle the ResuleSet will suddenly behave different. Think of display utilities such as ij. If a later version of JDBC adds a new column to the getTypeInfo ResultSet then we will be in the unenviable position of having to choose between breaking compatibility with JDBC or breaking compatibility with older versions of Derby. That's one option (add the columns to both JDBC and ODBC metadata resultsets). The other is to use whatever means are deemed best (by the Derby community) to resolve the following issue: > 2) Column Name changes. e.g JDBC returns RADIX for getProcedureColumns > whereas ODBC uses NUM_PREC_RADIX and rearranging of columns. For these > we could either make a VTI or perhaps there is some more direct internal > way to hack at the metadata. [ snip ] At this point, it seems like there are two possibilities for handling this. It's clear that we do NOT want to have 2 sets of humanly-maintained metadata functions, one for JDBC and another, _slightly_ different one for ODBC. That can lead to a maintenance headache if/when metadata processing changes in the future. That said, we could either: 1) Use VTIs (Virtual Table Interfaces), which are internal to the Derby engine and allow representation of ResultSets as virtual tables that can then be used in other table operations. With this approach, we would execute the regular, JDBC version of the metadata functions, and then we would take the result set and "massage" it into a virtual table that has ODBC-compliant column names. If we decide to go this route for extra ODBC columns, we would also use the VTI to add those extra columns. Then we would just execute a statement like "SELECT * FROM ODBCColumnsVTI" and return the result set to the client. Upsides: 1) Can base it on existing VTI structure in the engine, which makes for quicker development (I already have a VTI working for getProcedureColumns, which is probably the most complicated of the metadata functions, so it's a good proof-of-concept); 2) upgrade/downgrade, when it is implemented, should be easy: just drop the statements in metadata.properties and re-load them from the metadata.properties file of the target version of Derby. Downside: We're doing all the work in Java, when SQL and the Derby engine have the ability to do it for us. 2) Do some "under-the-covers" work to automatically generate ODBC-compliant SQL statements based on the existing JDBC statements, write the new queries out to file, and use the statements in that file for ODBC clients. This statement generation work could be done either at build time (via some kind of Ant process) or else at database creation time. The end result would in fact be two copies of the metadata functions, but only ONE of them would be manuall
Re: BackingStoreHashtable
My mail server dropped RPost's reply so I had to dig it out of the archive. I apologize for the delay and improper threading. RPost wrote: Does either 'spill to disk' approach have any other possible future use? Perhaps for implementing any other features or functionality on anyone's 'nice to have' list? Or would either btree or hash implementations be useful for only this one purpose? BackingStoreHashtable could be used to implement other operations such as INTERSECT, and EXCEPT, and in removing duplicates. I originally noticed that BackingStoreHashtable did not spill to disk when researching the implementation of INTERSECT. A dynamic disk hash table could be used for indexes. However BackingStoreHashtables are transient, single thread, data structures. A hash index must deal with multi-thread issues while BackingStoreHashtable would not want to spend time on locking. Furthermore BackingStoreHashtables only grow until they are discarded entirely. A hash index must deal with shrinking. So, with respect to BackingStoreHashtable and hash indexes, I would say close, but no cigar. Jack Jack Klebanoff wrote: Derby uses class BackingStoreHashtable to implement a hash table used in hash joins. Despite the class's name it does not spill to disk; the hash table is always in memory. The optimizer tries to avoid hash joins when the input is large because BackingStoreHashtable would use too much memory. The optimizer just works with row count estimates, so there is no guarantee that BackingStoreHashtable will not blow up. No customer has complained about this yet. I would like to work on this, changing BackingStoreHashtable to spill to disk when the hash table gets large, and changing the optimizer to understand that large hash tables are allowed, but more costly. The advantages of doing this are that 1. hash joins will not blow up when the compiler's row count estimate was low, 2. the optmizer can choose hash joins on large inputs when that is the least costly join implementation, and 3. we can use BackingStoreHashtable to implement features such as INTERSECT and GROUP BY, though I am not proposing to do so now. I am not proposing to implement a hash table that can be used as an alternative to Btrees for a secondary index. BackingStoreHashtable is used for transient data structures that are only accessed by a single thread. A secondary index implementation must deal with locking and must implement hashCode methods that are JVM independent. This is much more work and would yield a slower implementation. I propose that BackingStoreHashtable should start off using an in-memory hash table even if the estimated row count is large. That way it will use an in-memory hash table when the actual row count is small enough for the table to fit in memory. When it finds that spilling to disk is necessary BackingStoreHashtable will use the estimated row count to determine the initial number of buckets and move the in-memory entries to disk. The disk based hash table will use a linear hashing algorithm, see "External Memory Algorithms and Data Structures: Dealing withMassive Data", Jeffrey Scott Vitter, ACM Computing Surveys, Vol. 33, No. 2, June 2001, pp. 209–271. It grows the hash table one bucket at a time when the average number of entries per bucket grows beyond a threshold. The disadvantage of growing by a larger number of buckets is that the last expansion may be unnecessarity large, wasting time and space. I would appreciate it if anyone can point me to a better external hash table algorithm. The disk hash table will use overflow pages because an imperfect hash function will cause some buckets to get more than their share of entries, and because we may have duplicate keys. I have not yet looked at how the optimizer handles hash joins, so I do not yet have a proposal for how to change that. Can anyone explain how to generate a cost for the Derby optimizer? How do you compute it from an estimated number of disk accesses? Does an estimate of 2 disk accesses mean a cost of 2.0? Should the cost estimate include CPU time? If so, how do you estimate it relative to disk access cost? Putting it another way, what does a cost estimate of 1.0 (or x) mean? Comments, criticisms, questions? Jack Klebanoff
Re: setting svn:ignore
+1 Jack Samuel Andrew McIntyre wrote: Hi all, I was thinking that setting the property svn:ignore on the following files would be helpful when using 'svn stat' to determine which files have been changed files in your current view. All of these files added to svn:ignore are intermediate build files/directories. Removing these from the output of 'svn stat' will allow developers to more easily see what files have been changed in their current view. For example, instead of the output of svn stat returning this: ? tools/java/jce1_2_2.jar ? tools/java/servlet.jar ? tools/java/db2jcc.jar ? tools/java/javacc.jar ? tools/java/db2jcc_license_c.jar ? tools/java/jdbc2_0-stdext.jar ? tools/java/jakarta-oro-2.0.8.jar ? tools/java/jta-1_0_1B-classes.zip ? tools/java/osgi.jar ? java/tools/org/apache/derby/impl/tools/ij/ijConstants.java ? java/tools/org/apache/derby/impl/tools/ij/ijTokenManager.java ? java/tools/org/apache/derby/impl/tools/ij/TokenMgrError.java ? java/tools/org/apache/derby/impl/tools/ij/ij.java ? java/tools/org/apache/derby/impl/tools/ij/SimpleCharStream.java ? java/tools/org/apache/derby/impl/tools/ij/Token.java ? java/tools/org/apache/derby/impl/tools/ij/mtGrammarTokenManager.java ? java/tools/org/apache/derby/impl/tools/ij/mtGrammarConstants.java ? java/tools/org/apache/derby/impl/tools/ij/mtGrammar.java ? java/tools/org/apache/derby/impl/tools/ij/CharStream.java M java/demo/nserverdemo/NsSample.java M java/demo/nserverdemo/SimpleNetworkClientSample.java M java/demo/nserverdemo/NsSampleClientThread.java ? java/engine/state.properties ? java/engine/org/apache/derby/impl/sql/compile/ SQLParserTokenManager.java ? java/engine/org/apache/derby/impl/sql/compile/SQLParser.java ? java/engine/org/apache/derby/impl/sql/compile/TokenMgrError.java ? java/engine/org/apache/derby/impl/sql/compile/SQLParserConstants.java ? java/engine/org/apache/derby/iapi/services/cache/ClassSizeCatalog.java ? java/engine/org/apache/derby/iapi/services/sanity/SanityState.java It would just return this: M java/demo/nserverdemo/NsSample.java M java/demo/nserverdemo/SimpleNetworkClientSample.java M java/demo/nserverdemo/NsSampleClientThread.java Please let me know if you have any concerns with adding any of these files to svn:ignore lists. Otherwise I would like to check this in. Thanks, andrew Property changes on: ___ Name: svn:ignore + classes changenumber.properties jars javadoc Property changes on: tools/java ___ Name: svn:ignore + jce1_2_2.jar servlet.jar db2jcc.jar javacc.jar db2jcc_license_c.jar jdbc2_0-stdext.jar jakarta-oro-2.0.8.jar jta-1_0_1B-classes.zip osgi.jar Property changes on: java/tools/org/apache/derby/impl/tools/ij ___ Name: svn:ignore + ijConstants.java ijTokenManager.java TokenMgrError.java ij.java SimpleCharStream.java Token.java mtGrammarTokenManager.java mtGrammarConstants.java mtGrammar.java CharStream.java Property changes on: java/engine ___ Name: svn:ignore + state.properties Property changes on: java/engine/org/apache/derby/impl/sql/compile ___ Name: svn:ignore + SQLParserTokenManager.java SQLParser.java TokenMgrError.java SQLParserConstants.java Property changes on: java/engine/org/apache/derby/iapi/services/cache ___ Name: svn:ignore + ClassSizeCatalog.java Property changes on: java/engine/org/apache/derby/iapi/services/sanity ___ Name: svn:ignore + SanityState.java
Re: About improvement of DERBY-134
TomohitoNakayama wrote: (B (B> Hello. (B> (B> I have put some LOOKAHEAD to sqlgrammer.jj and (B> add some test pattern to orderby.sql. (B> (B> Would someone review patch please ? (B> (B> Best regards. (B> (B> /* (B> (B> Tomohito Nakayama (B> [EMAIL PROTECTED] (B> [EMAIL PROTECTED] (B> (B> Naka (B> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B> (B> */ (B> - Original Message - From: "TomohitoNakayama" (B> <[EMAIL PROTECTED]> (B> To: "Derby Development" (B> Sent: Sunday, February 13, 2005 4:09 PM (B> Subject: Re: About improvement of DERBY-134 (B> (B> (B>> Sorry. (B>> Mistaken. (B>> (B>> LOOKAHEAD() (B>> (B>> /* (B>> (B>> Tomohito Nakayama (B>> [EMAIL PROTECTED] (B>> [EMAIL PROTECTED] (B>> (B>> Naka (B>> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B>> (B>> */ (B>> - Original Message - From: "TomohitoNakayama" (B>> <[EMAIL PROTECTED]> (B>> To: "Derby Development" (B>> Sent: Sunday, February 13, 2005 3:42 PM (B>> Subject: Re: About improvement of DERBY-134 (B>> (B>> (B>>> Hello. (B>>> (B>>> Thank's for your reviewing. (B>>> Grammer ambiguity is very critical problem (B>>> (B>>> I will try to put LOOKUP() and consider about testing.. (B>>> (B>>> #World is not simple as I wish to be. (B>>> (B>>> Best regards. (B>>> (B>>> /* (B>>> (B>>> Tomohito Nakayama (B>>> [EMAIL PROTECTED] (B>>> [EMAIL PROTECTED] (B>>> (B>>> Naka (B>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B>>> (B>>> */ (B>>> - Original Message - From: Satheesh Bandaram (B>>> To: Derby Development (B>>> Sent: Saturday, February 12, 2005 4:10 AM (B>>> Subject: Re: About improvement of DERBY-134 (B>>> (B>>> (B>>> I think the patch is a good start. But more work needs to be done. (B>>> Based on a quick review, some of the items to be completed are: (B>>> (there may be more) (B>>> (B>>> Grammar ambiguity. SortKey() has grammar ambiguity the way the patch (B>>> is written. Since orderby expression and orderby column can both (B>>> start with an identifier, this causes ambiguity. Need to rewrite or (B>>> add lookup to avoid this. (B>>> Current patch doesn't seem to support all expressions, Ex: select i (B>>> from t1 order by i/2. So, needs more work. (B>>> Add more test cases and test outputs to show changed behavior. You (B>>> could add test cases to orderby.sql test that is already part of (B>>> functionTests/tests/lang. (B>>> I do encourage you to continue work on this ... (B>>> (B>>> Satheesh (B>>> (B>>> TomohitoNakayama wrote: (B>>> (B>>> I tried to solve DERBY-134. (B>>> Patch is attached to this mail. (B>>> (B>>> (B>>> /* (B>>> (B>>> Tomohito Nakayama (B>>> [EMAIL PROTECTED] (B>>> [EMAIL PROTECTED] (B>>> (B>>> Naka (B>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B>>> (B>>> */ (B>>> - Original Message - From: "TomohitoNakayama" (B>>> <[EMAIL PROTECTED]> (B>>> To: "Derby Development" (B>>> Sent: Wednesday, February 09, 2005 5:33 PM (B>>> Subject: Re: About improvement of DERBY-134 (B>>> (B>>> (B>>> (B>>> Woops. (B>>> Mistaken. (B>>> (B>>> (B>>> That's "DERBY-124 Sorted string columns are sorted in a case (B>>> sensitive way" (B>>> (B>>> (B>>> (B>>> That's "DERBY-134 Sorted string columns are sorted in a case (B>>> sensitive way" (B>>> (B>>> /* (B>>> (B>>> Tomohito Nakayama (B>>> [EMAIL PROTECTED] (B>>> [EMAIL PROTECTED] (B>>> (B>>> Naka (B>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B>>> (B>>> */ (B>>> - Original Message - From: "TomohitoNakayama" (B>>> <[EMAIL PROTECTED]> (B>>> To: (B>>> Sent: Wednesday, February 09, 2005 4:35 PM (B>>> Subject: About improvement of DERBY-134 (B>>> (B>>> (B>>> (B>>> Hello. (B>>> My name is Naka. (B>>> I'm very newbie in derby community. (B>>> (B>>> I'm now seeing report for derby in ASF Jira. (B>>> And found a interesting issue. (B>>> (B>>> That's "DERBY-124 Sorted string columns are sorted in a case (B>>> sensitive way" (B>>> (B>>> This issue seems to mean that we can't use complex item in order (B>>> clause. (B>>> #That title was difficult to understand a bit (B>>> (B>>> Solving this isn't useful? (B>>> Especially when we manipulate DBMS by hand. (B>>> (B>>> What I think we need to do is as next: (B>>> (B>>> 1) Allow additiveExpression() in sortKey() in "sqlgrammer.jj". 2) (B>>> Make OrderByColumn class to support additiveExpression. (B>>> (B>>> Best regards. (B>>> (B>>> /* (B>>> (B>>> Tomohito Nakayama (B>>> [EMAIL PROTECTED] (B>>> [EMAIL PROTECTED] (B>>> (B>>> Naka (B>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html (B>>> (B>>> */ (B>>> (BI have worked on Derby/Cloudscape for a few years and have even fixed (Bone or two ORDER BY bugs in the past. I have reviewed your patch. It is (Bclose, but I have some problems with it. (B (B1. sqlgrammar.jj. I think that creating a new method, (BisNonReservedKeyword() to determine whether a token is a non-reserved (Bkeyw
[PATCH] BackingStoreHashtable
I have attached a patch that causes BackingStoreHashtable to spill to
disk when it gets large. BackingStoreHashtable is used to implement hash
joins, DISTINCT, scroll insensitive cursors, and the HAVING clause. The
unpatched BackingStoreHashtable never spills to disk. This causes Derby
to sometimes run out of memory. See Jira report Derby-106.
One of the arguments of the BackingStoreHashtable constructor is the
maximum number of rows to store in memory. If this argument is
non-negative then the patched BackingStoreHashtable spills to disk when
more than that number of rows are added to the hash table.
If the max_inmemory_rowcnt argument is negative then
BackingStoreHashtable decides itself when to spill to disk. It does so
when its estimate of the size of the hash table (in bytes) grows larger
than 1% of the total memory when the BackingStoreHashtable was created.
Currently Derby only constructs BackingStoreHashtables with
max_inmemory_rowcnt = -1, so this mechanism is always used to decide
when to spill.
The disk portion of the hash table is handled in class DiskHashtable.
This does not implement a dynamic hash table data structure. Instead it
is implemented using an idea of Mike Matrigali. The rows are stored in a
standard heap conglomerate, also used by standard Derby tables. A Btree
is used to index the rows by hash code. We cannot index them by their
actual keys because our Btree implementation limits the length of a key.
In order to find a record by key DiskHashtable scans the Btree for all
rows with the same hash code and matches the retrieved rows with the
target key.
The advantage of this method is that it requires little new code because
it uses existing heap and btree conglomerate implementations. The
disadvantage is that it is slower than a dynamic hash table structure.
We expect that in most cases BackingStoreHashtable will not spill to
disk, so this trade off seems appropriate.
Issues and Future Work
The Derby community may want to consider some follow on work.
Hash join costing should be re-evaluated now that BackingStoreHashtable
can spill to disk. The optimizer can consider using hash joins on larger
tables, but it should take the cost of disk access into account. This
leads into a larger issue of optimizer costing. Our cost numbers were
derived many years ago by timing various operations on a particular
machine. That machine is probably no longer available for timing
BackingStoreHashtable, and it is probably obsolete now. We should
probably revise all of our costing numbers on a more modern machine.
We may want to implement a real dynamic disk hash structure to improve
the speed of BackingStoreHashtable when it has spilled to disk. If it
were faster we could use hash joins more often, potentially improving
the speed of some Derby joins. Furthermore BackingStoreHashtable is used
elsewhere. Our assumption that BackingStoreHashtable will seldom spill
to disk may not be correct.
In my implementation BackingStoreHashtable keeps old rows in memory
after it decides to spill new rows to disk. Mike Matrigali suggested
that it should move the old rows to disk to reduce memory usage. This
comes at the price of slowing down both the time to populate a
BackingStoreHashtable and the time to access an element. That is why I
chose not to move old rows to disk.
Jack Klebanoff
Index:
java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java
===
---
java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java
(revision 155029)
+++
java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java
(working copy)
@@ -66,7 +66,6 @@
private int
sourceRowWidth;
- private TransactionController tc;
private BackingStoreHashtable ht;
private ExecRow resultRow;
@@ -87,6 +86,8 @@
private GeneratedMethod closeCleanup;
+private boolean keepAfterCommit;
+
/**
* Constructor for a ScrollInsensitiveResultSet
*
@@ -110,6 +111,7 @@
optimizerEstimatedRowCount, optimizerEstimatedCost);
this.source = source;
this.sourceRowWidth = sourceRowWidth;
+keepAfterCommit = activation.getResultSetHoldability();
maxRows = activation.getMaxRows();
if (SanityManager.DEBUG)
{
@@ -160,7 +162,7 @@
* We need BackingStoreHashtable to actually go to disk when it
doesn't fit.
* This is a known limitation.
*/
- ht = new BackingStoreHashtable(tc,
+ ht = new BackingStoreHashtable(getTransactionContr
INTERSECT and EXCEPT Design
Attached is an HTML document describing the design of the INTERSECT and EXCEPT operators, which I contributed to Derby. Perhaps it can be posted on the Derby web site in the Papers/Derby Engine section. Jack Klebanoff Title: Intersect & Except Design Intersect & Except Design Jack Klebanoff Feb. 22 2005 Introduction This paper describes the implementation of the INTERSECT and EXCEPT operators. This paper assumes basic familiarity with SQL and the language (compiler) portion of Derby. The INTERSECT and EXCEPT operators operate on table expressions producing the intersection and difference, respectively. The syntax is (roughly): queryExpression INTERSECT [ALL] queryExpression queryExpression EXCEPT [ALL] queryExpression By default these operators remove duplicates, which can occur if there are duplicates in the inputs. If ALL is specified then duplicates are not removed. If t1 has m copies of row R and t2 has n copies then t1 INTERSECT ALL t2 returns min(m,n) copies of R, and t1 EXCEPT ALL t2 returns max( 0, m-n) copies of R. The EXCEPT operator has the same precedence as UNION. INTERSECT has higher precedence. The implementation is spread across several classes, primarily SetOpResultSet, which handles execution of the INTERSECT and EXCEPT operators, extensions to QueryTreeNode, which handle binding, optimization, and code generation, and the parser. Execution The INTERSECT and EXCEPT operations are executed in a similar fashion, by class SetOpResultSet. The two inputs are sorted separately. The sort key consists of all the columns. Then SetOpResultSet scans the two inputs simultaneously. The INTERSECT operation outputs approximately any row from its left input that is also found in its right output. The EXCEPT operation outputs approximately any row from its left input that is not found in its right output. Handling of duplicates complicates the picture a little, which is the reason for the "approximately" caveat. However the scans proceed in a strictly forward direction; there is no need for backtracking. If the left and right inputs have N and M rows respectively the sorts take time O(N*log(N) + M*log(M)). The final scan takes time O(N + M). So the time for the whole operation is O(N*log(N) + M*log(M)). Alternative Execution Plans Other implementations are possible. INTERSECT and EXCEPT can be implemeted using hash tables. You can build a hash table on the right input. Then, if you somehow know that the rows in the left input are unique, you can scan through the rows of the left input and output each one that is found/not found in the hash table. If the size of the right input is known at the start then the hash table can be built in time O(M). If the size is not known ahead of time then the hash table can be built in time O(M*log(M)). If the hash function is good then the final scan step takes time O(N). Keeping track of duplicates slows things down. You can keep a hash table of output rows or mark found rows in the right input hash table. Hash tables were rejected because, when the INTERSECT and EXCEPT operations were implemeted, BackingStoreHashtable did not spill to disk. A hash table implementation could exhaust memory. If the right input is a base table with a unique index then we could forgo sorting the right input and use the index to find rows that match the left rows. Unless the left rows are known to be unique we must sort them or build a hash table to handle duplicates. Using a hash table to eliminate duplicates the time to perform the INTERSECT or EXCEPT would be O(N*log(M) + N'*log(N')), where N' is the number of output rows (N' < N). So this is usually faster than the sort merge method, but it cannot always be used. The current implementation was chosen because it always provides at least decent speed and memory utilization, and in many, though certainly not all cases, it is the best implementation. We could have provided several implementations and let the optimizer choose the best, but this does not seem worthwhile for operations that are seldom used. Binding, Optimization, and Code Generation The IntersectOrExceptNode class handles compilation of both INTERSECT and EXCEPT operations, because they are implemented similarly. We do very little in the way of optimization because we have only implemented one execution plan; the optimizer has nothing to choose from. The INTERSECT and EXCEPT operators are bound much like the UNION operator. The bind methods are all found in super class SetOperatorNode, which is shared with UnionNode. IntersectOrExceptNode generates OrderByNodes for its inputs at the start of optimization, in the preprocess phase. Any column ordering can be used for the sorts, as long as the same one is used for both the left and right inputs. IntersectOrExceptNode tries to be a little clever about picking the column ordering for the sorts. If the INTERSECT or EXCEPT output must be ordered then IntersectOrEx
Re: INTERSECT and EXCEPT Design
RPost wrote: >Jack Klebanoff wrote: >The syntax is (roughly): />queryExpression/ INTERSECT [ALL] /queryExpression/ />queryExpression/ EXCEPT [ALL] /queryExpression/ Although DISTINCT is implicit if ALL is not specified the SQL standard also allows the DISTINCT operator to be specified explicitly. Would it make sense to do that in Derby also? INTERSECT [ALL | DISTINCT] and EXCEPT [ALL | DISTINCT] Other relational databases do not all support the "DISTINCT" keyword, so applications that use it will not be portable. That being said, I think that adding the "DISTINCT" keyword would be a good project for a neophyte who wants to get his or her feet wet in the Derby parser. > The architecture of the Derby optimizer makes it difficult to do further optimizations. Yikes! Tread lightly there hoss, I think he (Jeffrey Lichtman) is listening now. His mail has been helpful in the past. I hope that I can stimulate more. I don't think that we should spend a lot of effort on optimizing the INTERSECT and EXCEPT operators. It isn't used often and the performance of the current implementation is always at least decent. We should put our optimization efforts into optimizing the performance of other operations. (Please speak up if you think that INTERSECT or EXCEPT will be used often). >The UNION and EXCEPT operators have the same precedence. The INTERSECT operator has higher >precedence, so >t1 UNION ALL t2 UNION t3 > is equivalent to >(t1 UNION ALL t2) UNION t3 Yes but it is not equivalent to t1 UNION ALL (t2 UNION t3) Anyway, I think you meant to compare UNION (or UNION ALL) and INTERSECT here (as you did EXCEPT/INTERSECT below this) to illustrate the higher precedence of INTERSECT. Yes. I included the UNION/UNION ALL example because it is important to get the associativity right even in the UNION case. I have updated my paper (attached) to say so explicitly, and include an example with UNION and EXCEPT. >IntersectOrExceptNode uses the ORDER BY columns as the most significant part of the sort key for its inputs. Any >columns not in the ORDER BY list are tacked on to the least significant part of the sort keys of the inputs. This >ensures that the output of the INTERSECT or EXCEPT will be properly ordered without an additional sort step You sly dog, you. Is any check made for primary keys or unique indexes on any of the select columns for each of the tables? No. It isn't very easy for the set operators to find out the structure of the operands or to deduce whether there may be duplicate rows in either of the inputs. If the IntersectOrExceptNode class could know that one of the operands would use a unique index it could use that column in its sort key, avoiding a sort. However the structure of the optimizer requires that orderby lists be generated in the optimization preprocess phase, before the main body of the optimizer has started. So it is hard to use knowledge about unique keys if we could get it. Jack Title: Intersect & Except Design Intersect & Except Design Jack Klebanoff Feb. 22 2005 Introduction This paper describes the implementation of the INTERSECT and EXCEPT operators. This paper assumes basic familiarity with SQL and the language (compiler) portion of Derby. The INTERSECT and EXCEPT operators operate on table expressions producing the intersection and difference, respectively. The syntax is (roughly): queryExpression INTERSECT [ALL] queryExpression queryExpression EXCEPT [ALL] queryExpression By default these operators remove duplicates, which can occur if there are duplicates in the inputs. If ALL is specified then duplicates are not removed. If t1 has m copies of row R and t2 has n copies then t1 INTERSECT ALL t2 returns min(m,n) copies of R, and t1 EXCEPT ALL t2 returns max( 0, m-n) copies of R. The EXCEPT operator has the same precedence as UNION. INTERSECT has higher precedence. The implementation is spread across several classes, primarily SetOpResultSet, which handles execution of the INTERSECT and EXCEPT operators, extensions to QueryTreeNode, which handle binding, optimization, and code generation, and the parser. Execution The INTERSECT and EXCEPT operations are executed in a similar fashion, by class SetOpResultSet. The two inputs are sorted separately. The sort key consists of all the columns. Then SetOpResultSet scans the two inputs simultaneously. The INTERSECT operation outputs approximately any row from its left input that is also found in its right output. The EXCEPT operation outputs approximately any row from its left input that is not found in its right output. Handling of duplicates complicates the picture a little, which is the reason for the "approximately" caveat. However the scans proceed in a strictly forward direction; there is no need for backtracking. If the left and right inputs have N and M rows respectively the sorts tak
Re: Overly conservative on reserved words?
Jeremy Boynes wrote: Daniel John Debrunner wrote: Jeremy Boynes wrote: Reserving additional words from the second group poses a bigger issue as users' may have databases out there already using these words as identifiers. The smoothest path is probably to give people an indication of which words will need to be reserved at some point and hence should be avoided; it is better for us to do this earlier than later. Actually having even keywords defined as reserved by the SQL Standard reserved in Derby has caused problems. I recently changed LOCAL not to be a reserved word as other databases do not enforce it. We probably need some set rules, but reserving because the SQL standard says so it not the approach taken by other products. 'The nice thing about standards is there are so many to choose from' :-) Especially true with 4 versions of ISO SQL and N vendor-specific dialects. One of the issues users face is that the spec evolves and products implement newer versions, words that were not reserved before now need to be. Ideally we would not need to reserve anything, giving users complete freedom on how they name their things; however, that would make the parser, lets say, challenging. Short of this ideal, I think we should compormise and only reserve words needed to resolve ambiguity in the parser; that lets users decide how much portability they need. To help them do that I think it's useful for us to indicate direction and what is likely to be reserved (SAVEPOINT) vs. what isn't (PERCENTILE_DISC). Is it worth raising a warning on DDL operations that define objects that conflict with SQL's reservation list? -- Jeremy I am uneasy about offering extensions to the SQL standard. Every extension is a "minnow trap" making it difficult for developers to port applications from Derby to other relational databases. Many, perhaps most, developers are not that vigilant about all the fine points of standards and portability. Often they do not consciously decide how much portability they need, but use whatever gets their application going and find out later whether it is portable. We have to decide what Derby's goals are. Currently part of our charter says "developers can later migrate to other databases if they so choose". As long as this remains an important part of Derby's charter we should try to keep Derby a subset of the latest SQL standard, and we should avoid features that are incompatible with leading enterprise relational database management systems. (A real minnow trap is a device used to catch minnows, small bait fish. The trap is a cylindrical cage with funnel like openings at either end. You put some food in the cage. Minnows find it easy to swim in but hard to swim out). Jack
Re: [PATCH] BackingStoreHashtable
Mike Matrigali wrote: Any idea if many more queries will now spill to disk? I am worried that many more will, and now queries will run slower. Seems like there should be a better connection between the optimizer estimate to use in memory hash and what actually is done in memory. As I understand it now the Optimizer costs hash only as in memory, if it's estimates say it won't fit into memory it chooses a different plan. I assume the other plan is more efficient than a hash plan which may have to go to disk for every probe (even if pages are cached going to latched pages on every probe is a lot more expensive than in memory hash). My guess is no one did the work to set max_inmemory_rowcnt, because the backing store stuff wasn't implemented yet. I have not read the code yet. Is the 1% of all memory, or 1% of free memory? It is 1% of Runtime.getRuntime().totalMemory(). Changing the costing is difficult. The FromBaseTable.estimateCost method uses the JoinStrategy|.|multiplyBaseCostByOuterRows method to compute the cost of the join as either the cost of constructing the inner table or the number of rows in the outer table times the cost of constructing the inner table. HashJoinStrategy|.|multiplyBaseCostByOuterRows returns false, so the cost of a hash join is estimated as the cost of constructing the hash table, and independent of the number of rows in the outer table. This is a reasonable approximation if the hash table is in memory, the outer table is not enormous, and the hash function does a good job. If some of the hash table is on disk then neither join cost estimate method is very good. Then cost of a hash join is approximately hashtableBuildCost + outerTableRowCount*hashProbeCost, where hashProbeCost is the expected cost of one probe into the hash table. This will be a function of the fraction of inner table rows on disk, and the number of rows on disk. The JoinStrategy interface has a method, estimateCost, that looks like it could be made to do the trick, but for some reason FromBaseTable.estimateCost does not use it. The estimate of the inner table row count is used to estimate the size of the hash table. If it is large we do not use a hash join at all. If we try to include the disk access cost in our hash join cost estimate we have to use the inner table row count estimate to estimate the fraction of rows on disk. But if the inner table passes the maximum size test this fraction is zero if the row count estimate is correct, or very small if we assume some statistical variation. It doesn't make sense to include the disk hash table probe cost in the join cost estimate unless we also ditch the maximum size restriction on hash joins. This might be a good idea, but I would like to fix BackingStoreHashtable so that neither hash joins, nor DISTINCT, nor scroll insensitive cursors blow up before tackling join costing. I think that the right thing to do is for HashJoinStrategy to pass the correct maximum in-memory row count to BackingStoreHashtable so that the two are following the same playbook with respect to spilling. I will work on this. Jack
[jira] Closed: (DERBY-106) HashJoinStrategy leads to java.lang.OutOfMemoryError
[ http://issues.apache.org/jira/browse/DERBY-106?page=history ]
Jack Klebanoff closed DERBY-106:
Resolution: Fixed
Fix Version: 10.1.0.0
Subversion revision 157861
> HashJoinStrategy leads to java.lang.OutOfMemoryError
>
>
> Key: DERBY-106
> URL: http://issues.apache.org/jira/browse/DERBY-106
> Project: Derby
> Type: Bug
> Reporter: Gerald Khin
> Fix For: 10.1.0.0
>
> My application is running out of memory: I encounterd a
> java.lang.OutOfMemoryError. I used -Xmx256M. Unfortunatley, I cannot spend an
> arbitrary amount of JVM memory.
> Then, I commented out the line in class OptimizerFactoryImpl which was adding
> the HashJoinStrategy to the set of Join strategies:
> if (joinStrategySet == null)
> {
> //JoinStrategy[] jss = new JoinStrategy[2];
> JoinStrategy[] jss = new JoinStrategy[1];
> jss[0] = new NestedLoopJoinStrategy();
> //jss[1] = new HashJoinStrategy();
> joinStrategySet = jss;
> }
> And with these changes the OutOfMemoryError has gone away! And it works even
> with -Xmx128M!!!
> So I guess that there is a major memory issue with this HashJoin strategy
> implementation.
> If it turns out to be too complicated to make the memory consumption more
> predicatble or even bounded to some configurable limit, then I need at least
> as a workaround a way to turn off the HashJoin strategy completely: I did it
> by patching and building my own derby.jar, but if there would be an official
> solution with some kind of switch like a system property, it would be great!
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-176) Derby throws ERROR XBCM1: Java linkage error thrown during load of generated class org.apache.derby.exe.aced07c066x0102xca87x3319x00004aa5686e1 during execution of large query
[ http://issues.apache.org/jira/browse/DERBY-176?page=comments#action_61425 ] Jack Klebanoff commented on DERBY-176: -- I think that there are 3 approaches to solving the problem of the Derby code generator exceeding JVM class size limits: 1. Reduce the amount of code that is generated. 2. Split oversized generated methods into multiple methods. 3. Split oversized generated classes into multiple classes. We may want (need) to use all three. We may be able to reduce the amount of generated code by looking for common sub-expressions in the optimized query tree and putting the code for them in separate methods. Or we may be able to reuse intermediate result sets. Perhaps just sharing code for equivalent ProjectResrictNodes and SubQueryNodes would be enough. Splitting generated classes may be difficult. Derby assumes that each statement only generates one class. It might be possible to put the byte code for the overflow classes into saved objects of the generated ExecPreparedStatement. The main generated class would load the byte code when needed. > Derby throws ERROR XBCM1: Java linkage error thrown during load of generated > class org.apache.derby.exe.aced07c066x0102xca87x3319x4aa5686e1 during > execution of large query > --- > > Key: DERBY-176 > URL: http://issues.apache.org/jira/browse/DERBY-176 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.0.0 > Reporter: Kathey Marsden > Assignee: Kathey Marsden > > When executing a large query or oather large operations, Derby throws a java > linkage exception. > This is because the generated byte code exceeds the JVM limits for method > sizes constant pool entries etc, the amount of code in a conditional etc. > The attached repro demonstrates the problem but the problem can also occur > for other operations that generate lots of byte code. The repro is just a > new functional test, > so should be copied to derbyTesting/functionTests/lang/largeCodeGen.java and > run like > java -Djvmflags=-Xmx512M org.apache.derbyTesting.harness.RunTest > lang/largeCodeGen > When this problem is fixed additional scenarios should be added to this test. > ERROR XBCM1: Java linkage error thrown during load of > generated class org.apache.derby.exe.aced07c066x0102xca87x3319x4aa5686e1. > at > org.apache.derby.iapi.error.StandardException.newException(StandardException.java:315) > at > org.apache.derby.impl.services.reflect.DatabaseClasses.loadGeneratedClass(DatabaseClasses.java:162) > at > org.apache.derby.impl.services.bytecode.GClass.getGeneratedClass(GClass.java:59) > at > org.apache.derby.impl.sql.compile.ExpressionClassBuilder.getGeneratedClass(ExpressionClassBuilder.java:920) > at > org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:270) > at > org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:432) > at > org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107) > at > org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688) > at > org.apache.derby.impl.jdbc.EmbedPreparedStatement.(EmbedPreparedStatement.java:118) > at > org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(EmbedPreparedStatement20.java:82) > at > org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(EmbedPreparedStatement30.java:62) > at > org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Driver30.java:92) > at > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:675) > at > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:519) > at > org.apache.derbyTesting.functionTests.tests.lang.largeCodeGen.main(largeCodeGen.java:86) > Exception in thread "main" -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-181) Function/Procedure specific name documented but not supported
Function/Procedure specific name documented but not supported - Key: DERBY-181 URL: http://issues.apache.org/jira/browse/DERBY-181 Project: Derby Type: Bug Components: Documentation Reporter: Jack Klebanoff Priority: Minor The documentation for Derby found at http://incubator.apache.org/derby/manuals/reference/sqlj27.html#CREATE+FUNCTION+Statement says that "SPECIFIC name" is allowed as a function and procedure element. However an SQLException is thrown if you use it. The message says "ERROR 0A000: Feature not implemented: SPECIFIC identifier." -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-183) Parameter names required in CREATE FUNCTION
Parameter names required in CREATE FUNCTION --- Key: DERBY-183 URL: http://issues.apache.org/jira/browse/DERBY-183 Project: Derby Type: Bug Versions: 10.0.2.0 Reporter: Jack Klebanoff Priority: Minor A statement like create function s2.f2( char(8), integer) returns int language java parameter style java external name 'myclass.mymethod' fails with the message ERROR 42X01: Syntax error: Encountered "char" at line 1, column 24 However create function s2.f2( p1 char(8), p2 integer) returns int language java parameter style java external name 'myclass.mymethod' is accepted. The Derby documentation (at http://incubator.apache.org/derby/manuals/reference/sqlj27.html#CREATE+PROCEDURE+Statement), the SQL2003 standard, and DB2 all agree that the parameter name is optional. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-219) EXCEPT/INTERSECT fails in views
[ http://issues.apache.org/jira/browse/DERBY-219?page=history ] Jack Klebanoff updated DERBY-219: - Attachment: intersect.diff [PATCH] I have attached a fix. > EXCEPT/INTERSECT fails in views > --- > > Key: DERBY-219 > URL: http://issues.apache.org/jira/browse/DERBY-219 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.1.0.0 > Environment: Linux 2.4.20-31.9 > java version "1.4.2_02" > Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_02-b03) > Java HotSpot(TM) Client VM (build 1.4.2_02-b03, mixed mode) > Reporter: Bernt M. Johnsen > Attachments: intersect.diff > > EXCEPT or INTERSECT in views give: > ERROR XJ001: Java exception: 'ASSERT FAILED costEstimate is not expected to > be null for org.apache.derby.impl.sql.compile.SelectNode: > org.apache.derby.iapi.services.sanity.AssertFailure > To recreate, run: > create table tab1 (i integer primary key); > insert into tab1 values (1),(2),(3),(4); > create table tab2 (i integer primary key); > insert into tab2 values (2),(4); > create view view1 as select * from tab1 intersect select * from tab2; > select * from view1; > . -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-219) EXCEPT/INTERSECT fails in views
[ http://issues.apache.org/jira/browse/DERBY-219?page=comments#action_62747 ] Jack Klebanoff commented on DERBY-219: -- The bug affects any subquery. So select * from (select * from tab1 intersect select * from tab2); fails in the same way that as the query that used a view. > EXCEPT/INTERSECT fails in views > --- > > Key: DERBY-219 > URL: http://issues.apache.org/jira/browse/DERBY-219 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.1.0.0 > Environment: Linux 2.4.20-31.9 > java version "1.4.2_02" > Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_02-b03) > Java HotSpot(TM) Client VM (build 1.4.2_02-b03, mixed mode) > Reporter: Bernt M. Johnsen > Attachments: intersect.diff > > EXCEPT or INTERSECT in views give: > ERROR XJ001: Java exception: 'ASSERT FAILED costEstimate is not expected to > be null for org.apache.derby.impl.sql.compile.SelectNode: > org.apache.derby.iapi.services.sanity.AssertFailure > To recreate, run: > create table tab1 (i integer primary key); > insert into tab1 values (1),(2),(3),(4); > create table tab2 (i integer primary key); > insert into tab2 values (2),(4); > create view view1 as select * from tab1 intersect select * from tab2; > select * from view1; > . -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-234) Documentation of DateTime types is incomplete
Documentation of DateTime types is incomplete
-
Key: DERBY-234
URL: http://issues.apache.org/jira/browse/DERBY-234
Project: Derby
Type: Bug
Components: Documentation
Versions: 10.0.2.0
Reporter: Jack Klebanoff
Priority: Minor
The documentation for datatypes DATE, TIME, and TIMESTAMP is incomplete. The
documentation says that DATE, TIME, and TIMESTAMP accept any values accepted by
the java.sql.Date, java.sql.Time, and java.sql.Timestamp classes respectively.
Derby accepts a number of string formats:
DATE:
-mm-dd
mm/dd/
dd.mm.
TIME:
hh:mm[:ss]
hh.mm[.ss]
hh[:mm] {AM | PM}
TIMESTAMP:
-mm-dd-hh[.mm[.ss[.nn]]]
-mm-dd hh[:mm[:ss[.nn]]]
The year must always have 4 digits. Months, days, and hours may have one or two
digits. Minutes and seconds, if present, must have two digits. Nanoseconds, if
present may have 1 to 6 digits.
Derby also accepts strings in the locale specific datetime format, using the
locale of the database server. If there is an ambiguity the built in formats
above take precedence.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-12) Quoted table names mishandled in select list expansion
[ http://issues.apache.org/jira/browse/DERBY-12?page=all ] Jack Klebanoff reassigned DERBY-12: --- Assign To: Jack Klebanoff > Quoted table names mishandled in select list expansion > -- > > Key: DERBY-12 > URL: http://issues.apache.org/jira/browse/DERBY-12 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.0 > Reporter: Ramandeep Kaur > Assignee: Jack Klebanoff > Priority: Minor > > Opening this bug on behalf of Satheesh Bandaram. > -- > Select list expansion is incorrect when there is a quoted table > name with an embedded period. Consider the following SQL: > create table "S1.T1" (id int not null primary key, d1 int); > create schema s1; > create table s1.t1 (id int not null primary key, d2 int); > select s1.t1.* from "S1.T1"; > select "S1.T1".* from s1.t1; > The select statements should both throw errors, because s1.t1 > and "S1.T1" are different tables. However Derby does not > throw an error. However, the following SQL does throw an error, > as it should. > select "S1.T1".id from s1.t1; > select s1.t1.id from "S1.T1" -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-13) Quoted names with embedded period mishandled in from list
[ http://issues.apache.org/jira/browse/DERBY-13?page=all ] Jack Klebanoff reassigned DERBY-13: --- Assign To: Jack Klebanoff > Quoted names with embedded period mishandled in from list > - > > Key: DERBY-13 > URL: http://issues.apache.org/jira/browse/DERBY-13 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.0 > Reporter: Ramandeep Kaur > Assignee: Jack Klebanoff > Priority: Minor > Attachments: Derby-13.diff, Derby-13.patch, derbylang_report.txt > > Opening this bug on behalf of Satheesh Bandaram > - > The compiler mishandles quoted names with embedded periods when > checking uniqueness of table names in the from list of a > SELECT. Consider the following SQL: > create table "S1.T1" (id int not null primary key, d1 int); > create schema s1; > create table s1.t1 (id int not null primary key, d2 int); > select * from s1.t1, "S1.T1" where s1.t1.id = "S1.T1".id; > Derby throws an error on the final SELECT statement: > "ERROR 42X09: The table or alias name 'S1.T1' is used more than > once in the FROM list". However s1.t1 and "S1.T1" are different > tables. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-13) Quoted names with embedded period mishandled in from list
[ http://issues.apache.org/jira/browse/DERBY-13?page=all ] Jack Klebanoff reassigned DERBY-13: --- Assign To: (was: Jack Klebanoff) > Quoted names with embedded period mishandled in from list > - > > Key: DERBY-13 > URL: http://issues.apache.org/jira/browse/DERBY-13 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.0 > Reporter: Ramandeep Kaur > Priority: Minor > Attachments: Derby-13.diff, Derby-13.patch, derbylang_report.txt > > Opening this bug on behalf of Satheesh Bandaram > - > The compiler mishandles quoted names with embedded periods when > checking uniqueness of table names in the from list of a > SELECT. Consider the following SQL: > create table "S1.T1" (id int not null primary key, d1 int); > create schema s1; > create table s1.t1 (id int not null primary key, d2 int); > select * from s1.t1, "S1.T1" where s1.t1.id = "S1.T1".id; > Derby throws an error on the final SELECT statement: > "ERROR 42X09: The table or alias name 'S1.T1' is used more than > once in the FROM list". However s1.t1 and "S1.T1" are different > tables. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-12) Quoted table names mishandled in select list expansion
[ http://issues.apache.org/jira/browse/DERBY-12?page=all ] Jack Klebanoff reassigned DERBY-12: --- Assign To: (was: Jack Klebanoff) > Quoted table names mishandled in select list expansion > -- > > Key: DERBY-12 > URL: http://issues.apache.org/jira/browse/DERBY-12 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.0 > Reporter: Ramandeep Kaur > Priority: Minor > > Opening this bug on behalf of Satheesh Bandaram. > -- > Select list expansion is incorrect when there is a quoted table > name with an embedded period. Consider the following SQL: > create table "S1.T1" (id int not null primary key, d1 int); > create schema s1; > create table s1.t1 (id int not null primary key, d2 int); > select s1.t1.* from "S1.T1"; > select "S1.T1".* from s1.t1; > The select statements should both throw errors, because s1.t1 > and "S1.T1" are different tables. However Derby does not > throw an error. However, the following SQL does throw an error, > as it should. > select "S1.T1".id from s1.t1; > select s1.t1.id from "S1.T1" -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-84) Column aliasing could simplify queries
[ http://issues.apache.org/jira/browse/DERBY-84?page=comments#action_64077 ] Jack Klebanoff commented on DERBY-84: - The problem described by Bob Gibson in the main body of this report and the problem described by Micah Spears in his comment of 12/Jan/05 seem different to me. This bug report complains that Derby does _not_ support the use of column aliases in WHERE clauses. The problem described in the 12/Jan/05 comment is that Derby _requires_ the use of column aliases in an ORDER BY clause for columns that have aliases. The comment complains that Derby should, but currently does not, allow the original column names in an ORDER BY clause if the column has an alias. I think that the 12/Jan/05 comment is a duplicate of Derby-127, but this bug report (Derby-84) is different. > Column aliasing could simplify queries > -- > > Key: DERBY-84 > URL: http://issues.apache.org/jira/browse/DERBY-84 > Project: Derby > Type: New Feature > Components: SQL > Versions: 10.0.2.0 > Reporter: Bob Gibson > Priority: Minor > > Currently, one can not use an alias to simplify queries. For example, being > able to alias "LongDescriptiveColumnName" AS LDCN would allow one to use the > alias elsewhere in the query, e.g., the WHERE clause: > SELECT LongDescriptiveColumnName AS LDCN FROM MyTable WHERE LDCN LIKE > '%testing%'; > The current result is a message like: > ERROR 42X04: Column 'LDCN' is not in any table in the FROM list or it appears > within a join specification and is outside the scope of the join > specification or it appears in a HAVING clause and is not in the GROUP BY > list. If this is a CREATE or ALTER TABLE statement then 'LDCN' is not a > column in the target table. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
[ http://issues.apache.org/jira/browse/DERBY-127?page=all ] Jack Klebanoff reassigned DERBY-127: Assign To: Jack Klebanoff > Aliased Columns not recognized after "group by... order by" combination > --- > > Key: DERBY-127 > URL: http://issues.apache.org/jira/browse/DERBY-127 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.1 > Environment: Windows XP Professional > JDK 1.4 > (first found in relation to Mondrian 1.0.1) > Reporter: Thomas Browne > Assignee: Jack Klebanoff > > I've been doing work to try and integrate Derby with the Mondrian ROLAP > engine, which has uncovered a bug in Derby when a query involves column > aliasing, a group by clause, and an order by clause. > For example: Mondrian will generate the following query: > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by STORE.STORE_COUNTRY, STORE.STORE_STATE, > STORE.STORE_CITY, STORE.STORE_NAME > which should be valid SQL. I have tested this query outside of the Mondrian > environment and still receive the same error which is: > "Column 'STORE.STORE_COUNTRY' is not in any table in the FROM list or it > appears within a join specification and is outside the scope of the join > specification or it appears in a HAVING clause and is not in the GROUP BY > list. > SQL State: 42x04 > Error Code: 3 > However, if I remove any one of the three elements (aliasing, group by, order > by) or if the order by uses the aliased names, the query works. It is only > the combination of all 3 elements that is causing a problem. > [ie. all of the following queries work correctly] > select STORE.STORE_COUNTRY , STORE.STORE_STATE , STORE.STORE_CITY , > STORE.STORE_NAME , STORE.STORE_TYPE , STORE.STORE_MANAGER , STORE.STORE_SQFT > , STORE.GROCERY_SQFT , STORE.FROZEN_SQFT , STORE.MEAT_SQFT , STORE.COFFEE_BAR > , STORE.STORE_STREET_ADDRESS from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by STORE.STORE_COUNTRY, STORE.STORE_STATE, > STORE.STORE_CITY, STORE.STORE_NAME > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE order by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by c0,c1,c2,c3 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-273) The derbynet/dataSourcePermissions_net.java test fails intermittently
The derbynet/dataSourcePermissions_net.java test fails intermittently - Key: DERBY-273 URL: http://issues.apache.org/jira/browse/DERBY-273 Project: Derby Type: Bug Environment: 1.4.2 JVM (both Sun and IBM) Reporter: Jack Klebanoff The test fails in the derbyall/derbynetclientmats/derbynetmats suite stack with the following diff: *** Start: dataSourcePermissions_net jdk1.4.2 DerbyNetClient derbynetmats:derbynetmats 2005-05-11 04:24:11 *** 17a18,19 > org.apache.derby.iapi.services.context.ShutdownException: > agentThread[DRDAConnThread_2,5,derby.daemons] Test Failed. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
[ http://issues.apache.org/jira/browse/DERBY-127?page=all ] Jack Klebanoff resolved DERBY-127: -- Resolution: Fixed Fix Version: 10.1.0.0 SVN revision 169744 > Aliased Columns not recognized after "group by... order by" combination > --- > > Key: DERBY-127 > URL: http://issues.apache.org/jira/browse/DERBY-127 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.1 > Environment: Windows XP Professional > JDK 1.4 > (first found in relation to Mondrian 1.0.1) > Reporter: Thomas Browne > Assignee: Jack Klebanoff > Fix For: 10.1.0.0 > > I've been doing work to try and integrate Derby with the Mondrian ROLAP > engine, which has uncovered a bug in Derby when a query involves column > aliasing, a group by clause, and an order by clause. > For example: Mondrian will generate the following query: > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by STORE.STORE_COUNTRY, STORE.STORE_STATE, > STORE.STORE_CITY, STORE.STORE_NAME > which should be valid SQL. I have tested this query outside of the Mondrian > environment and still receive the same error which is: > "Column 'STORE.STORE_COUNTRY' is not in any table in the FROM list or it > appears within a join specification and is outside the scope of the join > specification or it appears in a HAVING clause and is not in the GROUP BY > list. > SQL State: 42x04 > Error Code: 3 > However, if I remove any one of the three elements (aliasing, group by, order > by) or if the order by uses the aliased names, the query works. It is only > the combination of all 3 elements that is causing a problem. > [ie. all of the following queries work correctly] > select STORE.STORE_COUNTRY , STORE.STORE_STATE , STORE.STORE_CITY , > STORE.STORE_NAME , STORE.STORE_TYPE , STORE.STORE_MANAGER , STORE.STORE_SQFT > , STORE.GROCERY_SQFT , STORE.FROZEN_SQFT , STORE.MEAT_SQFT , STORE.COFFEE_BAR > , STORE.STORE_STREET_ADDRESS from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by STORE.STORE_COUNTRY, STORE.STORE_STATE, > STORE.STORE_CITY, STORE.STORE_NAME > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE order by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by c0,c1,c2,c3 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-278) Document INTERSECT and EXCEPT operations
[ http://issues.apache.org/jira/browse/DERBY-278?page=comments#action_65623 ] Jack Klebanoff commented on DERBY-278: -- There is a problem in document rrefsqlj1083019.html. Item 4 reads: "... in rule rrefsqlj1083019.html#rrefsqlj1083019__sqlj30695". I think that we need a more descriptive text for the link. Document rrefsqlj21571.html is incorrect about the precedence of the intersect, except, and union operators. It confuses precedence with the number of duplicates in the output. The document states "... INTERSECT, EXCEPT, or UNION operations. These operations are evaluated from left to right when no parentheses are present". This is incorrect. In the absence of parentheses INTERSECTs are evaluated before EXCEPT, or UNION operations, but EXCEPT and UNION operations are evaluated from left to right. That is, INTERSECT has higher precedence than EXCEPT or UNION and EXCEPT and UNION have the same precedence. The sentence "You can combine two queries into one using the ALL parameter" is misleading. The set operators combine two queries into one with or without the ALL parameter. Change the sentence to something like: "The ALL and DISTINCT keywords determine whether duplicates are eliminated from the result. If you specify DISTINCT then there will be no duplicate rows in the result. If you specify ALL then there may be duplicates in the result, depending on whether there were duplicates in the input. DISTINCT is the default: if you specify neither ALL nor DISTINCT then duplicates will be eliminated". The next section in rrefsqlj21571.html is titled "Precedence of UNION, INTERSECT, and EXCEPT" and starts with "There are rules of precedence in how many duplicates will be contained...". Precedence has nothing to do with the number of duplicates in the output. Change the title to "Duplicates in UNION/INTERSECT/EXCEPT ALL". Include the previous paragraph in this section. Remove the sentence "There are rules of precedence ..." I think that the examples in rrefsqlj21571.html should have a header, e.g. "Examples". My first thought was that the examples were part of the discussion of the number of duplicates. A header would separate the two. > Document INTERSECT and EXCEPT operations > > > Key: DERBY-278 > URL: http://issues.apache.org/jira/browse/DERBY-278 > Project: Derby > Type: Task > Components: Documentation > Environment: All > Reporter: Jeff Levitt > Assignee: Jeff Levitt > Priority: Minor > Fix For: 10.1.0.0 > Attachments: intersectdocs.zip > > Currently the documentation does not discuss using INTERSECT and EXCEPT in > queries. We just added the DISTINCT keyword recently to those operations as > well as to the UNION operation, so I will attempt to document the DISTINCT > keyword for UNION, and add the INTERSECT and EXCEPT syntax as I go along. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-81) Datetime datatypes should allow arithmetic operations on them.
[ http://issues.apache.org/jira/browse/DERBY-81?page=comments#action_66203 ] Jack Klebanoff commented on DERBY-81: - As of revision 178061 Derby supports the ODBC/JCBC TIMESTAMPADD and TIMESTAMPDIFF escape functions. So the query in the request can be written as SELECT * FROM log WHERE modified_time > TIMESTAMPADD( SQL_TSI_MONTH, 1, CURRENT_TIMESTAMP) Unfortunately, the major SQL databases all seem to implement datetime arithmetic in different and incompatible ways. The ODBC/JDBC escape functions, though somewhat awkward, are the closest thing to a common denominator here. > Datetime datatypes should allow arithmetic operations on them. > -- > > Key: DERBY-81 > URL: http://issues.apache.org/jira/browse/DERBY-81 > Project: Derby > Type: New Feature > Components: SQL > Versions: 10.0.2.0 > Environment: Generic > Reporter: Satheesh Bandaram > Assignee: Jack Klebanoff > > Datetime datatypes in Derby are date, time and timestamp. Currently Derby > doesn't allow any arithmetic operations on them, limiting their use. It would > be great to allow statements like: >SELECT * FROM log >WHERE modified_time > CURRENT_TIMESTAMP - 1 MONTH > Commercial databases like Oracle, DB2 and mySQL also have this feature. SQL > standard allows these operations along with INTERVAL types. Since Derby > doesn't have INTERVAL datatypes, I would like to propose datetime arithmetic > to Derby without INTERVALs. > It should be possible to add, subtract datetime datatypes with a constant > like '1 MONTH' (called a duration or INTERVAL in SQL standard). It should > also be possible to subtract datetime types where the semantics are allowed > according to SQL standard specification. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-84) Column aliasing could simplify queries
[ http://issues.apache.org/jira/browse/DERBY-84?page=comments#action_66872 ] Jack Klebanoff commented on DERBY-84: - I suspect that this is not a bug, that Derby is behaving correctly here according to the SQL standard. The alias in the select list names the column in the virtual table produced by the SELECT. However Derby requires that where clause column references refer to columns in tables, views, or sub-queries listed in the FROM clause. In the bug report example LDCN is not a column in in any from list table, so Derby issues an error message when LDCN is used in the where clause. My reading of the SQL2003 spec indicates that the Derby behavior follows the SQL standard. See the standard's discussion of identifier chains. The spec differentiates between identifiers used in ORDER BY clauses and identifiers used elsewhere. The spec says that (column) identifiers in a ORDER BY clause should be bound to the column names defined in the select list, but that other (column) identifiers should be bound to columns in tables in the FROM list. > Column aliasing could simplify queries > -- > > Key: DERBY-84 > URL: http://issues.apache.org/jira/browse/DERBY-84 > Project: Derby > Type: New Feature > Components: SQL > Versions: 10.0.2.0 > Reporter: Bob Gibson > Priority: Minor > > Currently, one can not use an alias to simplify queries. For example, being > able to alias "LongDescriptiveColumnName" AS LDCN would allow one to use the > alias elsewhere in the query, e.g., the WHERE clause: > SELECT LongDescriptiveColumnName AS LDCN FROM MyTable WHERE LDCN LIKE > '%testing%'; > The current result is a message like: > ERROR 42X04: Column 'LDCN' is not in any table in the FROM list or it appears > within a join specification and is outside the scope of the join > specification or it appears in a HAVING clause and is not in the GROUP BY > list. If this is a CREATE or ALTER TABLE statement then 'LDCN' is not a > column in the target table. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-84) Column aliasing could simplify queries
[ http://issues.apache.org/jira/browse/DERBY-84?page=comments#action_67015 ] Jack Klebanoff commented on DERBY-84: - I think that this issue can be closed. The Hibernate/Derby issue that Micah Spears referred to in his comment is different than this Jira entry's issue. I believe that that issue was a Derby bug and that it has been fixed. > Column aliasing could simplify queries > -- > > Key: DERBY-84 > URL: http://issues.apache.org/jira/browse/DERBY-84 > Project: Derby > Type: New Feature > Components: SQL > Versions: 10.0.2.0 > Reporter: Bob Gibson > Priority: Minor > > Currently, one can not use an alias to simplify queries. For example, being > able to alias "LongDescriptiveColumnName" AS LDCN would allow one to use the > alias elsewhere in the query, e.g., the WHERE clause: > SELECT LongDescriptiveColumnName AS LDCN FROM MyTable WHERE LDCN LIKE > '%testing%'; > The current result is a message like: > ERROR 42X04: Column 'LDCN' is not in any table in the FROM list or it appears > within a join specification and is outside the scope of the join > specification or it appears in a HAVING clause and is not in the GROUP BY > list. If this is a CREATE or ALTER TABLE statement then 'LDCN' is not a > column in the target table. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-6) Trigger of the form: create trigger ... values myFunction(); has no effect.
[ http://issues.apache.org/jira/browse/DERBY-6?page=all ]
Jack Klebanoff closed DERBY-6:
--
> Trigger of the form: create trigger ... values myFunction(); has no effect.
> ---
>
> Key: DERBY-6
> URL: http://issues.apache.org/jira/browse/DERBY-6
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Tulika Agrawal
> Priority: Minor
> Fix For: 10.0.2.0
>
> Reporting for Jack Klebanoff, filed on derby-dev list.
> > Jack Klebanoff wrote:
> >
> > | I would like to submit a fix for a bug in triggers.
> > |
> > | The bug is that a trigger of the form:
> > | create trigger ... values myFunction();
> > | has no effect. MyFunction is not called even if the trigger is fired.
> > | Side effects of myFunction do not happen. Derby does not allow a "CALL"
> > | statement inside a trigger action so a values statement is the only way
> > | to call a function/procedure in a trigger action.
> > |
> > | The cause of the bug is that since the values are not actually used by
> > | the trigger, the trigger code does not fetch the row(s) returned by the
> > | trigger action. The fix is simple: change class
> > | org.apache.derby.impl.sql.execute.GenericTriggerExecutor to fetch (and
> > | discard) the rows returned by a trigger action.
> > |
> > | Please review the change. The diff file is attached.
> >
> >
> > I think you need to close the ResultSet (rs). Other locations in the
> > code where a ResultSet is processed terminate with an rs.close(). Eg.
> > see DeleteCascadeResultSet, ConstraintConstantAction.
> >
> > Dan.
> >
> I changed the code to close the ResultSet. The diff file is attached.
> Jack
> Index:
> java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
> ===
> --- java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
> (revision 37092)
> +++ java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
> (working copy)
> @@ -157,7 +157,18 @@
>*/
>try
>{
> -
> ps.execute(spsActivation, false, false, false);
> + ResultSet rs =
> ps.execute(spsActivation, false, false, false);
> +if( rs.returnsRows())
> +{
> +// Fetch all the data to ensure that functions in the
> select list or values statement will
> +// be evaluated and side effects will happen. Why else
> would the trigger action return
> +// rows, but for side effects?
> +// The result set was opened in ps.execute()
> +while( rs.getNextRow() != null)
> +{
> +}
> +}
> +rs.close();
>}
>catch (StandardException e)
>{
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
[ http://issues.apache.org/jira/browse/DERBY-127?page=all ] Jack Klebanoff closed DERBY-127: > Aliased Columns not recognized after "group by... order by" combination > --- > > Key: DERBY-127 > URL: http://issues.apache.org/jira/browse/DERBY-127 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.1 > Environment: Windows XP Professional > JDK 1.4 > (first found in relation to Mondrian 1.0.1) > Reporter: Thomas Browne > Assignee: Jack Klebanoff > Fix For: 10.1.0.0 > > I've been doing work to try and integrate Derby with the Mondrian ROLAP > engine, which has uncovered a bug in Derby when a query involves column > aliasing, a group by clause, and an order by clause. > For example: Mondrian will generate the following query: > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by STORE.STORE_COUNTRY, STORE.STORE_STATE, > STORE.STORE_CITY, STORE.STORE_NAME > which should be valid SQL. I have tested this query outside of the Mondrian > environment and still receive the same error which is: > "Column 'STORE.STORE_COUNTRY' is not in any table in the FROM list or it > appears within a join specification and is outside the scope of the join > specification or it appears in a HAVING clause and is not in the GROUP BY > list. > SQL State: 42x04 > Error Code: 3 > However, if I remove any one of the three elements (aliasing, group by, order > by) or if the order by uses the aliased names, the query works. It is only > the combination of all 3 elements that is causing a problem. > [ie. all of the following queries work correctly] > select STORE.STORE_COUNTRY , STORE.STORE_STATE , STORE.STORE_CITY , > STORE.STORE_NAME , STORE.STORE_TYPE , STORE.STORE_MANAGER , STORE.STORE_SQFT > , STORE.GROCERY_SQFT , STORE.FROZEN_SQFT , STORE.MEAT_SQFT , STORE.COFFEE_BAR > , STORE.STORE_STREET_ADDRESS from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by STORE.STORE_COUNTRY, STORE.STORE_STATE, > STORE.STORE_CITY, STORE.STORE_NAME > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE order by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by c0,c1,c2,c3 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-14) Triggers do not evaluate functions in VALUES trigger actions.
[ http://issues.apache.org/jira/browse/DERBY-14?page=all ] Jack Klebanoff closed DERBY-14: --- > Triggers do not evaluate functions in VALUES trigger actions. > - > > Key: DERBY-14 > URL: http://issues.apache.org/jira/browse/DERBY-14 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.0 > Reporter: Ramandeep Kaur > Priority: Minor > Fix For: 10.0.2.0 > > Opening this bug on behalf of Jack Klebanoff. > - > If a trigger is created with > CREATE TRIGGER T ... VALUES( fn()) > The function fn is not called even if the trigger event > happens. Side effects of the function will not happen. > Derby does not allow CALL statements in trigger actions. > In Derby, one has to use a VALUES statement and depend on function side > effects. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
