db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r356562 [2/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 GMT
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out?rev=356562&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out Tue Dec 13 10:23:42 2005
@@ -0,0 +1,970 @@
+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 2, 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_CURSH200C1
+Statement Text: 
+	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_CURSH200C1
+Statement Text: 
+	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_CURSH200C1
+Statement Text: 
+	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_CURSH200C1
+Statement Text: 
+	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_CURSH200C1
+Statement Text: 
+	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_CURSH200C1
+Statement Text: 
+	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_CURSH200C1
+Statement Text: 
+	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_CURSH200C1
+Statement Text: 
+	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_CURSH200C1
+Statement Text: 
+	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/DerbyNet/optimizerOverrides.out
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/metadata.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/metadata.out?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/metadata.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/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/DerbyNetClient/odbc_metadata.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/odbc_metadata.out?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/odbc_metadata.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/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



Mime
View raw message