db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r809632 [5/7] - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/util/ engine/org/apache/derby/impl/sql/execute/rts/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/ t...
Date Mon, 31 Aug 2009 15:47:24 GMT
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out?rev=809632&r1=809631&r2=809632&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out Mon Aug 31 15:47:23 2009
@@ -152,17 +152,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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 = 2
@@ -174,7 +176,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -183,14 +185,14 @@
 			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: 
+			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
+				None
 ij> -- simple EXISTS
 select * from outer1 o where exists (select * from idx1 i where o.c1 = i.c1);
 C1         |C2         |C3         
@@ -249,17 +251,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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 = 2
@@ -271,7 +275,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -280,14 +284,14 @@
 			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: 
+			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
+				None
 ij> -- simple ANY
 select * from outer1 o where o.c1 = ANY (select c1 from idx1);
 C1         |C2         |C3         
@@ -346,17 +350,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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 = 2
@@ -368,7 +374,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -377,14 +383,14 @@
 			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: 
+			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
+				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         
@@ -443,17 +449,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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 = 2
@@ -465,7 +473,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -474,18 +482,18 @@
 			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: 
+			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
+				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';
@@ -546,7 +554,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -555,14 +563,14 @@
 			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: 
+			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
+				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
@@ -574,17 +582,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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
@@ -644,7 +654,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -653,14 +663,14 @@
 			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: 
+			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
+				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
@@ -672,17 +682,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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         
@@ -741,7 +753,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0, 1}
 			Number of columns fetched=2
 			Number of deleted rows visited=0
@@ -750,16 +762,16 @@
 			Number of rows visited=2
 			Scan type=btree
 			Tree height=1
-			start position: 
-	None
-			stop position: 
-	None
+			start position:
+				None
+			stop position:
+				None
 			qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+				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
@@ -771,21 +783,23 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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         
@@ -855,7 +869,7 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			scan information:
 				Bit set of columns fetched={0, 1}
 				Number of columns fetched=2
 				Number of deleted rows visited=0
@@ -864,16 +878,16 @@
 				Number of rows visited=2
 				Scan type=btree
 				Tree height=1
-				start position: 
-	None
-				stop position: 
-	None
+				start position:
+					None
+				stop position:
+					None
 				qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+					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
@@ -885,21 +899,23 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			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
+				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
@@ -911,7 +927,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -920,14 +936,14 @@
 			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: 
+			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
+				None
 ij> -- comparisons with non-join expressions
 select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0);
 C1         |C2         |C3         
@@ -987,7 +1003,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -996,14 +1012,14 @@
 			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: 
+			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
+				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
@@ -1015,17 +1031,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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         
 -----------------------------------
@@ -1093,7 +1111,7 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			scan information:
 				Bit set of columns fetched={0, 1}
 				Number of columns fetched=2
 				Number of deleted rows visited=0
@@ -1102,16 +1120,16 @@
 				Number of rows visited=2
 				Scan type=btree
 				Tree height=1
-				start position: 
-	None
-				stop position: 
-	None
+				start position:
+					None
+				stop position:
+					None
 				qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+					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
@@ -1136,17 +1154,19 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				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
+					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
@@ -1158,7 +1178,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -1167,14 +1187,14 @@
 			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: 
+			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
+				None
 ij> -- multilevel subqueries
 -- only flatten bottom of where exists, any, or in with 
 -- exists, any, or in in its own where clause. DERBY-3301.
@@ -1253,7 +1273,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0, 1}
 					Number of columns fetched=2
 					Number of deleted rows visited=0
@@ -1262,14 +1282,14 @@
 					Number of rows visited=1
 					Scan type=btree
 					Tree height=1
-					start position: 
-	>= on first 2 column(s).
-	Ordered null semantics on the following columns: 
-					stop position: 
-	> on first 2 column(s).
-	Ordered null semantics on the following columns: 
+					start position:
+						>= on first 2 column(s).
+						Ordered null semantics on the following columns: 
+					stop position:
+						> on first 2 column(s).
+						Ordered null semantics on the following columns: 
 					qualifiers:
-None
+						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
@@ -1281,7 +1301,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -1290,14 +1310,14 @@
 					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: 
