#!/usr/bin/env tclsh

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test subquery-inner-filter {
    select sub.loud_hat from (
        select concat(name, '!!!') as loud_hat 
        from products where name = 'hat'
    ) sub;
} {hat!!!}

do_execsql_test subquery-inner-filter-cte {
    with sub as (
        select concat(name, '!!!') as loud_hat 
        from products where name = 'hat'
    )
    select sub.loud_hat from sub;
} {hat!!!}

do_execsql_test subquery-outer-filter {
    select sub.loud_hat from (
        select concat(name, '!!!') as loud_hat 
        from products
    ) sub where sub.loud_hat = 'hat!!!'
} {hat!!!}

do_execsql_test subquery-outer-filter-cte {
    with sub as (
        select concat(name, '!!!') as loud_hat 
        from products
    )
    select sub.loud_hat from sub where sub.loud_hat = 'hat!!!'
} {hat!!!}

do_execsql_test subquery-without-alias {
    select loud_hat from (
        select concat(name, '!!!') as loud_hat 
        from products where name = 'hat'
    );
} {hat!!!}

do_execsql_test subquery-without-alias-cte {
    with cte as (
        select concat(name, '!!!') as loud_hat 
        from products where name = 'hat'
    )
    select loud_hat from cte;
} {hat!!!}

do_execsql_test subquery-no-alias-on-col {
    select price from (
        select * from products where name = 'hat'
    )
} {79.0}

do_execsql_test subquery-no-alias-on-col-cte {
    with cte as (
        select * from products where name = 'hat'
    )
    select price from cte
} {79.0}

do_execsql_test subquery-no-alias-on-col-named {
    select price from (
        select price from products where name = 'hat'
    )
} {79.0}

do_execsql_test subquery-no-alias-on-col-named-cte {
    with cte as (
        select price from products where name = 'hat'
    )
    select price from cte
} {79.0}

do_execsql_test subquery-select-star {
    select * from (
        select price, price + 1.0, name from products where name = 'hat'
    )
} {79.0|80.0|hat}

do_execsql_test subquery-select-star-cte {
    with cte as (
        select price, price + 1.0, name from products where name = 'hat'
    )
    select * from cte
} {79.0|80.0|hat}

do_execsql_test subquery-select-table-star {
    select sub.* from (
        select price, price + 1.0, name from products where name = 'hat'
    ) sub
} {79.0|80.0|hat}

do_execsql_test subquery-select-table-star-cte {
    with sub as (
        select price, price + 1.0, name from products where name = 'hat'
    )
    select sub.* from sub
} {79.0|80.0|hat}

do_execsql_test nested-subquery {
    select sub.loudest_hat from (
        select upper(nested_sub.loud_hat) as loudest_hat from (
            select concat(name, '!!!') as loud_hat 
            from products where name = 'hat'
        ) nested_sub 
    ) sub;
} {HAT!!!}

do_execsql_test nested-subquery-cte {
    with nested_sub as (
        select concat(name, '!!!') as loud_hat 
        from products where name = 'hat'
    ),
    sub as (
        select upper(nested_sub.loud_hat) as loudest_hat from nested_sub
    )
    select sub.loudest_hat from sub;
} {HAT!!!}

do_execsql_test subquery-orderby-limit {
    select upper(sub.loud_name) as loudest_name 
    from (
        select concat(name, '!!!') as loud_name 
        from products 
        order by name 
        limit 3
    ) sub;
} {ACCESSORIES!!!
BOOTS!!!
CAP!!!}

do_execsql_test subquery-orderby-limit-cte {
    with sub as (
        select concat(name, '!!!') as loud_name 
        from products 
        order by name 
        limit 3
    )
    select upper(sub.loud_name) as loudest_name from sub;
} {ACCESSORIES!!!
BOOTS!!!
CAP!!!}

do_execsql_test table-join-subquery {
    select sub.product_name, p.name 
    from products p join (
        select name as product_name 
        from products
    ) sub on p.name = sub.product_name where p.name = 'hat'
} {hat|hat}

do_execsql_test table-join-subquery-cte {
    with sub as (
        select name as product_name 
        from products
    )
    select sub.product_name, p.name 
    from products p join sub on p.name = sub.product_name 
    where p.name = 'hat'
} {hat|hat}

do_execsql_test subquery-join-table {
    select sub.product_name, p.name
    from (
        select name as product_name 
        from products
    ) sub join products p on sub.product_name = p.name where sub.product_name = 'hat'
} {hat|hat}

do_execsql_test subquery-join-table-cte {
    with sub as (
        select name as product_name 
        from products
    )
    select sub.product_name, p.name
    from sub join products p on sub.product_name = p.name 
    where sub.product_name = 'hat'
} {hat|hat}

do_execsql_test subquery-join-subquery {
    select sub1.sus_name, sub2.truthful_name
    from (
        select name as sus_name
        from products
        where name = 'cap'
    ) sub1 join (
        select concat('no ', name) as truthful_name
        from products 
        where name = 'cap'
    ) sub2;
} {"cap|no cap"}

do_execsql_test subquery-join-subquery-cte {
    with sub1 as (
        select name as sus_name
        from products
        where name = 'cap'
    ),
    sub2 as (
        select concat('no ', name) as truthful_name
        from products 
        where name = 'cap'
    )
    select sub1.sus_name, sub2.truthful_name
    from sub1 join sub2;
} {"cap|no cap"}

do_execsql_test select-star-table-subquery {
    select * 
    from products p join (
        select name, price 
        from products
        where name = 'hat'
    ) sub on p.name = sub.name;
} {1|hat|79.0|hat|79.0}

