using Test, InMemoryDatasets, Random, CategoricalArrays, PooledArrays

const ≅ = isequal

DATE(x) = Date(x)

DATE(::Missing) = missing

isequal_coltyped(ds1::AbstractDataset, ds2::AbstractDataset) =

isequal(ds1, ds2) && typeof.(eachcol(ds1)) == typeof.(eachcol(ds2))

name = Dataset(ID = Union{Int, Missing}[1, 2, 3],

Name = Union{String, Missing}["John Doe", "Jane Doe", "Joe Blogs"])

job = Dataset(ID = Union{Int, Missing}[1, 2, 2, 4],

Job = Union{String, Missing}["Lawyer", "Doctor", "Florist", "Farmer"])

# Test output of various join types

outer = Dataset(ID = [1, 2, 2, 3, 4],

Name = ["John Doe", "Jane Doe", "Jane Doe", "Joe Blogs", missing],

Job = ["Lawyer", "Doctor", "Florist", missing, "Farmer"])

# (Tests use current column ordering but don't promote it)

right = outer[Bool[!ismissing(x) for x in outer.Job], [:ID, :Name, :Job]]

left = outer[Bool[!ismissing(x) for x in outer.Name], :]

inner = left[Bool[!ismissing(x) for x in left.Job], :]

semi = unique(inner[:, [:ID, :Name]])

anti = left[Bool[ismissing(x) for x in left.Job], [:ID, :Name]]

classA = Dataset(id = ["id1", "id2", "id3", "id4", "id5"],

mark = [50, 69.5, 45.5, 88.0, 98.5])

grades = Dataset(mark = [0, 49.5, 59.5, 69.5, 79.5, 89.5, 95.5],

grade = ["F", "P", "C", "B", "A-", "A", "A+"])

closeone = Dataset(id = ["id1", "id2", "id3", "id4", "id5"],

mark = [50, 69.5, 45.5, 88.0, 98.5],

grade = ["P", "B", "F", "A-", "A+"])

trades = Dataset(

[["20160525 13:30:00.023",

"20160525 13:30:00.038",

"20160525 13:30:00.048",

"20160525 13:30:00.048",

"20160525 13:30:00.048"],

["MSFT", "MSFT",

"GOOG", "GOOG", "AAPL"],

[51.95, 51.95,

720.77, 720.92, 98.00],

[75, 155,

100, 100, 100]],

["time", "ticker", "price", "quantity"]);

modify!(trades, 1 => byrow(x -> DateTime(x, dateformat"yyyymmdd HH:MM:SS.s")));

quotes = Dataset(

[["20160525 13:30:00.023",

"20160525 13:30:00.023",

"20160525 13:30:00.030",

"20160525 13:30:00.041",

"20160525 13:30:00.048",

"20160525 13:30:00.049",

"20160525 13:30:00.072",

"20160525 13:30:00.075"],

["GOOG", "MSFT", "MSFT", "MSFT",

"GOOG", "AAPL", "GOOG", "MSFT"],

[720.50, 51.95, 51.97, 51.99,

720.50, 97.99, 720.50, 52.01],

[720.93, 51.96, 51.98, 52.00,

720.93, 98.01, 720.88, 52.03]],

["time", "ticker", "bid", "ask"]);

modify!(quotes, 1 => byrow(x -> DateTime(x, dateformat"yyyymmdd HH:MM:SS.s")));

closefinance1 = Dataset([Union{Missing, DateTime}[DateTime("2016-05-25T13:30:00.023"), DateTime("2016-05-25T13:30:00.038"), DateTime("2016-05-25T13:30:00.048"), DateTime("2016-05-25T13:30:00.048"), DateTime("2016-05-25T13:30:00.048")],

Union{Missing, String}["MSFT", "MSFT", "GOOG", "GOOG", "AAPL"],

Union{Missing, Float64}[51.95, 51.95, 720.77, 720.92, 98.0],

Union{Missing, Int64}[75, 155, 100, 100, 100],

Union{Missing, String}["MSFT", "MSFT", "GOOG", "GOOG", "GOOG"],

Union{Missing, Float64}[51.95, 51.97, 720.5, 720.5, 720.5],

Union{Missing, Float64}[51.96, 51.98, 720.93, 720.93, 720.93]],["time", "ticker", "price", "quantity", "ticker_1", "bid", "ask"])

closfinance_tol2ms = Dataset([Union{Missing, DateTime}[DateTime("2016-05-25T13:30:00.023"), DateTime("2016-05-25T13:30:00.038"), DateTime("2016-05-25T13:30:00.048"), DateTime("2016-05-25T13:30:00.048"), DateTime("2016-05-25T13:30:00.048")], Union{Missing, String}["MSFT", "MSFT", "GOOG", "GOOG", "AAPL"], Union{Missing, Float64}[51.95, 51.95, 720.77, 720.92, 98.0], Union{Missing, Int64}[75, 155, 100, 100, 100], Union{Missing, Float64}[51.95, missing, 720.5, 720.5, missing], Union{Missing, Float64}[51.96, missing, 720.93, 720.93, missing]], ["time", "ticker", "price", "quantity", "bid", "ask"])

closfinance_tol0ms = Dataset([Union{Missing, DateTime}[DateTime("2016-05-25T13:30:00.023"), DateTime("2016-05-25T13:30:00.038"), DateTime("2016-05-25T13:30:00.048"), DateTime("2016-05-25T13:30:00.048"), DateTime("2016-05-25T13:30:00.048")], Union{Missing, String}["MSFT", "MSFT", "GOOG", "GOOG", "AAPL"], Union{Missing, Float64}[51.95, 51.95, 720.77, 720.92, 98.0], Union{Missing, Int64}[75, 155, 100, 100, 100], Union{Missing, Float64}[missing, missing, missing, missing, missing], Union{Missing, Float64}[missing, missing, missing, missing, missing]], ["time", "ticker", "price", "quantity", "bid", "ask"])

closefinance_tol10ms_noexact = Dataset([Union{Missing, DateTime}[DateTime("2016-05-25T13:30:00.023"), DateTime("2016-05-25T13:30:00.038"), DateTime("2016-05-25T13:30:00.048"), DateTime("2016-05-25T13:30:00.048"), DateTime("2016-05-25T13:30:00.048")], Union{Missing, String}["MSFT", "MSFT", "GOOG", "GOOG", "AAPL"], Union{Missing, Float64}[51.95, 51.95, 720.77, 720.92, 98.0], Union{Missing, Int64}[75, 155, 100, 100, 100], Union{Missing, Float64}[missing, 51.97, missing, missing, missing], Union{Missing, Float64}[missing, 51.98, missing, missing, missing]], ["time", "ticker", "price", "quantity", "bid", "ask"])

@testset "general usage" begin

# Join on symbols or vectors of symbols

innerjoin(name, job, on = :ID)

innerjoin(name, job, on = [:ID])

@test_throws ArgumentError innerjoin(name, job)

@test_throws MethodError innerjoin(name, job, on = :ID, matchmissing=:errors)

@test_throws MethodError outerjoin(name, job, on = :ID, matchmissing=:notequal)

@test innerjoin(name, job, on = :ID) == inner == innerjoin(name, job, on = :ID, method = :hash)

@test outerjoin(name, job, on = :ID) == outer == outerjoin(name, job, on = :ID, method = :hash)

