svn commit: r656929 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/select.out tests/lang/select.sql

[bp...@apache.org]

Author: bpendleton
Date: Thu May 15 20:15:33 2008
New Revision: 656929

URL: http://svn.apache.org/viewvc?rev=656929&view=rev
Log:
DERBY-2457: Use of column aliases could simplify queries

Derby does not permit the use of column aliases in WHERE clauses. When
a column is given an alias name, that name can only be used in the
ORDER BY clause, not in the WHERE clause (nor in the GROUP BY or
HAVING clauses -- see DERBY-2457).

This change adds several new tests to select.sql, demonstrating the
current Derby behavior, which we believe to be correct according
to the SQL standard.


Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/select.sql

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out?rev=656929&r1=656928&r2=656929&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out Thu May 15 20:15:33 2008
@@ -89,6 +89,18 @@
 ij> -- invalid correlation name for "*"
 select asdf.* from t;
 ERROR 42X10: 'ASDF' is not an exposed table name in the scope in which it appears.
+ij> -- Column aliases are not supported in WHERE clause:
+select s as col_s from t where col_s = 475;
+ERROR 42X04: Column 'COL_S' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'COL_S' is not a column in the target table.
+ij> -- Column references in WHERE clause always refer to the base table column:
+select s as i from t where s = 475;
+I     
+------
+475   
+ij> select s as i from t where i = 1956;
+I     
+------
+475   
 ij> -- cleanup
 drop table t;
 0 rows inserted/updated/deleted

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/select.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/select.sql?rev=656929&r1=656928&r2=656929&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/select.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/select.sql Thu May 15 20:15:33 2008
@@ -66,6 +66,12 @@
 -- invalid correlation name for "*"
 select asdf.* from t; 
 
+-- Column aliases are not supported in WHERE clause:
+select s as col_s from t where col_s = 475;
+-- Column references in WHERE clause always refer to the base table column:
+select s as i from t where s = 475;
+select s as i from t where i = 1956;
+
 -- cleanup
 drop table t;