org.apache.calcite.sql.fun.SqlStdOperatorTable Java Examples

/** 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));
}
 

@Override
public Boolean visitCall(RexCall call) {
  if (call.getOperator() == SqlStdOperatorTable.ITEM) {
    final RexNode op0 = call.getOperands().get(0);
    final RexNode op1 = call.getOperands().get(1);

    if (op0 instanceof RexInputRef &&
        op1 instanceof RexLiteral && ((RexLiteral) op1).getTypeName() == SqlTypeName.CHAR) {
      return true;
    } else if (op0 instanceof RexCall &&
        op1 instanceof RexLiteral && ((RexLiteral) op1).getTypeName() == SqlTypeName.CHAR) {
      return op0.accept(this);
    }
  }

  return false;
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-1769">[CALCITE-1769]
 * Druid adapter: Push down filters involving numeric cast of literals</a>. */
@Test void testPushCastNumeric() {
  String druidQuery = "'filter':{'type':'bound','dimension':'product_id',"
      + "'upper':'10','upperStrict':true,'ordering':'numeric'}";
  sql("?")
      .withRel(b -> {
        // select product_id
        // from foodmart.foodmart
        // where product_id < cast(10 as varchar)
        final RelDataType intType =
            b.getTypeFactory().createSqlType(SqlTypeName.INTEGER);
        return b.scan("foodmart", "foodmart")
            .filter(
                b.call(SqlStdOperatorTable.LESS_THAN,
                    b.getRexBuilder().makeCall(intType,
                        SqlStdOperatorTable.CAST,
                        ImmutableList.of(b.field("product_id"))),
                    b.getRexBuilder().makeCall(intType,
                        SqlStdOperatorTable.CAST,
                        ImmutableList.of(b.literal("10")))))
            .project(b.field("product_id"))
            .build();
      })
      .queryContains(new DruidChecker(druidQuery));
}
 

protected SqlNode expandDynamicStar(SqlIdentifier id, SqlIdentifier fqId) {
	if (DynamicRecordType.isDynamicStarColName(Util.last(fqId.names))
		&& !DynamicRecordType.isDynamicStarColName(Util.last(id.names))) {
		// Convert a column ref into ITEM(*, 'col_name')
		// for a dynamic star field in dynTable's rowType.
		SqlNode[] inputs = new SqlNode[2];
		inputs[0] = fqId;
		inputs[1] = SqlLiteral.createCharString(
			Util.last(id.names),
			id.getParserPosition());
		return new SqlBasicCall(
			SqlStdOperatorTable.ITEM,
			inputs,
			id.getParserPosition());
	}
	return fqId;
}
 

AggCallImpl(SqlAggFunction aggFunction, boolean distinct,
    boolean approximate, boolean ignoreNulls, RexNode filter,
    String alias, ImmutableList<RexNode> operands,
    ImmutableList<RexNode> orderKeys) {
  this.aggFunction = Objects.requireNonNull(aggFunction);
  // If the aggregate function ignores DISTINCT,
  // make the DISTINCT flag FALSE.
  this.distinct = distinct
      && aggFunction.getDistinctOptionality() != Optionality.IGNORED;
  this.approximate = approximate;
  this.ignoreNulls = ignoreNulls;
  this.alias = alias;
  this.operands = Objects.requireNonNull(operands);
  this.orderKeys = Objects.requireNonNull(orderKeys);
  if (filter != null) {
    if (filter.getType().getSqlTypeName() != SqlTypeName.BOOLEAN) {
      throw RESOURCE.filterMustBeBoolean().ex();
    }
    if (filter.getType().isNullable()) {
      filter = call(SqlStdOperatorTable.IS_TRUE, filter);
    }
  }
  this.filter = filter;
}
 

/**
 * Computes the number of distinct rows for a set of keys returned from a
 * semi-join
 *
 * @param semiJoinRel RelNode representing the semi-join
 * @param mq          metadata query
 * @param groupKey    keys that the distinct row count will be computed for
 * @param predicate   join predicate
 * @return number of distinct rows
 */
