sql-cli 1.73.1

SQL query tool for CSV/JSON with both interactive TUI and non-interactive CLI modes - perfect for exploration and automation
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214

#!/usr/bin/env python3
"""
Test suite for SQL aggregate functions
"""

import subprocess
import json
import csv
import os
import tempfile
import pytest
from pathlib import Path

# Find the sql-cli binary
PROJECT_ROOT = Path(__file__).parent.parent.parent
SQL_CLI = PROJECT_ROOT / "target" / "release" / "sql-cli"

if not SQL_CLI.exists():
    SQL_CLI = PROJECT_ROOT / "target" / "debug" / "sql-cli"


def run_query(csv_file, query):
    """Run a SQL query and return results as a list of dictionaries."""
    cmd = [str(SQL_CLI), csv_file, "-q", query, "-o", "json"]
    result = subprocess.run(cmd, capture_output=True, text=True)
    if result.returncode != 0:
        raise Exception(f"Query failed: {result.stderr}")
    
    # Parse JSON output
    lines = result.stdout.strip().split('\n')
    # Filter out comment lines
    json_lines = [l for l in lines if not l.startswith('#')]
    if not json_lines:
        return []
    
    return json.loads(''.join(json_lines))


class TestBasicAggregates:
    """Test basic aggregate functions."""
    
    @classmethod
    def setup_class(cls):
        """Create test data files."""
        cls.temp_dir = tempfile.mkdtemp()
        
        # Create numeric test data
        cls.numeric_file = os.path.join(cls.temp_dir, "numeric.csv")
        with open(cls.numeric_file, 'w', newline='') as f:
            writer = csv.writer(f)
            writer.writerow(['id', 'value', 'category'])
            writer.writerow([1, 10, 'A'])
            writer.writerow([2, 20, 'B'])
            writer.writerow([3, 30, 'A'])
            writer.writerow([4, 40, 'B'])
            writer.writerow([5, 50, 'A'])
        
        # Create data with nulls
        cls.nulls_file = os.path.join(cls.temp_dir, "nulls.csv")
        with open(cls.nulls_file, 'w', newline='') as f:
            writer = csv.writer(f)
            writer.writerow(['id', 'value', 'category'])
            writer.writerow([1, 10, 'A'])
            writer.writerow([2, '', 'B'])  # NULL value
            writer.writerow([3, 30, ''])   # NULL category
            writer.writerow([4, '', 'B'])  # NULL value
            writer.writerow([5, 50, 'A'])
    
    def test_count_star(self):
        """Test COUNT(*) function."""
        result = run_query(self.numeric_file, "SELECT COUNT(*) FROM numeric")
        assert len(result) == 1
        assert result[0]['expr_1'] == 5
    
    def test_count_column(self):
        """Test COUNT(column) function."""
        result = run_query(self.nulls_file, "SELECT COUNT(value), COUNT(category), COUNT(*) FROM nulls")
        assert len(result) == 1
        # COUNT(value) should skip nulls
        assert result[0]['expr_1'] == 3  # Only 3 non-null values
        assert result[0]['expr_2'] == 4  # 4 non-null categories
        assert result[0]['expr_3'] == 5  # COUNT(*) counts all rows
    
    def test_sum(self):
        """Test SUM() function."""
        result = run_query(self.numeric_file, "SELECT SUM(value) FROM numeric")
        assert len(result) == 1
        assert result[0]['expr_1'] == 150  # 10+20+30+40+50
    
    def test_sum_with_nulls(self):
        """Test SUM() with null values."""
        result = run_query(self.nulls_file, "SELECT SUM(value) FROM nulls")
        assert len(result) == 1
        assert result[0]['expr_1'] == 90  # 10+30+50 (nulls ignored)
    
    def test_avg(self):
        """Test AVG() function."""
        result = run_query(self.numeric_file, "SELECT AVG(value) FROM numeric")
        assert len(result) == 1
        assert result[0]['expr_1'] == 30.0  # 150/5
    
    def test_avg_with_nulls(self):
        """Test AVG() with null values."""
        result = run_query(self.nulls_file, "SELECT AVG(value) FROM nulls")
        assert len(result) == 1
        assert result[0]['expr_1'] == 30.0  # 90/3 (only non-null values)
    
    def test_min_max(self):
        """Test MIN() and MAX() functions."""
        result = run_query(self.numeric_file, "SELECT MIN(value), MAX(value) FROM numeric")
        assert len(result) == 1
        assert result[0]['expr_1'] == 10
        assert result[0]['expr_2'] == 50
    
    def test_min_max_strings(self):
        """Test MIN() and MAX() on string columns."""
        result = run_query(self.numeric_file, "SELECT MIN(category), MAX(category) FROM numeric")
        assert len(result) == 1
        assert result[0]['expr_1'] == 'A'
        assert result[0]['expr_2'] == 'B'
    
    def test_multiple_aggregates(self):
        """Test multiple aggregates in one query."""
        result = run_query(self.numeric_file, 
                          "SELECT COUNT(*), SUM(value), AVG(value), MIN(value), MAX(value) FROM numeric")
        assert len(result) == 1
        assert result[0]['expr_1'] == 5
        assert result[0]['expr_2'] == 150
        assert result[0]['expr_3'] == 30.0
        assert result[0]['expr_4'] == 10
        assert result[0]['expr_5'] == 50


