WHERE boolField = 'true', doesn't autocast (changed behavior)

[FreCap]

Version 1.13

In the previous version, this type of autocasting was supported

POST my_test_ndex_bool/_doc/
{
  "field1": {
    "subFieldA": true,
    "subFieldB": 2
  }
}



POST _opendistro/_sql
{
  "query": """SELECT * FROM
  my_test_ndex_bool as i 
  WHERE 
  i.field1.subFieldA ='false'
    LIMIT 50;"""
}

However in the current one it returns:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "= function expected {[BYTE,BYTE],[SHORT,SHORT],[INTEGER,INTEGER],[LONG,LONG],[FLOAT,FLOAT],[DOUBLE,DOUBLE],[BOOLEAN,BOOLEAN],[STRING,STRING],[TIMESTAMP,TIMESTAMP],[DATE,DATE],[TIME,TIME],[DATETIME,DATETIME],[INTERVAL,INTERVAL],[STRUCT,STRUCT],[ARRAY,ARRAY]}, but get [BOOLEAN,STRING]",
    "type": "ExpressionEvaluationException"
  },
  "status": 503
}

Should we allow auto-casting instead?

[penghuo]

Hi FreCap,

Thanks for reporting this breaking issue.

In the new engine released on 1.13, we don't support type auto casting.
The reason it works in old engine are

1. Elasticsearch support using "true" or "false" to represent boolean value.
   2.The new engine enfore the strict type check which is missing in old engine.

Do you have any suggestion for how to fix this issue? Do you want generic type auto casting or just boolean?

[dai-chen]

@FreCap I double checked ANSI SQL-99 but didn't find implicit casting support for this: https://crate.io/docs/sql-99/en/latest/chapters/09.html#boolean-operations. Although MySQL and PostgrelSQL does support your query (Postgre even supports string 'yes', '1' as TRUE), PostgreSQL doc says "The key words TRUE and FALSE are the preferred (SQL-compliant) method for writing Boolean constants in SQL queries".

As penghuo's comments, we may need to mark this as breaking changes or evaluate if this is very common case to support. Thanks!

[FreCap]

Thanks @dai-chen and @penghuo. IMO handling it like Postgres is probably ideal for most users since it is a less strict behavior but still well recognized.

[dai-chen]

Thanks @dai-chen and @penghuo. IMO handling it like Postgres is probably ideal for most users since it is a less strict behavior but still well recognized.

Yeah, we'll do some evaluation. Meanwhile could you provide us your use case? For example, did you use any BI tool or others that generated this kind of query? Or you just typed it in this way manually? Thanks!

[FreCap]

Yeah, we'll do some evaluation.
Great.

Yes, I have integrations with BI tools (unfortunately cannot disclose further).

[dai-chen]

Yeah, we'll do some evaluation.
Great.

Yes, I have integrations with BI tools (unfortunately cannot disclose further).

Got it. That's also what I was thinking. Will evaluate this changes. Thanks!