@test leftjoin(name, job, on = :ID) == left == leftjoin(name, job, on = :ID, method = :hash)

@test semijoin(name, job, on = :ID) == semi == semijoin(name, job, on = :ID, method = :hash)

@test antijoin(name, job, on = :ID) == anti == antijoin(name, job, on = :ID, method = :hash)

@test closejoin(classA, grades, on = :mark) == closeone == closejoin(classA, grades, on = :mark, method = :hash)

@test closejoin(trades, quotes, on = :time, makeunique = true) == closefinance1 == closejoin(trades, quotes, on = :time, makeunique = true, method = :hash)

@test innerjoin(name, job, on = :ID) == inner == innerjoin(name, job, on = :ID, threads = false)

@test innerjoin(name, job, on = :ID) == inner == innerjoin(name, job, on = :ID, method = :hash, threads = false)

@test outerjoin(name, job, on = :ID) == outer == outerjoin(name, job, on = :ID, threads = false)

@test outerjoin(name, job, on = :ID) == outer == outerjoin(name, job, on = :ID, method = :hash, threads = false)

@test leftjoin(name, job, on = :ID) == left == leftjoin(name, job, on = :ID, threads = false)

@test leftjoin(name, job, on = :ID) == left == leftjoin(name, job, on = :ID, method = :hash, threads = false)

@test semijoin(name, job, on = :ID) == semi == semijoin(name, job, on = :ID, threads = false)

@test semijoin(name, job, on = :ID) == semi == semijoin(name, job, on = :ID, method = :hash, threads = false)

@test antijoin(name, job, on = :ID) == anti == antijoin(name, job, on = :ID, threads = false)

@test antijoin(name, job, on = :ID) == anti == antijoin(name, job, on = :ID, method = :hash, threads = false)

@test closejoin(classA, grades, on = :mark) == closeone == closejoin(classA, grades, on = :mark, threads = false)

@test closejoin(classA, grades, on = :mark) == closeone == closejoin(classA, grades, on = :mark, method = :hash, threads = false)

@test closejoin(trades, quotes, on = :time, makeunique = true) == closefinance1 == closejoin(trades, quotes, on = :time, makeunique = true, threads = false)

@test closejoin(trades, quotes, on = :time, makeunique = true) == closefinance1 == closejoin(trades, quotes, on = :time, makeunique = true, method = :hash, threads = false)

@test innerjoin(name, view(job, :, :), on = :ID) == inner

@test outerjoin(name, view(job, :, :), on = :ID) == outer

@test leftjoin(name, view(job, :, :), on = :ID) == left

@test semijoin(name, view(job, :, :), on = :ID) == semi

@test antijoin(name, view(job, :, :), on = :ID) == anti

@test closejoin(classA, view(grades, :, :), on = :mark) == closeone

@test closejoin(trades, view(quotes, :, :), on = :time, makeunique = true) == closefinance1

@test innerjoin(name, view(job, :, :), on = :ID, method = :hash) == inner

@test outerjoin(name, view(job, :, :), on = :ID, method = :hash) == outer

@test leftjoin(name, view(job, :, :), on = :ID, method = :hash) == left

@test semijoin(name, view(job, :, :), on = :ID, method = :hash) == semi

@test antijoin(name, view(job, :, :), on = :ID, method = :hash) == anti

@test closejoin(classA, view(grades, :, :), on = :mark, method = :hash) == closeone

@test closejoin(trades, view(quotes, :, :), on = :time, makeunique = true, method = :hash) == closefinance1

@test innerjoin(name, view(job, :, :), on = :ID, threads = false) == inner

@test innerjoin(name, view(job, :, :), on = :ID, method = :hash, threads = false) == inner

@test outerjoin(name, view(job, :, :), on = :ID, threads = false) == outer

@test outerjoin(name, view(job, :, :), on = :ID, method = :hash, threads = false) == outer

@test leftjoin(name, view(job, :, :), on = :ID, threads = false) == left

@test leftjoin(name, view(job, :, :), on = :ID, method = :hash, threads = false) == left

@test semijoin(name, view(job, :, :), on = :ID, threads = false) == semi

@test semijoin(name, view(job, :, :), on = :ID, method = :hash, threads = false) == semi

@test antijoin(name, view(job, :, :), on = :ID, threads = false) == anti

@test antijoin(name, view(job, :, :), on = :ID, method = :hash, threads = false) == anti

@test closejoin(classA, view(grades, :, :), on = :mark, threads = false) == closeone

@test closejoin(classA, view(grades, :, :), on = :mark, method = :hash, threads = false) == closeone

@test closejoin(trades, view(quotes, :, :), on = :time, makeunique = true, threads = false) == closefinance1

@test closejoin(trades, view(quotes, :, :), on = :time, makeunique = true, method = :hash, threads = false) == closefinance1

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Millisecond(2)) == closfinance_tol2ms

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Day(2)) == closejoin(trades, quotes, on =[:ticker, :time])

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Millisecond(0)) == closfinance_tol0ms

@test closejoin(trades, quotes, on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false) == closefinance_tol10ms_noexact

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Millisecond(2)) == closfinance_tol2ms

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Day(2)) == closejoin(trades, quotes, on =[:ticker, :time])

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Millisecond(0)) == closfinance_tol0ms

@test closejoin!(copy(trades), quotes, on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false) == closefinance_tol10ms_noexact

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Millisecond(2), method = :hash) == closfinance_tol2ms

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Day(2), method = :hash) == closejoin(trades, quotes, on =[:ticker, :time])

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Millisecond(0), method = :hash) == closfinance_tol0ms

@test closejoin(trades, quotes, on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, method = :hash) == closefinance_tol10ms_noexact

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Millisecond(2), method = :hash) == closfinance_tol2ms

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Day(2), method = :hash) == closejoin(trades, quotes, on =[:ticker, :time])

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Millisecond(0), method = :hash) == closfinance_tol0ms

@test closejoin!(copy(trades), quotes, on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, method = :hash) == closefinance_tol10ms_noexact

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Millisecond(2),threads = false) == closfinance_tol2ms

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Millisecond(2),method = :hash, threads = false) == closfinance_tol2ms

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Day(2),threads = false) == closejoin(trades, quotes, on =[:ticker, :time])

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Day(2),method = :hash, threads = false) == closejoin(trades, quotes, on =[:ticker, :time])

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Millisecond(0),threads = false) == closfinance_tol0ms

@test closejoin(trades, quotes, on =[:ticker, :time], tol = Millisecond(0),method = :hash, threads = false) == closfinance_tol0ms

@test closejoin(trades, quotes, on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false,threads = false) == closefinance_tol10ms_noexact

@test closejoin(trades, quotes, on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false,method = :hash, threads = false) == closefinance_tol10ms_noexact

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Millisecond(2),threads = false) == closfinance_tol2ms

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Millisecond(2),method = :hash, threads = false) == closfinance_tol2ms

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Day(2),threads = false) == closejoin(trades, quotes, on =[:ticker, :time])

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Day(2),method = :hash, threads = false) == closejoin(trades, quotes, on =[:ticker, :time])

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Millisecond(0),threads = false) == closfinance_tol0ms

@test closejoin!(copy(trades), quotes, on =[:ticker, :time], tol = Millisecond(0),method = :hash, threads = false) == closfinance_tol0ms

