[BUG] PPL query against timestamp field in LogExplorer throws mapping exception

[engechas]

What is the bug?
Creating a materialized view with direct query creates an index to store the materialized view. When there is a timestamp in the data, the index throws an exception when querying it with PPL in the LogExplorer

{
  "error": {
    "reason": "Error occurred in OpenSearch engine: all shards failed",
    "details": "Shard[0]: OpenSearchParseException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]: [failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]]; nested: IllegalArgumentException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]; nested: DateTimeParseException[Text '460783082753' could not be parsed at index 0];\nShard[1]: OpenSearchParseException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]: [failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]]; nested: IllegalArgumentException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]; nested: DateTimeParseException[Text '460783082753' could not be parsed at index 0];\nShard[2]: OpenSearchParseException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]: [failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]]; nested: IllegalArgumentException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]; nested: DateTimeParseException[Text '460783082753' could not be parsed at index 0];\nShard[3]: OpenSearchParseException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]: [failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]]; nested: IllegalArgumentException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]; nested: DateTimeParseException[Text '460783082753' could not be parsed at index 0];\nShard[4]: OpenSearchParseException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]: [failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]]; nested: IllegalArgumentException[failed to parse date field [460783082753] with format [strict_date_optional_time_nanos]]; nested: DateTimeParseException[Text '460783082753' could not be parsed at index 0];\n\nFor more details, please send request for Json format to see the raw response from OpenSearch engine.",
    "type": "SearchPhaseExecutionException"
  },
  "status": 400
}

The index is queryable with SQL in the QueryWorkbench and with DSL in Discover.

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Create a materialized view against a table with a timestamp field
2. Navigate to the LogExplorer and attempt to query the index with

source = <mv index name> | head 10

What is the expected behavior?
The index should be queryable in the LogExplorer since it is queryable with SQL in the QueryWorkbench and with DSL in discover

What is your host/environment?

• Version 2.13

Do you have any screenshots?
If applicable, add screenshots to help explain your problem.

Do you have any additional context?
Index mappings:

{
      "_meta": {
        "latestId": "ZmxpbnRfdmFsaWRhdGlvbl9hbWF6b25fc2VjdXJpdHlfbGFrZV9nbHVlX2RiX2V1X3dlc3RfMV9hbWF6b25fc2VjdXJpdHlfbGFrZV90YWJsZV9ldV93ZXN0XzFfbGFtYmRhX2V4ZWN1dGlvbl8yXzBfX2YzM2ExODc1MGJlM19fbXZpZXc=",
        "kind": "mv",
        "indexedColumns": [
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.arn"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.userName"
          },
          {
            "columnType": "timestamp",
            "columnName": "@timestamp"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.eventId"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.sharedEventId"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.eventName"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.tlsDetailscipher_suite"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.tlsDetails.tls_version"
          },
          {
            "columnType": "string",
            "columnName": "errorMessage"
          },
          {
            "columnType": "bigint",
            "columnName": "aws.cloudtrail.recipientAccountId"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.accountId"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.principalId"
          },
          {
            "columnType": "boolean",
            "columnName": "aws.cloudtrail.userIdentity.sessionContext.attributes.mfaAuthenticated"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.readOnly"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.awsRegion"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.requestParameter"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.accountId"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.userName"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.eventType"
          },
          {
            "columnType": "string",
            "columnName": "errorCode"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.type"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.accessKeyId"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.vpcEndpointId"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.eventCategory"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.principalId"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.type"
          },
          {
            "columnType": "timestamp",
            "columnName": "aws.cloudtrail.userIdentity.sessionContext.attributes.creationDate"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.sourceIPAddress"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.invokedBy"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userAgent"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.apiVersion"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.responseElements"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.additionalEventData"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.tlsDetailsclient_provided_host_header"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.requestId"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.sessionContext.ec2RoleDelivery"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.eventVersion"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.eventSource"
          },
          {
            "columnType": "array<struct<uid:string,owner:struct<account:struct<uid:string>>,type:string>>",
            "columnName": "aws.cloudtrail.resources"
          },
          {
            "columnType": "string",
            "columnName": "aws.cloudtrail.userIdentity.arn"
          }
        ],
        "name": "validation.amazon_security_lake_glue_db_eu_west_1.amazon_security_lake_table_eu_west_1_lambda_execution_2_0__f33a18750be3__mview",
        "options": {
          "auto_refresh": "true",
          "refresh_interval": "15 Minute",
          "incremental_refresh": "false",
          "checkpoint_location": "s3://aws-security-data-lake-eu-west-1-iir8fucjvzbzxz6o2npqpvfjzd8xgn/fixedcheckpoint/validation-amazon_security_lake_table_eu_west_1_lambda_execution_2_0-7b15e07d-ade6-4cb5-b923-a85dcd74083a",
          "watermark_delay": "1 Minute",
          "extra_options": """{ "validation.amazon_security_lake_glue_db_eu_west_1.amazon_security_lake_table_eu_west_1_lambda_execution_2_0": { "maxFilesPerTrigger": "10" }}"""
        },
        "source": "SELECT CAST(IFNULL(actor.user.type, 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.type`, CAST(IFNULL(actor.user.uid_alt, 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.principalId`, CAST(IFNULL(actor.user.uid, 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.arn`, CAST(IFNULL(actor.user.account.uid, 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.accountId`, CAST(IFNULL(actor.invoked_by, 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.invokedBy`, CAST(IFNULL(actor.user.credential_uid, 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.accessKeyId`, CAST(IFNULL(actor.user.name, 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.userName`, CAST(IFNULL(actor.session.is_mfa, false) AS BOOLEAN) AS `aws.cloudtrail.userIdentity.sessionContext.attributes.mfaAuthenticated`, CAST( actor.session.created_time_dt AS TIMESTAMP) AS `aws.cloudtrail.userIdentity.sessionContext.attributes.creationDate`, CAST(IFNULL(unmapped['userIdentity.sessionContext.sessionIssuer.type'], 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.type`, CAST(IFNULL(unmapped['userIdentity.sessionContext.sessionIssuer.principalId'], 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.principalId`, CAST(IFNULL(actor.session.issuer, 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.arn`, CAST(IFNULL(unmapped['userIdentity.sessionContext.sessionIssuer.accountId'], 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.accountId`, CAST(IFNULL(unmapped['userIdentity.sessionContext.sessionIssuer.userName'], 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.sessionContext.sessionIssuer.userName`, CAST(IFNULL(unmapped['userIdentity.sessionContext.ec2RoleDelivery'], 'Unknown') AS STRING) AS `aws.cloudtrail.userIdentity.sessionContext.ec2RoleDelivery`, CAST(IFNULL(metadata.product.version, 'Unknown') AS STRING) AS `aws.cloudtrail.eventVersion`, CAST(time_dt AS TIMESTAMP) AS `@timestamp`, CAST(IFNULL(api.service.name, 'Unknown') AS STRING) AS `aws.cloudtrail.eventSource`, CAST(IFNULL(api.operation, 'Unknown') AS STRING) AS `aws.cloudtrail.eventName`, CAST(IFNULL(metadata.product.feature.name, 'Unknown') AS STRING) AS `aws.cloudtrail.eventCategory`, CAST(IFNULL(metadata.event_code, 'Unknown') AS STRING) AS `aws.cloudtrail.eventType`, CAST(IFNULL(metadata.uid, 'Unknown') AS STRING) AS `aws.cloudtrail.eventId`, CAST(IFNULL(cloud.region, 'Unknown') AS STRING) AS `aws.cloudtrail.awsRegion`, CAST(IFNULL(src_endpoint.ip, '0.0.0.0') AS STRING) AS `aws.cloudtrail.sourceIPAddress`, CAST(IFNULL(http_request.user_agent, 'Unknown') AS STRING) AS `aws.cloudtrail.userAgent`, CAST(IFNULL(api.response.error, 'Unknown') AS STRING) AS `errorCode`, CAST(IFNULL(api.response.message, 'Unknown') AS STRING) AS `errorMessage`, CAST(IFNULL(api.request.data, 'Unknown') AS STRING) AS `aws.cloudtrail.requestParameter`, CAST(IFNULL(api.response.data, 'Unknown') AS STRING) AS `aws.cloudtrail.responseElements`, CAST(IFNULL(dst_endpoint.svc_name, 'Unknown') AS STRING) AS `aws.cloudtrail.additionalEventData`, CAST(IFNULL(api.request.uid, 'Unknown') AS STRING) AS `aws.cloudtrail.requestId`, resources AS `aws.cloudtrail.resources`, CAST(IFNULL(api.version, 'Unknown') AS STRING) AS `aws.cloudtrail.apiVersion`, CAST(IFNULL(unmapped['readOnly'], 'Unknown') AS STRING) AS `aws.cloudtrail.readOnly`, CAST(IFNULL(unmapped['recipientAccountId'], 0) AS LONG) AS `aws.cloudtrail.recipientAccountId`, CAST(IFNULL(unmapped['sharedEventID'], 'Unknown') AS STRING) AS `aws.cloudtrail.sharedEventId`, CAST(IFNULL(src_endpoint.uid, 'Unknown') AS STRING) AS `aws.cloudtrail.vpcEndpointId`, CAST(IFNULL(unmapped['tlsDetails.tlsVersion'], 'Unknown') AS STRING) AS `aws.cloudtrail.tlsDetails.tls_version`, CAST(IFNULL(unmapped['tlsDetails.cipherSuite'], 'Unknown') AS STRING) AS `aws.cloudtrail.tlsDetailscipher_suite`, CAST(IFNULL(unmapped['tlsDetails.clientProvidedHostHeader'], 'Unknown') AS STRING) AS `aws.cloudtrail.tlsDetailsclient_provided_host_header` FROM validation.amazon_security_lake_glue_db_eu_west_1.amazon_security_lake_table_eu_west_1_lambda_execution_2_0",
        "version": "0.4.1",
        "properties": {
          "env": {
            "SERVERLESS_EMR_VIRTUAL_CLUSTER_ID": "00flapptvjlik70p",
            "SERVERLESS_EMR_JOB_ID": "00fldt0pq3tpl00r"
          }
        }
      },
      "properties": {
        "@timestamp": {
          "type": "date",
          "format": "strict_date_optional_time_nanos"
        },
        "aws": {
          "properties": {
            "cloudtrail": {
              "properties": {
                "additionalEventData": {
                  "type": "keyword"
                },
                "apiVersion": {
                  "type": "keyword"
                },
                "awsRegion": {
                  "type": "keyword"
                },
                "eventCategory": {
                  "type": "keyword"
                },
                "eventId": {
                  "type": "keyword"
                },
                "eventName": {
                  "type": "keyword"
                },
                "eventSource": {
                  "type": "keyword"
                },
                "eventType": {
                  "type": "keyword"
                },
                "eventVersion": {
                  "type": "keyword"
                },
                "readOnly": {
                  "type": "keyword"
                },
                "recipientAccountId": {
                  "type": "long"
                },
                "requestId": {
                  "type": "keyword"
                },
                "requestParameter": {
                  "type": "keyword"
                },
                "resources": {
                  "properties": {
                    "owner": {
                      "properties": {
                        "account": {
                          "properties": {
                            "uid": {
                              "type": "keyword"
                            }
                          }
                        }
                      }
                    },
                    "type": {
                      "type": "keyword"
                    },
                    "uid": {
                      "type": "keyword"
                    }
                  }
                },
                "responseElements": {
                  "type": "keyword"
                },
                "sharedEventId": {
                  "type": "keyword"
                },
                "sourceIPAddress": {
                  "type": "keyword"
                },
                "tlsDetails": {
                  "properties": {
                    "tls_version": {
                      "type": "keyword"
                    }
                  }
                },
                "tlsDetailscipher_suite": {
                  "type": "keyword"
                },
                "tlsDetailsclient_provided_host_header": {
                  "type": "keyword"
                },
                "userAgent": {
                  "type": "keyword"
                },
                "userIdentity": {
                  "properties": {
                    "accessKeyId": {
                      "type": "keyword"
                    },
                    "accountId": {
                      "type": "keyword"
                    },
                    "arn": {
                      "type": "keyword"
                    },
                    "invokedBy": {
                      "type": "keyword"
                    },
                    "principalId": {
                      "type": "keyword"
                    },
                    "sessionContext": {
                      "properties": {
                        "attributes": {
                          "properties": {
                            "creationDate": {
                              "type": "date",
                              "format": "strict_date_optional_time_nanos"
                            },
                            "mfaAuthenticated": {
                              "type": "boolean"
                            }
                          }
                        },
                        "ec2RoleDelivery": {
                          "type": "keyword"
                        },
                        "sessionIssuer": {
                          "properties": {
                            "accountId": {
                              "type": "keyword"
                            },
                            "arn": {
                              "type": "keyword"
                            },
                            "principalId": {
                              "type": "keyword"
                            },
                            "type": {
                              "type": "keyword"
                            },
                            "userName": {
                              "type": "keyword"
                            }
                          }
                        }
                      }
                    },
                    "type": {
                      "type": "keyword"
                    },
                    "userName": {
                      "type": "keyword"
                    }
                  }
                },
                "vpcEndpointId": {
                  "type": "keyword"
                }
              }
            }
          }
        },
        "errorCode": {
          "type": "keyword"
        },
        "errorMessage": {
          "type": "keyword"
        }
      }
    }

[dblock]

[Catch All Triage - 1, 2, 3, 4, 5]