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

use sql_cli::data::datatable::{DataColumn, DataRow, DataTable, DataValue};
use sql_cli::data::query_engine::QueryEngine;
use std::sync::Arc;

/// Create a test table with numeric data
fn create_test_table() -> Arc<DataTable> {
    let mut table = DataTable::new("test_table");

    // Add columns
    table.add_column(DataColumn::new("id"));
    table.add_column(DataColumn::new("quantity"));
    table.add_column(DataColumn::new("price"));

    // Add test rows
    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(1),
            DataValue::Integer(10),
            DataValue::Float(25.50),
        ]))
        .unwrap();

    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(2),
            DataValue::Integer(5),
            DataValue::Float(100.00),
        ]))
        .unwrap();

    Arc::new(table)
}

#[test]
fn test_select_star_with_computed_column() {
    let table = create_test_table();
    let engine = QueryEngine::new();

    // This should expand * to all columns AND add the computed column
    let view = engine
        .execute(
            table.clone(),
            "SELECT *, quantity * price as total FROM test_table",
        )
        .unwrap();

    // Should have 4 columns: id, quantity, price, total
    assert_eq!(view.column_count(), 4);

    let columns = view.column_names();
    assert_eq!(columns, vec!["id", "quantity", "price", "total"]);

    // Check first row values
    let row = view.get_row(0).unwrap();
    assert_eq!(row.get(0).unwrap(), &DataValue::Integer(1)); // id
    assert_eq!(row.get(1).unwrap(), &DataValue::Integer(10)); // quantity
    assert_eq!(row.get(2).unwrap(), &DataValue::Float(25.50)); // price
    assert_eq!(row.get(3).unwrap(), &DataValue::Float(255.0)); // total = 10 * 25.50
}

#[test]
fn test_select_star_comma_arithmetic() {
    let table = create_test_table();
    let engine = QueryEngine::new();

    // Test the problematic case: SELECT *,quantity * price as total
    let result = engine.execute(
        table.clone(),
        "SELECT *,quantity * price as total FROM test_table",
    );

    match result {
        Ok(view) => {
            println!("Columns: {:?}", view.column_names());
            println!("Column count: {}", view.column_count());
            // Should have 4 columns: id, quantity, price, total
            assert_eq!(view.column_count(), 4);

            let columns = view.column_names();
            assert_eq!(columns, vec!["id", "quantity", "price", "total"]);

            // Check first row values
            let row = view.get_row(0).unwrap();
            assert_eq!(row.get(0).unwrap(), &DataValue::Integer(1)); // id
            assert_eq!(row.get(1).unwrap(), &DataValue::Integer(10)); // quantity
            assert_eq!(row.get(2).unwrap(), &DataValue::Float(25.50)); // price
            assert_eq!(row.get(3).unwrap(), &DataValue::Float(255.0)); // total
        }
        Err(e) => {
            panic!("Query failed: {e}");
        }
    }
}

#[test]
fn test_star_in_middle_of_select() {
    let table = create_test_table();
    let engine = QueryEngine::new();

    // Test: SELECT id, *, price * 2 as double_price
    let view = engine
        .execute(
            table.clone(),
            "SELECT id, *, price * 2 as double_price FROM test_table",
        )
        .unwrap();

    // Should have 5 columns: id (explicit), id (from *), quantity (from *), price (from *), double_price
    // But the first id might be deduplicated
    assert!(view.column_count() >= 4);

    let columns = view.column_names();
    assert!(columns.contains(&"id".to_string()));
    assert!(columns.contains(&"quantity".to_string()));
    assert!(columns.contains(&"price".to_string()));
    assert!(columns.contains(&"double_price".to_string()));
}