turso/testing/join.test

#!/usr/bin/env tclsh

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

do_execsql_test cross-join {
    select * from users, products limit 1;
} {1|Jamie|Foster|dylan00@example.com|496-522-9493|62375\ Johnson\ Rest\ Suite\ 322|West\ Lauriestad|IL|35865|94|1|hat|79.0}

do_execsql_test cross-join-specific-columns {
    select first_name, price from users, products limit 1;
} {Jamie|79.0}

do_execsql_test cross-join-where-right-tbl {
    select users.first_name, products.name from users join products where products.id = 1 limit 2;
} {Jamie|hat
Cindy|hat}

do_execsql_test cross-join-where-left-tbl {
    select users.first_name, products.name from users join products where users.id = 1 limit 2;
} {Jamie|hat
Jamie|cap}

do_execsql_test inner-join-pk {
    select users.first_name as user_name, products.name as product_name from users join products on users.id = products.id;
} {Jamie|hat
Cindy|cap
Tommy|shirt
Jennifer|sweater
Edward|sweatshirt
Nicholas|shorts
Aimee|jeans
Rachel|sneakers
Matthew|boots
Daniel|coat
Travis|accessories}

do_execsql_test inner-join-non-pk-unqualified {
    select first_name, name from users join products on first_name != name limit 1;
} {Jamie|hat}

do_execsql_test inner-join-non-pk-qualified {
    select users.first_name as user_name, products.name as product_name from users join products on users.first_name = products.name;
} {}

do_execsql_test inner-join-self {
    select u1.first_name as user_name, u2.first_name as neighbor_name from users u1 join users as u2 on u1.id = u2.id + 1 limit 1;
} {Cindy|Jamie}

do_execsql_test inner-join-self-with-where {
    select u1.first_name as user_name, u2.first_name as neighbor_name from users u1 join users as u2 on u1.id = u2.id + 1 where u1.id = 5 limit 1;
} {Edward|Jennifer}

# Uncomment this test when it works. Sqlite3 returns 'Aaron' due to the way it reorders tables in the join based on the where clause.
#do_execsql_test inner-join-with-where-2 {
#    select u.first_name from users u join products as p on u.first_name != p.name where u.last_name = 'Williams' limit 1;
#} {Laura} <-- sqlite3 returns 'Aaron'

do_execsql_test inner-join-constant-condition-true {
    select u.first_name, p.name from users u join products as p where 1 limit 5;
} {Jamie|hat
Jamie|cap
Jamie|shirt
Jamie|sweater
Jamie|sweatshirt}

do_execsql_test inner-join-constant-condition-false {
    select u.first_name from users u join products as p where 0 limit 5;
} {}

do_execsql_test left-join-pk {
    select users.first_name as user_name, products.name as product_name from users left join products on users.id = products.id limit 12;
} {Jamie|hat
Cindy|cap
Tommy|shirt
Jennifer|sweater
Edward|sweatshirt
Nicholas|shorts
Aimee|jeans
Rachel|sneakers
Matthew|boots
Daniel|coat
Travis|accessories
Alan|}

do_execsql_test left-join-with-where {
    select u.first_name, p.name from users u left join products as p on u.id = p.id where u.id >= 10 limit 5;
} {Daniel|coat
Travis|accessories
Alan|
Michael|
Brianna|}

do_execsql_test left-join-with-where-2 {
   select users.first_name, products.name from users left join products on users.id < 2 where users.id < 3;
} {Jamie|hat
Jamie|cap
Jamie|shirt
Jamie|sweater
Jamie|sweatshirt
Jamie|shorts
Jamie|jeans
Jamie|sneakers
Jamie|boots
Jamie|coat
Jamie|accessories
Cindy|}

# This test verifies that the WHERE clause is evaluated after the LEFT JOIN,
# effectively filtering out rows where the right table has no match.
do_execsql_test left-join-with-where-right-table {
    select users.id, price
    from users left join products on users.id = products.id
    where products.price is not null
    order by users.id;
} {1|79.0
2|82.0
3|18.0
4|25.0
5|74.0
6|70.0
7|78.0
8|82.0
9|1.0
10|33.0
11|81.0}

do_execsql_test left-join-row-id {
    select u.rowid, p.rowid from users u left join products as p on u.rowid = p.rowid where u.rowid >= 10 limit 5;
} {10|10
11|11
12|
13|
14|}