+					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
+						None
 	End Subquery Number 0
 Project-Restrict ResultSet (2):
 Number of opens = 1
@@ -1322,17 +1342,19 @@
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
 		next time in milliseconds/row = 0
-	scan information: 
+	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
+		start position:
+			null
+		stop position:
+			null
+		qualifiers:
+			None
 ij> -- only flatten bottom
 select * from outer1 o where exists
     (select * from idx2 i where exists
@@ -1417,18 +1439,18 @@
 					Number of rows visited=2
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					scan qualifiers:
-None
+						None
 					next qualifiers:
-Column[0][0] Id: 0
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+						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
@@ -1440,7 +1462,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -1449,14 +1471,14 @@
 					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: 
+					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
+						None
 	End Subquery Number 0
 Project-Restrict ResultSet (2):
 Number of opens = 1
@@ -1481,17 +1503,19 @@
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
 		next time in milliseconds/row = 0
-	scan information: 
+	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
+		start position:
+			null
+		stop position:
+			null
+		qualifiers:
+			None
 ij> -- flatten innermost into exists join, but dont flatten middle into outer as it
 -- is a where exists, any, or in with exists, any, or in in its own where clause. 
 -- DERBY-3301.
@@ -1570,7 +1594,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0, 1}
 					Number of columns fetched=2
 					Number of deleted rows visited=0
@@ -1579,14 +1603,14 @@
 					Number of rows visited=1
 					Scan type=btree
 					Tree height=1
-					start position: 
-	>= on first 2 column(s).
-	Ordered null semantics on the following columns: 
-					stop position: 
-	> on first 2 column(s).
-	Ordered null semantics on the following columns: 
+					start position:
+						>= on first 2 column(s).
+						Ordered null semantics on the following columns: 
+					stop position:
+						> on first 2 column(s).
+						Ordered null semantics on the following columns: 
 					qualifiers:
-None
+						None
 			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
@@ -1598,7 +1622,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={}
 					Number of columns fetched=0
 					Number of deleted rows visited=0
@@ -1607,12 +1631,12 @@
 					Number of rows visited=1
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 	End Subquery Number 0
 Project-Restrict ResultSet (2):
 Number of opens = 1
@@ -1637,17 +1661,19 @@
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
 		next time in milliseconds/row = 0
-	scan information: 
+	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
+		start position:
+			null
+		stop position:
+			null
+		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
@@ -1698,7 +1724,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -1707,14 +1733,14 @@
 			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: 
+			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
+				None
 	End Subquery Number 1
 Project-Restrict ResultSet (4):
 Number of opens = 1
@@ -1750,7 +1776,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0, 1}
 			Number of columns fetched=2
 			Number of deleted rows visited=0
@@ -1759,16 +1785,16 @@
 			Number of rows visited=2
 			Scan type=btree
 			Tree height=1
-			start position: 
-	None
-			stop position: 
-	None
+			start position:
+				None
+			stop position:
+				None
 			qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+				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
@@ -1780,21 +1806,23 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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);
@@ -1855,17 +1883,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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 = 2
@@ -1877,7 +1907,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -1886,18 +1916,18 @@
 			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: 
+			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
+				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         
 -----------------------------------
@@ -1954,17 +1984,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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 = 2
@@ -1976,7 +2008,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -1985,18 +2017,18 @@
 			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: 
+			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
+				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         
@@ -2066,7 +2098,7 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			scan information:
 				Bit set of columns fetched={0, 1}
 				Number of columns fetched=2
 				Number of deleted rows visited=0
@@ -2075,16 +2107,16 @@
 				Number of rows visited=2
 				Scan type=btree
 				Tree height=1
-				start position: 
-	None
-				stop position: 
-	None
+				start position:
+					None
+				stop position:
+					None
 				qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+					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
@@ -2096,26 +2128,28 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			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
+				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
@@ -2127,7 +2161,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -2136,14 +2170,14 @@
 			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: 
+			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
+				None
 ij> -- flattening to an exists join
 -- no index on subquery table
 select * from outer1 where c1 in (select c1 from noidx);
