mirror of
https://github.com/tursodatabase/limbo.git
synced 2025-08-04 01:58:16 +00:00
83c509a613
In left joins, even if the join condition is not matched, the system must emit a row for every row of the outer table: -- this must return t1.count() rows, with NULLs for all columns of t2 SELECT * FROM t1 LEFT JOIN t2 ON FALSE; Our logic for clearing the null flag was to do it in Next/Prev. However, this is problematic for a few reasons: - If the inner table of the left join is using SeekRowid, then Next/Prev is never called on its cursor, so the null flag doesn't get cleared. - If the inner table of the left join is using a non-covering index seek, i.e. it iterates its rows using an index, but seeks to the main table to fetch data, then Next/Prev is never called on the main table, and the main table's null flag doesn't get cleared. What this results in is NULL values incorrectly being emitted for the inner table after the first correct NULL row, since the null flag is correctly set to true, but never cleared. This PR fixes the issue by clearing the null flag whenever seek() is invoked on the cursor. Hence, the null flag is now cleared on: - next() - prev() - seek()
285 lines
No EOL
11 KiB
Tcl
Executable file
285 lines
No EOL
11 KiB
Tcl
Executable file
#!/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;
|
|
} {}
|
|
|
|
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} |