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: r730188 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/impl/sql/compile/
 testing/org/apache/derbyTesting/functionTests/master/
 testing/org/apache/derbyTesting/functionTests/tests/lang/
Date: Tue, 30 Dec 2008 17:19:30 -0000
To: derby-commits@db.apache.org
From: kahatlen@apache.org
Message-Id: <20081230171930.717122388A65@eris.apache.org>

Author: kahatlen
Date: Tue Dec 30 09:19:29 2008
New Revision: 730188

URL: http://svn.apache.org/viewvc?rev=730188&view=rev
Log:
DERBY-3997: ORDER BY causes column to be returned

When all the columns in the order by clause are guaranteed to be
constant, the order by clause is removed. This patch ensures that we
also remove order by columns that are not in the select list from the
result.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderbyElimination.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=730188&r1=730187&r2=730188&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Tue Dec 30 09:19:29 2008
@@ -967,6 +967,7 @@
 				if (orderByList.size() == 0)
 				{
 					orderByList = null;
+                    resultColumns.removeOrderByColumns();
 				}
 			}
 		}

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=730188&r1=730187&r2=730188&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 Tue Dec 30 09:19:29 2008
@@ -1625,4 +1625,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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql?rev=730188&r1=730187&r2=730188&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderbyElimination.sql Tue Dec 30 09:19:29 2008
@@ -115,3 +115,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;