_xpack returned sql_illegal_argument_exception

[jasondu168]

Elasticsearch version (6.3):

Plugins installed: [_xpack]

JVM version (1.8.0_161-b14):

OS version (Linux 3.10.0-327.10.1.el7.x86_64):

Description of the problem including expected versus actual behavior:
Got sql_illegal_argument_exception when getting data from elastic search using _xpack/sql.

uri: http://${host}/_xpack/sql?format=json

SQL:

select created_at, device_id from table1 where  created_at > 1527982201597 AND user_id = '12345678'

Error:

Steps to reproduce:

1. use the code (js) below to post a request.

let options = {
    method: 'POST',
    uri: `http://${hosts}/_xpack/sql?format=json`,
    body: {"query": `select created_at, device_id from table1 where  created_at > 1527982201597 AND user_id = '12345678'`},
    json: true
  };
  return rp(options);

2. Got exception in the result.
   The created_at is date type and 1527982201597 is milliseconds.

Provide logs (if relevant):

{ error:
   { root_cause: [ [Object] ],
     type: 'sql_illegal_argument_exception',
     reason: '[1527982201597] out of [Int] range' },
    status: 500
 } 

[elasticmachine]

Pinging @elastic/es-search-aggs

[javanna]

Thoughts @costin and team? Seems like some field should be a long rather than an int, but just a wild guess.

[costin]

Needs more investigation to figure out the cause. Looks likely to be a bug in parsing which doesn't multiple integer types to find the right size.

[costin]

@jdu-mydevices Thanks for the (clear) bug report.
I've been able to reproduce the issue and created a PR for it (#31777) which will track when and in what versions it will be pushed.

Thanks!