[BUG] SELECT in SQL returns 0 rather than expected data

[adam-Oracle]

Describe the bug
A SELECT statement using = returns 0 when it should return a number. A SELECT for the same data using IN returns the correct data

To Reproduce

PUT the_test
{
  "settings": {
    "index.number_of_shards": 1,
    "index.number_of_replicas": 1
  },
  "mappings" : {
    "properties" : {
      "a" : {
        "type" : "keyword"
      },
      "b" : {
        "type" : "boolean"
      },
      "c" : {
        "properties" : {
          "ca" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "cb" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "cc" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          }
        }
      },
      "d" : {
        "type" : "keyword"
      },
      "e" : {
        "type" : "keyword"
      },
      "f" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
      },
      "g" : {
        "type" : "date"
      },
      "h" : {
        "type" : "keyword"
      },
      "i" : {
        "type" : "keyword"
      },
      "j" : {
        "type" : "boolean"
      },
      "k" : {
        "type" : "integer"
      },
      "l" : {
        "properties" : {
          "la" : {
            "type" : "long"
          },
          "lb" : {
            "type" : "keyword"
          },
          "lc" : {
            "type" : "integer"
          }
        }
      },
      "m" : {
        "type" : "date"
      },
      "n" : {
        "type" : "long"
      },
      "o" : {
        "type" : "keyword"
      },
      "p" : {
        "type" : "long"
      },
      "q" : {
        "properties" : {
          "qa" : {
            "properties" : {
              "qaa" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qab" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qac" : {
                "type" : "float"
              },
              "qad" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qb" : {
            "properties" : {
              "qba" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qbb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qc" : {
            "properties" : {
              "qca" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qcb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qcc" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qcd" : {
                "properties" : {
                  "qcda" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qcdb" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qcdc" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qcdd" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  }
                }
              },
              "qce" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qcf" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qd" : {
            "properties" : {
              "qda" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qdb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qdc" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qdd" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qe" : {
            "properties" : {
              "qea" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qeb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qec" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qf" : {
            "properties" : {
              "qfa" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qfb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qfc" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qfd" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qfe" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qg" : {
            "type" : "object"
          },
          "qh" : {
            "properties" : {
              "qha" : {
                "type" : "date"
              },
              "qhb" : {
                "properties" : {
                  "qhba" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qhbb" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qhbc" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  }
                }
              }
            }
          },
          "qi" : {
            "type" : "object"
          },
          "qj" : {
            "properties" : {
              "qja" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qjb" : {
                "properties" : {
                  "qjba" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qjbb" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qjbc" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qjbd" : {
                    "type" : "boolean"
                  }
                }
              }
            }
          },
          "qk" : {
            "properties" : {
              "qka" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qkb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qkc" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "ql" : {
            "type" : "object"
          }
        }
      },
      "r" : {
        "type" : "keyword"
      },
      "s" : {
        "properties" : {
          "sa" : {
            "properties" : {
              "saa" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          }
        }
      },
      "t" : {
        "type" : "keyword"
      },
      "u" : {
        "type" : "integer"
      },
      "v" : {
        "type" : "long"
      },
      "w" : {
        "type" : "keyword"
      },
      "x" : {
        "properties" : {
          "xa" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "xb" : {
            "type" : "date"
          },
          "xc" : {
            "type" : "date"
          },
          "xd" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          }
        }
      },
      "y" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
      },
      "z" : {
        "type" : "text"
      },
      "aa" : {
        "type" : "text"
      },
      "bb" : {
        "type" : "date"
      },
      "cc" : {
        "type" : "integer"
      }
    }
  }
}

Next add the following document

POST /the_test/_doc/999
{
  "aa" : "",
  "v" : "0",
  "z" : "",
  "o" : "PROFILE",
  "n" : "12345",
  "i" : "999",
  "j" : false,
  "g" : "2020-10-11T03:37:42.855Z",
  "u" : 1,
  "b" : true,
  "r" : "AB12345=T",
  "p" : "0",
  "d" : "",
  "h" : "TEST",
  "x" : [
  {
    "xa" : "0",
    "xc" : "2020-11-11T03:37:42.855Z",
    "xb" : "2020-11-11T03:37:42.855Z",
    "xd" : "Creating metadata record"
  }
  ],
  "t" : "e9a4c0a9-5ccc-427b-9e2c-bf505794191e",
  "e" : "ABC",
  "k" : 0,
  "a" : "SYNC",
  "c" : { },
  "m" : "2020-11-11T03:37:42.855Z",
  "bb" : 1605065862855,
  "l" : {
    "lb" : "",
    "lc" : 567,
    "la" : "987654"
  },
  "w" : "COMPLETE"
}

Expected behavior
Both of the following SQL queries should now return the same thing, the number 12345

select n from the_test where n = 12345;
select n from the_test where n in (12345);

Plugins
Please list all plugins currently enabled.

Screenshots
If applicable, add screenshots to help explain your problem.

Host/Environment (please complete the following information):

• ES 7.10.2
• opendistro-sql 1.13.2.0

Additional context
Add any other context about the problem here.

[stockholmux]

Confirmed in opensearch-sql v1.1.0.0

[stockholmux]

Context: https://discuss.opendistrocommunity.dev/t/missing-data-two-sql-queries-that-should-return-identical-responses-but-dont/7195/8

[chloe-zh]

Hi @adam-Oracle thanks for reporting the issue, i just reproduced the error and found the cause. When indexing the data, you defaults them as numeric type (long and integer etc), but input with string values. Looks like the implicit conversion in between does not work well, so the output is incorrect. We will look deeper into the issue.

For you workaround, I would suggest you index the data values with consistent data type in the mapping, for example:

POST /the_test/_doc/999
{
    "n" : 12345
}

Query in SQL plugin: select n from the_test where n = 12345

Result:

{
  "schema": [
    {
      "name": "n",
      "type": "long"
    }
  ],
  "datarows": [
    [
      12345
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

[adam-Oracle]

Just to clarify you said, "Looks like the implicit conversion in between does not work well, so the output is incorrect.", but the output from the query using 'between' was the correct one?!?

[acarbonetto]

OpenSearch is accepting the string value when the mapping is defined as a numeric (long). SQL doesn't handle this string value properly, and returns 0. It would be better to 'error out' or return 'null'. For data types, when the value isn't formatted correctly, we 'error out'.

Edge case: if you give a value mapped as a keyword an integer, OpenSearch also accepts that. SQL plugin behaves in a similar way, except that the implicit cast from integer to string will work in all cases.

[Yury-Fridlyand]

Probably, parsing and using numeric_detection could help to avoid this issue.

[GumpacG]

sql/opensearch/src/main/java/org/opensearch/sql/opensearch/data/utils/OpenSearchJsonContent.java

Line 163 in f4883c9

private Double extractDoubleValue(JsonNode node) {

Could be used as reference for a possible solution for all number types.