do_execsql_test left-join-constant-condition-true {
    select u.first_name, p.name from users u left join products as p on true limit 1;
} {Jamie|hat}

do_execsql_test left-join-constant-condition-false {
    select u.first_name, p.name from users u left join products as p on false limit 1;
} {Jamie|}

do_execsql_test left-join-constant-condition-where-false {
    select u.first_name, p.name from users u left join products as p where false limit 1;
} {}

do_execsql_test left-join-non-pk {
    select users.first_name as user_name, products.name as product_name from users left join products on users.first_name = products.name limit 3;
} {Jamie|
Cindy|
Tommy|}

do_execsql_test left-join-self {
    select u1.first_name as user_name, u2.first_name as neighbor_name from users u1 left join users as u2 on u1.id = u2.id + 1 limit 2;
} {Jamie|
Cindy|Jamie}

do_execsql_test left-join-self-2 {
    select u1.first_name as user_name, u2.first_name as neighbor_name from users u1 left join users as u2 on u2.id = u1.id + 1 limit 2;
} {Jamie|Cindy
Cindy|Tommy}

do_execsql_test left-join-self-with-where {
    select u1.first_name as user_name, u2.first_name as neighbor_name from users u1 left join users as u2 on u1.id = u2.id + 1 where u1.id = 5 limit 2;
} {Edward|Jennifer}

do_execsql_test left-join-multiple-cond-and {
    select u.first_name, p.name from users u left join products as p on u.id = p.id and u.first_name = p.name limit 2;
} {Jamie|
Cindy|}

do_execsql_test left-join-multiple-cond-or {
    select u.first_name, p.name from users u left join products as p on u.id = p.id or u.first_name = p.name limit 2;
} {Jamie|hat
Cindy|cap}

do_execsql_test left-join-no-join-conditions-but-multiple-where {
    select u.first_name, p.name from users u left join products as p where u.id = p.id or u.first_name = p.name limit 2;
} {Jamie|hat
Cindy|cap}

do_execsql_test left-join-order-by-qualified {
    select users.first_name, products.name from users left join products on users.id = products.id where users.first_name like 'Jam%' order by null limit 2;
} {Jamie|hat
James|}

do_execsql_test left-join-order-by-qualified-nullable-sorting-col {
    select users.first_name, products.name from users left join products on users.id = products.id order by products.name limit 1;
} {Alan|}

do_execsql_test left-join-constant-condition-true {
    select u.first_name, p.name from users u left join products as p on 1 limit 5;
} {Jamie|hat
Jamie|cap
Jamie|shirt
Jamie|sweater
Jamie|sweatshirt}

do_execsql_test left-join-constant-condition-false {
    select u.first_name, p.name from users u left join products as p on 0 limit 5;
} {Jamie|
Cindy|
Tommy|
Jennifer|
Edward|}

do_execsql_test four-way-inner-join {
  select u1.first_name, u2.first_name, u3.first_name, u4.first_name from users u1 join users u2 on u1.id = u2.id join users u3 on u2.id = u3.id + 1 join users u4 on u3.id = u4.id + 1 limit 1;
} {Tommy|Tommy|Cindy|Jamie}

