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 km...@apache.org on 2005/10/07 01:52:39 UTC
svn commit: r306964 - in /db/derby/code/branches/10.1/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/j9_13/
testing/org/apache/derbyTesting/func...
Author: kmarsden
Date: Thu Oct 6 16:52:27 2005
New Revision: 306964
URL: http://svn.apache.org/viewcvs?rev=306964&view=rev
Log:
DERBY-504
SELECT DISTINCT returns duplicates when selecting from subselects
Merged from trunk with
svn merge -r 267238:267239 https://svn.apache.org/repos/asf/db/derby/code/trunk
Contributed by Knut Anders Hatlen
I (Kathey) also ran j9 tests and updated the j9 masters for changes due to DERBY-392
Modified:
db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/distinct.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/distinct.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinct.sql
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Thu Oct 6 16:52:27 2005
@@ -96,6 +96,8 @@
import java.util.Enumeration;
import java.util.Properties;
import java.util.Vector;
+import java.util.HashSet;
+import java.util.Set;
/**
* A FromBaseTable represents a table in the FROM list of a DML statement,
@@ -4005,12 +4007,22 @@
* Is it possible to do a distinct scan on this ResultSet tree.
* (See SelectNode for the criteria.)
*
+ * @param distinctColumns the set of distinct columns
* @return Whether or not it is possible to do a distinct scan on this ResultSet tree.
*/
- boolean isPossibleDistinctScan()
+ boolean isPossibleDistinctScan(Set distinctColumns)
{
- return (restrictionList == null ||
- restrictionList.size() == 0);
+ if ((restrictionList != null && restrictionList.size() != 0)) {
+ return false;
+ }
+
+ HashSet columns = new HashSet();
+ for (int i = 0; i < resultColumns.size(); i++) {
+ ResultColumn rc = (ResultColumn) resultColumns.elementAt(i);
+ columns.add(rc.getExpression());
+ }
+
+ return columns.equals(distinctColumns);
}
/**
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java Thu Oct 6 16:52:27 2005
@@ -64,6 +64,8 @@
import org.apache.derby.iapi.services.classfile.VMOpcode;
import java.util.Properties;
+import java.util.HashSet;
+import java.util.Set;
/**
* A ProjectRestrictNode represents a result set for any of the basic DML
@@ -1608,16 +1610,26 @@
* Is it possible to do a distinct scan on this ResultSet tree.
* (See SelectNode for the criteria.)
*
+ * @param distinctColumns the set of distinct columns
* @return Whether or not it is possible to do a distinct scan on this ResultSet tree.
*/
- boolean isPossibleDistinctScan()
+ boolean isPossibleDistinctScan(Set distinctColumns)
{
if (restriction != null ||
(restrictionList != null && restrictionList.size() != 0))
{
return false;
}
- return childResult.isPossibleDistinctScan();
+
+ HashSet columns = new HashSet();
+ for (int i = 0; i < resultColumns.size(); i++) {
+ ResultColumn rc = (ResultColumn) resultColumns.elementAt(i);
+ BaseColumnNode bc = rc.getBaseColumnNode();
+ if (bc == null) return false;
+ columns.add(bc);
+ }
+
+ return columns.equals(distinctColumns) && childResult.isPossibleDistinctScan(distinctColumns);
}
/**
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Thu Oct 6 16:52:27 2005
@@ -1745,4 +1745,28 @@
public TableName getTableNameObject() {
return null;
}
+ /**
+ * Get the source BaseColumnNode for this result column. The
+ * BaseColumnNode cannot be found unless the ResultColumn is bound
+ * and is a simple reference to a column in a BaseFromTable.
+ *
+ * @return a BaseColumnNode,
+ * or null if a BaseColumnNode cannot be found
+ */
+ public BaseColumnNode getBaseColumnNode() {
+ ValueNode vn = expression;
+ while (true) {
+ if (vn instanceof ResultColumn) {
+ vn = ((ResultColumn) vn).expression;
+ } else if (vn instanceof ColumnReference) {
+ vn = ((ColumnReference) vn).getSource();
+ } else if (vn instanceof VirtualColumnNode) {
+ vn = ((VirtualColumnNode) vn).getSourceColumn();
+ } else if (vn instanceof BaseColumnNode) {
+ return (BaseColumnNode) vn;
+ } else {
+ return null;
+ }
+ }
+ }
}
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Thu Oct 6 16:52:27 2005
@@ -72,6 +72,7 @@
import java.util.Properties;
import java.util.Vector;
+import java.util.Set;
/**
* A ResultSetNode represents a result set, that is, a set of rows. It is
@@ -1857,9 +1858,10 @@
* Is it possible to do a distinct scan on this ResultSet tree.
* (See SelectNode for the criteria.)
*
+ * @param distinctColumns the set of distinct columns
* @return Whether or not it is possible to do a distinct scan on this ResultSet tree.
*/
- boolean isPossibleDistinctScan()
+ boolean isPossibleDistinctScan(Set distinctColumns)
{
return false;
}
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Thu Oct 6 16:52:27 2005
@@ -43,6 +43,7 @@
import org.apache.derby.iapi.util.JBitSet;
import java.util.Vector;
+import java.util.HashSet;
/**
* A SelectNode represents the result set for any of the basic DML
@@ -1335,14 +1336,29 @@
* FromBaseTable. This is because all of a table's columns must come
* from the same conglomerate in order to get consistent data.
*/
+ boolean distinctScanPossible = false;
if (origFromListSize == 1 &&
(! orderByAndDistinctMerged) &&
- resultColumns.countNumberOfSimpleColumnReferences() == resultColumns.size() &&
- prnRSN.isPossibleDistinctScan())
+ resultColumns.countNumberOfSimpleColumnReferences() == resultColumns.size())
{
- prnRSN.markForDistinctScan();
+ boolean simpleColumns = true;
+ HashSet distinctColumns = new HashSet();
+ int size = resultColumns.size();
+ for (int i = 1; i <= size; i++) {
+ BaseColumnNode bc = resultColumns.getResultColumn(i).getBaseColumnNode();
+ if (bc == null) {
+ simpleColumns = false;
+ break;
+ }
+ distinctColumns.add(bc);
+ }
+ if (simpleColumns && prnRSN.isPossibleDistinctScan(distinctColumns)) {
+ prnRSN.markForDistinctScan();
+ distinctScanPossible = true;
+ }
}
- else
+
+ if (!distinctScanPossible)
{
/* We can't do a distinct scan. Determine if we can filter out
* duplicates without a sorter.
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/distinct.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/distinct.out?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/distinct.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/distinct.out Thu Oct 6 16:52:27 2005
@@ -2477,6 +2477,288 @@
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name1 |sum2
ij> rollback;
-ij> -- reset autocomiit
+ij> -- Tests for DERBY-504 (select distinct from a subquery)
+create table names (id int, name varchar(10), age int);
+0 rows inserted/updated/deleted
+ij> insert into names (id, name, age) values
+ (1, 'Anna', 23), (2, 'Ben', 24), (3, 'Carl', 25),
+ (4, 'Anna', 23), (5, 'Ben', 24), (6, 'Carl', 25);
+6 rows inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+0 rows inserted/updated/deleted
+ij> maximumdisplaywidth 20000;
+ij> -- distinct names should be returned
+select distinct name from (select name, id from names) as n;
+NAME
+----------
+Anna
+Ben
+Carl
+ij> -- runtime statistics should not have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ -- distinct names should be returned
+select distinct name from (select name, id from names) as n
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Sort ResultSet:
+Number of opens = 1
+Rows input = 6
+Rows returned = 3
+Eliminate duplicates = true
+In sorted order = false
+Sort information:
+ Number of rows input=6
+ Number of rows output=3
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+Source result set:
+ Project-Restrict ResultSet (3):
+ Number of opens = 1
+ Rows seen = 6
+ Rows filtered = 0
+ restriction = false
+ projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ Table Scan ResultSet for NAMES at read committed isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 6
+ 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 pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
+None
+ij> -- distinct names should be returned
+select distinct name from (select name from names) as n;
+NAME
+----------
+Anna
+Ben
+Carl
+ij> -- runtime statistics should have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ -- distinct names should be returned
+select distinct name from (select name from names) as n
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Distinct Scan ResultSet for NAMES using index xxxxFILTERED-UUIDxxxx at read committed isolation level using instantaneous share row locking:
+Number of opens = 1
+Hash table size = 3
+Distinct column is column number 1
+Rows seen = 3
+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={1}
+ Number of columns fetched=1
+ Number of pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+ None
+ stop position:
+ None
+ scan qualifiers:
+None
+ next qualifiers:
+None
+ij> select distinct a, b, b, a from (select y as a, x as b from (select id as x, name as y from names) as n) as m;
+A |B |B |A
+---------------------------------------------
+Anna |1 |1 |Anna
+Carl |6 |6 |Carl
+Ben |5 |5 |Ben
+Carl |3 |3 |Carl
+Ben |2 |2 |Ben
+Anna |4 |4 |Anna
+ij> -- runtime statistics should have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ select distinct a, b, b, a from (select y as a, x as b from (select id as x, name as y from names) as n) as m
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Project-Restrict ResultSet (2):
+Number of opens = 1
+Rows seen = 6
+Rows filtered = 0
+restriction = false
+projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Distinct Scan ResultSet for NAMES using index xxxxFILTERED-UUIDxxxx at read committed isolation level using instantaneous share row locking:
+ Number of opens = 1
+ Hash table size = 6
+ Distinct columns are column numbers (0,1)
+ Rows seen = 6
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+ None
+ stop position:
+ None
+ scan qualifiers:
+None
+ next qualifiers:
+None
+ij> select distinct a, a from (select y as a from (select id as x, name as y from names) as n) as m;
+A |A
+---------------------
+Anna |Anna
+Ben |Ben
+Carl |Carl
+ij> -- runtime statistics should not have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ select distinct a, a from (select y as a from (select id as x, name as y from names) as n) as m
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Sort ResultSet:
+Number of opens = 1
+Rows input = 6
+Rows returned = 3
+Eliminate duplicates = true
+In sorted order = false
+Sort information:
+ Number of rows input=6
+ Number of rows output=3
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+Source result set:
+ Project-Restrict ResultSet (3):
+ Number of opens = 1
+ Rows seen = 6
+ Rows filtered = 0
+ restriction = false
+ projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ Table Scan ResultSet for NAMES at read committed isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 6
+ 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 pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
+None
+ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
+0 rows inserted/updated/deleted
+ij> drop table names;
+0 rows inserted/updated/deleted
+ij> -- reset autocommit
autocommit on;
ij>
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out Thu Oct 6 16:52:27 2005
@@ -289,7 +289,6 @@
------------------------------------------------------------------------
0 |goodbye everyone is here
0 |hello everyone is here
-0 |hello everyone is here
100 |hello everyone is here
0 |hello noone is here
NULL |NULL
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/distinct.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/distinct.out?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/distinct.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/distinct.out Thu Oct 6 16:52:27 2005
@@ -2477,6 +2477,313 @@
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name1 |sum2
ij> rollback;
-ij> -- reset autocomiit
+ij> create table td (x int);
+0 rows inserted/updated/deleted
+ij> insert into td values (1);
+1 row inserted/updated/deleted
+ij> insert into td values (1);
+1 row inserted/updated/deleted
+ij> insert into td values (2);
+1 row inserted/updated/deleted
+ij> -- distinct in subquery where the store does not perform the sort.
+select * from td, (select distinct 1 from td) as sub(x);
+X |X
+-----------------------
+1 |1
+1 |1
+2 |1
+ij> -- get the storage system to do the sort.
+select * from td, (select distinct x from td) as sub(x);
+X |X
+-----------------------
+1 |1
+1 |2
+1 |1
+1 |2
+2 |1
+2 |2
+ij> -- Tests for DERBY-504 (select distinct from a subquery)
+create table names (id int, name varchar(10), age int);
+0 rows inserted/updated/deleted
+ij> insert into names (id, name, age) values
+ (1, 'Anna', 23), (2, 'Ben', 24), (3, 'Carl', 25),
+ (4, 'Anna', 23), (5, 'Ben', 24), (6, 'Carl', 25);
+6 rows inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+0 rows inserted/updated/deleted
+ij> maximumdisplaywidth 20000;
+ij> -- distinct names should be returned
+select distinct name from (select name, id from names) as n;
+NAME
+----------
+Anna
+Ben
+Carl
+ij> -- runtime statistics should not have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ -- distinct names should be returned
+select distinct name from (select name, id from names) as n
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Sort ResultSet:
+Number of opens = 1
+Rows input = 6
+Rows returned = 3
+Eliminate duplicates = true
+In sorted order = false
+Sort information:
+ Number of rows input=6
+ Number of rows output=3
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+Source result set:
+ Project-Restrict ResultSet (3):
+ Number of opens = 1
+ Rows seen = 6
+ Rows filtered = 0
+ restriction = false
+ projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ Table Scan ResultSet for NAMES at read committed isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 6
+ 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 pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
+None
+ij> -- distinct names should be returned
+select distinct name from (select name from names) as n;
+NAME
+----------
+Ben
+Anna
+Carl
+ij> -- runtime statistics should have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ -- distinct names should be returned
+select distinct name from (select name from names) as n
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Distinct Scan ResultSet for NAMES using index xxxxFILTERED-UUIDxxxx at read committed isolation level using instantaneous share row locking:
+Number of opens = 1
+Hash table size = 3
+Distinct column is column number 1
+Rows seen = 3
+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={1}
+ Number of columns fetched=1
+ Number of pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+ None
+ stop position:
+ None
+ scan qualifiers:
+None
+ next qualifiers:
+None
+ij> select distinct a, b, b, a from (select y as a, x as b from (select id as x, name as y from names) as n) as m;
+A |B |B |A
+---------------------------------------------
+Ben |2 |2 |Ben
+Ben |5 |5 |Ben
+Anna |1 |1 |Anna
+Anna |4 |4 |Anna
+Carl |3 |3 |Carl
+Carl |6 |6 |Carl
+ij> -- runtime statistics should have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ select distinct a, b, b, a from (select y as a, x as b from (select id as x, name as y from names) as n) as m
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Project-Restrict ResultSet (2):
+Number of opens = 1
+Rows seen = 6
+Rows filtered = 0
+restriction = false
+projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Distinct Scan ResultSet for NAMES using index xxxxFILTERED-UUIDxxxx at read committed isolation level using instantaneous share row locking:
+ Number of opens = 1
+ Hash table size = 6
+ Distinct columns are column numbers (0,1)
+ Rows seen = 6
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+ None
+ stop position:
+ None
+ scan qualifiers:
+None
+ next qualifiers:
+None
+ij> select distinct a, a from (select y as a from (select id as x, name as y from names) as n) as m;
+A |A
+---------------------
+Anna |Anna
+Ben |Ben
+Carl |Carl
+ij> -- runtime statistics should not have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ select distinct a, a from (select y as a from (select id as x, name as y from names) as n) as m
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Sort ResultSet:
+Number of opens = 1
+Rows input = 6
+Rows returned = 3
+Eliminate duplicates = true
+In sorted order = false
+Sort information:
+ Number of rows input=6
+ Number of rows output=3
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+Source result set:
+ Project-Restrict ResultSet (3):
+ Number of opens = 1
+ Rows seen = 6
+ Rows filtered = 0
+ restriction = false
+ projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ Table Scan ResultSet for NAMES at read committed isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 6
+ 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 pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
+None
+ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
+0 rows inserted/updated/deleted
+ij> drop table names;
+0 rows inserted/updated/deleted
+ij> -- reset autocommit
autocommit on;
ij>
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out Thu Oct 6 16:52:27 2005
@@ -248,11 +248,7 @@
NULL
ij> -- grouping by long varchar [for bit data] cols should fail in db2 mode
select lbv from t group by lbv order by lbv;
-LBV
---------------------------------------------------------------------------------------------------------------------------------
-1234
-abcd
-NULL
+ERROR X0X67: Columns of type 'LONG VARCHAR FOR BIT DATA' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- multicolumn grouping
select i, dt, b from t where 1=1 group by i, dt, b order by i,dt,b;
I |DT |B
@@ -292,7 +288,6 @@
EXPR1 |EXPR2
------------------------------------------------------------------------
0 |goodbye everyone is here
-0 |hello everyone is here
0 |hello everyone is here
100 |hello everyone is here
0 |hello noone is here
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out Thu Oct 6 16:52:27 2005
@@ -2477,6 +2477,313 @@
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name1 |sum2
ij> rollback;
-ij> -- reset autocomiit
+ij> create table td (x int);
+0 rows inserted/updated/deleted
+ij> insert into td values (1);
+1 row inserted/updated/deleted
+ij> insert into td values (1);
+1 row inserted/updated/deleted
+ij> insert into td values (2);
+1 row inserted/updated/deleted
+ij> -- distinct in subquery where the store does not perform the sort.
+select * from td, (select distinct 1 from td) as sub(x);
+X |X
+-----------------------
+1 |1
+1 |1
+2 |1
+ij> -- get the storage system to do the sort.
+select * from td, (select distinct x from td) as sub(x);
+X |X
+-----------------------
+1 |2
+1 |1
+1 |2
+1 |1
+2 |2
+2 |1
+ij> -- Tests for DERBY-504 (select distinct from a subquery)
+create table names (id int, name varchar(10), age int);
+0 rows inserted/updated/deleted
+ij> insert into names (id, name, age) values
+ (1, 'Anna', 23), (2, 'Ben', 24), (3, 'Carl', 25),
+ (4, 'Anna', 23), (5, 'Ben', 24), (6, 'Carl', 25);
+6 rows inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+0 rows inserted/updated/deleted
+ij> maximumdisplaywidth 20000;
+ij> -- distinct names should be returned
+select distinct name from (select name, id from names) as n;
+NAME
+----------
+Anna
+Ben
+Carl
+ij> -- runtime statistics should not have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ -- distinct names should be returned
+select distinct name from (select name, id from names) as n
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Sort ResultSet:
+Number of opens = 1
+Rows input = 6
+Rows returned = 3
+Eliminate duplicates = true
+In sorted order = false
+Sort information:
+ Number of rows input=6
+ Number of rows output=3
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+Source result set:
+ Project-Restrict ResultSet (3):
+ Number of opens = 1
+ Rows seen = 6
+ Rows filtered = 0
+ restriction = false
+ projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ Table Scan ResultSet for NAMES at read committed isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 6
+ 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 pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
+None
+ij> -- distinct names should be returned
+select distinct name from (select name from names) as n;
+NAME
+----------
+Anna
+Ben
+Carl
+ij> -- runtime statistics should have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ -- distinct names should be returned
+select distinct name from (select name from names) as n
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Distinct Scan ResultSet for NAMES using index xxxxFILTERED-UUIDxxxx at read committed isolation level using instantaneous share row locking:
+Number of opens = 1
+Hash table size = 3
+Distinct column is column number 1
+Rows seen = 3
+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={1}
+ Number of columns fetched=1
+ Number of pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+ None
+ stop position:
+ None
+ scan qualifiers:
+None
+ next qualifiers:
+None
+ij> select distinct a, b, b, a from (select y as a, x as b from (select id as x, name as y from names) as n) as m;
+A |B |B |A
+---------------------------------------------
+Anna |1 |1 |Anna
+Carl |6 |6 |Carl
+Ben |5 |5 |Ben
+Carl |3 |3 |Carl
+Ben |2 |2 |Ben
+Anna |4 |4 |Anna
+ij> -- runtime statistics should have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ select distinct a, b, b, a from (select y as a, x as b from (select id as x, name as y from names) as n) as m
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Project-Restrict ResultSet (2):
+Number of opens = 1
+Rows seen = 6
+Rows filtered = 0
+restriction = false
+projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Distinct Scan ResultSet for NAMES using index xxxxFILTERED-UUIDxxxx at read committed isolation level using instantaneous share row locking:
+ Number of opens = 1
+ Hash table size = 6
+ Distinct columns are column numbers (0,1)
+ Rows seen = 6
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+ None
+ stop position:
+ None
+ scan qualifiers:
+None
+ next qualifiers:
+None
+ij> select distinct a, a from (select y as a from (select id as x, name as y from names) as n) as m;
+A |A
+---------------------
+Anna |Anna
+Ben |Ben
+Carl |Carl
+ij> -- runtime statistics should not have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ select distinct a, a from (select y as a from (select id as x, name as y from names) as n) as m
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Sort ResultSet:
+Number of opens = 1
+Rows input = 6
+Rows returned = 3
+Eliminate duplicates = true
+In sorted order = false
+Sort information:
+ Number of rows input=6
+ Number of rows output=3
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+Source result set:
+ Project-Restrict ResultSet (3):
+ Number of opens = 1
+ Rows seen = 6
+ Rows filtered = 0
+ restriction = false
+ projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ Table Scan ResultSet for NAMES at read committed isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 6
+ 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 pages visited=1
+ Number of rows qualified=6
+ Number of rows visited=6
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
+None
+ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
+0 rows inserted/updated/deleted
+ij> drop table names;
+0 rows inserted/updated/deleted
+ij> -- reset autocommit
autocommit on;
ij>
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out Thu Oct 6 16:52:27 2005
@@ -248,11 +248,7 @@
NULL
ij> -- grouping by long varchar [for bit data] cols should fail in db2 mode
select lbv from t group by lbv order by lbv;
-LBV
---------------------------------------------------------------------------------------------------------------------------------
-1234
-abcd
-NULL
+ERROR X0X67: Columns of type 'LONG VARCHAR FOR BIT DATA' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- multicolumn grouping
select i, dt, b from t where 1=1 group by i, dt, b order by i,dt,b;
I |DT |B
@@ -292,7 +288,6 @@
EXPR1 |EXPR2
------------------------------------------------------------------------
0 |goodbye everyone is here
-0 |hello everyone is here
0 |hello everyone is here
100 |hello everyone is here
0 |hello noone is here
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinct.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinct.sql?rev=306964&r1=306963&r2=306964&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinct.sql (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinct.sql Thu Oct 6 16:52:27 2005
@@ -329,5 +329,42 @@
execute c1 using 'values(''lusername1'', ''lusername2'', ''lname1'')';
rollback;
--- reset autocomiit
+-- Tests for DERBY-504 (select distinct from a subquery)
+
+create table names (id int, name varchar(10), age int);
+
+insert into names (id, name, age) values
+ (1, 'Anna', 23), (2, 'Ben', 24), (3, 'Carl', 25),
+ (4, 'Anna', 23), (5, 'Ben', 24), (6, 'Carl', 25);
+
+call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+maximumdisplaywidth 20000;
+
+-- distinct names should be returned
+select distinct name from (select name, id from names) as n;
+
+-- runtime statistics should not have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+-- distinct names should be returned
+select distinct name from (select name from names) as n;
+
+-- runtime statistics should have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+select distinct a, b, b, a from (select y as a, x as b from (select id as x, name as y from names) as n) as m;
+
+-- runtime statistics should have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+select distinct a, a from (select y as a from (select id as x, name as y from names) as n) as m;
+
+-- runtime statistics should not have Distinct Scan in it
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
+
+drop table names;
+
+-- reset autocommit
autocommit on;