Function test_derived_table_subquery

tests/subquery_cte_test.rs:141–168

()

#[test]
fn test_derived_table_subquery() {
    let conn = setup_test_db();
    
    // First test just the inner query
    let inner_sql = "SELECT c.name as customer_name, AVG(o.total) as avg_order 
                     FROM customers c 
                     JOIN orders o ON c.id = o.customer_id 
                     GROUP BY c.id, c.name";
    let inner_result = rewrite_query(&conn, inner_sql).unwrap();
    println!("Inner query rewritten to: {inner_result}");
    assert!(inner_result.contains("decimal_from_text"), "Inner query should wrap AVG argument");
    
    let sql = "SELECT customer_name, avg_order 
               FROM (
                   SELECT c.name as customer_name, AVG(o.total) as avg_order 
                   FROM customers c 
                   JOIN orders o ON c.id = o.customer_id 
                   GROUP BY c.id, c.name
               ) t 
               WHERE avg_order > 100";
    let result = rewrite_query(&conn, sql).unwrap();
    
    println!("Derived table query rewritten to: {result}");
    assert!(result.contains("AVG"));
    assert!(result.contains("decimal_from_text"));
    // The WHERE clause on avg_order should use decimal comparison
    assert!(result.contains("decimal_gt"), "WHERE clause should use decimal_gt for avg_order > 100");
}

#[test]
fn test_cte_basic() {