limbo/testing/subquery.test
2025-06-17 19:33:23 +02:00

437 lines
11 KiB
Tcl

#!/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}