class TestAggregatesWithExpressions:
    """Test aggregate functions with expressions."""
    
    @classmethod
    def setup_class(cls):
        """Create test data files."""
        cls.temp_dir = tempfile.mkdtemp()
        
        cls.sales_file = os.path.join(cls.temp_dir, "sales.csv")
        with open(cls.sales_file, 'w', newline='') as f:
            writer = csv.writer(f)
            writer.writerow(['product', 'quantity', 'price'])
            writer.writerow(['Apple', 10, 2.5])
            writer.writerow(['Banana', 5, 1.0])
            writer.writerow(['Orange', 8, 3.0])
            writer.writerow(['Apple', 15, 2.5])
            writer.writerow(['Banana', 12, 1.0])
    
    def test_sum_expression(self):
        """Test SUM() with expression."""
        result = run_query(self.sales_file, "SELECT SUM(quantity * price) FROM sales")
        assert len(result) == 1
        # (10*2.5) + (5*1.0) + (8*3.0) + (15*2.5) + (12*1.0) = 25+5+24+37.5+12 = 103.5
        assert result[0]['expr_1'] == pytest.approx(103.5)
    
    def test_avg_expression(self):
        """Test AVG() with expression."""
        result = run_query(self.sales_file, "SELECT AVG(quantity * price) FROM sales")
        assert len(result) == 1
        # 103.5 / 5 = 20.7
        assert result[0]['expr_1'] == pytest.approx(20.7)
    
    def test_complex_aggregates(self):
        """Test complex aggregate expressions."""
        result = run_query(self.sales_file, 
                          "SELECT SUM(quantity), AVG(price), SUM(quantity) * AVG(price) FROM sales")
        assert len(result) == 1
        assert result[0]['expr_1'] == 50  # Total quantity
        assert result[0]['expr_2'] == 2.0  # Avg price (2.5+1+3+2.5+1)/5
        assert result[0]['expr_3'] == 100.0  # 50 * 2.0


class TestAggregatesWithWhere:
    """Test aggregate functions with WHERE clause."""
    
    @classmethod
    def setup_class(cls):
        """Create test data files."""
        cls.temp_dir = tempfile.mkdtemp()
        
        cls.data_file = os.path.join(cls.temp_dir, "data.csv")
        with open(cls.data_file, 'w', newline='') as f:
            writer = csv.writer(f)
            writer.writerow(['id', 'value', 'category', 'active'])
            writer.writerow([1, 100, 'A', 'true'])
            writer.writerow([2, 200, 'B', 'false'])
            writer.writerow([3, 300, 'A', 'true'])
            writer.writerow([4, 400, 'B', 'true'])
            writer.writerow([5, 500, 'A', 'false'])
    
    def test_aggregates_with_where(self):
        """Test aggregates with WHERE filtering."""
        result = run_query(self.data_file, 
                          "SELECT COUNT(*), SUM(value), AVG(value) FROM data WHERE category = 'A'")
        assert len(result) == 1
        assert result[0]['expr_1'] == 3  # 3 rows with category A
        assert result[0]['expr_2'] == 900  # 100+300+500
        assert result[0]['expr_3'] == 300.0  # 900/3
    
    def test_aggregates_with_complex_where(self):
        """Test aggregates with complex WHERE clause."""
        # Note: Using numeric comparison since string comparison has separate issues
        result = run_query(self.data_file, 
                          "SELECT COUNT(*), SUM(value) FROM data WHERE value > 150 AND value < 450")
        assert len(result) == 1
        assert result[0]['expr_1'] == 3  # Rows 2, 3, 4 (200, 300, 400)
        assert result[0]['expr_2'] == 900  # 200+300+400


if __name__ == "__main__":
    pytest.main([__file__, "-v"])