Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 74859 invoked from network); 13 Dec 2005 18:24:49 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 13 Dec 2005 18:24:49 -0000 Received: (qmail 21337 invoked by uid 500); 13 Dec 2005 18:24:48 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 21223 invoked by uid 500); 13 Dec 2005 18:24:47 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 21091 invoked by uid 99); 13 Dec 2005 18:24:46 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 13 Dec 2005 10:24:46 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [209.237.227.194] (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.29) with SMTP; Tue, 13 Dec 2005 10:24:42 -0800 Received: (qmail 74563 invoked by uid 65534); 13 Dec 2005 18:24:21 -0000 Message-ID: <20051213182421.74558.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r356562 [3/6] - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/reference/ engine/org/apache/derby/iapi/sql/ engine/org/apache/derby/iapi/sql/conn/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/jdbc/ e... Date: Tue, 13 Dec 2005 18:24:11 -0000 To: derby-commits@db.apache.org From: bandaram@apache.org X-Mailer: svnmailer-1.0.5 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out?rev=356562&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out Tue Dec 13 10:23:42 2005 @@ -0,0 +1,979 @@ +ij> -- test the optimizer overrides +autocommit off; +ij> -- create the tables +create table t1 (c1 int, c2 int, c3 int, constraint cons1 primary key(c1, c2)); +0 rows inserted/updated/deleted +ij> create table t2 (c1 int not null, c2 int not null, c3 int, constraint cons2 unique(c1, c2)); +0 rows inserted/updated/deleted +ij> -- populate the tables +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4); +4 rows inserted/updated/deleted +ij> insert into t2 values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4); +4 rows inserted/updated/deleted +ij> -- create some indexes +create index t1_c1c2c3 on t1(c1, c2, c3); +0 rows inserted/updated/deleted +ij> create index t1_c3c2c1 on t1(c3, c2, c1); +0 rows inserted/updated/deleted +ij> create index t1_c1 on t1(c1); +0 rows inserted/updated/deleted +ij> create index t1_c2 on t1(c2); +0 rows inserted/updated/deleted +ij> create index t1_c3 on t1(c3); +0 rows inserted/updated/deleted +ij> create index "t1_c2c1" on t1(c2, c1); +0 rows inserted/updated/deleted +ij> create index t2_c1c2c3 on t2(c1, c2, c3); +0 rows inserted/updated/deleted +ij> create index t2_c3c2c1 on t2(c3, c2, c1); +0 rows inserted/updated/deleted +ij> create index t2_c1 on t2(c1); +0 rows inserted/updated/deleted +ij> create index t2_c2 on t2(c2); +0 rows inserted/updated/deleted +ij> create index t2_c3 on t2(c3); +0 rows inserted/updated/deleted +ij> -- create some views +create view v1 as select * from t1 --derby-properties index = t1_c1 +; +0 rows inserted/updated/deleted +ij> create view v2 as select t1.* from t1, t2; +0 rows inserted/updated/deleted +ij> create view v3 as select * from v1; +0 rows inserted/updated/deleted +ij> create view neg_v1 as select * from t1 --derby-properties asdf = fdsa +; +0 rows inserted/updated/deleted +ij> -- negative tests +select +----- derby-properties index = t1_c1 +* from t1; +ERROR 42X01: Syntax error: Encountered "derby-properties" at line 3, column 4. +ij> select * -- derby-properties index = t1_c1 +from t1; +ERROR 42X01: Syntax error: Encountered "derby-properties" at line 1, column 13. +ij> select +----- derby-properties +* from t1; +ERROR 42X01: Syntax error: Encountered "derby-properties" at line 2, column 4. +ij> -- optimizer override did not specify propertyname=value pairs +select * from t1 --derby-properties +; +ERROR XCY04: Invalid syntax for optimizer overrides. The syntax should be -- DERBY-PROPERTIES propertyName = value [, propertyName = value]* +ij> -- invalid property +select * from t1 --derby-properties asdf = i1 +; +ERROR 42Y44: Invalid key 'asdf' specified in the Properties list of a FROM list. The case-sensitive keys that are currently supported are 'index, constraint, joinStrategy'. +ij> select * from t1 exposedname --derby-properties asdf = i1 +; +ERROR 42Y44: Invalid key 'asdf' specified in the Properties list of a FROM list. The case-sensitive keys that are currently supported are 'index, constraint, joinStrategy'. +ij> -- non-existent index +select * from t1 --derby-properties index = t1_notexists +; +ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'T1_NOTEXISTS' on table 'T1'. +ij> select * from t1 exposedname --derby-properties index = t1_notexists +; +ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'T1_NOTEXISTS' on table 'T1'. +ij> -- non-existent constraint +select * from t1 --derby-properties constraint = t1_notexists +; +ERROR 42Y48: Invalid Properties list in FROM list. Either there is no named constraint 'T1_NOTEXISTS' on table 'T1' or the constraint does not have a backing index. +ij> select * from t1 exposedname --derby-properties constraint = t1_notexists +; +ERROR 42Y48: Invalid Properties list in FROM list. Either there is no named constraint 'T1_NOTEXISTS' on table 'T1' or the constraint does not have a backing index. +ij> -- make sure following get treated as comments +-----d +----- de +----- der +-----derb +-----derby comment +----- derby another comment +-----derby- +-----derby-p +-----derby-pr +-----derby-pro +-----derby-prop +-----derby-prope +-----derby-proper +----- derby-propert +----- derby-properti +----- derby-propertie +----- derby-propertiex +----- both index and constraint +select * from t1 --derby-properties index = t1_c1, constraint = cons1 +; +ERROR 42Y50: Properties list for table 'T1' may contain values for index or for constraint but not both. +ij> select * from t1 exposedname --derby-properties index = t1_c1, constraint = cons1 +; +ERROR 42Y50: Properties list for table 'T1' may contain values for index or for constraint but not both. +ij> -- index which includes columns in for update of list +select * from t1 --derby-properties index = t1_c1 +for update; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> select * from t1 exposedname --derby-properties index = t1_c1 +for update; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> select * from t1 --derby-properties index = t1_c1 +for update of c2, c1; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> select * from t1 exposedname --derby-properties index = t1_c1 +for update of c2, c1; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> -- constraint which includes columns in for update of list +select * from t1 --derby-properties constraint = cons1 +for update; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> select * from t1 exposedname --derby-properties constraint = cons1 +for update; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> select * from t1 --derby-properties constraint = cons1 +for update of c2, c1; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> select * from t1 exposedname --derby-properties constraint = cons1 +for update of c2, c1; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> -- select from view with bad derby-properties list +select * from neg_v1; +ERROR 42Y44: Invalid key 'asdf' specified in the Properties list of a FROM list. The case-sensitive keys that are currently supported are 'index, constraint, joinStrategy'. +ij> -- bad derby-properties tests on outer joins +select * from t1 --derby-properties i = a +left outer join t2 on 1=1; +ERROR 42Y44: Invalid key 'i' specified in the Properties list of a FROM list. The case-sensitive keys that are currently supported are 'index, constraint, joinStrategy'. +ij> select * from t1 left outer join t2 --derby-properties i = t1_c1 +on 1=1; +ERROR 42Y44: Invalid key 'i' specified in the Properties list of a FROM list. The case-sensitive keys that are currently supported are 'index, constraint, joinStrategy'. +ij> select * from t1 left outer join t2 --derby-properties index = t1_c1 +on 1=1; +ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'T1_C1' on table 'T2'. +ij> select * from t1 right outer join t2 --derby-properties index = t1_c1 +on 1=1; +ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'T1_C1' on table 'T2'. +ij> -- invalid joinStrategy +select * from t1 a, t1 b --derby-properties joinStrategy = asdf +; +ERROR 42Y56: Invalid join strategy 'ASDF' specified in Properties list on table 'T1'. The currently supported values for a join strategy are: 'hash' and 'nestedloop'. +ij> -- positive tests +----- verify that statements are dependent on specified index or constraint +commit; +ij> -- dependent on index +prepare p1 as 'select * from t1 --derby-properties index = t1_c1 +'; +ij> execute p1; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> drop index t1_c1; +0 rows inserted/updated/deleted +ij> execute p1; +ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'T1_C1' on table 'T1'. +ij> remove p1; +ij> rollback; +ij> -- dependent on constraint +prepare p2 as 'select * from t1 --derby-properties constraint = cons1 +'; +ij> execute p2; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> alter table t1 drop constraint cons1; +0 rows inserted/updated/deleted +ij> execute p2; +ERROR 42Y48: Invalid Properties list in FROM list. Either there is no named constraint 'CONS1' on table 'T1' or the constraint does not have a backing index. +ij> remove p2; +ij> rollback; +ij> -- change display width in anticipation of runtimestatistics +maximumdisplaywidth 5000; +ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); +Statement executed. +ij> -- the token derby-properties is case insensitive. Few tests for that +select * from t1 --DeRbY-pRoPeRtIeS index = t1_c1 +; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----- +Statement Name: + SQL_CURLH000C1 +Statement Text: + -- the token derby-properties is case insensitive. Few tests for that +select * from t1 --DeRbY-pRoPeRtIeS index = t1_c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched={1} + Number of columns fetched=1 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None +ij> -- misspell derby-properties and make sure that it gets treated as a regular comment rather than optimizer override +select * from t1 --DeRbY-pRoPeRtIeAAA index = t1_c1 +; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----- +Statement Name: + SQL_CURLH000C1 +Statement Text: + -- misspell derby-properties and make sure that it gets treated as a regular comment rather than optimizer override +select * from t1 --DeRbY-pRoPeRtIeAAA index = t1_c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Scan ResultSet for T1 using index T1_C1C2C3 at read committed isolation level using instantaneous share row locking chosen by the optimizer +Number of opens = 1 +Rows seen = 4 +Rows filtered = 0 +Fetch Size = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 +scan information: + Bit set of columns fetched={0, 1, 2} + Number of columns fetched=3 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None +ij> -- force index, delimited identifier +select * from t1 --derby-properties index = "t1_c2c1" +; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----- +Statement Name: + SQL_CURLH000C1 +Statement Text: + -- force index, delimited identifier +select * from t1 --derby-properties index = "t1_c2c1" +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Index Scan ResultSet for T1 using index t1_c2c1 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched={2} + Number of columns fetched=1 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None +ij> -- force table scan +select * from t1 --derby-properties index = null +; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----- +Statement Name: + SQL_CURLH000C1 +Statement Text: + -- force table scan +select * from t1 --derby-properties index = null +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer +Number of opens = 1 +Rows seen = 4 +Rows filtered = 0 +Fetch Size = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 +scan information: + Bit set of columns fetched=All + Number of columns fetched=3 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=heap + start position: +null stop position: +null qualifiers: +None +ij> -- force index in create view +select * from v1; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----- +Statement Name: + SQL_CURLH000C1 +Statement Text: + -- force index in create view +select * from v1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using share row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None +ij> -- cursor updateability test +select * from t1 --derby-properties index = t1_c1 +for update of c2, c3; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----- +Statement Name: + SQL_CURLH000C1 +Statement Text: + -- cursor updateability test +select * from t1 --derby-properties index = t1_c1 +for update of c2, c3 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None +ij> -- joins +select 1 from t1 a --derby-properties index = t1_c1 +, t2 b --derby-properties index = t2_c2 +; +1 +----- +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----- +Statement Name: + SQL_CURLH000C1 +Statement Text: + -- joins +select 1 from t1 a --derby-properties index = t1_c1 +, t2 b --derby-properties index = t2_c2 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Project-Restrict ResultSet (4): +Number of opens = 1 +Rows seen = 16 +Rows filtered = 0 +restriction = false +projection = true + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + restriction time (milliseconds) = 0 + projection time (milliseconds) = 0 +Source result set: + Nested Loop Join ResultSet: + Number of opens = 1 + Rows seen from the left = 4 + Rows seen from the right = 16 + Rows filtered = 0 + Rows returned = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched={} + Number of columns fetched=0 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None + Right result set: + Index Scan ResultSet for T2 using index T2_C2 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 4 + Rows seen = 16 + Rows filtered = 0 + Fetch Size = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched={} + Number of columns fetched=0 + Number of deleted rows visited=0 + Number of pages visited=4 + Number of rows qualified=16 + Number of rows visited=16 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None +ij> -- comparisons that can't get pushed down +select * from t1 --derby-properties index = t1_c1 +where c1 = c1; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> select * from t1 --derby-properties index = t1_c1 +where c1 = c2; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> select * from t1 --derby-properties index = t1_c1 +where c1 + 1 = 1 + c1; +C1 |C2 |C3 +----- +1 |1 |1 +2 |2 |2 +3 |3 |3 +4 |4 |4 +ij> -- outer joins +select * from t1 --derby-properties index = t1_c1 +left outer join t2 --derby-properties index = t2_c2 +on t1.c1 = t2.c1; +C1 |C2 |C3 |C1 |C2 |C3 +----- +1 |1 |1 |1 |1 |1 +2 |2 |2 |2 |2 |2 +3 |3 |3 |3 |3 |3 +4 |4 |4 |4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----- +Statement Name: + SQL_CURLH000C1 +Statement Text: + -- outer joins +select * from t1 --derby-properties index = t1_c1 +left outer join t2 --derby-properties index = t2_c2 +on t1.c1 = t2.c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Nested Loop Left Outer Join ResultSet: +Number of opens = 1 +Rows seen from the left = 4 +Rows seen from the right = 4 +Empty right rows returned = 0 +Rows filtered = 0 +Rows returned = 4 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 +Left result set: + Index Row to Base Row ResultSet for T1: + Number of opens = 1 + Rows seen = 4 + Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched={1} + Number of columns fetched=1 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None +Right result set: + Project-Restrict ResultSet (6): + Number of opens = 4 + Rows seen = 16 + Rows filtered = 12 + restriction = true + projection = true + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + restriction time (milliseconds) = 0 + projection time (milliseconds) = 0 + Source result set: + Index Row to Base Row ResultSet for T2: + Number of opens = 4 + Rows seen = 16 + Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Index Scan ResultSet for T2 using index T2_C2 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 4 + Rows seen = 16 + Rows filtered = 0 + Fetch Size = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched={1} + Number of columns fetched=1 + Number of deleted rows visited=0 + Number of pages visited=4 + Number of rows qualified=16 + Number of rows visited=16 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None +ij> -- verify nestedloop joinStrategy +select * from t1 a, t1 b --derby-properties joinStrategy = nestedloop +where a.c1 = b.c1; +C1 |C2 |C3 |C1 |C2 |C3 +----- +1 |1 |1 |1 |1 |1 +2 |2 |2 |2 |2 |2 +3 |3 |3 |3 |3 |3 +4 |4 |4 |4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----- +Statement Name: + SQL_CURLH000C1 +Statement Text: + -- verify nestedloop joinStrategy +select * from t1 a, t1 b --derby-properties joinStrategy = nestedloop +where a.c1 = b.c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Project-Restrict ResultSet (4): +Number of opens = 1 +Rows seen = 4 +Rows filtered = 0 +restriction = false +projection = true + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + restriction time (milliseconds) = 0 + projection time (milliseconds) = 0 +Source result set: + Hash Join ResultSet: + Number of opens = 1 + Rows seen from the left = 4 + Rows seen from the right = 4 + Rows filtered = 0 + Rows returned = 4 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Index Scan ResultSet for T1 using index T1_C1C2C3 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 16 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched={0, 1, 2} + Number of columns fetched=3 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None + Right result set: + Hash Scan ResultSet for T1 using index T1_C1C2C3 at read committed isolation level using instantaneous share row locking: + Number of opens = 4 + Hash table size = 4 + Hash key is column number 0 + Rows seen = 4 + Rows filtered = 0 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + next time in milliseconds/row = 0 + scan information: + Bit set of columns fetched={0, 1, 2} + Number of columns fetched=3 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + scan qualifiers: +None + next qualifiers: +Column[0][0] Id: 0 +Operator: = +Ordered nulls: false +Unknown return value: false +Negate comparison result: false +ij> --negative test. insertModeValue is not avaible to a user and hence will +-----give a syntax error. There are some undocumented properties which are +-----allowed within Derby engine only and insertModeValue is one of them. +create table temp1 (c1 int, c2 int, c3 int, constraint temp1cons1 primary key(c1, c2)); +0 rows inserted/updated/deleted +ij> insert into temp1 (c1,c2,c3) -- derby-properties insertModeValue=replace +select * from t1; +ERROR 42X01: Syntax error: DERBY-PROPERTIES. +ij> -- clean up +drop view neg_v1; +0 rows inserted/updated/deleted +ij> drop view v3; +0 rows inserted/updated/deleted +ij> drop view v2; +0 rows inserted/updated/deleted +ij> drop view v1; +0 rows inserted/updated/deleted +ij> drop table t2; +0 rows inserted/updated/deleted +ij> drop table t1; +0 rows inserted/updated/deleted +ij> drop table temp1; +0 rows inserted/updated/deleted +ij> Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/access.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/access.out?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/access.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/access.out Tue Dec 13 10:23:42 2005 @@ -2591,11 +2591,11 @@ 0_col16 |0_col17 |0_ 1_col16 |1_col17 |1_ ij> commit; -ij> update t2778 properties index = a_idx +ij> update t2778 --derby-properties index = a_idx set col10 = '0_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxxxxx' where col00 = '0_'; -ERROR 42X01: Syntax error: PROPERTIES. +ERROR 42X01: Syntax error: DERBY-PROPERTIES. ij> rollback; ij> -- prior to the fix col17 and col18 would come back null. select col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, col11, col12, col13, col14, col15, col16, col17, col18 from t2778; Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkConstraint.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkConstraint.out?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkConstraint.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkConstraint.out Tue Dec 13 10:23:42 2005 @@ -26,8 +26,9 @@ ij> -- verify that a check constraint can't be used as an optimizer override create table t1(c1 int constraint asdf check(c1 = 1)); 0 rows inserted/updated/deleted -ij> select * from t1 properties constraint = asdf; -ERROR 42X01: Syntax error: PROPERTIES. +ij> select * from t1 --derby-properties constraint = asdf +; +ERROR 42Y48: Invalid Properties list in FROM list. Either there is no named constraint 'ASDF' on table 'T1' or the constraint does not have a backing index. ij> -- alter table t1 drop constraint asdf; rollback; ij> -- alter table t1 drop constraint asdf; Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out Tue Dec 13 10:23:42 2005 @@ -186,15 +186,9 @@ 0 rows inserted/updated/deleted ij> DROP TABLE testCS; 0 rows inserted/updated/deleted -ij> -- PROPERTIES in DB2 mode -create table maps (country_ISO_code char(2)) PROPERTIES derby.storage.pageSize=262144; -ERROR 42X01: Syntax error: PROPERTIES. -ij> -- PROPERTIES in DB2 mode --- beetle 5177 +ij> -- beetle 5177 create table maps2 (country_ISO_code char(2)); 0 rows inserted/updated/deleted -ij> create index map_idx1 on maps2(country_ISO_code) properties derby.storage.pageSize = 2048; -ERROR 42X01: Syntax error: PROPERTIES. ij> -- BTREE not supported in both Cloudscape and DB2 mode and that is why rather than getting feature not implemented, we will get syntax error in DB2 mode create btree index map_idx2 on maps2(country_ISO_code); ERROR 42X01: Syntax error: Encountered "btree" at line 2, column 8. Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/metadata.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/metadata.out?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/metadata.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/metadata.out Tue Dec 13 10:23:42 2005 @@ -40,6 +40,16 @@ ,APP,GETPCTEST4A,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4a,1 ,APP,GETPCTEST4B,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 ,APP,GETPCTEST4BX,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 +getUDTs() with user-named types null : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('JAVA_OBJECT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('STRUCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('DISTINCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('JAVA_OBJECT', 'STRUCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] allProceduresAreCallable(): true getUserName(): APP isReadOnly(): false @@ -151,6 +161,20 @@ supportsDataManipulationTransactionsOnly(): false dataDefinitionCausesTransactionCommit(): false dataDefinitionIgnoredInTransactions(): false +Test the metadata calls related to visibility of changes made by others for different resultset types +Since Derby materializes a forward only ResultSet incrementally, it is possible to see changes +made by others and hence following 3 metadata calls will return true for forward only ResultSets. +othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +Scroll insensitive ResultSet by their definition do not see changes made by others and hence following metadata calls return false +othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +Derby does not yet implement scroll sensitive resultsets and hence following metadata calls return false +othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false +othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false +othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false getConnection(): same connection getProcedureColumns(): PROCEDURE_CAT[VARCHAR],PROCEDURE_SCHEM[VARCHAR],PROCEDURE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],COLUMN_TYPE[SMALLINT],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],PRECISION[INTEGER],LENGTH[INTEGER],SCALE[SMALLINT],RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],METHOD_ID[SMALLINT],PARAMETER_ID[SMALLINT] @@ -427,6 +451,119 @@ ,SYS,SYSTABLES,TABLETYPE,1,CHAR,1,null,null,null,0,,null,null,null,2,3,NO ,SYS,SYSTABLES,SCHEMAID,1,CHAR,36,null,null,null,0,,null,null,null,72,4,NO ,SYS,SYSTABLES,LOCKGRANULARITY,1,CHAR,1,null,null,null,0,,null,null,null,2,5,NO +Test escaped numeric functions - JDBC 3.0 C.1 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ABS(-25.67) } -- = >25.67< << +Executing VALUES { fn MOD(124, 7) } -- = >5< << +Executing VALUES { fn SQRT(6.22) } -- = >2.49< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ACOS(1.34) } -- +SQLSTATE(42X01): Syntax error: Encountered "ACOS" at line 1, column 13. +Executing VALUES { fn ASIN(1.21) } -- +SQLSTATE(42X01): Syntax error: Encountered "ASIN" at line 1, column 13. +Executing VALUES { fn ATAN(0.34) } -- +SQLSTATE(42X01): Syntax error: Encountered "ATAN" at line 1, column 13. +Executing VALUES { fn ATAN2(0.56, 1.2) } -- +SQLSTATE(42X01): Syntax error: Encountered "ATAN2" at line 1, column 13. +Executing VALUES { fn CEILING(3.45) } -- +SQLSTATE(42X01): Syntax error: Encountered "CEILING" at line 1, column 13. +Executing VALUES { fn COS(1.2) } -- +SQLSTATE(42X01): Syntax error: Encountered "COS" at line 1, column 13. +Executing VALUES { fn COT(3.4) } -- +SQLSTATE(42X01): Syntax error: Encountered "COT" at line 1, column 13. +Executing VALUES { fn DEGREES(2.1) } -- +SQLSTATE(42X01): Syntax error: Encountered "DEGREES" at line 1, column 13. +Executing VALUES { fn EXP(2.3) } -- +SQLSTATE(42X01): Syntax error: Encountered "EXP" at line 1, column 13. +Executing VALUES { fn FLOOR(3.22) } -- +SQLSTATE(42X01): Syntax error: Encountered "FLOOR" at line 1, column 13. +Executing VALUES { fn LOG(34.1) } -- +SQLSTATE(42X01): Syntax error: Encountered "LOG" at line 1, column 13. +Executing VALUES { fn LOG10(18.7) } -- +SQLSTATE(42X01): Syntax error: Encountered "LOG10" at line 1, column 13. +Executing VALUES { fn PI() } -- +SQLSTATE(42X01): Syntax error: Encountered "PI" at line 1, column 13. +Executing VALUES { fn POWER(2, 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "POWER" at line 1, column 13. +Executing VALUES { fn RADIANS(54) } -- +SQLSTATE(42X01): Syntax error: Encountered "RADIANS" at line 1, column 13. +Executing VALUES { fn RAND(17) } -- +SQLSTATE(42X01): Syntax error: Encountered "RAND" at line 1, column 13. +Executing VALUES { fn ROUND(345.345, 1) } -- +SQLSTATE(42X01): Syntax error: Encountered "ROUND" at line 1, column 13. +Executing VALUES { fn SIGN(-34) } -- +SQLSTATE(42X01): Syntax error: Encountered "SIGN" at line 1, column 13. +Executing VALUES { fn SIN(0.32) } -- +SQLSTATE(42X01): Syntax error: Encountered "SIN" at line 1, column 13. +Executing VALUES { fn TAN(0.57) } -- +SQLSTATE(42X01): Syntax error: Encountered "TAN" at line 1, column 13. +Executing VALUES { fn TRUNCATE(345.395, 1) } -- +SQLSTATE(42X01): Syntax error: Encountered "TRUNCATE" at line 1, column 13. +Test escaped string functions - JDBC 3.0 C.2 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn CONCAT('hello', 'there') } -- = >hellothere< << +Executing VALUES { fn LENGTH('four ') } -- = >4< << +Executing VALUES { fn LCASE('Fernando Alonso') } -- = >fernando alonso< << +Executing VALUES { fn LOCATE('jour', 'Bonjour') } -- = >4< << +Executing VALUES { fn LTRIM(' left trim ') } -- = >left trim < << +Executing VALUES { fn RTRIM(' right trim ') } -- = > right trim< << +Executing VALUES { fn SUBSTRING('Ruby the Rubicon Jeep', 10, 7) } -- = >Rubicon< << +Executing VALUES { fn UCASE('Fernando Alonso') } -- = >FERNANDO ALONSO< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ASCII('Yellow') } -- +SQLSTATE(42X01): Syntax error: Encountered "ASCII" at line 1, column 13. +Executing VALUES { fn CHAR(65) } -- = >65 < << +FAIL function works but not declared in list: CHAR +Executing VALUES { fn DIFFERENCE('Pires', 'Piers') } -- +SQLSTATE(42X01): Syntax error: Encountered "DIFFERENCE" at line 1, column 13. +Executing VALUES { fn INSERT('Bill Clinton', 4, 'William') } -- +SQLSTATE(42X01): Syntax error: Encountered "INSERT" at line 1, column 13. +Executing VALUES { fn LEFT('Bonjour', 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "LEFT" at line 1, column 13. +Executing VALUES { fn REPEAT('echo', 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "REPEAT" at line 1, column 13. +Executing VALUES { fn REPLACE('to be or not to be', 'be', 'England') } -- +SQLSTATE(42X01): Syntax error: Encountered "REPLACE" at line 1, column 13. +Executing VALUES { fn SOUNDEX('Derby') } -- +SQLSTATE(42X01): Syntax error: Encountered "SOUNDEX" at line 1, column 13. +Executing VALUES { fn SPACE(12) } -- +SQLSTATE(42X01): Syntax error: Encountered "SPACE" at line 1, column 13. +Test escaped date time functions - JDBC 3.0 C.3 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES CASE WHEN { fn CURDATE()} = CURRENT_DATE THEN 'OK' ELSE 'wrong' END -- = >OK< << +Executing VALUES CASE WHEN { fn CURTIME()} = CURRENT_TIME THEN 'OK' ELSE 'wrong' END -- = >OK< << +Executing VALUES { fn HOUR({t '16:13:03'}) } -- = >16< << +Executing VALUES { fn MINUTE({t '16:13:03'}) } -- = >13< << +Executing VALUES { fn MONTH({d '1995-12-19'}) } -- = >12< << +Executing VALUES { fn SECOND({t '16:13:03'}) } -- = >3< << +Executing VALUES { fn TIMESTAMPADD(SQL_TSI_DAY, 7, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}) } -- = >xxxxxxFILTERED-TIMESTAMPxxxxx< << +Executing VALUES { fn TIMESTAMPDIFF(SQL_TSI_DAY, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}) } -- = >683< << +Executing VALUES { fn YEAR({d '1995-12-19'}) } -- = >1995< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn DAYNAME({d '1995-12-19'h}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYNAME" at line 1, column 13. +Executing VALUES { fn DAYOFMONTH({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFMONTH" at line 1, column 13. +Executing VALUES { fn DAYOFWEEK({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFWEEK" at line 1, column 13. +Executing VALUES { fn DAYOFYEAR({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFYEAR" at line 1, column 13. +Executing VALUES { fn MONTHNAME({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "MONTHNAME" at line 1, column 13. +Executing VALUES CASE WHEN { fn NOW()} = CURRENT_TIMESTAMP THEN 'OK' ELSE 'wrong' END -- +SQLSTATE(42X01): Syntax error: Encountered "NOW" at line 1, column 23. +Executing VALUES { fn QUARTER({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "QUARTER" at line 1, column 13. +Executing VALUES { fn WEEK({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "WEEK" at line 1, column 13. +Test escaped system functions - JDBC 3.0 C.4 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn USER() } -- = >APP< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn DATABASE() } -- +SQLSTATE(42X01): Syntax error: Encountered "DATABASE" at line 1, column 13. +Executing VALUES { fn IFNULL('this', 'that') } -- +SQLSTATE(42X01): Syntax error: Encountered "IFNULL" at line 1, column 13. --------------------------------------- Referential action values RESTRICT = 1 Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out Tue Dec 13 10:23:42 2005 @@ -70,6 +70,16 @@ ,APP,GETPCTEST4A,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4a,1 ,APP,GETPCTEST4B,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 ,APP,GETPCTEST4BX,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 +getUDTs() with user-named types null : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('JAVA_OBJECT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('STRUCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('DISTINCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('JAVA_OBJECT', 'STRUCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] allProceduresAreCallable(): true getUserName(): APP isReadOnly(): false @@ -181,6 +191,20 @@ supportsDataManipulationTransactionsOnly(): false dataDefinitionCausesTransactionCommit(): false dataDefinitionIgnoredInTransactions(): false +Test the metadata calls related to visibility of changes made by others for different resultset types +Since Derby materializes a forward only ResultSet incrementally, it is possible to see changes +made by others and hence following 3 metadata calls will return true for forward only ResultSets. +othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +Scroll insensitive ResultSet by their definition do not see changes made by others and hence following metadata calls return false +othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +Derby does not yet implement scroll sensitive resultsets and hence following metadata calls return false +othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false +othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false +othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false getConnection(): same connection getProcedureColumns(): PROCEDURE_CAT[VARCHAR],PROCEDURE_SCHEM[VARCHAR],PROCEDURE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],COLUMN_TYPE[SMALLINT],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR],METHOD_ID[SMALLINT],PARAMETER_ID[SMALLINT] @@ -458,6 +482,119 @@ ,SYS,SYSTABLES,SCHEMAID,1,CHAR,36,72,null,null,0,,null,1,null,72,4,NO ,SYS,SYSTABLES,LOCKGRANULARITY,1,CHAR,1,2,null,null,0,,null,1,null,2,5,NO OK -- user result set closed by intervening OBDC getColumns request; this was expected because of the way the test works. +Test escaped numeric functions - JDBC 3.0 C.1 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ABS(-25.67) } -- = >25.67< << +Executing VALUES { fn MOD(124, 7) } -- = >5< << +Executing VALUES { fn SQRT(6.22) } -- = >2.49< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ACOS(1.34) } -- +SQLSTATE(42X01): Syntax error: Encountered "ACOS" at line 1, column 13. +Executing VALUES { fn ASIN(1.21) } -- +SQLSTATE(42X01): Syntax error: Encountered "ASIN" at line 1, column 13. +Executing VALUES { fn ATAN(0.34) } -- +SQLSTATE(42X01): Syntax error: Encountered "ATAN" at line 1, column 13. +Executing VALUES { fn ATAN2(0.56, 1.2) } -- +SQLSTATE(42X01): Syntax error: Encountered "ATAN2" at line 1, column 13. +Executing VALUES { fn CEILING(3.45) } -- +SQLSTATE(42X01): Syntax error: Encountered "CEILING" at line 1, column 13. +Executing VALUES { fn COS(1.2) } -- +SQLSTATE(42X01): Syntax error: Encountered "COS" at line 1, column 13. +Executing VALUES { fn COT(3.4) } -- +SQLSTATE(42X01): Syntax error: Encountered "COT" at line 1, column 13. +Executing VALUES { fn DEGREES(2.1) } -- +SQLSTATE(42X01): Syntax error: Encountered "DEGREES" at line 1, column 13. +Executing VALUES { fn EXP(2.3) } -- +SQLSTATE(42X01): Syntax error: Encountered "EXP" at line 1, column 13. +Executing VALUES { fn FLOOR(3.22) } -- +SQLSTATE(42X01): Syntax error: Encountered "FLOOR" at line 1, column 13. +Executing VALUES { fn LOG(34.1) } -- +SQLSTATE(42X01): Syntax error: Encountered "LOG" at line 1, column 13. +Executing VALUES { fn LOG10(18.7) } -- +SQLSTATE(42X01): Syntax error: Encountered "LOG10" at line 1, column 13. +Executing VALUES { fn PI() } -- +SQLSTATE(42X01): Syntax error: Encountered "PI" at line 1, column 13. +Executing VALUES { fn POWER(2, 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "POWER" at line 1, column 13. +Executing VALUES { fn RADIANS(54) } -- +SQLSTATE(42X01): Syntax error: Encountered "RADIANS" at line 1, column 13. +Executing VALUES { fn RAND(17) } -- +SQLSTATE(42X01): Syntax error: Encountered "RAND" at line 1, column 13. +Executing VALUES { fn ROUND(345.345, 1) } -- +SQLSTATE(42X01): Syntax error: Encountered "ROUND" at line 1, column 13. +Executing VALUES { fn SIGN(-34) } -- +SQLSTATE(42X01): Syntax error: Encountered "SIGN" at line 1, column 13. +Executing VALUES { fn SIN(0.32) } -- +SQLSTATE(42X01): Syntax error: Encountered "SIN" at line 1, column 13. +Executing VALUES { fn TAN(0.57) } -- +SQLSTATE(42X01): Syntax error: Encountered "TAN" at line 1, column 13. +Executing VALUES { fn TRUNCATE(345.395, 1) } -- +SQLSTATE(42X01): Syntax error: Encountered "TRUNCATE" at line 1, column 13. +Test escaped string functions - JDBC 3.0 C.2 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn CONCAT('hello', 'there') } -- = >hellothere< << +Executing VALUES { fn LENGTH('four ') } -- = >4< << +Executing VALUES { fn LCASE('Fernando Alonso') } -- = >fernando alonso< << +Executing VALUES { fn LOCATE('jour', 'Bonjour') } -- = >4< << +Executing VALUES { fn LTRIM(' left trim ') } -- = >left trim < << +Executing VALUES { fn RTRIM(' right trim ') } -- = > right trim< << +Executing VALUES { fn SUBSTRING('Ruby the Rubicon Jeep', 10, 7) } -- = >Rubicon< << +Executing VALUES { fn UCASE('Fernando Alonso') } -- = >FERNANDO ALONSO< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ASCII('Yellow') } -- +SQLSTATE(42X01): Syntax error: Encountered "ASCII" at line 1, column 13. +Executing VALUES { fn CHAR(65) } -- = >65 < << +FAIL function works but not declared in list: CHAR +Executing VALUES { fn DIFFERENCE('Pires', 'Piers') } -- +SQLSTATE(42X01): Syntax error: Encountered "DIFFERENCE" at line 1, column 13. +Executing VALUES { fn INSERT('Bill Clinton', 4, 'William') } -- +SQLSTATE(42X01): Syntax error: Encountered "INSERT" at line 1, column 13. +Executing VALUES { fn LEFT('Bonjour', 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "LEFT" at line 1, column 13. +Executing VALUES { fn REPEAT('echo', 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "REPEAT" at line 1, column 13. +Executing VALUES { fn REPLACE('to be or not to be', 'be', 'England') } -- +SQLSTATE(42X01): Syntax error: Encountered "REPLACE" at line 1, column 13. +Executing VALUES { fn SOUNDEX('Derby') } -- +SQLSTATE(42X01): Syntax error: Encountered "SOUNDEX" at line 1, column 13. +Executing VALUES { fn SPACE(12) } -- +SQLSTATE(42X01): Syntax error: Encountered "SPACE" at line 1, column 13. +Test escaped date time functions - JDBC 3.0 C.3 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES CASE WHEN { fn CURDATE()} = CURRENT_DATE THEN 'OK' ELSE 'wrong' END -- = >OK< << +Executing VALUES CASE WHEN { fn CURTIME()} = CURRENT_TIME THEN 'OK' ELSE 'wrong' END -- = >OK< << +Executing VALUES { fn HOUR({t '16:13:03'}) } -- = >16< << +Executing VALUES { fn MINUTE({t '16:13:03'}) } -- = >13< << +Executing VALUES { fn MONTH({d '1995-12-19'}) } -- = >12< << +Executing VALUES { fn SECOND({t '16:13:03'}) } -- = >3< << +Executing VALUES { fn TIMESTAMPADD(SQL_TSI_DAY, 7, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}) } -- = >xxxxxxFILTERED-TIMESTAMPxxxxx< << +Executing VALUES { fn TIMESTAMPDIFF(SQL_TSI_DAY, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}) } -- = >683< << +Executing VALUES { fn YEAR({d '1995-12-19'}) } -- = >1995< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn DAYNAME({d '1995-12-19'h}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYNAME" at line 1, column 13. +Executing VALUES { fn DAYOFMONTH({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFMONTH" at line 1, column 13. +Executing VALUES { fn DAYOFWEEK({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFWEEK" at line 1, column 13. +Executing VALUES { fn DAYOFYEAR({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFYEAR" at line 1, column 13. +Executing VALUES { fn MONTHNAME({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "MONTHNAME" at line 1, column 13. +Executing VALUES CASE WHEN { fn NOW()} = CURRENT_TIMESTAMP THEN 'OK' ELSE 'wrong' END -- +SQLSTATE(42X01): Syntax error: Encountered "NOW" at line 1, column 23. +Executing VALUES { fn QUARTER({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "QUARTER" at line 1, column 13. +Executing VALUES { fn WEEK({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "WEEK" at line 1, column 13. +Test escaped system functions - JDBC 3.0 C.4 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn USER() } -- = >APP< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn DATABASE() } -- +SQLSTATE(42X01): Syntax error: Encountered "DATABASE" at line 1, column 13. +Executing VALUES { fn IFNULL('this', 'that') } -- +SQLSTATE(42X01): Syntax error: Encountered "IFNULL" at line 1, column 13. --------------------------------------- Referential action values RESTRICT = 1