Fixes bug 29732: Get rid of coalesce(to_char()) when possible to improve query
authorAugusto Mauch <augusto.mauch@openbravo.com>
Mon, 11 May 2015 13:18:57 +0200
changeset 27070 e684c73bb502
parent 27069 aa165ef81792
child 27071 e65e148f179c
Fixes bug 29732: Get rid of coalesce(to_char()) when possible to improve query

We sometimes use the coalesce(to_char(value),'') functions to prevent passing null values in a where clause, replacing the null value with an empty string. The problem is that if those functions are used, then it will not be possible benefit from the indexes defined in the database.

In this issue we have made a distintion between mandatory and non-mandatory columns. Mandatory columns will no longer use the coalesce and to_char functions in the AdvancedQueryBuilder.createIdentifierLeftClause method. We can do that safely as it is not possible that those columns have null values.
modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
--- a/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java	Fri May 08 12:39:56 2015 +0200
+++ b/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java	Mon May 11 13:18:57 2015 +0200
@@ -1567,7 +1567,16 @@
               + OBContext.getOBContext().getLanguage().getLanguage() + "')), to_char("
               + replaceValueWithJoins(prefix + prop.getName()) + "), '')");
         } else {
-          sb.append("COALESCE(to_char(" + replaceValueWithJoins(prefix + prop.getName()) + "),'')");
+          if (prop.isMandatory()) {
+            // if the property is mandatory there is no need to use coalesce to replace a
+            // possible null value with an empty string
+            // getting rid of the coalesce and to_char functions allow under certain circumstances
+            // to use indexes defined on that property
+            sb.append(replaceValueWithJoins(prefix + prop.getName()));
+          } else {
+            sb.append("COALESCE(to_char(" + replaceValueWithJoins(prefix + prop.getName())
+                + "),'')");
+          }
         }
 
       } else {