@@ -2204,17 +2238,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			start position:
+				null
+			stop position:
+				null
+			qualifiers:
+				None
 	Right result set:
 		Hash Scan ResultSet for NOIDX at serializable isolation level using share table locking: 
 		Number of opens = 2
@@ -2234,16 +2270,18 @@
 			Number of rows qualified=2
 			Number of rows visited=2
 			Scan type=heap
-			start position: 
-null			stop position: 
-null			scan qualifiers:
-None
+			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
+				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         
@@ -2302,17 +2340,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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
@@ -2334,18 +2374,18 @@
 			Number of rows visited=2
 			Scan type=btree
 			Tree height=1
-			start position: 
-	None
-			stop position: 
-	None
+			start position:
+				None
+			stop position:
+				None
 			scan qualifiers:
-None
+				None
 			next qualifiers:
-Column[0][0] Id: 0
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+				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         
@@ -2404,17 +2444,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			start position:
+				null
+			stop position:
+				null
+			qualifiers:
+				None
 	Right result set:
 		Hash Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using share table locking: 
 		Number of opens = 2
@@ -2436,18 +2478,18 @@
 			Number of rows visited=2
 			Scan type=btree
 			Tree height=1
-			start position: 
-	None
-			stop position: 
-	None
+			start position:
+				None
+			stop position:
+				None
 			scan qualifiers:
-None
+				None
 			next qualifiers:
-Column[0][0] Id: 0
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+				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         
@@ -2507,17 +2549,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			start position:
+				null
+			stop position:
+				null
+			qualifiers:
+				None
 	Right result set:
 		Project-Restrict ResultSet (4):
 		Number of opens = 2
@@ -2542,7 +2586,7 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			scan information:
 				Bit set of columns fetched={0}
 				Number of columns fetched=1
 				Number of deleted rows visited=0
@@ -2551,12 +2595,12 @@
 				Number of rows visited=2
 				Scan type=btree
 				Tree height=1
-				start position: 
-	None
-				stop position: 
-	None
+				start position:
+					None
+				stop position:
+					None
 				qualifiers:
-None
+					None
 ij> -- flattening values subqueries
 -- flatten unless contains a subquery
 select * from outer1 where c1 in (values 1);
@@ -2593,21 +2637,23 @@
 	next time (milliseconds) = 0
 	close time (milliseconds) = 0
 	next time in milliseconds/row = 0
-scan information: 
+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
+	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> select * from outer1 where c1 in (values (select max(c1) from outer1));
 C1         |C2         |C3         
 -----------------------------------
@@ -2685,17 +2731,19 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					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
+						start position:
+							null
+						stop position:
+							null
+						qualifiers:
+							None
 	End Subquery Number 1
 Project-Restrict ResultSet (14):
 Number of opens = 1
@@ -2731,21 +2779,23 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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
@@ -2828,17 +2878,19 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			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
+				start position:
+					null
+				stop position:
+					null
+				qualifiers:
+					None
 		Right result set:
 			Hash Scan ResultSet for OUTER2 at serializable isolation level using share table locking: 
 			Number of opens = 2
@@ -2858,16 +2910,18 @@
 				Number of rows qualified=2
 				Number of rows visited=2
 				Scan type=heap
-				start position: 
-null				stop position: 
-null				scan qualifiers:
-None
+				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
+					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 = 2
@@ -2879,7 +2933,7 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			Number of deleted rows visited=0
@@ -2888,12 +2942,12 @@
 			Number of rows visited=2
 			Scan type=btree
 			Tree height=1
-			start position: 
-	None
-			stop position: 
-	None
+			start position:
+				None
+			stop position:
+				None
 			qualifiers:
-None
+				None
 ij> -- in predicate (will be flattened to exists)
 select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) 
 where o.c1 in (select c1 from idx1);
@@ -2965,7 +3019,7 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			scan information:
 				Bit set of columns fetched={0}
 				Number of columns fetched=1
 				Number of deleted rows visited=0
@@ -2974,12 +3028,12 @@
 				Number of rows visited=2
 				Scan type=btree
 				Tree height=1
-				start position: 
-	None
-				stop position: 
-	None
+				start position:
+					None
+				stop position:
+					None
 				qualifiers:
