Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 57071 invoked from network); 12 May 2005 02:01:28 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 12 May 2005 02:01:28 -0000 Received: (qmail 2685 invoked by uid 500); 12 May 2005 02:05:20 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 2661 invoked by uid 500); 12 May 2005 02:05:19 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 2645 invoked by uid 500); 12 May 2005 02:05:19 -0000 Delivered-To: apmail-incubator-derby-cvs@incubator.apache.org Received: (qmail 2642 invoked by uid 99); 12 May 2005 02:05:19 -0000 X-ASF-Spam-Status: No, hits=0.2 required=10.0 tests=NO_REAL_NAME X-Spam-Check-By: apache.org Received: from minotaur.apache.org (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.28) with SMTP; Wed, 11 May 2005 19:05:18 -0700 Received: (qmail 57043 invoked by uid 65534); 12 May 2005 02:01:25 -0000 Message-ID: <20050512020125.57042.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: svn commit: r169744 - in /incubator/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: Thu, 12 May 2005 02:01:24 -0000 To: derby-cvs@incubator.apache.org From: bandaram@apache.org X-Mailer: svnmailer-1.0.0-dev X-Virus-Checked: Checked X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Author: bandaram Date: Wed May 11 19:01:23 2005 New Revision: 169744 URL: http://svn.apache.org/viewcvs?rev=3D169744&view=3Drev Log: Derby-127: Handle the case of select statements that use a correlation names in the select list, a group by clause, and an order by clause that refers to a column by its database name instead of its correlation name. (Eg: select c1 as x from t where ... group by x order by c1) Derby currently throws an exception with SQLState 42x04. Submitted by Jack Klebanoff (klebanoff-derby@sbcglobal.net) Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/OrderByColumn.java incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ResultColumnList.java incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/TableName.java incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/sqlgrammar.jj incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/orderby.out incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/orderby.sql Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/= compile/OrderByColumn.java URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/o= rg/apache/derby/impl/sql/compile/OrderByColumn.java?rev=3D169744&r1=3D16974= 3&r2=3D169744&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/OrderByColumn.java (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/OrderByColumn.java Wed May 11 19:01:23 2005 @@ -30,6 +30,8 @@ import org.apache.derby.iapi.sql.compile.NodeFactory; import org.apache.derby.iapi.sql.compile.C_NodeTypes; =20 +import org.apache.derby.iapi.util.ReuseFactory; + /** * An OrderByColumn is a column in the ORDER BY clause. An OrderByColumn * can be ordered ascending or descending. @@ -44,9 +46,15 @@ private ResultColumn resultCol; private boolean ascending =3D true; private ValueNode expression; + /** + * If this sort key is added to the result column list then it is at r= esult column position + * 1 + resultColumnList.size() - resultColumnList.getOrderBySelect() += addedColumnOffset + * If the sort key is already in the result column list then addedColu= mnOffset < 0. + */ + private int addedColumnOffset =3D -1; =20 =20 - /** + /** * Initializer. * * @param expression Expression of this column @@ -161,31 +169,23 @@ } =20 }else{ - ResultColumnList targetCols =3D target.getResultColumns(); - ResultColumn col =3D null; - int i =3D 1; - =09 - for(i =3D 1; - i <=3D targetCols.size(); - i ++){ - =09 - col =3D targetCols.getOrderByColumn(i); - if(col !=3D null &&=20 - col.getExpression() =3D=3D expression){ - =09 - break; - } - } - =09 - resultCol =3D col; - columnPosition =3D i; - =20 + if( SanityManager.DEBUG) + SanityManager.ASSERT( addedColumnOffset >=3D 0, + "Order by expression was not pulled = into the result column list"); + resolveAddedColumn(target); } =20 // Verify that the column is orderable resultCol.verifyOrderable(); } =20 + private void resolveAddedColumn(ResultSetNode target) + { + ResultColumnList targetCols =3D target.getResultColumns(); + columnPosition =3D targetCols.size() - targetCols.getOrderBySelect= () + addedColumnOffset + 1; + resultCol =3D targetCols.getResultColumn( columnPosition); + } + /** * Pull up this orderby column if it doesn't appear in the resultset * @@ -195,15 +195,42 @@ public void pullUpOrderByColumn(ResultSetNode target) throws StandardException=20 { - if(expression instanceof ColumnReference){ + ResultColumnList targetCols =3D target.getResultColumns(); + + // If the target is generated for a select node then we must also = pull the order by column + // into the select list of the subquery. + if((target instanceof SelectNode) && ((SelectNode) target).getGene= ratedForGroupbyClause()) + { + if( SanityManager.DEBUG) + SanityManager.ASSERT( target.getFromList().size() =3D=3D 1 + && (target.getFromList().elementAt(0= ) instanceof FromSubquery) + && targetCols.size() =3D=3D 1 + && targetCols.getResultColumn(1) ins= tanceof AllResultColumn, + "Unexpected structure of selectNode = generated for a group by clause"); + + ResultSetNode subquery =3D ((FromSubquery) target.getFromList(= )=2EelementAt(0)).getSubquery(); + pullUpOrderByColumn( subquery); + if( resultCol =3D=3D null) // The order by column is reference= d by number + return; + + // ResultCol is in the subquery's ResultColumnList. We have to= transform this OrderByColumn + // so that it refers to the column added to the subquery. We a= ssume that the select list + // in the top level target is a (generated) AllResultColumn no= de, so the this order by expression + // does not have to be pulled into the the top level ResultCol= umnList. Just change this + // OrderByColumn to be a reference to the added column. We can= not use an integer column + // number because the subquery can have a '*' in its select li= st, causing the column + // number to change when the '*' is expanded. + resultCol =3D null; + targetCols.copyOrderBySelect( subquery.getResultColumns()); + return; + } + + if(expression instanceof ColumnReference){ =20 ColumnReference cr =3D (ColumnReference) expression; =20 - ResultColumnList targetCols =3D target.getResultColumns(); resultCol =3D targetCols.getOrderByColumn(cr.getColumnName(), - cr.tableName !=3D null ?=20 - cr.tableName.getFullTableName(): - null); + cr.getTableNameNode()); =20 if(resultCol =3D=3D null){ resultCol =3D (ResultColumn) getNodeFactory().getNode(C_NodeTypes.RESU= LT_COLUMN, @@ -211,16 +238,17 @@ cr, getContextManager()); targetCols.addResultColumn(resultCol); + addedColumnOffset =3D targetCols.getOrderBySelect(); targetCols.incOrderBySelect(); } =09 }else if(!isReferedColByNum(expression)){ - ResultColumnList targetCols =3D target.getResultColumns(); resultCol =3D (ResultColumn) getNodeFactory().getNode(C_NodeTypes.RESUL= T_COLUMN, null, expression, getContextManager()); targetCols.addResultColumn(resultCol); + addedColumnOffset =3D targetCols.getOrderBySelect(); targetCols.incOrderBySelect(); } } @@ -284,7 +312,7 @@ } =20 =09 - private static ResultColumn resolveColumnReference(ResultSetNode target, + private ResultColumn resolveColumnReference(ResultSetNode target, ColumnReference cr) throws StandardException{ =09 @@ -336,8 +364,15 @@ ResultColumnList targetCols =3D target.getResultColumns(); =20 resultCol =3D targetCols.getOrderByColumn(cr.getColumnName(), - cr.getTableName(), + cr.getTableNameNode(), sourceTableNumber); + /* Search targetCols before using addedColumnOffset because select= list wildcards, '*', + * are expanded after pullUpOrderByColumn is called. A simple colu= mn reference in the + * order by clause may be found in the user specified select list = now even though it was + * not found when pullUpOrderByColumn was called. + */ + if( resultCol =3D=3D null && addedColumnOffset >=3D 0) + resolveAddedColumn(target); =09 if (resultCol =3D=3D null || resultCol.isNameGenerated()){ String errString =3D cr.columnName; Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/= compile/ResultColumnList.java URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/o= rg/apache/derby/impl/sql/compile/ResultColumnList.java?rev=3D169744&r1=3D16= 9743&r2=3D169744&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ResultColumnList.java (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ResultColumnList.java Wed May 11 19:01:23 2005 @@ -354,14 +354,14 @@ * columnName and ensure that there is only one match. * * @param columnName The ResultColumn to get from the list - * @param exposedName The correlation name on the OrderByColumn, if any + * @param tableName The table name on the OrderByColumn, if any * @param tableNumber The tableNumber corresponding to the FromTable with= the - * exposed name of exposedName, if exposedName !=3D null. + * exposed name of tableName, if tableName !=3D null. * * @return the column that matches that name. * @exception StandardException thrown on duplicate */ - public ResultColumn getOrderByColumn(String columnName, String exposedNam= e, int tableNumber) + public ResultColumn getOrderByColumn(String columnName, TableName tableNa= me, int tableNumber) throws StandardException { int size =3D size(); @@ -378,26 +378,15 @@ * o The RC is not qualified, but its expression is a ColumnReference * from the same table (as determined by the tableNumbers). */ - if (exposedName !=3D null) + if (tableName !=3D null) { - String rcTableName =3D resultColumn.getTableName(); - - if (rcTableName =3D=3D null) - { - ValueNode rcExpr =3D resultColumn.getExpression(); - if (! (rcExpr instanceof ColumnReference)) - { - continue; - } - else if (tableNumber !=3D ((ColumnReference) rcExpr).getTableNumber()) - { + ValueNode rcExpr =3D resultColumn.getExpression(); + if (! (rcExpr instanceof ColumnReference)) continue; - } - } - else if (! exposedName.equals(resultColumn.getTableName())) - { - continue; - } + + ColumnReference cr =3D (ColumnReference) rcExpr; + if( (! tableName.equals( cr.getTableNameNode())) && tableN= umber !=3D cr.getTableNumber()) + continue; } =20 /* We finally got past the qualifiers, now see if the column @@ -430,12 +419,12 @@ * columnName and ensure that there is only one match before the bind pro= cess. * * @param columnName The ResultColumn to get from the list - * @param exposedName The correlation name on the OrderByColumn, if any + * @param tableName The table name on the OrderByColumn, if any * * @return the column that matches that name. * @exception StandardException thrown on duplicate */ - public ResultColumn getOrderByColumn(String columnName, String exposedNam= e) + public ResultColumn getOrderByColumn(String columnName, TableName tableNa= me) throws StandardException { int size =3D size(); @@ -449,20 +438,16 @@ // exposedName will not be null and "*" will not have an expression // or tablename. // We may be checking on "ORDER BY T.A" against "SELECT T.B, T.A". - if (exposedName !=3D null) + if (tableName !=3D null) { ValueNode rcExpr =3D resultColumn.getExpression(); - if (rcExpr =3D=3D null || resultColumn.getTableName() =3D=3D null) - { - continue; - } - else - { - if (! (rcExpr instanceof ColumnReference) || ! exposedName.equals(res= ultColumn.getTableName())) - { - continue; - } - } + if (rcExpr =3D=3D null || ! (rcExpr instanceof ColumnReference)) + { + continue; + } + ColumnReference cr =3D (ColumnReference) rcExpr; + if( ! tableName.equals( cr.getTableNameNode())) + continue; } =20 /* We finally got past the qualifiers, now see if the column @@ -3925,4 +3910,9 @@ { return orderBySelect; } + + public void copyOrderBySelect( ResultColumnList src) + { + orderBySelect =3D src.orderBySelect; + } } Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/= compile/TableName.java URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/o= rg/apache/derby/impl/sql/compile/TableName.java?rev=3D169744&r1=3D169743&r2= =3D169744&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/TableName.java (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/TableName.java Wed May 11 19:01:23 2005 @@ -206,6 +206,9 @@ */ public boolean equals(TableName otherTableName) { + if( otherTableName =3D=3D null) + return false; + =20 String fullTableName =3D getFullTableName(); if (fullTableName =3D=3D null) { Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/= compile/sqlgrammar.jj URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/o= rg/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=3D169744&r1=3D169743&r2= =3D169744&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/sqlgrammar.jj (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/sqlgrammar.jj Wed May 11 19:01:23 2005 @@ -7305,6 +7305,12 @@ * * RESOLVE - someday we should try to find matching aggregates * instead of just adding them. + * + * NOTE: This rewriting of the query tree makes the handling o= f an ORDER BY + * clause difficult. See OrderByColumn.pullUpOrderByColumn. It= makes specific + * assumptions about the structure of the generated query tree= . Do not make + * any changes to this transformation without carefully consid= ering the + * OrderByColumn pullUpOrderByColumn and bindOrderByColumn met= hods. */ if (havingClause !=3D null) { Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/f= unctionTests/master/orderby.out URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/= org/apache/derbyTesting/functionTests/master/orderby.out?rev=3D169744&r1=3D= 169743&r2=3D169744&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/orderby.out (original) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/orderby.out Wed May 11 19:01:23 2005 @@ -759,6 +759,30 @@ ----------------------- 1 |2 =20 3 |6 =20 +ij> select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug= 2769.c1 order by bug2769.c1; +X |Y =20 +----------------------- +1 |2 =20 +3 |6 =20 +ij> select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug= 2769.c1 order by x; +X |Y =20 +----------------------- +1 |2 =20 +3 |6 =20 +ij> select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 o= rder by c1 + c2; +X |Y =20 +----------------------- +1 |1 =20 +1 |2 =20 +3 |2 =20 +3 |3 =20 +ij> select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 o= rder by -(c1 + c2); +X |Y =20 +----------------------- +3 |3 =20 +3 |2 =20 +1 |2 =20 +1 |1 =20 ij> rollback; ij> -- reset autocommit autocommit on; Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/f= unctionTests/tests/lang/orderby.sql URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/= org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=3D169744&r= 1=3D169743&r2=3D169744&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/orderby.sql (original) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/orderby.sql Wed May 11 19:01:23 2005 @@ -320,6 +320,10 @@ create table bug2769(c1 int, c2 int); insert into bug2769 values (1, 1), (1, 2), (3, 2), (3, 3); select a.c1, sum(a.c1) from bug2769 a group by a.c1 order by a.c1; +select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769= .c1 order by bug2769.c1; +select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769= .c1 order by x; +select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order= by c1 + c2; +select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order= by -(c1 + c2); rollback; =20 -- reset autocommit