[BUG] PPL and SQL queries produces seemingly incorrect results

[hbrodin]

Describe the bug
When filtering the results for a specific manufacturer in either PPL or SQL-mode it seems like results not having that manufacturer is returned

To Reproduce

Steps to reproduce the behavior:

1. Login anonymous in the future playground.
2. Select the OTEL demo environment.
3. Select Discover from the menu on the left hand side
4. In the data source selection dropdown, select opensearch*ecomm*
5. Using PPL query mode, set the time filter to absolute mode starting at Oct 20, 2024 @ 00:00:00.00 and ending at Oct 20, 2024 @23:00:00.00 then run source = opensearch*ecomm* | where manufacturer = "Oceanavigations",
6. Notice how not every manufacturer-field has the value Oceanavigations, some have e.g. Low Tide Media.

Expected behavior
When using the specific filter one would expect result to have the manufacturer field set to Oceanavigations.

OpenSearch Version

2.18

Dashboards Version

2.18

Plugins

Alerting, anomaly-detection, asynchronous-search, cross-cluster-replication, custom-codecs, flow-framework, geospatial, index-management, job-scheduler, knn, ml, neural-search, notifications, notifications-core, observability, performance-analyzer, reports-scheduler, security, security-analytics, skills, sql, system-templates, query-insights

Screenshots

N/A

Host/Environment:

• OS: macOS
• Browser and version Google Chrome, version 131.0.6778.205

Additional context
Running the equivalent SQL-query SELECT * from opensearch*ecomm* where manufacturer = "Oceanavigations" shows a similar result where not every manufacturer-field is set to Oceanavigations.
However, running the DQL query manufacturer: Oceanavigations, it can be seen that several documents have multiple manufacturers e.g. manufacturer: Oceanavigations, Low Tide Media.
Peeking in the developer console it can be seen that the results are represented very differently if the query mode is DQL or PPL/SQL. It appears as if PPL/SQL-representations can only handle a single value, whereas DQL can handle an array.

[krisfreedain]

Catch All Triage - 1, 2, 3

[kavilla]

@opensearch-project/admin please transfer this to @opensearch-project/sql to see if this is an issue or not.

[joshuali925]

should be supported by #3095 in 2.19