db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r125133 - /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql
Date Fri, 14 Jan 2005 01:56:16 GMT
Author: djd
Date: Thu Jan 13 17:56:14 2005
New Revision: 125133

URL: http://svn.apache.org/viewcvs?view=rev&rev=125133
Log:
Fix subqueryFlattening test for linux.

Modified:
   incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
   incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out?view=diff&rev=125133&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out&r1=125132&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out&r2=125133
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out	(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out	Thu Jan 13 17:56:14 2005
@@ -79,7 +79,7 @@
 ij> -- subqueries that should get flattened
 call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
 0 rows inserted/updated/deleted
-ij> maximumdisplaywidth 2000;
+ij> maximumdisplaywidth 40000;
 ij> -- simple IN
 select * from outer1 o where o.c1 in (select c1 from idx1);
 C1         |C2         |C3         
@@ -150,7 +150,33 @@
 null			qualifiers:
 None
 	Right result set:
-		Index Scan ResultSet for IDX1 using index IDX1_1 at ser&
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 2
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=2
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
 ij> -- simple EXISTS
 select * from outer1 o where exists (select * from idx1 i where o.c1 = i.c1);
 C1         |C2         |C3         
@@ -221,7 +247,33 @@
 null			qualifiers:
 None
 	Right result set:
-		Index Scan ResultSet for IDX1 usi&
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 2
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=2
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
 ij> -- simple ANY
 select * from outer1 o where o.c1 = ANY (select c1 from idx1);
 C1         |C2         |C3         
@@ -292,7 +344,33 @@
 null			qualifiers:
 None
 	Right result set:
-		Index Scan ResultSet for IDX1 using index IDX1_1 at&
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 2
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=2
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
 ij> -- another simple ANY
 select * from outer1 o where o.c2 > ANY (select c1 from idx1 i where o.c1 = i.c1);
 C1         |C2         |C3         
@@ -363,7 +441,37 @@
 null			qualifiers:
 None
 	Right result set:
-		Index Scan ResultSet fo&
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 2
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=2
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+Column[0][0] Id: 0
+Operator: <
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
 ij> -- comparisons with parameters
 prepare p1 as 'select * from outer1 o where exists (select * from idx1 i where i.c1 = ?)';
 ij> execute p1 using 'values 1';
@@ -438,7 +546,31 @@
 	Ordered null semantics on the following columns: 
 			stop position: 
 	> on first 1 column(s).
-	Ordered null semantics on the foll&
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
+	Right result set:
+		Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 2
+		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=2
+			Number of rows visited=2
+			Scan type=heap
+			start position: 
+null			stop position: 
+null			qualifiers:
+None
 ij> prepare p2 as 'select * from outer1 o where ? = ANY (select c1 from idx1)';
 ij> execute p2 using 'values 1';
 IJ WARNING: Autocommit may close using result set
@@ -512,7 +644,31 @@
 	Ordered null semantics on the following columns: 
 			stop position: 
 	> on first 1 column(s).
-	Ordered null semantics on the following columns: &
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
+	Right result set:
+		Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 2
+		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=2
+			Number of rows visited=2
+			Scan type=heap
+			start position: 
+null			stop position: 
+null			qualifiers:
+None
 ij> -- mix constants with correlation columns
 select * from outer1 o where exists (select * from idx2 i where o.c1 = i.c1 and i.c2 = 2);
 C1         |C2         |C3         
@@ -587,7 +743,35 @@
 			qualifiers:
 Column[0][0] Id: 1
 Operator: =
-Ordered nulls: &
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+	Right result set:
+		Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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=1
+			Number of rows visited=2
+			Scan type=heap
+			start position: 
+null			stop position: 
+null			qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
 ij> -- multiple tables in subquery
 select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1 and i.c2 = 1);
 C1         |C2         |C3         
@@ -654,72 +838,147 @@
 			Fetch Size = 16
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
-				next time (milliseconds) = &
-ij> -- comparisons with non-join expressions
-select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0);
-C1         |C2         |C3         
------------------------------------
-1          |2          |3          
-4          |5          |6          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-Statement Name: 
-	null
-Statement Text: 
-	-- comparisons with non-join expressions
-select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0)
-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 = 2
-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 = 1
-	Rows seen from the right = 2
-	Rows filtered = 0
-	Rows returned = 2
-		constructor time (milliseconds) = 0
-		open time (milliseconds) = 0
-		next time (milliseconds) = 0
-		close time (milliseconds) = 0
-	Left result set:
-		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
-		Number of opens = 1
-		Rows seen = 1
-		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={0}
-			Number of columns fetched=1
-			Number of deleted rows visited=0
-			Number of pages visited=1
-			Number of rows qualified=1
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+				next time in milliseconds/row = 0
+			scan information: 
+				Bit set of columns fetched={0, 1}
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				Number of pages visited=1
+				Number of rows qualified=1
+				Number of rows visited=2
+				Scan type=btree
+				Tree height=1
+				start position: 
+	None
+				stop position: 
+	None
+				qualifiers:
+Column[0][0] Id: 1
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+		Right result set:
+			Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+			Number of opens = 1
+			Rows seen = 1
+			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=1
+				Number of rows visited=2
+				Scan type=heap
+				start position: 
+null				stop position: 
+null				qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+	Right result set:
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
+ij> -- comparisons with non-join expressions
+select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0);
+C1         |C2         |C3         
+-----------------------------------
+1          |2          |3          
+4          |5          |6          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name: 
+	null
+Statement Text: 
+	-- comparisons with non-join expressions
+select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0)
+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 = 2
+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 = 1
+	Rows seen from the right = 2
+	Rows filtered = 0
+	Rows returned = 2
+		constructor time (milliseconds) = 0
+		open time (milliseconds) = 0
+		next time (milliseconds) = 0
+		close time (milliseconds) = 0
+	Left result set:
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=1
 			Number of rows visited=1
 			Scan type=btree
 			Tree height=1
