# 6 pts

statement ok
set force_optimizer_starter_rule=yes

statement ok
create table temp_1(colA int, colB int, colC int, colD int);

statement ok
insert into temp_1 values
    (0 , 1, 6113, 48270),
    (1 , 5, 8005, 22497),
    (2 , 0, 4476, 73541),
    (9 , 2, 2600, 32938),
    (10, 8, 398 , 60977),
    (11, 1, 5472, 33166),
    (12, 3, 1107, 94947),
    (13, 0, 4153, 54624),
    (14, 3, 1907, 48515),
    (15, 6, 9754, 95830),
    (16, 8, 5252, 96696),
    (3 , 2, 290 , 20860),
    (4 , 0, 3362, 47696),
    (5 , 8, 7022, 59586),
    (6 , 2, 170 , 92798),
    (7 , 2, 8524, 19746),
    (8 , 8, 3074, 56174),
    (17, 3, 4819, 99411),
    (18, 9, 666 , 86073),
    (19, 3, 701 , 34675),
    (20, 2, 2307, 97170),
    (28, 5, 8710, 194  ),
    (29, 5, 5519, 4256 ),
    (30, 0, 3803, 18357),
    (31, 6, 219 , 2090 ),
    (32, 2, 3842, 38160),
    (33, 8, 991 , 24527),
    (34, 4, 1443, 41478),
    (35, 2, 7564, 14242),
    (21, 4, 194 , 42522),
    (22, 3, 4256, 54404),
    (23, 7, 6037, 70355),
    (24, 6, 1973, 98970),
    (25, 3, 2090, 701  ),
    (26, 2, 5392, 94519),
    (27, 0, 8143, 47916),
    (36, 6, 2269, 54671),
    (37, 3, 3582, 77724),
    (38, 2, 5267, 69339),
    (39, 0, 1180, 94216),
    (40, 6, 3158, 16603),
    (41, 3, 4878, 85762),
    (42, 1, 34  , 95622),
    (43, 6, 9359, 1443 ),
    (44, 0, 2173, 31618),
    (45, 3, 9616, 67805),
    (46, 2, 2006, 92368),
    (47, 0, 6785, 75686),
    (48, 6, 2696, 19966),
    (49, 1, 3313, 70803),
    (50, 7, 4001, 99484),
    (51, 8, 9106, 28177),
    (52, 1, 8312, 52310),
    (58, 8, 483 , 86798),
    (59, 6, 9742, 59516),
    (60, 5, 4347, 34   ),
    (61, 2, 9484, 25743),
    (53, 1, 9567, 10624),
    (54, 2, 6866, 15963),
    (55, 1, 4956, 27755),
    (56, 1, 4060, 16227),
    (57, 9, 3683, 78077),
    (62, 2, 9506, 13806),
    (63, 2, 7758, 44219),
    (64, 2, 1999, 83926),
    (65, 3, 3886, 72321),
    (66, 3, 8020, 97035),
    (67, 0, 4633, 68232),
    (68, 4, 2699, 52465),
    (69, 8, 755 , 20385),
    (70, 2, 8311, 59841),
    (71, 4, 7826, 97071),
    (72, 7, 7027, 29049),
    (73, 9, 499 , 73848),
    (74, 1, 9088, 12950),
    (75, 3, 3517, 94485),
    (76, 7, 4339, 25951),
    (77, 2, 5801, 59170),
    (85, 9, 849 , 11512),
    (86, 3, 2697, 9484 ),
    (87, 2, 3060, 25890),
    (88, 2, 7329, 40526),
    (89, 1, 9418, 34767),
    (90, 9, 6826, 69422),
    (91, 1, 1411, 8020 ),
    (92, 9, 2110, 28695),
    (78, 3, 5737, 59426),
    (79, 3, 4299, 97298),
    (80, 3, 7683, 21340),
    (81, 0, 2045, 3683 ),
    (82, 8, 5290, 98787),
    (96, 1, 4733, 2699 ),
    (97, 1, 2273, 63790),
    (98, 3, 1394, 17139),
    (99, 6, 4482, 57463),
    (83, 3, 2914, 91662),
    (84, 9, 4626, 92192),
    (93, 4, 2758, 27498),
    (94, 5, 8802, 70169),
    (95, 9, 5706, 15441);


statement ok
create table temp_2(colA int, colB int, colC int);

