db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r544532 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTe...
Date Tue, 05 Jun 2007 16:02:32 GMT
Author: bpendleton
Date: Tue Jun  5 09:02:30 2007
New Revision: 544532

URL: http://svn.apache.org/viewvc?view=rev&rev=544532
Log:
DERBY-2459: Ordering on a CASE expression causes NPE when using UNION

When an order by clause involves a CASE-expression, in a situation when
two or more select statements are combined in a union (or union all),
a NullPointerException is thrown.

In the original problem report, the CASE expression included qualified
column references:
   CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END 
This is invalid, because the "t1" and "t2" references are not applicable
to the result of the UNION operator. So an error message should have been
issued for the query from the DERBY-2459 description, due to the qualified
column references.

However, with the current implementation, CASE expressions as a whole are
not allowed in the ORDER BY clause of a *UNION* select. CASE expressions seem
to work correctly for a non-UNION select. I believe that Derby's current
behavior here is too restrictive; I think that there are valid CASE
expressions for a UNION select that *ought* to work (such as those which
use only unqualified column references to valid columns in the UNION's target
list), but the current implementation is lacking.

For the original query posted in the problem description, since the intent
was to order the results of the union by the second column in the union,
a column position expression can be used successfully: ORDER BY 2.

This patch causes Derby to issue a comprehensible error message which
reflects reality accurately, as this is preferable to crashing with an NPE. 

The patch also adds a number of additional ORDER BY test cases to the
ORDER BY test, demonstrating various query constructions, both those which
currently work, and those which currently do not work.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java?view=diff&rev=544532&r1=544531&r2=544532
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java Tue
Jun  5 09:02:30 2007
@@ -185,6 +185,8 @@
                 SanityManager.ASSERT( addedColumnOffset >= 0,
                                       "Order by expression was not pulled into the result
column list");
             resolveAddedColumn(target);
+		if (resultCol == null)
+			throw StandardException.newException(SQLState.LANG_UNION_ORDER_BY);
 		}
 
 		// Verify that the column is orderable

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?view=diff&rev=544532&r1=544531&r2=544532
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Tue Jun  5 09:02:30
2007
@@ -1174,6 +1174,11 @@
             </msg>
 
             <msg>
+                <name>42878</name>
+                <text>The ORDER BY clause of a SELECT UNION statement only supports
unqualified column references and column position numbers. Other expressions are not currently
supported.</text>
+            </msg>
+
+            <msg>
                 <name>42884</name>
                 <text>No authorized routine named '{0}' of type '{1}' having compatible
arguments was found.</text>
                 <arg>routineName</arg>

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?view=diff&rev=544532&r1=544531&r2=544532
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Tue Jun  5 09:02:30 2007
@@ -855,6 +855,7 @@
 	String LANG_ORDER_BY_COLUMN_NOT_FOUND                              = "42X78";
 	String LANG_DUPLICATE_COLUMN_FOR_ORDER_BY                          = "42X79";
 	String LANG_QUALIFIED_COLUMN_NAME_NOT_ALLOWED                      = "42877";
+        String LANG_UNION_ORDER_BY                                         = "42878";
 	String LANG_EMPTY_VALUES_CLAUSE                                    = "42X80";
 	String LANG_USING_CARDINALITY_VIOLATION                            = "42X82";
 	String LANG_ADDING_COLUMN_WITH_NULL_AND_NOT_NULL_CONSTRAINT        = "42X83";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?view=diff&rev=544532&r1=544531&r2=544532
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Tue Jun  5 09:02:30 2007
@@ -1485,4 +1485,193 @@
 A          |B          |C          |D          
 -----------------------------------------------
 1          |2          |3          |4          