@@ -727,7 +986,32 @@
 	>= on first 1 column(s).
 	Ordered null semantics on the following columns: 
 			stop position: 
-	> on first 1&
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
+	Right result set:
+		Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 2
+		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=2
+			Number of rows visited=2
+			Scan type=heap
+			start position: 
+null			stop position: 
+null			qualifiers:
+None
 ij> select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 + 0 = i.c1 and i.c2 + 0 = idx1.c1 and i.c2 = 1 + 0);
 C1         |C2         |C3         
 -----------------------------------
@@ -793,7 +1077,90 @@
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
-				close time (mi&
+				close time (milliseconds) = 0
+				next time in milliseconds/row = 0
+			scan information: 
+				Bit set of columns fetched={0, 1}
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				Number of pages visited=1
+				Number of rows qualified=1
+				Number of rows visited=2
+				Scan type=btree
+				Tree height=1
+				start position: 
+	None
+				stop position: 
+	None
+				qualifiers:
+Column[0][0] Id: 1
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+		Right result set:
+			Project-Restrict ResultSet (5):
+			Number of opens = 1
+			Rows seen = 2
+			Rows filtered = 1
+			restriction = true
+			projection = false
+				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:
+				Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+				Number of opens = 1
+				Rows seen = 2
+				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=2
+					Number of rows visited=2
+					Scan type=heap
+					start position: 
+null					stop position: 
+null					qualifiers:
+None
+	Right result set:
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
 ij> -- multilevel subqueries
 -- flatten all
 select * from outer1 o where exists
@@ -867,7 +1234,83 @@
 			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}
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				Number of pages visited=1
+				Number of rows qualified=1
+				Number of rows visited=2
+				Scan type=btree
+				Tree height=1
+				start position: 
+	None
+				stop position: 
+	None
+				qualifiers:
+Column[0][0] Id: 1
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+		Right result set:
+			Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+			Number of opens = 1
+			Rows seen = 1
+			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=1
+				Number of rows visited=2
+				Scan type=heap
+				start position: 
+null				stop position: 
+null				qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+	Right result set:
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
 ij> -- only flatten bottom
 select * from outer1 o where exists
     (select * from idx2 i where exists
@@ -938,7 +1381,95 @@
 				Hash key is column number 0
 				Rows seen = 1
 				Rows filtered = 0
-					constructor time (milliseconds) = 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}
+					Number of columns fetched=2
+					Number of deleted rows visited=0
+					Number of pages visited=1
+					Number of rows qualified=2
+					Number of rows visited=2
+					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
+			Right result set:
+				Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+				Number of opens = 1
+				Rows seen = 1
+				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={0}
+					Number of columns fetched=1
+					Number of deleted rows visited=0
+					Number of pages visited=1
+					Number of rows qualified=1
+					Number of rows visited=1
+					Scan type=btree
+					Tree height=1
+					start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					qualifiers:
+None
+	End Subquery Number 0
+Project-Restrict ResultSet (2):
+Number of opens = 1
+Rows seen = 2
+Rows filtered = 1
+restriction = true
+projection = false
+	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:
+	Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 2
+	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=2
+		Number of rows visited=2
+		Scan type=heap
+		start position: 
+null		stop position: 
+null		qualifiers:
+None
 ij> -- flatten innermost into exists join, then flatten middle
 -- into outer
 select * from outer1 o where exists
@@ -1011,7 +1542,82 @@
 			Rows seen = 1
 			Rows filtered = 0
 			Fetch Size = 16
-				constructor time (millise&
+				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}
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				Number of pages visited=1
+				Number of rows qualified=1
+				Number of rows visited=2
+				Scan type=btree
+				Tree height=1
+				start position: 
+	None
+				stop position: 
+	None
+				qualifiers:
+Column[0][0] Id: 1
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+		Right result set:
+			Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+			Number of opens = 1
+			Rows seen = 1
+			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=1
+				Number of rows visited=2
+				Scan type=heap
+				start position: 
+null				stop position: 
+null				qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+	Right result set:
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share table locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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={}
+			Number of columns fetched=0
+			Number of deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	None
+			stop position: 
+	None
+			qualifiers:
+None
 ij> -- flatten a subquery that has a subquery in its select list
 -- verify that subquery gets copied up to outer block
 select * from outer1 o where c1 in
@@ -1086,7 +1692,79 @@
 Rows filtered = 0
 restriction = true
 projection = true
-	construct&
+	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 = 1
+	Rows seen from the right = 1
+	Rows filtered = 0
+	Rows returned = 1
+		constructor time (milliseconds) = 0
+		open time (milliseconds) = 0
+		next time (milliseconds) = 0
+		close time (milliseconds) = 0
+	Left result set:
+		Index Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using share table locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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}
+			Number of columns fetched=2
+			Number of deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=1
+			Number of rows visited=2
+			Scan type=btree
+			Tree height=1
+			start position: 
+	None
+			stop position: 
+	None
+			qualifiers:
+Column[0][0] Id: 1
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+	Right result set:
+		Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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=1
+			Number of rows visited=2
+			Scan type=heap
+			start position: 
+null			stop position: 
+null			qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
 ij> -- expression subqueries
 -- simple =
 select * from outer1 o where o.c1 = (select c1 from idx1 i where o.c1 = i.c1);
@@ -1159,7 +1837,37 @@
 null			qualifiers:
 None
 	Right result set:
-		Index Scan &
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 2
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=2
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
 ij> select * from outer1 o where o.c1 <= (select c1 from idx1 i where o.c1 = i.c1);
 C1         |C2         |C3         
 -----------------------------------