public static Double getSemiJoinDistinctRowCount(Join semiJoinRel, RelMetadataQuery mq,
    ImmutableBitSet groupKey, RexNode predicate) {
  if (predicate == null || predicate.isAlwaysTrue()) {
    if (groupKey.isEmpty()) {
      return 1D;
    }
  }
  // create a RexNode representing the selectivity of the
  // semijoin filter and pass it to getDistinctRowCount
  RexNode newPred = RelMdUtil.makeSemiJoinSelectivityRexNode(mq, semiJoinRel);
  if (predicate != null) {
    RexBuilder rexBuilder = semiJoinRel.getCluster().getRexBuilder();
    newPred =
        rexBuilder.makeCall(
            SqlStdOperatorTable.AND,
            newPred,
            predicate);
  }

  return mq.getDistinctRowCount(semiJoinRel.getLeft(), groupKey, newPred);
}
 

private RexNode getSumAggregatedRexNodeWithBinding(Aggregate oldAggRel,
    AggregateCall oldCall,
    List<AggregateCall> newCalls,
    Map<AggregateCall, RexNode> aggCallMapping,
    RelDataType operandType,
    int argOrdinal,
    int filter) {
  RelOptCluster cluster = oldAggRel.getCluster();
  final AggregateCall sumArgSquaredAggCall =
      createAggregateCallWithBinding(cluster.getTypeFactory(),
          SqlStdOperatorTable.SUM, operandType, oldAggRel, oldCall, argOrdinal, filter);

  return cluster.getRexBuilder().addAggCall(sumArgSquaredAggCall,
      oldAggRel.getGroupCount(),
      oldAggRel.indicator,
      newCalls,
      aggCallMapping,
      ImmutableList.of(sumArgSquaredAggCall.getType()));
}
 

private RexNode getSumAggregatedRexNodeWithBinding(Aggregate oldAggRel,
    AggregateCall oldCall,
    List<AggregateCall> newCalls,
    Map<AggregateCall, RexNode> aggCallMapping,
    RelDataType operandType,
    int argOrdinal,
    int filter) {
  RelOptCluster cluster = oldAggRel.getCluster();
  final AggregateCall sumArgSquaredAggCall =
      createAggregateCallWithBinding(cluster.getTypeFactory(),
          SqlStdOperatorTable.SUM, operandType, oldAggRel, oldCall, argOrdinal, filter);

  return cluster.getRexBuilder().addAggCall(sumArgSquaredAggCall,
      oldAggRel.getGroupCount(),
      newCalls,
      aggCallMapping,
      ImmutableList.of(sumArgSquaredAggCall.getType()));
}
 

public SqlNode rewriteCall(SqlValidator validator, SqlCall call) {
  // for now, rewrite "CALL f(x)" to "SELECT f(x) FROM VALUES(0)"
  // TODO jvs 18-Jan-2005:  rewrite to SELECT * FROM TABLE f(x)
  // once we support function calls as tables
  return new SqlSelect(SqlParserPos.ZERO,
      null,
      new SqlNodeList(
          Collections.singletonList(call.operand(0)),
          SqlParserPos.ZERO),
      SqlStdOperatorTable.VALUES.createCall(
          SqlParserPos.ZERO,
          SqlStdOperatorTable.ROW.createCall(
              SqlParserPos.ZERO,
              SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO))),
      null,
      null,
      null,
      null,
      null,
      null,
      null,
      null);
}
 

@Override public void unparseCall(SqlWriter writer, SqlCall call,
    int leftPrec, int rightPrec) {
  if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) {
    if (call.operandCount() != 3) {
      throw new IllegalArgumentException("MSSQL SUBSTRING requires FROM and FOR arguments");
    }
    SqlUtil.unparseFunctionSyntax(MSSQL_SUBSTRING, writer, call);
  } else {
    switch (call.getKind()) {
    case FLOOR:
      if (call.operandCount() != 2) {
        super.unparseCall(writer, call, leftPrec, rightPrec);
        return;
      }
      unparseFloor(writer, call);
      break;

    default:
      super.unparseCall(writer, call, leftPrec, rightPrec);
    }
  }
}
 

