[PATCH] SQLExpression.java

[Dave Polito <da...@planetcad.com>]

I think I'm target with this one.  I'm most familiar with Oracle, and you
need to handle NULLs differently in the where clause.

Examples;

Invalid			Valid
WHERE foo = NULL		WHERE foo IS NULL
WHERE foo != NULL		WHERE foo IS NOT NULL

INSERT INTO foo (column1, column2) values ('WITH NULL',NULL);
INSERT INTO foo (column1, column2) values ('WITH EMPTY STRING','');

SELECT * FROM foo WHERE column2 = NULL;

0 rows returned

SELECT * FROM foo WHERE column2 = '';

column1		column2
-------------------------
WITH EMPTY STRING	
1 row returned

SELECT * FROM foo WHERE column2 IS NULL;

column1		column2
-------------------------
WITH NULL		NULL	
1 row returned

I know this is the same with MySql, Postgresql, Oracle, and SQLServer.  So
I've attached a patch to fix this to the valid behavior.  So any Object that
is null, or any ObjectKey that's value is null, will have it's where clause
fixed to match the correct syntax.

Please let me know if this breaks anything else.

Dave Polito

Comment:  Fixed the handling of nulls in where clauses.  The where clause
will now use IS NULL and IS NOT NULL.

Index: SqlExpression.java
===================================================================
RCS file:
/home/cvspublic/jakarta-turbine/src/java/org/apache/turbine/util/db/SqlExpre
ssion.java,v
retrieving revision 1.14
diff -u -r1.14 SqlExpression.java
--- SqlExpression.java	2001/04/07 08:12:03	1.14
+++ SqlExpression.java	2001/04/17 23:01:06
@@ -63,6 +63,7 @@
 import org.apache.turbine.util.db.adapter.DB;
 import org.apache.turbine.om.StringKey;
 import org.apache.turbine.om.DateKey;
+import org.apache.turbine.om.ObjectKey;
 
 /**
  * This class represents a part of an SQL query found in the
<code>WHERE</code>
@@ -240,12 +241,41 @@
     {
         // Allow null criteria
         // This will result in queries like
-        // select * from table where field = null
         // insert into table (name, parent) values ('x', null);
         //
+        
+        /* Check to see if the criteria is an ObjectKey
+         * and if the value of that ObjectKey is null.
+         * In that case, criteria should be null.
+         */
+        
+        if (criteria != null && criteria instanceof ObjectKey)
+        {
+            if (((ObjectKey)criteria).getValue() == null)
+            {
+                criteria = null;
+            }
+        }
+        /*  If the criteria is null, check to see comparison
+         *  is an =, <>, or !=.  If so, replace the comparison
+         *  with the proper IS or IS NOT.
+         */
+        
         if (criteria == null)
         {
-           criteria = "null";
+            criteria = "null";
+            if (comparison.equals(Criteria.EQUAL))
+            {
+                comparison = " IS ";
+            }
+            else if (comparison.equals(Criteria.NOT_EQUAL))
+            {
+                comparison = " IS NOT ";
+            }
+            else if (comparison.equals(Criteria.ALT_NOT_EQUAL))
+            {
+                comparison = " IS NOT ";
+            }
         }
         else 
         {

---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org