@test closejoin!(copy(trades), quotes, on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false,threads = false) == closefinance_tol10ms_noexact

@test closejoin!(copy(trades), quotes, on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false,method = :hash, threads = false) == closefinance_tol10ms_noexact

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2)) == closfinance_tol2ms

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Day(2)) == closejoin(trades, view(quotes, :, :), on =[:ticker, :time])

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0)) == closfinance_tol0ms

@test closejoin(trades, view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false) == closefinance_tol10ms_noexact

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2)) == closfinance_tol2ms

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Day(2)) == closejoin(trades, view(quotes, :, :), on =[:ticker, :time])

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0)) == closfinance_tol0ms

@test closejoin!(copy(trades), view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false) == closefinance_tol10ms_noexact

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash) == closfinance_tol2ms

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Day(2), method = :hash) == closejoin(trades, view(quotes, :, :), on =[:ticker, :time])

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), method = :hash) == closfinance_tol0ms

@test closejoin(trades, view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, method = :hash) == closefinance_tol10ms_noexact

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash) == closfinance_tol2ms

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Day(2), method = :hash) == closejoin(trades, view(quotes, :, :), on =[:ticker, :time])

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), method = :hash) == closfinance_tol0ms

@test closejoin!(copy(trades), view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, method = :hash) == closefinance_tol10ms_noexact

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), threads = false) == closfinance_tol2ms

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash, threads = false) == closfinance_tol2ms

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Day(2), threads = false) == closejoin(trades, view(quotes, :, :), on =[:ticker, :time])

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Day(2), method = :hash, threads = false) == closejoin(trades, view(quotes, :, :), on =[:ticker, :time])

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), threads = false) == closfinance_tol0ms

@test closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), method = :hash, threads = false) == closfinance_tol0ms

@test closejoin(trades, view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, threads = false) == closefinance_tol10ms_noexact

@test closejoin(trades, view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, method = :hash, threads = false) == closefinance_tol10ms_noexact

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), threads = false) == closfinance_tol2ms

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash, threads = false) == closfinance_tol2ms

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Day(2), threads = false) == closejoin(trades, view(quotes, :, :), on =[:ticker, :time])

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Day(2), method = :hash, threads = false) == closejoin(trades, view(quotes, :, :), on =[:ticker, :time])

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), threads = false) == closfinance_tol0ms

@test closejoin!(copy(trades), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), method = :hash, threads = false) == closfinance_tol0ms

@test closejoin!(copy(trades), view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, threads = false) == closefinance_tol10ms_noexact

@test closejoin!(copy(trades), view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, method = :hash, threads = false) == closefinance_tol10ms_noexact

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :sort) == closfinance_tol2ms

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Day(2), method = :sort) == closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time])

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), method = :sort) == closfinance_tol0ms

@test closejoin(view(trades, :, :), view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, method = :sort) == closefinance_tol10ms_noexact

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash) == closfinance_tol2ms

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Day(2), method = :hash) == closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time])

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), method = :hash) == closfinance_tol0ms

@test closejoin(view(trades, :, :), view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, method = :hash) == closefinance_tol10ms_noexact

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), threads = false) == closfinance_tol2ms

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash, threads = false) == closfinance_tol2ms

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Day(2), threads = false) == closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time])

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Day(2), method = :hash, threads = false) == closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time])

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), threads = false) == closfinance_tol0ms

@test closejoin(view(trades, :, :), view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(0), method = :hash, threads = false) == closfinance_tol0ms

@test closejoin(view(trades, :, :), view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, threads = false) == closefinance_tol10ms_noexact

@test closejoin(view(trades, :, :), view(quotes, :, :), on = [:ticker, :time], tol = Millisecond(10), allow_exact_match = false, method = :hash, threads = false) == closefinance_tol10ms_noexact

ANS = closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2))

@test ANS.price.val !== trades.price.val

cpy_trades = copy(trades)

closejoin!(cpy_trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2))

@test ANS.price.val !== trades.price.val

ANS = closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash)

ANS = closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), threads = false)

ANS = closejoin(trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash, threads = false)

@test ANS.price.val !== trades.price.val

cpy_trades = copy(trades)

closejoin!(cpy_trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash)

cpy_trades = copy(trades)

closejoin!(cpy_trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), threads = false)

cpy_trades = copy(trades)

closejoin!(cpy_trades, view(quotes, :, :), on =[:ticker, :time], tol = Millisecond(2), method = :hash, threads = false)

@test ANS.price.val !== trades.price.val

# Join with no non-key columns

on = [:ID]

nameid = name[:, on]

jobid = job[:, on]

@test innerjoin(nameid, jobid, on = :ID) == inner[:, on]

@test outerjoin(nameid, jobid, on = :ID) == outer[:, on]

@test leftjoin(nameid, jobid, on = :ID) == left[:, on]

@test semijoin(nameid, jobid, on = :ID) == semi[:, on]

@test antijoin(nameid, jobid, on = :ID) == anti[:, on]

@test innerjoin(nameid, view(jobid, :, :), on = :ID) == inner[:, on]

@test outerjoin(nameid, view(jobid, :, :), on = :ID) == outer[:, on]

@test leftjoin(nameid, view(jobid, :, :), on = :ID) == left[:, on]

@test innerjoin(view(nameid, :, :), view(jobid, :, :), on = :ID) == inner[:, on]

@test outerjoin(view(nameid, :, :), view(jobid, :, :), on = :ID) == outer[:, on]

@test leftjoin(view(nameid, :, :), view(jobid, :, :), on = :ID) == left[:, on]

@test semijoin(nameid, view(jobid, :, :), on = :ID) == semi[:, on]

@test antijoin(nameid, view(jobid, :, :), on = :ID) == anti[:, on]

@test innerjoin(nameid, jobid, on = :ID, method = :hash) == inner[:, on]

@test outerjoin(nameid, jobid, on = :ID, method = :hash) == outer[:, on]

@test leftjoin(nameid, jobid, on = :ID, method = :hash) == left[:, on]

@test semijoin(nameid, jobid, on = :ID, method = :hash) == semi[:, on]

@test antijoin(nameid, jobid, on = :ID, method = :hash) == anti[:, on]

@test innerjoin(nameid, view(jobid, :, :), on = :ID, method = :hash) == inner[:, on]

@test outerjoin(nameid, view(jobid, :, :), on = :ID, method = :hash) == outer[:, on]

@test leftjoin(nameid, view(jobid, :, :), on = :ID, method = :hash) == left[:, on]

@test innerjoin(view(nameid, :, :), view(jobid, :, :), on = :ID, method = :hash) == inner[:, on]

@test outerjoin(view(nameid, :, :), view(jobid, :, :), on = :ID, method = :hash) == outer[:, on]

@test leftjoin(view(nameid, :, :), view(jobid, :, :), on = :ID, method = :hash) == left[:, on]

@test semijoin(nameid, view(jobid, :, :), on = :ID, method = :hash) == semi[:, on]

@test antijoin(nameid, view(jobid, :, :), on = :ID, method = :hash) == anti[:, on]

@test innerjoin(nameid, jobid, on = :ID, threads = false) == inner[:, on]

@test innerjoin(nameid, jobid, on = :ID, method = :hash, threads = false) == inner[:, on]

