[RFC] Better Command-Chain Testing with Index Views

[Swiddis]

Problem Statement

We see a lot of bugs that boil down to "Command A works, command B works, but A + B doesn't work." Recent examples are #5482 and #5483. These show clear testing gaps with combining multiple commands in one query, some of which are easy to catch (e.g. prepending a where clause) but not currently tested.

Current State

We have a lot of integration correctness and performance tests that use specific test indices. These do somewhat thoroughly exercise all individual commands, but these tests rarely involve carefully checking multiple commands. We also don't have great general visibility on whether certain optimization rules are applied, so explain tests are locked in scope to only simple cases or hand-checked complex cases.

Long-Term Goals

• Catch more bugs with common cross-command combinations. For example, I think all commands should work after simple-predicate where clauses, up to some pathological counterexample, since where is very easy to push down.
• Keep it simple: easy to implement, largely reuse our current testing infrastructure without complex introductions like property-based tests.
• Better model real-world use cases: complex commands are rarely used directly, usually there are some pre-filtering steps.

Proposal

I propose implementing two general migrations. Both can be incrementally done, and I believe they're mechanical enough to largely delegate to agents:

1. Introduce Views as additional options for test indices
2. Add optimization annotations to _explain output

Test Views

We currently implement all of our integ tests on top of test indices. The largest indices are TEST_INDEX_ACCOUNT (accounting for 19.4% of all E2E query tests), followed by TEST_INDEX_BANK (15.7%) and TEST_INDEX_STATE_COUNTRY (5.5%). This means that, in order to test any given command with another command, we need to manually work out a query that exercises the new command, as well as its output.

Taking the idea of a View in SQL, we can instead use an approach where we store and reuse query parts as test indices.

Suppose we have this index for tests, with many tests relying on it:

{"a": 1, "b": "cat"}
{"a": 2, "b": "dog"}

Now let's extend it with some fake additional fields and records:

{"a": 1, "b": "cat", "_is_real": true}
{"a": 2, "b": "dog", "_is_real": true}
{"a": 3, "b": "duck", "_is_real": false}
{"a": 4, "b": "goose", "_is_real": false}

And we create a view: source=extended_idx | where _is_real | fields - _is_real

Now, for all tests that use the original idx, we can parametrize these tests: for both idx and idx_is_real_view, we can run the same test and expect the same results.

Optimization Annotations

Test views work for correctness tests as-is, but not optimization tests. A lot of our current optimization tests are based on hardcoding _explain output. To support testing optimizations (e.g. pushdowns) in diverse contexts, we can improve the situation by adding context on applied optimizations in the explain output itself. i.e. current explain output resembles:

sourceBuilder:
  query:
    bool:
      must:
        - range: { "@timestamp": { "gt": "..." } }

We can add a new section for optimizations that includes basic metadata:

sourceBuilder:
  query:
    bool:
      must:
        - range: { "@timestamp": { "gt": "..." } }
_optimization_metadata:
  rules_applied:
    - FilterIndexScanRule (pushed: range on @timestamp)
    - DedupPushdownRule (pushed: composite aggregation)
  rules_attempted:
    - RareTopPushdownRule (failed: no rare/top pattern)

Now any tests that want to validate specific optimizations are applying can do something like assert(plan.contains("pushed: range on @timestamp")).

Approach

To do this incrementally, here's a quick sketch of an approach.

For views:

1. Extend our top 3 test indices with dummy values, courtesy of your friendly neighborhood data generator. Call these EXTENDED_{index_name} or something.
2. Create views which take EXTENDED_... and produce the original index, prioritizing the top indices (mostly accounts and bank). Some candidates for views:
   1. The trivial view (source=index)
   2. WHERE on temporal fields (incredibly common due to OSD's filter behavior)
   3. EVAL -> WHERE chains (simple derived data)
   4. STATS with WHERE
   5. Something involving join
   6. Something involving sort
   7. Something involving rex
3. Create some utils to make it easy to run queries on views instead of indices.
4. Migrate some correctness tests to be parametrized over these views -- can largely be done in bulk by your favorite coding agent, most of these should boil down to changing a string formatting expression. We can also do this incrementally, just prioritizing commands that seem most error-prone (e.g. dedup).

To create these views, we can probably do this with creative use of fields like _is_real, _timestamp, _shard_key, etc. Easy cases can have these extra fields filtered out with fields - _*, harder cases might take more work (e.g. self-join probably takes random fields from both sides).

Incidentally, switching to views that have specific field orderings can help us avoid some historic cases with flaky tests due to field ordering.

For _explain:

1. Implement the core optimization rules annotation system
2. Onboard a couple rules to the annotation
3. Refactor existing explain tests to check for the rules' presence in the annotations (again, can be done in bulk and incrementally).

The _explain tests will be tricky due to all our hardcoding, while we're at it we should try and refactor out these string assertions directly and figure out how to only check specific elements we care about. Seems like good timing.

Alternatives

• Explore something with dynamically generating test cases, building on the work from [RFC] Dynamic Correctness Testing Framework #3220. I do have some work-in-progress experimental code where I revisited the core ideas from that RFC with a new lens, might mess with it more some time.
• Generally revisit and potentially improve our optimization system -- I have another RFC for this on the way, it just needs more research & revision.
• Just add more chain tests manually.

I'm generally in favor of this approach because it's very easy to do and lets us reuse a lot of existing tests with only minimal rework. I do think with that last point we have opportunities to do better (including more queries that model realistic use cases with messy input data), but it's a lot less bang for buck in bulk.

Implementation Discussion

See "Approach."

[anasalkouz]

A where prefix is straight forward case because it just reduces rows -- it doesn't change schemas, introduce nulls, or aggregate. is the same approach will work with such type of command chains?
For example:
| eval | dedup
| head 10 | stats count() by status
| stats | sort | head

[Swiddis]

It's a little less trivial to do with aggregates, but not impossible. An aggregate view for the cat and dog example might take an index like this:

{"a": 1, "b": "cat", "_agg_key": 1}
{"a": 2, "b": "dog", "_agg_key": 2}
{"a": 3, "b": "duck", "_agg_key": 1}
{"a": 4, "b": "goose", "_agg_key": 2}

With the view:

source=extended
| stats first(a) as a, first(b) as b by _agg_key
| fields - _agg_key

Another approach might just duplicate records and undo it with aggregates:

{"a": 1, "b": "cat"}
{"a": 2, "b": "dog"}
{"a": 1, "b": "cat"}
{"a": 2, "b": "dog"}
{"a": 2, "b": "dog"}

source=extended
| stats count() by a, b
| fields - `count()`

or...

source=extended
| dedup a

Similarly for join, we'd introduce a unique _join_key and join the index to itself, selecting fields from each side. Same thing for sort, we can shuffle the rows but add an invisible _sort_key. (We don't necessarily need to introduce a new key for every view, there's probably ways to simplify this per-index)

[Swiddis]

Some of them might be better to do with additional extended indices though. Like we can include rex by taking any string column and embedding it in another column and using rex to extract it back out, but we don't necessarily want to force every view to do this in order to recover that field. Might even be better overall if we just make one index per view, I'm ok to leave that to the implementer based on whatever seems easiest at implementation-time.

[gingeekrishna]

Hi @Swiddis , @anasalkouz

I have created the PR: #5508

It implements the WHERE-prefix view infrastructure from phase 1 of the RFC:

• Extended accounts and bank test datasets with an _is_real flag (real records get true, 3 synthetic records per index get false)
• A sourceView(extendedIndex, query) helper in PPLIntegTestCase that builds the view chain: source=<ext> | where _is_real | fields - _is_real | <query>
• ACCOUNT_EXTENDED and BANK_EXTENDED wired into the Index enum so any test can call loadIndex(Index.ACCOUNT_EXTENDED)
• Proof-of-concept in FieldsCommandIT: testBasicFieldSelection, testMultipleFieldSelection, and testSpecialDataTypes are now @ParameterizedTest running against both the direct index and the WHERE-prefix view with identical assertions.

[Swiddis]

Sweet, thanks!

So now we have groundwork, all that's really left is to onboard some more test suites and poke for issues. We know that there's one known issue with where | dedup that should be catchable with the current version, other issues might come up with more sophisticated views or just applying the where view in more contexts. I can try a few when I get time

[gingeekrishna]

Thanks @Swiddis! Happy to keep expanding coverage. Since where | dedup is a known catchable case, I can onboard DeduplicateCommandIT to the WHERE-prefix view as a next step — that should surface the bug directly. I can also extend a few other command ITs (e.g., SortCommandIT, EvalCommandIT) to build up broader coverage while we're at it.

Let me know if there are specific commands you'd like prioritized, or I'll start with dedup and work outward from there.