Changeset 2804

Timestamp:

Oct 25, 2006, 2:54:34 PM (17 years ago)

Author:

Nicklas Nordborg

Message:

Fixed problem with databases (ie. Postgres) that throws exceptions when invalid values are passed
to certain functions. For example, division by zero and taking the logarithm of a negative number.
This caused the entire query to fail even if only one row had the error. The solution now returns
null just as MySQL does.

Location:

trunk/src/core/net/sf/basedb/core

Files:

• dbengine/AbstractDbEngine.java (modified) (1 diff)
• dbengine/DbEngine.java (modified) (1 diff)
• dbengine/PostgresDbEngine.java (modified) (1 diff)
• query/DivideExpression.java (modified) (2 diffs)
• query/LogExpression.java (modified) (1 diff)
• query/SqrtExpression.java (modified) (3 diffs)

trunk/src/core/net/sf/basedb/core/dbengine/AbstractDbEngine.java

@@ -86 +86 @@
   }
   /**
+    Returns FALSE.
+  */
+  public boolean checkForInvalidNumberOperation()
+  {
+    return false;
+  }
+  /**
     Return <code>LN(&lt;value&gt;)</code>.
   */

trunk/src/core/net/sf/basedb/core/dbengine/DbEngine.java

@@ -179 +179 @@
   
   /**
+    If we need to check for invalid arguments to numerical
+    functions to avoid exceptions from the database. Postgres,
+    for example, throws exceptions if trying to divide by zero,
+    while MySQL returns null. To get the same behavior we need
+    to add a <code>CASE...WHEN</code> statement to the query for
+    Postgres. Example:
+    <pre class="code">
+CASE WHEN denominator = 0 THEN null ELSE numerator / denominator
+</pre>
+    @return TRUE if we need to check the numbers, FALSE otherwise
+  */
+  public boolean checkForInvalidNumberOperation();
+  
+  /**
     Get the function call that takes the natural logarithm
     of a value. For example: <code>LN(value)</code>

trunk/src/core/net/sf/basedb/core/dbengine/PostgresDbEngine.java

@@ -179 +179 @@
     return false;
   }
+  /**
+    Returns TRUE.
+  */
+  public boolean checkForInvalidNumberOperation()
+  {
+    return true;
+  }
   // -------------------------------------------
   

trunk/src/core/net/sf/basedb/core/query/DivideExpression.java

@@ -29 +29 @@
 import net.sf.basedb.core.DbControl;
 import net.sf.basedb.core.BaseException;
+import net.sf.basedb.core.HibernateUtil;
 
 /**
@@ -59 +60 @@
     throws BaseException
   {
-    return "(" + e1.toQl(query, dc) + " / " + e2.toQl(query, dc) + ")";
+    String numerator = e1.toQl(query, dc);
+    String denominator = e2.toQl(query, dc);
+    if (HibernateUtil.getDbEngine().checkForInvalidNumberOperation())
+    {
+      return "(CASE WHEN " + denominator + "=0 THEN null ELSE " + numerator + "/" + denominator + " END)";
+    }
+    else
+    {
+      return "(" + numerator + " / " + denominator + ")";
+    }
   }
   /**

trunk/src/core/net/sf/basedb/core/query/LogExpression.java

@@ -64 +64 @@
   {
     // Get the log function that is returning the natural logarithm
-    String result = HibernateUtil.getDbEngine().ln(e1.toQl(query, dc));
+    String argument = e1.toQl(query, dc);
+    String ln = HibernateUtil.getDbEngine().ln(argument);
+    if (HibernateUtil.getDbEngine().checkForInvalidNumberOperation())
+    {
+      ln = "(CASE WHEN " + argument + "<=0 THEN null ELSE " + ln + " END)";
+    }
     if (base != Math.E)
     {
       double logBase = Math.log(base);
-      result = "(" + result + " / " + logBase + ")";
+      ln = "(" + ln + " / " + logBase + ")";
     }
-    return result;
+    return ln;
   }
   /**

trunk/src/core/net/sf/basedb/core/query/SqrtExpression.java

@@ -24 +24 @@
 import net.sf.basedb.core.BaseException;
 import net.sf.basedb.core.DbControl;
+import net.sf.basedb.core.HibernateUtil;
 
 public class SqrtExpression
@@ -40 +41 @@
     -------------------------------------------
   */
-  public String toQl(Query query, DbControl dc) throws BaseException
+  public String toQl(Query query, DbControl dc) 
+    throws BaseException
   {
-    return "SQRT("+e1.toQl(query, dc)+")";
+    String argument = e1.toQl(query, dc);
+    String sqrt = "SQRT("+argument+")";
+    if (HibernateUtil.getDbEngine().checkForInvalidNumberOperation())
+    {
+      sqrt = "(CASE WHEN " + argument + "<0 THEN null ELSE " + sqrt + " END)";
+    }
+    return sqrt;
   }
   /**
@@ -59 +67 @@
   public String toString()
   {
-    return "ln(" + e1 + ")";
+    return "sqrt(" + e1 + ")";
   }
   // -------------------------------------------