do_execsql_test select-star-table-subquery-cte {
    with sub as (
        select name, price 
        from products
        where name = 'hat'
    )
    select * 
    from products p join sub on p.name = sub.name;
} {1|hat|79.0|hat|79.0}

do_execsql_test select-star-subquery-table {
    select * 
    from (
        select name, price 
        from products
        where name = 'hat'
    ) sub join products p on sub.name = p.name;
} {hat|79.0|1|hat|79.0}

do_execsql_test select-star-subquery-table-cte {
    with sub as (
        select name, price 
        from products
        where name = 'hat'
    )
    select * 
    from sub join products p on sub.name = p.name;
} {hat|79.0|1|hat|79.0}

do_execsql_test select-star-subquery-subquery {
    select *
    from (
        select name, price 
        from products
        where name = 'hat'
    ) sub1 join (
        select price
        from products 
        where name = 'hat'
    ) sub2 on sub1.price = sub2.price;
} {hat|79.0|79.0}

do_execsql_test select-star-subquery-subquery-cte {
    with sub1 as (
        select name, price 
        from products
        where name = 'hat'
    ),
    sub2 as (
        select price
        from products 
        where name = 'hat'
    )
    select *
    from sub1 join sub2 on sub1.price = sub2.price;
} {hat|79.0|79.0}

do_execsql_test subquery-inner-grouping {
    select is_jennifer, person_count
    from (
        select first_name = 'Jennifer' as is_jennifer, count(1) as person_count from users
        group by first_name = 'Jennifer'
    ) order by person_count asc
} {1|151
0|9849}

do_execsql_test subquery-inner-grouping-cte {
    with cte as (
        select first_name = 'Jennifer' as is_jennifer, count(1) as person_count from users
        group by first_name = 'Jennifer'
    )
    select is_jennifer, person_count
    from cte order by person_count asc
} {1|151
0|9849}

do_execsql_test subquery-outer-grouping {
    select is_jennifer, count(1) as person_count
    from (
        select first_name = 'Jennifer' as is_jennifer from users
    ) group by is_jennifer order by count(1) asc
} {1|151
0|9849}

do_execsql_test subquery-outer-grouping-cte {
    with cte as (
        select first_name = 'Jennifer' as is_jennifer from users
    )
    select is_jennifer, count(1) as person_count
    from cte group by is_jennifer order by count(1) asc
} {1|151
0|9849}

do_execsql_test subquery-join-using-with-outer-limit {
    SELECT p.name, sub.funny_name 
    FROM products p 
    JOIN (
        select id, concat(name, '-lol') as funny_name 
        from products
    ) sub USING (id) 
    LIMIT 3;
} {"hat|hat-lol
cap|cap-lol
shirt|shirt-lol"}

do_execsql_test subquery-join-using-with-outer-limit-cte {
    WITH sub AS (
        select id, concat(name, '-lol') as funny_name 
        from products
    )
    SELECT p.name, sub.funny_name 
    FROM products p 
    JOIN sub USING (id) 
    LIMIT 3;
} {"hat|hat-lol
cap|cap-lol
shirt|shirt-lol"}

do_execsql_test subquery-join-using-with-inner-limit {
    SELECT p.name, sub.funny_name 
    FROM products p 
    JOIN (
        select id, concat(name, '-lol') as funny_name 
        from products
        limit 3
    ) sub USING (id);
} {"hat|hat-lol
cap|cap-lol
shirt|shirt-lol"}

do_execsql_test subquery-join-using-with-inner-limit-cte {
    WITH sub AS (
        select id, concat(name, '-lol') as funny_name 
        from products
        limit 3
    )
    SELECT p.name, sub.funny_name 
    FROM products p 
    JOIN sub USING (id);
} {"hat|hat-lol
cap|cap-lol
shirt|shirt-lol"}

do_execsql_test subquery-join-using-with-both-limits {
    SELECT p.name, sub.funny_name 
    FROM products p 
    JOIN (
        select id, concat(name, '-lol') as funny_name 
        from products
        limit 3
    ) sub USING (id)
    LIMIT 2;
} {"hat|hat-lol
cap|cap-lol"}

do_execsql_test subquery-join-using-with-both-limits-cte {
    WITH sub AS (
        select id, concat(name, '-lol') as funny_name 
        from products
        limit 3
    )
    SELECT p.name, sub.funny_name 
    FROM products p 
    JOIN sub USING (id)
    LIMIT 2;
} {"hat|hat-lol
cap|cap-lol"}

do_execsql_test subquery-containing-join {
    select foo, bar 
    from (
        select p.name as foo, u.first_name as bar 
        from products p join users u using (id)
    ) limit 3;
} {hat|Jamie
cap|Cindy
shirt|Tommy}

do_execsql_test subquery-containing-join-cte {
    with cte as (
        select p.name as foo, u.first_name as bar 
        from products p join users u using (id)
    )
    select foo, bar 
    from cte limit 3;
} {hat|Jamie
cap|Cindy
shirt|Tommy}

do_execsql_test subquery-ignore-unused-cte {
    with unused as (select last_name from users), 
    sub as (select first_name from users where first_name = 'Jamie' limit 1) 
    select * from sub;
} {Jamie}

if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} {
    # Test verifying that select distinct works (distinct ages are 1-100)
    do_execsql_test subquery-count-distinct-age {
        select count(1) from (select distinct age from users);
    } {100}

    # Test verifying that select distinct works for multiple columns, and across joins
    do_execsql_test subquery-count-distinct {
        select count(1) from (
            select distinct first_name, name 
            from users u join products p 
            where u.id < 100
        );
    } {902}
}

do_execsql_test subquery-count-all {
    select count(1) from (
        select first_name, name 
        from users u join products p 
        where u.id < 100
    );
} {1089}