@Test
  public void testMultiValuedParamsForNonSupportingOperators() {
    Map<String, Object> param = new HashMap<String, Object>();
    param.put("param1", new String[] {"SHIPPED", "DELIVERED"});

    handleFormulaFailure(
        getOrdersModel(),
        "Hypersonic",
        param,
        "[bc_ORDERS.BC_ORDERS_STATUS]=[param:param1]",
        Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "="));

    handleFormulaFailure(
        getOrdersModel(),
        "Hypersonic",
        param,
        "[bc_ORDERS.BC_ORDERS_STATUS]>[param:param1]",
        Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", ">"));
    handleFormulaFailure(
        getOrdersModel(),
        "Hypersonic",
        param,
        "[bc_ORDERS.BC_ORDERS_STATUS]<[param:param1]",
        Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "<"));
    handleFormulaFailure(
        getOrdersModel(),
        "Hypersonic",
        param,
        "[bc_ORDERS.BC_ORDERS_STATUS]>=[param:param1]",
        Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", ">="));
    handleFormulaFailure(
        getOrdersModel(),
        "Hypersonic",
        param,
        "[bc_ORDERS.BC_ORDERS_STATUS]<=[param:param1]",
        Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "<="));
  }
  @Test
  public void testMultiValuedParamsForNonSupportingFunctions() {
    Map<String, Object> param = new HashMap<String, Object>();
    param.put("param1", new String[] {"SHIPPED", "DELIVERED"});

    handleFormulaFailure(
        getOrdersModel(),
        "Hypersonic",
        param,
        "ENDSWITH([bc_ORDERS.BC_ORDERS_STATUS];[param:param1])",
        Messages.getErrorString(
            "SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "ENDSWITH"));

    handleFormulaFailure(
        getOrdersModel(),
        "Hypersonic",
        param,
        "BEGINSWITH([bc_ORDERS.BC_ORDERS_STATUS];[param:param1])",
        Messages.getErrorString(
            "SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "BEGINSWITH"));

    handleFormulaFailure(
        getOrdersModel(),
        "Hypersonic",
        param,
        "LIKE([bc_ORDERS.BC_ORDERS_STATUS];[param:param1])",
        Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "LIKE"));

    handleFormulaFailure(
        getOrdersModel(),
        "Hypersonic",
        param,
        "COALESCE([bc_ORDERS.BC_ORDERS_STATUS];[param:param1])",
        Messages.getErrorString(
            "SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "COALESCE"));
  }
 /**
  * SqlOpenFormulaContext and SqlOpenFormula do not use libFormula in the traditional manner of
  * executing a formula. Instead they generate the necessary SQL to be executed from an RDBMS.
  */
 public Type resolveReferenceType(Object name) {
   throw new UnsupportedOperationException(
       Messages.getErrorString("SqlOpenFormulaContext.ERROR_0001_INVALID_USE")); // $NON-NLS-1$
 }
  /**
   * returns the generated SQL and additional metadata
   *
   * @param selections The selected business columns
   * @param conditions the conditions to apply (null = no conditions)
   * @param orderBy the ordering (null = no order by clause)
   * @param databaseMeta the meta info which determines the SQL generated.
   * @param locale the locale
   * @param disableDistinct if true, disables default behavior of using DISTINCT when there are no
   *     groupings.
   * @param securityConstraint if provided, applies a global security constraint to the query
   * @return a SQL query based on a column selection, conditions and a locale
   */
  protected MappedQuery getSQL(
      LogicalModel model,
      List<Selection> selections,
      List<Constraint> conditions,
      List<Order> orderBy,
      DatabaseMeta databaseMeta,
      String locale,
      Map<String, Object> parameters,
      boolean genAsPreparedStatement,
      boolean disableDistinct,
      int limit,
      Constraint securityConstraint)
      throws PentahoMetadataException {

    SQLQueryModel query = new SQLQueryModel();

    // Get settings for the query model
    Object val = null;
    val = model.getProperty("delay_outer_join_conditions"); // $NON-NLS-1$
    if ((val != null) && (val instanceof Boolean)) {
      query.setDelayOuterJoinConditions(((Boolean) val).booleanValue());
    }

    Map<String, String> columnsMap = new HashMap<String, String>();

    // generate the formula objects for constraints
    Map<Constraint, SqlOpenFormula> constraintFormulaMap =
        new HashMap<Constraint, SqlOpenFormula>();
    for (Constraint constraint : conditions) {
      SqlOpenFormula formula =
          new SqlOpenFormula(
              model,
              databaseMeta,
              constraint.getFormula(),
              null,
              parameters,
              genAsPreparedStatement);
      formula.parseAndValidate();
      constraintFormulaMap.put(constraint, formula);
    }
    if (securityConstraint != null) {
      SqlOpenFormula formula =
          new SqlOpenFormula(
              model,
              databaseMeta,
              securityConstraint.getFormula(),
              null,
              parameters,
              genAsPreparedStatement);
      formula.parseAndValidate();
      constraintFormulaMap.put(securityConstraint, formula);
    }

    // These are the tables involved in the field selection
    //
    List<LogicalTable> tabs =
        getTablesInvolved(
            model,
            selections,
            conditions,
            orderBy,
            constraintFormulaMap,
            parameters,
            genAsPreparedStatement,
            databaseMeta,
            locale,
            securityConstraint);

    // Now get the shortest path between these tables.
    Path path = getShortestPathBetween(model, tabs);
    if (path == null) {
      throw new PentahoMetadataException(
          Messages.getErrorString("SqlGenerator.ERROR_0002_FAILED_TO_FIND_PATH")); // $NON-NLS-1$
    }

    List<LogicalTable> usedBusinessTables = path.getUsedTables();
    if (path.size() == 0) {
      // just a selection from 1 table: pick any column...
      if (selections.size() > 0) // Otherwise, why bother, right?
      {
        usedBusinessTables.add(selections.get(0).getLogicalColumn().getLogicalTable());
      }
    }

    Map<LogicalTable, String> tableAliases = null;

    if (usedBusinessTables.size() > 0) {

      // generate tableAliases mapping

      int maxAliasNameWidth = SQLDialectFactory.getSQLDialect(databaseMeta).getMaxTableNameLength();
      tableAliases = new HashMap<LogicalTable, String>();
      for (LogicalTable table : usedBusinessTables) {
        String uniqueAlias =
            generateUniqueAlias(table.getId(), maxAliasNameWidth, tableAliases.values());
        tableAliases.put(table, uniqueAlias);
      }

      boolean group =
          hasFactsInIt(
              model,
              selections,
              conditions,
              constraintFormulaMap,
              parameters,
              genAsPreparedStatement,
              databaseMeta,
              locale);

      generateSelect(
          query,
          model,
          databaseMeta,
          selections,
          disableDistinct,
          limit,
          group,
          locale,
          tableAliases,
          columnsMap,
          parameters,
          genAsPreparedStatement);
      generateFromAndWhere(
          query,
          usedBusinessTables,
          model,
          path,
          conditions,
          tableAliases,
          constraintFormulaMap,
          parameters,
          genAsPreparedStatement,
          databaseMeta,
          locale);
      if (group) {
        generateGroupBy(
            query,
            model,
            selections,
            tableAliases,
            parameters,
            genAsPreparedStatement,
            databaseMeta,
            locale);
      }
      generateOrderBy(
          query,
          model,
          orderBy,
          databaseMeta,
          locale,
          tableAliases,
          columnsMap,
          parameters,
          genAsPreparedStatement);

      if (securityConstraint != null) {
        // apply current table aliases
        SqlOpenFormula securityFormula = constraintFormulaMap.get(securityConstraint);
        securityFormula.setTableAliases(tableAliases);

        // generate sql
        String sqlFormula = securityFormula.generateSQL(locale);
        query.setSecurityConstraint(sqlFormula, securityFormula.hasAggregate());
      }
    }

    // this is available to classes that override sql generation behavior
    preprocessQueryModel(query, selections, tableAliases, databaseMeta);

    // Convert temporary param placements with Sql Prepared Statement ? values
    SQLDialectInterface dialect = SQLDialectFactory.getSQLDialect(databaseMeta);
    List<String> paramNames = null;
    String sql = dialect.generateSelectStatement(query);
    Pattern p = Pattern.compile("___PARAM\\[(.*?)\\]___"); // $NON-NLS-1$
    Matcher m = p.matcher(sql);
    StringBuffer sb = new StringBuffer();
    while (m.find()) {
      String paramName = m.group(1);
      String repl = "?";
      if (parameters.get(paramName) instanceof Object[]) {
        Object[] paramz = (Object[]) parameters.get(paramName);
        for (int i = 1; i < paramz.length; i++) {
          repl += ", ?";
        }
      }
      m.appendReplacement(sb, repl); // $NON-NLS-1$
      if (paramNames == null) {
        paramNames = new ArrayList<String>();
      }
      paramNames.add(paramName);
    }
    m.appendTail(sb);

    String sqlStr = sb.toString();
    if (logger.isTraceEnabled()) {
      logger.trace(sqlStr);
    }
    // this is available to classes that override sql generation behavior
    String sqlOutput = processGeneratedSql(sb.toString());

    return new MappedQuery(sqlOutput, columnsMap, selections, paramNames);
  }
  protected String getJoin(
      LogicalModel businessModel,
      LogicalRelationship relation,
      Map<LogicalTable, String> tableAliases,
      Map<String, Object> parameters,
      boolean genAsPreparedStatement,
      DatabaseMeta databaseMeta,
      String locale)
      throws PentahoMetadataException {
    String join = ""; // $NON-NLS-1$
    if (relation.isComplex()) {
      try {
        // parse join as MQL
        SqlOpenFormula formula =
            new SqlOpenFormula(
                businessModel,
                databaseMeta,
                relation.getComplexJoin(),
                tableAliases,
                parameters,
                genAsPreparedStatement);
        formula.parseAndValidate();
        join = formula.generateSQL(locale);
      } catch (PentahoMetadataException e) {
        // backward compatibility, deprecate
        logger.warn(
            Messages.getErrorString(
                "SqlGenerator.ERROR_0017_FAILED_TO_PARSE_COMPLEX_JOIN", relation.getComplexJoin()),
            e); //$NON-NLS-1$
        join = relation.getComplexJoin();
      }
    } else if (relation.getFromTable() != null
        && relation.getToTable() != null
        && relation.getFromColumn() != null
        && relation.getToColumn() != null) {
      // Left side
      String leftTableAlias = null;
      if (tableAliases != null) {
        leftTableAlias = tableAliases.get(relation.getFromColumn().getLogicalTable());
      } else {
        leftTableAlias = relation.getFromColumn().getLogicalTable().getId();
      }

      join = databaseMeta.quoteField(leftTableAlias);
      join += "."; // $NON-NLS-1$
      join +=
          databaseMeta.quoteField(
              (String) relation.getFromColumn().getProperty(SqlPhysicalColumn.TARGET_COLUMN));

      // Equals
      join += " = "; // $NON-NLS-1$

      // Right side
      String rightTableAlias = null;
      if (tableAliases != null) {
        rightTableAlias = tableAliases.get(relation.getToColumn().getLogicalTable());
      } else {
        rightTableAlias = relation.getToColumn().getLogicalTable().getId();
      }

      join += databaseMeta.quoteField(rightTableAlias);
      join += "."; // $NON-NLS-1$
      join +=
          databaseMeta.quoteField(
              (String) relation.getToColumn().getProperty(SqlPhysicalColumn.TARGET_COLUMN));
    } else {
      throw new PentahoMetadataException(
          Messages.getErrorString(
              "SqlGenerator.ERROR_0003_INVALID_RELATION", relation.toString())); // $NON-NLS-1$
    }

    return join;
  }
  public static SqlAndTables getBusinessColumnSQL(
      LogicalModel businessModel,
      Selection column,
      Map<LogicalTable, String> tableAliases,
      Map<String, Object> parameters,
      boolean genAsPreparedStatement,
      DatabaseMeta databaseMeta,
      String locale) {
    String targetColumn =
        (String) column.getLogicalColumn().getProperty(SqlPhysicalColumn.TARGET_COLUMN);
    LogicalTable logicalTable = column.getLogicalColumn().getLogicalTable();
    if (column.getLogicalColumn().getProperty(SqlPhysicalColumn.TARGET_COLUMN_TYPE)
        == TargetColumnType.OPEN_FORMULA) {
      // convert to sql using libformula subsystem

      try {
        // we'll need to pass in some context to PMSFormula so it can resolve aliases if necessary
        SqlOpenFormula formula =
            new SqlOpenFormula(
                businessModel,
                logicalTable,
                databaseMeta,
                targetColumn,
                tableAliases,
                parameters,
                genAsPreparedStatement);
        formula.parseAndValidate();

        String formulaSql = formula.generateSQL(locale);

        // check for old style, where function is hardcoded in the model.
        if (column.hasAggregate() && !hasAggregateDefinedAlready(formulaSql, databaseMeta)) {
          formulaSql = getFunctionExpression(column, formulaSql, databaseMeta);
        }

        return new SqlAndTables(formulaSql, formula.getLogicalTables(), formula.getSelections());
      } catch (PentahoMetadataException e) {
        // this is for backwards compatibility.
        // eventually throw any errors
        logger.warn(
            Messages.getErrorString(
                "SqlGenerator.ERROR_0001_FAILED_TO_PARSE_FORMULA", targetColumn),
            e); //$NON-NLS-1$

        // Report just this table and column as being used along with the formula.
        //
        return new SqlAndTables(targetColumn, logicalTable, column);
      }
    } else {
      String tableColumn = ""; // $NON-NLS-1$

      // this step is required because this method is called in two contexts.  The first
      // call determines all the tables involved, making it impossible to guarantee
      // unique aliases.

      String tableAlias = null;
      if (tableAliases != null) {
        tableAlias = tableAliases.get(logicalTable);
      } else {
        tableAlias = logicalTable.getId();
      }
      tableColumn += databaseMeta.quoteField(tableAlias);
      tableColumn += "."; // $NON-NLS-1$

      // TODO: WPG: instead of using formula, shouldn't we use the physical column's name?
      tableColumn += databaseMeta.quoteField(targetColumn);

      // For the having clause, for example: HAVING sum(turnover) > 100
      if (column.hasAggregate()) {
        return new SqlAndTables(
            getFunctionExpression(column, tableColumn, databaseMeta), logicalTable, column);
      } else {
        return new SqlAndTables(tableColumn, logicalTable, column);
      }
    }
  }