limbo/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|}

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;
} {}

if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} {
    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}
} else {
    # without index experimental the order is different since we don't use indexes
    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;
    } {Jamie|hat
    Nicholas|shorts
    Matthew|boots}

}

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