+ij> -- Tests which verify the handling of expressions in the ORDER BY list
+-- related to DERBY-2459. The issue in DERBY-2459 has to do with handling
+-- of ORDER BY in the UNION case. The current Derby implementation has no
+-- support for expressions in the ORDER BY clause of a UNION SELECT.
+-- These test cases demonstrate some aspects of what works, and what doesn't.
+
+create table d2459_A1 ( id char(1) ,value int ,ref char(1));
+0 rows inserted/updated/deleted
+ij> create table d2459_A2 ( id char(1) ,value int ,ref char(1));
+0 rows inserted/updated/deleted
+ij> create table d2459_B1 ( id char(1) ,value int);
+0 rows inserted/updated/deleted
+ij> create table d2459_B2 ( id char(1) ,value int);
+0 rows inserted/updated/deleted
+ij> insert into d2459_A1 (id, value, ref) values ('b', 1, null);
+1 row inserted/updated/deleted
+ij> insert into d2459_A1 (id, value, ref) values ('a', 12, 'e');
+1 row inserted/updated/deleted
+ij> insert into d2459_A2 (id, value, ref) values ('c', 3, 'g');
+1 row inserted/updated/deleted
+ij> insert into d2459_A2 (id, value, ref) values ('d', 8, null);
+1 row inserted/updated/deleted
+ij> insert into d2459_B1 (id, value) values ('f', 2);
+1 row inserted/updated/deleted
+ij> insert into d2459_B1 (id, value) values ('e', 4);
+1 row inserted/updated/deleted
+ij> insert into d2459_B2 (id, value) values ('g', 5);
+1 row inserted/updated/deleted
+ij> -- Should work, as the order by expression is against a select, not a union:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END ;
+ID  |2          
+----------------
+c   |5          
+d   |8          
+ij> -- Should work, it's a simple column reference to the first column in UNION:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by id;
+ID  |2          
+----------------
+a   |4          
+b   |1          
+c   |5          
+d   |8          
+ij> -- Should work, it's a column reference by position number
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by 2;
+ID  |2          
+----------------
+b   |1          
+a   |4          
+c   |5          
+d   |8          
+ij> -- should fail, because qualified column references can't refer to UNIONs
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by t1.id;
+ERROR 42877: A qualified column name 'T1.ID' is not allowed in the ORDER BY clause.
+ij> -- should fail, because the union's results can't be referenced this way
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END;
+ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified column
references and column position numbers. Other expressions are not currently supported.
+ij> -- should fail, because this column is not in the result:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by value;
+ERROR 42X78: Column 'VALUE' is not in the result of the query expression.
+ij> -- ought to work, but currently fails, due to implementation restrictions:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by CASE WHEN id IS NOT NULL THEN id ELSE 2 END;
+ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified column
references and column position numbers. Other expressions are not currently supported.
+ij> -- Also ought to work, but currently fails due to implementation restrictions:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by id || 'abc';
+ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified column
references and column position numbers. Other expressions are not currently supported.
+ij> -- A number of simpler test cases investigating how the result set of the
+-- UNION is constructed. If both children have identical result column names,
+-- then the UNION result set's columns have the same names. Otherwise the
+-- UNION result set's columns have generated names, and can only be
+-- referred to by column position. Note als othat the matching of columns
+-- for the result set of the UNION is done by column position, not by name
+
+select id from D2459_A1 union select ref from D2459_A2;
+1   
+----
+a   
+b   
+g   
+NULL
+ij> select id from D2459_A1 union select ref from D2459_A2 order by id;
+ERROR 42X78: Column 'ID' is not in the result of the query expression.
+ij> select id from D2459_A1 union select ref from D2459_A2 order by 1;
+1   
+----
+a   
+b   
+g   
+NULL
+ij> select id i from D2459_A1 union select ref i from D2459_A2 order by i;
+I   
+----
+a   
+b   
+g   
+NULL
+ij> select id i from D2459_A1 union select ref j from D2459_A2;
+1   
+----
+a   
+b   
+g   
+NULL
+ij> select id i from D2459_A1 union select ref j from D2459_A2 order by i;
+ERROR 42X78: Column 'I' is not in the result of the query expression.
+ij> select id i from D2459_A1 union select ref j from D2459_A2 order by 1;
+1   
+----
+a   
+b   
+g   
+NULL
+ij> select id from D2459_A1 union select id from D2459_A2 order by D2459_A1.id;
+ERROR 42877: A qualified column name 'D2459_A1.ID' is not allowed in the ORDER BY clause.
+ij> select id from D2459_A1 union select id from D2459_A2 order by id||'abc';
+ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified column
references and column position numbers. Other expressions are not currently supported.
+ij> select * from D2459_A1 union select id, value, ref from D2459_A2 order by value;
+ID  |VALUE      |REF 
+---------------------
+b   |1          |NULL
+c   |3          |g   
+d   |8          |NULL
+a   |12         |e   
+ij> select id, value, ref from D2459_A1 union select * from D2459_A2 order by 2;
+ID  |VALUE      |REF 
+---------------------
+b   |1          |NULL
+c   |3          |g   
+d   |8          |NULL
+a   |12         |e   
+ij> select id, id i from D2459_A1 union select id j, id from D2459_A2 order by id;
+ERROR 42X78: Column 'ID' is not in the result of the query expression.
+ij> select id, id i from D2459_A1 union select id j, id from D2459_A2 order by 2;
+1   |2   
+---------
+a   |a   
+b   |b   
+c   |c   
+d   |d   
+ij> select id, ref from D2459_A1 union select ref, id from D2459_A2;
+1   |2   
+---------
+a   |e   
+b   |NULL
+g   |c   
+NULL|d   
+ij> select id i, ref j from D2459_A1 union select ref i, id j from D2459_A2;
+I   |J   
+---------
+a   |e   
+b   |NULL
+g   |c   
+NULL|d   
 ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?view=diff&rev=544532&r1=544531&r2=544532
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Tue Jun  5 09:02:30 2007
@@ -534,3 +534,105 @@
 select t.*, t.c+2 from derby1861 t order by a, b, c+2;
 select * from derby1861 order by 3, 1;
 select * from derby1861 order by 2, a-2;