# regression test for case where 3-way join that used 1 scan and 2 seeks (point lookups) was buggy due to incorrect jump opcodes
do_execsql_test three-way-inner-join-with-two-seeks {
    select * from users u join users u2 on u.id=u2.id join products p on u2.id = p.id limit 3;
} {"1|Jamie|Foster|dylan00@example.com|496-522-9493|62375 Johnson Rest Suite 322|West Lauriestad|IL|35865|94|1|Jamie|Foster|dylan00@example.com|496-522-9493|62375 Johnson Rest Suite 322|West Lauriestad|IL|35865|94|1|hat|79.0
2|Cindy|Salazar|williamsrebecca@example.com|287-934-1135|75615 Stacey Shore|South Stephanie|NC|85181|37|2|Cindy|Salazar|williamsrebecca@example.com|287-934-1135|75615 Stacey Shore|South Stephanie|NC|85181|37|2|cap|82.0
3|Tommy|Perry|warechristopher@example.org|001-288-554-8139x0276|2896 Paul Fall Apt. 972|Michaelborough|VA|15691|18|3|Tommy|Perry|warechristopher@example.org|001-288-554-8139x0276|2896 Paul Fall Apt. 972|Michaelborough|VA|15691|18|3|shirt|18.0"}

do_execsql_test leftjoin-innerjoin-where {
  select u.first_name, p.name, p2.name from users u left join products p on p.name = u.first_name join products p2 on length(p2.name) > 8 where u.first_name = 'Franklin';
} {Franklin||sweatshirt
Franklin||accessories}

do_execsql_test leftjoin-leftjoin-where {
  select u.first_name, p.name, p2.name from users u left join products p on p.name = u.first_name join products p2 on length(p2.name) > 8 where u.first_name = 'Franklin';
} {Franklin||sweatshirt
Franklin||accessories}

do_execsql_test innerjoin-leftjoin-where {
  select u.first_name, u2.first_name, p.name from users u join users u2 on u.id = u2.id + 1 left join products p on p.name = u.first_name where u.first_name = 'Franklin';
} {Franklin|Cynthia|}

do_execsql_test innerjoin-leftjoin-with-or-terms {
  select u.first_name, u2.first_name, p.name from users u join users u2 on u.id = u2.id + 1 left join products p on p.name = u.first_name or p.name like 'sweat%' where u.first_name = 'Franklin';
} {Franklin|Cynthia|sweater
Franklin|Cynthia|sweatshirt}

do_execsql_test left-join-constant-condition-false-inner-join-constant-condition-true {
    select u.first_name, p.name, u2.first_name from users u left join products as p on 0 join users u2 on 1 limit 5;
} {Jamie||Jamie
Jamie||Cindy
Jamie||Tommy
Jamie||Jennifer
Jamie||Edward}

do_execsql_test left-join-constant-condition-true-inner-join-constant-condition-false {
    select u.first_name, p.name, u2.first_name from users u left join products as p on 1 join users u2 on 0 limit 5;
} {}

do_execsql_test join-utilizing-both-seekrowid-and-secondary-index {
    select u.first_name, p.name from users u join products p on u.id = p.id and u.age > 70;
} {Matthew|boots
Nicholas|shorts
Jamie|hat}

# important difference between regular SELECT * join and a SELECT * USING join is that the join keys are deduplicated
# from the result in the USING case.
do_execsql_test join-using {
    select * from users join products using (id) limit 3;
} {"1|Jamie|Foster|dylan00@example.com|496-522-9493|62375 Johnson Rest Suite 322|West Lauriestad|IL|35865|94|hat|79.0
2|Cindy|Salazar|williamsrebecca@example.com|287-934-1135|75615 Stacey Shore|South Stephanie|NC|85181|37|cap|82.0
3|Tommy|Perry|warechristopher@example.org|001-288-554-8139x0276|2896 Paul Fall Apt. 972|Michaelborough|VA|15691|18|shirt|18.0"}

do_execsql_test join-using-multiple {
    select u.first_name, u.last_name, p.name from users u join users u2 using(id) join products p using(id) limit 3;
} {"Jamie|Foster|hat
Cindy|Salazar|cap
Tommy|Perry|shirt"}

do_execsql_test join-using-multiple-with-quoting {
    select u.first_name, u.last_name, p.name from users u join users u2 using(id) join [products] p using(`id`) limit 3;
} {"Jamie|Foster|hat
Cindy|Salazar|cap
Tommy|Perry|shirt"}

# NATURAL JOIN desugars to JOIN USING (common_column1, common_column2...)
do_execsql_test join-using {
    select * from users natural join products limit 3;
} {"1|Jamie|Foster|dylan00@example.com|496-522-9493|62375 Johnson Rest Suite 322|West Lauriestad|IL|35865|94|hat|79.0
2|Cindy|Salazar|williamsrebecca@example.com|287-934-1135|75615 Stacey Shore|South Stephanie|NC|85181|37|cap|82.0
3|Tommy|Perry|warechristopher@example.org|001-288-554-8139x0276|2896 Paul Fall Apt. 972|Michaelborough|VA|15691|18|shirt|18.0"}

do_execsql_test natural-join-multiple {
    select u.first_name, u2.last_name, p.name from users u natural join users u2 natural join products p limit 3;
} {"Jamie|Foster|hat
Cindy|Salazar|cap
Tommy|Perry|shirt"}

# have to be able to join between 1st table and 3rd table as well
do_execsql_test natural-join-and-using-join {
    select u.id, u2.id, p.id from users u natural join products p join users u2 using (first_name) limit 3;
} {"1|1|1
1|1204|1
1|1261|1"}

# regression test for a backwards iteration left join case,
# where the null flag of the right table was not cleared after a previous unmatched row.
do_execsql_test left-join-backwards-iteration {
    select users.id, users.first_name as user_name, products.name as product_name 
    from users left join products on users.id = products.id 
    where users.id < 13 order by users.id desc limit 3;
} {12|Alan|
11|Travis|accessories
10|Daniel|coat}

# regression test for issue 2794: not nulling out rowid properly when left join does not match
do_execsql_test_on_specific_db {:memory:} min-null-regression-test {
    create table t (x integer primary key, y);
    create table u (x integer primary key, y);
    insert into t values (1,1),(2,2);
    insert into u values (1,1),(3,3);
    select count(u.x) from t left join u using(y);
} {1}

# regression test for issue 2949: consuming WHERE terms not originating from the LEFT JOIN
do_execsql_test_on_specific_db {:memory:} left-join-seek-key-regression-test {
  CREATE TABLE t (x INTEGER PRIMARY KEY);
  CREATE TABLE u (x INTEGER PRIMARY KEY);
  INSERT INTO t VALUES (1);
  SELECT * FROM t LEFT JOIN u ON false WHERE u.x = 1; 
} {}

# regression test for issue 2924: calling Next on a cursor that hasn't moved yet
do_execsql_test_on_specific_db {:memory:} next-crash {
  create table a(x int primary key,y);
  create table b(x int primary key,y);
  create table c(x int primary key,y);
  insert into a values (1,1),(2,2);
  select a.x, b.x, c.x from a left join b on a.y=b.x left join c on b.y=c.x;
} {1||
2||}

# regression test for crash in op_column
do_execsql_test_on_specific_db {:memory:} left-join-column-crash {
    create table a(x int primary key,y);
    create table b(x int primary key,y);
    insert into a values (1,1),(2,2);
    insert into b values (3,3),(4,4);
    select * from a left join b on a.x < 2 where a.x < 3 and b.x < 12;
} {1|1|3|3
1|1|4|4}

# regression test for ignoring ON clause when LEFT JOIN uses a FROM clause subquery
do_execsql_test_on_specific_db {:memory:} left-join-subquery-on {
  CREATE TABLE t1(a INTEGER);
  INSERT INTO t1 VALUES (1),(2),(3);
  CREATE TABLE t2(b INTEGER);
  INSERT INTO t2 VALUES (2),(4);
  SELECT a, b FROM t1 LEFT JOIN (SELECT * FROM t2 WHERE b > 2) ON t1.a = b ORDER BY a;
} {1|
2|
3|}

# Test that column names in USING clause are not ambiguous since they are deduplicated.
# The column 'a' appears in both tables but can be referenced unqualified in the SELECT
# since it's in the USING clause.
do_execsql_test_on_specific_db {:memory:} using-deduplicates-columns {
    create table t(a);
    create table tt(a); 
    insert into t values (1),(2),(3),(4),(5);
    insert into tt values (4),(5),(6),(7),(8);
    select a from t join tt using(a);
} {4
5}

# regression test for https://github.com/tursodatabase/turso/issues/3470
do_execsql_test_on_specific_db {:memory:} left-join-where-clause-regression {
    create table t(a);
    create table s(a);
    insert into t values (1), (2);
    insert into s values (2);
    select t.a, s.a from t left join s on t.a=s.a where t.a = 2;
    select t.a, s.a from t left join s on t.a=s.a where s.a = 2;
} {2|2
2|2}

# Regression test for: https://github.com/tursodatabase/turso/issues/3468
do_execsql_test_on_specific_db {:memory:} left-join-using-star-vs-explicit {
    create table t(a, tb);
    create table s(a, sb);
    insert into t values (1, 't1'), (2, 't2');
    insert into s values (1, 's1'), (3, 's3');
    select * from t left join s using(a);
    select a, tb, sb from t left join s using(a);
} {1|t1|s1
2|t2|
1|t1|s1
2|t2|}

do_execsql_test_on_specific_db {:memory:} left-join-using-null {
    create table t(a, b);
    create table s(a, b);
    insert into t values (1, null), (2, null); 
    insert into s values (1, null), (2, null); 
    select a, b from t left join s using (a, b);
} {1|
2|}

# Regression test for: https://github.com/tursodatabase/turso/issues/3656
do_execsql_test_on_specific_db {:memory:} redundant-join-condition {
    create table t(x);
    insert into t values ('lol');
    select t1.x from t t1 join t t2 on t1.x=t2.x where t1.x=t2.x;
} {lol}