-None
+					None
 		Right result set:
 			Hash Scan ResultSet for OUTER1 at serializable isolation level using share table locking: 
 			Number of opens = 2
@@ -2999,16 +3053,18 @@
 				Number of rows qualified=2
 				Number of rows visited=2
 				Scan type=heap
-				start position: 
-null				stop position: 
-null				scan qualifiers:
-None
+				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
+					Column[0][0] Id: 0
+					Operator: =
+					Ordered nulls: false
+					Unknown return value: false
+					Negate comparison result: false
 	Right result set:
 		Table Scan ResultSet for OUTER2 at serializable isolation level using share table locking chosen by the optimizer
 		Number of opens = 1
@@ -3020,21 +3076,23 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched={0}
 			Number of columns fetched=1
 			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
+			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> -- flattened exists join in nested subquery
 select c1 from (select t.c1 from (select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) where exists (select c1 from idx1)) t, outer2 where t.c1 = outer2.c1) t2;
 C1         
@@ -3129,17 +3187,19 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					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
+						start position:
+							null
+						stop position:
+							null
+						qualifiers:
+							None
 				Right result set:
 					Hash Scan ResultSet for OUTER2 at serializable isolation level using share table locking: 
 					Number of opens = 2
@@ -3159,16 +3219,18 @@
 						Number of rows qualified=2
 						Number of rows visited=2
 						Scan type=heap
-						start position: 
-null						stop position: 
-null						scan qualifiers:
-None
+						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
+							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 = 2
@@ -3180,7 +3242,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -3189,12 +3251,12 @@
 					Number of rows visited=2
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						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
@@ -3206,21 +3268,23 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		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
+			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
@@ -3324,26 +3388,28 @@
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched=All
 					Number of columns fetched=3
 					Number of pages visited=1
 					Number of rows qualified=0
 					Number of rows visited=0
 					Scan type=heap
-					start position: 
-null					stop position: 
-null					qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
-Column[0][1] Id: 2
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+					start position:
+						null
+					stop position:
+						null
+					qualifiers:
+						Column[0][0] Id: 1
+						Operator: =
+						Ordered nulls: false
+						Unknown return value: false
+						Negate comparison result: false
+						Column[0][1] Id: 2
+						Operator: =
+						Ordered nulls: false
+						Unknown return value: false
+						Negate comparison result: false
 			Right result set:
 				Table Scan ResultSet for BUSINESS at serializable isolation level using share table locking chosen by the optimizer
 				Number of opens = 0
@@ -3354,15 +3420,17 @@
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
-				scan information: 
-					start position: 
-null					stop position: 
-null					qualifiers:
-Column[0][0] Id: 0
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+				scan information:
+					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:
 			Table Scan ResultSet for CATEGORYBAG at serializable isolation level using share table locking chosen by the optimizer
 			Number of opens = 0
@@ -3373,30 +3441,32 @@
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-			scan information: 
-				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: 3
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
-Column[0][2] Id: 2
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
-Column[0][3] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+			scan information:
+				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: 3
+					Operator: =
+					Ordered nulls: false
+					Unknown return value: false
+					Negate comparison result: false
+					Column[0][2] Id: 2
+					Operator: =
+					Ordered nulls: false
+					Unknown return value: false
+					Negate comparison result: false
+					Column[0][3] Id: 1
+					Operator: =
+					Ordered nulls: false
+					Unknown return value: false
+					Negate comparison result: false
 ij> -- clean up
 drop table outer1;
 0 rows inserted/updated/deleted
@@ -3553,7 +3623,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0}
 						Number of columns fetched=1
 						Number of deleted rows visited=0
@@ -3562,12 +3632,12 @@
 						Number of rows visited=6
 						Scan type=btree
 						Tree height=1
-						start position: 
-	None
-						stop position: 
-	None
+						start position:
+							None
+						stop position:
+							None
 						qualifiers:
-None
+							None
 				Right result set:
 					Project-Restrict ResultSet (5):
 					Number of opens = 6
@@ -3592,7 +3662,7 @@
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 							next time in milliseconds/row = 0
-						scan information: 
+						scan information:
 							Bit set of columns fetched={0, 1}
 							Number of columns fetched=2
 							Number of deleted rows visited=0