@test outerjoin(nameid, jobid, on = :ID, threads = false) == outer[:, on]

@test outerjoin(nameid, jobid, on = :ID, method = :hash, threads = false) == outer[:, on]

@test leftjoin(nameid, jobid, on = :ID, threads = false) == left[:, on]

@test leftjoin(nameid, jobid, on = :ID, method = :hash, threads = false) == left[:, on]

@test semijoin(nameid, jobid, on = :ID, threads = false) == semi[:, on]

@test semijoin(nameid, jobid, on = :ID, method = :hash, threads = false) == semi[:, on]

@test antijoin(nameid, jobid, on = :ID, threads = false) == anti[:, on]

@test antijoin(nameid, jobid, on = :ID, method = :hash, threads = false) == anti[:, on]

@test innerjoin(nameid, view(jobid, :, :), on = :ID, threads = false) == inner[:, on]

@test innerjoin(nameid, view(jobid, :, :), on = :ID, method = :hash, threads = false) == inner[:, on]

@test outerjoin(nameid, view(jobid, :, :), on = :ID, threads = false) == outer[:, on]

@test outerjoin(nameid, view(jobid, :, :), on = :ID, method = :hash, threads = false) == outer[:, on]

@test leftjoin(nameid, view(jobid, :, :), on = :ID, threads = false) == left[:, on]

@test leftjoin(nameid, view(jobid, :, :), on = :ID, method = :hash, threads = false) == left[:, on]

@test innerjoin(view(nameid, :, :), view(jobid, :, :), on = :ID, threads = false) == inner[:, on]

@test innerjoin(view(nameid, :, :), view(jobid, :, :), on = :ID, method = :hash, threads = false) == inner[:, on]

@test outerjoin(view(nameid, :, :), view(jobid, :, :), on = :ID, threads = false) == outer[:, on]

@test outerjoin(view(nameid, :, :), view(jobid, :, :), on = :ID, method = :hash, threads = false) == outer[:, on]

@test leftjoin(view(nameid, :, :), view(jobid, :, :), on = :ID, threads = false) == left[:, on]

@test leftjoin(view(nameid, :, :), view(jobid, :, :), on = :ID, method = :hash, threads = false) == left[:, on]

@test semijoin(nameid, view(jobid, :, :), on = :ID, threads = false) == semi[:, on]

@test semijoin(nameid, view(jobid, :, :), on = :ID, method = :hash, threads = false) == semi[:, on]

@test antijoin(nameid, view(jobid, :, :), on = :ID, threads = false) == anti[:, on]

@test antijoin(nameid, view(jobid, :, :), on = :ID, method = :hash, threads = false) == anti[:, on]

# Join on multiple keys

ds1 = Dataset(A = 1, B = 2, C = 3)

ds2 = Dataset(A = 1, B = 2, D = 4)

@test innerjoin(ds1, ds2, on = [:A, :B]) == Dataset(A = 1, B = 2, C = 3, D = 4)

@test innerjoin(ds1, view(ds2, :, :), on = [:A, :B]) == Dataset(A = 1, B = 2, C = 3, D = 4)

@test innerjoin(ds1, ds2, on = [:A, :B], method = :hash) == Dataset(A = 1, B = 2, C = 3, D = 4)

@test innerjoin(ds1, view(ds2, :, :), on = [:A, :B], method = :hash) == Dataset(A = 1, B = 2, C = 3, D = 4)

@test innerjoin(ds1, view(ds2, :, :), on = [:A, :B], threads = false) == Dataset(A = 1, B = 2, C = 3, D = 4)

@test innerjoin(ds1, view(ds2, :, :), on = [:A, :B], method = :hash, threads = false) == Dataset(A = 1, B = 2, C = 3, D = 4)

dsl = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]], ["x1", "x2", "x3", "row"])

dsr = Dataset(x1=[1, 3], y =[100.0, 200.0])

setformat!(dsl, 1=>iseven)

setformat!(dsr, 1=>isodd)

left1 = leftjoin(dsl, dsr, on = :x1)

left1_v = leftjoin(dsl, view(dsr, :, [2,1]), on = :x1)

left1_t = Dataset([Union{Missing, Int64}[10, 10, 3, 4, 4, 1, 5, 5, 6, 6, 7, 2, 2, 10, 10],

Union{Missing, Int64}[10, 10, 3, 4, 4, 1, 5, 5, 6, 6, 7, 2, 2, 10, 10],

Union{Missing, Int64}[3, 3, 6, 7, 7, 10, 10, 5, 10, 10, 9, 1, 1, 1, 1],

Union{Missing, Int64}[1, 1, 2, 3, 3, 4, 5, 6, 7, 7, 8, 9, 9, 10, 10],

Union{Missing, Float64}[100.0, 200.0, missing, 100.0, 200.0, missing, missing, missing, 100.0, 200.0, missing, 100.0, 200.0, 100.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

left2 = leftjoin(dsl, dsr, on = :x1, mapformats = [true, false])

left2_v = leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false])

@test left2 == leftjoin(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash)

@test left2_v == leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash)

@test left2 == leftjoin(dsl, dsr, on = :x1, mapformats = [true, false], threads = false)

@test left2 == leftjoin(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash, threads = false)

@test left2_v == leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], threads = false)

@test left2_v == leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash, threads = false)

left2_t = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[100.0, missing, 100.0, missing, missing, missing, 100.0, missing, 100.0, 100.0]], ["x1", "x2", "x3", "row", "y"])

left3 = leftjoin(dsl, dsr, on = :x1, mapformats = [false, true])

left3_v = leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true])

@test left3 == leftjoin(dsl, dsr, on = :x1, mapformats = [false, true], method = :hash)

@test left3_v == leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], method = :hash)

@test left3 == leftjoin(dsl, dsr, on = :x1, mapformats = [false, true], threads = false)

@test left3 == leftjoin(dsl, dsr, on = :x1, mapformats = [false, true], method = :hash, threads = false)

@test left3_v == leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], threads = false)

@test left3_v == leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], method = :hash, threads = false)

left3_t = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[missing, missing, missing, 100.0, 200.0, missing, missing, missing, missing, missing, missing]], ["x1", "x2", "x3", "row", "y"])

left4 = leftjoin(dsl, dsr, on = :x1, mapformats = [false, false])

left4_v = leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false])

@test left4 == leftjoin(dsl, dsr, on = :x1, mapformats = [false, false], method = :hash)

@test left4_v == leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], method = :hash)

@test left4 == leftjoin(dsl, dsr, on = :x1, mapformats = [false, false], threads = false)

@test left4 == leftjoin(dsl, dsr, on = :x1, mapformats = [false, false], method = :hash, threads = false)

@test left4_v == leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], threads = false)

@test left4_v == leftjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], method = :hash, threads = false)

left4_t = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[missing, 200.0, missing, 100.0, missing, missing, missing, missing, missing, missing]], ["x1", "x2", "x3", "row", "y"])

inner1 = innerjoin(dsl, dsr, on = :x1)

inner1_v = innerjoin(dsl, view(dsr, :, [2,1]), on = :x1)

@test inner1 == innerjoin(dsl, dsr, on = :x1, method = :hash)

@test inner1_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, method = :hash)

@test inner1 == innerjoin(dsl, dsr, on = :x1, threads = false)

