support string to number coercion for mixed math operations

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/branches/exprs@1083 f203690c-595d-4dc9-a70b-905162fa7fd2

3 files changed, 100 insertions, 2 deletions

diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java
index e456e8b..227b3c8 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java
@@ -543,7 +543,8 @@ public class BuiltinOperators
   protected static int nonNullCompareTo(
       Value param1, Value param2)
   {
-    Value.Type compareType = getGeneralMathTypePrecedence(param1, param2);
+    // note that comparison does not do string to num coercion
+    Value.Type compareType = getGeneralMathTypePrecedence(param1, param2, false);
 
     switch(compareType) {
     case STRING:
@@ -641,6 +642,15 @@ public class BuiltinOperators
     }
 
     if((t1 == Value.Type.STRING) || (t2 == Value.Type.STRING)) {
+
+      // see if this is mixed string/numeric and the string can be coerced to
+      // a number
+      Value.Type numericType = coerceStringToNumeric(param1, param2);
+      if(numericType != null) {
+        // string can be coerced to number
+        return numericType;
+      }
+
       // string always wins
       return Value.Type.STRING;
     }
@@ -666,6 +676,12 @@ public class BuiltinOperators
   private static Value.Type getGeneralMathTypePrecedence(
       Value param1, Value param2)
   {
+    return getGeneralMathTypePrecedence(param1, param2, true);
+  }
+
+  private static Value.Type getGeneralMathTypePrecedence(
+      Value param1, Value param2, boolean allowCoerceToNum)
+  {
     Value.Type t1 = param1.getType();
     Value.Type t2 = param2.getType();
 
@@ -681,6 +697,17 @@ public class BuiltinOperators
     }
 
     if((t1 == Value.Type.STRING) || (t2 == Value.Type.STRING)) {
+
+      if(allowCoerceToNum) {
+        // see if this is mixed string/numeric and the string can be coerced
+        // to a number
+        Value.Type numericType = coerceStringToNumeric(param1, param2);
+        if(numericType != null) {
+          // string can be coerced to number
+          return numericType;
+        }
+      }
+
       // string always wins
       return Value.Type.STRING;
     }
@@ -694,6 +721,34 @@ public class BuiltinOperators
     return max(t1.getPreferredFPType(), t2.getPreferredFPType());
   }
 
+  private static Value.Type coerceStringToNumeric(Value param1, Value param2) {
+    Value.Type t1 = param1.getType();
+    Value.Type t2 = param2.getType();
+
+    Value.Type numericType = null;
+    Value strParam = null;
+    if(t1.isNumeric()) {
+      numericType = t1;
+      strParam = param2;
+    } else if(t2.isNumeric()) {
+      numericType = t2;
+      strParam = param1;
+    } else {
+      // no numeric type involved
+      return null;
+    }
+
+    try {
+      // see if string can be coerced to a number
+      strParam.getAsBigDecimal();
+      return numericType;
+    } catch(NumberFormatException ignored) {
+      // not a number
+    }
+
+    return null;
+  }
+
   private static Value.Type max(Value.Type t1, Value.Type t2) {
     return ((t1.compareTo(t2) > 0) ? t1 : t2);
   }
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/Expressionator.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/Expressionator.java
index f92ad8c..7f3a7fb 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/Expressionator.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/Expressionator.java
@@ -1877,8 +1877,14 @@ public class Expressionator
         return null;
       }
 
+      Value.Type resultType = ctx.getResultType();
+      if(resultType == null) {
+        // return as "native" type
+        return val.get();
+      }
+
       // FIXME possibly do some type coercion.  are there conversions here which don't work elsewhere? (string -> date, string -> number)?
-      switch(ctx.getResultType()) {
+      switch(resultType) {
       case STRING:
         return val.getAsString();
       case DATE:
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java
index ac3688d..6133139 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java
@@ -16,13 +16,18 @@ limitations under the License.
 
 package com.healthmarketscience.jackcess.impl.expr;
 
+import java.math.BigDecimal;
+
 /**
  *
  * @author James Ahlborn
  */
 public class StringValue extends BaseValue
 {
+  private static final Object NOT_A_NUMBER = new Object();
+
   private final String _val;
+  private Object _num;
 
   public StringValue(String val) 
   {
@@ -47,4 +52,36 @@ public class StringValue extends BaseValue
   public String getAsString() {
     return _val;
   }
+
+  @Override
+  public Long getAsLong() {
+    return getNumber().longValue();
+  }
+
+  @Override
+  public Double getAsDouble() {
+    return getNumber().doubleValue();
+  }
+
+  @Override
+  public BigDecimal getAsBigDecimal() {
+    return getNumber();
+  }
+
+  protected BigDecimal getNumber() {
+    if(_num instanceof BigDecimal) {
+      return (BigDecimal)_num;
+    }
+    if(_num == null) {
+      // see if it is parseable as a number
+      try {
+        _num = new BigDecimal(_val);
+        return (BigDecimal)_num;
+      } catch(NumberFormatException nfe) {
+        _num = NOT_A_NUMBER;
+        throw nfe;
+      }
+    }
+    throw new NumberFormatException();
+  }
 }