@@ -3601,16 +3671,16 @@
 							Number of rows visited=6
 							Scan type=btree
 							Tree height=1
-							start position: 
-	>= on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
-							stop position: 
-	> on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
+							start position:
+								>= on first 1 column(s).
+								Ordered null semantics on the following columns: 
+								0 
+							stop position:
+								> on first 1 column(s).
+								Ordered null semantics on the following columns: 
+								0 
 							qualifiers:
-None
+								None
 ij> -- NOT EXISTS is flattened
 SELECT COUNT(*) FROM
 ( SELECT ID FROM DOCS WHERE
@@ -3712,7 +3782,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0}
 						Number of columns fetched=1
 						Number of deleted rows visited=0
@@ -3721,12 +3791,12 @@
 						Number of rows visited=6
 						Scan type=btree
 						Tree height=1
-						start position: 
-	None
-						stop position: 
-	None
+						start position:
+							None
+						stop position:
+							None
 						qualifiers:
-None
+							None
 				Right result set:
 					Project-Restrict ResultSet (5):
 					Number of opens = 6
@@ -3751,7 +3821,7 @@
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 							next time in milliseconds/row = 0
-						scan information: 
+						scan information:
 							Bit set of columns fetched={0, 1}
 							Number of columns fetched=2
 							Number of deleted rows visited=0
@@ -3760,16 +3830,16 @@
 							Number of rows visited=6
 							Scan type=btree
 							Tree height=1
-							start position: 
-	>= on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
-							stop position: 
-	> on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
+							start position:
+								>= on first 1 column(s).
+								Ordered null semantics on the following columns: 
+								0 
+							stop position:
+								> on first 1 column(s).
+								Ordered null semantics on the following columns: 
+								0 
 							qualifiers:
-None
+								None
 ij> -- EXISTS is flattened
 SELECT COUNT(*) FROM
 ( SELECT ID FROM DOCS WHERE
@@ -3871,7 +3941,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0}
 						Number of columns fetched=1
 						Number of deleted rows visited=0
@@ -3880,12 +3950,12 @@
 						Number of rows visited=6
 						Scan type=btree
 						Tree height=1
-						start position: 
-	None
-						stop position: 
-	None
+						start position:
+							None
+						stop position:
+							None
 						qualifiers:
-None
+							None
 				Right result set:
 					Project-Restrict ResultSet (5):
 					Number of opens = 6
@@ -3910,7 +3980,7 @@
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 							next time in milliseconds/row = 0
-						scan information: 
+						scan information:
 							Bit set of columns fetched={0, 1}
 							Number of columns fetched=2
 							Number of deleted rows visited=0
@@ -3919,16 +3989,16 @@
 							Number of rows visited=6
 							Scan type=btree
 							Tree height=1
-							start position: 
-	>= on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
-							stop position: 
-	> on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
+							start position:
+								>= on first 1 column(s).
+								Ordered null semantics on the following columns: 
+								0 
+							stop position:
+								> on first 1 column(s).
+								Ordered null semantics on the following columns: 
+								0 
 							qualifiers:
-None
+								None
 ij> -- IN is flattened
 SELECT count(ID) FROM DOCS WHERE ID IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -4009,7 +4079,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -4018,12 +4088,12 @@
 					Number of rows visited=6
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (5):
 				Number of opens = 6
@@ -4048,7 +4118,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0, 1}
 						Number of columns fetched=2
 						Number of deleted rows visited=0
@@ -4057,16 +4127,16 @@
 						Number of rows visited=6
 						Scan type=btree
 						Tree height=1
-						start position: 
-	>= on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
-						stop position: 
-	> on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
+						start position:
+							>= on first 1 column(s).
+							Ordered null semantics on the following columns: 
+							0 
+						stop position:
+							> on first 1 column(s).
+							Ordered null semantics on the following columns: 
+							0 
 						qualifiers:
-None
+							None
 ij> -- ANY is flattened
 SELECT count(ID) FROM DOCS WHERE ID > ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -4147,7 +4217,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -4156,12 +4226,12 @@
 					Number of rows visited=6
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (5):
 				Number of opens = 6