void andOrList(SqlWriter writer, SqlKind sepKind) {
  SqlBinaryOperator sepOp =
      sepKind == SqlKind.AND
          ? SqlStdOperatorTable.AND
          : SqlStdOperatorTable.OR;
  for (int i = 0; i < list.size(); i++) {
    SqlNode node = list.get(i);
    writer.sep(sepKind.name(), false);

    // The precedence pulling on the LHS of a node is the
    // right-precedence of the separator operator, except at the start
    // of the list; similarly for the RHS of a node. If the operator
    // has left precedence 4 and right precedence 5, the precedences
    // in a 3-node list will look as follows:
    //   0 <- node1 -> 4  5 <- node2 -> 4  5 <- node3 -> 0
    int lprec = (i == 0) ? 0 : sepOp.getRightPrec();
    int rprec = (i == (list.size() - 1)) ? 0 : sepOp.getLeftPrec();
    node.unparse(writer, lprec, rprec);
  }
}
 

@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.call(SqlStdOperatorTable.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));
}
 

private static RexNode isDistinctFromInternal(RexBuilder rexBuilder, RexNode x, RexNode y, boolean neg) {

        if (neg) {
            // x is not distinct from y
            // x=y IS TRUE or ((x is null) and (y is null)),
            return rexBuilder.makeCall(SqlStdOperatorTable.OR,
                    rexBuilder.makeCall(SqlStdOperatorTable.AND, rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, x),
                            rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, y)),
                    rexBuilder.makeCall(SqlStdOperatorTable.IS_TRUE,
                            rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, x, y)));
        } else {
            // x is distinct from y
            // x=y IS NOT TRUE and ((x is not null) or (y is not null)),
            return rexBuilder.makeCall(SqlStdOperatorTable.AND,
                    rexBuilder.makeCall(SqlStdOperatorTable.OR, rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, x),
                            rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, y)),
                    rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_TRUE,
                            rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, x, y)));
        }
    }
 

public static SqlOperator op(SqlKind kind) {
  switch (kind) {
  case IS_FALSE:
    return SqlStdOperatorTable.IS_FALSE;
  case IS_TRUE:
    return SqlStdOperatorTable.IS_TRUE;
  case IS_UNKNOWN:
    return SqlStdOperatorTable.IS_UNKNOWN;
  case IS_NULL:
    return SqlStdOperatorTable.IS_NULL;
  case IS_NOT_FALSE:
    return SqlStdOperatorTable.IS_NOT_FALSE;
  case IS_NOT_TRUE:
    return SqlStdOperatorTable.IS_NOT_TRUE;
  case IS_NOT_NULL:
    return SqlStdOperatorTable.IS_NOT_NULL;
  case EQUALS:
    return SqlStdOperatorTable.EQUALS;
  case NOT_EQUALS:
    return SqlStdOperatorTable.NOT_EQUALS;
  case LESS_THAN:
    return SqlStdOperatorTable.LESS_THAN;
  case GREATER_THAN:
    return SqlStdOperatorTable.GREATER_THAN;
  case LESS_THAN_OR_EQUAL:
    return SqlStdOperatorTable.LESS_THAN_OR_EQUAL;
  case GREATER_THAN_OR_EQUAL:
    return SqlStdOperatorTable.GREATER_THAN_OR_EQUAL;
  default:
    throw new AssertionError(kind);
  }
}
 

private RexNode convertPlus(SqlRexContext cx, SqlCall call) {
  final RexNode rex = convertCall(cx, call);
  switch (rex.getType().getSqlTypeName()) {
  case DATE:
  case TIME:
  case TIMESTAMP:
    // Use special "+" operator for datetime + interval.
    // Re-order operands, if necessary, so that interval is second.
    final RexBuilder rexBuilder = cx.getRexBuilder();
    List<RexNode> operands = ((RexCall) rex).getOperands();
    if (operands.size() == 2) {
      final SqlTypeName sqlTypeName = operands.get(0).getType().getSqlTypeName();
      switch (sqlTypeName) {
      case INTERVAL_YEAR:
      case INTERVAL_YEAR_MONTH:
      case INTERVAL_MONTH:
      case INTERVAL_DAY:
      case INTERVAL_DAY_HOUR:
      case INTERVAL_DAY_MINUTE:
      case INTERVAL_DAY_SECOND:
      case INTERVAL_HOUR:
      case INTERVAL_HOUR_MINUTE:
      case INTERVAL_HOUR_SECOND:
      case INTERVAL_MINUTE:
      case INTERVAL_MINUTE_SECOND:
      case INTERVAL_SECOND:
        operands = ImmutableList.of(operands.get(1), operands.get(0));
      }
    }
    return rexBuilder.makeCall(rex.getType(),
        SqlStdOperatorTable.DATETIME_PLUS, operands);
  default:
    return rex;
  }
}
 

