Error occurs when convert bool to integer

[dai-chen]

Exception is thrown from plugin for a query with SELECT trying to convert a boolean field to integer.

POST _opendistro/_sql
{  
  "query": """
      SELECT CAST(Cancelled AS INT) 
      FROM kibana_sample_data_flights
  """
}

{
  "error": {
    "reason": "Error occurred in Elasticsearch engine: all shards failed",
    "details": """Shard[0]: ScriptException[runtime error]; nested: NumberFormatException[For input string: "false"];

For more details, please send request for Json format to see the raw response from elasticsearch engine.""",
    "type": "SearchPhaseExecutionException"
  },
  "status": 400
}

[dai-chen]

The root cause is the painless script generated is wrong, particularly in Double.parseDouble(doc['Cancelled'].value.toString()).

{
  "from" : 0,
  "size" : 200,
  "_source" : {
    "includes" : [
      "cast_Cancelled"
    ],
    "excludes" : [ ]
  },
  "script_fields" : {
    "cast_Cancelled" : {
      "script" : {
        "source" : "def cast_1 = Double.parseDouble(doc['Cancelled'].value.toString()).intValue();return cast_1;",
        "lang" : "painless"
      },
      "ignore_failure" : false
    }
  }
}

[dai-chen]

The code generating the script above is here:

sql/legacy/src/main/java/com/amazon/opendistroforelasticsearch/sql/legacy/utils/SQLFunctions.java

Line 976 in 893cd18

return String.format("def %s = Double.parseDouble(%s.toString()).intValue()", name, castFieldName);

It seems our implementation always assumes the source type is double type when target is INT/LONG/FLOAT.

MySQL example:

SELECT CAST(TRUE AS SIGNED);

CAST(TRUE AS SIGNED)
-------------------------
1

SELECT CAST(FALSE AS SIGNED);

CAST(FALSE AS SIGNED)
-------------------------
0