@test inner1 == innerjoin(dsl, dsr, on = :x1, method = :hash, threads = false)

@test inner1_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, threads = false)

@test inner1_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, method = :hash, threads = false)

inner1_t = Dataset([Union{Missing, Int64}[10, 10, 4, 4, 6, 6, 2, 2, 10, 10],

Union{Missing, Int64}[10, 10, 4, 4, 6, 6, 2, 2, 10, 10],

Union{Missing, Int64}[3, 3, 7, 7, 10, 10, 1, 1, 1, 1],

Union{Missing, Int64}[1, 1, 3, 3, 7, 7, 9, 9, 10, 10],

Union{Missing, Float64}[100.0, 200.0, 100.0, 200.0, 100.0, 200.0, 100.0, 200.0, 100.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

inner2 = innerjoin(dsl, dsr, on = :x1, mapformats = [true, false])

inner2_v = innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false])

@test inner2 == innerjoin(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash)

@test inner2_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash)

@test inner2 == innerjoin(dsl, dsr, on = :x1, mapformats = [true, false], threads = false)

@test inner2 == innerjoin(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash, threads = false)

@test inner2_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], threads = false)

@test inner2_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash, threads = false)

inner2_t = Dataset([ Union{Missing, Int64}[10, 4, 6, 2, 10],

Union{Missing, Int64}[10, 4, 6, 2, 10],

Union{Missing, Int64}[3, 7, 10, 1, 1],

Union{Missing, Int64}[1, 3, 7, 9, 10],

Union{Missing, Float64}[100.0, 100.0, 100.0, 100.0, 100.0]], ["x1", "x2", "x3", "row", "y"])

inner3 = innerjoin(dsl, dsr, on = :x1, mapformats = [false, true])

inner3_v = innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true])

@test inner3 == innerjoin(dsl, dsr, on = :x1, mapformats = [false, true], method = :hash)

@test inner3_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], method = :hash)

@test inner3 == innerjoin(dsl, dsr, on = :x1, mapformats = [false, true], threads = false)

@test inner3 == innerjoin(dsl, dsr, on = :x1, mapformats = [false, true], method = :hash, threads = false)

@test inner3_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], threads = false)

@test inner3_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], method = :hash, threads = false)

inner3_t = Dataset([Union{Missing, Int64}[1, 1],

Union{Missing, Int64}[1, 1],

Union{Missing, Int64}[10, 10],

Union{Missing, Int64}[4, 4],

Union{Missing, Float64}[100.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

inner4 = innerjoin(dsl, dsr, on = :x1, mapformats = [false, false])

inner4_v = innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false])

@test inner4 == innerjoin(dsl, dsr, on = :x1, mapformats = [false, false], method = :hash)

@test inner4_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], method = :hash)

@test inner4 == innerjoin(dsl, dsr, on = :x1, mapformats = [false, false], threads = false)

@test inner4 == innerjoin(dsl, dsr, on = :x1, mapformats = [false, false], method = :hash, threads = false)

@test inner4_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], threads = false)

@test inner4_v == innerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], method = :hash, threads = false)

inner4_t = Dataset([Union{Missing, Int64}[3, 1],

Union{Missing, Int64}[3, 1],

Union{Missing, Int64}[6, 10],

Union{Missing, Int64}[2, 4],

Union{Missing, Float64}[200.0, 100.0]], ["x1", "x2", "x3", "row", "y"])

outer1 = outerjoin(dsl, dsr, on = :x1)

outer1_v = outerjoin(dsl, view(dsr, :, [2,1]), on = :x1)

@test outer1 == outerjoin(dsl, dsr, on = :x1, method = :hash)

@test outer1_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, method = :hash)

@test outer1 == outerjoin(dsl, dsr, on = :x1, threads = false)

@test outer1 == outerjoin(dsl, dsr, on = :x1, method = :hash, threads = false)

@test outer1_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, threads = false)

@test outer1_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, method = :hash, threads = false)

outer1_t = Dataset([Union{Missing, Int64}[10, 10, 3, 4, 4, 1, 5, 5, 6, 6, 7, 2, 2, 10, 10],

Union{Missing, Int64}[10, 10, 3, 4, 4, 1, 5, 5, 6, 6, 7, 2, 2, 10, 10],

Union{Missing, Int64}[3, 3, 6, 7, 7, 10, 10, 5, 10, 10, 9, 1, 1, 1, 1],

Union{Missing, Int64}[1, 1, 2, 3, 3, 4, 5, 6, 7, 7, 8, 9, 9, 10, 10],

Union{Missing, Float64}[100.0, 200.0, missing, 100.0, 200.0, missing, missing, missing, 100.0, 200.0, missing, 100.0, 200.0, 100.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

outer2 = outerjoin(dsl, dsr, on = :x1, mapformats = [false, true])

outer2_v = outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true])

@test outer2 == outerjoin(dsl, dsr, on = :x1, mapformats = [false, true], method = :hash)

@test outer2_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], method = :hash)

@test outer2 == outerjoin(dsl, dsr, on = :x1, mapformats = [false, true], threads = false)

@test outer2 == outerjoin(dsl, dsr, on = :x1, mapformats = [false, true], method = :hash, threads = false)

@test outer2_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], threads = false)

@test outer2_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], method = :hash, threads = false)

outer2_t = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[missing, missing, missing, 100.0, 200.0, missing, missing, missing, missing, missing, missing]], ["x1", "x2", "x3", "row", "y"])

outer3 = outerjoin(dsl, dsr, on = :x1, mapformats = [true, false])

outer3_v = outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false])

@test outer3 == outerjoin(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash)

@test outer3_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash)

@test outer3 == outerjoin(dsl, dsr, on = :x1, mapformats = [true, false], threads = false)

@test outer3 == outerjoin(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash, threads = false)

@test outer3_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], threads = false)

@test outer3_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash, threads = false)

outer3_t = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10, 3],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10, missing],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1, missing],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, missing],

Union{Missing, Float64}[100.0, missing, 100.0, missing, missing, missing, 100.0, missing, 100.0, 100.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

outer4 = outerjoin(dsl, dsr, on = :x1, mapformats = [false, false])

outer4_v = outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false])

@test outer4 == outerjoin(dsl, dsr, on = :x1, mapformats = [false, false], method = :hash)

@test outer4_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], method = :hash)

@test outer4 == outerjoin(dsl, dsr, on = :x1, mapformats = [false, false], threads =false)

@test outer4_v == outerjoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], threads =false)

outer4_t = Dataset([ Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[missing, 200.0, missing, 100.0, missing, missing, missing, missing, missing, missing]], ["x1", "x2", "x3", "row", "y"])

contains1 = contains(dsl, dsr, on = :x1)

contains1_v = contains(dsl, view(dsr, :, [2,1]), on = :x1)

@test contains1 == contains(dsl, dsr, on = :x1, method = :hash)

@test contains1_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, method = :hash)

@test contains1 == contains(dsl, dsr, on = :x1, threads = false)

@test contains1 == contains(dsl, dsr, on = :x1, method = :hash, threads = false)

@test contains1_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, threads = false)

@test contains1_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, method = :hash, threads = false)

contains1_t = Bool[1, 0, 1, 0, 0, 0, 1, 0, 1, 1]

