Simple query using date_add

Plugins SQL
1

I have submitted this query and am receiving the null pointer error. We are running Open Distro v7.9.1 which should include 1.11 version of SQL, not sure why I am using this error. If the version I’m working with can’t accept date_add in the simplest way I cannot do more complex things that I really need to.

POST _opendistro/_sql { "query": "SELECT date_add(\"2021-07-12\", INTERVAL 59 second) FROM my-index-*" }

{ "error": { "reason": "Invalid SQL query", "details": "", "type": "NullPointerException" }, "status": 400 }

Can you share what might be wrong?

Thanks,

Ed

2

BUMP – Broken Date functions in AWS Managed Elastic Search??

As Ed points out above, we are running AWS Elasticsearch 7.9 which AWS Support tells us is running OD SQL 1.11.0 … however, we are unable to get the date functions working as documented.

Is anyone else running into problems with AES 7.9 and OD SQL 1.11.0 date functions?

AWS Support is trying to point the finger at the OD SQL team for releasing broken date functions in 1.11.0 which were later fixed in 1.13.0 but I can’t confirm that in the github repo.

Thanks,

jpf

3

@anirudha Thoughts on this one?

4

Thanks for bumping this @searchymcsearchface

As it turns out, AWS ES 7.9 has the “new SQL engine” disabled. Running the following seems to enable it and open up the world of date_functions which were born in 1.11.0 but “hidden” until 1.13.0 when the “new engine” was enabled by default.

PUT _opendistro/_sql/settings
{
  "transient" : {
    "opendistro.sql.engine.new.enabled" : "true"
  }
}

There was a bunch of digging that went into this one … and the keys came from here:

and eventually led me here:

of course YMMV but that’s what we’ve found and what we are going with.

1 Like
5

Hi @Ed_DeCelie @jpf321 , the plugin does not have function date_add, but it has a function adddate with similar grammar. For example:

POST _opendistro/_sql
{
  "query": "select adddate(\"2021-07-12\", INTERVAL 59 second) from kibana_sample_data_flights limit 3"
}

{
  "schema": [
    {
      "name": """adddate("2021-07-12", INTERVAL 59 second)""",
      "type": "datetime"
    }
  ],
  "datarows": [
    [
      "2021-07-12 00:00:59"
    ],
    [
      "2021-07-12 00:00:59"
    ],
    [
      "2021-07-12 00:00:59"
    ]
  ],
  "total": 3,
  "size": 3,
  "status": 200
}

Here is the user manual for this function: sql/functions.rst at main · opensearch-project/sql · GitHub
Thanks!

6

thanks @chloe-zh … but to be clear, AWS ES 7.9 has no date_functions turned on by default until you enable them via the OD SQL settings as I detailed above.

7

thanks @chloe-zh ; @Ed_DeCelie , where did you see the reference or docs to use “date_add” ?

8

@jpf321 the new engine was not turned on by default until the odfe v1.13.0.0 when it turned stable. So only versions no earlier than odfe1.13 has the new engine turned on by default. And 1.13 maps to es version of 7.10.2. If you would like to use the new engine by default, please upgrade the version to 7.10

Related release note:

Related PR: Enable new SQL query engine by dai-chen · Pull Request #989 · opendistro-for-elasticsearch/sql · GitHub

9

Here is the version mappings: Version History - Open Distro Documentation

1 Like
10

Yes thank @chloe-zh … but you can enable the “new engine” within 1.11.0 in 7.9 (AWS ES) with the below (we understand it may not be considered “stable”) :

11

Hi Kyle,

Can I message you? Quick question … not for this thread

Thanks,
Ed

12

This throws an error although the functions do work independently. Trying to dynamically produce dates from the past such as -14 days, -30 days etc.

SELECT adddate(curdate(), INTERVAL -14 DAY) from my-index – throws an error

SELECT adddate(@timestamp, INTERVAL -14 DAY) from my-index – works

Thanks,

Ed