Cache-hit tests fail when concurrent test queries push their executions out of the cache_size window

[laughingman7743]

Use case

TestCursor::test_cache_size fails intermittently in CI. The third execute(query, cache_size=100) returns a new query ID instead of one of the two earlier ones:

AssertionError: assert 'ad46191a-…' in ['cbc9ad0e-…', '5b851bb1-…']
tests/pyathena/test_cursor.py:112

It failed twice in two days: in run 35939526000 on Python 3.12, and in run 36024670053 on Python 3.11. Both times, other pull requests' Test runs overlapped, and a rerun alone passed.

The cause is test isolation, not the cache feature.

• cache_size=100 searches only the 100 most recent executions in the work group.
• The test uses the default primary work group, which almost every other test also uses.
• While a single Test run is active, primary receives 30–50 executions per second, so the test's own executions leave the window within a few seconds.
• Overlapping runs raise that rate further.

The rerun added in #811 applies only to Athena service-side error messages, so it doesn't cover this AssertionError. Running the three suites in parallel (#814) increases the rate in primary.

The investigation details are in the first comment.

Proposed change

• Run the tests that assert a cache hit in the pyathena work group (ENV.work_group). That work group received about 40 executions during a whole Test run.
  • test_cache_size_with_work_group already does this.
  • The same change applies to test_cache_size and the cache-hit part of test_cache_expiration_time_with_cache_size.
  • Tests that assert a cache miss, and the mocked schema/catalog tests, need no change.
• In docs/usage.md, state that cache_size counts the most recent executions in the work group, including other clients' queries, so a busy work group can push a previous execution out of the window.

Alternatives considered:

• Increasing cache_size in the tests (for example, to 1000) still depends on load. At the rates measured with overlapping runs, 1000 executions take only about 8–25 seconds. It also makes each lookup page through 20 ListQueryExecutions and BatchGetQueryExecution calls.
• A dedicated work group for the cache tests isolates them fully. However, it needs a CloudFormation, IAM, and environment variable change, and the pyathena work group is already quiet enough.
• Adding the failure to the rerun list would hide the load dependency without removing it.

Validation plan (if implementing)

• Run the affected tests with uv run --env-file .env pytest -n 1 tests/pyathena/test_cursor.py -k cache.
• Then run the full just test pyathena in CI.
• Confirm the assertions still distinguish a hit from a miss.

This uses the existing test account's pyathena work group. No new AWS resources are needed.

[laughingman7743]

Investigation (2026-09-25)

All measurements are read-only Athena API calls in the test account. They were run while no Test workflow was active.

Failed executions

BatchGetQueryExecution on the three query IDs of each failure:

Run	Execution	Work group	Submitted (UTC)	Completed (UTC)
35939526000 (3.12)	1st cfc799e3	primary	2026-09-24 00:47:52.141	00:47:52.539
	2nd 30ae396e	primary	00:47:53.479	00:47:53.980
	3rd b0da90a4 (expected hit)	primary	00:47:57.306	00:47:57.762
36024670053 (3.11)	1st cbc9ad0e	primary	2026-09-24 16:11:49.674	16:11:50.389
	2nd 5b851bb1	primary	16:11:51.189	16:11:52.544
	3rd ad46191a (expected hit)	primary	16:11:54.656	16:11:56.021

• All three executions in each failure have the same query text, which contains a microsecond timestamp. So the third call ran a new query because the lookup missed; it did not pick up another run's execution.
• Neither failure logged Failed to check the cache. Moving on without cache.. So _find_previous_query_id did not return None because of an exception.

Why the lookup missed

Cursor._find_previous_query_id (pyathena/common.py):

• With cache_size=100, it calls ListQueryExecutions for the cursor's work group in pages of 50 (LIST_QUERY_EXECUTIONS_MAX_RESULTS), for at most two pages.
• It then looks for a succeeded DML execution with the same query, schema, and catalog.
• The listing returns the newest executions first, so a previous execution counts only while fewer than 100 newer executions exist in that work group.

Test cursors use ENV.default_work_group, which defaults to primary. Almost all tests run their queries there.

Execution rate in primary

Paging ListQueryExecutions backward and sampling the submission time every 500 executions, during the Test run on master (run 36107643451, 07:26–07:45 UTC, no other Test run active):

• 30–50 executions per second from 07:27:31 to 07:40:51, peaking at 49.5/s;
• 15–21/s from 07:40:51 to 07:43:08, then tailing off;
• about 30,000 executions for the run.

At 30–50/s, 100 executions take 2–3.5 seconds. The test's second execution takes 0.5–1.4 seconds, and the lookup then lists two pages. So the margin is small even for a single run, and overlapping pull request runs multiply the rate. Both failures happened while other Test runs overlapped.

As another data point, a backward scan of 150,000 executions (3,000 pages) in primary, started 2026-09-25 about 07:50 UTC, did not reach the failed executions from 2026-09-24 16:11 UTC.

Execution rate in pyathena

The 50 most recent executions in the pyathena work group (ENV.work_group) span 2026-09-25 06:47–07:37 UTC. The master run above contributed about 40 of them, between 07:35 and 07:37. A 100-execution window there holds more than two full Test runs' worth of executions. test_cache_size_with_work_group already uses this work group and has not failed this way.

Affected tests

Test	Assertion	Window	Exposure
test_cache_size	hit	100 in primary	failed twice
test_cache_expiration_time_with_cache_size, last part	hit	1000 in primary, 1 hour	about 8 s of headroom under overlapping runs, versus about 7 s of sleep and queries before the lookup
test_cache_size_with_work_group	hit	100 in pyathena	quiet work group
test_cache_expiration_time	hit	unbounded size, 1 hour	independent of volume (pages back to the hit)
test_cache_expiration_time_with_cache_size, other parts	miss	—	not affected
test_cache_size_different_schema / _catalog (sync and aio)	mocked	—	not affected

Recent test changes

• Rerun tests once on Athena service-side query failures #811 reruns only failures matching Amazon Athena experienced an internal error or Invalid S3 request, so this AssertionError is not rerun.
• Run the three test suites in parallel #814 runs the three suites in parallel, which increases the concurrent rate in primary.
• Give each test session its own S3 Tables namespace #815 affects only S3 Tables namespaces.

[laughingman7743]

Decision (2026-09-25): #817 runs the cache-hit tests in the existing pyathena work group. A dedicated work group is not planned.

• The pyathena work group is used only by a few tests in tests/pyathena/test_cursor.py: the cache, result-reuse, and work-group tests. It receives about 150 executions per Test run with Assert cache hits in the test work group instead of primary #817.
• A dedicated work group would still be shared by the five Python versions and by overlapping pull request runs. It would remove only those few other tests' executions, which doesn't justify the CloudFormation, IAM, and environment variable change.