contains2 = contains(dsl, dsr, on = :x1, mapformats = [true, false])

contains2_v = contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false])

@test contains2 == contains(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash)

@test contains2_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash)

@test contains2 == contains(dsl, dsr, on = :x1, mapformats = [true, false], threads = false)

@test contains2 == contains(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash, threads = false)

@test contains2_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], threads = false)

@test contains2_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash, threads = false)

contains2_t = Bool[1, 0, 1, 0, 0, 0, 1, 0, 1, 1]

contains3 = contains(dsl, dsr, on = :x1, mapformats =[false, true])

contains3_v = contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats =[false, true])

@test contains3 == contains(dsl, dsr, on = :x1, mapformats =[false, true], method = :hash)

@test contains3_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats =[false, true], method = :hash)

@test contains3 == contains(dsl, dsr, on = :x1, mapformats =[false, true], threads = false)

@test contains3 == contains(dsl, dsr, on = :x1, mapformats =[false, true], method = :hash, threads = false)

@test contains3_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats =[false, true], threads = false)

@test contains3_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats =[false, true], method = :hash, threads = false)

contains3_t = Bool[0, 0, 0, 1, 0, 0, 0, 0, 0, 0]

contains4 = contains(dsl, dsr, on = :x1, mapformats = [false, false])

contains4_v = contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false])

@test contains4 == contains(dsl, dsr, on = :x1, mapformats = [false, false], method = :hash)

@test contains4_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], method = :hash)

@test contains4 == contains(dsl, dsr, on = :x1, mapformats = [false, false], threads = false)

@test contains4 == contains(dsl, dsr, on = :x1, mapformats = [false, false], method = :hash, threads = false)

@test contains4_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], threads = false)

@test contains4_v == contains(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], method = :hash, threads = false)

contains4_t = Bool[0, 1, 0, 1, 0, 0, 0, 0, 0, 0]

close1 = closejoin(dsl, dsr, on = :x1)

close1_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1)

@test close1 == closejoin(dsl, dsr, on = :x1, method = :hash)

@test close1_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, method = :hash)

@test close1 == closejoin(dsl, dsr, on = :x1, threads =false)

@test close1_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, threads =false)

close1_t = Dataset([ Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[200.0, missing, 200.0, missing, missing, missing, 200.0, missing, 200.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

close2 = closejoin(dsl, dsr, on = :x1, direction = :forward)

close2_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, direction = :forward)

@test close2 == closejoin(dsl, dsr, on = :x1, direction = :forward, method = :hash)

@test close2_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, direction = :forward, method = :hash)

@test close2 == closejoin(dsl, dsr, on = :x1, direction = :forward, threads = false)

@test close2 == closejoin(dsl, dsr, on = :x1, direction = :forward, method = :hash, threads = false)

@test close2_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, direction = :forward, threads = false)

@test close2_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, direction = :forward, method = :hash, threads = false)

close2_t = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100.0]], ["x1", "x2", "x3", "row", "y"])

close3 = closejoin(dsl, dsr, on = :x1, border = :nearest)

close3_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, border = :nearest)

@test close3 == closejoin(dsl, dsr, on = :x1, border = :nearest, method = :hash)

@test close3_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, border = :nearest, method = :hash)

@test close3 == closejoin(dsl, dsr, on = :x1, border = :nearest, threads = false)

@test close3 == closejoin(dsl, dsr, on = :x1, border = :nearest, method = :hash, threads = false)

@test close3_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, border = :nearest, threads = false)

@test close3_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, border = :nearest, method = :hash, threads = false)

close3_t = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[200.0, 100.0, 200.0, 100.0, 100.0, 100.0, 200.0, 100.0, 200.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

close4 = closejoin(dsl, dsr, on = :x1, mapformats = [true, false])

close4_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false])

@test close4 == closejoin(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash)

@test close4_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash)

@test close4 == closejoin(dsl, dsr, on = :x1, mapformats = [true, false], threads = false)

@test close4 == closejoin(dsl, dsr, on = :x1, mapformats = [true, false], method = :hash, threads = false)

@test close4_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], threads = false)

@test close4_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], method = :hash, threads = false)

close4_t = Dataset([ Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[100.0, missing, 100.0, missing, missing, missing, 100.0, missing, 100.0, 100.0]], ["x1", "x2", "x3", "row", "y"])

close5 = closejoin(dsl, dsr, on = :x1, mapformats = [true, false], direction = :forward)

close5_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], direction = :forward)

@test close5 == closejoin(dsl, dsr, on = :x1, mapformats = [true, false], direction = :forward, method = :hash)

@test close5_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], direction = :forward, method = :hash)

@test close5 == closejoin(dsl, dsr, on = :x1, mapformats = [true, false], direction = :forward, threads = false)

@test close5 == closejoin(dsl, dsr, on = :x1, mapformats = [true, false], direction = :forward, method = :hash, threads = false)

@test close5_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], direction = :forward, threads = false)

@test close5_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [true, false], direction = :forward, method = :hash, threads = false)

close5_t = Dataset([ Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100.0]], ["x1", "x2", "x3", "row", "y"])

close6 = closejoin(dsl, dsr, on = :x1, mapformats = [false, true])

close6_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true])

@test close6 == closejoin(dsl, dsr, on = :x1, mapformats = [false, true], method = :hash)

@test close6_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], method = :hash)

@test close6 == closejoin(dsl, dsr, on = :x1, mapformats = [false, true], threads = false)

@test close6 == closejoin(dsl, dsr, on = :x1, mapformats = [false, true], method = :hash, threads = false)

@test close6_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], threads = false)

@test close6_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], method = :hash, threads = false)

close6_t = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[200.0, 200.0, 200.0, 200.0, 200.0, 200.0, 200.0, 200.0, 200.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

close7 = closejoin(dsl, dsr, on = :x1, mapformats = [false, true], direction = :forward)

close7_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], direction = :forward)

@test close7 == closejoin(dsl, dsr, on = :x1, mapformats = [false, true], direction = :forward, method = :hash)

@test close7_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], direction = :forward, method = :hash)

@test close7 == closejoin(dsl, dsr, on = :x1, mapformats = [false, true], direction = :forward, threads = false)

@test close7 == closejoin(dsl, dsr, on = :x1, mapformats = [false, true], direction = :forward, method = :hash, threads = false)

@test close7_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], direction = :forward, threads = false)

@test close7_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], direction = :forward, method = :hash, threads = false)

close7_t = Dataset([ Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[missing, missing, missing, 100.0, missing, missing, missing, missing, missing, missing]],["x1", "x2", "x3", "row", "y"])

close8 = closejoin(dsl, dsr, on = :x1, mapformats = [false, true], direction = :forward, border = :nearest)

close8_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], direction = :forward, border = :nearest)

@test close8 == closejoin(dsl, dsr, on = :x1, mapformats = [false, true], direction = :forward, border = :nearest, method = :hash)

@test close8_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], direction = :forward, border = :nearest, method = :hash)

@test close8 == closejoin(dsl, dsr, on = :x1, mapformats = [false, true], direction = :forward, border = :nearest, threads = false)

@test close8 == closejoin(dsl, dsr, on = :x1, mapformats = [false, true], direction = :forward, border = :nearest, method = :hash, threads = false)

@test close8_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], direction = :forward, border = :nearest, threads = false)

