S3 partition pruning fails when daily compaction hasn't run

[khalid244]

Summary

Queries fail with No files found error when querying S3 storage and daily compaction hasn't created day-level files yet.

Reproduction

1. Use S3 storage backend
2. Ingest data (creates hourly files at /year/month/day/hour/*.parquet)
3. Query data before daily compaction runs:

SELECT COUNT(*) FROM measurement WHERE time >= '2026-01-20' AND time < '2026-01-21'

Error

IO Error: No files found that match the pattern "s3://bucket/db/measurement/2026/01/20/*.parquet"

Root Cause

1. GeneratePartitionPaths unconditionally adds day-level paths (/2026/01/20/*.parquet) for every day in the query range - no check for whether daily compaction has run or files exist.

2. filterExistingRemotePaths checks if the directory exists (it does - contains hour subdirs), not if files exist at that path (they don't).

3. Partition pruner has no access to daily_min_age_hours config to determine which days might have daily-compacted files.

File Structure

s3://bucket/db/measurement/2026/01/20/
├── 19/file_compacted.parquet   ← Files here (hourly)
├── 20/file_compacted.parquet   ← Files here (hourly)
└── (no files at day level)     ← Query looks here, fails!

Generated Query

FROM read_parquet([
  's3://.../2026/01/20/19/*.parquet',  -- ✓ Has files
  's3://.../2026/01/20/20/*.parquet',  -- ✓ Has files
  's3://.../2026/01/20/*.parquet',     -- ✗ NO FILES - causes error
])

Affected Files

• internal/pruning/partition_pruner.go:498-508 - Unconditional day path addition
• internal/pruning/partition_pruner.go:778-791 - Directory check instead of file check

Workaround Limitation

Running daily compaction manually does NOT fully resolve this issue:

curl -X POST "http://server/api/v1/compaction/trigger?tier=daily"

Why: Daily compaction respects daily_min_age_hours (default: 24 hours). Data newer than this threshold will NOT be compacted, so day-level files won't exist for recent days.

Example with daily_min_age_hours = 25:

• Today's data: No day-level files (too recent) → Query fails
• Yesterday's data: No day-level files (only 24h old) → Query fails
• Data from 2+ days ago: Day-level files exist → Query works

Result: Queries on recent data (< daily_min_age_hours) will always fail on S3, regardless of manual compaction triggers.

Real-World Impact: Grafana Dashboards

Testing with Grafana dashboards querying last 6 hours of data:

SELECT COUNT(*) FROM downloads 
WHERE $__timeFilter(time) AND response = 200
-- Expands to: WHERE time >= '2026-01-21T00:00:00Z' AND time < '2026-01-21T06:00:00Z'

This query will ALWAYS fail because:

• 6-hour-old data will never have daily compacted files
• daily_min_age_hours (default 24h) prevents recent data from being daily-compacted
• Partition pruner still adds day-level paths for these recent hours
• Day-level paths have no files → query fails

Impact: Any Grafana dashboard querying recent data (< 24 hours) on S3 storage is broken.

Unexpected Behavior: Complex Queries Work

Simple query - FAILS:

SELECT COUNT(*) FROM downloads 
WHERE time >= '2026-01-20' AND time < '2026-01-21' AND response = 200

Complex query - WORKS:

SELECT COUNT(*) FROM downloads 
WHERE time >= '2026-01-20' AND time < '2026-01-21' AND response = 200 
AND site NOT IN ('site1', 'site2')

Why: The WHERE clause extraction regex (whereClausePattern) can be affected by complex predicates, causing time range extraction to fail. When extraction fails, partition pruning falls back to **/*.parquet glob pattern which works correctly.

This means adding unrelated WHERE conditions can accidentally "fix" the query - indicating the partition pruning logic is fragile.

---

Related: #134

[khalid244]

Related discussion: #133 (comment)

I think we should go with Option A: Fix the existing S3 check to verify files exist, not just directories.

This approach:

• ✅ Same number of S3 API calls (no performance impact)
• ✅ Actually verifies files exist
• ✅ Works regardless of daily_min_age_hours config

[xe-nvdk]

Hey @khalid244, thank you for opening a new issue, I think that this was addressed in this issue.

#125 PR #127

[xe-nvdk]

Can you confirm what version are you using or if you are building from source? This was fixed in the PR 127. I will try to reproduce you scenario here, to see if I'm seeing the same behavior.

[xe-nvdk]

Ok. I was able to reproduce this. I'm going to test your PR, and we can continue from there. Makes sense?

[xe-nvdk]

I found the issue. that basically is, the query failing if no data is found, cause the query aim to the future of cause there is not data.

This one works

curl -s -X POST http://localhost:8000/api/v1/query \                                                                                ✔  at 02:13:57 PM   
  -H "Authorization: Bearer xxx \
  -H "Content-Type: application/json" \
  -H "X-Arc-Database: production" \
  -d @- <<'EOF'
{"sql": "SELECT COUNT(*) FROM cpu  WHERE time >= '2026-01-20' AND time < '2026-01-21'"}
EOF

{"success":true,"columns":["count_star()"],"data":[[0]],"row_count":1,"execution_time_ms":11,"timestamp":"2026-01-21T17:14:11Z"}

This one doesn't work

curl -s -X POST http://localhost:8000/api/v1/query \                                                                                ✔  at 02:14:11 PM   
  -H "Authorization: Bearer xxx" \
  -H "Content-Type: application/json" \
  -H "X-Arc-Database: production" \
  -d @- <<'EOF'
{"sql": "SELECT COUNT(*) FROM cpu  WHERE time >= '2026-01-20' AND time < '2026-01-22'"}
EOF

{"success":false,"columns":null,"data":null,"row_count":0,"execution_time_ms":86,"timestamp":"2026-01-21T17:14:38Z","error":"Query execution failed"}%      

I think that your commit fix this partially, I will post a comment detailing what's needed to be addressed, but you find something here. I appreciate that you took the time to post the issue and commit a fix.

[khalid244]

Hi @xe-nvdk,

I encountered this issue while testing the latest main branch build (f9e2659). It occurs in Grafana when querying with this time range: {"from":"now-6h","to":"now"}.

During my investigation, I found that #145 resolves the issue. However, as you mentioned, there may be additional edge cases when users query from the future. Do you have a better alternative solution?

[xe-nvdk]

Yes, is in the PR. If you want, I can take it from there. Let me know.

[khalid244]

Yes, feel free to take it from there. Thanks! I will focus on #147