@@ -4186,7 +4256,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0, 1}
 						Number of columns fetched=2
 						Number of deleted rows visited=0
@@ -4195,14 +4265,14 @@
 						Number of rows visited=17
 						Scan type=btree
 						Tree height=1
-						start position: 
-	None
-						stop position: 
-	>= on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
+						start position:
+							None
+						stop position:
+							>= on first 1 column(s).
+							Ordered null semantics on the following columns: 
+							0 
 						qualifiers:
-None
+							None
 ij> -- ANY is flattened
 SELECT count(ID) FROM DOCS WHERE ID <> ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -4283,7 +4353,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -4292,12 +4362,12 @@
 					Number of rows visited=6
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (6):
 				Number of opens = 6
@@ -4330,7 +4400,7 @@
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 							next time in milliseconds/row = 0
-						scan information: 
+						scan information:
 							Bit set of columns fetched=All
 							Number of columns fetched=2
 							Number of deleted rows visited=0
@@ -4339,14 +4409,14 @@
 							Number of rows visited=6
 							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: 
+							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
+								None
 ij> -- ALL is flattened, what's not?
 SELECT count(ID) FROM DOCS WHERE ID = ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -4427,7 +4497,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -4436,12 +4506,12 @@
 					Number of rows visited=6
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (6):
 				Number of opens = 6
@@ -4474,7 +4544,7 @@
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 							next time in milliseconds/row = 0
-						scan information: 
+						scan information:
 							Bit set of columns fetched=All
 							Number of columns fetched=2
 							Number of deleted rows visited=0
@@ -4483,14 +4553,14 @@
 							Number of rows visited=6
 							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: 
+							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
+								None
 ij> -- ALL is flattened, what's not?
 SELECT count(ID) FROM DOCS WHERE ID < ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -4571,7 +4641,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -4580,12 +4650,12 @@
 					Number of rows visited=6
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (5):
 				Number of opens = 6
@@ -4610,7 +4680,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0, 1}
 						Number of columns fetched=2
 						Number of deleted rows visited=0
@@ -4619,14 +4689,14 @@
 						Number of rows visited=18
 						Scan type=btree
 						Tree height=1
-						start position: 
-	None
-						stop position: 
-	> on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
+						start position:
+							None
+						stop position:
+							> on first 1 column(s).
+							Ordered null semantics on the following columns: 
+							0 
 						qualifiers:
-None
+							None
 ij> -- ALL is flattened, what's not?
 SELECT count(ID) FROM DOCS WHERE ID <> ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -4707,7 +4777,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -4716,12 +4786,12 @@
 					Number of rows visited=6
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (5):
 				Number of opens = 6
@@ -4746,7 +4816,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0, 1}
 						Number of columns fetched=2
 						Number of deleted rows visited=0
@@ -4755,16 +4825,16 @@
 						Number of rows visited=6
 						Scan type=btree
 						Tree height=1
-						start position: 
-	>= on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
-						stop position: 
-	> on first 1 column(s).
-	Ordered null semantics on the following columns: 
-0 
+						start position:
+							>= on first 1 column(s).
+							Ordered null semantics on the following columns: 
+							0 
+						stop position:
+							> on first 1 column(s).
+							Ordered null semantics on the following columns: 
+							0 
 						qualifiers:
-None
+							None
 ij> -- Now test nullable correlated columns
 drop table colls;
 0 rows inserted/updated/deleted
@@ -4894,7 +4964,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0}
 						Number of columns fetched=1
 						Number of deleted rows visited=0
@@ -4903,12 +4973,12 @@
 						Number of rows visited=6
 						Scan type=btree
 						Tree height=1
-						start position: 
-	None
-						stop position: 
-	None
+						start position:
+							None
+						stop position:
+							None
 						qualifiers:
-None
+							None
 				Right result set:
 					Project-Restrict ResultSet (5):
 					Number of opens = 6
@@ -4941,16 +5011,18 @@
 							Number of rows qualified=9
 							Number of rows visited=9
 							Scan type=heap
