/* * Licensed to CRATE Technology GmbH ("Crate") under one or more contributor * license agreements. See the NOTICE file distributed with this work for * additional information regarding copyright ownership. Crate licenses * this file to you under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. You may * obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the * License for the specific language governing permissions and limitations * under the License. * * However, if you have executed another commercial license agreement * with Crate these terms will supersede the license and you may use the * software solely pursuant to the terms of the relevant commercial agreement. */ package io.crate.integrationtests; import io.crate.data.CollectionBucket; import io.crate.exceptions.SQLExceptions; import io.crate.execution.engine.join.RamBlockSizeCalculator; import io.crate.execution.engine.sort.OrderingByPosition; import io.crate.metadata.RelationName; import io.crate.statistics.Stats; import io.crate.statistics.TableStats; import io.crate.testing.TestingHelpers; import io.crate.testing.UseHashJoins; import org.elasticsearch.common.breaker.CircuitBreaker; import org.elasticsearch.index.IndexNotFoundException; import org.elasticsearch.indices.breaker.CircuitBreakerService; import org.elasticsearch.indices.breaker.HierarchyCircuitBreakerService; import org.elasticsearch.test.ESIntegTestCase; import org.junit.After; import org.junit.Test; import java.util.Arrays; import java.util.Collections; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Random; import static io.crate.testing.TestingHelpers.printRows; import static io.crate.testing.TestingHelpers.printedTable; import static org.hamcrest.Matchers.arrayContaining; import static org.hamcrest.Matchers.containsInAnyOrder; import static org.hamcrest.core.Is.is; @ESIntegTestCase.ClusterScope(minNumDataNodes = 2) public class JoinIntegrationTest extends SQLTransportIntegrationTest { @After public void resetStatsAndBreakerSettings() { resetTableStats(); execute("reset global indices"); } @Test public void testCrossJoinOrderByOnBothTables() throws Exception { createColorsAndSizes(); execute("select colors.name, sizes.name from colors, sizes order by colors.name, sizes.name"); assertThat(printedTable(response.rows()), is( "blue| large\n" + "blue| small\n" + "green| large\n" + "green| small\n" + "red| large\n" + "red| small\n")); } @Test public void testCrossJoinOrderByOnOneTableWithLimit() throws Exception { createColorsAndSizes(); execute("select colors.name, sizes.name from colors, sizes order by sizes.name, colors.name limit 4"); assertThat(printedTable(response.rows()), is("" + "blue| large\n" + "green| large\n" + "red| large\n" + "blue| small\n")); } @Test public void testInsertFromCrossJoin() throws Exception { createColorsAndSizes(); execute("create table target (color string, size string)"); ensureYellow(); execute("insert into target (color, size) (select colors.name, sizes.name from colors cross join sizes)"); execute("refresh table target"); execute("select color, size from target order by size, color limit 4"); assertThat(printedTable(response.rows()), is("" + "blue| large\n" + "green| large\n" + "red| large\n" + "blue| small\n")); } @Test public void testInsertFromInnerJoin() throws Exception { execute("create table t1 (x int)"); execute("create table t2 (y int)"); execute("create table target (x int, y int)"); ensureYellow(); execute("insert into t1 (x) values (1), (2)"); execute("insert into t2 (y) values (2), (3)"); execute("refresh table t1, t2"); execute("insert into target (x, y) (select t1.x, t2.y from t1 inner join t2 on t1.x = t2.y)"); execute("refresh table target"); execute("select x, y from target order by x, y"); assertThat(printedTable(response.rows()), is("2| 2\n")); } @Test public void testInsertFromInnerJoinUsing() throws Exception { execute("create table t1 (num int, value string)"); execute("create table t2 (num int, value string, qty int)"); ensureYellow(); execute("insert into t1 (num, value) values (1, 'xxx'), (3, 'yyy'), (5, 'zzz')"); execute("insert into t2 (num, value, qty) values (1, 'xxx', 12), (3, 'yyy', 7), (5, 'zzz', 0)"); execute("refresh table t1, t2"); ensureGreen(); execute("SELECT * FROM t1 AS rel1 INNER JOIN t2 USING (num, value) ORDER BY rel1.num"); assertThat(printedTable(response.rows()), is( "1| xxx| 1| xxx| 12\n" + "3| yyy| 3| yyy| 7\n" + "5| zzz| 5| zzz| 0\n")); execute("SELECT * FROM (SELECT * FROM t1) AS rel1 JOIN (SELECT * FROM t2) AS rel2 USING (num, value) ORDER BY rel1.num;"); assertThat(printedTable(response.rows()), is( "1| xxx| 1| xxx| 12\n" + "3| yyy| 3| yyy| 7\n" + "5| zzz| 5| zzz| 0\n")); } @Test public void testJoinOnEmptyPartitionedTablesWithAndWithoutJoinCondition() throws Exception { execute("create table foo (id long) partitioned by (id)"); execute("create table bar (id long) partitioned by (id)"); ensureYellow(); execute("select * from foo f, bar b where f.id = b.id"); assertThat(printedTable(response.rows()), is("")); execute("select * from foo f, bar b"); assertThat(printedTable(response.rows()), is("")); } @Test public void testCrossJoinJoinUnordered() throws Exception { execute("create table employees (size float, name string) clustered by (size) into 1 shards"); execute("create table offices (height float, name string) clustered by (height) into 1 shards"); execute("insert into employees (size, name) values (1.5, 'Trillian')"); execute("insert into offices (height, name) values (1.5, 'Hobbit House')"); execute("refresh table employees, offices"); // which employee fits in which office? execute("select employees.name, offices.name from employees, offices limit 1"); assertThat(response.rows().length, is(1)); } @Test public void testCrossJoinWithFunction() throws Exception { execute("create table t1 (price float)"); execute("create table t2 (price float)"); ensureYellow(); execute("insert into t1 (price) values (20.3), (15.0)"); execute("insert into t2 (price) values (28.3)"); execute("refresh table t1, t2"); execute("select round(t1.price * t2.price) as total_price from t1, t2 order by total_price"); assertThat(printedTable(response.rows()), is("425\n574\n")); } @Test public void testOrderByWithMixedRelationOrder() throws Exception { execute("create table t1 (price float)"); execute("create table t2 (price float, name string)"); ensureYellow(); execute("insert into t1 (price) values (20.3), (15.0)"); execute("insert into t2 (price, name) values (28.3, 'foobar'), (40.1, 'bar')"); execute("refresh table t1, t2"); execute("select t2.price, t1.price, name from t1, t2 order by t2.price, t1.price, t2.name"); assertThat(printedTable(response.rows()), is("" + "28.3| 15.0| foobar\n" + "28.3| 20.3| foobar\n" + "40.1| 15.0| bar\n" + "40.1| 20.3| bar\n")); } @Test public void testOrderByNoneSelectedField() throws Exception { execute("create table colors (name string)"); execute("create table articles (price float, name string)"); ensureYellow(); execute("insert into colors (name) values ('black'), ('grey')"); execute("insert into articles (price, name) values (28.3, 'towel'), (40.1, 'cheese')"); execute("refresh table colors, articles"); execute("select colors.name, articles.name from colors, articles order by articles.price, colors.name, articles.name"); assertThat(printedTable(response.rows()), is("" + "black| towel\n" + "grey| towel\n" + "black| cheese\n" + "grey| cheese\n")); } @Test public void testCrossJoinWithoutLimitAndOrderByAndCrossJoinSyntax() throws Exception { createColorsAndSizes(); execute("select colors.name, sizes.name from colors cross join sizes"); assertThat(response.rowCount(), is(6L)); List<Object[]> rows = Arrays.asList(response.rows()); Collections.sort(rows, OrderingByPosition.arrayOrdering( new int[]{0, 1}, new boolean[]{false, false}, new boolean[]{false, false})); assertThat(printRows(rows), is( "blue| large\n" + "blue| small\n" + "green| large\n" + "green| small\n" + "red| large\n" + "red| small\n" )); } @Test public void testOutputFromOnlyOneTable() throws Exception { createColorsAndSizes(); execute("select colors.name from colors, sizes order by colors.name"); assertThat(response.rowCount(), is(6L)); assertThat(printedTable(response.rows()), is("" + "blue\n" + "blue\n" + "green\n" + "green\n" + "red\n" + "red\n")); } @Test public void testCrossJoinWithSysTable() throws Exception { execute("create table t (name string) clustered into 3 shards with (number_of_replicas = 0)"); ensureYellow(); execute("insert into t values ('foo'), ('bar')"); execute("refresh table t"); execute("select shards.id, t.name from sys.shards, t where shards.table_name = 't' order by shards.id, t.name"); assertThat(response.rowCount(), is(6L)); assertThat(printedTable(response.rows()), is("" + "0| bar\n" + "0| foo\n" + "1| bar\n" + "1| foo\n" + "2| bar\n" + "2| foo\n")); } @Test public void testJoinOnSysTables() throws Exception { execute("select column_policy, column_name from information_schema.tables, information_schema.columns " + "where " + "tables.table_schema = 'sys' " + "and tables.table_name = 'shards' " + "and tables.table_schema = columns.table_schema " + "and tables.table_name = columns.table_name " + "order by columns.column_name " + "limit 4"); assertThat(response.rowCount(), is(4L)); assertThat(printedTable(response.rows()), is("strict| blob_path\n" + "strict| id\n" + "strict| min_lucene_version\n" + "strict| node\n")); } @Test public void testCrossJoinSysTablesOnly() throws Exception { execute("create table t (name string) clustered into 3 shards with (number_of_replicas = 0)"); ensureYellow(); execute("select s1.id, s2.id, s1.table_name from sys.shards s1, sys.shards s2 order by s1.id asc, s2.id desc"); assertThat(response.rowCount(), is(9L)); assertThat(printedTable(response.rows()), is("" + "0| 2| t\n" + "0| 1| t\n" + "0| 0| t\n" + "1| 2| t\n" + "1| 1| t\n" + "1| 0| t\n" + "2| 2| t\n" + "2| 1| t\n" + "2| 0| t\n")); execute("select s1.id, s2.id, s1.table_name from sys.shards s1, sys.shards s2"); assertThat(response.rowCount(), is(9L)); List<Object[]> rows = Arrays.asList(response.rows()); Collections.sort(rows, OrderingByPosition.arrayOrdering( new int[]{0, 1}, new boolean[]{false, true}, new boolean[]{false, true})); assertThat(printedTable(new CollectionBucket(rows)), is("" + "0| 2| t\n" + "0| 1| t\n" + "0| 0| t\n" + "1| 2| t\n" + "1| 1| t\n" + "1| 0| t\n" + "2| 2| t\n" + "2| 1| t\n" + "2| 0| t\n")); } @Test public void testJoinUsingSubscriptInQuerySpec() { execute("create table t1 (id int, a object as (b int))"); execute("create table t2 (id int)"); execute("insert into t1 (id, a) values (1, {b=1})"); execute("insert into t2 (id) values (1)"); refresh(); execute("select t.id, tt.id from t1 as t, t2 as tt where tt.id = t.a['b']"); assertThat(printedTable(response.rows()), is("1| 1\n")); } @Test public void testCrossJoinFromInformationSchemaTable() throws Exception { // sys table with doc granularity on single node execute("select * from information_schema.schemata t1, information_schema.schemata t2 " + "order by t1.schema_name, t2.schema_name"); assertThat(response.rowCount(), is(25L)); assertThat(printedTable(response.rows()), is("" + "blob| blob\n" + "blob| doc\n" + "blob| information_schema\n" + "blob| pg_catalog\n" + "blob| sys\n" + "doc| blob\n" + "doc| doc\n" + "doc| information_schema\n" + "doc| pg_catalog\n" + "doc| sys\n" + "information_schema| blob\n" + "information_schema| doc\n" + "information_schema| information_schema\n" + "information_schema| pg_catalog\n" + "information_schema| sys\n" + "pg_catalog| blob\n" + "pg_catalog| doc\n" + "pg_catalog| information_schema\n" + "pg_catalog| pg_catalog\n" + "pg_catalog| sys\n" + "sys| blob\n" + "sys| doc\n" + "sys| information_schema\n" + "sys| pg_catalog\n" + "sys| sys\n")); } @Test public void testSelfJoin() throws Exception { execute("create table t (x int)"); ensureYellow(); execute("insert into t (x) values (1), (2)"); execute("refresh table t"); execute("select * from t as t1, t as t2"); assertThat(response.rowCount(), is(4L)); assertThat(Arrays.asList(response.rows()), containsInAnyOrder(new Object[]{1, 1}, new Object[]{1, 2}, new Object[]{2, 1}, new Object[]{2, 2})); } @Test public void testSelfJoinWithOrder() throws Exception { execute("create table t (x int)"); execute("insert into t (x) values (1), (2)"); execute("refresh table t"); execute("select * from t as t1, t as t2 order by t1.x, t2.x"); assertThat(printedTable(response.rows()), is("1| 1\n" + "1| 2\n" + "2| 1\n" + "2| 2\n")); } @Test public void test_self_join_with_order_and_limit_is_executed_with_qtf() throws Exception { execute("create table doc.t (x int, y int)"); execute("insert into doc.t (x, y) values (1, 10), (2, 20)"); execute("refresh table doc.t"); execute("explain select * from doc.t as t1, doc.t as t2 order by t1.x, t2.x limit 3"); assertThat(printedTable(response.rows()), is( "Fetch[x, y, x, y]\n" + " └ Limit[3::bigint;0]\n" + " └ OrderBy[x ASC x ASC]\n" + " └ NestedLoopJoin[CROSS]\n" + " ├ Rename[t1._fetchid, x] AS t1\n" + " │ └ Collect[doc.t | [_fetchid, x] | true]\n" + " └ Rename[t2._fetchid, x] AS t2\n" + " └ Collect[doc.t | [_fetchid, x] | true]\n" )); execute("select * from doc.t as t1, doc.t as t2 order by t1.x, t2.x limit 3"); assertThat(printedTable(response.rows()), is( "1| 10| 1| 10\n" + "1| 10| 2| 20\n" + "2| 20| 1| 10\n" )); } @Test public void testFilteredSelfJoin() throws Exception { execute("create table employees (salary float, name string)"); ensureYellow(); execute("insert into employees (salary, name) values (600, 'Trillian'), (200, 'Ford Perfect'), (800, 'Douglas Adams')"); execute("refresh table employees"); execute("select more.name, less.name, (more.salary - less.salary) from employees as more, employees as less " + "where more.salary > less.salary " + "order by more.salary desc, less.salary desc"); assertThat(printedTable(response.rows()), is("Douglas Adams| Trillian| 200.0\n" + "Douglas Adams| Ford Perfect| 600.0\n" + "Trillian| Ford Perfect| 400.0\n")); } @Test public void testFilteredSelfJoinWithFilterOnBothRelations() { execute("create table test(id long primary key, num long, txt string) with (number_of_replicas=0)"); ensureYellow(); execute("insert into test(id, num, txt) values(1, 1, '1111'), (2, 2, '2222'), (3, 1, '2222'), (4, 2, '2222')"); execute("refresh table test"); execute("select t1.id, t2.id from test as t1 inner join test as t2 on t1.num = t2.num " + "where t1.txt = '1111' and t2.txt='2222'"); assertThat(TestingHelpers.printedTable(response.rows()), is("1| 3\n")); } @Test public void testFilteredJoin() throws Exception { execute("create table employees (size float, name string)"); execute("create table offices (height float, name string)"); ensureYellow(); execute("insert into employees (size, name) values (1.5, 'Trillian'), (1.3, 'Ford Perfect'), (1.96, 'Douglas Adams')"); execute("insert into offices (height, name) values (1.5, 'Hobbit House'), (1.6, 'Entresol'), (2.0, 'Chief Office')"); execute("refresh table employees, offices"); // which employee fits in which office? execute("select employees.name, offices.name from employees inner join offices on size < height " + "where size < height order by height - size limit 3"); assertThat(printedTable(response.rows()), is("" + "Douglas Adams| Chief Office\n" + "Trillian| Entresol\n" + "Ford Perfect| Hobbit House\n")); } @Test public void testFetchWithoutOrder() throws Exception { createColorsAndSizes(); execute("select colors.name, sizes.name from colors, sizes limit 3"); assertThat(response.rowCount(), is(3L)); } @Test public void testJoinWithFunctionInOutputAndOrderBy() throws Exception { createColorsAndSizes(); execute("select substr(colors.name, 0, 1), sizes.name from colors, sizes order by colors.name, sizes.name limit 3"); assertThat(printedTable(response.rows()), is("b| large\n" + "b| small\n" + "g| large\n")); } private void createColorsAndSizes() { execute("create table colors (name string) "); execute("create table sizes (name string) "); ensureYellow(); execute("insert into colors (name) values (?)", new Object[][]{ new Object[]{"red"}, new Object[]{"blue"}, new Object[]{"green"} }); execute("insert into sizes (name) values (?)", new Object[][]{ new Object[]{"small"}, new Object[]{"large"}, }); execute("refresh table colors, sizes"); } @Test public void testJoinTableWithEmptyRouting() throws Exception { // no shards in sys.shards -> empty routing execute("SELECT s.id, n.id, n.name FROM sys.shards s, sys.nodes n"); assertThat(response.cols(), arrayContaining("id", "id", "name")); assertThat(response.rowCount(), is(0L)); } @Test public void testFilteredJoinWithPartitionsAndSelectFromOnlyOneTable() throws Exception { execute("create table users ( " + "id int primary key, " + "name string, " + "gender string primary key" + ") partitioned by (gender) with (number_of_replicas = 0)"); execute("create table events ( " + "name string, " + "user_id int)"); ensureYellow(); execute("insert into users (id, name, gender) values " + "(1, 'Arthur', 'male'), " + "(2, 'Trillian', 'female'), " + "(3, 'Marvin', 'android'), " + "(4, 'Slartibartfast', 'male')"); execute("insert into events (name, user_id) values ('a', 1), ('a', 2), ('b', 1)"); execute("refresh table users, events"); ensureYellow(); // wait for shards of new partitions execute("select users.* from users join events on users.id = events.user_id order by users.id"); } @Test public void testJoinWithFilterAndJoinCriteriaNotInOutputs() throws Exception { execute("create table t_left (id long primary key, temp float, ref_id int) clustered into 2 shards with (number_of_replicas = 0)"); execute("create table t_right (id int primary key, name string) clustered into 2 shards with (number_of_replicas = 0)"); ensureYellow(); execute("insert into t_left (id, temp, ref_id) values (1, 23.2, 1), (2, 20.8, 1), (3, 19.7, 1), (4, -0.5, 2), (5, -1.2, 2), (6, 0.2, 2)"); execute("refresh table t_left"); execute("insert into t_right (id, name) values (1, 'San Francisco'), (2, 'Vienna')"); execute("refresh table t_right"); execute("select temp, name from t_left inner join t_right on t_left.ref_id = t_right.id order by temp"); assertThat(TestingHelpers.printedTable(response.rows()), is("-1.2| Vienna\n" + "-0.5| Vienna\n" + "0.2| Vienna\n" + "19.7| San Francisco\n" + "20.8| San Francisco\n" + "23.2| San Francisco\n")); } @Test public void test3TableCrossJoin() throws Exception { execute("create table t1 (x int)"); execute("create table t2 (x int)"); execute("create table t3 (x int)"); ensureYellow(); execute("insert into t1 (x) values (1)"); execute("insert into t2 (x) values (2)"); execute("insert into t3 (x) values (3)"); execute("refresh table t1, t2, t3"); execute("select * from t1, t2, t3"); assertThat(response.rowCount(), is(1L)); assertThat(TestingHelpers.printedTable(response.rows()), is("1| 2| 3\n")); } @Test public void test3TableJoinWithJoinFilters() throws Exception { execute("create table users (id int primary key, name string) with (number_of_replicas = 0)"); execute("create table events (id int primary key, name string) with (number_of_replicas = 0)"); execute("create table logs (user_id int, event_id int) with (number_of_replicas = 0)"); ensureYellow(); execute("insert into users (id, name) values (1, 'Arthur'), (2, 'Trillian')"); execute("insert into events (id, name) values (1, 'Earth destroyed')"); execute("insert into events (id, name) values (2, 'Hitch hiking on a vogon ship')"); execute("insert into events (id, name) values (3, 'Meeting Arthur')"); execute("insert into logs (user_id, event_id) values (1, 1), (1, 2), (2, 3)"); execute("refresh table users, events, logs"); execute("select users.name, events.name " + "from users " + "join logs on users.id = logs.user_id " + "join events on events.id = logs.event_id " + "order by users.name, events.id"); assertThat(TestingHelpers.printedTable(response.rows()), is("Arthur| Earth destroyed\n" + "Arthur| Hitch hiking on a vogon ship\n" + "Trillian| Meeting Arthur\n")); } @Test public void testFetchArrayAndAnalyzedColumnsWithJoin() throws Exception { execute("create table t1 (id int primary key, text string index using fulltext)"); execute("create table t2 (id int primary key, tags array(string))"); ensureYellow(); execute("insert into t1 (id, text) values (1, 'Hello World')"); execute("insert into t2 (id, tags) values (1, ['foo', 'bar'])"); execute("refresh table t1, t2"); execute("select text, tags from t1 join t2 on t1.id = t2.id"); assertThat(TestingHelpers.printedTable(response.rows()), is("Hello World| [foo, bar]\n")); } @Test public void test3TableJoinWithFunctionOrderBy() throws Exception { execute("create table t1 (x integer)"); execute("create table t2 (y integer)"); execute("create table t3 (z integer)"); ensureYellow(); execute("insert into t1 (x) values (1)"); execute("insert into t2 (y) values (2)"); execute("insert into t3 (z) values (3)"); execute("refresh table t1, t2, t3"); execute("select x+y+z from t1,t2,t3 order by x,y,z"); assertThat(TestingHelpers.printedTable(response.rows()), is("6\n")); } @Test public void testOrderByExpressionWithMultiRelationSymbol() throws Exception { execute("create table t1 (x integer)"); execute("create table t2 (y integer)"); execute("create table t3 (z integer)"); ensureYellow(); execute("insert into t1 (x) values (3), (1)"); execute("insert into t2 (y) values (4), (2)"); execute("insert into t3 (z) values (5), (6)"); execute("refresh table t1, t2, t3"); execute("select x,y,z from t1,t2,t3 order by x-y+z, x+y"); assertThat(TestingHelpers.printedTable(response.rows()), is("1| 4| 5\n" + "1| 4| 6\n" + "1| 2| 5\n" + "3| 4| 5\n" + "1| 2| 6\n" + "3| 4| 6\n" + "3| 2| 5\n" + "3| 2| 6\n")); } @Test public void testSimpleOrderByNonUniqueValues() throws Exception { execute("create table t1 (a integer)"); execute("create table t2 (x integer)"); ensureYellow(); execute("insert into t1 (a) values (1), (1), (2), (2)"); execute("insert into t2 (x) values (1), (2)"); execute("refresh table t1, t2"); execute("select a, x from t1, t2 order by a, x"); assertThat(TestingHelpers.printedTable(response.rows()), is("1| 1\n" + "1| 1\n" + "1| 2\n" + "1| 2\n" + "2| 1\n" + "2| 1\n" + "2| 2\n" + "2| 2\n")); } @Test public void testJoinOnInformationSchema() throws Exception { execute("create table t (id string, name string)"); ensureYellow(); execute("insert into t (id, name) values ('0-1', 'Marvin')"); execute("refresh table t"); execute("select * from t inner join information_schema.tables on t.id = tables.number_of_replicas"); assertThat(response.rowCount(), is(1L)); } @Test public void testJoinWithIndexMissingExceptions() throws Throwable { execute("create table t1 (x int)"); execute("create table t2 (x int)"); ensureYellow(); execute("insert into t1 (x) values (1)"); execute("insert into t2 (x) values (2)"); execute("refresh table t1, t2"); PlanForNode plan = plan("select * from t1, t2 where t1.x = t2.x"); execute("drop table t2"); expectedException.expect(IndexNotFoundException.class); try { execute(plan).getResult(); } catch (Throwable t) { throw SQLExceptions.unwrap(t); } } @Test public void testAggOnJoin() throws Exception { execute("create table t1 (x int)"); ensureYellow(); execute("insert into t1 (x) values (1), (2)"); execute("refresh table t1"); execute("select sum(t1.x) from t1, t1 as t2"); assertThat(TestingHelpers.printedTable(response.rows()), is("6\n")); } @Test public void testAggOnJoinWithScalarAfterAggregation() throws Exception { execute("select sum(t1.col1) * 2 from unnest([1, 2]) t1, unnest([3, 4]) t2"); assertThat(TestingHelpers.printedTable(response.rows()), is("12\n")); } @Test public void testAggOnJoinWithHaving() throws Exception { execute("select sum(t1.col1) from unnest([1, 2]) t1, unnest([3, 4]) t2 having sum(t1.col1) > 8"); assertThat(response.rowCount(), is(0L)); } @Test public void testAggOnJoinWithLimit() throws Exception { execute("select " + " sum(t1.col1) " + "from unnest([1, 2]) t1, unnest([3, 4]) t2 " + "limit 0"); assertThat(response.rowCount(), is(0L)); } @Test public void testLimitIsAppliedPostJoin() throws Exception { execute("select " + " sum(t1.col1) " + "from unnest([1, 1]) t1, unnest([1, 1]) t2 " + "limit 1"); assertThat(TestingHelpers.printedTable(response.rows()), is("4\n")); } @Test public void testJoinOnAggWithOrderBy() throws Exception { execute("select sum(t1.col1) from unnest([1, 1]) t1, unnest([1, 1]) t2 order by 1"); assertThat(TestingHelpers.printedTable(response.rows()), is("4\n")); } @Test public void testFailureOfJoinDownstream() throws Exception { // provoke an exception when the NL emits a row, must bubble up and NL must stop expectedException.expectMessage("Cannot cast "); execute("select cast(R.col2 || ' ' || L.col2 as integer)" + " from " + " unnest(['hello', 'world'], [1, 2]) L " + " inner join " + " unnest(['world', 'hello'], [1, 2]) R " + " on l.col1 = r.col1 " + "where r.col1 > 1"); } @Test public void testGlobalAggregateMultiTableJoin() throws Exception { execute("create table t1 (id int primary key, t2 int, val double)"); execute("create table t2 (id int primary key, t3 int)"); execute("create table t3 (id int primary key)"); ensureYellow(); execute("insert into t3 (id) values (1), (2)"); execute("insert into t2 (id, t3) values (1, 1), (2, 1), (3, 2), (3, 4)"); execute("insert into t1 (id, t2, val) values (1, 1, 0.12), (2, 2, 1.23), (3, 3, 2.34), (4, 4, 3.45)"); refresh(); execute("select sum(t1.val), avg(t2.id), min(t3.id) from t1 inner join t2 on t1.t2 = t2.id inner join t3 on t2.t3 = t3.id"); assertThat(TestingHelpers.printedTable(response.rows()), is("3.69| 2.0| 1\n")); } @Test public void testJoinWithWhereOnPartitionColumnThatDoesNotMatch() throws Exception { execute("create table t (id int, p int) clustered into 1 shards partitioned by (p)"); execute("insert into t (id, p) values (1, 1), (2, 2)"); ensureYellow(); // regression test: // whereClause with query on partitioned column becomes a noMatch after normalization on collector // which leads to using RowsBatchIterator.empty() which always had a columnSize of 0 execute("select * from t as t1 inner join t as t2 on t1.id = t2.id where t2.p = 2"); } @Test public void testJoinOnSimpleVirtualTables() throws Exception { execute("select * from " + " (select col1 as x from unnest([1, 2, 3])) t1, " + " (select max(col1) as y from unnest([4])) t2 " + "order by t1.x"); assertThat(printedTable(response.rows()), is("1| 4\n" + "2| 4\n" + "3| 4\n")); } @Test public void testJoinOnComplexVirtualTable() throws Exception { execute("create table t1 (x int)"); ensureYellow(); execute("insert into t1 (x) values (1), (2), (3), (4)"); execute("refresh table t1"); execute("select * from " + " (select x from " + " (select x from t1 order by x asc limit 4) tt1 " + " order by tt1.x desc limit 2 " + " ) ttt1, " + " (select col1 as y from unnest([10])) tt2 "); assertThat(printedTable(response.rows()), is("4| 10\n" + "3| 10\n")); execute("select * from " + " (select x from " + " (select x from t1 order by x asc limit 4) tt1 " + " order by tt1.x desc limit 2 " + " ) ttt1, " + " (select max(y) as y from " + " (select min(col1) as y from unnest([10])) tt2 " + " ) ttt2 "); assertThat(printedTable(response.rows()), is("4| 10\n" + "3| 10\n")); } @Test public void testJoinOnVirtualTableWithQTF() throws Exception { execute("create table customers (" + "id long," + "name string," + "country string," + "company_id long" + ")"); ensureYellow(); execute("insert into customers (id, name, country, company_id) values(1, 'Marios', 'Greece', 1) "); execute("refresh table customers"); execute("create table orders (" + "id long," + "customer_id long," + "price float" + ")"); ensureYellow(); execute("insert into orders(id, customer_id, price) values (1,1,20.0), (2,1,10.0), (3,1,30.0), (4,1,40.0), (5,1,50.0)"); execute("refresh table orders"); String stmt = "SELECT t1.company_id, t1.country, t1.id, t1.name, t2.customer_id, t2.id, t2.price FROM" + " customers t1, " + " (SELECT * FROM (SELECT * from orders order by price desc limit 4) t ORDER BY price limit 3) t2 " + "WHERE t2.customer_id = t1.id " + "order by price limit 3 offset 1"; execute(stmt); assertThat(printedTable(response.rows()), is("1| Greece| 1| Marios| 1| 3| 30.0\n" + "1| Greece| 1| Marios| 1| 4| 40.0\n")); } @Test public void testJoinOnVirtualTableWithSingleRowSubselect() throws Exception { execute("SELECT\n" + " (select min(t1.x) from\n" + " (select col1 as x from unnest([1, 2, 3])) t1,\n" + " (select * from unnest([1, 2, 3])) t2\n" + " ) as min_col1,\n" + " *\n" + " FROM\n" + " unnest([1]) tt1," + " unnest([2]) tt2"); assertThat(printedTable(response.rows()), is("1| 1| 2\n")); } @Test @UseHashJoins(1) public void testInnerEquiJoinUsingHashJoin() { execute("create table t1 (a integer)"); execute("create table t2 (x integer)"); ensureYellow(); execute("insert into t1 (a) values (0), (0), (1), (2), (4)"); execute("insert into t2 (x) values (1), (3), (3), (4), (4)"); execute("refresh table t1, t2"); long memoryLimit = 6 * 1024 * 1024; double overhead = 1.0d; execute("set global \"indices.breaker.query.limit\" = '" + memoryLimit + "b', " + "\"indices.breaker.query.overhead\" = " + overhead); CircuitBreaker queryCircuitBreaker = internalCluster().getInstance(CircuitBreakerService.class).getBreaker(HierarchyCircuitBreakerService.QUERY); randomiseAndConfigureJoinBlockSize("t1", 5L, queryCircuitBreaker); randomiseAndConfigureJoinBlockSize("t2", 5L, queryCircuitBreaker); execute("select a, x from t1 join t2 on t1.a + 1 = t2.x order by a, x"); assertThat(TestingHelpers.printedTable(response.rows()), is("0| 1\n" + "0| 1\n" + "2| 3\n" + "2| 3\n")); } private void resetTableStats() { for (TableStats tableStats : internalCluster().getInstances(TableStats.class)) { tableStats.updateTableStats(new HashMap<>()); } } @Test public void testJoinWithLargerRightBranch() throws Exception { execute("create table t1 (a integer)"); execute("create table t2 (x integer)"); ensureYellow(); execute("insert into t1 (a) values (0), (1), (1), (2)"); execute("insert into t2 (x) values (1), (3), (4), (4), (5), (6)"); execute("refresh table t1, t2"); Iterable<TableStats> tableStatsOnAllNodes = internalCluster().getInstances(TableStats.class); for (TableStats tableStats : tableStatsOnAllNodes) { Map<RelationName, Stats> newStats = new HashMap<>(); newStats.put(new RelationName(sqlExecutor.getCurrentSchema(), "t1"), new Stats(4L, 16L, Map.of())); newStats.put(new RelationName(sqlExecutor.getCurrentSchema(), "t2"), new Stats(6L, 24L, Map.of())); tableStats.updateTableStats(newStats); } execute("select a, x from t1 join t2 on t1.a + 1 = t2.x + 1 order by a, x"); assertThat(TestingHelpers.printedTable(response.rows()), is("1| 1\n" + "1| 1\n")); } /** * some implementations will apply the branch reordering optimisation, whilst others might not. * either way, all join implementations should yield the same results */ @Test public void testJoinBranchReorderingOnMultipleTables() throws Exception { execute("create table t1 (a integer)"); execute("create table t2 (x integer)"); execute("create table t3 (y integer)"); ensureYellow(); execute("insert into t1 (a) values (0), (1)"); execute("insert into t2 (x) values (0), (1), (2)"); execute("insert into t3 (y) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)"); execute("refresh table t1, t2, t3"); Iterable<TableStats> tableStatsOnAllNodes = internalCluster().getInstances(TableStats.class); for (TableStats tableStats : tableStatsOnAllNodes) { Map<RelationName, Stats> newStats = new HashMap<>(); newStats.put(new RelationName(sqlExecutor.getCurrentSchema(), "t1"), new Stats(2L, 8L, Map.of())); newStats.put(new RelationName(sqlExecutor.getCurrentSchema(), "t2"), new Stats(3L, 12L, Map.of())); newStats.put(new RelationName(sqlExecutor.getCurrentSchema(), "t3"), new Stats(10L, 40L, Map.of())); tableStats.updateTableStats(newStats); } execute("select a, x, y from t1 join t2 on t1.a = t2.x join t3 on t3.y = t2.x where t1.a < t2.x + 1 " + "and t2.x < t3.y + 1 order by a, x, y"); assertThat(TestingHelpers.printedTable(response.rows()), is("0| 0| 0\n" + "1| 1| 1\n")); } @Test public void test_block_NestedLoop_or_HashJoin__with_group_by_on_right_side() { execute("create table t1 (x integer)"); execute("insert into t1 (x) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)"); execute("refresh table t1"); long memoryLimit = 6 * 1024 * 1024; double overhead = 1.0d; execute("set global \"indices.breaker.query.limit\" = '" + memoryLimit + "b', " + "\"indices.breaker.query.overhead\" = " + overhead); CircuitBreaker queryCircuitBreaker = internalCluster().getInstance(CircuitBreakerService.class).getBreaker(HierarchyCircuitBreakerService.QUERY); randomiseAndConfigureJoinBlockSize("t1", 10L, queryCircuitBreaker); execute("select x from t1 left_rel JOIN (select x x2, count(x) from t1 group by x2) right_rel " + "ON left_rel.x = right_rel.x2 order by left_rel.x"); assertThat(TestingHelpers.printedTable(response.rows()), is("0\n" + "1\n" + "2\n" + "3\n" + "4\n" + "5\n" + "6\n" + "7\n" + "8\n" + "9\n")); } private void randomiseAndConfigureJoinBlockSize(String relationName, long rowsCount, CircuitBreaker circuitBreaker) { long availableMemory = circuitBreaker.getLimit() - circuitBreaker.getUsed(); // We're randomising the table size we configure in the stats in a way such that the number of rows that fit // in memory is sometimes less than the row count of the table (ie. multiple blocks are created) and sometimes // the entire table fits in memory (ie. one block is used) long tableSizeInBytes = new Random().nextInt(3 * (int) availableMemory); long rowSizeBytes = tableSizeInBytes / rowsCount; for (TableStats tableStats : internalCluster().getInstances(TableStats.class)) { Map<RelationName, Stats> newStats = new HashMap<>(); newStats.put(new RelationName(sqlExecutor.getCurrentSchema(), relationName), new Stats(rowsCount, tableSizeInBytes, Map.of())); tableStats.updateTableStats(newStats); } RamBlockSizeCalculator ramBlockSizeCalculator = new RamBlockSizeCalculator( 500_000, circuitBreaker, rowSizeBytes, rowsCount ); logger.info("\n\tThe block size for relation {}, total size {} bytes, with row count {} and row size {} bytes, " + "if it would be used in a block join algorithm, would be {}", relationName, tableSizeInBytes, rowsCount, rowSizeBytes, ramBlockSizeCalculator.getAsInt()); } @Test public void testInnerJoinWithPushDownOptimizations() { execute("CREATE TABLE t1 (id INTEGER)"); execute("CREATE TABLE t2 (id INTEGER, name STRING, id_t1 INTEGER)"); execute("INSERT INTO t1 (id) VALUES (1), (2)"); execute("INSERT INTO t2 (id, name, id_t1) VALUES (1, 'A', 1), (2, 'B', 2), (3, 'C', 2)"); execute("REFRESH TABLE t1, t2"); assertThat(printedTable(execute( "SELECT t1.id, t2.id FROM t2 INNER JOIN t1 ON t1.id = t2.id_t1 ORDER BY lower(t2.name)").rows()), is("1| 1\n" + "2| 2\n" + "2| 3\n") ); assertThat(printedTable(execute( "SELECT t1.id, t2.id, t2.name FROM t2 INNER JOIN t1 ON t1.id = t2.id_t1 ORDER BY lower(t2.name)").rows()), is("1| 1| A\n" + "2| 2| B\n" + "2| 3| C\n")); assertThat(printedTable(execute( "SELECT t1.id, t2.id, lower(t2.name) FROM t2 INNER JOIN t1 ON t1.id = t2.id_t1 ORDER BY lower(t2.name)").rows()), is("1| 1| a\n" + "2| 2| b\n" + "2| 3| c\n") ); } @Test public void testInnerJoinOnPreSortedRightRelation() { execute("CREATE TABLE t1 (x int) with (number_of_replicas = 0)"); execute("insert into t1 (x) values (1) "); execute("refresh table t1"); // regression test; the repeat requirement wasn't set correctly for the right side assertThat( printedTable(execute( "select * from (select * from t1 order by x) t1 " + "join (select * from t1 order by x) t2 on t1.x=t2.x").rows()), is("1| 1\n") ); } @Test public void test_join_with_and_false_in_where_clause_returns_empty_result() { String stmt = "SELECT n.* " + "FROM " + " pg_catalog.pg_namespace n," + " pg_catalog.pg_class c " + "WHERE " + " n.nspname LIKE E'sys' " + " AND c.relnamespace = n.oid " + " AND (false)"; execute(stmt); assertThat(response.rowCount(), is(0L)); } @Test public void test_many_table_join_with_filter_pushdown() throws Exception { // regression this; optimization rule resulted in a endless loop String stmt = "" + "SELECT\n" + " *\n" + "FROM\n" + " pg_catalog.pg_namespace pkn,\n" + " pg_catalog.pg_class pkc,\n" + " pg_catalog.pg_attribute pka,\n" + " pg_catalog.pg_namespace fkn,\n" + " pg_catalog.pg_class fkc,\n" + " pg_catalog.pg_attribute fka,\n" + " pg_catalog.pg_constraint con,\n" + " pg_catalog.generate_series(1, 32) pos (n),\n" + " pg_catalog.pg_class pkic\n" + "WHERE\n" + " pkn.oid = pkc.relnamespace\n" + " AND pkc.oid = pka.attrelid\n" + " AND pka.attnum = con.confkey[pos.n]\n" + " AND con.confrelid = pkc.oid\n" + " AND fkn.oid = fkc.relnamespace\n" + " AND fkc.oid = fka.attrelid\n" + " AND fka.attnum = con.conkey[pos.n]\n" + " AND con.conrelid = fkc.oid\n" + " AND con.contype = 'f'\n" + " AND pkic.relkind = 'i'\n" + " AND pkic.oid = con.conindid\n" + " AND pkn.nspname = E'sys'\n" + " AND fkn.nspname = E'sys'\n" + " AND pkc.relname = E'jobs'\n" + " AND fkc.relname = E'jobs_log'\n" + "ORDER BY\n" + " fkn.nspname,\n" + " fkc.relname,\n" + " con.conname,\n" + " pos.n\n"; execute(stmt); assertThat(response.rowCount(), is(0L)); } }