| /* |
| * Licensed to the Apache Software Foundation (ASF) under one or more |
| * contributor license agreements. See the NOTICE file distributed with |
| * this work for additional information regarding copyright ownership. |
| * The ASF 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. |
| */ |
| package org.apache.calcite.test; |
| |
| import org.apache.calcite.adapter.enumerable.EnumerableConvention; |
| import org.apache.calcite.adapter.java.ReflectiveSchema; |
| import org.apache.calcite.jdbc.CalciteConnection; |
| import org.apache.calcite.plan.Contexts; |
| import org.apache.calcite.plan.Convention; |
| import org.apache.calcite.plan.RelOptTable; |
| import org.apache.calcite.plan.RelTraitDef; |
| import org.apache.calcite.rel.RelCollations; |
| import org.apache.calcite.rel.RelDistributions; |
| import org.apache.calcite.rel.RelNode; |
| import org.apache.calcite.rel.core.AggregateCall; |
| import org.apache.calcite.rel.core.Correlate; |
| import org.apache.calcite.rel.core.CorrelationId; |
| import org.apache.calcite.rel.core.Exchange; |
| import org.apache.calcite.rel.core.JoinRelType; |
| import org.apache.calcite.rel.core.Project; |
| import org.apache.calcite.rel.core.Sort; |
| import org.apache.calcite.rel.core.TableFunctionScan; |
| import org.apache.calcite.rel.core.TableModify; |
| import org.apache.calcite.rel.core.Window; |
| import org.apache.calcite.rel.hint.RelHint; |
| import org.apache.calcite.rel.type.RelDataType; |
| import org.apache.calcite.rel.type.RelDataTypeFactory; |
| import org.apache.calcite.rel.type.RelDataTypeField; |
| import org.apache.calcite.rex.RexBuilder; |
| import org.apache.calcite.rex.RexCorrelVariable; |
| import org.apache.calcite.rex.RexFieldCollation; |
| import org.apache.calcite.rex.RexInputRef; |
| import org.apache.calcite.rex.RexNode; |
| import org.apache.calcite.rex.RexWindowBounds; |
| import org.apache.calcite.runtime.CalciteException; |
| import org.apache.calcite.schema.SchemaPlus; |
| import org.apache.calcite.schema.TableFunction; |
| import org.apache.calcite.schema.impl.TableFunctionImpl; |
| import org.apache.calcite.schema.impl.ViewTable; |
| import org.apache.calcite.schema.impl.ViewTableMacro; |
| import org.apache.calcite.sql.SqlIdentifier; |
| import org.apache.calcite.sql.SqlKind; |
| import org.apache.calcite.sql.SqlMatchRecognize; |
| import org.apache.calcite.sql.SqlOperator; |
| import org.apache.calcite.sql.fun.SqlLibraryOperators; |
| import org.apache.calcite.sql.fun.SqlStdOperatorTable; |
| import org.apache.calcite.sql.parser.SqlParser; |
| import org.apache.calcite.sql.parser.SqlParserPos; |
| import org.apache.calcite.sql.type.InferTypes; |
| import org.apache.calcite.sql.type.OperandTypes; |
| import org.apache.calcite.sql.type.ReturnTypes; |
| import org.apache.calcite.sql.type.SqlOperandMetadata; |
| import org.apache.calcite.sql.type.SqlTypeFamily; |
| import org.apache.calcite.sql.type.SqlTypeName; |
| import org.apache.calcite.sql.validate.SqlUserDefinedTableFunction; |
| import org.apache.calcite.test.schemata.hr.HrSchema; |
| import org.apache.calcite.tools.Frameworks; |
| import org.apache.calcite.tools.Programs; |
| import org.apache.calcite.tools.RelBuilder; |
| import org.apache.calcite.tools.RelRunner; |
| import org.apache.calcite.tools.RelRunners; |
| import org.apache.calcite.util.Holder; |
| import org.apache.calcite.util.ImmutableBitSet; |
| import org.apache.calcite.util.Pair; |
| import org.apache.calcite.util.Smalls; |
| import org.apache.calcite.util.TimestampString; |
| import org.apache.calcite.util.Util; |
| import org.apache.calcite.util.mapping.Mappings; |
| |
| import com.google.common.collect.ImmutableList; |
| import com.google.common.collect.ImmutableMap; |
| import com.google.common.collect.ImmutableSet; |
| import com.google.common.collect.Lists; |
| |
| import org.checkerframework.checker.nullness.qual.Nullable; |
| import org.hamcrest.Matcher; |
| import org.junit.jupiter.api.Test; |
| |
| import java.lang.reflect.Method; |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.util.ArrayList; |
| import java.util.Arrays; |
| import java.util.Collections; |
| import java.util.List; |
| import java.util.Locale; |
| import java.util.NoSuchElementException; |
| import java.util.TreeSet; |
| import java.util.concurrent.atomic.AtomicInteger; |
| import java.util.function.BiFunction; |
| import java.util.function.Function; |
| import java.util.function.UnaryOperator; |
| import java.util.stream.Collectors; |
| |
| import static org.apache.calcite.test.Matchers.hasHints; |
| import static org.apache.calcite.test.Matchers.hasTree; |
| |
| import static org.hamcrest.CoreMatchers.allOf; |
| import static org.hamcrest.CoreMatchers.containsString; |
| import static org.hamcrest.CoreMatchers.is; |
| import static org.hamcrest.CoreMatchers.nullValue; |
| import static org.hamcrest.MatcherAssert.assertThat; |
| import static org.junit.jupiter.api.Assertions.assertNull; |
| import static org.junit.jupiter.api.Assertions.assertThrows; |
| import static org.junit.jupiter.api.Assertions.assertTrue; |
| import static org.junit.jupiter.api.Assertions.fail; |
| |
| /** |
| * Unit test for {@link RelBuilder}. |
| * |
| * <p>Tasks:</p> |
| * <ol> |
| * <li>Add RelBuilder.scan(List<String>)</li> |
| * <li>Add RelBuilder.scan(Table)</li> |
| * <li>Test that {@link RelBuilder#filter} does not create a filter if the |
| * predicates optimize to true</li> |
| * <li>Test that {@link RelBuilder#filter} DOES create a filter if the |
| * predicates optimize to false. (Creating an empty Values seems too |
| * devious.)</li> |
| * <li>Test that {@link RelBuilder#scan} throws good error if table not |
| * found</li> |
| * <li>Test that {@link RelBuilder#scan} obeys case-sensitivity</li> |
| * <li>Test that {@link RelBuilder#join(JoinRelType, String...)} obeys |
| * case-sensitivity</li> |
| * <li>Test RelBuilder with alternative factories</li> |
| * <li>Test that {@link RelBuilder#field(String)} obeys case-sensitivity</li> |
| * <li>Test case-insensitive unique field names</li> |
| * <li>Test that an alias created using |
| * {@link RelBuilder#alias(RexNode, String)} is removed if not a top-level |
| * project</li> |
| * <li>{@link RelBuilder#aggregate} with grouping sets</li> |
| * <li>Add call to create {@link TableFunctionScan}</li> |
| * <li>Add call to create {@link Window}</li> |
| * <li>Add call to create {@link TableModify}</li> |
| * <li>Add call to create {@link Exchange}</li> |
| * <li>Add call to create {@link Correlate}</li> |
| * <li>Add call to create {@link AggregateCall} with filter</li> |
| * </ol> |
| */ |
| public class RelBuilderTest { |
| /** Creates a config based on the "scott" schema. */ |
| public static Frameworks.ConfigBuilder config() { |
| final SchemaPlus rootSchema = Frameworks.createRootSchema(true); |
| return Frameworks.newConfigBuilder() |
| .parserConfig(SqlParser.Config.DEFAULT) |
| .defaultSchema( |
| CalciteAssert.addSchema(rootSchema, CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL)) |
| .traitDefs((List<RelTraitDef>) null) |
| .programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2)); |
| } |
| |
| /** Creates a config builder that will contain a view, "MYVIEW", and also |
| * the SCOTT JDBC schema, whose tables implement |
| * {@link org.apache.calcite.schema.TranslatableTable}. */ |
| static Frameworks.ConfigBuilder expandingConfig(Connection connection) |
| throws SQLException { |
| final CalciteConnection calciteConnection = |
| connection.unwrap(CalciteConnection.class); |
| final SchemaPlus root = calciteConnection.getRootSchema(); |
| CalciteAssert.SchemaSpec spec = CalciteAssert.SchemaSpec.SCOTT; |
| CalciteAssert.addSchema(root, spec); |
| final String viewSql = |
| String.format(Locale.ROOT, "select * from \"%s\".\"%s\" where 1=1", |
| spec.schemaName, "EMP"); |
| |
| // create view |
| ViewTableMacro macro = ViewTable.viewMacro(root, viewSql, |
| Collections.singletonList("test"), Arrays.asList("test", "view"), false); |
| |
| // register view (in root schema) |
| root.add("MYVIEW", macro); |
| |
| return Frameworks.newConfigBuilder().defaultSchema(root); |
| } |
| |
| /** Creates a RelBuilder with default config. */ |
| static RelBuilder createBuilder() { |
| return createBuilder(c -> c); |
| } |
| |
| /** Creates a RelBuilder with transformed config. */ |
| static RelBuilder createBuilder(UnaryOperator<RelBuilder.Config> transform) { |
| final Frameworks.ConfigBuilder configBuilder = config(); |
| configBuilder.context( |
| Contexts.of(transform.apply(RelBuilder.Config.DEFAULT))); |
| return RelBuilder.create(configBuilder.build()); |
| } |
| |
| @Test void testScan() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| final RelNode root = |
| RelBuilder.create(config().build()) |
| .scan("EMP") |
| .build(); |
| assertThat(root, |
| hasTree("LogicalTableScan(table=[[scott, EMP]])\n")); |
| } |
| |
| @Test void testScanQualifiedTable() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM "scott"."emp" |
| final RelNode root = |
| RelBuilder.create(config().build()) |
| .scan("scott", "EMP") |
| .build(); |
| assertThat(root, |
| hasTree("LogicalTableScan(table=[[scott, EMP]])\n")); |
| } |
| |
| @Test void testScanInvalidTable() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM zzz |
| try { |
| final RelNode root = |
| RelBuilder.create(config().build()) |
| .scan("ZZZ") // this relation does not exist |
| .build(); |
| fail("expected error, got " + root); |
| } catch (Exception e) { |
| assertThat(e.getMessage(), is("Table 'ZZZ' not found")); |
| } |
| } |
| |
| @Test void testScanInvalidSchema() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM "zzz"."emp" |
| try { |
| final RelNode root = |
| RelBuilder.create(config().build()) |
| .scan("ZZZ", "EMP") // the table exists, but the schema does not |
| .build(); |
| fail("expected error, got " + root); |
| } catch (Exception e) { |
| assertThat(e.getMessage(), is("Table 'ZZZ.EMP' not found")); |
| } |
| } |
| |
| @Test void testScanInvalidQualifiedTable() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM "scott"."zzz" |
| try { |
| final RelNode root = |
| RelBuilder.create(config().build()) |
| .scan("scott", "ZZZ") // the schema is valid, but the table does not exist |
| .build(); |
| fail("expected error, got " + root); |
| } catch (Exception e) { |
| assertThat(e.getMessage(), is("Table 'scott.ZZZ' not found")); |
| } |
| } |
| |
| @Test void testScanValidTableWrongCase() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM "emp" |
| try { |
| final RelNode root = |
| RelBuilder.create(config().build()) |
| .scan("emp") // the table is named 'EMP', not 'emp' |
| .build(); |
| fail("Expected error (table names are case-sensitive), but got " + root); |
| } catch (Exception e) { |
| assertThat(e.getMessage(), is("Table 'emp' not found")); |
| } |
| } |
| |
| @Test void testScanFilterTrue() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE TRUE |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter(builder.literal(true)) |
| .build(); |
| assertThat(root, |
| hasTree("LogicalTableScan(table=[[scott, EMP]])\n")); |
| } |
| |
| @Test void testScanFilterTriviallyFalse() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE 1 = 2 |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter(builder.equals(builder.literal(1), builder.literal(2))) |
| .build(); |
| assertThat(root, |
| hasTree("LogicalValues(tuples=[[]])\n")); |
| } |
| |
| @Test void testScanFilterEquals() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno = 20 |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.equals(builder.field("DEPTNO"), builder.literal(20))) |
| .build(); |
| final String expected = "LogicalFilter(condition=[=($7, 20)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testScanFilterGreaterThan() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno > 20 |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.greaterThan(builder.field("DEPTNO"), builder.literal(20))) |
| .build(); |
| final String expected = "LogicalFilter(condition=[>($7, 20)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testSnapshotTemporalTable() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM products_temporal FOR SYSTEM_TIME AS OF TIMESTAMP '2011-07-20 12:34:56' |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("products_temporal") |
| .snapshot( |
| builder.getRexBuilder().makeTimestampLiteral( |
| new TimestampString("2011-07-20 12:34:56"), 0)) |
| .build(); |
| final String expected = "LogicalSnapshot(period=[2011-07-20 12:34:56])\n" |
| + " LogicalTableScan(table=[[scott, products_temporal]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testTableFunctionScan() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM TABLE( |
| // DEDUP(CURSOR(select * from emp), |
| // CURSOR(select * from DEPT), 'NAME')) |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final SqlOperator dedupFunction = |
| new MockSqlOperatorTable.DedupFunction(); |
| RelNode root = builder.scan("EMP") |
| .scan("DEPT") |
| .functionScan(dedupFunction, 2, builder.cursor(2, 0), |
| builder.cursor(2, 1)) |
| .build(); |
| final String expected = "LogicalTableFunctionScan(" |
| + "invocation=[DEDUP(CURSOR($0), CURSOR($1))], " |
| + "rowType=[RecordType(VARCHAR(1024) NAME)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| |
| // Make sure that the builder's stack is empty. |
| try { |
| RelNode node = builder.build(); |
| fail("expected error, got " + node); |
| } catch (NoSuchElementException e) { |
| assertNull(e.getMessage()); |
| } |
| } |
| |
| @Test void testTableFunctionScanZeroInputs() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM TABLE(RAMP(3)) |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final SqlOperator rampFunction = new MockSqlOperatorTable.RampFunction(); |
| RelNode root = builder.functionScan(rampFunction, 0, builder.literal(3)) |
| .build(); |
| final String expected = "LogicalTableFunctionScan(invocation=[RAMP(3)], " |
| + "rowType=[RecordType(INTEGER I)])\n"; |
| assertThat(root, hasTree(expected)); |
| |
| // Make sure that the builder's stack is empty. |
| try { |
| RelNode node = builder.build(); |
| fail("expected error, got " + node); |
| } catch (NoSuchElementException e) { |
| assertNull(e.getMessage()); |
| } |
| } |
| |
| /** Tests scanning a table function whose row type is determined by parsing a |
| * JSON argument. The arguments must therefore be available at prepare |
| * time. */ |
| @Test void testTableFunctionScanDynamicType() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM TABLE("dynamicRowType"('{nullable:true,fields:[...]}', 3)) |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final Method m = Smalls.DYNAMIC_ROW_TYPE_TABLE_METHOD; |
| final TableFunction tableFunction = |
| TableFunctionImpl.create(m.getDeclaringClass(), m.getName()); |
| final SqlOperator operator = |
| new SqlUserDefinedTableFunction( |
| new SqlIdentifier("dynamicRowType", SqlParserPos.ZERO), |
| SqlKind.OTHER_FUNCTION, ReturnTypes.CURSOR, InferTypes.ANY_NULLABLE, |
| Arg.metadata( |
| Arg.of("count", f -> f.createSqlType(SqlTypeName.INTEGER), |
| SqlTypeFamily.INTEGER, false), |
| Arg.of("typeJson", f -> f.createSqlType(SqlTypeName.VARCHAR), |
| SqlTypeFamily.STRING, false)), |
| tableFunction); |
| |
| final String jsonRowType = "{\"nullable\":false,\"fields\":[" |
| + " {\"name\":\"i\",\"type\":\"INTEGER\",\"nullable\":false}," |
| + " {\"name\":\"d\",\"type\":\"DATE\",\"nullable\":true}" |
| + "]}"; |
| final int rowCount = 3; |
| RelNode root = builder.functionScan(operator, 0, |
| builder.literal(jsonRowType), builder.literal(rowCount)) |
| .build(); |
| final String expected = "LogicalTableFunctionScan(" |
| + "invocation=[dynamicRowType('{\"nullable\":false,\"fields\":[" |
| + " {\"name\":\"i\",\"type\":\"INTEGER\",\"nullable\":false}," |
| + " {\"name\":\"d\",\"type\":\"DATE\",\"nullable\":true}]}', 3)], " |
| + "rowType=[RecordType(INTEGER i, DATE d)])\n"; |
| assertThat(root, hasTree(expected)); |
| |
| // Make sure that the builder's stack is empty. |
| try { |
| RelNode node = builder.build(); |
| fail("expected error, got " + node); |
| } catch (NoSuchElementException e) { |
| assertNull(e.getMessage()); |
| } |
| } |
| |
| @Test void testJoinTemporalTable() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM orders |
| // JOIN products_temporal FOR SYSTEM_TIME AS OF TIMESTAMP '2011-07-20 12:34:56' |
| // ON orders.product = products_temporal.id |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("orders") |
| .scan("products_temporal") |
| .snapshot( |
| builder.getRexBuilder().makeTimestampLiteral( |
| new TimestampString("2011-07-20 12:34:56"), 0)) |
| .join(JoinRelType.INNER, |
| builder.equals(builder.field(2, 0, "PRODUCT"), |
| builder.field(2, 1, "ID"))) |
| .build(); |
| final String expected = "LogicalJoin(condition=[=($2, $4)], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, orders]])\n" |
| + " LogicalSnapshot(period=[2011-07-20 12:34:56])\n" |
| + " LogicalTableScan(table=[[scott, products_temporal]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests that {@link RelBuilder#project} simplifies expressions if and only if |
| * {@link RelBuilder.Config#simplify}. */ |
| @Test void testSimplify() { |
| checkSimplify(c -> c.withSimplify(true), |
| hasTree("LogicalProject($f0=[true])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n")); |
| checkSimplify(c -> c, |
| hasTree("LogicalProject($f0=[true])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n")); |
| checkSimplify(c -> c.withSimplify(false), |
| hasTree("LogicalProject($f0=[IS NOT NULL($0)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n")); |
| } |
| |
| private void checkSimplify(UnaryOperator<RelBuilder.Config> transform, |
| Matcher<RelNode> matcher) { |
| final RelBuilder builder = createBuilder(transform); |
| final RelNode root = |
| builder.scan("EMP") |
| .project(builder.isNotNull(builder.field("EMPNO"))) |
| .build(); |
| assertThat(root, matcher); |
| } |
| |
| @Test void testScanFilterOr() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE (deptno = 20 OR comm IS NULL) AND mgr IS NOT NULL |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.or( |
| builder.equals(builder.field("DEPTNO"), |
| builder.literal(20)), |
| builder.isNull(builder.field(6))), |
| builder.isNotNull(builder.field(3))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[AND(OR(=($7, 20), IS NULL($6)), IS NOT NULL($3))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testScanFilterOr2() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno = 20 OR deptno = 20 |
| // simplifies to |
| // SELECT * |
| // FROM emp |
| // WHERE deptno = 20 |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.or( |
| builder.greaterThan(builder.field("DEPTNO"), |
| builder.literal(20)), |
| builder.greaterThan(builder.field("DEPTNO"), |
| builder.literal(20)))) |
| .build(); |
| final String expected = "LogicalFilter(condition=[>($7, 20)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testScanFilterAndFalse() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno = 20 AND FALSE |
| // simplifies to |
| // VALUES |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.greaterThan(builder.field("DEPTNO"), |
| builder.literal(20)), |
| builder.literal(false)) |
| .build(); |
| final String expected = "LogicalValues(tuples=[[]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testScanFilterAndTrue() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno = 20 AND TRUE |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.greaterThan(builder.field("DEPTNO"), |
| builder.literal(20)), |
| builder.literal(true)) |
| .build(); |
| final String expected = "LogicalFilter(condition=[>($7, 20)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-2730">[CALCITE-2730] |
| * RelBuilder incorrectly simplifies a filter with duplicate conjunction to |
| * empty</a>. */ |
| @Test void testScanFilterDuplicateAnd() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno > 20 AND deptno > 20 AND deptno > 20 |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| builder.scan("EMP"); |
| final RexNode condition = |
| builder.greaterThan(builder.field("DEPTNO"), builder.literal(20)); |
| final RexNode condition2 = |
| builder.lessThan(builder.field("DEPTNO"), builder.literal(30)); |
| final RelNode root = builder.filter(condition, condition, condition) |
| .build(); |
| final String expected = "LogicalFilter(condition=[>($7, 20)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno > 20 AND deptno < 30 AND deptno > 20 |
| final RelNode root2 = builder.scan("EMP") |
| .filter(condition, condition2, condition, condition) |
| .build(); |
| final String expected2 = "" |
| + "LogicalFilter(condition=[SEARCH($7, Sarg[(20..30)])])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root2, hasTree(expected2)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-4325">[CALCITE-4325] |
| * RexSimplify incorrectly simplifies complex expressions with Sarg and |
| * NULL</a>. */ |
| @Test void testFilterAndOrWithNull() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE (deptno <> 20 OR deptno IS NULL) AND deptno = 10 |
| // Should be simplified to: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno = 10 |
| // With [CALCITE-4325], is incorrectly simplified to: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno = 10 OR deptno IS NULL |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .filter( |
| b.and( |
| b.or( |
| b.notEquals(b.field("DEPTNO"), b.literal(20)), |
| b.isNull(b.field("DEPTNO"))), |
| b.equals(b.field("DEPTNO"), b.literal(10)))) |
| .build(); |
| |
| final String expected = "LogicalFilter(condition=[=($7, 10)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| } |
| |
| @Test void testFilterAndOrWithNull2() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE (deptno = 20 OR deptno IS NULL) AND deptno = 10 |
| // Should be simplified to: |
| // No rows (WHERE FALSE) |
| // With [CALCITE-4325], is incorrectly simplified to: |
| // SELECT * |
| // FROM emp |
| // WHERE deptno IS NULL |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .filter( |
| b.and( |
| b.or(b.equals(b.field("DEPTNO"), b.literal(20)), |
| b.isNull(b.field("DEPTNO"))), |
| b.equals(b.field("DEPTNO"), b.literal(10)))) |
| .build(); |
| |
| final String expected = "LogicalValues(tuples=[[]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| } |
| |
| @Test void testBadFieldName() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| try { |
| RexInputRef ref = builder.scan("EMP").field("deptno"); |
| fail("expected error, got " + ref); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| is("field [deptno] not found; input fields are: [EMPNO, ENAME, JOB, " |
| + "MGR, HIREDATE, SAL, COMM, DEPTNO]")); |
| } |
| } |
| |
| @Test void testBadFieldOrdinal() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| try { |
| RexInputRef ref = builder.scan("DEPT").field(20); |
| fail("expected error, got " + ref); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| is("field ordinal [20] out of range; " |
| + "input fields are: [DEPTNO, DNAME, LOC]")); |
| } |
| } |
| |
| @Test void testBadType() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| try { |
| builder.scan("EMP"); |
| RexNode call = builder.call(SqlStdOperatorTable.PLUS, |
| builder.field(1), |
| builder.field(3)); |
| fail("expected error, got " + call); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| is("Cannot infer return type for +; " |
| + "operand types: [VARCHAR(10), SMALLINT]")); |
| } |
| } |
| |
| @Test void testProject() { |
| // Equivalent SQL: |
| // SELECT deptno, CAST(comm AS SMALLINT) AS comm, 20 AS $f2, |
| // comm AS comm3, comm AS c |
| // FROM emp |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .project(builder.field("DEPTNO"), |
| builder.cast(builder.field(6), SqlTypeName.SMALLINT), |
| builder.literal(20), |
| builder.field(6), |
| builder.alias(builder.field(6), "C")) |
| .build(); |
| // Note: CAST(COMM) gets the COMM alias because it occurs first |
| // Note: AS(COMM, C) becomes just $6 |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$7], COMM=[CAST($6):SMALLINT NOT NULL], $f2=[20], COMM0=[$6], C=[$6])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests each method that creates a scalar expression. */ |
| @Test void testProject2() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .project(builder.field("DEPTNO"), |
| builder.cast(builder.field(6), SqlTypeName.SMALLINT), |
| builder.or( |
| builder.equals(builder.field("DEPTNO"), |
| builder.literal(20)), |
| builder.and( |
| builder.cast(builder.literal(null), |
| SqlTypeName.BOOLEAN), |
| builder.equals(builder.field("DEPTNO"), |
| builder.literal(10)), |
| builder.and(builder.isNull(builder.field(6)), |
| builder.not(builder.isNotNull(builder.field(5))))), |
| builder.equals(builder.field("DEPTNO"), |
| builder.literal(20)), |
| builder.equals(builder.field("DEPTNO"), |
| builder.literal(30))), |
| builder.alias(builder.isNull(builder.field(2)), "n2"), |
| builder.alias(builder.isNotNull(builder.field(3)), "nn2"), |
| builder.literal(20), |
| builder.field(6), |
| builder.alias(builder.field(6), "C")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$7], COMM=[CAST($6):SMALLINT NOT NULL], " |
| + "$f2=[OR(SEARCH($7, Sarg[20, 30]), AND(null, =($7, 10), " |
| + "IS NULL($6), IS NULL($5)))], n2=[IS NULL($2)], " |
| + "nn2=[IS NOT NULL($3)], $f5=[20], COMM0=[$6], C=[$6])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testProjectIdentity() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.fields(Mappings.bijection(Arrays.asList(0, 1, 2)))) |
| .build(); |
| final String expected = "LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1297">[CALCITE-1297] |
| * RelBuilder does not translate identity projects even if they rename |
| * fields</a>. */ |
| @Test void testProjectIdentityWithFieldsRename() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.alias(builder.field(0), "a"), |
| builder.alias(builder.field(1), "b"), |
| builder.alias(builder.field(2), "c")) |
| .as("t1") |
| .project(builder.field("a"), |
| builder.field("t1", "c")) |
| .build(); |
| final String expected = "LogicalProject(a=[$0], c=[$2])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Variation on {@link #testProjectIdentityWithFieldsRename}: don't use a |
| * table alias, and make sure the field names propagate through a filter. */ |
| @Test void testProjectIdentityWithFieldsRenameFilter() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.alias(builder.field(0), "a"), |
| builder.alias(builder.field(1), "b"), |
| builder.alias(builder.field(2), "c")) |
| .filter( |
| builder.equals(builder.field("a"), |
| builder.literal(20))) |
| .aggregate(builder.groupKey(0, 1, 2), |
| builder.aggregateCall(SqlStdOperatorTable.SUM, |
| builder.field(0))) |
| .project(builder.field("c"), |
| builder.field("a")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(c=[$2], a=[$0])\n" |
| + " LogicalAggregate(group=[{0, 1, 2}], agg#0=[SUM($0)])\n" |
| + " LogicalFilter(condition=[=($0, 20)])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testProjectLeadingEdge() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .project(builder.fields(Mappings.bijection(Arrays.asList(0, 1, 2)))) |
| .build(); |
| final String expected = "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testProjectWithAliasFromScan() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .project(builder.field(1, "EMP", "ENAME")) |
| .build(); |
| final String expected = |
| "LogicalProject(ENAME=[$1])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3228">[CALCITE-3228] |
| * IllegalArgumentException in getMapping() for project containing same reference</a>. */ |
| @Test void testProjectMapping() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .project(builder.field(0), builder.field(0)) |
| .build(); |
| assertTrue(root instanceof Project); |
| Project project = (Project) root; |
| Mappings.TargetMapping mapping = project.getMapping(); |
| assertThat(mapping, nullValue()); |
| } |
| |
| private void project1(int value, SqlTypeName sqlTypeName, String message, String expected) { |
| final RelBuilder builder = createBuilder(c -> c.withSimplifyValues(false)); |
| RexBuilder rex = builder.getRexBuilder(); |
| RelNode actual = |
| builder.values(new String[]{"x"}, 42) |
| .empty() |
| .project( |
| rex.makeLiteral(value, |
| rex.getTypeFactory().createSqlType(sqlTypeName))) |
| .build(); |
| assertThat(message, actual, hasTree(expected)); |
| } |
| |
| @Test void testProject1asInt() { |
| project1(1, SqlTypeName.INTEGER, |
| "project(1 as INT) might omit type of 1 in the output plan as" |
| + " it is convention to omit INTEGER for integer literals", |
| "LogicalProject($f0=[1])\n" |
| + " LogicalValues(tuples=[[]])\n"); |
| } |
| |
| @Test void testProject1asBigInt() { |
| project1(1, SqlTypeName.BIGINT, "project(1 as BIGINT) should contain" |
| + " type of 1 in the output plan since the convention is to omit type of INTEGER", |
| "LogicalProject($f0=[1:BIGINT])\n" |
| + " LogicalValues(tuples=[[]])\n"); |
| } |
| |
| @Test void testProjectBloat() { |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .project( |
| b.alias( |
| caseCall(b, b.field("DEPTNO"), |
| b.literal(0), b.literal("zero"), |
| b.literal(1), b.literal("one"), |
| b.literal(2), b.literal("two"), |
| b.literal("other")), |
| "v")) |
| .project( |
| b.call(SqlStdOperatorTable.PLUS, b.field("v"), b.field("v"))) |
| .build(); |
| // Complexity of bottom is 14; top is 3; merged is 29; difference is -12. |
| // So, we merge if bloat is 20 or 100 (the default), |
| // but not if it is -1, 0 or 10. |
| final String expected = "LogicalProject($f0=[+" |
| + "(CASE(=($7, 0), 'zero', =($7, 1), 'one', =($7, 2), 'two', 'other')," |
| + " CASE(=($7, 0), 'zero', =($7, 1), 'one', =($7, 2), 'two', 'other'))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| final String expectedNeg = "LogicalProject($f0=[+($0, $0)])\n" |
| + " LogicalProject(v=[CASE(=($7, 0), 'zero', =($7, 1), " |
| + "'one', =($7, 2), 'two', 'other')])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withBloat(0))), |
| hasTree(expectedNeg)); |
| assertThat(f.apply(createBuilder(c -> c.withBloat(-1))), |
| hasTree(expectedNeg)); |
| assertThat(f.apply(createBuilder(c -> c.withBloat(10))), |
| hasTree(expectedNeg)); |
| assertThat(f.apply(createBuilder(c -> c.withBloat(20))), |
| hasTree(expected)); |
| } |
| |
| @Test void testProjectBloat2() { |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .project( |
| b.field("DEPTNO"), |
| b.field("SAL"), |
| b.alias( |
| b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"), |
| b.field("EMPNO")), "PLUS")) |
| .project( |
| b.call(SqlStdOperatorTable.MULTIPLY, b.field("SAL"), |
| b.field("PLUS")), |
| b.field("SAL")) |
| .build(); |
| // Complexity of bottom is 5; top is 4; merged is 6; difference is 3. |
| // So, we merge except when bloat is -1. |
| final String expected = "LogicalProject($f0=[*($5, +($7, $0))], SAL=[$5])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| final String expectedNeg = "LogicalProject($f0=[*($1, $2)], SAL=[$1])\n" |
| + " LogicalProject(DEPTNO=[$7], SAL=[$5], PLUS=[+($7, $0)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withBloat(0))), |
| hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withBloat(-1))), |
| hasTree(expectedNeg)); |
| assertThat(f.apply(createBuilder(c -> c.withBloat(10))), |
| hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withBloat(20))), |
| hasTree(expected)); |
| } |
| |
| private RexNode caseCall(RelBuilder b, RexNode ref, RexNode... nodes) { |
| final List<RexNode> list = new ArrayList<>(); |
| for (int i = 0; i + 1 < nodes.length; i += 2) { |
| list.add(b.equals(ref, nodes[i])); |
| list.add(nodes[i + 1]); |
| } |
| list.add(nodes.length % 2 == 1 ? nodes[nodes.length - 1] |
| : b.literal(null)); |
| return b.call(SqlStdOperatorTable.CASE, list); |
| } |
| |
| /** Creates a {@link Project} that contains a windowed aggregate function. |
| * Repeats the using {@link RelBuilder.AggCall#over} and |
| * {@link RexBuilder#makeOver}. */ |
| @Test void testProjectOver() { |
| final Function<RelBuilder, RelNode> f = b -> { |
| final RelDataType intType = |
| b.getTypeFactory().createSqlType(SqlTypeName.INTEGER); |
| return b.scan("EMP") |
| .project(b.field("DEPTNO"), |
| b.alias( |
| b.getRexBuilder().makeOver(intType, |
| SqlStdOperatorTable.ROW_NUMBER, ImmutableList.of(), |
| ImmutableList.of(), |
| ImmutableList.of( |
| new RexFieldCollation(b.field("EMPNO"), |
| ImmutableSet.of())), |
| RexWindowBounds.UNBOUNDED_PRECEDING, |
| RexWindowBounds.UNBOUNDED_FOLLOWING, |
| true, true, false, false, false), |
| "x")) |
| .build(); |
| }; |
| final Function<RelBuilder, RelNode> f2 = b -> b.scan("EMP") |
| .project(b.field("DEPTNO"), |
| b.aggregateCall(SqlStdOperatorTable.ROW_NUMBER) |
| .over() |
| .partitionBy() |
| .orderBy(b.field("EMPNO")) |
| .rowsUnbounded() |
| .allowPartial(true) |
| .nullWhenCountZero(false) |
| .as("x")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$7], x=[ROW_NUMBER() OVER (ORDER BY $0)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| assertThat(f2.apply(createBuilder()), hasTree(expected)); |
| } |
| |
| /** Tests that RelBuilder does not merge a Project that contains a windowed |
| * aggregate function into a lower Project. */ |
| @Test void testProjectOverOver() { |
| final Function<RelBuilder, RelNode> f = b -> b.scan("EMP") |
| .project(b.field("DEPTNO"), |
| b.aggregateCall(SqlStdOperatorTable.ROW_NUMBER) |
| .over() |
| .partitionBy() |
| .orderBy(b.field("EMPNO")) |
| .rowsUnbounded() |
| .as("x")) |
| .project(b.field("DEPTNO"), |
| b.aggregateCall(SqlStdOperatorTable.ROW_NUMBER) |
| .over() |
| .partitionBy() |
| .orderBy(b.field("DEPTNO")) |
| .rowsUnbounded() |
| .as("y")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$0], y=[ROW_NUMBER() OVER (ORDER BY $0)])\n" |
| + " LogicalProject(DEPTNO=[$7], x=[ROW_NUMBER() OVER (ORDER BY $0)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| } |
| |
| @Test void testRename() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| |
| // No rename necessary (null name is ignored) |
| RelNode root = |
| builder.scan("DEPT") |
| .rename(Arrays.asList("DEPTNO", null)) |
| .build(); |
| final String expected = "LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| |
| // No rename necessary (prefix matches) |
| root = |
| builder.scan("DEPT") |
| .rename(ImmutableList.of("DEPTNO")) |
| .build(); |
| assertThat(root, hasTree(expected)); |
| |
| // Add project to rename fields |
| root = |
| builder.scan("DEPT") |
| .rename(Arrays.asList("NAME", null, "DEPTNO")) |
| .build(); |
| final String expected2 = "" |
| + "LogicalProject(NAME=[$0], DNAME=[$1], DEPTNO=[$2])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected2)); |
| |
| // If our requested list has non-unique names, we might get the same field |
| // names we started with. Don't add a useless project. |
| root = |
| builder.scan("DEPT") |
| .rename(Arrays.asList("DEPTNO", null, "DEPTNO")) |
| .build(); |
| final String expected3 = "" |
| + "LogicalProject(DEPTNO=[$0], DNAME=[$1], DEPTNO0=[$2])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected3)); |
| root = |
| builder.scan("DEPT") |
| .rename(Arrays.asList("DEPTNO", null, "DEPTNO")) |
| .rename(Arrays.asList("DEPTNO", null, "DEPTNO")) |
| .build(); |
| // No extra Project |
| assertThat(root, hasTree(expected3)); |
| |
| // Name list too long |
| try { |
| root = |
| builder.scan("DEPT") |
| .rename(ImmutableList.of("NAME", "DEPTNO", "Y", "Z")) |
| .build(); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), is("More names than fields")); |
| } |
| } |
| |
| @Test void testRenameValues() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.values(new String[]{"a", "b"}, true, 1, false, -50) |
| .build(); |
| final String expected = |
| "LogicalValues(tuples=[[{ true, 1 }, { false, -50 }]])\n"; |
| assertThat(root, hasTree(expected)); |
| |
| // When you rename Values, you get a Values with a new row type, no Project |
| root = |
| builder.push(root) |
| .rename(ImmutableList.of("x", "y z")) |
| .build(); |
| assertThat(root, hasTree(expected)); |
| assertThat(root.getRowType().getFieldNames().toString(), is("[x, y z]")); |
| } |
| |
| /** Tests conditional rename using {@link RelBuilder#let}. */ |
| @Test void testLetRename() { |
| final AtomicInteger i = new AtomicInteger(); |
| final Function<RelBuilder, String> f = builder -> |
| builder.values(new String[]{"a", "b"}, 1, true) |
| .rename(Arrays.asList("p", "q")) |
| .let(r -> i.getAndIncrement() == 0 |
| ? r.rename(Arrays.asList("x", "y")) : r) |
| .let(r -> i.getAndIncrement() == 1 |
| ? r.project(r.field(1), r.field(0)) : r) |
| .let(r -> i.getAndIncrement() == 0 |
| ? r.rename(Arrays.asList("c", "d")) : r) |
| .let(r -> r.build().getRowType().toString()); |
| final String expected = "RecordType(BOOLEAN y, INTEGER x)"; |
| assertThat(f.apply(createBuilder()), is(expected)); |
| assertThat(i.get(), is(3)); |
| } |
| |
| @Test void testPermute() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .permute(Mappings.bijection(Arrays.asList(1, 2, 0))) |
| .build(); |
| final String expected = "LogicalProject(JOB=[$2], EMPNO=[$0], ENAME=[$1])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testConvert() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelDataType rowType = |
| builder.getTypeFactory().builder() |
| .add("a", SqlTypeName.BIGINT) |
| .add("b", SqlTypeName.VARCHAR, 10) |
| .add("c", SqlTypeName.VARCHAR, 10) |
| .build(); |
| RelNode root = |
| builder.scan("DEPT") |
| .convert(rowType, false) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[CAST($0):BIGINT NOT NULL], DNAME=[CAST($1):VARCHAR(10) NOT NULL], LOC=[CAST($2):VARCHAR(10) NOT NULL])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testConvertRename() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelDataType rowType = |
| builder.getTypeFactory().builder() |
| .add("a", SqlTypeName.BIGINT) |
| .add("b", SqlTypeName.VARCHAR, 10) |
| .add("c", SqlTypeName.VARCHAR, 10) |
| .build(); |
| RelNode root = |
| builder.scan("DEPT") |
| .convert(rowType, true) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(a=[CAST($0):BIGINT NOT NULL], b=[CAST($1):VARCHAR(10) NOT NULL], c=[CAST($2):VARCHAR(10) NOT NULL])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-4429">[CALCITE-4429] |
| * RelOptUtil#createCastRel should throw an exception when the desired row type |
| * and the row type to be converted don't have the same number of fields</a>. */ |
| @Test void testConvertNegative() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelDataType rowType = |
| builder.getTypeFactory().builder() |
| .add("a", SqlTypeName.BIGINT) |
| .add("b", SqlTypeName.VARCHAR, 10) |
| .build(); |
| IllegalArgumentException ex = assertThrows(IllegalArgumentException.class, () -> { |
| builder.scan("DEPT") |
| .convert(rowType, false) |
| .build(); |
| }, "Convert should fail since the field counts are not equal."); |
| assertThat(ex.getMessage(), containsString("Field counts are not equal")); |
| } |
| |
| @Test void testAggregate() { |
| // Equivalent SQL: |
| // SELECT COUNT(DISTINCT deptno) AS c |
| // FROM emp |
| // GROUP BY () |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate(builder.groupKey(), |
| builder.count(true, "C", builder.field("DEPTNO"))) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{}], C=[COUNT(DISTINCT $7)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAggregate2() { |
| // Equivalent SQL: |
| // SELECT COUNT(*) AS c, SUM(mgr + 1) AS s |
| // FROM emp |
| // GROUP BY ename, hiredate + mgr |
| final Function<RelBuilder, RelNode> f = builder -> |
| builder.scan("EMP") |
| .aggregate( |
| builder.groupKey(builder.field(1), |
| builder.call(SqlStdOperatorTable.PLUS, |
| builder.field(4), |
| builder.field(3)), |
| builder.field(1)), |
| builder.countStar("C"), |
| builder.sum( |
| builder.call(SqlStdOperatorTable.PLUS, builder.field(3), |
| builder.literal(1))).as("S")) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{0, 1}], C=[COUNT()], S=[SUM($2)])\n" |
| + " LogicalProject(ENAME=[$1], $f8=[+($4, $3)], $f9=[+($3, 1)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| |
| // now without pruning |
| final String expected2 = "" |
| + "LogicalAggregate(group=[{1, 8}], C=[COUNT()], S=[SUM($9)])\n" |
| + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " |
| + "HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[+($4, $3)], " |
| + "$f9=[+($3, 1)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder(c -> c.withPruneInputOfAggregate(false))), |
| hasTree(expected2)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-2192">[CALCITE-2192] |
| * RelBuilder wrongly skips creation of Aggregate that prunes columns if input |
| * is unique</a>. */ |
| @Test void testAggregate3() { |
| // Equivalent SQL: |
| // SELECT DISTINCT deptno FROM ( |
| // SELECT deptno, COUNT(*) |
| // FROM emp |
| // GROUP BY deptno) |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate(builder.groupKey(builder.field(1)), |
| builder.count().as("C")) |
| .aggregate(builder.groupKey(builder.field(0))) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(ENAME=[$0])\n" |
| + " LogicalAggregate(group=[{1}], C=[COUNT()])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** As {@link #testAggregate3()} but with Filter. */ |
| @Test void testAggregate4() { |
| // Equivalent SQL: |
| // SELECT DISTINCT deptno FROM ( |
| // SELECT deptno, COUNT(*) |
| // FROM emp |
| // GROUP BY deptno |
| // HAVING COUNT(*) > 3) |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate(builder.groupKey(builder.field(1)), |
| builder.count().as("C")) |
| .filter( |
| builder.greaterThan(builder.field(1), builder.literal(3))) |
| .aggregate(builder.groupKey(builder.field(0))) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(ENAME=[$0])\n" |
| + " LogicalFilter(condition=[>($1, 3)])\n" |
| + " LogicalAggregate(group=[{1}], C=[COUNT()])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-2946">[CALCITE-2946] |
| * RelBuilder wrongly skips creation of Aggregate that prunes columns if input |
| * produces one row at most</a>. */ |
| @Test void testAggregate5() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate(builder.groupKey(), builder.count().as("C")) |
| .project(builder.literal(4), builder.literal(2), builder.field(0)) |
| .aggregate(builder.groupKey(builder.field(0), builder.field(1))) |
| .build(); |
| final String expected = "" |
| + "LogicalProject($f0=[4], $f1=[2])\n" |
| + " LogicalAggregate(group=[{}], C=[COUNT()])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3839">[CALCITE-3839] |
| * After calling RelBuilder.aggregate, cannot lookup field by name</a>. */ |
| @Test void testAggregateAndThenProjectNamedField() { |
| final Function<RelBuilder, RelNode> f = builder -> |
| builder.scan("EMP") |
| .project(builder.field("EMPNO"), builder.field("ENAME"), |
| builder.field("SAL")) |
| .aggregate(builder.groupKey(builder.field("ENAME")), |
| builder.sum(builder.field("SAL"))) |
| // Before [CALCITE-3839] was fixed, the following line gave |
| // 'field [ENAME] not found' |
| .project(builder.field("ENAME")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(ENAME=[$0])\n" |
| + " LogicalAggregate(group=[{0}], agg#0=[SUM($1)])\n" |
| + " LogicalProject(ENAME=[$1], SAL=[$5])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| } |
| |
| /** Tests that {@link RelBuilder#aggregate} eliminates duplicate aggregate |
| * calls and creates a {@code Project} to compensate. */ |
| @Test void testAggregateEliminatesDuplicateCalls() { |
| final String expected = "" |
| + "LogicalProject(S1=[$0], C=[$1], S2=[$2], S1b=[$0])\n" |
| + " LogicalAggregate(group=[{}], S1=[SUM($1)], C=[COUNT()], S2=[SUM($2)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat( |
| buildRelWithDuplicateAggregates(c -> c.withDedupAggregateCalls(true)), |
| hasTree(expected)); |
| |
| // Now, disable the rewrite |
| final String expected2 = "" |
| + "LogicalAggregate(group=[{}], S1=[SUM($1)], C=[COUNT()], S2=[SUM($2)], S1b=[SUM($1)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat( |
| buildRelWithDuplicateAggregates(c -> c.withDedupAggregateCalls(false)), |
| hasTree(expected2)); |
| } |
| |
| /** As {@link #testAggregateEliminatesDuplicateCalls()} but with a |
| * single-column GROUP BY clause. */ |
| @Test void testAggregateEliminatesDuplicateCalls2() { |
| RelNode root = buildRelWithDuplicateAggregates(c -> c, 0); |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0], S1=[$1], C=[$2], S2=[$3], S1b=[$1])\n" |
| + " LogicalAggregate(group=[{0}], S1=[SUM($1)], C=[COUNT()], S2=[SUM($2)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** As {@link #testAggregateEliminatesDuplicateCalls()} but with a |
| * multi-column GROUP BY clause. */ |
| @Test void testAggregateEliminatesDuplicateCalls3() { |
| RelNode root = buildRelWithDuplicateAggregates(c -> c, 2, 0, 4, 3); |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0], JOB=[$1], MGR=[$2], HIREDATE=[$3], S1=[$4], C=[$5], S2=[$6], S1b=[$4])\n" |
| + " LogicalAggregate(group=[{0, 2, 3, 4}], S1=[SUM($1)], C=[COUNT()], S2=[SUM($2)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| private RelNode buildRelWithDuplicateAggregates( |
| UnaryOperator<RelBuilder.Config> transform, |
| int... groupFieldOrdinals) { |
| final RelBuilder builder = createBuilder(transform); |
| return builder.scan("EMP") |
| .aggregate(builder.groupKey(groupFieldOrdinals), |
| builder.sum(builder.field(1)).as("S1"), |
| builder.count().as("C"), |
| builder.sum(builder.field(2)).as("S2"), |
| builder.sum(builder.field(1)).as("S1b")) |
| .build(); |
| } |
| |
| /** Tests eliminating duplicate aggregate calls, when some of them are only |
| * seen to be duplicates when a spurious "DISTINCT" has been eliminated. |
| * |
| * <p>Note that "M2" and "MD2" are based on the same field, because |
| * "MIN(DISTINCT $2)" is identical to "MIN($2)". The same is not true for |
| * "SUM". */ |
| @Test void testAggregateEliminatesDuplicateDistinctCalls() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = builder.scan("EMP") |
| .aggregate(builder.groupKey(2), |
| builder.sum(builder.field(1)).as("S1"), |
| builder.sum(builder.field(1)).distinct().as("SD1"), |
| builder.count().as("C"), |
| builder.min(builder.field(2)).distinct().as("MD2"), |
| builder.min(builder.field(2)).as("M2"), |
| builder.min(builder.field(2)).distinct().as("MD2b"), |
| builder.sum(builder.field(1)).distinct().as("S1b")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(JOB=[$0], S1=[$1], SD1=[$2], C=[$3], MD2=[$4], " |
| + "M2=[$4], MD2b=[$4], S1b=[$2])\n" |
| + " LogicalAggregate(group=[{2}], S1=[SUM($1)], " |
| + "SD1=[SUM(DISTINCT $1)], C=[COUNT()], MD2=[MIN($2)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAggregateFilter() { |
| // Equivalent SQL: |
| // SELECT deptno, COUNT(*) FILTER (WHERE empno > 100) AS c |
| // FROM emp |
| // GROUP BY ROLLUP(deptno) |
| final Function<RelBuilder, RelNode> f = builder -> |
| builder.scan("EMP") |
| .aggregate( |
| builder.groupKey(ImmutableBitSet.of(7), |
| (Iterable<ImmutableBitSet>) |
| ImmutableList.of(ImmutableBitSet.of(7), |
| ImmutableBitSet.of())), |
| builder.count() |
| .filter( |
| builder.greaterThan(builder.field("EMPNO"), |
| builder.literal(100))) |
| .as("C")) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{0}], groups=[[{0}, {}]], C=[COUNT() FILTER $1])\n" |
| + " LogicalProject(DEPTNO=[$7], $f8=[>($0, 100)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| |
| // now without pruning |
| final String expected2 = "" |
| + "LogicalAggregate(group=[{7}], groups=[[{7}, {}]], C=[COUNT() FILTER $8])\n" |
| + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " |
| + "HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[>($0, 100)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder(c -> c.withPruneInputOfAggregate(false))), |
| hasTree(expected2)); |
| } |
| |
| @Test void testAggregateFilterFails() { |
| // Equivalent SQL: |
| // SELECT deptno, SUM(sal) FILTER (WHERE comm) AS c |
| // FROM emp |
| // GROUP BY deptno |
| try { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate( |
| builder.groupKey(builder.field("DEPTNO")), |
| builder.sum(builder.field("SAL")) |
| .filter(builder.field("COMM")) |
| .as("C")) |
| .build(); |
| fail("expected error, got " + root); |
| } catch (CalciteException e) { |
| assertThat(e.getMessage(), |
| is("FILTER expression must be of type BOOLEAN")); |
| } |
| } |
| |
| @Test void testAggregateFilterNullable() { |
| // Equivalent SQL: |
| // SELECT deptno, SUM(sal) FILTER (WHERE comm < 100) AS c |
| // FROM emp |
| // GROUP BY deptno |
| final Function<RelBuilder, RelNode> f = builder -> |
| builder.scan("EMP") |
| .aggregate( |
| builder.groupKey(builder.field("DEPTNO")), |
| builder.sum(builder.field("SAL")) |
| .filter( |
| builder.lessThan(builder.field("COMM"), |
| builder.literal(100))) |
| .as("C")) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{1}], C=[SUM($0) FILTER $2])\n" |
| + " LogicalProject(SAL=[$5], DEPTNO=[$7], $f8=[IS TRUE(<($6, 100))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| |
| // now without pruning |
| final String expected2 = "" |
| + "LogicalAggregate(group=[{7}], C=[SUM($5) FILTER $8])\n" |
| + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[IS TRUE(<($6, 100))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder(c -> c.withPruneInputOfAggregate(false))), |
| hasTree(expected2)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1980">[CALCITE-1980] |
| * RelBuilder gives NPE if groupKey contains alias</a>. |
| * |
| * <p>Now, the alias does not cause a new expression to be added to the input, |
| * but causes the referenced fields to be renamed. */ |
| @Test void testAggregateProjectWithAliases() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .project(builder.field("DEPTNO")) |
| .aggregate( |
| builder.groupKey( |
| builder.alias(builder.field("DEPTNO"), "departmentNo"))) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{0}])\n" |
| + " LogicalProject(departmentNo=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAggregateProjectWithExpression() { |
| final Function<RelBuilder, RelNode> f = builder -> |
| builder.scan("EMP") |
| .project(builder.field("DEPTNO")) |
| .aggregate( |
| builder.groupKey( |
| builder.alias( |
| builder.call(SqlStdOperatorTable.PLUS, |
| builder.field("DEPTNO"), builder.literal(3)), |
| "d3"))) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{0}])\n" |
| + " LogicalProject(d3=[+($7, 3)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| |
| // now without pruning |
| final String expected2 = "" |
| + "LogicalAggregate(group=[{1}])\n" |
| + " LogicalProject(DEPTNO=[$7], d3=[+($7, 3)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder(c -> c.withPruneInputOfAggregate(false))), |
| hasTree(expected2)); |
| } |
| |
| /** Tests that {@link RelBuilder#aggregate} on top of a {@link Project} prunes |
| * away expressions that are not used. |
| * |
| * @see RelBuilder.Config#pruneInputOfAggregate */ |
| @Test void testAggregateProjectPrune() { |
| // SELECT deptno, SUM(sal) FILTER (WHERE b) |
| // FROM ( |
| // SELECT deptno, empno + 10, sal, job = 'CLERK' AS b |
| // FROM emp) |
| // GROUP BY deptno |
| // --> |
| // SELECT deptno, SUM(sal) FILTER (WHERE b) |
| // FROM ( |
| // SELECT deptno, sal, job = 'CLERK' AS b |
| // FROM emp) |
| // GROUP BY deptno |
| final Function<RelBuilder, RelNode> f = builder -> |
| builder.scan("EMP") |
| .project(builder.field("DEPTNO"), |
| builder.call(SqlStdOperatorTable.PLUS, |
| builder.field("EMPNO"), builder.literal(10)), |
| builder.field("SAL"), |
| builder.field("JOB")) |
| .aggregate( |
| builder.groupKey(builder.field("DEPTNO")), |
| builder.sum(builder.field("SAL")) |
| .filter( |
| builder.equals(builder.field("JOB"), |
| builder.literal("CLERK")))) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{0}], agg#0=[SUM($1) FILTER $2])\n" |
| + " LogicalProject(DEPTNO=[$7], SAL=[$5], $f4=[IS TRUE(=($2, 'CLERK'))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), |
| hasTree(expected)); |
| |
| // now with pruning disabled |
| final String expected2 = "" |
| + "LogicalAggregate(group=[{0}], agg#0=[SUM($2) FILTER $4])\n" |
| + " LogicalProject(DEPTNO=[$7], $f1=[+($0, 10)], SAL=[$5], JOB=[$2], " |
| + "$f4=[IS TRUE(=($2, 'CLERK'))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder(c -> c.withPruneInputOfAggregate(false))), |
| hasTree(expected2)); |
| } |
| |
| /** Tests that (a) if the input is a project and no fields are used |
| * we remove the project (rather than projecting zero fields, which |
| * would be wrong), and (b) if the same aggregate function is used |
| * twice, we add a project on top. */ |
| @Test void testAggregateProjectPruneEmpty() { |
| // SELECT COUNT(*) AS C, COUNT(*) AS C2 FROM ( |
| // SELECT deptno, empno + 10, sal, job = 'CLERK' AS b |
| // FROM emp) |
| // --> |
| // SELECT C, C AS C2 FROM ( |
| // SELECT COUNT(*) AS c |
| // FROM emp) |
| final Function<RelBuilder, RelNode> f = builder -> |
| builder.scan("EMP") |
| .project(builder.field("DEPTNO"), |
| builder.call(SqlStdOperatorTable.PLUS, |
| builder.field("EMPNO"), builder.literal(10)), |
| builder.field("SAL"), |
| builder.field("JOB")) |
| .aggregate( |
| builder.groupKey(), |
| builder.countStar("C"), |
| builder.countStar("C2")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(C=[$0], C2=[$0])\n" |
| + " LogicalAggregate(group=[{}], C=[COUNT()])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| |
| // now with pruning disabled |
| final String expected2 = "" |
| + "LogicalProject(C=[$0], C2=[$0])\n" |
| + " LogicalAggregate(group=[{}], C=[COUNT()])\n" |
| + " LogicalProject(DEPTNO=[$7], $f1=[+($0, 10)], SAL=[$5], JOB=[$2])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder(c -> c.withPruneInputOfAggregate(false))), |
| hasTree(expected2)); |
| } |
| |
| @Test void testAggregateGroupingKeyOutOfRangeFails() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| try { |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate(builder.groupKey(ImmutableBitSet.of(17))) |
| .build(); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), is("out of bounds: {17}")); |
| } |
| } |
| |
| @Test void testAggregateGroupingSetNotSubsetFails() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| try { |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate( |
| builder.groupKey(ImmutableBitSet.of(7), |
| (Iterable<ImmutableBitSet>) |
| ImmutableList.of(ImmutableBitSet.of(4), |
| ImmutableBitSet.of()))) |
| .build(); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| is("group set element [$4] must be a subset of group key")); |
| } |
| } |
| |
| /** Tests that, if you try to create an Aggregate with duplicate grouping |
| * sets, RelBuilder creates a Union. Each branch of the Union has an |
| * Aggregate that has distinct grouping sets. */ |
| @Test void testAggregateGroupingSetDuplicate() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate( |
| builder.groupKey(ImmutableBitSet.of(7, 6), |
| (Iterable<ImmutableBitSet>) |
| ImmutableList.of(ImmutableBitSet.of(7), |
| ImmutableBitSet.of(6), |
| ImmutableBitSet.of(7)))) |
| .build(); |
| final String expected = "" |
| + "LogicalUnion(all=[true])\n" |
| + " LogicalAggregate(group=[{6, 7}], groups=[[{6}, {7}]])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalAggregate(group=[{6, 7}], groups=[[{7}]])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-4665">[CALCITE-4665] |
| * Allow Aggregate.groupSet to contain columns not in any of the groupSets.</a>. */ |
| @Test void testGroupingSetWithGroupKeysContainingUnusedColumn() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = builder.scan("EMP") |
| .aggregate( |
| builder.groupKey( |
| ImmutableBitSet.of(0, 1, 2), |
| (Iterable<ImmutableBitSet>) |
| ImmutableList.of(ImmutableBitSet.of(0, 1), ImmutableBitSet.of(0))), |
| builder.count(false, "C"), |
| builder.sum(false, "S", builder.field("SAL"))) |
| .filter( |
| builder.call( |
| SqlStdOperatorTable.GREATER_THAN, |
| builder.field("C"), |
| builder.literal(10))) |
| .filter( |
| builder.call( |
| SqlStdOperatorTable.EQUALS, |
| builder.field("JOB"), |
| builder.literal("DEVELOP"))) |
| .project(builder.field("JOB")).build(); |
| final String expected = "" |
| + "LogicalProject(JOB=[$2])\n" |
| + " LogicalFilter(condition=[=($2, 'DEVELOP')])\n" |
| + " LogicalFilter(condition=[>($3, 10)])\n" |
| + " LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}, {0}]], C=[COUNT()], S=[SUM" |
| + "($5)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAggregateGrouping() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate(builder.groupKey(6, 7), |
| builder.aggregateCall(SqlStdOperatorTable.GROUPING, |
| builder.field("DEPTNO")).as("g")) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{6, 7}], g=[GROUPING($7)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAggregateGroupingWithDistinctFails() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| try { |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate(builder.groupKey(6, 7), |
| builder.aggregateCall(SqlStdOperatorTable.GROUPING, |
| builder.field("DEPTNO")) |
| .distinct(true) |
| .as("g")) |
| .build(); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), is("DISTINCT not allowed")); |
| } |
| } |
| |
| @Test void testAggregateGroupingWithFilterFails() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| try { |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate(builder.groupKey(6, 7), |
| builder.aggregateCall(SqlStdOperatorTable.GROUPING, |
| builder.field("DEPTNO")) |
| .filter(builder.literal(true)) |
| .as("g")) |
| .build(); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), is("FILTER not allowed")); |
| } |
| } |
| |
| @Test void testAggregateOneRow() { |
| final Function<RelBuilder, RelNode> f = builder -> |
| builder.values(new String[] {"a", "b"}, 1, 2) |
| .aggregate(builder.groupKey(1)) |
| .build(); |
| final String plan = "LogicalProject(b=[$1])\n" |
| + " LogicalValues(tuples=[[{ 1, 2 }]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(plan)); |
| |
| final String plan2 = "LogicalAggregate(group=[{1}])\n" |
| + " LogicalValues(tuples=[[{ 1, 2 }]])\n"; |
| assertThat(f.apply(createBuilder(c -> c.withAggregateUnique(true))), |
| hasTree(plan2)); |
| } |
| |
| /** Tests that we do not convert an Aggregate to a Project if there are |
| * multiple group sets. */ |
| @Test void testAggregateGroupingSetsOneRow() { |
| final Function<RelBuilder, RelNode> f = builder -> { |
| final List<Integer> list01 = Arrays.asList(0, 1); |
| final List<Integer> list0 = Collections.singletonList(0); |
| final List<Integer> list1 = Collections.singletonList(1); |
| return builder.values(new String[] {"a", "b"}, 1, 2) |
| .aggregate( |
| builder.groupKey(builder.fields(list01), |
| ImmutableList.of(builder.fields(list0), |
| builder.fields(list1), |
| builder.fields(list01)))) |
| .build(); |
| }; |
| final String plan = "" |
| + "LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}]])\n" |
| + " LogicalValues(tuples=[[{ 1, 2 }]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(plan)); |
| assertThat(f.apply(createBuilder(c -> c.withAggregateUnique(true))), |
| hasTree(plan)); |
| } |
| |
| /** Tests creating (and expanding) a call to {@code GROUP_ID()} in a |
| * {@code GROUPING SETS} query. Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-4199">[CALCITE-4199] |
| * RelBuilder throws NullPointerException while implementing |
| * GROUP_ID()</a>. */ |
| @Test void testAggregateGroupingSetsGroupId() { |
| final String plan = "" |
| + "LogicalProject(JOB=[$0], DEPTNO=[$1], $f2=[0:BIGINT])\n" |
| + " LogicalAggregate(group=[{2, 7}], groups=[[{2, 7}, {2}, {7}]])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(groupIdRel(createBuilder(), false), hasTree(plan)); |
| assertThat( |
| groupIdRel(createBuilder(c -> c.withAggregateUnique(true)), false), |
| hasTree(plan)); |
| |
| // If any group occurs more than once, we need a UNION ALL. |
| final String plan2 = "" |
| + "LogicalUnion(all=[true])\n" |
| + " LogicalProject(JOB=[$0], DEPTNO=[$1], $f2=[0:BIGINT])\n" |
| + " LogicalAggregate(group=[{2, 7}], groups=[[{2, 7}, {2}, {7}]])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalProject(JOB=[$0], DEPTNO=[$1], $f2=[1:BIGINT])\n" |
| + " LogicalAggregate(group=[{2, 7}])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(groupIdRel(createBuilder(), true), hasTree(plan2)); |
| } |
| |
| private static RelNode groupIdRel(RelBuilder builder, boolean extra) { |
| final List<String> djList = Arrays.asList("DEPTNO", "JOB"); |
| final List<String> dList = Collections.singletonList("DEPTNO"); |
| final List<String> jList = Collections.singletonList("JOB"); |
| return builder.scan("EMP") |
| .aggregate( |
| builder.groupKey(builder.fields(djList), |
| ImmutableList.<List<? extends RexNode>>builder() |
| .add(builder.fields(dList)) |
| .add(builder.fields(jList)) |
| .add(builder.fields(djList)) |
| .addAll(extra ? ImmutableList.of(builder.fields(djList)) |
| : ImmutableList.of()) |
| .build()), |
| builder.aggregateCall(SqlStdOperatorTable.GROUP_ID)) |
| .build(); |
| } |
| |
| @Test void testWithinDistinct() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .aggregate(builder.groupKey(), |
| builder.avg(builder.field("SAL")) |
| .as("g"), |
| builder.avg(builder.field("SAL")) |
| .unique(builder.field("DEPTNO")) |
| .as("g2")) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{}], g=[AVG($5)]," |
| + " g2=[AVG($5) WITHIN DISTINCT ($7)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testDistinct() { |
| // Equivalent SQL: |
| // SELECT DISTINCT deptno |
| // FROM emp |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .project(builder.field("DEPTNO")) |
| .distinct() |
| .build(); |
| final String expected = "LogicalAggregate(group=[{0}])\n" |
| + " LogicalProject(DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testDistinctAlready() { |
| // DEPT is already distinct |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .distinct() |
| .build(); |
| final String expected = "LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testDistinctEmpty() { |
| // Is a relation with zero columns distinct? |
| // What about if we know there are zero rows? |
| // It is a matter of definition: there are no duplicate rows, |
| // but applying "select ... group by ()" to it would change the result. |
| // In theory, we could omit the distinct if we know there is precisely one |
| // row, but we don't currently. |
| final Function<RelBuilder, RelNode> f = builder -> |
| builder.scan("EMP") |
| .filter( |
| builder.call(SqlStdOperatorTable.IS_NULL, |
| builder.field("COMM"))) |
| .project() |
| .distinct() |
| .build(); |
| final String expected = "LogicalAggregate(group=[{}])\n" |
| + " LogicalFilter(condition=[IS NULL($6)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| |
| // now without pruning |
| // (The empty LogicalProject is dubious, but it's what we've always done) |
| final String expected2 = "LogicalAggregate(group=[{}])\n" |
| + " LogicalProject\n" |
| + " LogicalFilter(condition=[IS NULL($6)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder(c -> c.withPruneInputOfAggregate(false))), |
| hasTree(expected2)); |
| } |
| |
| @Test void testUnion() { |
| // Equivalent SQL: |
| // SELECT deptno FROM emp |
| // UNION ALL |
| // SELECT deptno FROM dept |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.field("DEPTNO")) |
| .scan("EMP") |
| .filter( |
| builder.equals(builder.field("DEPTNO"), |
| builder.literal(20))) |
| .project(builder.field("EMPNO")) |
| .union(true) |
| .build(); |
| final String expected = "" |
| + "LogicalUnion(all=[true])\n" |
| + " LogicalProject(DEPTNO=[$0])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n" |
| + " LogicalProject(EMPNO=[$0])\n" |
| + " LogicalFilter(condition=[=($7, 20)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1522">[CALCITE-1522] |
| * Fix error message for SetOp with incompatible args</a>. */ |
| @Test void testBadUnionArgsErrorMessage() { |
| // Equivalent SQL: |
| // SELECT EMPNO, SAL FROM emp |
| // UNION ALL |
| // SELECT DEPTNO FROM dept |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| try { |
| final RelNode root = |
| builder.scan("DEPT") |
| .project(builder.field("DEPTNO")) |
| .scan("EMP") |
| .project(builder.field("EMPNO"), builder.field("SAL")) |
| .union(true) |
| .build(); |
| fail("Expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| final String expected = "Cannot compute compatible row type for " |
| + "arguments to set op: RecordType(TINYINT DEPTNO), " |
| + "RecordType(SMALLINT EMPNO, DECIMAL(7, 2) SAL)"; |
| assertThat(e.getMessage(), is(expected)); |
| } |
| } |
| |
| @Test void testUnion3() { |
| // Equivalent SQL: |
| // SELECT deptno FROM dept |
| // UNION ALL |
| // SELECT empno FROM emp |
| // UNION ALL |
| // SELECT deptno FROM emp |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.field("DEPTNO")) |
| .scan("EMP") |
| .project(builder.field("EMPNO")) |
| .scan("EMP") |
| .project(builder.field("DEPTNO")) |
| .union(true, 3) |
| .build(); |
| final String expected = "" |
| + "LogicalUnion(all=[true])\n" |
| + " LogicalProject(DEPTNO=[$0])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n" |
| + " LogicalProject(EMPNO=[$0])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalProject(DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testUnion1() { |
| // Equivalent SQL: |
| // SELECT deptno FROM dept |
| // UNION ALL |
| // SELECT empno FROM emp |
| // UNION ALL |
| // SELECT deptno FROM emp |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.field("DEPTNO")) |
| .scan("EMP") |
| .project(builder.field("EMPNO")) |
| .scan("EMP") |
| .project(builder.field("DEPTNO")) |
| .union(true, 1) |
| .build(); |
| final String expected = "LogicalProject(DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testRepeatUnion1() { |
| // Generates the sequence 1,2,3,...10 using a repeat union. Equivalent SQL: |
| // WITH RECURSIVE delta(n) AS ( |
| // VALUES (1) |
| // UNION ALL |
| // SELECT n+1 FROM delta WHERE n < 10 |
| // ) |
| // SELECT * FROM delta |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.values(new String[] { "i" }, 1) |
| .transientScan("DELTA_TABLE") |
| .filter( |
| builder.call( |
| SqlStdOperatorTable.LESS_THAN, |
| builder.field(0), |
| builder.literal(10))) |
| .project( |
| builder.call(SqlStdOperatorTable.PLUS, |
| builder.field(0), |
| builder.literal(1))) |
| .repeatUnion("DELTA_TABLE", true) |
| .build(); |
| final String expected = "LogicalRepeatUnion(all=[true])\n" |
| + " LogicalTableSpool(readType=[LAZY], writeType=[LAZY], table=[[DELTA_TABLE]])\n" |
| + " LogicalValues(tuples=[[{ 1 }]])\n" |
| + " LogicalTableSpool(readType=[LAZY], writeType=[LAZY], table=[[DELTA_TABLE]])\n" |
| + " LogicalProject($f0=[+($0, 1)])\n" |
| + " LogicalFilter(condition=[<($0, 10)])\n" |
| + " LogicalTableScan(table=[[DELTA_TABLE]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testRepeatUnion2() { |
| // Generates the factorial function from 0 to 7. Equivalent SQL: |
| // WITH RECURSIVE delta (n, fact) AS ( |
| // VALUES (0, 1) |
| // UNION ALL |
| // SELECT n+1, (n+1)*fact FROM delta WHERE n < 7 |
| // ) |
| // SELECT * FROM delta |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.values(new String[] { "n", "fact" }, 0, 1) |
| .transientScan("AUX") |
| .filter( |
| builder.call( |
| SqlStdOperatorTable.LESS_THAN, |
| builder.field("n"), |
| builder.literal(7))) |
| .project( |
| Arrays.asList( |
| builder.call(SqlStdOperatorTable.PLUS, |
| builder.field("n"), |
| builder.literal(1)), |
| builder.call(SqlStdOperatorTable.MULTIPLY, |
| builder.call(SqlStdOperatorTable.PLUS, |
| builder.field("n"), |
| builder.literal(1)), |
| builder.field("fact"))), |
| Arrays.asList("n", "fact")) |
| .repeatUnion("AUX", true) |
| .build(); |
| final String expected = "LogicalRepeatUnion(all=[true])\n" |
| + " LogicalTableSpool(readType=[LAZY], writeType=[LAZY], table=[[AUX]])\n" |
| + " LogicalValues(tuples=[[{ 0, 1 }]])\n" |
| + " LogicalTableSpool(readType=[LAZY], writeType=[LAZY], table=[[AUX]])\n" |
| + " LogicalProject(n=[+($0, 1)], fact=[*(+($0, 1), $1)])\n" |
| + " LogicalFilter(condition=[<($0, 7)])\n" |
| + " LogicalTableScan(table=[[AUX]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testIntersect() { |
| // Equivalent SQL: |
| // SELECT empno FROM emp |
| // WHERE deptno = 20 |
| // INTERSECT |
| // SELECT deptno FROM dept |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.field("DEPTNO")) |
| .scan("EMP") |
| .filter( |
| builder.equals(builder.field("DEPTNO"), |
| builder.literal(20))) |
| .project(builder.field("EMPNO")) |
| .intersect(false) |
| .build(); |
| final String expected = "" |
| + "LogicalIntersect(all=[false])\n" |
| + " LogicalProject(DEPTNO=[$0])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n" |
| + " LogicalProject(EMPNO=[$0])\n" |
| + " LogicalFilter(condition=[=($7, 20)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testIntersect3() { |
| // Equivalent SQL: |
| // SELECT deptno FROM dept |
| // INTERSECT ALL |
| // SELECT empno FROM emp |
| // INTERSECT ALL |
| // SELECT deptno FROM emp |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.field("DEPTNO")) |
| .scan("EMP") |
| .project(builder.field("EMPNO")) |
| .scan("EMP") |
| .project(builder.field("DEPTNO")) |
| .intersect(true, 3) |
| .build(); |
| final String expected = "" |
| + "LogicalIntersect(all=[true])\n" |
| + " LogicalProject(DEPTNO=[$0])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n" |
| + " LogicalProject(EMPNO=[$0])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalProject(DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testExcept() { |
| // Equivalent SQL: |
| // SELECT empno FROM emp |
| // WHERE deptno = 20 |
| // MINUS |
| // SELECT deptno FROM dept |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.field("DEPTNO")) |
| .scan("EMP") |
| .filter( |
| builder.equals(builder.field("DEPTNO"), |
| builder.literal(20))) |
| .project(builder.field("EMPNO")) |
| .minus(false) |
| .build(); |
| final String expected = "" |
| + "LogicalMinus(all=[false])\n" |
| + " LogicalProject(DEPTNO=[$0])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n" |
| + " LogicalProject(EMPNO=[$0])\n" |
| + " LogicalFilter(condition=[=($7, 20)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testJoin() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM (SELECT * FROM emp WHERE comm IS NULL) |
| // JOIN dept ON emp.deptno = dept.deptno |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.call(SqlStdOperatorTable.IS_NULL, |
| builder.field("COMM"))) |
| .scan("DEPT") |
| .join(JoinRelType.INNER, |
| builder.equals(builder.field(2, 0, "DEPTNO"), |
| builder.field(2, 1, "DEPTNO"))) |
| .build(); |
| final String expected = "" |
| + "LogicalJoin(condition=[=($7, $8)], joinType=[inner])\n" |
| + " LogicalFilter(condition=[IS NULL($6)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Same as {@link #testJoin} using USING. */ |
| @Test void testJoinUsing() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root2 = |
| builder.scan("EMP") |
| .filter( |
| builder.call(SqlStdOperatorTable.IS_NULL, |
| builder.field("COMM"))) |
| .scan("DEPT") |
| .join(JoinRelType.INNER, "DEPTNO") |
| .build(); |
| final String expected = "" |
| + "LogicalJoin(condition=[=($7, $8)], joinType=[inner])\n" |
| + " LogicalFilter(condition=[IS NULL($6)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root2, hasTree(expected)); |
| } |
| |
| @Test void testJoin2() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // LEFT JOIN dept ON emp.deptno = dept.deptno |
| // AND emp.empno = 123 |
| // AND dept.deptno IS NOT NULL |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .scan("DEPT") |
| .join(JoinRelType.LEFT, |
| builder.equals(builder.field(2, 0, "DEPTNO"), |
| builder.field(2, 1, "DEPTNO")), |
| builder.equals(builder.field(2, 0, "EMPNO"), |
| builder.literal(123)), |
| builder.isNotNull(builder.field(2, 1, "DEPTNO"))) |
| .build(); |
| // Note that "dept.deptno IS NOT NULL" has been simplified away. |
| final String expected = "" |
| + "LogicalJoin(condition=[AND(=($7, $8), =($0, 123))], joinType=[left])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests that simplification is run in |
| * {@link org.apache.calcite.rex.RexUnknownAs#FALSE} mode for join |
| * conditions. */ |
| @Test void testJoinConditionSimplification() { |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .scan("DEPT") |
| .join(JoinRelType.INNER, |
| b.or(b.literal(null), |
| b.and(b.equals(b.field(2, 0, "DEPTNO"), b.literal(1)), |
| b.equals(b.field(2, 0, "DEPTNO"), b.literal(2)), |
| b.equals(b.field(2, 1, "DEPTNO"), |
| b.field(2, 0, "DEPTNO"))))) |
| .build(); |
| final String expected = "" |
| + "LogicalJoin(condition=[false], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| final String expectedWithoutSimplify = "" |
| + "LogicalJoin(condition=[OR(null:NULL, " |
| + "AND(=($7, 1), =($7, 2), =($8, $7)))], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withSimplify(true))), |
| hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withSimplify(false))), |
| hasTree(expectedWithoutSimplify)); |
| } |
| |
| @Test void testJoinPushCondition() { |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .scan("DEPT") |
| .join(JoinRelType.INNER, |
| b.equals( |
| b.call(SqlStdOperatorTable.PLUS, |
| b.field(2, 0, "DEPTNO"), |
| b.field(2, 0, "EMPNO")), |
| b.field(2, 1, "DEPTNO"))) |
| .build(); |
| // SELECT * FROM EMP AS e JOIN DEPT AS d ON e.DEPTNO + e.EMPNO = d.DEPTNO |
| // becomes |
| // SELECT * FROM (SELECT *, EMPNO + DEPTNO AS x FROM EMP) AS e |
| // JOIN DEPT AS d ON e.x = d.DEPTNO |
| final String expectedWithoutPush = "" |
| + "LogicalJoin(condition=[=(+($7, $0), $8)], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " |
| + "HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$9], " |
| + "DNAME=[$10], LOC=[$11])\n" |
| + " LogicalJoin(condition=[=($8, $9)], joinType=[inner])\n" |
| + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " |
| + "HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[+($7, $0)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expectedWithoutPush)); |
| assertThat(f.apply(createBuilder(c -> c.withPushJoinCondition(true))), |
| hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withPushJoinCondition(false))), |
| hasTree(expectedWithoutPush)); |
| } |
| |
| @Test void testJoinCartesian() { |
| // Equivalent SQL: |
| // SELECT * emp CROSS JOIN dept |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .scan("DEPT") |
| .join(JoinRelType.INNER) |
| .build(); |
| final String expected = |
| "LogicalJoin(condition=[true], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testCorrelationFails() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final Holder<@Nullable RexCorrelVariable> v = Holder.empty(); |
| try { |
| builder.scan("EMP") |
| .variable(v) |
| .filter(builder.equals(builder.field(0), v.get())) |
| .scan("DEPT") |
| .join(JoinRelType.INNER, builder.literal(true), |
| ImmutableSet.of(v.get().id)); |
| fail("expected error"); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| containsString("variable $cor0 must not be used by left input to correlation")); |
| } |
| } |
| |
| @Test void testCorrelationWithCondition() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final Holder<@Nullable RexCorrelVariable> v = Holder.empty(); |
| RelNode root = builder.scan("EMP") |
| .variable(v) |
| .scan("DEPT") |
| .filter( |
| builder.equals(builder.field(0), |
| builder.field(v.get(), "DEPTNO"))) |
| .join(JoinRelType.LEFT, |
| builder.equals(builder.field(2, 0, "SAL"), |
| builder.literal(1000)), |
| ImmutableSet.of(v.get().id)) |
| .build(); |
| // Note that the join filter gets pushed to the right-hand input of |
| // LogicalCorrelate |
| final String expected = "" |
| + "LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{5, 7}])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalFilter(condition=[=($cor0.SAL, 1000)])\n" |
| + " LogicalFilter(condition=[=($0, $cor0.DEPTNO)])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testTrivialCorrelation() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final Holder<@Nullable RexCorrelVariable> v = Holder.empty(); |
| RelNode root = builder.scan("EMP") |
| .variable(v) |
| .scan("DEPT") |
| .join(JoinRelType.LEFT, |
| builder.equals(builder.field(2, 0, "SAL"), |
| builder.literal(1000)), |
| ImmutableSet.of(v.get().id)) |
| .build(); |
| // Note that the join is emitted since the query is not actually a correlated. |
| final String expected = "" |
| + "LogicalJoin(condition=[=($5, 1000)], joinType=[left])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAntiJoin() { |
| // Equivalent SQL: |
| // SELECT * FROM dept d |
| // WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno) |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = builder |
| .scan("DEPT") |
| .scan("EMP") |
| .antiJoin( |
| builder.equals( |
| builder.field(2, 0, "DEPTNO"), |
| builder.field(2, 1, "DEPTNO"))) |
| .build(); |
| final String expected = "" |
| + "LogicalJoin(condition=[=($0, $10)], joinType=[anti])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAlias() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp AS e, dept |
| // WHERE e.deptno = dept.deptno |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("e") |
| .scan("DEPT") |
| .join(JoinRelType.LEFT) |
| .filter( |
| builder.equals(builder.field("e", "DEPTNO"), |
| builder.field("DEPT", "DEPTNO"))) |
| .project(builder.field("e", "ENAME"), |
| builder.field("DEPT", "DNAME")) |
| .build(); |
| final String expected = "LogicalProject(ENAME=[$1], DNAME=[$9])\n" |
| + " LogicalFilter(condition=[=($7, $8)])\n" |
| + " LogicalJoin(condition=[true], joinType=[left])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| final RelDataTypeField field = root.getRowType().getFieldList().get(1); |
| assertThat(field.getName(), is("DNAME")); |
| assertThat(field.getType().isNullable(), is(true)); |
| } |
| |
| @Test void testAlias2() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp AS e, emp as m, dept |
| // WHERE e.deptno = dept.deptno |
| // AND m.empno = e.mgr |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("e") |
| .scan("EMP") |
| .as("m") |
| .scan("DEPT") |
| .join(JoinRelType.INNER) |
| .join(JoinRelType.INNER) |
| .filter( |
| builder.equals(builder.field("e", "DEPTNO"), |
| builder.field("DEPT", "DEPTNO")), |
| builder.equals(builder.field("m", "EMPNO"), |
| builder.field("e", "MGR"))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[AND(=($7, $16), =($8, $3))])\n" |
| + " LogicalJoin(condition=[true], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalJoin(condition=[true], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAliasSort() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("e") |
| .sort(0) |
| .project(builder.field("e", "EMPNO")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0])\n" |
| + " LogicalSort(sort0=[$0], dir0=[ASC])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAliasLimit() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("e") |
| .sort(1) |
| .sortLimit(10, 20) // aliases were lost here if preceded by sort() |
| .project(builder.field("e", "EMPNO")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0])\n" |
| + " LogicalSort(sort0=[$1], dir0=[ASC], offset=[10], fetch=[20])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1551">[CALCITE-1551] |
| * RelBuilder's project() doesn't preserve alias</a>. */ |
| @Test void testAliasProject() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("EMP_alias") |
| .project(builder.field("DEPTNO"), |
| builder.literal(20)) |
| .project(builder.field("EMP_alias", "DEPTNO")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests that table aliases are propagated even when there is a project on |
| * top of a project. (Aliases tend to get lost when projects are merged). */ |
| @Test void testAliasProjectProject() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("EMP_alias") |
| .project(builder.field("DEPTNO"), |
| builder.literal(20)) |
| .project(builder.field(1), |
| builder.literal(10), |
| builder.field(0)) |
| .project(builder.alias(builder.field(1), "sum"), |
| builder.field("EMP_alias", "DEPTNO")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(sum=[10], DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests that table aliases are propagated and are available to a filter, |
| * even when there is a project on top of a project. (Aliases tend to get lost |
| * when projects are merged). */ |
| @Test void testAliasFilter() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("EMP_alias") |
| .project(builder.field("DEPTNO"), |
| builder.literal(20)) |
| .project(builder.field(1), // literal 20 |
| builder.literal(10), |
| builder.field(0)) // DEPTNO |
| .filter( |
| builder.greaterThan(builder.field(1), |
| builder.field("EMP_alias", "DEPTNO"))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[>($1, $2)])\n" |
| + " LogicalProject($f1=[20], $f2=[10], DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests that the {@link RelBuilder#alias(RexNode, String)} function is |
| * idempotent. */ |
| @Test void testScanAlias() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| builder.scan("EMP"); |
| |
| // Simplify "emp.deptno as d as d" to "emp.deptno as d". |
| final RexNode e0 = |
| builder.alias(builder.alias(builder.field("DEPTNO"), "D"), "D"); |
| assertThat(e0.toString(), is("AS($7, 'D')")); |
| |
| // It would be nice if RelBuilder could simplify |
| // "emp.deptno as deptno" to "emp.deptno", but there is not |
| // enough information in RexInputRef. |
| final RexNode e1 = builder.alias(builder.field("DEPTNO"), "DEPTNO"); |
| assertThat(e1.toString(), is("AS($7, 'DEPTNO')")); |
| |
| // The intervening alias 'DEPTNO' is removed |
| final RexNode e2 = |
| builder.alias(builder.alias(builder.field("DEPTNO"), "DEPTNO"), "D1"); |
| assertThat(e2.toString(), is("AS($7, 'D1')")); |
| |
| // Simplify "emp.deptno as d2 as d3" to "emp.deptno as d3" |
| // because "d3" alias overrides "d2". |
| final RexNode e3 = |
| builder.alias(builder.alias(builder.field("DEPTNO"), "D2"), "D3"); |
| assertThat(e3.toString(), is("AS($7, 'D3')")); |
| |
| final RelNode root = builder.project(e0, e1, e2, e3).build(); |
| final String expected = "" |
| + "LogicalProject(D=[$7], DEPTNO=[$7], D1=[$7], D3=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** |
| * Tests that project field name aliases are suggested incrementally. |
| */ |
| @Test void testAliasSuggester() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = builder.scan("EMP") |
| .project(builder.field(0), |
| builder.field(0), |
| builder.field(0), |
| builder.field(0), |
| builder.field(0), |
| builder.field(0), |
| builder.field(0), |
| builder.field(0), |
| builder.field(0), |
| builder.field(0), |
| builder.field(0), |
| builder.field(0)) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0], EMPNO0=[$0], EMPNO1=[$0], " |
| + "EMPNO2=[$0], EMPNO3=[$0], EMPNO4=[$0], EMPNO5=[$0], " |
| + "EMPNO6=[$0], EMPNO7=[$0], EMPNO8=[$0], EMPNO9=[$0], EMPNO10=[$0])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAliasAggregate() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("EMP_alias") |
| .project(builder.field("DEPTNO"), |
| builder.literal(20)) |
| .aggregate(builder.groupKey(builder.field("EMP_alias", "DEPTNO")), |
| builder.sum(builder.field(1))) |
| .project(builder.alias(builder.field(1), "sum"), |
| builder.field("EMP_alias", "DEPTNO")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(sum=[$1], DEPTNO=[$0])\n" |
| + " LogicalAggregate(group=[{0}], agg#0=[SUM($1)])\n" |
| + " LogicalProject(DEPTNO=[$7], $f1=[20])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests that a projection retains field names after a join. */ |
| @Test void testProjectJoin() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("e") |
| .scan("DEPT") |
| .join(JoinRelType.INNER) |
| .project(builder.field("DEPT", "DEPTNO"), |
| builder.field(0), |
| builder.field("e", "MGR")) |
| // essentially a no-op, was previously throwing exception due to |
| // project() using join-renamed fields |
| .project(builder.field("DEPT", "DEPTNO"), |
| builder.field(1), |
| builder.field("e", "MGR")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$8], EMPNO=[$0], MGR=[$3])\n" |
| + " LogicalJoin(condition=[true], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests that a projection after a projection. */ |
| @Test void testProjectProject() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("e") |
| .projectPlus( |
| builder.alias( |
| builder.call(SqlStdOperatorTable.PLUS, builder.field(0), |
| builder.field(3)), "x")) |
| .project(builder.field("e", "DEPTNO"), |
| builder.field(0), |
| builder.field("e", "MGR"), |
| Util.last(builder.fields())) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$7], EMPNO=[$0], MGR=[$3], x=[+($0, $3)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3462">[CALCITE-3462] |
| * Add projectExcept method in RelBuilder for projecting out expressions</a>. */ |
| @Test void testProjectExceptWithOrdinal() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .projectExcept( |
| builder.field(2), |
| builder.field(3)) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0], ENAME=[$1], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3462">[CALCITE-3462] |
| * Add projectExcept method in RelBuilder for projecting out expressions</a>. */ |
| @Test void testProjectExceptWithName() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .projectExcept( |
| builder.field("MGR"), |
| builder.field("JOB")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0], ENAME=[$1], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3462">[CALCITE-3462] |
| * Add projectExcept method in RelBuilder for projecting out expressions</a>. */ |
| @Test void testProjectExceptWithExplicitAliasAndName() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("e") |
| .projectExcept( |
| builder.field("e", "MGR"), |
| builder.field("e", "JOB")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0], ENAME=[$1], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3462">[CALCITE-3462] |
| * Add projectExcept method in RelBuilder for projecting out expressions</a>. */ |
| @Test void testProjectExceptWithImplicitAliasAndName() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .projectExcept( |
| builder.field("EMP", "MGR"), |
| builder.field("EMP", "JOB")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(EMPNO=[$0], ENAME=[$1], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3462">[CALCITE-3462] |
| * Add projectExcept method in RelBuilder for projecting out expressions</a>. */ |
| @Test void testProjectExceptWithDuplicateField() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| IllegalArgumentException ex = assertThrows(IllegalArgumentException.class, () -> { |
| builder.scan("EMP") |
| .projectExcept( |
| builder.field("EMP", "MGR"), |
| builder.field("EMP", "MGR")); |
| }, "Project should fail since we are trying to remove the same field two times."); |
| assertThat(ex.getMessage(), containsString("Input list contains duplicates.")); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3462">[CALCITE-3462] |
| * Add projectExcept method in RelBuilder for projecting out expressions</a>. */ |
| @Test void testProjectExceptWithMissingField() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| builder.scan("EMP"); |
| RexNode deptnoField = builder.field("DEPTNO"); |
| IllegalArgumentException ex = assertThrows(IllegalArgumentException.class, () -> { |
| builder.project( |
| builder.field("EMPNO"), |
| builder.field("ENAME")) |
| .projectExcept(deptnoField); |
| }, "Project should fail since we are trying to remove a field that does not exist."); |
| assertThat(ex.getMessage(), allOf(containsString("Expression"), containsString("not found"))); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-4409">[CALCITE-4409] |
| * Improve exception when RelBuilder tries to create a field on a non-struct expression</a>. */ |
| @Test void testFieldOnNonStructExpression() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| IllegalStateException ex = assertThrows(IllegalStateException.class, () -> { |
| builder.scan("EMP") |
| .project( |
| builder.field(builder.field("EMPNO"), "abc")) |
| .build(); |
| }, "Field should fail since we are trying access a field on expression with non-struct type"); |
| assertThat(ex.getMessage(), |
| is("Trying to access field abc in a type with no fields: SMALLINT")); |
| } |
| |
| @Test void testMultiLevelAlias() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("e") |
| .scan("EMP") |
| .as("m") |
| .scan("DEPT") |
| .join(JoinRelType.INNER) |
| .join(JoinRelType.INNER) |
| .project(builder.field("DEPT", "DEPTNO"), |
| builder.field(16), |
| builder.field("m", "EMPNO"), |
| builder.field("e", "MGR")) |
| .as("all") |
| .filter( |
| builder.greaterThan(builder.field("DEPT", "DEPTNO"), |
| builder.literal(100))) |
| .project(builder.field("DEPT", "DEPTNO"), |
| builder.field("all", "EMPNO")) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$0], EMPNO=[$2])\n" |
| + " LogicalFilter(condition=[>($0, 100)])\n" |
| + " LogicalProject(DEPTNO=[$16], DEPTNO0=[$16], EMPNO=[$8], MGR=[$3])\n" |
| + " LogicalJoin(condition=[true], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalJoin(condition=[true], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testUnionAlias() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .as("e1") |
| .project(builder.field("EMPNO"), |
| builder.call(SqlStdOperatorTable.CONCAT, |
| builder.field("ENAME"), |
| builder.literal("-1"))) |
| .scan("EMP") |
| .as("e2") |
| .project(builder.field("EMPNO"), |
| builder.call(SqlStdOperatorTable.CONCAT, |
| builder.field("ENAME"), |
| builder.literal("-2"))) |
| .union(false) // aliases lost here |
| .project(builder.fields(Lists.newArrayList(1, 0))) |
| .build(); |
| final String expected = "" |
| + "LogicalProject($f1=[$1], EMPNO=[$0])\n" |
| + " LogicalUnion(all=[false])\n" |
| + " LogicalProject(EMPNO=[$0], $f1=[||($1, '-1')])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalProject(EMPNO=[$0], $f1=[||($1, '-2')])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1523">[CALCITE-1523] |
| * Add RelBuilder field() method to reference aliased relations not on top of |
| * stack</a>, accessing tables aliased that are not accessible in the top |
| * RelNode. */ |
| @Test void testAliasPastTop() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // LEFT JOIN dept ON emp.deptno = dept.deptno |
| // AND emp.empno = 123 |
| // AND dept.deptno IS NOT NULL |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .scan("DEPT") |
| .join(JoinRelType.LEFT, |
| builder.equals(builder.field(2, "EMP", "DEPTNO"), |
| builder.field(2, "DEPT", "DEPTNO")), |
| builder.equals(builder.field(2, "EMP", "EMPNO"), |
| builder.literal(123))) |
| .build(); |
| final String expected = "" |
| + "LogicalJoin(condition=[AND(=($7, $8), =($0, 123))], joinType=[left])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** As {@link #testAliasPastTop()}. */ |
| @Test void testAliasPastTop2() { |
| // Equivalent SQL: |
| // SELECT t1.EMPNO, t2.EMPNO, t3.DEPTNO |
| // FROM emp t1 |
| // INNER JOIN emp t2 ON t1.EMPNO = t2.EMPNO |
| // INNER JOIN dept t3 ON t1.DEPTNO = t3.DEPTNO |
| // AND t2.JOB != t3.LOC |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP").as("t1") |
| .scan("EMP").as("t2") |
| .join(JoinRelType.INNER, |
| builder.equals(builder.field(2, "t1", "EMPNO"), |
| builder.field(2, "t2", "EMPNO"))) |
| .scan("DEPT").as("t3") |
| .join(JoinRelType.INNER, |
| builder.equals(builder.field(2, "t1", "DEPTNO"), |
| builder.field(2, "t3", "DEPTNO")), |
| builder.not( |
| builder.equals(builder.field(2, "t2", "JOB"), |
| builder.field(2, "t3", "LOC")))) |
| .build(); |
| // Cols: |
| // 0-7 EMP as t1 |
| // 8-15 EMP as t2 |
| // 16-18 DEPT as t3 |
| final String expected = "" |
| + "LogicalJoin(condition=[AND(=($7, $16), <>($10, $18))], joinType=[inner])\n" |
| + " LogicalJoin(condition=[=($0, $8)], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testEmpty() { |
| // Equivalent SQL: |
| // SELECT deptno, true FROM dept LIMIT 0 |
| // optimized to |
| // VALUES |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("DEPT") |
| .project(builder.field(0), builder.literal(false)) |
| .empty() |
| .build(); |
| final String expected = |
| "LogicalValues(tuples=[[]])\n"; |
| assertThat(root, hasTree(expected)); |
| final String expectedType = |
| "RecordType(TINYINT NOT NULL DEPTNO, BOOLEAN NOT NULL $f1) NOT NULL"; |
| assertThat(root.getRowType().getFullTypeString(), is(expectedType)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3172">[CALCITE-3172] |
| * RelBuilder#empty does not keep aliases</a>. */ |
| @Test void testEmptyWithAlias() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final String expected = |
| "LogicalProject(DEPTNO=[$0], DNAME=[$1])\n LogicalValues(tuples=[[]])\n"; |
| final String expectedType = |
| "RecordType(TINYINT NOT NULL DEPTNO, VARCHAR(14) DNAME) NOT NULL"; |
| |
| // Scan + Empty + Project (without alias) |
| RelNode root = |
| builder.scan("DEPT") |
| .empty() |
| .project( |
| builder.field("DEPTNO"), |
| builder.field("DNAME")) |
| .build(); |
| assertThat(root, hasTree(expected)); |
| assertThat(root.getRowType().getFullTypeString(), is(expectedType)); |
| |
| // Scan + Empty + Project (with alias) |
| root = |
| builder.scan("DEPT").as("d") |
| .empty() |
| .project( |
| builder.field(1, "d", "DEPTNO"), |
| builder.field(1, "d", "DNAME")) |
| .build(); |
| assertThat(root, hasTree(expected)); |
| assertThat(root.getRowType().getFullTypeString(), is(expectedType)); |
| |
| // Scan + Filter false (implicitly converted into Empty) + Project (with alias) |
| root = |
| builder.scan("DEPT").as("d") |
| .filter(builder.literal(false)) |
| .project( |
| builder.field(1, "d", "DEPTNO"), |
| builder.field(1, "d", "DNAME")) |
| .build(); |
| assertThat(root, hasTree(expected)); |
| assertThat(root.getRowType().getFullTypeString(), is(expectedType)); |
| } |
| |
| @Test void testValues() { |
| // Equivalent SQL: |
| // VALUES (true, 1), (false, -50) AS t(a, b) |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.values(new String[]{"a", "b"}, true, 1, false, -50) |
| .build(); |
| final String expected = |
| "LogicalValues(tuples=[[{ true, 1 }, { false, -50 }]])\n"; |
| assertThat(root, hasTree(expected)); |
| final String expectedType = |
| "RecordType(BOOLEAN NOT NULL a, INTEGER NOT NULL b) NOT NULL"; |
| assertThat(root.getRowType().getFullTypeString(), is(expectedType)); |
| } |
| |
| /** Tests creating Values with some field names and some values null. */ |
| @Test void testValuesNullable() { |
| // Equivalent SQL: |
| // VALUES (null, 1, 'abc'), (false, null, 'longer string') |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.values(new String[]{"a", null, "c"}, |
| null, 1, "abc", |
| false, null, "longer string").build(); |
| final String expected = |
| "LogicalValues(tuples=[[{ null, 1, 'abc' }, { false, null, 'longer string' }]])\n"; |
| assertThat(root, hasTree(expected)); |
| final String expectedType = |
| "RecordType(BOOLEAN a, INTEGER expr$1, CHAR(13) NOT NULL c) NOT NULL"; |
| assertThat(root.getRowType().getFullTypeString(), is(expectedType)); |
| } |
| |
| @Test void testValuesBadNullFieldNames() { |
| try { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelBuilder root = builder.values((String[]) null, "a", "b"); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| is("Value count must be a positive multiple of field count")); |
| } |
| } |
| |
| @Test void testValuesBadNoFields() { |
| try { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelBuilder root = builder.values(new String[0], 1, 2, 3); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| is("Value count must be a positive multiple of field count")); |
| } |
| } |
| |
| @Test void testValuesBadNoValues() { |
| try { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelBuilder root = builder.values(new String[]{"a", "b"}); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| is("Value count must be a positive multiple of field count")); |
| } |
| } |
| |
| @Test void testValuesBadOddMultiple() { |
| try { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelBuilder root = builder.values(new String[] {"a", "b"}, 1, 2, 3, 4, 5); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| is("Value count must be a positive multiple of field count")); |
| } |
| } |
| |
| @Test void testValuesBadAllNull() { |
| try { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelBuilder root = |
| builder.values(new String[] {"a", "b"}, null, null, 1, null); |
| fail("expected error, got " + root); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), |
| is("All values of field 'b' (field index 1) are null; cannot deduce type")); |
| } |
| } |
| |
| @Test void testValuesAllNull() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelDataType rowType = |
| builder.getTypeFactory().builder() |
| .add("a", SqlTypeName.BIGINT) |
| .add("a", SqlTypeName.VARCHAR, 10) |
| .build(); |
| RelNode root = |
| builder.values(rowType, null, null, 1, null).build(); |
| final String expected = |
| "LogicalValues(tuples=[[{ null, null }, { 1, null }]])\n"; |
| assertThat(root, hasTree(expected)); |
| final String expectedType = |
| "RecordType(BIGINT NOT NULL a, VARCHAR(10) NOT NULL a) NOT NULL"; |
| assertThat(root.getRowType().getFullTypeString(), is(expectedType)); |
| } |
| |
| @Test void testValuesRename() { |
| final Function<RelBuilder, RelNode> f = b -> |
| b.values(new String[] {"a", "b"}, 1, true, 2, false) |
| .rename(Arrays.asList("x", "y")) |
| .build(); |
| final String expected = |
| "LogicalValues(tuples=[[{ 1, true }, { 2, false }]])\n"; |
| final String expectedRowType = "RecordType(INTEGER x, BOOLEAN y)"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| assertThat(f.apply(createBuilder()).getRowType().toString(), |
| is(expectedRowType)); |
| } |
| |
| /** Tests that {@code Union(Project(Values), ... Project(Values))} is |
| * simplified to {@code Values}. It occurs commonly: people write |
| * {@code SELECT 1 UNION SELECT 2}. */ |
| @Test void testUnionProjectValues() { |
| // Equivalent SQL: |
| // SELECT 'a', 1 |
| // UNION ALL |
| // SELECT 'b', 2 |
| final BiFunction<RelBuilder, Boolean, RelNode> f = (b, all) -> |
| b.values(new String[] {"zero"}, 0) |
| .project(b.literal("a"), b.literal(1)) |
| .values(new String[] {"zero"}, 0) |
| .project(b.literal("b"), b.literal(2)) |
| .union(all, 2) |
| .build(); |
| final String expected = |
| "LogicalValues(tuples=[[{ 'a', 1 }, { 'b', 2 }]])\n"; |
| |
| // Same effect with and without ALL because tuples are distinct |
| assertThat(f.apply(createBuilder(), true), hasTree(expected)); |
| assertThat(f.apply(createBuilder(), false), hasTree(expected)); |
| } |
| |
| @Test void testUnionProjectValues2() { |
| // Equivalent SQL: |
| // SELECT 'a', 1 FROM (VALUES (0), (0)) |
| // UNION ALL |
| // SELECT 'b', 2 |
| final BiFunction<RelBuilder, Boolean, RelNode> f = (b, all) -> |
| b.values(new String[] {"zero"}, 0) |
| .project(b.literal("a"), b.literal(1)) |
| .values(new String[] {"zero"}, 0, 0) |
| .project(b.literal("b"), b.literal(2)) |
| .union(all, 2) |
| .build(); |
| |
| // Different effect with and without ALL because tuples are not distinct. |
| final String expectedAll = |
| "LogicalValues(tuples=[[{ 'a', 1 }, { 'b', 2 }, { 'b', 2 }]])\n"; |
| final String expectedDistinct = |
| "LogicalValues(tuples=[[{ 'a', 1 }, { 'b', 2 }]])\n"; |
| assertThat(f.apply(createBuilder(), true), hasTree(expectedAll)); |
| assertThat(f.apply(createBuilder(), false), hasTree(expectedDistinct)); |
| } |
| |
| @Test void testSort() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // ORDER BY 3. 1 DESC |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| .sort(builder.field(2), builder.desc(builder.field(0))) |
| .build(); |
| final String expected = |
| "LogicalSort(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[DESC])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| assertThat(((Sort) root).getSortExps().toString(), is("[$2, $0]")); |
| |
| // same result using ordinals |
| final RelNode root2 = |
| builder.scan("EMP") |
| .sort(2, -1) |
| .build(); |
| assertThat(root2, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1015">[CALCITE-1015] |
| * OFFSET 0 causes AssertionError</a>. */ |
| @Test void testTrivialSort() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // OFFSET 0 |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| .sortLimit(0, -1, ImmutableList.of()) |
| .build(); |
| final String expected = "LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testSortDuplicate() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // ORDER BY empno DESC, deptno, empno ASC, hiredate |
| // |
| // The sort key "empno ASC" is unnecessary and is ignored. |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| .sort(builder.desc(builder.field("EMPNO")), |
| builder.field("DEPTNO"), |
| builder.field("EMPNO"), |
| builder.field("HIREDATE")) |
| .build(); |
| final String expected = "LogicalSort(sort0=[$0], sort1=[$7], sort2=[$4], " |
| + "dir0=[DESC], dir1=[ASC], dir2=[ASC])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testSortByExpression() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // ORDER BY ename ASC NULLS LAST, hiredate + mgr DESC NULLS FIRST |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| .sort(builder.nullsLast(builder.desc(builder.field(1))), |
| builder.nullsFirst( |
| builder.call(SqlStdOperatorTable.PLUS, builder.field(4), |
| builder.field(3)))) |
| .build(); |
| final String expected = |
| "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n" |
| + " LogicalSort(sort0=[$1], sort1=[$8], dir0=[DESC-nulls-last], dir1=[ASC-nulls-first])\n" |
| + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[+($4, $3)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testLimit() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // OFFSET 2 FETCH 10 |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| .limit(2, 10) |
| .build(); |
| final String expected = |
| "LogicalSort(offset=[2], fetch=[10])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testSortLimit() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // ORDER BY deptno DESC FETCH 10 |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| .sortLimit(-1, 10, builder.desc(builder.field("DEPTNO"))) |
| .build(); |
| final String expected = |
| "LogicalSort(sort0=[$7], dir0=[DESC], fetch=[10])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testSortLimit0() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // ORDER BY deptno DESC FETCH 0 |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .sortLimit(-1, 0, b.desc(b.field("DEPTNO"))) |
| .build(); |
| final String expected = "LogicalValues(tuples=[[]])\n"; |
| final String expectedNoSimplify = "" |
| + "LogicalSort(sort0=[$7], dir0=[DESC], fetch=[0])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withSimplifyLimit(true))), |
| hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withSimplifyLimit(false))), |
| hasTree(expectedNoSimplify)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1610">[CALCITE-1610] |
| * RelBuilder sort-combining optimization treats aliases incorrectly</a>. */ |
| @Test void testSortOverProjectSort() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| builder.scan("EMP") |
| .sort(0) |
| .project(builder.field(1)) |
| // was throwing exception here when attempting to apply to |
| // inner sort node |
| .limit(0, 1) |
| .build(); |
| RelNode root = builder.scan("EMP") |
| .sort(0) |
| .project(Lists.newArrayList(builder.field(1)), |
| Lists.newArrayList("F1")) |
| .limit(0, 1) |
| // make sure we can still access the field by alias |
| .project(builder.field("F1")) |
| .build(); |
| String expected = "LogicalProject(F1=[$1])\n" |
| + " LogicalSort(sort0=[$0], dir0=[ASC], fetch=[1])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests that a sort on a field followed by a limit gives the same |
| * effect as calling sortLimit. |
| * |
| * <p>In general a relational operator cannot rely on the order of its input, |
| * but it is reasonable to merge sort and limit if they were created by |
| * consecutive builder operations. And clients such as Piglet rely on it. */ |
| @Test void testSortThenLimit() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| .sort(builder.desc(builder.field("DEPTNO"))) |
| .limit(-1, 10) |
| .build(); |
| final String expected = "" |
| + "LogicalSort(sort0=[$7], dir0=[DESC], fetch=[10])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| |
| final RelNode root2 = |
| builder.scan("EMP") |
| .sortLimit(-1, 10, builder.desc(builder.field("DEPTNO"))) |
| .build(); |
| assertThat(root2, hasTree(expected)); |
| } |
| |
| /** Tests that a sort on an expression followed by a limit gives the same |
| * effect as calling sortLimit. */ |
| @Test void testSortExpThenLimit() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("DEPT") |
| .sort( |
| builder.desc( |
| builder.call(SqlStdOperatorTable.PLUS, |
| builder.field("DEPTNO"), builder.literal(1)))) |
| .limit(3, 10) |
| .build(); |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n" |
| + " LogicalSort(sort0=[$3], dir0=[DESC], offset=[3], fetch=[10])\n" |
| + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], $f3=[+($0, 1)])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| |
| final RelNode root2 = |
| builder.scan("DEPT") |
| .sortLimit(3, 10, |
| builder.desc( |
| builder.call(SqlStdOperatorTable.PLUS, |
| builder.field("DEPTNO"), builder.literal(1)))) |
| .build(); |
| assertThat(root2, hasTree(expected)); |
| } |
| |
| /** Tests {@link org.apache.calcite.tools.RelRunner} for a VALUES query. */ |
| @Test void testRunValues() throws Exception { |
| // Equivalent SQL: |
| // VALUES (true, 1), (false, -50) AS t(a, b) |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.values(new String[]{"a", "b"}, true, 1, false, -50) |
| .build(); |
| try (PreparedStatement preparedStatement = RelRunners.run(root)) { |
| String s = CalciteAssert.toString(preparedStatement.executeQuery()); |
| final String result = "a=true; b=1\n" |
| + "a=false; b=-50\n"; |
| assertThat(s, is(result)); |
| } |
| } |
| |
| /** Tests {@link org.apache.calcite.tools.RelRunner} for a table scan + filter |
| * query. */ |
| @Test void testRun() throws Exception { |
| // Equivalent SQL: |
| // SELECT * FROM EMP WHERE DEPTNO = 20 |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.equals(builder.field("DEPTNO"), builder.literal(20))) |
| .build(); |
| |
| // Note that because the table has been resolved in the RelNode tree |
| // we do not need to supply a "schema" as context to the runner. |
| try (PreparedStatement preparedStatement = RelRunners.run(root)) { |
| String s = CalciteAssert.toString(preparedStatement.executeQuery()); |
| final String result = "" |
| + "EMPNO=7369; ENAME=SMITH; JOB=CLERK; MGR=7902; HIREDATE=1980-12-17; SAL=800.00; COMM=null; DEPTNO=20\n" |
| + "EMPNO=7566; ENAME=JONES; JOB=MANAGER; MGR=7839; HIREDATE=1981-02-04; SAL=2975.00; COMM=null; DEPTNO=20\n" |
| + "EMPNO=7788; ENAME=SCOTT; JOB=ANALYST; MGR=7566; HIREDATE=1987-04-19; SAL=3000.00; COMM=null; DEPTNO=20\n" |
| + "EMPNO=7876; ENAME=ADAMS; JOB=CLERK; MGR=7788; HIREDATE=1987-05-23; SAL=1100.00; COMM=null; DEPTNO=20\n" |
| + "EMPNO=7902; ENAME=FORD; JOB=ANALYST; MGR=7566; HIREDATE=1981-12-03; SAL=3000.00; COMM=null; DEPTNO=20\n"; |
| assertThat(s, is(result)); |
| } |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-1595">[CALCITE-1595] |
| * RelBuilder.call throws NullPointerException if argument types are |
| * invalid</a>. */ |
| @Test void testTypeInferenceValidation() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| // test for a) call(operator, Iterable<RexNode>) |
| final RexNode arg0 = builder.literal(0); |
| final RexNode arg1 = builder.literal("xyz"); |
| try { |
| builder.call(SqlStdOperatorTable.PLUS, Lists.newArrayList(arg0, arg1)); |
| fail("Invalid combination of parameter types"); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), containsString("Cannot infer return type")); |
| } |
| |
| // test for b) call(operator, RexNode...) |
| try { |
| builder.call(SqlStdOperatorTable.PLUS, arg0, arg1); |
| fail("Invalid combination of parameter types"); |
| } catch (IllegalArgumentException e) { |
| assertThat(e.getMessage(), containsString("Cannot infer return type")); |
| } |
| } |
| |
| @Test void testPivot() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM (SELECT mgr, deptno, job, sal FROM emp) |
| // PIVOT (SUM(sal) AS ss, COUNT(*) AS c |
| // FOR (job, deptno) |
| // IN (('CLERK', 10) AS c10, ('MANAGER', 20) AS m20)) |
| // |
| // translates to |
| // SELECT mgr, |
| // SUM(sal) FILTER (WHERE job = 'CLERK' AND deptno = 10) AS c10_ss, |
| // COUNT(*) FILTER (WHERE job = 'CLERK' AND deptno = 10) AS c10_c, |
| // SUM(sal) FILTER (WHERE job = 'MANAGER' AND deptno = 20) AS m20_ss, |
| // COUNT(*) FILTER (WHERE job = 'MANAGER' AND deptno = 20) AS m20_c |
| // FROM emp |
| // GROUP BY mgr |
| // |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .pivot(b.groupKey("MGR"), |
| Arrays.asList( |
| b.sum(b.field("SAL")).as("SS"), |
| b.count().as("C")), |
| b.fields(Arrays.asList("JOB", "DEPTNO")), |
| ImmutableMap.<String, List<RexNode>>builder() |
| .put("C10", |
| Arrays.asList(b.literal("CLERK"), b.literal(10))) |
| .put("M20", |
| Arrays.asList(b.literal("MANAGER"), b.literal(20))) |
| .build() |
| .entrySet()) |
| .build(); |
| final String expected = "" |
| + "LogicalAggregate(group=[{0}], C10_SS=[SUM($1) FILTER $2], " |
| + "C10_C=[COUNT() FILTER $2], M20_SS=[SUM($1) FILTER $3], " |
| + "M20_C=[COUNT() FILTER $3])\n" |
| + " LogicalProject(MGR=[$3], SAL=[$5], " |
| + "$f8=[IS TRUE(AND(=($2, 'CLERK'), =($7, 10)))], " |
| + "$f9=[IS TRUE(AND(=($2, 'MANAGER'), =($7, 20)))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| } |
| |
| @Test void testUnpivot() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM (SELECT deptno, job, sal, comm FROM emp) |
| // UNPIVOT INCLUDE NULLS (remuneration |
| // FOR remuneration_type IN (comm AS 'commission', |
| // sal AS 'salary')) |
| // |
| // translates to |
| // SELECT e.deptno, e.job, |
| // CASE t.remuneration_type |
| // WHEN 'commission' THEN comm |
| // ELSE sal |
| // END AS remuneration |
| // FROM emp |
| // CROSS JOIN VALUES ('commission', 'salary') AS t (remuneration_type) |
| // |
| final BiFunction<RelBuilder, Boolean, RelNode> f = (b, includeNulls) -> |
| b.scan("EMP") |
| .unpivot(includeNulls, ImmutableList.of("REMUNERATION"), |
| ImmutableList.of("REMUNERATION_TYPE"), |
| Pair.zip( |
| Arrays.asList(ImmutableList.of(b.literal("commission")), |
| ImmutableList.of(b.literal("salary"))), |
| Arrays.asList(ImmutableList.of(b.field("COMM")), |
| ImmutableList.of(b.field("SAL"))))) |
| .build(); |
| final String expectedIncludeNulls = "" |
| + "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " |
| + "HIREDATE=[$4], DEPTNO=[$7], REMUNERATION_TYPE=[$8], " |
| + "REMUNERATION=[CASE(=($8, 'commission'), $6, =($8, 'salary'), $5, " |
| + "null:NULL)])\n" |
| + " LogicalJoin(condition=[true], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalValues(tuples=[[{ 'commission' }, { 'salary' }]])\n"; |
| final String expectedExcludeNulls = "" |
| + "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " |
| + "HIREDATE=[$4], DEPTNO=[$5], REMUNERATION_TYPE=[$6], " |
| + "REMUNERATION=[CAST($7):DECIMAL(7, 2) NOT NULL])\n" |
| + " LogicalFilter(condition=[IS NOT NULL($7)])\n" |
| + " " + expectedIncludeNulls.replace("\n ", "\n "); |
| assertThat(f.apply(createBuilder(), true), hasTree(expectedIncludeNulls)); |
| assertThat(f.apply(createBuilder(), false), hasTree(expectedExcludeNulls)); |
| } |
| |
| @Test void testMatchRecognize() { |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // MATCH_RECOGNIZE ( |
| // PARTITION BY deptno |
| // ORDER BY empno asc |
| // MEASURES |
| // STRT.mgr as start_nw, |
| // LAST(DOWN.mgr) as bottom_nw, |
| // PATTERN (STRT DOWN+ UP+) WITHIN INTERVAL '5' SECOND |
| // DEFINE |
| // DOWN as DOWN.mgr < PREV(DOWN.mgr), |
| // UP as UP.mgr > PREV(UP.mgr) |
| // ) |
| final RelBuilder builder = RelBuilder.create(config().build()).scan("EMP"); |
| final RelDataTypeFactory typeFactory = builder.getTypeFactory(); |
| final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER); |
| |
| RexNode pattern = builder.patternConcat( |
| builder.literal("STRT"), |
| builder.patternQuantify(builder.literal("DOWN"), builder.literal(1), |
| builder.literal(-1), builder.literal(false)), |
| builder.patternQuantify(builder.literal("UP"), builder.literal(1), |
| builder.literal(-1), builder.literal(false))); |
| |
| ImmutableMap.Builder<String, RexNode> pdBuilder = new ImmutableMap.Builder<>(); |
| RexNode downDefinition = builder.lessThan( |
| builder.call(SqlStdOperatorTable.PREV, |
| builder.patternField("DOWN", intType, 3), |
| builder.literal(0)), |
| builder.call(SqlStdOperatorTable.PREV, |
| builder.patternField("DOWN", intType, 3), |
| builder.literal(1))); |
| pdBuilder.put("DOWN", downDefinition); |
| RexNode upDefinition = builder.greaterThan( |
| builder.call(SqlStdOperatorTable.PREV, |
| builder.patternField("UP", intType, 3), |
| builder.literal(0)), |
| builder.call(SqlStdOperatorTable.PREV, |
| builder.patternField("UP", intType, 3), |
| builder.literal(1))); |
| pdBuilder.put("UP", upDefinition); |
| |
| ImmutableList.Builder<RexNode> measuresBuilder = new ImmutableList.Builder<>(); |
| measuresBuilder.add( |
| builder.alias(builder.patternField("STRT", intType, 3), |
| "start_nw")); |
| measuresBuilder.add( |
| builder.alias( |
| builder.call(SqlStdOperatorTable.LAST, |
| builder.patternField("DOWN", intType, 3), |
| builder.literal(0)), |
| "bottom_nw")); |
| |
| RexNode after = builder.getRexBuilder().makeFlag( |
| SqlMatchRecognize.AfterOption.SKIP_TO_NEXT_ROW); |
| |
| ImmutableList.Builder<RexNode> partitionKeysBuilder = new ImmutableList.Builder<>(); |
| partitionKeysBuilder.add(builder.field("DEPTNO")); |
| |
| ImmutableList.Builder<RexNode> orderKeysBuilder = new ImmutableList.Builder<>(); |
| orderKeysBuilder.add(builder.field("EMPNO")); |
| |
| RexNode interval = builder.literal("INTERVAL '5' SECOND"); |
| |
| final ImmutableMap<String, TreeSet<String>> subsets = ImmutableMap.of(); |
| final RelNode root = builder |
| .match(pattern, false, false, pdBuilder.build(), |
| measuresBuilder.build(), after, subsets, false, |
| partitionKeysBuilder.build(), orderKeysBuilder.build(), interval) |
| .build(); |
| final String expected = "LogicalMatch(partition=[[7]], order=[[0]], " |
| + "outputFields=[[$7, 'start_nw', 'bottom_nw']], allRows=[false], " |
| + "after=[FLAG(SKIP TO NEXT ROW)], pattern=[(('STRT', " |
| + "PATTERN_QUANTIFIER('DOWN', 1, -1, false)), " |
| + "PATTERN_QUANTIFIER('UP', 1, -1, false))], " |
| + "isStrictStarts=[false], isStrictEnds=[false], " |
| + "interval=['INTERVAL ''5'' SECOND'], subsets=[[]], " |
| + "patternDefinitions=[[<(PREV(DOWN.$3, 0), PREV(DOWN.$3, 1)), " |
| + ">(PREV(UP.$3, 0), PREV(UP.$3, 1))]], " |
| + "inputFields=[[EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO]])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testFilterCastAny() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelDataType anyType = |
| builder.getTypeFactory().createSqlType(SqlTypeName.ANY); |
| final RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.cast( |
| builder.getRexBuilder().makeInputRef(anyType, 0), |
| SqlTypeName.BOOLEAN)) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[CAST($0):BOOLEAN NOT NULL])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testFilterCastNull() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelDataTypeFactory typeFactory = builder.getTypeFactory(); |
| final RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.getRexBuilder().makeCast( |
| typeFactory.createTypeWithNullability( |
| typeFactory.createSqlType(SqlTypeName.BOOLEAN), true), |
| builder.equals(builder.field("DEPTNO"), |
| builder.literal(10)))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[=($7, 10)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Tests {@link RelBuilder#in} with duplicate values. */ |
| @Test void testFilterIn() { |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .filter( |
| b.in(b.field("DEPTNO"), b.literal(10), b.literal(20), |
| b.literal(10))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[SEARCH($7, Sarg[10, 20])])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withSimplify(false))), |
| hasTree(expected)); |
| } |
| |
| @Test void testFilterOrIn() { |
| final Function<RelBuilder, RelNode> f = b -> |
| b.scan("EMP") |
| .filter( |
| b.or( |
| b.greaterThan(b.field("DEPTNO"), b.literal(15)), |
| b.in(b.field("JOB"), b.literal("CLERK")), |
| b.in(b.field("DEPTNO"), b.literal(10), b.literal(20), |
| b.literal(11), b.literal(10)))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[OR(SEARCH($7, Sarg[10, 11, (15..+∞)]), =($2, 'CLERK'))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| final String expectedWithoutSimplify = "" |
| + "LogicalFilter(condition=[OR(>($7, 15), SEARCH($2, Sarg['CLERK']:CHAR(5)), SEARCH($7, " |
| + "Sarg[10, 11, 20]))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| assertThat(f.apply(createBuilder(c -> c.withSimplify(false))), |
| hasTree(expectedWithoutSimplify)); |
| } |
| |
| /** Tests filter builder with correlation variables. */ |
| @Test void testFilterWithCorrelationVariables() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final Holder<@Nullable RexCorrelVariable> v = Holder.empty(); |
| RelNode root = builder.scan("EMP") |
| .variable(v) |
| .scan("DEPT") |
| .filter(Collections.singletonList(v.get().id), |
| builder.or( |
| builder.and( |
| builder.lessThan(builder.field(v.get(), "DEPTNO"), |
| builder.literal(30)), |
| builder.greaterThan(builder.field(v.get(), "DEPTNO"), |
| builder.literal(20))), |
| builder.isNull(builder.field(2)))) |
| .join(JoinRelType.LEFT, |
| builder.equals(builder.field(2, 0, "SAL"), |
| builder.literal(1000)), |
| ImmutableSet.of(v.get().id)) |
| .build(); |
| |
| final String expected = "" |
| + "LogicalCorrelate(correlation=[$cor0], joinType=[left], " |
| + "requiredColumns=[{5, 7}])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalFilter(condition=[=($cor0.SAL, 1000)])\n" |
| + " LogicalFilter(condition=[OR(" |
| + "SEARCH($cor0.DEPTNO, Sarg[(20..30)]), " |
| + "IS NULL($2))], variablesSet=[[$cor0]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testFilterEmpty() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| // We intend to call |
| // filter(Iterable<CorrelationId>, RexNode...) |
| // with zero varargs, not |
| // filter(Iterable<RexNode>) |
| // Let's hope they're distinct after type erasure. |
| .filter(ImmutableSet.<CorrelationId>of()) |
| .build(); |
| assertThat(root, hasTree("LogicalTableScan(table=[[scott, EMP]])\n")); |
| } |
| |
| /** Checks if simplification is run in |
| * {@link org.apache.calcite.rex.RexUnknownAs#FALSE} mode for filter |
| * conditions. */ |
| @Test void testFilterSimplification() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.or( |
| builder.literal(null), |
| builder.and( |
| builder.equals(builder.field(2), builder.literal(1)), |
| builder.equals(builder.field(2), builder.literal(2)) |
| ))) |
| .build(); |
| assertThat(root, hasTree("LogicalValues(tuples=[[]])\n")); |
| } |
| |
| @Test void testFilterWithoutSimplification() { |
| final RelBuilder builder = createBuilder(c -> c.withSimplify(false)); |
| final RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.or( |
| builder.literal(null), |
| builder.and( |
| builder.equals(builder.field(2), builder.literal(1)), |
| builder.equals(builder.field(2), builder.literal(2)) |
| ))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[OR(null:NULL, AND(=($2, 1), =($2, 2)))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testRelBuilderToString() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| builder.scan("EMP"); |
| |
| // One entry on the stack, a single-node tree |
| final String expected1 = "LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(Util.toLinux(builder.toString()), is(expected1)); |
| |
| // One entry on the stack, a two-node tree |
| builder.filter(builder.equals(builder.field(2), builder.literal(3))); |
| final String expected2 = "LogicalFilter(condition=[=($2, 3)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(Util.toLinux(builder.toString()), is(expected2)); |
| |
| // Two entries on the stack |
| builder.scan("DEPT"); |
| final String expected3 = "LogicalTableScan(table=[[scott, DEPT]])\n" |
| + "LogicalFilter(condition=[=($2, 3)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(Util.toLinux(builder.toString()), is(expected3)); |
| } |
| |
| /** |
| * Ensures that relational algebra ({@link RelBuilder}) works with SQL views. |
| * |
| * <p>This test currently fails (thus ignored). |
| */ |
| @Test void testExpandViewInRelBuilder() throws SQLException { |
| try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) { |
| final Frameworks.ConfigBuilder configBuilder = |
| expandingConfig(connection); |
| final RelOptTable.ViewExpander viewExpander = |
| (RelOptTable.ViewExpander) Frameworks.getPlanner(configBuilder.build()); |
| configBuilder.context(Contexts.of(viewExpander)); |
| final RelBuilder builder = RelBuilder.create(configBuilder.build()); |
| RelNode node = builder.scan("MYVIEW").build(); |
| |
| int count = 0; |
| try (PreparedStatement statement = |
| connection.unwrap(RelRunner.class).prepareStatement(node); |
| ResultSet resultSet = statement.executeQuery()) { |
| while (resultSet.next()) { |
| count++; |
| } |
| } |
| |
| assertTrue(count > 1); |
| } |
| } |
| |
| @Test void testExpandViewShouldKeepAlias() throws SQLException { |
| try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) { |
| final Frameworks.ConfigBuilder configBuilder = |
| expandingConfig(connection); |
| final RelOptTable.ViewExpander viewExpander = |
| (RelOptTable.ViewExpander) Frameworks.getPlanner(configBuilder.build()); |
| configBuilder.context(Contexts.of(viewExpander)); |
| final RelBuilder builder = RelBuilder.create(configBuilder.build()); |
| RelNode node = |
| builder.scan("MYVIEW") |
| .project( |
| builder.field(1, "MYVIEW", "EMPNO"), |
| builder.field(1, "MYVIEW", "ENAME")) |
| .build(); |
| String expected = |
| "LogicalProject(EMPNO=[$0], ENAME=[$1])\n" |
| + " LogicalFilter(condition=[=(1, 1)])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(node, hasTree(expected)); |
| } |
| } |
| |
| @Test void testExpandTable() throws SQLException { |
| try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) { |
| // RelBuilder expands as default. Plan contains JdbcTableScan, |
| // because RelBuilder.scan has called RelOptTable.toRel. |
| final Frameworks.ConfigBuilder configBuilder = |
| expandingConfig(connection); |
| final RelBuilder builder = RelBuilder.create(configBuilder.build()); |
| final String expected = "LogicalFilter(condition=[>($2, 10)])\n" |
| + " JdbcTableScan(table=[[JDBC_SCOTT, EMP]])\n"; |
| checkExpandTable(builder, hasTree(expected)); |
| } |
| } |
| |
| private void checkExpandTable(RelBuilder builder, Matcher<RelNode> matcher) { |
| final RelNode root = |
| builder.scan("JDBC_SCOTT", "EMP") |
| .filter( |
| builder.greaterThan(builder.field(2), builder.literal(10))) |
| .build(); |
| assertThat(root, matcher); |
| } |
| |
| @Test void testExchange() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = builder.scan("EMP") |
| .exchange(RelDistributions.hash(Lists.newArrayList(0))) |
| .build(); |
| final String expected = |
| "LogicalExchange(distribution=[hash[0]])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testSortExchange() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder.scan("EMP") |
| .sortExchange(RelDistributions.hash(Lists.newArrayList(0)), |
| RelCollations.of(0)) |
| .build(); |
| final String expected = |
| "LogicalSortExchange(distribution=[hash[0]], collation=[[0]])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testCorrelate() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final Holder<@Nullable RexCorrelVariable> v = Holder.empty(); |
| RelNode root = builder.scan("EMP") |
| .variable(v) |
| .scan("DEPT") |
| .filter( |
| builder.equals(builder.field(0), |
| builder.field(v.get(), "DEPTNO"))) |
| .correlate(JoinRelType.LEFT, v.get().id, builder.field(2, 0, "DEPTNO")) |
| .build(); |
| |
| final String expected = "" |
| + "LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{7}])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalFilter(condition=[=($0, $cor0.DEPTNO)])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testSimpleSemiCorrelateViaJoin() { |
| RelNode root = buildSimpleCorrelateWithJoin(JoinRelType.SEMI); |
| final String expected = "" |
| + "LogicalJoin(condition=[=($7, $8)], joinType=[semi])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat( |
| "Join with correlate id but the id never used should be simplified to a join.", |
| root, hasTree(expected)); |
| } |
| |
| @Test void testSemiCorrelatedViaJoin() { |
| RelNode root = buildCorrelateWithJoin(JoinRelType.SEMI); |
| final String expected = "" |
| + "LogicalCorrelate(correlation=[$cor0], joinType=[semi], requiredColumns=[{0, 7}])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalFilter(condition=[=($cor0.DEPTNO, $0)])\n" |
| + " LogicalFilter(condition=[=($cor0.EMPNO, 'NaN')])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat( |
| "Correlated semi joins should emmit a correlate with a filter on the right side.", |
| root, hasTree(expected)); |
| } |
| |
| @Test void testSimpleAntiCorrelateViaJoin() { |
| RelNode root = buildSimpleCorrelateWithJoin(JoinRelType.ANTI); |
| final String expected = "" |
| + "LogicalJoin(condition=[=($7, $8)], joinType=[anti])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat( |
| "Join with correlate id but the id never used should be simplified to a join.", |
| root, hasTree(expected)); |
| } |
| |
| @Test void testAntiCorrelateViaJoin() { |
| RelNode root = buildCorrelateWithJoin(JoinRelType.ANTI); |
| final String expected = "" |
| + "LogicalCorrelate(correlation=[$cor0], joinType=[anti], requiredColumns=[{0, 7}])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalFilter(condition=[=($cor0.DEPTNO, $0)])\n" |
| + " LogicalFilter(condition=[=($cor0.EMPNO, 'NaN')])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat( |
| "Correlated anti joins should emmit a correlate with a filter on the right side.", |
| root, hasTree(expected)); } |
| |
| @Test void testSimpleLeftCorrelateViaJoin() { |
| RelNode root = buildSimpleCorrelateWithJoin(JoinRelType.LEFT); |
| final String expected = "" |
| + "LogicalJoin(condition=[=($7, $8)], joinType=[left])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat( |
| "Join with correlate id but the id never used should be simplified to a join.", |
| root, hasTree(expected)); |
| } |
| |
| @Test void testLeftCorrelateViaJoin() { |
| RelNode root = buildCorrelateWithJoin(JoinRelType.LEFT); |
| final String expected = "" |
| + "LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0, 7}])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalFilter(condition=[=($cor0.DEPTNO, $0)])\n" |
| + " LogicalFilter(condition=[=($cor0.EMPNO, 'NaN')])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat( |
| "Correlated left joins should emmit a correlate with a filter on the right side.", |
| root, hasTree(expected)); |
| } |
| |
| @Test void testSimpleInnerCorrelateViaJoin() { |
| RelNode root = buildSimpleCorrelateWithJoin(JoinRelType.INNER); |
| final String expected = "" |
| + "LogicalJoin(condition=[=($7, $8)], joinType=[inner])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat("Join with correlate id but never used should be simplified to a join.", |
| root, hasTree(expected)); |
| } |
| |
| @Test void testInnerCorrelateViaJoin() { |
| RelNode root = buildCorrelateWithJoin(JoinRelType.INNER); |
| final String expected = "" |
| + "LogicalFilter(condition=[=($7, $8)])\n" |
| + " LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalFilter(condition=[=($cor0.EMPNO, 'NaN')])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat( |
| "Correlated inner joins should emmit a correlate with a filter on top.", |
| root, hasTree(expected)); |
| } |
| |
| @Test void testSimpleRightCorrelateViaJoinThrowsException() { |
| assertThrows(IllegalArgumentException.class, |
| () -> buildSimpleCorrelateWithJoin(JoinRelType.RIGHT), |
| "Right outer joins with correlated ids are invalid even if id is not used."); |
| } |
| |
| @Test void testSimpleFullCorrelateViaJoinThrowsException() { |
| assertThrows(IllegalArgumentException.class, |
| () -> buildSimpleCorrelateWithJoin(JoinRelType.FULL), |
| "Full outer joins with correlated ids are invalid even if id is not used."); |
| } |
| |
| @Test void testRightCorrelateViaJoinThrowsException() { |
| assertThrows(IllegalArgumentException.class, |
| () -> buildCorrelateWithJoin(JoinRelType.RIGHT), |
| "Right outer joins with correlated ids are invalid."); |
| } |
| |
| @Test void testFullCorrelateViaJoinThrowsException() { |
| assertThrows(IllegalArgumentException.class, |
| () -> buildCorrelateWithJoin(JoinRelType.FULL), |
| "Full outer joins with correlated ids are invalid."); |
| } |
| |
| private static RelNode buildSimpleCorrelateWithJoin(JoinRelType type) { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final Holder<@Nullable RexCorrelVariable> v = Holder.empty(); |
| return builder |
| .scan("EMP") |
| .variable(v) |
| .scan("DEPT") |
| .join(type, |
| builder.equals( |
| builder.field(2, 0, "DEPTNO"), |
| builder.field(2, 1, "DEPTNO")), ImmutableSet.of(v.get().id)) |
| .build(); |
| } |
| |
| private static RelNode buildCorrelateWithJoin(JoinRelType type) { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RexBuilder rexBuilder = builder.getRexBuilder(); |
| final Holder<@Nullable RexCorrelVariable> v = Holder.empty(); |
| return builder |
| .scan("EMP") |
| .variable(v) |
| .scan("DEPT") |
| .filter( |
| builder.equals( |
| rexBuilder.makeFieldAccess(v.get(), 0), |
| builder.literal("NaN"))) |
| .join(type, |
| builder.equals( |
| builder.field(2, 0, "DEPTNO"), |
| builder.field(2, 1, "DEPTNO")), ImmutableSet.of(v.get().id)) |
| .build(); |
| } |
| |
| @Test void testCorrelateWithComplexFields() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final Holder<@Nullable RexCorrelVariable> v = Holder.empty(); |
| RelNode root = builder.scan("EMP") |
| .variable(v) |
| .scan("DEPT") |
| .filter( |
| builder.equals(builder.field(0), |
| builder.field(v.get(), "DEPTNO"))) |
| .correlate(JoinRelType.LEFT, v.get().id, |
| builder.field(2, 0, "DEPTNO"), |
| builder.getRexBuilder().makeCall(SqlStdOperatorTable.AS, |
| builder.field(2, 0, "EMPNO"), |
| builder.literal("RENAMED_EMPNO"))) |
| .build(); |
| |
| final String expected = "" |
| + "LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0, 7}])\n" |
| + " LogicalProject(RENAMED_EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n" |
| + " LogicalFilter(condition=[=($0, $cor0.DEPTNO)])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testAdoptConventionEnumerable() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = builder |
| .adoptConvention(EnumerableConvention.INSTANCE) |
| .scan("DEPT") |
| .filter( |
| builder.equals(builder.field("DEPTNO"), builder.literal(20))) |
| .sort(builder.field(2), builder.desc(builder.field(0))) |
| .project(builder.field(0)) |
| .build(); |
| String expected = "" |
| + "EnumerableProject(DEPTNO=[$0])\n" |
| + " EnumerableSort(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[DESC])\n" |
| + " EnumerableFilter(condition=[=($0, 20)])\n" |
| + " EnumerableTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testSwitchConventions() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = builder |
| .scan("DEPT") |
| .adoptConvention(EnumerableConvention.INSTANCE) |
| .filter( |
| builder.equals(builder.field("DEPTNO"), builder.literal(20))) |
| .sort(builder.field(2), builder.desc(builder.field(0))) |
| .adoptConvention(Convention.NONE) |
| .project(builder.field(0)) |
| .build(); |
| String expected = "" |
| + "LogicalProject(DEPTNO=[$0])\n" |
| + " EnumerableSort(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[DESC])\n" |
| + " EnumerableFilter(condition=[=($0, 20)])\n" |
| + " LogicalTableScan(table=[[scott, DEPT]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testHints() { |
| final RelHint indexHint = RelHint.builder("INDEX") |
| .hintOption("_idx1") |
| .hintOption("_idx2") |
| .build(); |
| final RelHint propsHint = RelHint.builder("PROPERTIES") |
| .inheritPath(0) |
| .hintOption("parallelism", "3") |
| .hintOption("mem", "20Mb") |
| .build(); |
| final RelHint noHashJoinHint = RelHint.builder("NO_HASH_JOIN") |
| .inheritPath(0) |
| .build(); |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp /*+ INDEX(_idx1, _idx2) */ |
| final RelNode root = builder |
| .scan("EMP") |
| .hints(indexHint) |
| .build(); |
| assertThat(root, |
| hasHints("[[INDEX inheritPath:[] options:[_idx1, _idx2]]]")); |
| // Equivalent SQL: |
| // SELECT /*+ PROPERTIES(parallelism='3', mem='20Mb') */ |
| // * |
| // FROM emp /*+ INDEX(_idx1, _idx2) */ |
| final RelNode root1 = builder |
| .scan("EMP") |
| .hints(indexHint, propsHint) |
| .build(); |
| assertThat(root1, |
| hasHints("[[INDEX inheritPath:[] options:[_idx1, _idx2]], " |
| + "[PROPERTIES inheritPath:[0] options:{parallelism=3, mem=20Mb}]]")); |
| // Equivalent SQL: |
| // SELECT /*+ NO_HASH_JOIN */ |
| // * |
| // FROM emp |
| // join dept |
| // on emp.deptno = dept.deptno |
| final RelNode root2 = builder |
| .scan("EMP") |
| .scan("DEPT") |
| .join(JoinRelType.INNER, |
| builder.equals( |
| builder.field(2, 0, "DEPTNO"), |
| builder.field(2, 1, "DEPTNO"))) |
| .hints(noHashJoinHint) |
| .build(); |
| assertThat(root2, hasHints("[[NO_HASH_JOIN inheritPath:[0]]]")); |
| } |
| |
| @Test void testHintsOnEmptyStack() { |
| final RelHint indexHint = RelHint.builder("INDEX") |
| .hintOption("_idx1") |
| .hintOption("_idx2") |
| .build(); |
| // Attach hints on empty stack. |
| final AssertionError error = assertThrows( |
| AssertionError.class, |
| () -> RelBuilder.create(config().build()).hints(indexHint), |
| "hints() should fail on empty stack"); |
| assertThat(error.getMessage(), |
| containsString("There is no relational expression to attach the hints")); |
| } |
| |
| @Test void testHintsOnNonHintable() { |
| final RelHint indexHint = RelHint.builder("INDEX") |
| .hintOption("_idx1") |
| .hintOption("_idx2") |
| .build(); |
| // Attach hints on non hintable. |
| final AssertionError error1 = assertThrows( |
| AssertionError.class, |
| () -> { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| // Equivalent SQL: |
| // SELECT * |
| // FROM emp |
| // WHERE EMPNO = 124 |
| builder |
| .scan("EMP") |
| .filter( |
| builder.equals( |
| builder.field("EMPNO"), |
| builder.literal(124))) |
| .hints(indexHint); |
| }, |
| "hints() should fail on non Hintable relational expression"); |
| assertThat(error1.getMessage(), |
| containsString("The top relational expression is not a Hintable")); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3747">[CALCITE-3747] |
| * Constructing BETWEEN with RelBuilder throws class cast exception</a>. |
| * |
| * <p>BETWEEN is no longer allowed in RexCall. 'a BETWEEN b AND c' is expanded |
| * 'a >= b AND a <= c', whether created via |
| * {@link RelBuilder#call(SqlOperator, RexNode...)} or |
| * {@link RelBuilder#between(RexNode, RexNode, RexNode)}.*/ |
| @Test void testCallBetweenOperator() { |
| final RelBuilder builder = RelBuilder.create(config().build()).scan("EMP"); |
| |
| final String expected = "SEARCH($0, Sarg[[1..5]])"; |
| final RexNode call = |
| builder.call(SqlStdOperatorTable.BETWEEN, |
| builder.field("EMPNO"), |
| builder.literal(1), |
| builder.literal(5)); |
| assertThat(call.toString(), is(expected)); |
| |
| final RexNode call2 = |
| builder.between(builder.field("EMPNO"), |
| builder.literal(1), |
| builder.literal(5)); |
| assertThat(call2.toString(), is(expected)); |
| |
| final RelNode root = builder.filter(call2).build(); |
| final String expectedRel = "" |
| + "LogicalFilter(condition=[SEARCH($0, Sarg[[1..5]])])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expectedRel)); |
| |
| // Consecutive filters are not merged. (For now, anyway.) |
| builder.push(root) |
| .filter( |
| builder.not( |
| builder.equals(builder.field("EMPNO"), builder.literal(3))), |
| builder.equals(builder.field("DEPTNO"), builder.literal(10))); |
| final RelNode root2 = builder.build(); |
| final String expectedRel2 = "" |
| + "LogicalFilter(condition=[AND(<>($0, 3), =($7, 10))])\n" |
| + " LogicalFilter(condition=[SEARCH($0, Sarg[[1..5]])])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root2, hasTree(expectedRel2)); |
| |
| // The conditions in one filter are simplified. |
| builder.scan("EMP") |
| .filter( |
| builder.between(builder.field("EMPNO"), |
| builder.literal(1), |
| builder.literal(5)), |
| builder.not( |
| builder.equals(builder.field("EMPNO"), builder.literal(3))), |
| builder.equals(builder.field("DEPTNO"), builder.literal(10))); |
| final RelNode root3 = builder.build(); |
| final String expectedRel3 = "" |
| + "LogicalFilter(condition=[AND(SEARCH($0, Sarg[[1..3), (3..5]]), =($7, 10))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root3, hasTree(expectedRel3)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-3926">[CALCITE-3926] |
| * CannotPlanException when an empty LogicalValues requires a certain collation</a>. */ |
| @Test void testEmptyValuesWithCollation() throws Exception { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| final RelNode root = |
| builder |
| .scan("DEPT").empty() |
| .sort( |
| builder.field("DNAME"), |
| builder.field("DEPTNO")) |
| .build(); |
| try (PreparedStatement preparedStatement = RelRunners.run(root)) { |
| final String result = CalciteAssert.toString(preparedStatement.executeQuery()); |
| final String expectedResult = ""; |
| assertThat(result, is(expectedResult)); |
| } |
| } |
| |
| /** Tests {@link RelBuilder#isDistinctFrom} and |
| * {@link RelBuilder#isNotDistinctFrom}. */ |
| @Test void testIsDistinctFrom() { |
| final Function<RelBuilder, RelNode> f = b -> b.scan("EMP") |
| .project(b.field("DEPTNO"), |
| b.isNotDistinctFrom(b.field("SAL"), b.field("DEPTNO")), |
| b.isNotDistinctFrom(b.field("EMPNO"), b.field("DEPTNO")), |
| b.isDistinctFrom(b.field("EMPNO"), b.field("DEPTNO"))) |
| .build(); |
| // Note: skip IS NULL check when both fields are NOT NULL; |
| // enclose in IS TRUE or IS NOT TRUE so that the result is BOOLEAN NOT NULL. |
| final String expected = "" |
| + "LogicalProject(DEPTNO=[$7], " |
| + "$f1=[OR(AND(IS NULL($5), IS NULL($7)), IS TRUE(=($5, $7)))], " |
| + "$f2=[IS TRUE(=($0, $7))], " |
| + "$f3=[IS NOT TRUE(=($0, $7))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(f.apply(createBuilder()), hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-4415">[CALCITE-4415] |
| * SqlStdOperatorTable.NOT_LIKE has a wrong implementor</a>. */ |
| @Test void testNotLike() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.call(SqlStdOperatorTable.NOT_LIKE, |
| builder.field("ENAME"), |
| builder.literal("a%b%c"))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[NOT(LIKE($1, 'a%b%c'))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| @Test void testNotIlike() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.call(SqlLibraryOperators.NOT_ILIKE, |
| builder.field("ENAME"), |
| builder.literal("a%b%c"))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[NOT(ILIKE($1, 'a%b%c'))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-4415">[CALCITE-4415] |
| * SqlStdOperatorTable.NOT_LIKE has a wrong implementor</a>. */ |
| @Test void testNotSimilarTo() { |
| final RelBuilder builder = RelBuilder.create(config().build()); |
| RelNode root = |
| builder.scan("EMP") |
| .filter( |
| builder.call( |
| SqlStdOperatorTable.NOT_SIMILAR_TO, |
| builder.field("ENAME"), |
| builder.literal("a%b%c"))) |
| .build(); |
| final String expected = "" |
| + "LogicalFilter(condition=[NOT(SIMILAR TO($1, 'a%b%c'))])\n" |
| + " LogicalTableScan(table=[[scott, EMP]])\n"; |
| assertThat(root, hasTree(expected)); |
| } |
| |
| /** Test case for |
| * <a href="https://issues.apache.org/jira/browse/CALCITE-4415">[CALCITE-4415] |
| * SqlStdOperatorTable.NOT_LIKE has a wrong implementor</a>. */ |
| @Test void testExecuteNotLike() { |
| CalciteAssert.that() |
| .withSchema("s", new ReflectiveSchema(new HrSchema())) |
| .query("?") |
| .withRel( |
| builder -> builder |
| .scan("s", "emps") |
| .filter( |
| builder.call( |
| SqlStdOperatorTable.NOT_LIKE, |
| builder.field("name"), |
| builder.literal("%r%c"))) |
| .project( |
| builder.field("empid"), |
| builder.field("name")) |
| .build()) |
| .returnsUnordered( |
| "empid=100; name=Bill", |
| "empid=110; name=Theodore", |
| "empid=150; name=Sebastian"); |
| } |
| |
| /** Operand to a user-defined function. */ |
| private interface Arg { |
| String name(); |
| RelDataType type(RelDataTypeFactory typeFactory); |
| SqlTypeFamily family(); |
| boolean optional(); |
| |
| static SqlOperandMetadata metadata(Arg... args) { |
| return OperandTypes.operandMetadata( |
| Arrays.stream(args).map(Arg::family).collect(Collectors.toList()), |
| typeFactory -> |
| Arrays.stream(args).map(arg -> arg.type(typeFactory)) |
| .collect(Collectors.toList()), |
| i -> args[i].name(), i -> args[i].optional()); |
| } |
| |
| static Arg of(String name, |
| Function<RelDataTypeFactory, RelDataType> protoType, |
| SqlTypeFamily family, boolean optional) { |
| return new Arg() { |
| @Override public String name() { |
| return name; |
| } |
| |
| @Override public RelDataType type(RelDataTypeFactory typeFactory) { |
| return protoType.apply(typeFactory); |
| } |
| |
| @Override public SqlTypeFamily family() { |
| return family; |
| } |
| |
| @Override public boolean optional() { |
| return optional; |
| } |
| }; |
| } |
| } |
| } |
