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 dj...@apache.org on 2005/01/12 22:19:15 UTC
svn commit: r124992 - /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/distinctElimination.out /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinctElimination.sql
Author: djd
Date: Wed Jan 12 13:19:13 2005
New Revision: 124992
URL: http://svn.apache.org/viewcvs?view=rev&rev=124992
Log:
Fix distinctElimination test on linux/unix.
Modified:
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/distinctElimination.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinctElimination.sql
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/distinctElimination.out
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/distinctElimination.out?view=diff&rev=124992&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/distinctElimination.out&r1=124991&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/distinctElimination.out&r2=124992
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/distinctElimination.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/distinctElimination.out Wed Jan 12 13:19:13 2005
@@ -89,7 +89,7 @@
1 row inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
-ij> maximumdisplaywidth 2000;
+ij> maximumdisplaywidth 20000;
ij> -- queries that cannot eliminate the distinct
-- no unique index
select distinct c2 from one;
@@ -347,7 +347,52 @@
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
- next&
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of pages visited=1
+ Number of rows qualified=9
+ Number of rows visited=9
+ 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
+ Right result set:
+ Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 9
+ Rows seen = 9
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=9
+ Number of rows qualified=9
+ Number of rows visited=9
+ Scan type=btree
+ Tree height=1
+ start position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 1 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
ij> -- cross product join
select distinct a.c1 from one a, two b;
C1
@@ -428,7 +473,31 @@
qualifiers:
None
Right result set:
- Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isol&
+ Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking chosen by the optimizer
+ Number of opens = 8
+ Rows seen = 72
+ Rows filtered = 0
+ Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={}
+ Number of columns fetched=0
+ Number of deleted rows visited=0
+ Number of pages visited=8
+ Number of rows qualified=72
+ Number of rows visited=72
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ qualifiers:
+None
ij> -- no single table will yield at most 1 row
select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1;
C1 |C3 |C2
@@ -507,7 +576,53 @@
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
- next time (millis&
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1, 2}
+ Number of columns fetched=3
+ Number of pages visited=1
+ Number of rows qualified=9
+ Number of rows visited=9
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
+None
+ Right result set:
+ Hash Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking:
+ Number of opens = 9
+ Hash table size = 3
+ Hash key is column number 0
+ Rows seen = 27
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=9
+ Number of rows visited=9
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ scan qualifiers:
+None
+ next qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
ij> select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and a.c2 = 1;
C1 |C3 |C2
-----------------------------------
@@ -585,7 +700,51 @@
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
- close tim&
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1, 2}
+ Number of columns fetched=3
+ Number of pages visited=1
+ Number of rows qualified=9
+ Number of rows visited=9
+ 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
+ Right result set:
+ Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 9
+ Rows seen = 27
+ Rows filtered = 0
+ Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=9
+ Number of rows qualified=27
+ Number of rows visited=33
+ Scan type=btree
+ Tree height=1
+ start position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 1 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
ij> -- both keys from unique index in where clause but joined to different tables
select distinct a.c1 from one a, two b, three c where a.c1 = b.c1 and c.c1 = b.c3
and a.c1 = 1;
@@ -658,7 +817,90 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- opti&
+ Left result set:
+ Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 1
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=1
+ Number of rows visited=1
+ Scan type=btree
+ Tree height=-1
+ start position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 1 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
+ Right result set:
+ Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 3
+ Rows filtered = 0
+ Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=3
+ 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:
+ qualifiers:
+None
+ Right result set:
+ Index Scan ResultSet for THREE using index THREE_C1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 3
+ Rows seen = 3
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=3
+ Number of rows qualified=3
+ Number of rows visited=3
+ Scan type=btree
+ Tree height=1
+ start position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 1 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
ij> -- join between two tables using one columns of unique key
select distinct a.c1 from two a, four b where a.c1 = b.c1 and b.c3 = 1;
C1
@@ -730,7 +972,57 @@
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
- open tim&
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=3
+ Number of rows visited=9
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ qualifiers:
+Column[0][0] Id: 1
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+ Right result set:
+ Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 3
+ Rows seen = 9
+ Rows filtered = 0
+ Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=3
+ Number of rows qualified=9
+ Number of rows visited=11
+ Scan type=btree
+ Tree height=1
+ start position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 1 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
ij> -- join between two tables with no join predicate
select distinct a.c1, a.c3 from two a, one b;
C1 |C3
@@ -814,7 +1106,32 @@
None
qualifiers:
None
- optimizer es&
+ Right result set:
+ Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking chosen by the optimizer
+ Number of opens = 8
+ Rows seen = 72
+ Rows filtered = 0
+ Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ Number of pages visited=8
+ Number of rows qualified=72
+ Number of rows visited=72
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ qualifiers:
+None
ij> -- join between three tables with two tables joined uniquely
select distinct a.c1 from one a, two b, three c where a.c1 = c.c1 and a.c1 = 1;
C1
@@ -885,6 +1202,88 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
+ Left result set:
+ Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 1
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=1
+ Number of rows visited=1
+ Scan type=btree
+ Tree height=-1
+ start position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 1 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
+ Right result set:
+ Index Scan ResultSet for THREE using index THREE_C1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 1
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=1
+ Number of rows visited=1
+ Scan type=btree
+ Tree height=1
+ start position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 1 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
+ Right result set:
+ Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 9
+ Rows filtered = 0
+ Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={}
+ Number of columns fetched=0
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=9
+ Number of rows visited=9
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ qualifiers:
+None
ij> --
-- queries that should eliminate the distinct
-- single table queries
@@ -1154,7 +1553,6 @@
Ordered null semantics on the following columns:
qualifiers:
None
- optimizer estimat&
ij> select distinct c3 from one where c1 = 3;
C3
-----------
@@ -1440,6 +1838,34 @@
None
qualifiers:
None
+ Right result set:
+ Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 8
+ Rows seen = 8
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=8
+ Number of rows qualified=8
+ 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:
+ qualifiers:
+None
ij> select distinct a.c1, 3 from one a, one b where a.c1 = b.c1;
C1 |2
-----------------------
@@ -1519,7 +1945,34 @@
None
qualifiers:
None
- Right result &
+ Right result set:
+ Index Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 8
+ Rows seen = 8
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=8
+ Number of rows qualified=8
+ 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:
+ qualifiers:
+None
ij> select distinct a.c1, a.c3, a.c2 from two a, one b where a.c1 = b.c1;
C1 |C3 |C2
-----------------------------------
@@ -1597,7 +2050,38 @@
null qualifiers:
None
Right result set:
- Hash Scan ResultSet for ONE using index ONE_C1 at serializable &
+ Hash Scan ResultSet for ONE using index ONE_C1 at serializable isolation level using share table locking:
+ Number of opens = 9
+ Hash table size = 8
+ Hash key is column number 0
+ Rows seen = 9
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=8
+ Number of rows visited=8
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ scan qualifiers:
+None
+ next qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
ij> select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and b.c3 = 1;
C1 |C3 |C2
-----------------------------------
@@ -1681,7 +2165,43 @@
Operator: =
Ordered nulls: false
Unknown return value: false
-Negate comparis&
+Negate comparison result: false
+ Right result set:
+ Index Row to Base Row ResultSet for TWO:
+ Number of opens = 3
+ Rows seen = 9
+ Columns accessed from heap = {0, 1, 2}
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 3
+ Rows seen = 9
+ Rows filtered = 0
+ Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 2}
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ Number of pages visited=3
+ Number of rows qualified=9
+ Number of rows visited=11
+ Scan type=btree
+ Tree height=1
+ start position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 1 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
ij> -- join between two tables using both columns of unique key
select distinct a.c1 from two a, four b where a.c1 = b.c1 and a.c3 = b.c3 and b.c3 = 1;
C1
@@ -1758,7 +2278,38 @@
None
qualifiers:
Column[0][0] Id: 1
-O&
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+ Right result set:
+ Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 3
+ Rows seen = 3
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ Number of pages visited=3
+ Number of rows qualified=3
+ 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:
+ qualifiers:
+None
ij> -- clean up
drop table one;
0 rows inserted/updated/deleted
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinctElimination.sql
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinctElimination.sql?view=diff&rev=124992&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinctElimination.sql&r1=124991&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinctElimination.sql&r2=124992
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinctElimination.sql (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinctElimination.sql Wed Jan 12 13:19:13 2005
@@ -52,7 +52,7 @@
insert into four values (3, 1, 3, 1, 1);
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-maximumdisplaywidth 2000;
+maximumdisplaywidth 20000;
-- queries that cannot eliminate the distinct