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: r468696 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
 testing/org/apache/derbyTesting/functionTests/master/groupBy.out
 testing/org/apache/derbyTesting/functionTests/tests/lang/groupBy.sql
Date: Sat, 28 Oct 2006 16:15:09 -0000
To: derby-commits@db.apache.org
From: bpendleton@apache.org
Message-Id: <20061028161510.36C051A9846@eris.apache.org>

Author: bpendleton
Date: Sat Oct 28 09:15:09 2006
New Revision: 468696

URL: http://svn.apache.org/viewvc?view=rev&rev=468696
Log:
DERBY-2008: NPE with 2-arg SUBSTR call in GROUP BY clause

This patch was contributed by Yip Ng (yipng168@gmail.com)

For SUBSTR function, there can be 2 or 3 arguments, and in the
case of 2-args, the rightOperand of the TernaryOperatorNode will
be null. In its isEquivalent() method, it did not take care of
this case; thus, the NPE.

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

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java?view=diff&rev=468696&r1=468695&r2=468696
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java Sat Oct 28 09:15:09 2006
@@ -921,10 +921,18 @@
     	if (isSameNodeType(o)) 
 	{
 		TernaryOperatorNode other = (TernaryOperatorNode)o;
+		
+			/*
+			 * SUBSTR function can either have 2 or 3 arguments.  In the 
+			 * 2-args case, rightOperand will be null and thus needs 
+			 * additional handling in the equivalence check.
+			 */
     		return (other.methodName.equals(methodName)
 				&& other.receiver.isEquivalent(receiver)
     				&& other.leftOperand.isEquivalent(leftOperand)
-    				&& other.rightOperand.isEquivalent(rightOperand));
+    				&& ( (rightOperand == null && other.rightOperand == null) || 
+    				     (other.rightOperand != null && 
+    				    	other.rightOperand.isEquivalent(rightOperand)) ) );
         }
     	return false;
     }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out?view=diff&rev=468696&r1=468695&r2=468696
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out Sat Oct 28 09:15:09 2006
@@ -636,4 +636,28 @@
 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> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/groupBy.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/groupBy.sql?view=diff&rev=468696&r1=468695&r2=468696
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/groupBy.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/groupBy.sql Sat Oct 28 09:15:09 2006
@@ -360,3 +360,15 @@
 -- 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;