@test close8_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, true], direction = :forward, border = :nearest, method = :hash, threads = false)

close8_t = Dataset([ Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[200.0, 200.0, 200.0, 100.0, 200.0, 200.0, 200.0, 200.0, 200.0, 200.0]],["x1", "x2", "x3", "row", "y"])

close9 = closejoin(dsl, dsr, on = :x1, mapformats = [false, false])

close9_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false])

@test close9 == closejoin(dsl, dsr, on = :x1, mapformats = [false, false], method = :hash)

@test close9_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], method = :hash)

@test close9 == closejoin(dsl, dsr, on = :x1, mapformats = [false, false], threads = false)

@test close9 == closejoin(dsl, dsr, on = :x1, mapformats = [false, false], method = :hash, threads = false)

@test close9_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], threads = false)

@test close9_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = [false, false], method = :hash, threads = false)

close9_t = Dataset([Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[200.0, 200.0, 200.0, 100.0, 200.0, 200.0, 200.0, 200.0, 100.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

close10 = closejoin(dsl, dsr, on = :x1, mapformats = false, direction = :forward)

close10_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = false, direction = :forward)

@test close10 == closejoin(dsl, dsr, on = :x1, mapformats = false, direction = :forward, method = :hash)

@test close10_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = false, direction = :forward, method = :hash)

@test close10 == closejoin(dsl, dsr, on = :x1, mapformats = false, direction = :forward, threads = false)

@test close10 == closejoin(dsl, dsr, on = :x1, mapformats = false, direction = :forward, method = :hash, threads = false)

@test close10_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = false, direction = :forward, threads = false)

@test close10_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = false, direction = :forward, method = :hash, threads = false)

close10_t = Dataset([ Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[missing, 200.0, missing, 100.0, missing, missing, missing, missing, 200.0, missing]], ["x1", "x2", "x3", "row", "y"])

close11 = closejoin(dsl, dsr, on = :x1, mapformats = false, direction = :forward, border = :nearest)

close11_v = closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = false, direction = :forward, border = :nearest)

@test close11 == closejoin(dsl, dsr, on = :x1, mapformats = false, direction = :forward, border = :nearest, method = :hash)

@test close11_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = false, direction = :forward, border = :nearest, method = :hash)

@test close11 == closejoin(dsl, dsr, on = :x1, mapformats = false, direction = :forward, border = :nearest, threads = false)

@test close11 == closejoin(dsl, dsr, on = :x1, mapformats = false, direction = :forward, border = :nearest, method = :hash, threads = false)

@test close11_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = false, direction = :forward, border = :nearest, threads = false)

@test close11_v == closejoin(dsl, view(dsr, :, [2,1]), on = :x1, mapformats = false, direction = :forward, border = :nearest, method = :hash, threads = false)

close11_t = Dataset([ Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[10, 3, 4, 1, 5, 5, 6, 7, 2, 10],

Union{Missing, Int64}[3, 6, 7, 10, 10, 5, 10, 9, 1, 1],

Union{Missing, Int64}[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],

Union{Missing, Float64}[200.0, 200.0, 200.0, 100.0, 200.0, 200.0, 200.0, 200.0, 200.0, 200.0]], ["x1", "x2", "x3", "row", "y"])

@test left1 == left1_t

@test left2 == left2_t

@test left3 == left3_t

@test left4 == left4_t

@test inner1 == inner1_t

@test inner2 == inner2_t

@test inner3 == inner3_t

@test inner4 == inner4_t

@test outer1 == outer1_t

@test outer2 == outer2_t

@test outer3 == outer3_t

@test outer4 == outer4_t

@test contains1 == contains1_t

@test contains2 == contains2_t

@test contains3 == contains3_t

@test contains4 == contains4_t

@test close1 == close1_t

@test close2 == close2_t

@test close3 == close3_t

@test close4 == close4_t

@test close5 == close5_t

@test close6 == close6_t

@test close7 == close7_t

@test close8 == close8_t

@test close9 == close9_t

@test close10 == close10_t

@test close11 == close11_t

@test left1_v == left1_t

@test left2_v == left2_t

@test left3_v == left3_t

@test left4_v == left4_t

@test inner1_v == inner1_t

@test inner2_v == inner2_t

@test inner3_v == inner3_t

@test inner4_v == inner4_t

@test outer1_v == outer1_t

@test outer2_v == outer2_t

@test outer3_v == outer3_t

@test outer4_v == outer4_t

@test contains1_v == contains1_t

@test contains2_v == contains2_t

@test contains3_v == contains3_t

@test contains4_v == contains4_t

@test close1_v == close1_t

@test close2_v == close2_t

@test close3_v == close3_t

@test close4_v == close4_t

@test close5_v == close5_t

@test close6_v == close6_t

@test close7_v == close7_t

@test close8_v == close8_t

@test close9_v == close9_t

@test close10_v == close10_t

@test close11_v == close11_t

dsl = Dataset([[Characters{1}(randstring(1)) for _ in 1:10^5] for _ in 1:3], :auto)

dsr = Dataset([[Characters{1}(randstring(1)) for _ in 1:10^5] for _ in 1:3], :auto)

left1 = leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = true, stable =true, check = false)

left2 = leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false)

@test left1 == leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = true, stable =true, check = false, method = :hash)

@test left2 == leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false, method = :hash)

@test left1 == leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = true, stable =true, check = false, threads = false)

@test left1 == leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = true, stable =true, check = false, method = :hash, threads = false)

@test left2 == leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false, threads = false)

@test left2 == leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false, method = :hash, threads = false)

@test left1 == left2

@test unique(select!(left1, [:x1, :x2, :x3]), [:x1, :x2]) == unique(dsl, [:x1, :x2])

dsl = Dataset([[Characters{1}(randstring(1)) for _ in 1:10^5] for _ in 1:3], :auto)

dsr = Dataset([[Characters{1}(randstring(1)) for _ in 1:10^5] for _ in 1:3], :auto)

for i in 1:3

dsl[!, i] = PooledArray(dsl[!, i])

dsr[!, i] = PooledArray(dsr[!, i])

end

for i in 1:10

left1 = leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = true, stable =true, check = false)

left2 = leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false)

@test left2 == leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false, method = :hash)

@test left2 == leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false, threads = false)

@test left2 == leftjoin(dsl, dsr, on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false, method = :hash, threads = false)

@test left1 == left2

@test unique(select!(left1, [:x1, :x2, :x3]), [:x1, :x2]) == unique(dsl, [:x1, :x2])

left1 = leftjoin(dsl, view(dsr, :, :), on = [:x1, :x2], makeunique = true, accelerate = true, stable =true, check = false)

left2 = leftjoin(dsl, view(dsr, :, :), on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false)

@test left2 == leftjoin(dsl, view(dsr, :, :), on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false, method = :hash)

@test left2 == leftjoin(dsl, view(dsr, :, :), on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false, threads = false)

@test left2 == leftjoin(dsl, view(dsr, :, :), on = [:x1, :x2], makeunique = true, accelerate = false, stable = true, check = false, method = :hash, threads = false)

@test left1 == left2

@test unique(select!(left1, [:x1, :x2, :x3]), [:x1, :x2]) == unique(dsl, [:x1, :x2])

end

x1 = rand(1:1000, 5000)

