sql-cli 1.73.1

# RANGE Function Testing Summary

## Key Findings

### ✅ WHAT WORKS

1. **Basic RANGE Operations**
   - `SELECT value FROM RANGE(1, 10)` ✓
   - `SELECT value FROM RANGE(1, 10) WHERE value > 5` ✓
   - `SELECT value FROM RANGE(1, 10) WHERE IS_PRIME(value) = true` ✓

2. **CTEs with RANGE**
   ```sql
   WITH nums AS (SELECT value FROM RANGE(1, 10))
   SELECT value, value * 2 AS doubled FROM nums
   ```
   ✓ Works perfectly

3. **WHERE Clauses with MOD Function**
   ```sql
   SELECT value FROM RANGE(1, 20) WHERE MOD(value, 3) = 0
   ```
   ✓ Returns: 3, 6, 9, 12, 15, 18

4. **Complex Prime Analysis**
   ```sql
   WITH blocks AS (SELECT value AS block_num FROM RANGE(1, 5))
   SELECT block_num, 
          PRIME_PI(block_num * 100) - PRIME_PI((block_num - 1) * 100) AS primes_in_block
   FROM blocks
   ```
   ✓ Works as expected

## ❌ WHAT DOESN'T WORK

1. **Modulo Operator (%) - Not Supported**
   - We don't support `%` operator
   - **Solution**: Use `MOD(value, n)` function instead

2. **Column References in Expressions Outside Basic SELECT**
   ```sql
   SELECT value, value % 3 FROM RANGE(1, 10)  -- Fails: Column 'value' not found
   ```
   - **Workaround**: Use CTE wrapper

3. **Cross Joins Between Multiple CTEs**
   ```sql
   WITH small AS (SELECT value AS s FROM RANGE(1, 3)), 
        large AS (SELECT value AS l FROM RANGE(10, 12))
   SELECT s, l FROM small, large  -- Fails: Column 'l' not found
   ```

4. **GROUP BY with Expressions**
   ```sql
   GROUP BY MOD(value, 5)  -- Fails: Column 'MOD' not found for GROUP BY
   ```

## 📝 Fixed Python Tests

The test file needs these corrections:

1. Replace all `value % n` with `MOD(value, n)`
2. Remove cross-join tests or mark as known limitations
3. Remove GROUP BY expression tests or use column aliases

## Example Working Queries

```sql
-- Prime numbers in range
SELECT value FROM RANGE(2, 100) WHERE IS_PRIME(value) = true

-- Numbers divisible by 3
SELECT value FROM RANGE(1, 30) WHERE MOD(value, 3) = 0

-- Square numbers
WITH nums AS (SELECT value FROM RANGE(1, 10))
SELECT value, value * value AS squared FROM nums

-- Prime density analysis
WITH blocks AS (SELECT value AS block FROM RANGE(1, 10))
SELECT block,
       PRIME_PI(block * 100) - PRIME_PI((block - 1) * 100) AS primes_in_block,
       ROUND((PRIME_PI(block * 100) - PRIME_PI((block - 1) * 100)) * 100.0 / 100, 2) AS density
FROM blocks
```

## Recommendations

1. **Document MOD function usage** - Users should know to use MOD instead of %
2. **Always use CTEs for complex operations** - Better compatibility
3. **For window functions**, need to ensure proper data file context
4. **Cross joins** need investigation - column resolution issue

## Test Results

After corrections:
- ✅ 15/17 tests pass with MOD function
- ❌ 2 tests fail (cross joins, GROUP BY expressions) - these are actual limitations