You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2009/08/31 17:47:24 UTC
svn commit: r809632 [3/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...
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out?rev=809632&r1=809631&r2=809632&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out Mon Aug 31 15:47:23 2009
@@ -103,17 +103,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=4
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> select distinct c1, c2, c3, c4 from t1 order by c1, c2, c3, c4;
C1 |C2 |C3 |C4
-----------------------------------------------
@@ -169,17 +171,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=4
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> -- in order prefix
select distinct c3, c4 from t1 order by 1, 2;
C3 |C4
@@ -236,17 +240,19 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={2, 3}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> select distinct c3, c4 from t1 order by c3, c4;
C3 |C4
-----------------------
@@ -301,17 +307,19 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={2, 3}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> -- no prefix
select distinct c3, c4 from t1 order by 2;
C3 |C4
@@ -394,17 +402,19 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={2, 3}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> select distinct c3, c4 from t1 order by c4;
C3 |C4
-----------------------
@@ -485,17 +495,19 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={2, 3}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> -- expression
select distinct c3, 1 from t1 order by 1;
C3 |2
@@ -561,17 +573,19 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={2}
Number of columns fetched=1
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> select distinct c3, 1 from t1 order by 2;
C3 |2
-----------------------
@@ -648,17 +662,19 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={2}
Number of columns fetched=1
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> -- verify that a sort is still done when a unique index
-- exists
create unique index i1 on t1(c1, c2, c3, c4);
@@ -723,7 +739,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, 2, 3}
Number of columns fetched=4
Number of deleted rows visited=0
@@ -732,14 +748,14 @@
Number of rows visited=3
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
ij> select distinct c3, c4 from t1 where c1 = 1 and c2 = 2 order by c4;
C3 |C4
-----------------------
@@ -800,7 +816,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, 2, 3}
Number of columns fetched=4
Number of deleted rows visited=0
@@ -809,14 +825,14 @@
Number of rows visited=3
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
ij> -- order by and union
select c1 from t1 union select c2 from t1 order by 1;
1
@@ -881,7 +897,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
@@ -890,12 +906,12 @@
Number of rows visited=10
Scan type=btree
Tree height=1
- start position:
- None
- stop position:
- None
+ start position:
+ None
+ stop position:
+ None
qualifiers:
-None
+ None
Right result set:
Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
@@ -907,7 +923,7 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={1}
Number of columns fetched=1
Number of deleted rows visited=0
@@ -916,12 +932,12 @@
Number of rows visited=10
Scan type=btree
Tree height=1
- start position:
- None
- stop position:
- None
+ start position:
+ None
+ stop position:
+ None
qualifiers:
-None
+ None
ij> select c1 from t1 union select c2 as c1 from t1 order by c1;
C1
-----------
@@ -984,7 +1000,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
@@ -993,12 +1009,12 @@
Number of rows visited=10
Scan type=btree
Tree height=1
- start position:
- None
- stop position:
- None
+ start position:
+ None
+ stop position:
+ None
qualifiers:
-None
+ None
Right result set:
Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
@@ -1010,7 +1026,7 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={1}
Number of columns fetched=1
Number of deleted rows visited=0
@@ -1019,12 +1035,12 @@
Number of rows visited=10
Scan type=btree
Tree height=1
- start position:
- None
- stop position:
- None
+ start position:
+ None
+ stop position:
+ None
qualifiers:
-None
+ None
ij> -- RESOLVE: next 2 will do 2 sorts (bug 58)
select c3, c4 from t1 union select c2, c1 from t1 order by 2;
1 |2
@@ -1111,7 +1127,7 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={2, 3}
Number of columns fetched=2
Number of deleted rows visited=0
@@ -1120,12 +1136,12 @@
Number of rows visited=10
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 = 1
@@ -1150,7 +1166,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
@@ -1159,12 +1175,12 @@
Number of rows visited=10
Scan type=btree
Tree height=1
- start position:
- None
- stop position:
- None
+ start position:
+ None
+ stop position:
+ None
qualifiers:
-None
+ None
ij> select c3, c4 from t1 union select c2, c1 as c4 from t1 order by c4;
1 |C4
-----------------------
@@ -1249,7 +1265,7 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
- scan information:
+ scan information:
Bit set of columns fetched={2, 3}
Number of columns fetched=2
Number of deleted rows visited=0
@@ -1258,12 +1274,12 @@
Number of rows visited=10
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 = 1
@@ -1288,7 +1304,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
@@ -1297,12 +1313,12 @@
Number of rows visited=10
Scan type=btree
Tree height=1
- start position:
- None
- stop position:
- None
+ start position:
+ None
+ stop position:
+ None
qualifiers:
-None
+ None
ij> -- DERBY-2887: investigate affect of NULLS FIRST/LAST on sorting
insert into t1 values (1, null, 14, null);
1 row inserted/updated/deleted
@@ -1346,7 +1362,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, 2}
Number of columns fetched=3
Number of deleted rows visited=0
@@ -1355,14 +1371,14 @@
Number of rows visited=8
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> -- Needs to do a sort to get the NULLS FIRST:
select c1,c2,c3 from t1 where c1 = 1 order by c1,c2 nulls first;
C1 |C2 |C3
@@ -1417,7 +1433,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, 2}
Number of columns fetched=3
Number of deleted rows visited=0
@@ -1426,14 +1442,14 @@
Number of rows visited=8
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> -- test recognition of single row tables
-- even when scanning heap
create table u1(c1 int, c2 int);
@@ -1541,7 +1557,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
@@ -1550,14 +1566,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:
Index Row to Base Row ResultSet for U2:
Number of opens = 1
@@ -1577,7 +1593,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
@@ -1586,14 +1602,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 U3 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
@@ -1605,17 +1621,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=2
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> -- clean up
drop table t1;
0 rows inserted/updated/deleted
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out?rev=809632&r1=809631&r2=809632&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out Mon Aug 31 15:47:23 2009
@@ -674,7 +674,7 @@
3 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
-ij> maximumdisplaywidth 4500;
+ij> maximumdisplaywidth 10000;
ij> -- no xform, predicate on outer table
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt1.c1 = 3;
C1 |C2 |C3 |C1 |C2 |C3
@@ -725,21 +725,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=8
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:
Table Scan ResultSet for TT2 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
@@ -751,21 +753,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=8
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
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ Column[0][0] Id: 1
+ Operator: =
+ Ordered nulls: false
+ Unknown return value: false
+ Negate comparison result: false
ij> select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= tt2.c2;
C1 |C2 |C3 |C1 |C2 |C3
-----------------------------------------------------------------------
@@ -836,17 +840,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=3
Number of rows visited=8
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Table Scan ResultSet for TT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 3
@@ -858,21 +864,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=2
Number of rows visited=24
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 tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3;
C1 |C2 |C3 |C1 |C2 |C3
-----------------------------------------------------------------------
@@ -942,17 +950,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=3
Number of rows visited=8
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Table Scan ResultSet for TT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
@@ -964,21 +974,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=8
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 tt2 right outer join tt1 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3;
C1 |C2 |C3 |C1 |C2 |C3
-----------------------------------------------------------------------
@@ -1035,17 +1047,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=3
Number of rows visited=8
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Table Scan ResultSet for TT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
@@ -1057,21 +1071,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=8
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 tt1 left outer join tt2 on tt1.c1 = tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where tt3.c3 = 3;
C1 |C2 |C3 |C1 |C2 |C3 |C1 |C2 |C3
-----------------------------------------------------------------------------------------------------------
@@ -1128,21 +1144,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=8
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-Column[0][0] 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: 2
+ Operator: =
+ Ordered nulls: false
+ Unknown return value: false
+ Negate comparison result: false
Right result set:
Project-Restrict ResultSet (6):
Number of opens = 1
@@ -1179,17 +1197,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=3
Number of rows visited=8
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 TT2 at read committed isolation level using instantaneous share row locking:
Number of opens = 3
@@ -1209,10 +1229,18 @@
Number of rows qualified=3
Number of rows visited=8
Scan type=heap
- start position:
-null stop position:
-null scan qualifiers:
-Non&
+ start position:
+ null
+ stop position:
+ null
+ scan qualifiers:
+ None
+ next qualifiers:
+ Column[0][0] Id: 1
+ Operator: =
+ Ordered nulls: false
+ Unknown return value: false
+ Negate comparison result: false
ij> select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where tt2.c2 = 3;
C1 |C2 |C3 |C1 |C2 |C3 |C1 |C2 |C3
-----------------------------------------------------------------------------------------------------------
@@ -1268,17 +1296,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=3
Number of rows visited=8
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 TT2 at read committed isolation level using instantaneous share row locking:
Number of opens = 3
@@ -1298,20 +1328,22 @@
Number of rows qualified=1
Number of rows visited=8
Scan type=heap
- start position:
-null stop position:
-null scan qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
+ start position:
+ null
+ stop position:
+ null
+ scan qualifiers:
+ Column[0][0] Id: 1
+ Operator: =
+ Ordered nulls: false
+ Unknown return value: false
+ Negate comparison result: false
next 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 TT3 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
@@ -1323,21 +1355,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=8
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-Column[0][0] 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: 2
+ Operator: =
+ Ordered nulls: false
+ Unknown return value: false
+ Negate comparison result: false
ij> -- make sure predicates are null tolerant
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2
where char(tt2.c2) is null;
@@ -1400,17 +1434,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=3
Number of rows visited=8
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 TT2 at read committed isolation level using instantaneous share row locking:
Number of opens = 3
@@ -1430,16 +1466,18 @@
Number of rows qualified=3
Number of rows visited=8
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: 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
ij> commit;
ij> -- bug 2924, cross join under an outer join
CREATE TABLE inventory(itemno INT NOT NULL PRIMARY KEY, capacity INT);
@@ -1772,17 +1810,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=5
Number of rows visited=5
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 (5):
Number of opens = 5
@@ -1815,16 +1855,18 @@
Number of rows qualified=5
Number of rows visited=5
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 T3 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 3
@@ -1836,21 +1878,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=1
Number of pages visited=1
Number of rows qualified=2
Number of rows visited=15
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> -- Test fix for bug 5659
create table xxx (a int not null);
0 rows inserted/updated/deleted
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out?rev=809632&r1=809631&r2=809632&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out Mon Aug 31 15:47:23 2009
@@ -129,21 +129,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=4
Number of rows visited=8
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 c1, c2 from sv1 where c1 = 1 + 1 order by c1, c2;
C1 |C2
-----------------------
@@ -219,21 +221,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=4
Number of rows visited=8
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 x1, x2 from sv2 where x1 = 1 order by x1, x2;
X1 |X2
-----------------------
@@ -309,21 +313,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=4
Number of rows visited=8
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 y1, y2 from sv3 where y1 = 1 order by y1, y2;
Y1 |Y2
-----------------------
@@ -412,21 +418,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=4
Number of rows visited=8
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 z1, z2, z4, z5 from sv4 where z1 = z4 and z2 = z5
order by z1, z2, z4, z5;
Z1 |Z2 |Z4 |Z5
@@ -527,17 +535,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=8
Number of rows visited=8
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 T1 at serializable isolation level using share table locking:
Number of opens = 8
@@ -557,21 +567,23 @@
Number of rows qualified=8
Number of rows visited=8
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][1] Id: 1
-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
+ Column[0][1] Id: 1
+ Operator: =
+ Ordered nulls: false
+ Unknown return value: false
+ Negate comparison result: false
ij> select * from av1 where c1 = 1;
C1 |C2
-----------------------
@@ -643,21 +655,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, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
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 av2 where x1 = 2;
X1 |X2
-----------------------
@@ -768,21 +782,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, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
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 av3;
Y1 |Y2 |Y3 |Y4
-----------------------------------------------
@@ -886,17 +902,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, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
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 T1 at serializable isolation level using share table locking:
Number of opens = 8
@@ -916,16 +934,18 @@
Number of rows qualified=8
Number of rows visited=8
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 cv1;
C1 |C2
-----------------------
@@ -1015,21 +1035,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, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
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> create index t1_c1 on t1(c1);
0 rows inserted/updated/deleted
ij> select c1, c2 from sv1 where c1 = 1 order by c1, c2;
@@ -1115,7 +1137,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
@@ -1124,14 +1146,14 @@
Number of rows visited=5
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> select x1, x2 from sv2 where x1 = 1 order by x1, x2;
X1 |X2
-----------------------
@@ -1215,7 +1237,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
@@ -1224,14 +1246,14 @@
Number of rows visited=5
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> select y1, y2 from sv3 where y1 = 1 order by y1, y2;
Y1 |Y2
-----------------------
@@ -1328,7 +1350,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
@@ -1337,14 +1359,14 @@
Number of rows visited=5
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> select z1, z2, z4, z5 from sv4 where z1 = z4 and z2 = z5
order by z1, z2, z4, z5;
Z1 |Z2 |Z4 |Z5
@@ -1445,17 +1467,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=8
Number of rows visited=8
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 T1 at serializable isolation level using share table locking:
Number of opens = 8
@@ -1475,21 +1499,23 @@
Number of rows qualified=8
Number of rows visited=8
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][1] Id: 1
-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
+ Column[0][1] Id: 1
+ Operator: =
+ Ordered nulls: false
+ Unknown return value: false
+ Negate comparison result: false
ij> select * from av1 where c1 = 1;
C1 |C2
-----------------------
@@ -1566,7 +1592,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
@@ -1575,14 +1601,14 @@
Number of rows visited=5
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> select * from av2 where x1 = 2;
X1 |X2
-----------------------
@@ -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=All
Number of columns fetched=2
Number of deleted rows visited=0
@@ -1707,14 +1733,14 @@
Number of rows visited=4
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> select y1, y2, y3, y4 + 0 from av3 where y1 = y2;
Y1 |Y2 |Y3 |4
-----------------------------------------------
@@ -1811,17 +1837,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, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
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 T1 at serializable isolation level using share table locking:
Number of opens = 8
@@ -1841,16 +1869,18 @@
Number of rows qualified=8
Number of rows visited=8
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 cv1 where c1 = 1;
C1 |C2
-----------------------
@@ -1943,7 +1973,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
@@ -1952,14 +1982,14 @@
Number of rows visited=5
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> -- cleanup
drop view cv1;
0 rows inserted/updated/deleted
@@ -2049,7 +2079,7 @@
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=2
Number of deleted rows visited=0
@@ -2058,14 +2088,14 @@
Number of rows visited=0
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> select a from test.table2 where b=25;
A
-----------
@@ -2117,7 +2147,7 @@
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=2
Number of deleted rows visited=0
@@ -2126,14 +2156,14 @@
Number of rows visited=0
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> -- This select from a view based on the tables would use TableScan before DERBY-649
select a from test.view0 where b=25;
A
@@ -2210,7 +2240,7 @@
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=2
Number of deleted rows visited=0
@@ -2219,14 +2249,14 @@
Number of rows visited=0
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:
Index Row to Base Row ResultSet for TABLE2:
Number of opens = 1
@@ -2245,7 +2275,7 @@
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=2
Number of deleted rows visited=0
@@ -2254,14 +2284,14 @@
Number of rows visited=0
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> -- Can't use index for the following
select a from test.view0 where b=25+a;
A
@@ -2330,17 +2360,19 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
+ scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
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:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Table Scan ResultSet for TABLE2 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
@@ -2351,17 +2383,19 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
+ scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
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:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> -- Inlist tests
select * from test.view0 where b in (1, 2, 3);
A |B
@@ -2425,7 +2459,7 @@
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=2
Number of deleted rows visited=0
@@ -2434,14 +2468,14 @@
Number of rows visited=0
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:
Index Row to Base Row ResultSet for TABLE2:
Number of opens = 1
@@ -2460,7 +2494,7 @@
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=2
Number of deleted rows visited=0
@@ -2469,14 +2503,14 @@
Number of rows visited=0
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> prepare s as 'select * from test.view0 where b in (?, ?, ?)';
ij> execute s using 'values (1,2,3)';
IJ WARNING: Autocommit may close using result set
@@ -2540,7 +2574,7 @@
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=2
Number of deleted rows visited=0
@@ -2549,14 +2583,14 @@
Number of rows visited=0
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:
Index Row to Base Row ResultSet for TABLE2:
Number of opens = 1
@@ -2575,7 +2609,7 @@
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=2
Number of deleted rows visited=0
@@ -2584,14 +2618,14 @@
Number of rows visited=0
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> -- This select should use index for first two selects, table scan for the third
select a from test.view1 where b=25;
A
@@ -2670,17 +2704,19 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
+ scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
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:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Table Scan ResultSet for TABLE2 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
@@ -2691,17 +2727,19 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
+ scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
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:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Project-Restrict ResultSet (6):
Number of opens = 1
@@ -2725,7 +2763,7 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
+ scan information:
Bit set of columns fetched={}
Number of columns fetched=0
Number of deleted rows visited=0
@@ -2734,12 +2772,12 @@
Number of rows visited=0
Scan type=btree
Tree height=1
- start position:
- None
- stop position:
- None
+ start position:
+ None
+ stop position:
+ None
qualifiers:
-None
+ None
ij> select d from test.view2 where d=25;
D
-----------
@@ -2819,17 +2857,19 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
+ scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
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:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Table Scan ResultSet for TABLE2 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
@@ -2840,17 +2880,19 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
+ scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
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:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
0 rows inserted/updated/deleted
ij> -- Beetle 4983, customer case, complex query involving views, wrong column remapping
@@ -6940,17 +6982,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, 1, 2, 4, 5, 6, 7, 8}
Number of columns fetched=8
Number of pages visited=2
Number of rows qualified=16
Number of rows visited=16
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 (13):
Number of opens = 16
@@ -6983,25 +7027,27 @@
Number of rows qualified=16
Number of rows visited=16
Scan type=heap
- start position:
-null stop position:
-null scan qualifiers:
-Column[0][0] Id: 14
-Operator: =
-Ordered nulls: true
-Unknown return value: false
-Negate comparison result: false
-Column[0][1] Id: 14
-Operator: =
-Ordered nulls: true
-Unknown return value: false
-Negate comparison result: false
+ start position:
+ null
+ stop position:
+ null
+ scan qualifiers:
+ Column[0][0] Id: 14
+ Operator: =
+ Ordered nulls: true
+ Unknown return value: false
+ Negate comparison result: false
+ Column[0][1] Id: 14
+ Operator: =
+ Ordered nulls: true
+ Unknown return value: false
+ Negate comparison result: false
next 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:
Hash Table ResultSet (18):
Number of opens = 0
@@ -7053,11 +7099,13 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
- start position:
-null stop position:
-null qualifiers:
-None
+ scan information:
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Hash Scan ResultSet for REPOSITORYOBJECTRESOURCE using constraint ROR_CURRENTVERSION at serializable isolation level using share table locking:
Number of opens = 0
@@ -7070,18 +7118,18 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
- 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:
Hash Scan ResultSet for BUSINESSENTITY at serializable isolation level using share table locking:
Number of opens = 0
@@ -7094,16 +7142,18 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
- 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:
Hash Scan ResultSet for BUSINESSENTITY at serializable isolation level using share table locking:
Number of opens = 0
@@ -7116,16 +7166,18 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
- 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:
Hash Scan ResultSet for REPOSITORYENTRY at serializable isolation level using share table locking:
Number of opens = 0
@@ -7138,16 +7190,18 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
- 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:
Hash Scan ResultSet for REPOSITORYENTRYCONTENT at serializable isolation level using share table locking:
Number of opens = 0
@@ -7160,16 +7214,18 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
- 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:
Hash Table ResultSet (27):
Number of opens = 0
@@ -7221,13 +7277,13 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
- start position:
- None
- stop position:
- None
+ scan information:
+ start position:
+ None
+ stop position:
+ None
qualifiers:
-None
+ None
Right result set:
Hash Scan ResultSet for CLASSIFICATION_VALUES at serializable isolation level using share table locking:
Number of opens = 0
@@ -7240,16 +7296,18 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
- 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:
Hash Scan ResultSet for OBJECTASSOCIATION at serializable isolation level using share table locking:
Number of opens = 0
@@ -7262,16 +7320,18 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
- 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:
Hash Scan ResultSet for CLASSIFICATIONSCHEME at serializable isolation level using share table locking:
Number of opens = 0
@@ -7284,16 +7344,18 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
- 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:
Project-Restrict ResultSet (31):
Number of opens = 0
@@ -7319,16 +7381,18 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
- 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> rollback;
ij> autocommit on;
ij> CREATE TABLE D1 (A INT, B VARCHAR(4) FOR BIT DATA);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/setOpPlan.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/setOpPlan.out?rev=809632&r1=809631&r2=809632&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/setOpPlan.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/setOpPlan.out Mon Aug 31 15:47:23 2009
@@ -90,17 +90,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=3
Number of rows visited=3
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Intersect ResultSet:
Number of opens = 1
@@ -136,17 +138,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=4
Number of rows visited=4
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Sort ResultSet:
Number of opens = 1
@@ -172,17 +176,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=5
Number of rows visited=5
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> SELECT i FROM t1 UNION SELECT j FROM t2 EXCEPT SELECT k FROM t3;
1
-----------
@@ -248,17 +254,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=3
Number of rows visited=3
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Table Scan ResultSet for T2 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
@@ -270,17 +278,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=4
Number of rows visited=4
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Sort ResultSet:
Number of opens = 1
@@ -306,17 +316,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=5
Number of rows visited=5
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> SELECT i FROM t1 INTERSECT SELECT j FROM t2 EXCEPT SELECT k FROM t3;
1
-----------
@@ -382,17 +394,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=3
Number of rows visited=3
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Sort ResultSet:
Number of opens = 1
@@ -418,17 +432,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=4
Number of rows visited=4
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
Right result set:
Sort ResultSet:
Number of opens = 1
@@ -454,17 +470,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=5
Number of rows visited=5
Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-None
+ start position:
+ null
+ stop position:
+ null
+ qualifiers:
+ None
ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
0 rows inserted/updated/deleted
ij>