-							start position: 
-null							stop position: 
-null							scan qualifiers:
-None
+							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
+								Column[0][0] Id: 0
+								Operator: =
+								Ordered nulls: false
+								Unknown return value: false
+								Negate comparison result: false
 ij> -- EXISTS should be flattened
 SELECT COUNT(*) FROM
 ( SELECT ID FROM DOCS WHERE
@@ -5052,7 +5124,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0}
 						Number of columns fetched=1
 						Number of deleted rows visited=0
@@ -5061,12 +5133,12 @@
 						Number of rows visited=6
 						Scan type=btree
 						Tree height=1
-						start position: 
-	None
-						stop position: 
-	None
+						start position:
+							None
+						stop position:
+							None
 						qualifiers:
-None
+							None
 				Right result set:
 					Project-Restrict ResultSet (5):
 					Number of opens = 6
@@ -5099,16 +5171,18 @@
 							Number of rows qualified=9
 							Number of rows visited=9
 							Scan type=heap
-							start position: 
-null							stop position: 
-null							scan qualifiers:
-None
+							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
+								Column[0][0] Id: 0
+								Operator: =
+								Ordered nulls: false
+								Unknown return value: false
+								Negate comparison result: false
 ij> -- IN should be flattened
 SELECT count(ID) FROM DOCS WHERE ID IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -5189,7 +5263,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -5198,12 +5272,12 @@
 					Number of rows visited=6
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (5):
 				Number of opens = 6
@@ -5236,16 +5310,18 @@
 						Number of rows qualified=9
 						Number of rows visited=9
 						Scan type=heap
-						start position: 
-null						stop position: 
-null						scan qualifiers:
-None
+						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
+							Column[0][0] Id: 0
+							Operator: =
+							Ordered nulls: false
+							Unknown return value: false
+							Negate comparison result: false
 ij> -- ANY should be flattened
 SELECT count(ID) FROM DOCS WHERE ID > ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -5326,7 +5402,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -5335,12 +5411,12 @@
 					Number of rows visited=6
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (6):
 				Number of opens = 6
@@ -5373,7 +5449,7 @@
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 							next time in milliseconds/row = 0
-						scan information: 
+						scan information:
 							Bit set of columns fetched=All
 							Number of columns fetched=2
 							Number of deleted rows visited=0
@@ -5382,14 +5458,14 @@
 							Number of rows visited=21
 							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: 
+							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
+								None
 ij> -- ALL should NOT be flattened, but subquery should be materialized
 SELECT count(ID) FROM DOCS WHERE ID <> ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -5562,7 +5638,7 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			scan information:
 				Bit set of columns fetched={0}
 				Number of columns fetched=1
 				Number of deleted rows visited=0
@@ -5571,12 +5647,12 @@
 				Number of rows visited=6
 				Scan type=btree
 				Tree height=1
-				start position: 
-	None
-				stop position: 
-	None
+				start position:
+					None
+				stop position:
+					None
 				qualifiers:
-None
+					None
 ij> -- Now we make the other correlated column also nullable
 drop table docs;
 0 rows inserted/updated/deleted
@@ -5689,7 +5765,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0}
 						Number of columns fetched=1
 						Number of deleted rows visited=0
@@ -5698,12 +5774,12 @@
 						Number of rows visited=7
 						Scan type=btree
 						Tree height=1
-						start position: 
-	None
-						stop position: 
-	None
+						start position:
+							None
+						stop position:
+							None
 						qualifiers:
-None
+							None
 				Right result set:
 					Project-Restrict ResultSet (5):
 					Number of opens = 7
@@ -5736,16 +5812,18 @@
 							Number of rows qualified=9
 							Number of rows visited=9
 							Scan type=heap