/**
 * Converts a BETWEEN expression.
 *
 * <p>Called automatically via reflection.
 */
public RexNode convertBetween(
    SqlRexContext cx,
    SqlBetweenOperator op,
    SqlCall call) {
  final List<RexNode> list =
      convertExpressionList(cx, call.getOperandList(),
          op.getOperandTypeChecker().getConsistency());
  final RexNode x = list.get(SqlBetweenOperator.VALUE_OPERAND);
  final RexNode y = list.get(SqlBetweenOperator.LOWER_OPERAND);
  final RexNode z = list.get(SqlBetweenOperator.UPPER_OPERAND);

  final RexBuilder rexBuilder = cx.getRexBuilder();
  RexNode ge1 = ge(rexBuilder, x, y);
  RexNode le1 = le(rexBuilder, x, z);
  RexNode and1 = and(rexBuilder, ge1, le1);

  RexNode res;
  final SqlBetweenOperator.Flag symmetric = op.flag;
  switch (symmetric) {
  case ASYMMETRIC:
    res = and1;
    break;
  case SYMMETRIC:
    RexNode ge2 = ge(rexBuilder, x, z);
    RexNode le2 = le(rexBuilder, x, y);
    RexNode and2 = and(rexBuilder, ge2, le2);
    res = or(rexBuilder, and1, and2);
    break;
  default:
    throw Util.unexpected(symmetric);
  }
  final SqlBetweenOperator betweenOp =
      (SqlBetweenOperator) call.getOperator();
  if (betweenOp.isNegated()) {
    res = rexBuilder.makeCall(SqlStdOperatorTable.NOT, res);
  }
  return res;
}
 

private SqlNode getCastedSqlNode(SqlNode argInput, RelDataType varType,
    SqlParserPos pos, RexNode argRex) {
  SqlNode arg;
  if (argRex != null && !argRex.getType().equals(varType)) {
    arg = SqlStdOperatorTable.CAST.createCall(
        pos, argInput, SqlTypeUtil.convertTypeToSpec(varType));
  } else {
    arg = argInput;
  }
  return arg;
}
 

public RexNode convertCall(SqlRexContext cx, SqlCall call) {
  // TIMESTAMPADD(unit, count, timestamp)
  //  => timestamp + count * INTERVAL '1' UNIT
  final RexBuilder rexBuilder = cx.getRexBuilder();
  final SqlLiteral unitLiteral = call.operand(0);
  final TimeUnit unit = unitLiteral.symbolValue(TimeUnit.class);
  RexNode interval2Add;
  SqlIntervalQualifier qualifier =
      new SqlIntervalQualifier(unit, null, unitLiteral.getParserPosition());
  RexNode op1 = cx.convertExpression(call.operand(1));
  switch (unit) {
  case MICROSECOND:
  case NANOSECOND:
    interval2Add =
        divide(rexBuilder,
            multiply(rexBuilder,
                rexBuilder.makeIntervalLiteral(BigDecimal.ONE, qualifier), op1),
            BigDecimal.ONE.divide(unit.multiplier,
                RoundingMode.UNNECESSARY));
    break;
  default:
    interval2Add = multiply(rexBuilder,
        rexBuilder.makeIntervalLiteral(unit.multiplier, qualifier), op1);
  }

  return rexBuilder.makeCall(SqlStdOperatorTable.DATETIME_PLUS,
      cx.convertExpression(call.operand(2)), interval2Add);
}
 

@Override public RexNode singleton(RexBuilder rexBuilder,
    RelDataType inputRowType, AggregateCall aggregateCall) {
  final int arg = aggregateCall.getArgList().get(0);
  final RelDataType type = inputRowType.getFieldList().get(arg).getType();
  final RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
  final RelDataType type1 = typeFactory.getTypeSystem().deriveSumType(typeFactory, type);
  final RexNode inputRef = rexBuilder.makeInputRef(type1, arg);
  if (type.isNullable()) {
    return rexBuilder.makeCall(SqlStdOperatorTable.COALESCE, inputRef,
        rexBuilder.makeExactLiteral(BigDecimal.ZERO, type));
  } else {
    return inputRef;
  }
}
 