statement ok
insert into temp_2 values
    (5, 290, 5),
    (6, 592, 6),
    (0, 142, 0),
    (1, 993, 1),
    (2, 837, 2),
    (3, 380, 3),
    (4, 240, 4),
    (7, 88 , 7),
    (8, 194, 8),
    (9, 638, 9),
    (10, 290, 0),
    (11, 878, 1),
    (12, 170, 2),
    (13, 332, 3),
    (14, 680, 4),
    (15, 2, 5),
    (16, 552, 6),
    (17, 561, 7),
    (18, 398, 8),
    (19, 974, 9),
    (20, 739, 0),
    (21, 352, 1),
    (53, 770, 3),
    (39, 160, 9),
    (40, 917, 0),
    (41, 949, 1),
    (36, 259, 6),
    (37, 812, 7),
    (38, 194, 8),
    (47, 399, 7),
    (48, 924, 8),
    (49, 731, 9),
    (50, 8  , 0),
    (51, 219, 1),
    (52, 276, 2),
    (55, 390, 5),
    (56, 419, 6),
    (57, 346, 7),
    (58, 898, 8),
    (59, 396, 9),
    (60, 221, 0),
    (61, 208, 1),
    (84, 711, 4),
    (85, 779, 5),
    (86, 648, 6),
    (87, 590, 7),
    (88, 241, 8),
    (89, 929, 9),
    (90, 914, 0),
    (91, 449, 1),
    (92, 815, 2),
    (93, 225, 3),
    (54, 991, 4),
    (22, 387, 2),
    (23, 598, 3),
    (24, 440, 4),
    (25, 83, 5),
    (26, 57, 6),
    (27, 883, 7),
    (28, 914, 8),
    (29, 538, 9),
    (30, 132, 0),
    (31, 723, 1),
    (62, 934, 2),
    (63, 510, 3),
    (64, 147, 4),
    (65, 156, 5),
    (66, 529, 6),
    (67, 86 , 7),
    (68, 384, 8),
    (69, 603, 9),
    (70, 107, 0),
    (71, 867, 1),
    (42, 42 , 2),
    (43, 272, 3),
    (44, 975, 4),
    (45, 518, 5),
    (46, 930, 6),
    (79, 125, 9),
    (80, 400, 0),
    (81, 187, 1),
    (82, 982, 2),
    (83, 641, 3),
    (94, 377, 4),
    (95, 120, 5),
    (96, 662, 6),
    (97, 277, 7),
    (98, 351, 8),
    (32, 666, 2),
    (33, 701, 3),
    (34, 311, 4),
    (35, 214, 5),
    (72, 253, 2),
    (73, 782, 3),
    (74, 124, 4),
    (75, 879, 5),
    (76, 34 , 6),
    (77, 143, 7),
    (78, 494, 8),
    (99, 722, 9);

statement ok
create table temp_3(colA int, colB int);

statement ok
insert into temp_3 values
    (5, 29),
    (6, 52),
    (0, 142),
    (1, 993),
    (2, 837),
    (3, 52),
    (4, 240),
    (7, 88),
    (8, 14),
    (9, 52),
    (10, 290),
    (11, 878),
    (12, 17),
    (13, 52),
    (14, 680),
    (15, 2),
    (16, 52),
    (17, 561),
    (18, 38),
    (19, 974),
    (20, 739);

statement ok
create index temp_1_colA on temp_1(colA);

statement ok
create index temp_2_colA on temp_2(colA);

query rowsort +ensure:index_join
select * from __mock_table_1 m1 inner join temp_1 t1 on t1.colA = m1.colB;
----
0 0 0 1 6113 48270


query rowsort +ensure:index_join
select * from temp_3 t3 inner join temp_2 t2 on t3.colB = t2.colA;
----
5 29 29 538 9
6 52 52 276 2
3 52 52 276 2
7 88 88 241 8
8 14 14 680 4
9 52 52 276 2
12 17 17 561 7
13 52 52 276 2
15 2 2 837 2
16 52 52 276 2
18 38 38 194 8



# Left join
statement ok
create table t1(v1 int);

statement ok
insert into t1 (select * from __mock_table_123);

statement ok
create index t1_v1 on t1(v1);

query rowsort +ensure:index_join
select * from test_simple_seq_2 t2 left join t1 on t1.v1 = t2.col1;
----
0 10 integer_null
1 11 1
2 12 2
3 13 3
4 14 integer_null
5 15 integer_null
6 16 integer_null
7 17 integer_null
8 18 integer_null
9 19 integer_null


# Repeat
query rowsort +ensure:index_join
select * from
    temp_2 t2 inner join
    (select distinct(temp_2.colA) from temp_1 inner join temp_2 on temp_1.colC = temp_2.colA) t3
    on t2.colB  < t3.colA;
----
15 2 5 34
50 8 0 34