@@ -1228,7 +1936,37 @@
 null			qualifiers:
 None
 	Right result set:
-		Index Scan ResultSet for IDX1 using index IDX1_1 &
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 2
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=2
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+Column[0][0] Id: 0
+Operator: <
+Ordered nulls: false
+Unknown return value: true
+Negate comparison result: true
 ij> -- multiple tables in subquery
 select * from outer1 o where c1 =  (select i.c1 from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1 and i.c2 = 1);
 C1         |C2         |C3         
@@ -1295,7 +2033,87 @@
 			Fetch Size = 16
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
-				next time (milliseconds) &
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+				next time in milliseconds/row = 0
+			scan information: 
+				Bit set of columns fetched={0, 1}
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				Number of pages visited=1
+				Number of rows qualified=1
+				Number of rows visited=2
+				Scan type=btree
+				Tree height=1
+				start position: 
+	None
+				stop position: 
+	None
+				qualifiers:
+Column[0][0] Id: 1
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+		Right result set:
+			Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+			Number of opens = 1
+			Rows seen = 1
+			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=1
+				Number of rows visited=2
+				Scan type=heap
+				start position: 
+null				stop position: 
+null				qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+Column[0][1] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+	Right result set:
+		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=1
+			Number of rows visited=1
+			Scan type=btree
+			Tree height=1
+			start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+			qualifiers:
+None
 ij> -- flattening to an exists join
 -- no index on subquery table
 select * from outer1 where c1 in (select c1 from noidx);
@@ -1368,7 +2186,34 @@
 null			qualifiers:
 None
 	Right result set:
-		Hash Scan Resul&
+		Hash Scan ResultSet for NOIDX at serializable isolation level using share table locking: 
+		Number of opens = 2
+		Hash table size = 1
+		Hash key is column number 0
+		Rows seen = 1
+		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=All
+			Number of columns fetched=1
+			Number of pages visited=1
+			Number of rows qualified=2
+			Number of rows visited=2
+			Scan type=heap
+			start position: 
+null			stop position: 
+null			scan qualifiers:
+None
+			next qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
 ij> -- no unique index on subquery table
 select * from outer1 where c1 in (select c1 from nonunique_idx1);
 C1         |C2         |C3         
@@ -1421,25 +2266,56 @@
 		Number of opens = 1
 		Rows seen = 2
 		Rows filtered = 0
-		Fetch Size = 16
+		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=2
+			Number of rows visited=2
+			Scan type=heap
+			start position: 
+null			stop position: 
+null			qualifiers:
+None
+	Right result set:
+		Hash Scan ResultSet for NONUNIQUE_IDX1 using index NONUNIQUE_IDX1_1 at serializable isolation level using share table locking: 
+		Number of opens = 2
+		Hash table size = 1
+		Hash key is column number 0
+		Rows seen = 1
+		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=All
-			Number of columns fetched=3
+			Bit set of columns fetched={0}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
 			Number of pages visited=1
 			Number of rows qualified=2
 			Number of rows visited=2
-			Scan type=heap
+			Scan type=btree
+			Tree height=1
 			start position: 
-null			stop position: 
-null			qualifiers:
+	None
+			stop position: 
+	None
+			scan qualifiers:
 None
-	Right result set:
-		Hash Scan ResultSet for NONUNIQU&
+			next qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
 ij> -- columns in subquery are not superset of unique index
 select * from outer1 where c1 in (select c1 from idx2);
 C1         |C2         |C3         
@@ -1510,7 +2386,38 @@
 null			qualifiers:
 None
 	Right result set:
-		Hash Scan ResultSet for&
+		Hash Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using share table locking: 
+		Number of opens = 2
+		Hash table size = 1
+		Hash key is column number 0
+		Rows seen = 1
+		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}
+			Number of columns fetched=1
+			Number of deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=2
+			Number of rows visited=2
+			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> -- single table subquery, self join on unique column
 select * from outer1 where exists (select * from idx1 where c1 = c1);
 C1         |C2         |C3         
@@ -1582,7 +2489,44 @@
 null			qualifiers:
 None
 	Right result set:
-		Proje&
+		Project-Restrict ResultSet (4):
+		Number of opens = 2
+		Rows seen = 2
+		Rows filtered = 0
+		restriction = true
+		projection = false
+			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 Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share table locking chosen by the optimizer
+			Number of opens = 2
+			Rows seen = 2
+			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={0}
+				Number of columns fetched=1
+				Number of deleted rows visited=0
+				Number of pages visited=2
+				Number of rows qualified=2
+				Number of rows visited=2
+				Scan type=btree
+				Tree height=1
+				start position: 
+	None
+				stop position: 
+	None
+				qualifiers:
+None
 ij> -- flattening values subqueries
 -- flatten unless contains a subquery
 select * from outer1 where c1 in (values 1);
@@ -1700,11 +2644,90 @@
 					close time (milliseconds) = 0
 					restriction time (milliseconds) = 0
 					projection time (milliseconds) = 0
-					optimizer estimated c&
+				Source result set:
+					Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+					Number of opens = 1
+					Rows seen = 2
+					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}
+						Number of columns fetched=1
+						Number of pages visited=1
+						Number of rows qualified=2
+						Number of rows visited=2
+						Scan type=heap
+						start position: 
+null						stop position: 
+null						qualifiers:
+None
+	End Subquery Number 1
+Project-Restrict ResultSet (14):
+Number of opens = 1
+Rows seen = 1
+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 = 1
+	Rows seen from the right = 1
+	Rows filtered = 0
+	Rows returned = 1
+		constructor time (milliseconds) = 0
+		open time (milliseconds) = 0
+		next time (milliseconds) = 0
+		close time (milliseconds) = 0
+	Left result set:
+		Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 1
+		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=1
+			Number of rows visited=2
+			Scan type=heap
+			start position: 
+null			stop position: 
+null			qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+	Right result set:
+		Row ResultSet:
+		Number of opens = 1
+		Rows returned = 1
+			constructor time (milliseconds) = 0
+			open time (milliseconds) = 0
+			next time (milliseconds) = 0
+			close time (milliseconds) = 0
 ij> -- beetle 4459 - problems with flattening to exist joins and then flattening to 
 -- normal join
 -- non correlated exists subquery with conditional join
