Materialize/test/sqllogictest/cockroach/exec_hash_join.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/exec_hash_join
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

# Test that the exec HashJoiner follows SQL NULL semantics for ON predicate
# equivalence. The use of sorts here force the planning of merge join.

statement ok
CREATE TABLE  t1 (k INT PRIMARY KEY, v INT)

statement ok
INSERT INTO t1 VALUES (0, 4), (2, 1), (5, 4), (3, 4), (-1, -1)

statement ok
CREATE TABLE t2 (x INT PRIMARY KEY, y INT)

statement ok
INSERT INTO t2 VALUES (1, 3), (4, 6), (0, 5), (3, 2)

statement ok
CREATE TABLE a (k INT, v INT)

statement ok
INSERT INTO a VALUES (0, 1), (1, 2), (2, 0)

statement ok
CREATE TABLE b (a INT, b INT, c TEXT)

statement ok
INSERT INTO b VALUES (0, 1, 'a'), (2, 1, 'b'), (0, 2, 'c'), (0, 1, 'd')

statement ok
CREATE TABLE c (a INT, b TEXT)

statement ok
INSERT INTO c VALUES (1, 'a'), (1, 'b'), (2, 'c')

query IIII rowsort
SELECT * FROM t1 JOIN t2 ON t1.k = t2.x
----
0  4  0  5
3  4  3  2

query IIII rowsort
SELECT * FROM a AS a1 JOIN a AS a2 ON a1.k = a2.v
----
0  1  2  0
1  2  0  1
2  0  1  2

query IIII rowsort
SELECT * FROM a AS a2 JOIN a AS a1 ON a1.k = a2.v
----
0  1  1  2
1  2  2  0
2  0  0  1

query II rowsort
SELECT t2.y, t1.v FROM t1 JOIN t2 ON t1.k = t2.x
----
5  4
2  4

query IIII rowsort
SELECT * FROM t1 JOIN t2 ON t1.v = t2.x
----
0  4  4  6
2  1  1  3
3  4  4  6
5  4  4  6

query IIII rowsort
SELECT * FROM t1 LEFT JOIN t2 ON t1.v = t2.x
----
-1  -1  NULL  NULL
0   4   4     6
2   1   1     3
3   4   4     6
5   4   4     6

query IIII rowsort
SELECT * FROM t1 RIGHT JOIN t2 ON t1.v = t2.x
----
0     4     4  6
2     1     1  3
3     4     4  6
5     4     4  6
NULL  NULL  0  5
NULL  NULL  3  2

query IIII rowsort
SELECT * FROM t1 FULL JOIN t2 ON t1.v = t2.x
----
-1    -1    NULL  NULL
0     4     4     6
2     1     1     3
3     4     4     6
5     4     4     6
NULL  NULL  3     2
NULL  NULL  0     5

query IIT rowsort
SELECT b.a, b.b, b.c FROM b JOIN a ON b.a = a.k AND a.v = b.b
----
0  1  a
0  1  d

query ITI rowsort
SELECT b.a, b.c, c.a FROM b JOIN c ON b.b = c.a AND b.c = c.b
----
0  a  1
2  b  1
0  c  2