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

# Regression test for: https://github.com/tursodatabase/turso/issues/4098
do_execsql_test_on_specific_db {:memory:} left-join-duplicate-column-constraints {
    CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT, c INT);
    CREATE TABLE t2(id INTEGER PRIMARY KEY, a INT, c INT);
    CREATE TABLE t3(id INTEGER PRIMARY KEY, b INT);
    INSERT INTO t1 VALUES (1, 17, 20);
    INSERT INTO t2 VALUES (1, 17, 20);
    INSERT INTO t3 VALUES (1, 17);
    SELECT t1.a, t2.a, t2.c, t3.b FROM t1 LEFT JOIN t2 ON t1.a = t2.a AND t1.c = t2.c LEFT JOIN t3 ON t2.a = t3.b WHERE t2.a = 17;
} {17|17|20|17}

do_execsql_test_on_specific_db {:memory:} test-spill-hash-join-count {
	CREATE TABLE t(a,b);
	CREATE TABLE t2(a,b);
	-- insert 150 rows @ 10kb/ea into each table to ensure the hash table we build is much large enough to trigger spilling
	INSERT INTO t SELECT replace(substr(quote(zeroblob((1024*10 + 1) / 2)), 3, 1024*10), '0', 'a'), 'abcdef' FROM generate_series(1,150);
	INSERT INTO t2 SELECT replace(substr(quote(zeroblob((1024*10 + 1) / 2)), 3,1024*10), '0', 'a'), 'abcdef' FROM generate_series(1,150);
	-- uses hash join because we have no indexes on either column
    SELECT count(*) FROM t JOIN t2 ON t.a = t2.a;
} {22500}

do_execsql_test_on_specific_db {:memory:} test-spill-hash-join-rows {
	CREATE TABLE t(a,b);
	CREATE TABLE t2(a,b);
	-- insert 150 rows @ 10kb/ea into each table to ensure the hash table we build is much large enough to trigger spilling
	INSERT INTO t SELECT replace(substr(quote(zeroblob((1024*10 + 1) / 2)), 3, 1024*10), '0', 'a'), 'abcdef' FROM generate_series(1,150);
	INSERT INTO t2 SELECT replace(substr(quote(zeroblob((1024*10 + 1) / 2)), 3,1024*10), '0', 'a'), 'abcdef' FROM generate_series(1,150);
	-- uses hash join because we have no indexes on either column
    SELECT substr(t.a, 1,4), substr(t2.b, 1, 4) FROM t JOIN t2 ON t.a = t2.a limit 2;
} {aaaa|abcd
aaaa|abcd}

do_execsql_test hash-join-expr-equality-pred {
    SELECT p.name, u.email
    FROM products p
    JOIN users u ON substr(p.name,1,2) = lower(substr(u.first_name, 1,2))
    ORDER BY p.name, u.email limit 2;
} {boots|agiles@example.net
boots|bryantrevino@example.com}

do_execsql_test_on_specific_db {:memory:} inner-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 join s using (a, b);
} {}

do_execsql_test_on_specific_db {:memory:} left-join-null-probe-key {
  create table t(a, b);
  create table s(a, b);
  insert into t values (1, 'x'), (null, 'y');
  insert into s values (1, 'x');
  select t.a, t.b, s.a, s.b
    from t
    left join s on t.a = s.a and t.b = s.b
   order by t.rowid;
} {1|x|1|x
|y||}

do_execsql_test_on_specific_db {:memory:} three-way-left-chain-null {
  create table t(a, b);
  create table u(a, b);
  create table v(a, b);

  insert into t values (1, 't1'), (2, 't2'), (3, null);
  insert into u values (1, 'u1'), (2, null);
  insert into v values (1, 'v1'), (2, 'v2'), (3, 'v3');

  select t.a, t.b, u.b, v.b
    from t
    left join u on t.a = u.a and t.b = u.b
    left join v on t.a = v.a
   order by t.a;
} {1|t1||v1
2|t2||v2
3|||v3}

do_execsql_test_on_specific_db {:memory:} three-way-inner-using-null {
  create table t(a, b);
  create table u(a, b);
  create table v(a, b);

  insert into t values (1, 'x'), (2, null), (3, 'z');
  insert into u values (1, 'x'), (2, null), (3, 'z');
  insert into v values (1, 'x'), (2, null), (3, 'z');

  select a, b, '|' as sep
    from t
    join u using(a, b)
    join v using(a, b)
   order by a;
} {1|x||
3|z||}

do_execsql_test_on_specific_db {:memory:} four-way-left-joins-null-keys {
  create table t(a, b);
  create table u(a, b);
  create table v(a, b);
  create table w(a, b);

  insert into t values (1, 't1'), (2, null);
  insert into u values (1, 't1'), (2, 'u2');
  insert into v values (1, 'v1'), (2, null);
  insert into w values (1, 'w1');

  select t.a, t.b, u.b, v.b, w.b
    from t
    left join u on t.a = u.a and t.b = u.b
    left join v on t.a = v.a and t.b = v.b
    left join w on t.a = w.a
   order by t.a;
} {1|t1|t1||w1
2||||}