-maximumdisplaywidth 5000;
+maximumdisplaywidth 40000;
 ij> select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) 
 where exists (select c1 from idx1);
 C1         
@@ -2132,7 +3155,42 @@
 					Number of columns fetched=1
 					Number of deleted rows visited=0
 					Number of pages visited=2
-					Number of rows q&
+					Number of rows qualified=2
+					Number of rows visited=2
+					Scan type=btree
+					Tree height=1
+					start position: 
+	None
+					stop position: 
+	None
+					qualifiers:
+None
+	Right result set:
+		Table Scan ResultSet for OUTER2 at serializable isolation level using share table locking chosen by the optimizer
+		Number of opens = 2
+		Rows seen = 2
+		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={0}
+			Number of columns fetched=1
+			Number of pages visited=1
+			Number of rows qualified=2
+			Number of rows visited=4
+			Scan type=heap
+			start position: 
+null			stop position: 
+null			qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
 ij> -- original reported bug
 create table business(businesskey int, name varchar(50), changedate int);
 0 rows inserted/updated/deleted
@@ -2309,7 +3367,6 @@
 Ordered nulls: false
 Unknown return value: false
 Negate comparison result: false
-			&
 ij> -- clean up
 drop table outer1;
 0 rows inserted/updated/deleted
@@ -2366,7 +3423,7 @@
 6 rows inserted/updated/deleted
 ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
 0 rows inserted/updated/deleted
-ij> maximumdisplaywidth 10000;
+ij> maximumdisplaywidth 40000;
 ij> -- NOT IN is flattened
 SELECT COUNT(*) FROM
 ( SELECT ID FROM DOCS WHERE
@@ -5550,25 +6607,22 @@
 Ordered nulls: false
 Unknown return value: false
 Negate comparison result: false
-ij> -- watch out result, should return 2,3,4,2
-select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1
-not in (select t3.c1 from t3, t4));
+ij> select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4));
 C1         
 -----------
 2          
 3          
 4          
 2          
-ij> -- can not be flattened, should be materialized
+ij> -- watch out result, should return 2,3,4,2
+-- can not be flattened, should be materialized
 values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Statement Name: 
 	null
 Statement Text: 
-	-- watch out result, should return 2,3,4,2
-select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1
-not in (select t3.c1 from t3, t4))
+	select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4))
 Parse Time: 0
 Bind Time: 0
 Optimize Time: 0
@@ -5779,25 +6833,351 @@
 																					Rows seen from the right = 3
 																					Rows returned = 50
 																						constructor time (milliseconds) = 0