+
+-- Tests which verify the handling of expressions in the ORDER BY list
+-- related to DERBY-2459. The issue in DERBY-2459 has to do with handling
+-- of ORDER BY in the UNION case. The current Derby implementation has no
+-- support for expressions in the ORDER BY clause of a UNION SELECT.
+-- These test cases demonstrate some aspects of what works, and what doesn't.
+
+create table d2459_A1 ( id char(1) ,value int ,ref char(1));
+create table d2459_A2 ( id char(1) ,value int ,ref char(1));
+create table d2459_B1 ( id char(1) ,value int);
+create table d2459_B2 ( id char(1) ,value int);
+insert into d2459_A1 (id, value, ref) values ('b', 1, null);
+insert into d2459_A1 (id, value, ref) values ('a', 12, 'e');
+insert into d2459_A2 (id, value, ref) values ('c', 3, 'g');
+insert into d2459_A2 (id, value, ref) values ('d', 8, null);
+insert into d2459_B1 (id, value) values ('f', 2);
+insert into d2459_B1 (id, value) values ('e', 4);
+insert into d2459_B2 (id, value) values ('g', 5); 
+
+-- Should work, as the order by expression is against a select, not a union:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END ;
+
+-- Should work, it's a simple column reference to the first column in UNION:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by id;
+
+-- Should work, it's a column reference by position number
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by 2;
+
+-- should fail, because qualified column references can't refer to UNIONs
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by t1.id;
+
+-- should fail, because the union's results can't be referenced this way
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END;
+
+-- should fail, because this column is not in the result:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by value;
+
+-- ought to work, but currently fails, due to implementation restrictions:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by CASE WHEN id IS NOT NULL THEN id ELSE 2 END;
+
+-- Also ought to work, but currently fails due to implementation restrictions:
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref
+union all
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by id || 'abc';
+
+-- A number of simpler test cases investigating how the result set of the
+-- UNION is constructed. If both children have identical result column names,
+-- then the UNION result set's columns have the same names. Otherwise the
+-- UNION result set's columns have generated names, and can only be
+-- referred to by column position. Note als othat the matching of columns
+-- for the result set of the UNION is done by column position, not by name
+
+select id from D2459_A1 union select ref from D2459_A2;
+select id from D2459_A1 union select ref from D2459_A2 order by id;
+select id from D2459_A1 union select ref from D2459_A2 order by 1;
+select id i from D2459_A1 union select ref i from D2459_A2 order by i;
+select id i from D2459_A1 union select ref j from D2459_A2;
+select id i from D2459_A1 union select ref j from D2459_A2 order by i;
+select id i from D2459_A1 union select ref j from D2459_A2 order by 1;
+select id from D2459_A1 union select id from D2459_A2 order by D2459_A1.id;
+select id from D2459_A1 union select id from D2459_A2 order by id||'abc';
+select * from D2459_A1 union select id, value, ref from D2459_A2 order by value;
+select id, value, ref from D2459_A1 union select * from D2459_A2 order by 2;
+select id, id i from D2459_A1 union select id j, id from D2459_A2 order by id;
+select id, id i from D2459_A1 union select id j, id from D2459_A2 order by 2;
+select id, ref from D2459_A1 union select ref, id from D2459_A2;
+select id i, ref j from D2459_A1 union select ref i, id j from D2459_A2;



Mime
View raw message