Index: java/engine/org/apache/derby/impl/sql/compile/ConstantNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/ConstantNode.java (revision 471112)
+++ java/engine/org/apache/derby/impl/sql/compile/ConstantNode.java (working copy)
@@ -282,7 +282,11 @@
{
if (isSameNodeType(o)) {
ConstantNode other = (ConstantNode)o;
- return other.getValue().compare(getValue()) == 0;
+
+ // value can be null which represents a SQL NULL value.
+ return ( (other.getValue() == null && getValue() == null) ||
+ (other.getValue() != null &&
+ other.getValue().compare(getValue()) == 0) );
}
return false;
}
Index: java/engine/org/apache/derby/impl/sql/compile/ValueNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/ValueNode.java (revision 471112)
+++ java/engine/org/apache/derby/impl/sql/compile/ValueNode.java (working copy)
@@ -1335,6 +1335,43 @@
* ValueNodes are considered equivalent if they will evaluate to the same
* value during query execution.
*
+ * This method provides basic expression matching facility for the derived
+ * class of ValueNode and it is used by the language layer to compare the
+ * node structural form of the two expressions for equivalence at bind
+ * phase.
+ *
+ * Note that it is not comparing the actual row values at runtime to produce
+ * a result; hence, when comparing SQL NULLs, they are considered to be
+ * equivalent and not unknown.
+ *
+ * One usage case of this method in this context is to compare the select
+ * column expression against the group by expression to check if they are
+ * equivalent. e.g.:
+ *
+ * SELECT c1+c2 FROM t1 GROUP BY c1+c2
+ *
+ * In general, node equivalence is determined by the derived class of
+ * ValueNode. But they generally abide to the rules below:
+ *
+ * o The two ValueNodes must be of the same node type to be considered
+ * equivalent. e.g.: CastNode vs. CastNode - equivalent (if their args
+ * also match), ColumnReference vs CastNode - not equivalent.
+ *
+ * o If node P contains other ValueNode(s) and so on, those node(s) must
+ * also be of the same node type to be considered equivalent.
+ *
+ * o If node P takes a parameter list, then the number of arguments and its
+ * arguments for the two nodes must also match to be considered
+ * equivalent. e.g.: CAST(c1 as INTEGER) vs CAST(c1 as SMALLINT), they
+ * are not equivalent.
+ *
+ * o When comparing SQL NULLs in this context, they are considered to be
+ * equivalent.
+ *
+ * o If this does not apply or it is determined that the two nodes are not
+ * equivalent then the derived class of this method should return false;
+ * otherwise, return true.
+ *
* @param other the node to compare this ValueNode against.
* @return true if the two nodes are equivalent,
* false otherwise.
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/groupBy.sql
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/groupBy.sql (revision 471112)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/groupBy.sql (working copy)
@@ -360,15 +360,3 @@
-- ok, gives one row
select 10,avg(c) from t having 1 < 2;
drop table t;
-
--- DERBY-2008
--- test SUBSTR with 2 args with GROUP BY expression
-create table dt (vc varchar(30));
-insert into dt values ('1928-09-21'), ('1903-12-08');
--- ok
-select substr(vc, 3) from dt group by substr(vc, 3);
-select substr(vc, 3, 4) from dt group by substr(vc, 3, 4);
--- expect errors
-select substr(vc, 3, 4) from dt group by substr(vc, 3);
-select substr(vc, 3) from dt group by substr(vc, 3, 4);
-drop table dt;
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java (revision 471112)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java (working copy)
@@ -36,7 +36,7 @@
public class GroupByExpressionTest extends BaseJDBCTestCase
{
- private static String[][] TABLES = {
+ private static String[][] TABLES = {
{"test", "create table test (c1 int, c2 int, c3 int, c4 int)"},
{"coal", "create table coal (vc1 varchar(2), vc2 varchar(2))"},
{"alltypes",
@@ -45,7 +45,12 @@
" d double precision, r real, " +
" dt date, t time, ts timestamp, " +
" b char(2) for bit data, bv varchar(8) for bit data, " +
- " lbv long varchar for bit data, dc decimal(5,2))"}};
+ " lbv long varchar for bit data, dc decimal(5,2))"},
+ {"t1", "create table t1 (c1 varchar(30))"},
+ {"t2", "create table t2 (c1 varchar(10))"},
+ {"t3", "create table t3 (c1 int, c2 int)"}
+ };
+
private static String[][] FUNCTIONS = {
{"r", "create function r() returns double external name " +
"'java.lang.Math.random' language java parameter style java"}};
@@ -182,6 +187,81 @@
{"dupl", new Integer(14)},
{"good", new Integer(1)}});
+ // DERBY-2008
+ // substr (2-args)
+ verifyQueryResults(
+ "substr-Q1",
+ "select substr(c1, 3) from t1 group by substr(c1, 3)",
+ new String[][] { {"03-12-08"},
+ {"28-09-21"} });
+ // substr (3-args)
+ verifyQueryResults(
+ "substr-Q2",
+ "select substr(c1, 3, 4) from t1 group by substr(c1, 3, 4)",
+ new String[][] { {"03-1"},
+ {"28-0"} });
+
+ // ltrim
+ verifyQueryResults(
+ "ltrim",
+ "select ltrim(c1) from t2 group by ltrim(c1)",
+ new String[][] { {"123 "},
+ {"abc "} });
+
+ // rtrim
+ verifyQueryResults(
+ "rtrim",
+ "select rtrim(c1) from t2 group by rtrim(c1)",
+ new String[][] { {"123"},
+ {"abc"} });
+
+ // locate (2-args)
+ verifyQueryResults(
+ "locate-Q1",
+ "select locate(c1, 'abc') from t2 group by locate(c1, 'abc')",
+ new int[][] { { 0 },
+ { 1 } });
+
+ // locate (3-args)
+ verifyQueryResults(
+ "locate-Q2",
+ "select locate(c1, 'abc', 1) from t2 group by locate(c1, 'abc',1)",
+ new int[][] { { 0 },
+ { 1 } });
+
+ // cast with NULL
+ verifyQueryResults(
+ "cast-Q2",
+ "select (cast (NULL as INTEGER)) from t2 group by (cast (NULL as INTEGER))",
+ new Object[][] { { null } } );
+
+ // DERBY-2014
+ // nullif
+ verifyQueryResults(
+ "nullif-Q1",
+ "select nullif(c1,c1) from t3 group by nullif(c1,c1)",
+ new Object[][] { { null } } );
+
+ verifyQueryResults(
+ "nullif-Q2",
+ "select nullif(c1,c2) from t3 group by nullif(c1,c2)",
+ new Object[][] { { new Integer(5) },
+ { null } });
+
+ verifyQueryResults(
+ "nullif-Q3",
+ "select nullif(c1,10) from t3 group by nullif(c1,10)",
+ new Object[][] { { new Integer(1) },
+ { new Integer(2) },
+ { new Integer(3) },
+ { new Integer(5) },
+ { null } });
+
+ verifyQueryResults(
+ "nullif-Q4",
+ "select nullif(1,c1) from t3 group by nullif(1,c1)",
+ new Object[][] { { new Integer(1) },
+ { null } });
}
public void testExtractOperator() throws Exception
@@ -263,6 +343,34 @@
assertCompileError(
"42Y30",
"select substr(c, 3, 4) from alltypes group by substr(v, 3, 4)");
+
+ // DERBY-2008
+ // invalid grouping expression
+ assertCompileError(
+ "42Y30",
+ "select substr(c1, 3, 4) from t1 group by substr(c1, 3)");
+ assertCompileError(
+ "42Y30",
+ "select substr(c1, 3) from t1 group by substr(c1, 3, 4)");
+ assertCompileError(
+ "42Y30",
+ "select locate(c1, 'abc') from t2 group by locate(c1, 'abc',3)");
+ assertCompileError(
+ "42Y30",
+ "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc')");
+ assertCompileError(
+ "42Y30",
+ "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc',3)");
+
+ // DERBY-2014
+ // invalid grouping expression
+ assertCompileError(
+ "42Y30",
+ "select nullif(c1,c2) from t3 group by nullif(c2,c1)");
+ assertCompileError(
+ "42Y30",
+ "select nullif(c1,100) from t3 group by nullif(c1,200)");
+
// aggregates in group by list.
assertCompileError(
"42Y26",
@@ -463,7 +571,11 @@
" date('1992-03-04'), time('12:30:42'), " +
" timestamp('1992-03-04 12:30:42'), " +
" X'12af', X'1111111111111111', X'1234', 111.11) " );
-
+
+ s.execute("insert into t1 values ('1928-09-21'), ('1903-12-08')");
+ s.execute("insert into t2 values '123 ', 'abc ', '123', 'abc'") ;
+ s.execute("insert into t3 values (1,1), (2,2), (2,2), (3,3), (null, null), (5,100)");
+
s.close();
c.commit();
c.close();
Index: java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out (revision 471112)
+++ java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out (working copy)
@@ -636,28 +636,4 @@
10 |2
ij> drop table t;
0 rows inserted/updated/deleted
-ij> -- DERBY-2008
--- test SUBSTR with 2 args with GROUP BY expression
-create table dt (vc varchar(30));
-0 rows inserted/updated/deleted
-ij> insert into dt values ('1928-09-21'), ('1903-12-08');
-2 rows inserted/updated/deleted
-ij> -- ok
-select substr(vc, 3) from dt group by substr(vc, 3);
-1
-------------------------------
-03-12-08
-28-09-21
-ij> select substr(vc, 3, 4) from dt group by substr(vc, 3, 4);
-1
-----
-03-1
-28-0
-ij> -- expect errors
-select substr(vc, 3, 4) from dt group by substr(vc, 3);
-ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
-ij> select substr(vc, 3) from dt group by substr(vc, 3, 4);
-ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
-ij> drop table dt;
-0 rows inserted/updated/deleted
ij>