-							&
-ij> -- should return 1,5,1
-select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not
-in (select t3.c1 from t3, t4));
+																						open time (milliseconds) = 0
+																						next time (milliseconds) = 0
+																						close time (milliseconds) = 0
+																					Left result set:
+																						Union ResultSet:
+																						Number of opens = 5
+																						Rows seen from the left = 44
+																						Rows seen from the right = 3
+																						Rows returned = 47
+																							constructor time (milliseconds) = 0
+																							open time (milliseconds) = 0
+																							next time (milliseconds) = 0
+																							close time (milliseconds) = 0
+																						Left result set:
+																							Union ResultSet:
+																							Number of opens = 5
+																							Rows seen from the left = 41
+																							Rows seen from the right = 3
+																							Rows returned = 44
+																								constructor time (milliseconds) = 0
+																								open time (milliseconds) = 0
+																								next time (milliseconds) = 0
+																								close time (milliseconds) = 0
+																							Left result set:
+																								Union ResultSet:
+																								Number of opens = 5
+																								Rows seen from the left = 38
+																								Rows seen from the right = 3
+																								Rows returned = 41
+																									constructor time (milliseconds) = 0
+																									open time (milliseconds) = 0
+																									next time (milliseconds) = 0
+																									close time (milliseconds) = 0
+																								Left result set:
+																									Union ResultSet:
+																									Number of opens = 5
+																									Rows seen from the left = 35
+																									Rows seen from the right = 3
+																									Rows returned = 38
+																										constructor time (milliseconds) = 0
+																										open time (milliseconds) = 0
+																										next time (milliseconds) = 0
+																										close time (milliseconds) = 0
+																									Left result set:
+																										Union ResultSet:
+																										Number of opens = 5
+																										Rows seen from the left = 32
+																										Rows seen from the right = 3
+																										Rows returned = 35
+																											constructor time (milliseconds) = 0
+																											open time (milliseconds) = 0
+																											next time (milliseconds) = 0
+																											close time (milliseconds) = 0
+																										Left result set:
+																											Union ResultSet:
+																											Number of opens = 5
+																											Rows seen from the left = 29
+																											Rows seen from the right = 3
+																											Rows returned = 32
+																												constructor time (milliseconds) = 0
+																												open time (milliseconds) = 0
+																												next time (milliseconds) = 0
+																												close time (milliseconds) = 0
+																											Left result set:
+																												Union ResultSet:
+																												Number of opens = 5
+																												Rows seen from the left = 26
+																												Rows seen from the right = 3
+																												Rows returned = 29
+																													constructor time (milliseconds) = 0
+																													open time (milliseconds) = 0
+																													next time (milliseconds) = 0
+																													close time (milliseconds) = 0
+																												Left result set:
+																													Union ResultSet:
+																													Number of opens = 5
+																													Rows seen from the left = 23
+																													Rows seen from the right = 3
+																													Rows returned = 26
+																														constructor time (milliseconds) = 0
+																														open time (milliseconds) = 0
+																														next time (milliseconds) = 0
+																														close time (milliseconds) = 0
+																													Left result set:
+																														Union ResultSet:
+																														Number of opens = 5
+																														Rows seen from the left = 20
+																														Rows seen from the right = 3
+																														Rows returned = 23
+																															constructor time (milliseconds) = 0
+																															open time (milliseconds) = 0
+																															next time (milliseconds) = 0
+																															close time (milliseconds) = 0
+																														Left result set:
+																															Union ResultSet:
+																															Number of opens = 5
+																															Rows seen from the left = 17
+																															Rows seen from the right = 3
+																															Rows returned = 20
+																																constructor time (milliseconds) = 0
+																																open time (milliseconds) = 0
+																																next time (milliseconds) = 0
+																																close time (milliseconds) = 0
+																															Left result set:
+																																Union ResultSet:
+																																Number of opens = 5
+																																Rows seen from the left = 14
+																																Rows seen from the right = 3
+																																Rows returned = 17
+																																	constructor time (milliseconds) = 0
+																																	open time (milliseconds) = 0
+																																	next time (milliseconds) = 0
+																																	close time (milliseconds) = 0
+																																Left result set:
+																																	Union ResultSet:
+																																	Number of opens = 5
+																																	Rows seen from the left = 11
+																																	Rows seen from the right = 3
+																																	Rows returned = 14
+																																		constructor time (milliseconds) = 0
+																																		open time (milliseconds) = 0
+																																		next time (milliseconds) = 0
+																																		close time (milliseconds) = 0
+																																	Left result set:
+																																		Union ResultSet:
+																																		Number of opens = 5
+																																		Rows seen from the left = 8
+																																		Rows seen from the right = 3
+																																		Rows returned = 11
+																																			constructor time (milliseconds) = 0
+																																			open time (milliseconds) = 0
+																																			next time (milliseconds) = 0
+																																			close time (milliseconds) = 0
+																																		Left result set:
+																																			Union ResultSet:
+																																			Number of opens = 5
+																																			Rows seen from the left = 5
+																																			Rows seen from the right = 3
+																																			Rows returned = 8
+																																				constructor time (milliseconds) = 0
+																																				open time (milliseconds) = 0
+																																				next time (milliseconds) = 0
+																																				close time (milliseconds) = 0
+																																			Left result set:
+																																				Row ResultSet:
+																																				Number of opens = 5
+																																				Rows returned = 5
+																																					constructor time (milliseconds) = 0
+																																					open time (milliseconds) = 0
+																																					next time (milliseconds) = 0
+																																					close time (milliseconds) = 0
+																																			Right result set:
+																																				Row ResultSet:
+																																				Number of opens = 3
+																																				Rows returned = 3
+																																					constructor time (milliseconds) = 0
+																																					open time (milliseconds) = 0
+																																					next time (milliseconds) = 0
+																																					close time (milliseconds) = 0
+																																		Right result set:
+																																			Row ResultSet:
+																																			Number of opens = 3
+																																			Rows returned = 3
+																																				constructor time (milliseconds) = 0
+																																				open time (milliseconds) = 0
+																																				next time (milliseconds) = 0
+																																				close time (milliseconds) = 0
+																																	Right result set:
+																																		Row ResultSet:
+																																		Number of opens = 3
+																																		Rows returned = 3
+																																			constructor time (milliseconds) = 0
+																																			open time (milliseconds) = 0
+																																			next time (milliseconds) = 0
+																																			close time (milliseconds) = 0
+																																Right result set:
+																																	Row ResultSet:
+																																	Number of opens = 3
+																																	Rows returned = 3
+																																		constructor time (milliseconds) = 0
+																																		open time (milliseconds) = 0
+																																		next time (milliseconds) = 0
+																																		close time (milliseconds) = 0
+																															Right result set:
+																																Row ResultSet:
+																																Number of opens = 3
+																																Rows returned = 3
+																																	constructor time (milliseconds) = 0
+																																	open time (milliseconds) = 0
+																																	next time (milliseconds) = 0
+																																	close time (milliseconds) = 0
+																														Right result set:
+																															Row ResultSet:
+																															Number of opens = 3
+																															Rows returned = 3
+																																constructor time (milliseconds) = 0
+																																open time (milliseconds) = 0
+																																next time (milliseconds) = 0
+																																close time (milliseconds) = 0
+																													Right result set:
+																														Row ResultSet:
+																														Number of opens = 3
+																														Rows returned = 3
+																															constructor time (milliseconds) = 0
+																															open time (milliseconds) = 0
+																															next time (milliseconds) = 0
+																															close time (milliseconds) = 0
+																												Right result set:
+																													Row ResultSet:
+																													Number of opens = 3
+																													Rows returned = 3
+																														constructor time (milliseconds) = 0
+																														open time (milliseconds) = 0
+																														next time (milliseconds) = 0
+																														close time (milliseconds) = 0
+																											Right result set:
+																												Row ResultSet:
+																												Number of opens = 3
+																												Rows returned = 3
+																													constructor time (milliseconds) = 0
+																													open time (milliseconds) = 0
+																													next time (milliseconds) = 0
+																													close time (milliseconds) = 0
+																										Right result set:
+																											Row ResultSet:
+																											Number of opens = 3
+																											Rows returned = 3
+																												constructor time (milliseconds) = 0
+																												open time (milliseconds) = 0
+																												next time (milliseconds) = 0
+																												close time (milliseconds) = 0
+																									Right result set:
+																										Row ResultSet:
+																										Number of opens = 3
+																										Rows returned = 3
+																											constructor time (milliseconds) = 0
+																											open time (milliseconds) = 0
+																											next time (milliseconds) = 0
+																											close time (milliseconds) = 0
+																								Right result set:
+																									Row ResultSet:
+																									Number of opens = 3
+																									Rows returned = 3
+																										constructor time (milliseconds) = 0
+																										open time (milliseconds) = 0
+																										next time (milliseconds) = 0
+																										close time (milliseconds) = 0
+																							Right result set:
+																								Row ResultSet:
+																								Number of opens = 3
+																								Rows returned = 3
+																									constructor time (milliseconds) = 0
+																									open time (milliseconds) = 0
+																									next time (milliseconds) = 0
+																									close time (milliseconds) = 0
+																						Right result set:
+																							Row ResultSet:
+																							Number of opens = 3
+																							Rows returned = 3
+																								constructor time (milliseconds) = 0
+																								open time (milliseconds) = 0
+																								next time (milliseconds) = 0
+																								close time (milliseconds) = 0
+																					Right result set:
+																						Row ResultSet:
+																						Number of opens = 3
+																						Rows returned = 3
+																							constructor time (milliseconds) = 0
+																							open time (milliseconds) = 0
+																							next time (milliseconds) = 0
+																							close time (milliseconds) = 0
+																				Right result set:
+																					Row ResultSet:
+																					Number of opens = 3
+																					Rows returned = 3
+																						constructor time (milliseconds) = 0
+																						open time (milliseconds) = 0
+																						next time (milliseconds) = 0
+																						close time (milliseconds) = 0
+																			Right result set:
+																				Row ResultSet:
+																				Number of opens = 3
+																				Rows returned = 3
+																					constructor time (milliseconds) = 0
+																					open time (milliseconds) = 0
+																					next time (milliseconds) = 0
+																					close time (milliseconds) = 0
+																		Right result set:
+																			Row ResultSet:
+																			Number of opens = 3
+																			Rows returned = 3
+																				constructor time (milliseconds) = 0
+																				open time (milliseconds) = 0
+																				next time (milliseconds) = 0
+																				close time (milliseconds) = 0
+																	Right result set:
+																		Row ResultSet:
+																		Number of opens = 3
+																		Rows returned = 3
+																			constructor time (milliseconds) = 0
+																			open time (milliseconds) = 0
+																			next time (milliseconds) = 0
+																			close time (milliseconds) = 0
+																Right result set:
+																	Row ResultSet:
+																	Number of opens = 3
+																	Rows returned = 3
+																		constructor time (milliseconds) = 0
+																		open time (milliseconds) = 0
+																		next time (milliseconds) = 0
+																		close time (milliseconds) = 0
+															Right result set:
+																Row ResultSet:
+																Number of opens = 3
+																Rows returned = 3
+																	constructor time (milliseconds) = 0
+																	open time (milliseconds) = 0
+																	next time (milliseconds) = 0
+																	close time (milliseconds) = 0
+														Right result set:
+															Row ResultSet:
+															Number of opens = 3
+															Rows returned = 3
+																constructor time (milliseconds) = 0
+																open time (milliseconds) = 0
+																next time (milliseconds) = 0
+																close time (milliseconds) = 0
+													Right result set:
+														Row ResultSet:
+								
+ij> select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4));
 C1         
 -----------
 1          
 5          
 1          