x2 = rand(1:100, 5000)

y = rand(5000)

y2 = rand(5000)

dsl = Dataset(x1 = Characters{6}.(c"id" .* string.(x1)), x2 = Characters{5}.(c"id" .* string.(x2)), y = y)

dsr = Dataset(x1 = x1, x2 = x2, y2 = y2)

fmtfun(x) = @views parse(Int, x[3:end])

setformat!(dsl, 1:2=>fmtfun)

semi1 = semijoin(dsl, dsr, on = [:x1, :x2])

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], method = :hash)

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], threads = false)

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], method = :hash, threads = false)

semi2 = semijoin(dsl, dsr, on = [:x1, :x2], accelerate = true)

@test semi1 == dsl

@test semi2 == dsl

semi1 = semijoin(dsl, view(dsr, :, :), on = [:x1, :x2])

@test semi1 == semijoin(dsl, view(dsr, :, :), on = [:x1, :x2], method = :hash)

@test semi1 == semijoin(dsl, view(dsr, :, :), on = [:x1, :x2], threads = false)

@test semi1 == semijoin(dsl, view(dsr, :, :), on = [:x1, :x2], method = :hash, threads = false)

semi2 = semijoin(dsl, view(dsr, :, :), on = [:x1, :x2], accelerate = true)

@test semi1 == dsl

@test semi2 == dsl

inn1 = innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true)

out1 = outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true)

left1 = leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true, method = :hash)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, threads = false)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash, threads = false)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, threads = false)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash, threads = false)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true, threads = false)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true, method = :hash, threads = false)

@test inn1 == out1 == left1

fmtfun2(x) = c"id" * Characters{4}(x)

setformat!(dsr, 1:2=>fmtfun2)

semi1 = semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [false, true])

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [false, true], method = :hash)

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [false, true], threads = false)

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [false, true], method = :hash, threads = false)

semi2 = semijoin(dsl, dsr, on = [:x1, :x2], accelerate = true, mapformats = [false, true])

@test semi1 == dsl

@test semi2 == dsl

inn1 = innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true)

out1 = outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true)

left1 = leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], accelerate = true, stable =true)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, method = :hash)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, method = :hash)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], accelerate = true, stable =true, method = :hash)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, threads = false)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, method = :hash, threads = false)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, threads = false)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, method = :hash, threads = false)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], accelerate = true, stable =true, threads = false)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], accelerate = true, stable =true, method = :hash, threads = false)

@test inn1 == out1 == left1

x1 = rand(1:1000, 5000)

x2 = rand(1:100, 5000)

y = rand(5000)

y2 = rand(5000)

dsl = Dataset(x1 = Characters{6}.(c"id" .* string.(x1)), x2 = Characters{5}.(c"id" .* string.(x2)), y = y)

dsr = Dataset(x1 = x1, x2 = x2, y2 = y2)

for i in 1:2

dsl[!, i] = PooledArray(dsl[!, i])

dsr[!, i] = PooledArray(dsr[!, i])

end

setformat!(dsl, 1:2=>fmtfun)

semi1 = semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [true, false])

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [true, false], method =:hash)

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [true, false], threads = false)

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [true, false], method = :hash, threads = false)

semi2 = semijoin(dsl, dsr, on = [:x1, :x2], accelerate = true, mapformats = [true, false])

@test semi1 == dsl

@test semi2 == dsl

inn1 = innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true)

out1 = outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true)

left1 = leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true, method = :hash)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, threads = false)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash, threads = false)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, threads = false)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash, threads = false)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true, threads = false)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true, method = :hash, threads = false)

@test inn1 == out1 == left1

setformat!(dsr, 1:2=>fmtfun2)

semi1 = semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [false, true])

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [false, true], method = :hash)

semi2 = semijoin(dsl, dsr, on = [:x1, :x2], accelerate = true, mapformats = [false, true])

@test semi1 == dsl

@test semi2 == dsl

inn1 = innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true)

out1 = outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true)

left1 = leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], accelerate = true, stable =true)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, method = :hash)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, method = :hash)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], accelerate = true, stable =true, method = :hash)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, threads = false)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, method = :hash, threads = false)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, threads = false)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], stable = true, method = :hash, threads = false)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], accelerate = true, stable =true, threads = false)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [false, true], accelerate = true, stable =true, method = :hash, threads = false)

@test inn1 == out1 == left1

x1 = -rand(1:1000, 5000)

x2 = -rand(1:100, 5000)

y = rand(5000)

y2 = rand(5000)

dsl = Dataset(x1 = Characters{6}.(c"id" .* string.(-x1)), x2 = Characters{5}.(c"id" .* string.(-x2)), y = y)

dsr = Dataset(x1 = x1, x2 = x2, y2 = y2)

for i in 1:2

dsl[!, i] = PooledArray(dsl[!, i])

dsr[!, i] = PooledArray(dsr[!, i])

end

fmtfun3(x) = @views -parse(Int, x[3:end])

setformat!(dsl, 1:2=>fmtfun3)

semi1 = semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [true, false])

@test semi1 == semijoin(dsl, dsr, on = [:x1, :x2], mapformats = [true, false], method = :hash)

semi2 = semijoin(dsl, dsr, on = [:x1, :x2], accelerate = true, mapformats = [true, false])

@test semi1 == dsl

@test semi2 == dsl

inn1 = innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true)

out1 = outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true)

left1 = leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true)

@test inn1 == innerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash)

@test out1 == outerjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], stable = true, method = :hash)

@test left1 == leftjoin(dsl, dsr, on =[:x1, :x2], mapformats = [true, false], accelerate = true, stable =true, method = :hash)

@test inn1 == out1 == left1

dsl = Dataset(x = [1,1,1,2,2,2], y = PooledArray([6,4,1,2,5,3]))

dsr = Dataset(x = [1,1,2], y = PooledArray([0,3,1]), z=[100,200,300])

@test closejoin(dsl, dsr, on = [:x, :y], makeunique = true) == Dataset(x=[1,1,1,2,2,2], y=[6,4,1,2,5,3],z=[200,200,100, 300,300,300])

@test closejoin(dsl, dsr, on = [:x, :y], makeunique = true, direction = :forward) == Dataset(x=[1,1,1,2,2,2], y=[6,4,1,2,5,3],z=[missing, missing, 200, missing, missing, missing])

@test closejoin(dsl, dsr, on = [:x, :y], makeunique = true, direction = :forward, border = :nearest) == Dataset(x=[1,1,1,2,2,2], y=[6,4,1,2,5,3],z=[200,200,200, 300,300,300])

@test closejoin(dsl, dsr, on = [:x, :y], method = :hash, makeunique = true) == Dataset(x=[1,1,1,2,2,2], y=[6,4,1,2,5,3],z=[200,200,100, 300,300,300])

@test closejoin(dsl, dsr, on = [:x, :y], method = :hash, makeunique = true, direction = :forward) == Dataset(x=[1,1,1,2,2,2], y=[6,4,1,2,5,3],z=[missing, missing, 200, missing, missing, missing])

@test closejoin(dsl, dsr, on = [:x, :y], method = :hash, makeunique = true, direction = :forward, border = :nearest) == Dataset(x=[1,1,1,2,2,2], y=[6,4,1,2,5,3],z=[200,200,200, 300,300,300])