do_execsql_test_on_specific_db {:memory:} left-join-integer-real {
  CREATE TABLE IF NOT EXISTS t1 (a INTEGER, b INTEGER);
  CREATE TABLE IF NOT EXISTS t2 (a INTEGER, c REAL);
  INSERT INTO t1 (a, b) VALUES (1, NULL), (2, 10);
  INSERT INTO t2 (a, c) VALUES (1, 10.0), (3, NULL);
  SELECT * FROM t1 LEFT JOIN t2 ON t1.b = t2.c;
} {1|||
2|10|1|10.0}

do_execsql_test_on_specific_db {:memory:} left-join-real-integer {
  CREATE TABLE IF NOT EXISTS t3 (a INTEGER, b REAL);
  CREATE TABLE IF NOT EXISTS t4 (a INTEGER, c INTEGER);
  INSERT INTO t3 (a, b) VALUES (1, NULL), (2, 10.0);
  INSERT INTO t4 (a, c) VALUES (1, 10), (3, NULL);
  SELECT * FROM t3 LEFT JOIN t4 ON t3.b = t4.c;
} {1|||
2|10.0|1|10}

do_execsql_test_on_specific_db {:memory:} inner-join-integer-real {
  CREATE TABLE IF NOT EXISTS t5 (a INTEGER, b INTEGER);
  CREATE TABLE IF NOT EXISTS t6 (a INTEGER, c REAL);
  INSERT INTO t5 (a, b) VALUES (1, 5), (2, 10), (3, 15);
  INSERT INTO t6 (a, c) VALUES (1, 10.0), (2, 20.0), (3, 5.0);
  SELECT t5.a, t5.b, t6.a, t6.c FROM t5 INNER JOIN t6 ON t5.b = t6.c ORDER BY t5.a;
} {1|5|3|5.0
2|10|1|10.0}

do_execsql_test_on_specific_db {:memory:} left-join-integer-real-zero {
  CREATE TABLE IF NOT EXISTS t7 (a INTEGER, b INTEGER);
  CREATE TABLE IF NOT EXISTS t8 (a INTEGER, c REAL);
  INSERT INTO t7 (a, b) VALUES (1, 0);
  INSERT INTO t8 (a, c) VALUES (1, 0.0);
  SELECT * FROM t7 LEFT JOIN t8 ON t7.b = t8.c;
} {1|0|1|0.0}

do_execsql_test_on_specific_db {:memory:} left-join-integer-real-negative {
  CREATE TABLE IF NOT EXISTS t9 (a INTEGER, b INTEGER);
  CREATE TABLE IF NOT EXISTS t10 (a INTEGER, c REAL);
  INSERT INTO t9 (a, b) VALUES (1, -10);
  INSERT INTO t10 (a, c) VALUES (1, -10.0);
  SELECT * FROM t9 LEFT JOIN t10 ON t9.b = t10.c;
} {1|-10|1|-10.0}

do_execsql_test_on_specific_db {:memory:} left-join-integer-real-no-match {
  CREATE TABLE IF NOT EXISTS t11 (a INTEGER, b INTEGER);
  CREATE TABLE IF NOT EXISTS t12 (a INTEGER, c REAL);
  INSERT INTO t11 (a, b) VALUES (1, 10);
  INSERT INTO t12 (a, c) VALUES (1, 10.5);
  SELECT * FROM t11 LEFT JOIN t12 ON t11.b = t12.c;
} {1|10||}

do_execsql_test_on_specific_db {:memory:} left-join-integer-real-multi-key {
  CREATE TABLE IF NOT EXISTS t13 (a INTEGER, b INTEGER, c INTEGER);
  CREATE TABLE IF NOT EXISTS t14 (a INTEGER, b REAL, c REAL);
  INSERT INTO t13 (a, b, c) VALUES (1, 10, 20);
  INSERT INTO t14 (a, b, c) VALUES (1, 10.0, 20.0);
  SELECT * FROM t13 LEFT JOIN t14 ON t13.b = t14.b AND t13.c = t14.c;
} {1|10|20|1|10.0|20.0}

do_execsql_test_on_specific_db {:memory:} affinity-hash-join {
  CREATE TABLE x(a INTEGER);
  CREATE TABLE y(b TEXT);
  INSERT INTO x VALUES (2),(3);
  INSERT INTO y VALUES ('02'),('2'),('2.0'),('3x'),('3.5');
  
  SELECT a, b
  FROM x JOIN y ON a = b
  ORDER BY a, b;
} {
  {2|02}
  {2|2}
  {2|2.0}
}