private SqlNode getCastedSqlNode(SqlNode argInput, RelDataType varType,
    SqlParserPos pos, RexNode argRex) {
  SqlNode arg;
  if (argRex != null && !argRex.getType().equals(varType)) {
    arg = SqlStdOperatorTable.CAST.createCall(
        pos, argInput, SqlTypeUtil.convertTypeToSpec(varType));
  } else {
    arg = argInput;
  }
  return arg;
}
 

public void addSelect(List<SqlNode> selectList, SqlNode node,
                      RelDataType rowType) {
  String name = rowType.getFieldNames().get(selectList.size());
  String alias = SqlValidatorUtil.getAlias(node, -1);
  if (alias == null || !alias.equals(name)) {
    node = SqlStdOperatorTable.AS.createCall(
      POS, node, new SqlIdentifier(name, POS));
  }
  selectList.add(node);
}
 

/**
 * Converts an expression "expr" into "expr AS alias".
 */
public static SqlNode addAlias(
    SqlNode expr,
    String alias) {
  final SqlParserPos pos = expr.getParserPosition();
  final SqlIdentifier id = new SqlIdentifier(alias, pos);
  return SqlStdOperatorTable.AS.createCall(pos, expr, id);
}
 

private RexNode restructure(RelDataType structuredType) {
  // Use ROW(f1,f2,...,fn) to put flattened data back together into a structure.
  List<RexNode> structFields = restructureFields(structuredType);
  RexNode rowConstructor = rexBuilder.makeCall(
      structuredType,
      SqlStdOperatorTable.ROW,
      structFields);
  return rowConstructor;
}
 

protected RexNode makeDivide(
    RexNode a,
    RexNode b) {
  return builder.makeCall(
      SqlStdOperatorTable.DIVIDE_INTEGER,
      a,
      b);
}
 

public boolean isKey(RelOptTable table, List<Integer> columns) {
  final SqlDialect dialect = table.unwrap(SqlDialect.class);
  final DataSource dataSource = table.unwrap(DataSource.class);
  return withBuilder(
      (cluster, relOptSchema, relBuilder) -> {
        // The collection of columns ['DEPTNO'] is a key for 'EMP' if the
        // following query returns no rows:
        //
        //   SELECT 1
        //   FROM `EMP`
        //   GROUP BY `DEPTNO`
        //   HAVING COUNT(*) > 1
        //
        final RelOptTable.ToRelContext toRelContext =
            ViewExpanders.simpleContext(cluster);
        relBuilder.push(table.toRel(toRelContext))
            .aggregate(relBuilder.groupKey(relBuilder.fields(columns)),
                relBuilder.count())
            .filter(
                relBuilder.call(SqlStdOperatorTable.GREATER_THAN,
                    Util.last(relBuilder.fields()), relBuilder.literal(1)));
        final String sql = toSql(relBuilder.build(), dialect);

        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
          return !resultSet.next();
        } catch (SQLException e) {
          throw handle(e, sql);
        }
      });
}
 

protected RexNode makeMultiply(
    RexNode a,
    RexNode b) {
  return builder.makeCall(
      SqlStdOperatorTable.MULTIPLY,
      a,
      b);
}
 

@Override public boolean validRexOperands(int count, Litmus litmus) {
  if (count != 2) {
    // Special exception for AND and OR.
    if ((this == SqlStdOperatorTable.AND
        || this == SqlStdOperatorTable.OR)
        && count > 2) {
      return true;
    }
    return litmus.fail("wrong operand count {} for {}", count, this);
  }
  return litmus.succeed();
}
 

/** Wraps a call in a {@link SqlKind#WITHIN_GROUP} call, if
 * {@code orderList} is non-empty. */
private SqlNode withOrder(SqlCall call, SqlNodeList orderList) {
    if (orderList == null || orderList.size() == 0) {
        return call;
    }
    return SqlStdOperatorTable.WITHIN_GROUP.createCall(POS, call, orderList);
}
 

protected RexNode makePlus(
    RexNode a,
    RexNode b) {
  return builder.makeCall(
      SqlStdOperatorTable.PLUS,
      a,
      b);
}
 

protected RexNode makeCase(
    RexNode whenA,
    RexNode thenA,
    RexNode whenB,
    RexNode thenB,
    RexNode elseClause) {
  return builder.makeCall(
      SqlStdOperatorTable.CASE,
      whenA,
      thenA,
      whenB,
      thenB,
      elseClause);
}