# HG changeset patch
# User Augusto Mauch <augusto.mauch@openbravo.com>
# Date 1431343137 -7200
#      Mon May 11 13:18:57 2015 +0200
# Node ID 46e7d9337cdfda8af8bac7d31b8c340ce0e8c1da
# Parent  2e9c64067e97342eb0d944ec91923a29f975eced
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.

diff --git a/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java b/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
+++ b/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
@@ -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 {