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 2009/06/23 23:49:32 UTC
svn commit: r787847 - 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/tests/lang/
Author: kmarsden
Date: Tue Jun 23 21:49:32 2009
New Revision: 787847
URL: http://svn.apache.org/viewvc?rev=787847&view=rev
Log:
DERBY-3997 ORDER BY causes column to be returned
Contributed by Knut Anders Hatlen (knut dot hatlen at sun dot com)
Modified:
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/orderbyElimination.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=787847&r1=787846&r2=787847&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 Tue Jun 23 21:49:32 2009
@@ -1024,6 +1024,7 @@
if (orderByList.size() == 0)
{
orderByList = null;
+ resultColumns.removeOrderByColumns();
}
}
}
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out?rev=787847&r1=787846&r2=787847&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out Tue Jun 23 21:49:32 2009
@@ -1478,4 +1478,55 @@
0 rows inserted/updated/deleted
ij> drop table u3;
0 rows inserted/updated/deleted
+ij> -- DERBY-3997: Elimination of ORDER BY clause because all the columns
+-- to order by were known to be constant, made extra columns appear in
+-- the result.
+create table d3997(x int, y int, z int);
+0 rows inserted/updated/deleted
+ij> -- These queries used to have two result columns, but should only have one
+select 1 from d3997 where x=1 order by x;
+1
+-----------
+ij> select y from d3997 where x=1 order by x;
+Y
+-----------
+ij> -- Used to have three columns, should only have two
+select y,z from d3997 where x=1 order by x;
+Y |Z
+-----------------------
+ij> -- Used to have three columns, should only have one
+select x from d3997 where y=1 and z=1 order by y,z;
+X
+-----------
+ij> -- Dynamic parameters are also constants (expect one column)
+execute 'select x from d3997 where y=? order by y' using 'values 1';
+IJ WARNING: Autocommit may close using result set
+X
+-----------
+ij> -- Order by columns should not be removed from the result here
+select * from d3997 where x=1 order by x;
+X |Y |Z
+-----------------------------------
+ij> select x,y,z from d3997 where x=1 order by x;
+X |Y |Z
+-----------------------------------
+ij> select x,y,z from d3997 where x=1 and y=1 order by x,y;
+X |Y |Z
+-----------------------------------
+ij> -- Order by should not be eliminated here (not constant values). Insert some
+-- data in reverse order to verify that the results are sorted.
+insert into d3997 values (9,8,7),(6,5,4),(3,2,1);
+3 rows inserted/updated/deleted
+ij> select * from d3997 where y<>2 order by y;
+X |Y |Z
+-----------------------------------
+6 |5 |4
+9 |8 |7
+ij> select z from d3997 where y>2 order by y;
+Z
+-----------
+4
+7
+ij> drop table d3997;
+0 rows inserted/updated/deleted
ij>
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql?rev=787847&r1=787846&r2=787847&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql Tue Jun 23 21:49:32 2009
@@ -88,3 +88,27 @@
drop table u1;
drop table u2;
drop table u3;
+
+-- DERBY-3997: Elimination of ORDER BY clause because all the columns
+-- to order by were known to be constant, made extra columns appear in
+-- the result.
+create table d3997(x int, y int, z int);
+-- These queries used to have two result columns, but should only have one
+select 1 from d3997 where x=1 order by x;
+select y from d3997 where x=1 order by x;
+-- Used to have three columns, should only have two
+select y,z from d3997 where x=1 order by x;
+-- Used to have three columns, should only have one
+select x from d3997 where y=1 and z=1 order by y,z;
+-- Dynamic parameters are also constants (expect one column)
+execute 'select x from d3997 where y=? order by y' using 'values 1';
+-- Order by columns should not be removed from the result here
+select * from d3997 where x=1 order by x;
+select x,y,z from d3997 where x=1 order by x;
+select x,y,z from d3997 where x=1 and y=1 order by x,y;
+-- Order by should not be eliminated here (not constant values). Insert some
+-- data in reverse order to verify that the results are sorted.
+insert into d3997 values (9,8,7),(6,5,4),(3,2,1);
+select * from d3997 where y<>2 order by y;
+select z from d3997 where y>2 order by y;
+drop table d3997;