Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r787504 - in /db/derby/code/branches/10.2/java:
 engine/org/apache/derby/impl/sql/compile/
 testing/org/apache/derbyTesting/functionTests/master/
 testing/org/apache/derbyTesting/functionTests/tests/lang/
Date: Tue, 23 Jun 2009 00:34:21 -0000
To: derby-commits@db.apache.org
From: kmarsden@apache.org
Message-Id: <20090623003421.48DF42388893@eris.apache.org>

Author: kmarsden
Date: Tue Jun 23 00:34:20 2009
New Revision: 787504

URL: http://svn.apache.org/viewvc?rev=787504&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.2/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=787504&r1=787503&r2=787504&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Tue Jun 23 00:34:20 2009
@@ -1005,6 +1005,7 @@
 				if (orderByList.size() == 0)
 				{
 					orderByList = null;
+                    resultColumns.removeOrderByColumns();
 				}
 			}
 		}

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out?rev=787504&r1=787503&r2=787504&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out Tue Jun 23 00:34:20 2009
@@ -1494,4 +1494,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.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql?rev=787504&r1=787503&r2=787504&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql Tue Jun 23 00:34:20 2009
@@ -104,3 +104,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;