-							start position: 
-null							stop position: 
-null							scan qualifiers:
-None
+							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
+								Column[0][0] Id: 0
+								Operator: =
+								Ordered nulls: false
+								Unknown return value: false
+								Negate comparison result: false
 ij> -- EXISTS should be flattened
 SELECT COUNT(*) FROM
 ( SELECT ID FROM DOCS WHERE
@@ -5847,7 +5925,7 @@
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						next time in milliseconds/row = 0
-					scan information: 
+					scan information:
 						Bit set of columns fetched={0}
 						Number of columns fetched=1
 						Number of deleted rows visited=0
@@ -5856,12 +5934,12 @@
 						Number of rows visited=7
 						Scan type=btree
 						Tree height=1
-						start position: 
-	None
-						stop position: 
-	None
+						start position:
+							None
+						stop position:
+							None
 						qualifiers:
-None
+							None
 				Right result set:
 					Project-Restrict ResultSet (5):
 					Number of opens = 7
@@ -5894,16 +5972,18 @@
 							Number of rows qualified=9
 							Number of rows visited=9
 							Scan type=heap
-							start position: 
-null							stop position: 
-null							scan qualifiers:
-None
+							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
+								Column[0][0] Id: 0
+								Operator: =
+								Ordered nulls: false
+								Unknown return value: false
+								Negate comparison result: false
 ij> -- IN should be flattened
 SELECT count(ID) FROM DOCS WHERE ID IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -5984,7 +6064,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -5993,12 +6073,12 @@
 					Number of rows visited=7
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (5):
 				Number of opens = 7
@@ -6031,16 +6111,18 @@
 						Number of rows qualified=9
 						Number of rows visited=9
 						Scan type=heap
-						start position: 
-null						stop position: 
-null						scan qualifiers:
-None
+						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
+							Column[0][0] Id: 0
+							Operator: =
+							Ordered nulls: false
+							Unknown return value: false
+							Negate comparison result: false
 ij> -- ANY should be flattened
 SELECT count(ID) FROM DOCS WHERE ID > ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -6121,7 +6203,7 @@
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 					next time in milliseconds/row = 0
-				scan information: 
+				scan information:
 					Bit set of columns fetched={0}
 					Number of columns fetched=1
 					Number of deleted rows visited=0
@@ -6130,12 +6212,12 @@
 					Number of rows visited=7
 					Scan type=btree
 					Tree height=1
-					start position: 
-	None
-					stop position: 
-	None
+					start position:
+						None
+					stop position:
+						None
 					qualifiers:
-None
+						None
 			Right result set:
 				Project-Restrict ResultSet (6):
 				Number of opens = 7
@@ -6168,7 +6250,7 @@
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 							next time in milliseconds/row = 0
-						scan information: 
+						scan information:
 							Bit set of columns fetched=All
 							Number of columns fetched=2
 							Number of deleted rows visited=0
@@ -6177,14 +6259,14 @@
 							Number of rows visited=28
 							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: 
+							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
+								None
 ij> -- ALL should NOT be flattened, but subquery should be materialized, watch out results
 SELECT count(ID) FROM DOCS WHERE ID <> ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
 1          
@@ -6357,7 +6439,7 @@
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 				next time in milliseconds/row = 0
-			scan information: 
+			scan information:
 				Bit set of columns fetched={0}
 				Number of columns fetched=1
 				Number of deleted rows visited=0
@@ -6366,12 +6448,12 @@
 				Number of rows visited=7
 				Scan type=btree
 				Tree height=1
-				start position: 
-	None
-				stop position: 
-	None
+				start position:
+					None
+				stop position:
+					None
 				qualifiers:
-None
+					None
 ij> drop table t1;
 ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist.
 ij> drop table t2;
@@ -6457,17 +6539,19 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 			next time in milliseconds/row = 0
-		scan information: 
+		scan information:
 			Bit set of columns fetched=All
 			Number of columns fetched=1
 			Number of pages visited=1
 			Number of rows qualified=7
 			Number of rows visited=7
 			Scan type=heap
-			start position: 
-null			stop position: 
-null			qualifiers:
-None
+			start position:
+				null
+			stop position:
+				null
+			qualifiers:
+				None
 	Right result set:
 		Hash Scan ResultSet for T2 at serializable isolation level using share table locking: 
 		Number of opens = 7
@@ -6487,16 +6571,18 @@
 			Number of rows qualified=7
 			Number of rows visited=7
 			Scan type=heap
-			start position: 
-null			stop position: 
-null			scan qualifiers:
-None
+			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
+				Column[0][0] Id: 0
+				Operator: =
+				Ordered nulls: false
+				Unknown return value: false
+				Negate comparison result: false
 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         
 -----------



Mime
View raw message