-ij> -- can not be flattened, should be materialized
+ij> -- should return 1,5,1
+-- can not be flattened, should be materialized
 values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Statement Name: 
 	null
 Statement Text: 
-	-- should return 1,5,1
-select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not
-in (select t3.c1 from t3, t4))
+	select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4))
 Parse Time: 0
 Bind Time: 0
 Optimize Time: 0
@@ -6008,7 +7388,336 @@
 																					Rows seen from the right = 3
 																					Rows returned = 50
 																						constructor time (milliseconds) = 0
-																						open time&
+																						open time (milliseconds) = 0
+																						next time (milliseconds) = 0
+																						close time (milliseconds) = 0
+																					Left result set:
+																						Union ResultSet:
+																						Number of opens = 5
+																						Rows seen from the left = 44
+																						Rows seen from the right = 3
+																						Rows returned = 47
+																							constructor time (milliseconds) = 0
+																							open time (milliseconds) = 0
+																							next time (milliseconds) = 0
+																							close time (milliseconds) = 0
+																						Left result set:
+																							Union ResultSet:
+																							Number of opens = 5
+																							Rows seen from the left = 41
+																							Rows seen from the right = 3
+																							Rows returned = 44
+																								constructor time (milliseconds) = 0
+																								open time (milliseconds) = 0
+																								next time (milliseconds) = 0
+																								close time (milliseconds) = 0
+																							Left result set:
+																								Union ResultSet:
+																								Number of opens = 5
+																								Rows seen from the left = 38
+																								Rows seen from the right = 3
+																								Rows returned = 41
+																									constructor time (milliseconds) = 0
+																									open time (milliseconds) = 0
+																									next time (milliseconds) = 0
+																									close time (milliseconds) = 0
+																								Left result set:
+																									Union ResultSet:
+																									Number of opens = 5
+																									Rows seen from the left = 35
+																									Rows seen from the right = 3
+																									Rows returned = 38
+																										constructor time (milliseconds) = 0
+																										open time (milliseconds) = 0
+																										next time (milliseconds) = 0
+																										close time (milliseconds) = 0
+																									Left result set:
+																										Union ResultSet:
+																										Number of opens = 5
+																										Rows seen from the left = 32
+																										Rows seen from the right = 3
+																										Rows returned = 35
+																											constructor time (milliseconds) = 0
+																											open time (milliseconds) = 0
+																											next time (milliseconds) = 0
+																											close time (milliseconds) = 0
+																										Left result set:
+																											Union ResultSet:
+																											Number of opens = 5
+																											Rows seen from the left = 29
+																											Rows seen from the right = 3
+																											Rows returned = 32
+																												constructor time (milliseconds) = 0
+																												open time (milliseconds) = 0
+																												next time (milliseconds) = 0
+																												close time (milliseconds) = 0
+																											Left result set:
+																												Union ResultSet:
+																												Number of opens = 5
+																												Rows seen from the left = 26
+																												Rows seen from the right = 3
+																												Rows returned = 29
+																													constructor time (milliseconds) = 0
+																													open time (milliseconds) = 0
+																													next time (milliseconds) = 0
+																													close time (milliseconds) = 0
+																												Left result set:
+																													Union ResultSet:
+																													Number of opens = 5
+																													Rows seen from the left = 23
+																													Rows seen from the right = 3
+																													Rows returned = 26
+																														constructor time (milliseconds) = 0
+																														open time (milliseconds) = 0
+																														next time (milliseconds) = 0
+																														close time (milliseconds) = 0
+																													Left result set:
+																														Union ResultSet:
+																														Number of opens = 5
+																														Rows seen from the left = 20
+																														Rows seen from the right = 3
+																														Rows returned = 23
+																															constructor time (milliseconds) = 0
+																															open time (milliseconds) = 0
+																															next time (milliseconds) = 0
+																															close time (milliseconds) = 0
+																														Left result set:
+																															Union ResultSet:
+																															Number of opens = 5
+																															Rows seen from the left = 17
+																															Rows seen from the right = 3
+																															Rows returned = 20
+																																constructor time (milliseconds) = 0
+																																open time (milliseconds) = 0
+																																next time (milliseconds) = 0
+																																close time (milliseconds) = 0
+																															Left result set:
+																																Union ResultSet:
+																																Number of opens = 5
+																																Rows seen from the left = 14
+																																Rows seen from the right = 3
+																																Rows returned = 17
+																																	constructor time (milliseconds) = 0
+																																	open time (milliseconds) = 0
+																																	next time (milliseconds) = 0
+																																	close time (milliseconds) = 0
+																																Left result set:
+																																	Union ResultSet:
+																																	Number of opens = 5
+																																	Rows seen from the left = 11
+																																	Rows seen from the right = 3
+																																	Rows returned = 14
+																																		constructor time (milliseconds) = 0
+																																		open time (milliseconds) = 0
+																																		next time (milliseconds) = 0
+																																		close time (milliseconds) = 0
+																																	Left result set:
+																																		Union ResultSet:
+																																		Number of opens = 5
+																																		Rows seen from the left = 8
+																																		Rows seen from the right = 3
+																																		Rows returned = 11
+																																			constructor time (milliseconds) = 0
+																																			open time (milliseconds) = 0
+																																			next time (milliseconds) = 0
+																																			close time (milliseconds) = 0
+																																		Left result set:
+																																			Union ResultSet:
+																																			Number of opens = 5
+																																			Rows seen from the left = 5
+																																			Rows seen from the right = 3
+																																			Rows returned = 8
+																																				constructor time (milliseconds) = 0
+																																				open time (milliseconds) = 0
+																																				next time (milliseconds) = 0
+																																				close time (milliseconds) = 0
+																																			Left result set:
+																																				Row ResultSet:
+																																				Number of opens = 5
+																																				Rows returned = 5
+																																					constructor time (milliseconds) = 0
+																																					open time (milliseconds) = 0
+																																					next time (milliseconds) = 0
+																																					close time (milliseconds) = 0
+																																			Right result set:
+																																				Row ResultSet:
+																																				Number of opens = 3
+																																				Rows returned = 3
+																																					constructor time (milliseconds) = 0
+																																					open time (milliseconds) = 0
+																																					next time (milliseconds) = 0
+																																					close time (milliseconds) = 0
+																																		Right result set:
+																																			Row ResultSet:
+																																			Number of opens = 3
+																																			Rows returned = 3
+																																				constructor time (milliseconds) = 0
+																																				open time (milliseconds) = 0
+																																				next time (milliseconds) = 0
+																																				close time (milliseconds) = 0
+																																	Right result set:
+																																		Row ResultSet:
+																																		Number of opens = 3
+																																		Rows returned = 3
+																																			constructor time (milliseconds) = 0
+																																			open time (milliseconds) = 0
+																																			next time (milliseconds) = 0
+																																			close time (milliseconds) = 0
+																																Right result set:
+																																	Row ResultSet:
+																																	Number of opens = 3
+																																	Rows returned = 3
+																																		constructor time (milliseconds) = 0
+																																		open time (milliseconds) = 0
+																																		next time (milliseconds) = 0
+																																		close time (milliseconds) = 0
+																															Right result set:
+																																Row ResultSet:
+																																Number of opens = 3
+																																Rows returned = 3
+																																	constructor time (milliseconds) = 0
+																																	open time (milliseconds) = 0
+																																	next time (milliseconds) = 0
+																																	close time (milliseconds) = 0
+																														Right result set:
+																															Row ResultSet:
+																															Number of opens = 3
+																															Rows returned = 3
+																																constructor time (milliseconds) = 0
+																																open time (milliseconds) = 0
+																																next time (milliseconds) = 0
+																																close time (milliseconds) = 0
+																													Right result set:
+																														Row ResultSet:
+																														Number of opens = 3
+																														Rows returned = 3
+																															constructor time (milliseconds) = 0
+																															open time (milliseconds) = 0
+																															next time (milliseconds) = 0
+																															close time (milliseconds) = 0
+																												Right result set:
+																													Row ResultSet:
+																													Number of opens = 3
+																													Rows returned = 3
+																														constructor time (milliseconds) = 0
+																														open time (milliseconds) = 0
+																														next time (milliseconds) = 0
+																														close time (milliseconds) = 0
+																											Right result set:
+																												Row ResultSet:
+																												Number of opens = 3
+																												Rows returned = 3
+																													constructor time (milliseconds) = 0
+																													open time (milliseconds) = 0
+																													next time (milliseconds) = 0
+																													close time (milliseconds) = 0
+																										Right result set:
+																											Row ResultSet:
+																											Number of opens = 3
+																											Rows returned = 3
+																												constructor time (milliseconds) = 0
+																												open time (milliseconds) = 0
+																												next time (milliseconds) = 0
+																												close time (milliseconds) = 0
+																									Right result set:
+																										Row ResultSet:
+																										Number of opens = 3
+																										Rows returned = 3
+																											constructor time (milliseconds) = 0
+																											open time (milliseconds) = 0
+																											next time (milliseconds) = 0
+																											close time (milliseconds) = 0
+																								Right result set:
+																									Row ResultSet:
+																									Number of opens = 3
+																									Rows returned = 3
+																										constructor time (milliseconds) = 0
+																										open time (milliseconds) = 0
+																										next time (milliseconds) = 0
+																										close time (milliseconds) = 0
+																							Right result set:
+																								Row ResultSet:
+																								Number of opens = 3
+																								Rows returned = 3
+																									constructor time (milliseconds) = 0
+																									open time (milliseconds) = 0
+																									next time (milliseconds) = 0
+																									close time (milliseconds) = 0
+																						Right result set:
+																							Row ResultSet:
+																							Number of opens = 3
+																							Rows returned = 3
+																								constructor time (milliseconds) = 0
+																								open time (milliseconds) = 0
+																								next time (milliseconds) = 0
+																								close time (milliseconds) = 0
+																					Right result set:
+																						Row ResultSet:
+																						Number of opens = 3
+																						Rows returned = 3
+																							constructor time (milliseconds) = 0
+																							open time (milliseconds) = 0
+																							next time (milliseconds) = 0
+																							close time (milliseconds) = 0
+																				Right result set:
+																					Row ResultSet:
+																					Number of opens = 3
+																					Rows returned = 3
+																						constructor time (milliseconds) = 0
+																						open time (milliseconds) = 0
+																						next time (milliseconds) = 0
+																						close time (milliseconds) = 0
+																			Right result set:
+																				Row ResultSet:
+																				Number of opens = 3
+																				Rows returned = 3
+																					constructor time (milliseconds) = 0
+																					open time (milliseconds) = 0
+																					next time (milliseconds) = 0
+																					close time (milliseconds) = 0
+																		Right result set:
+																			Row ResultSet:
+																			Number of opens = 3
+																			Rows returned = 3
+																				constructor time (milliseconds) = 0
+																				open time (milliseconds) = 0
+																				next time (milliseconds) = 0
+																				close time (milliseconds) = 0
+																	Right result set:
+																		Row ResultSet:
+																		Number of opens = 3
+																		Rows returned = 3
+																			constructor time (milliseconds) = 0
+																			open time (milliseconds) = 0
+																			next time (milliseconds) = 0
+																			close time (milliseconds) = 0
+																Right result set:
+																	Row ResultSet:
+																	Number of opens = 3
+																	Rows returned = 3
+																		constructor time (milliseconds) = 0
+																		open time (milliseconds) = 0
+																		next time (milliseconds) = 0
+																		close time (milliseconds) = 0
+															Right result set:
+																Row ResultSet:
+																Number of opens = 3
+																Rows returned = 3
+																	constructor time (milliseconds) = 0
+																	open time (milliseconds) = 0
+																	next time (milliseconds) = 0
+																	close time (milliseconds) = 0
+														Right result set:
+															Row ResultSet:
+															Number of opens = 3
+															Rows returned = 3
+																constructor time (milliseconds) = 0
+																open time (milliseconds) = 0
+																next time (milliseconds) = 0
+																close time (milliseconds) = 0
+													Right result set:
+														Row ResultSet:
+												
 ij> drop table colls;
 0 rows inserted/updated/deleted
 ij> drop table docs;

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql?view=diff&rev=125133&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql&r1=125132&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql&r2=125133
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql	(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql	Thu Jan 13 17:56:14 2005
@@ -49,7 +49,7 @@
 
 -- subqueries that should get flattened
 call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-maximumdisplaywidth 2000;
+maximumdisplaywidth 40000;
 
 -- simple IN
 select * from outer1 o where o.c1 in (select c1 from idx1);
@@ -148,7 +148,7 @@
 -- beetle 4459 - problems with flattening to exist joins and then flattening to 
 -- normal join
 -- non correlated exists subquery with conditional join
-maximumdisplaywidth 5000;
+maximumdisplaywidth 40000;
 select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) 
 where exists (select c1 from idx1);
 values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
@@ -214,7 +214,7 @@
 insert into docs values '24', '25', '36', '27', '124', '567';
 
 call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-maximumdisplaywidth 10000;
+maximumdisplaywidth 40000;
 
 -- NOT IN is flattened
 SELECT COUNT(*) FROM
@@ -361,15 +361,13 @@
 -- should be flattened
 values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
 
+select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4));
 -- watch out result, should return 2,3,4,2
-select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1
-not in (select t3.c1 from t3, t4));
 -- can not be flattened, should be materialized
 values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
 
+select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4));
 -- should return 1,5,1
-select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not
-in (select t3.c1 from t3, t4));
 -- can not be flattened, should be materialized
 values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
 

Mime
View raw message