public void testA() {
    // Schema has two cubes sharing a dimension.
    // Query from the first cube.
    TestContext testContext = getTestContextForSharedDimCubeACubeB();

    testContext.assertQueryReturns(queryCubeA, resultCubeA);
  }
 private static void checkLocale(
     final String localeName, String expr, String expected, boolean isQuery) {
   TestContext testContextSpain =
       new TestContext() {
         public mondrian.olap.Connection getConnection() {
           Util.PropertyList properties = Util.parseConnectString(getConnectString());
           properties.put(RolapConnectionProperties.Locale.name(), localeName);
           return DriverManager.getConnection(properties, null);
         }
       };
   if (isQuery) {
     String query =
         "WITH MEMBER [Measures].[Foo] AS '"
             + expr
             + "',\n"
             + " FORMAT_STRING = '#,##.#' \n"
             + "SELECT {[MEasures].[Foo]} ON COLUMNS FROM [Sales]";
     String expected2 =
         "Axis #0:\n"
             + "{}\n"
             + "Axis #1:\n"
             + "{[Measures].[Foo]}\n"
             + "Row #0: "
             + expected
             + "\n";
     testContextSpain.assertQueryReturns(query, expected2);
   } else {
     testContextSpain.assertExprReturns(expr, expected);
   }
 }
Beispiel #3
0
  /**
   * Makes sure the MDX runs correctly and not in native mode.
   *
   * @param rowCount Number of rows returned
   * @param mdx Query
   * @param expectedResult Expected result string
   */
  protected void checkNotNative(int rowCount, String mdx, String expectedResult) {
    getConnection().getCacheControl(null).flushSchemaCache();
    Connection con = getTestContext().withSchemaPool(false).getConnection();
    RolapNativeRegistry reg = getRegistry(con);
    reg.setListener(
        new Listener() {
          public void foundEvaluator(NativeEvent e) {
            fail("should not be executed native");
          }

          public void foundInCache(TupleEvent e) {}

          public void executingSql(TupleEvent e) {}
        });

    TestCase c = new TestCase(con, 0, rowCount, mdx);
    Result result = c.run();

    if (expectedResult != null) {
      String nonNativeResult = TestContext.toString(result);
      if (!nonNativeResult.equals(expectedResult)) {
        TestContext.assertEqualsVerbose(
            expectedResult,
            nonNativeResult,
            false,
            "Non Native implementation returned different result than " + "expected; MDX=" + mdx);
      }
    }
  }
  public void testNECJMemberList() {
    // Schema has two cubes sharing a dimension.
    // Query from the second cube.
    TestContext testContext = getTestContextForSharedDimCubeACubeB();

    testContext.assertQueryReturns(queryNECJMemberList, resultNECJMemberList);
  }
  public void testNECJMultiLevelMemberList() {
    // Schema has two cubes sharing a dimension.
    // Query from the first cube.
    // This is a case where not using alias not only affects performance,
    // but also produces incorrect result.
    TestContext testContext = getTestContextForSharedDimCubeACubeB();

    testContext.assertQueryReturns(queryNECJMultiLevelMemberList, resultNECJMultiLevelMemberList);
  }
  public void testVirtualCube() {
    // Schema has two cubes sharing a dimension, and a virtual cube built
    // over these two cubes.
    // Query from the virtual cube.

    TestContext testContext =
        TestContext.instance()
            .create(sharedDimension, cubeA + "\n" + cubeB, virtualCube, null, null, null);

    testContext.assertQueryReturns(queryVirtualCube, resultVirtualCube);
  }
 protected void setUp() throws Exception {
   super.setUp(); // parent setUp enabled agg
   propSaver.set(propSaver.properties.EnableNativeCrossJoin, true);
   propSaver.set(propSaver.properties.EnableNativeNonEmpty, true);
   propSaver.set(propSaver.properties.GenerateFormattedSql, true);
   TestContext.instance().flushSchemaCache();
 }
Beispiel #8
0
  public void testD1() throws Exception {
    if (!isApplicable()) {
      return;
    }
    String mdx =
        "select "
            + " filter({[D1].[a],[D1].[b],[D1].[c]}, "
            + "    [Measures].[Value] > 0) "
            + " ON COLUMNS, "
            + " {[D2].[x],[D2].[y],[D2].[z]} "
            + " ON ROWS "
            + "from FT1";

    // getCubeTestContext().assertQueryReturns(mdx, RESULTS);
    Result result = getCubeTestContext().executeQuery(mdx);
    String resultString = TestContext.toString(result);
    // System.out.println(resultString);
    /*
     This is what is produced
    Axis #0:
    {}
    Axis #1:
    Axis #2:
    {[D2].[x]}
    {[D2].[y]}
    {[D2].[z]}
    */
    assertEquals(resultString, RESULTS);
  }
Beispiel #9
0
 private RolapCube lookupCube(String cubeName) {
   Connection connection = TestContext.instance().getConnection();
   for (Cube cube : connection.getSchema().getCubes()) {
     if (cube.getName().equals(cubeName)) {
       return (RolapCube) cube;
     }
   }
   return null;
 }
  public void testJndiConnection() throws NamingException {
    // Cannot guarantee that this test will work if they have chosen to
    // resolve data sources other than by JNDI.
    if (MondrianProperties.instance().DataSourceResolverClass.isSet()) {
      return;
    }
    // get a regular connection
    Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();
    final StringBuilder buf = new StringBuilder();
    final DataSource dataSource = RolapConnection.createDataSource(null, properties, buf);
    // Don't know what the connect string is - it differs with database
    // and with the user's set up - but we know that it contains a JDBC
    // connect string. Best we can do is check that createDataSource is
    // setting it to something.
    final String desc = buf.toString();
    assertTrue(desc, desc.startsWith("Jdbc="));

    final List<String> lookupCalls = new ArrayList<String>();
    // mock the JNDI naming manager to provide that datasource
    THREAD_INITIAL_CONTEXT.set(
        // Use lazy initialization. Otherwise during initialization of this
        // initial context JNDI tries to create a default initial context
        // and bumps into itself coming the other way.
        new InitialContext(true) {
          public Object lookup(String str) {
            lookupCalls.add("Called");
            return dataSource;
          }
        });

    // Use the datasource property to connect to the database.
    // Remove user and password, because some data sources (those using
    // pools) don't allow you to override user.
    Util.PropertyList properties2 = TestContext.instance().getConnectionProperties().clone();
    properties2.remove(RolapConnectionProperties.Jdbc.name());
    properties2.remove(RolapConnectionProperties.JdbcUser.name());
    properties2.remove(RolapConnectionProperties.JdbcPassword.name());
    properties2.put(RolapConnectionProperties.DataSource.name(), "jnditest");
    DriverManager.getConnection(properties2, null);

    // if we've made it here with lookupCalls,
    // we've successfully used JNDI
    assertTrue(lookupCalls.size() > 0);
  }
  public void testNegativeMatching() throws Exception {
    if (!MondrianProperties.instance().EnableNativeFilter.get()) {
      // No point testing these if the native filters
      // are turned off.
      return;
    }
    final String sqlOracle =
        "select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", \"fname\" || ' ' || \"lname\" as \"c4\", \"fname\" || ' ' || \"lname\" as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", \"fname\" || ' ' || \"lname\", \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having NOT(REGEXP_LIKE(\"fname\" || ' ' || \"lname\", '.*jeanne.*', 'i'))  order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, \"fname\" || ' ' || \"lname\" ASC NULLS LAST";
    final String sqlPgsql =
        "select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", fullname as \"c4\", fullname as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" as \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", fullname, \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having NOT(cast(fullname as text) ~ '(?i).*jeanne.*')  order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, fullname ASC NULLS LAST";
    final String sqlMysql =
        "select `customer`.`country` as `c0`, `customer`.`state_province` as `c1`, `customer`.`city` as `c2`, `customer`.`customer_id` as `c3`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`, `customer`.`gender` as `c6`, `customer`.`marital_status` as `c7`, `customer`.`education` as `c8`, `customer`.`yearly_income` as `c9` from `customer` as `customer` group by `customer`.`country`, `customer`.`state_province`, `customer`.`city`, `customer`.`customer_id`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`), `customer`.`gender`, `customer`.`marital_status`, `customer`.`education`, `customer`.`yearly_income` having NOT(UPPER(c5) REGEXP '.*JEANNE.*')  order by "
            + (TestContext.instance().getDialect().requiresOrderByAlias()
                ? "ISNULL(`c0`) ASC, `c0` ASC, "
                    + "ISNULL(`c1`) ASC, `c1` ASC, "
                    + "ISNULL(`c2`) ASC, `c2` ASC, "
                    + "ISNULL(`c4`) ASC, `c4` ASC"
                : "ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC, ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC, ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC, ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC");
    SqlPattern[] patterns = {
      new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length()),
      new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()),
      new SqlPattern(Dialect.DatabaseProduct.POSTGRESQL, sqlPgsql, sqlPgsql.length())
    };

    final String query =
        "With\n"
            + "Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Customers], Not IsEmpty ([Measures].[Unit Sales]))'\n"
            + "Set [*SORTED_COL_AXIS] as 'Order([*CJ_COL_AXIS],[Customers].CurrentMember.OrderKey,BASC,Ancestor([Customers].CurrentMember,[Customers].[City]).OrderKey,BASC)'\n"
            + "Set [*BASE_MEMBERS_Customers] as 'Filter([Customers].[Name].Members,[Customers].CurrentMember.Caption Not Matches (\"(?i).*\\Qjeanne\\E.*\"))'\n"
            + "Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'\n"
            + "Set [*CJ_COL_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Customers].currentMember)})'\n"
            + "Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Unit Sales]', FORMAT_STRING = 'Standard', SOLVE_ORDER=400\n"
            + "Select\n"
            + "CrossJoin([*SORTED_COL_AXIS],[*BASE_MEMBERS_Measures]) on columns\n"
            + "From [Sales]";

    assertQuerySqlOrNot(getTestContext(), query, patterns, false, true, true);

    final Result result = executeQuery(query);
    final String resultString = TestContext.toString(result);
    assertFalse(resultString.contains("Jeanne"));
    verifySameNativeAndNot(query, null, getTestContext());
  }
  public void testNativeFilterWithCompoundSlicerWithAggs() {
    propSaver.set(MondrianProperties.instance().UseAggregates, true);
    propSaver.set(MondrianProperties.instance().ReadAggregates, true);
    propSaver.set(MondrianProperties.instance().GenerateFormattedSql, true);
    final String mdx =
        "with member measures.avgQtrs as 'avg( filter( time.quarter.members, measures.[unit sales] > 80))' "
            + "select measures.avgQtrs * gender.members on 0 from sales where head( product.[product name].members, 3)";
    if (MondrianProperties.instance().EnableNativeFilter.get()
        && MondrianProperties.instance().EnableNativeNonEmpty.get()) {
      final String sqlMysql =
          "select\n"
              + "    `agg_c_14_sales_fact_1997`.`the_year` as `c0`,\n"
              + "    `agg_c_14_sales_fact_1997`.`quarter` as `c1`\n"
              + "from\n"
              + "    `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n"
              + "    `product` as `product`,\n"
              + "    `customer` as `customer`\n"
              + "where\n"
              + "    `agg_c_14_sales_fact_1997`.`product_id` = `product`.`product_id`\n"
              + "and\n"
              + "    `product`.`product_name` in ('Good Imported Beer', 'Good Light Beer', 'Pearl Imported Beer')\n"
              + "and\n"
              + "    `agg_c_14_sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n"
              + "and\n"
              + "    `customer`.`gender` = 'M'\n"
              + "group by\n"
              + "    `agg_c_14_sales_fact_1997`.`the_year`,\n"
              + "    `agg_c_14_sales_fact_1997`.`quarter`\n"
              + "having\n"
              + "    (sum(`agg_c_14_sales_fact_1997`.`unit_sales`) > 80)\n"
              + "order by\n"
              + (TestContext.instance().getDialect().requiresOrderByAlias()
                  ? "    ISNULL(`c0`) ASC, `c0` ASC,\n" + "    ISNULL(`c1`) ASC, `c1` ASC"
                  : "    ISNULL(`agg_c_14_sales_fact_1997`.`the_year`) ASC, `agg_c_14_sales_fact_1997`.`the_year` ASC,\n"
                      + "    ISNULL(`agg_c_14_sales_fact_1997`.`quarter`) ASC, `agg_c_14_sales_fact_1997`.`quarter` ASC");
      final SqlPattern[] patterns = mysqlPattern(sqlMysql);

      // Make sure the tuples list is using the HAVING clause.
      assertQuerySqlOrNot(getTestContext(), mdx, patterns, false, true, true);
    }
    // Make sure the numbers are right
    assertQueryReturns(
        mdx,
        "Axis #0:\n"
            + "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good].[Good Imported Beer]}\n"
            + "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good].[Good Light Beer]}\n"
            + "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Pearl].[Pearl Imported Beer]}\n"
            + "Axis #1:\n"
            + "{[Measures].[avgQtrs], [Gender].[All Gender]}\n"
            + "{[Measures].[avgQtrs], [Gender].[F]}\n"
            + "{[Measures].[avgQtrs], [Gender].[M]}\n"
            + "Row #0: 111\n"
            + "Row #0: \n"
            + "Row #0: \n");
  }
Beispiel #13
0
  public void testNonAllPromotionMembers() {
    TestContext testContext =
        TestContext.instance()
            .createSubstitutingCube(
                "Sales",
                "<Dimension name=\"Promotions2\" foreignKey=\"promotion_id\">\n"
                    + "  <Hierarchy hasAll=\"false\" primaryKey=\"promotion_id\">\n"
                    + "    <Table name=\"promotion\"/>\n"
                    + "    <Level name=\"Promotion2 Name\" column=\"promotion_name\" uniqueMembers=\"true\"/>\n"
                    + "  </Hierarchy>\n"
                    + "</Dimension>");

    testContext.assertQueryReturns(
        "select {[Promotion2 Name].[Price Winners], [Promotion2 Name].[Sale Winners]} * {Tail([Time].[Year].Members,3)} ON COLUMNS, "
            + "NON EMPTY Crossjoin({[Store].CurrentMember.Children},  {[Store Type].[All Store Types].Children}) ON ROWS "
            + "from [Sales]",
        "Axis #0:\n"
            + "{}\n"
            + "Axis #1:\n"
            + "{[Promotions2].[Price Winners], [Time].[1997]}\n"
            + "{[Promotions2].[Price Winners], [Time].[1998]}\n"
            + "{[Promotions2].[Sale Winners], [Time].[1997]}\n"
            + "{[Promotions2].[Sale Winners], [Time].[1998]}\n"
            + "Axis #2:\n"
            + "{[Store].[USA], [Store Type].[Mid-Size Grocery]}\n"
            + "{[Store].[USA], [Store Type].[Small Grocery]}\n"
            + "{[Store].[USA], [Store Type].[Supermarket]}\n"
            + "Row #0: \n"
            + "Row #0: \n"
            + "Row #0: 444\n"
            + "Row #0: \n"
            + "Row #1: 23\n"
            + "Row #1: \n"
            + "Row #1: \n"
            + "Row #1: \n"
            + "Row #2: 1,271\n"
            + "Row #2: \n"
            + "Row #2: \n"
            + "Row #2: \n");
  }
Beispiel #14
0
  /**
   * This ought to give the same result as the above testD2() method. In this case, the FT2Extra
   * cube has a default measure with no data (null) for all members. This default measure is used in
   * the evaluation even though there is an implicit use of the measure [Measures].[Value].
   *
   * @throws Exception
   */
  public void _testNullDefaultMeasure() throws Exception {
    if (!isApplicable()) {
      return;
    }

    String mdx =
        "select "
            + " NON EMPTY filter({[D1].[a],[D1].[b],[D1].[c]}, "
            + "    [Measures].[Value] > 0) "
            + " ON COLUMNS, "
            + " {[D2].[x],[D2].[y],[D2].[z]} "
            + " ON ROWS "
            + "from FT2Extra";

    // getCubeTestContext().assertQueryReturns(mdx, RESULTS);
    Result result = getCubeTestContext().executeQuery(mdx);
    String resultString = TestContext.toString(result);
    assertTrue(resultString.equals(RESULTS));
  }
  public void testPooledConnectionWithProperties() throws SQLException {
    Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();

    // Only the JDBC-ODBC bridge gives the error necessary for this
    // test to succeed. So trivially succeed for all other JDBC
    // drivers.
    final String jdbc = properties.get("Jdbc");
    if (jdbc != null && !jdbc.startsWith("jdbc:odbc:")) {
      return;
    }

    // JDBC-ODBC driver does not support UTF-16, so this test succeeds
    // because creating the connection from the DataSource will fail.
    properties.put("jdbc.charSet", "UTF-16");

    final StringBuilder buf = new StringBuilder();
    DataSource dataSource = RolapConnection.createDataSource(null, properties, buf);
    final String desc = buf.toString();
    assertTrue(desc.startsWith("Jdbc="));

    Connection connection;
    try {
      connection = dataSource.getConnection();
      connection.close();
      fail("Expected exception");
    } catch (SQLException e) {
      if (e.getClass().getName().equals("org.apache.commons.dbcp.DbcpException")) {
        // This is expected. (We use string-comparison so that the
        // compiler doesn't warn about using a deprecated class.)
      } else if (e.getClass() == SQLException.class
          && e.getCause() == null
          && e.getMessage() != null
          && e.getMessage().equals("")) {
        // This is expected, from a later version of Dbcp.
      } else {
        fail("Expected exception, but got a different one: " + e);
      }
    } catch (IllegalArgumentException e) {
      handleIllegalArgumentException(properties, e);
    } finally {
      RolapConnectionPool.instance().clearPool();
    }
  }
  public void testNonPooledConnectionWithProperties() {
    Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();

    // Only the JDBC-ODBC bridge gives the error necessary for this
    // test to succeed. So trivially succeed for all other JDBC
    // drivers.
    final String jdbc = properties.get("Jdbc");
    if (jdbc != null && !jdbc.startsWith("jdbc:odbc:")) {
      return;
    }

    // This test is just like the test testPooledConnectionWithProperties
    // except with non-pooled connections.
    properties.put("jdbc.charSet", "UTF-16");
    properties.put(RolapConnectionProperties.PoolNeeded.name(), "false");

    final StringBuilder buf = new StringBuilder();
    DataSource dataSource = RolapConnection.createDataSource(null, properties, buf);
    final String desc = buf.toString();
    assertTrue(desc.startsWith("Jdbc="));

    Connection connection = null;
    try {
      connection = dataSource.getConnection();
      fail("Expected exception");
    } catch (SQLException se) {
      // this is expected
    } catch (IllegalArgumentException e) {
      handleIllegalArgumentException(properties, e);
    } finally {
      if (connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
          // ignore
        }
      }
    }
  }
  public void testConnectSansCatalogFails() {
    Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();
    properties.remove(RolapConnectionProperties.Catalog.name());
    properties.remove(RolapConnectionProperties.CatalogContent.name());

    if (RolapUtil.SQL_LOGGER.isDebugEnabled()) {
      RolapUtil.SQL_LOGGER.debug(
          this.getName() + "\n  [Connection Properties | " + properties + "]\n");
    } else {
      System.out.println(properties);
    }

    try {
      DriverManager.getConnection(properties, null);
      fail("expected exception");
    } catch (MondrianException e) {
      assertTrue(
          e.getMessage()
                  .indexOf(
                      "Connect string must contain property 'Catalog' or "
                          + "property 'CatalogContent'")
              >= 0);
    }
  }
Beispiel #18
0
  /**
   * Checks that a given sequence of cell requests results in a particular SQL statement being
   * generated.
   *
   * <p>Always clears the cache before running the requests.
   *
   * <p>Runs the requests once for each SQL pattern in the current dialect. If there are multiple
   * patterns, runs the MDX query multiple times, and expects to see each SQL statement appear. If
   * there are no patterns in this dialect, the test trivially succeeds.
   *
   * @param requests Sequence of cell requests
   * @param patterns Set of patterns
   * @param negative Set to false in order to 'expect' a query or true to 'forbid' a query.
   */
  protected void assertRequestSql(CellRequest[] requests, SqlPattern[] patterns, boolean negative) {
    final RolapStar star = requests[0].getMeasure().getStar();
    final String cubeName = requests[0].getMeasure().getCubeName();
    final RolapCube cube = lookupCube(cubeName);
    final Dialect sqlDialect = star.getSqlQueryDialect();
    Dialect.DatabaseProduct d = sqlDialect.getDatabaseProduct();
    SqlPattern sqlPattern = SqlPattern.getPattern(d, patterns);
    if (d == Dialect.DatabaseProduct.UNKNOWN) {
      // If the dialect is not one in the pattern set, do not run the
      // test. We do not print any warning message.
      return;
    }

    boolean patternFound = false;
    for (SqlPattern pattern : patterns) {
      if (!pattern.hasDatabaseProduct(d)) {
        continue;
      }

      patternFound = true;

      clearCache(cube);

      String sql = sqlPattern.getSql();
      String trigger = sqlPattern.getTriggerSql();
      switch (d) {
        case ORACLE:
          sql = sql.replaceAll(" =as= ", " ");
          trigger = trigger.replaceAll(" =as= ", " ");
          break;
        case TERADATA:
          sql = sql.replaceAll(" =as= ", " as ");
          trigger = trigger.replaceAll(" =as= ", " as ");
          break;
      }

      // Create a dummy DataSource which will throw a 'bomb' if it is
      // asked to execute a particular SQL statement, but will otherwise
      // behave exactly the same as the current DataSource.
      RolapUtil.setHook(new TriggerHook(trigger));
      Bomb bomb;
      final Execution execution =
          new Execution(((RolapConnection) getConnection()).getInternalStatement(), 1000);
      final AggregationManager aggMgr =
          execution
              .getMondrianStatement()
              .getMondrianConnection()
              .getServer()
              .getAggregationManager();
      final Locus locus = new Locus(execution, "BatchTestCase", "BatchTestCase");
      try {
        FastBatchingCellReader fbcr =
            new FastBatchingCellReader(execution, getCube(cubeName), aggMgr);
        for (CellRequest request : requests) {
          fbcr.recordCellRequest(request);
        }
        // The FBCR will presume there is a current Locus in the stack,
        // so let's create a mock one.
        Locus.push(locus);
        fbcr.loadAggregations();
        bomb = null;
      } catch (Bomb e) {
        bomb = e;
      } finally {
        RolapUtil.setHook(null);
        Locus.pop(locus);
      }
      if (!negative && bomb == null) {
        fail("expected query [" + sql + "] did not occur");
      } else if (negative && bomb != null) {
        fail("forbidden query [" + sql + "] detected");
      }
      TestContext.assertEqualsVerbose(replaceQuotes(sql), replaceQuotes(bomb.sql));
    }

    // Print warning message that no pattern was specified for the current
    // dialect.
    if (!patternFound) {
      String warnDialect = MondrianProperties.instance().WarnIfNoPatternForDialect.get();

      if (warnDialect.equals(d.toString())) {
        System.out.println(
            "[No expected SQL statements found for dialect \""
                + sqlDialect.toString()
                + "\" and test not run]");
      }
    }
  }
Beispiel #19
0
 protected Connection getFoodMartConnection() {
   return TestContext.instance().getConnection();
 }
Beispiel #20
0
  /**
   * During MDX query parse and execution, checks that the query results (or does not result) in a
   * particular SQL statement being generated.
   *
   * <p>Parses and executes the MDX query once for each SQL pattern in the current dialect. If there
   * are multiple patterns, runs the MDX query multiple times, and expects to see each SQL statement
   * appear. If there are no patterns in this dialect, the test trivially succeeds.
   *
   * @param testContext non-default test context if required
   * @param mdxQuery MDX query
   * @param patterns Set of patterns
   * @param negative false to assert if SQL is generated; true to assert if SQL is NOT generated
   * @param bypassSchemaCache whether to grab a new connection and bypass the schema cache before
   *     parsing the MDX query
   * @param clearCache whether to clear cache before executing the MDX query
   */
  protected void assertQuerySqlOrNot(
      TestContext testContext,
      String mdxQuery,
      SqlPattern[] patterns,
      boolean negative,
      boolean bypassSchemaCache,
      boolean clearCache) {
    Connection connection = testContext.getConnection();

    mdxQuery = testContext.upgradeQuery(mdxQuery);

    // Run the test once for each pattern in this dialect.
    // (We could optimize and run it once, collecting multiple queries, and
    // comparing all queries at the end.)
    Dialect dialect = testContext.getDialect();
    Dialect.DatabaseProduct d = dialect.getDatabaseProduct();
    boolean patternFound = false;
    for (SqlPattern sqlPattern : patterns) {
      if (!sqlPattern.hasDatabaseProduct(d)) {
        // If the dialect is not one in the pattern set, skip the
        // test. If in the end no pattern is located, print a warning
        // message if required.
        continue;
      }

      patternFound = true;

      String sql = sqlPattern.getSql();
      String trigger = sqlPattern.getTriggerSql();

      sql = dialectize(d, sql);
      trigger = dialectize(d, trigger);

      // Create a dummy DataSource which will throw a 'bomb' if it is
      // asked to execute a particular SQL statement, but will otherwise
      // behave exactly the same as the current DataSource.
      RolapUtil.setHook(new TriggerHook(trigger));
      Bomb bomb = null;
      try {
        if (bypassSchemaCache) {
          connection = testContext.withSchemaPool(false).getConnection();
        }
        final Query query = connection.parseQuery(mdxQuery);
        if (clearCache) {
          clearCache((RolapCube) query.getCube());
        }
        final Result result = connection.execute(query);
        Util.discard(result);
        bomb = null;
      } catch (Bomb e) {
        bomb = e;
      } catch (RuntimeException e) {
        // Walk up the exception tree and see if the root cause
        // was a SQL bomb.
        bomb = Util.getMatchingCause(e, Bomb.class);
        if (bomb == null) {
          throw e;
        }
      } finally {
        RolapUtil.setHook(null);
      }
      if (negative) {
        if (bomb != null) {
          fail("forbidden query [" + sql + "] detected");
        }
      } else {
        if (bomb == null) {
          fail("expected query [" + sql + "] did not occur");
        }
        assertEquals(
            replaceQuotes(sql.replaceAll("\r\n", "\n")),
            replaceQuotes(bomb.sql.replaceAll("\r\n", "\n")));
      }
    }

    // Print warning message that no pattern was specified for the current
    // dialect.
    if (!patternFound) {
      String warnDialect = MondrianProperties.instance().WarnIfNoPatternForDialect.get();

      if (warnDialect.equals(d.toString())) {
        System.out.println(
            "[No expected SQL statements found for dialect \""
                + dialect.toString()
                + "\" and test not run]");
      }
    }
  }
Beispiel #21
0
 public static void checkNative(String mdx, Result expectedResult) {
   BatchTestCase test = new BatchTestCase();
   test.checkNative(
       0, getRowCount(expectedResult), mdx, TestContext.toString(expectedResult), true);
 }
Beispiel #22
0
  /**
   * Runs a query twice, with native crossjoin optimization enabled and disabled. If both results
   * are equal,and both aggree with the expected result, it is considered correct.
   *
   * <p>Optionally the query can be run with fresh connection. This is useful if the test case sets
   * its certain mondrian properties, e.g. native properties like: mondrian.native.filter.enable
   *
   * @param resultLimit Maximum result size of all the MDX operations in this query. This might be
   *     hard to estimate as it is usually larger than the rowCount of the final result. Setting it
   *     to 0 will cause this limit to be ignored.
   * @param rowCount Number of rows returned. (That is, the number of positions on the last axis of
   *     the query.)
   * @param mdx Query
   * @param expectedResult Expected result string
   * @param freshConnection Whether fresh connection is required
   */
  protected void checkNative(
      int resultLimit, int rowCount, String mdx, String expectedResult, boolean freshConnection) {
    // Don't run the test if we're testing expression dependencies.
    // Expression dependencies cause spurious interval calls to
    // 'level.getMembers()' which create false negatives in this test.
    if (MondrianProperties.instance().TestExpDependencies.get() > 0) {
      return;
    }

    getConnection().getCacheControl(null).flushSchemaCache();
    try {
      Logger.getLogger(getClass()).debug("*** Native: " + mdx);
      boolean reuseConnection = !freshConnection;
      Connection con = getTestContext().withSchemaPool(reuseConnection).getConnection();
      RolapNativeRegistry reg = getRegistry(con);
      reg.useHardCache(true);
      TestListener listener = new TestListener();
      reg.setListener(listener);
      reg.setEnabled(true);
      TestCase c = new TestCase(con, resultLimit, rowCount, mdx);
      Result result = c.run();
      String nativeResult = TestContext.toString(result);
      if (!listener.isFoundEvaluator()) {
        fail("expected native execution of " + mdx);
      }
      if (!listener.isExecuteSql()) {
        fail("cache is empty: expected SQL query to be executed");
      }
      if (MondrianProperties.instance().EnableRolapCubeMemberCache.get()) {
        // run once more to make sure that the result comes from cache
        // now
        listener.setExecuteSql(false);
        c.run();
        if (listener.isExecuteSql()) {
          fail("expected result from cache when query runs twice");
        }
      }
      con.close();

      Logger.getLogger(getClass()).debug("*** Interpreter: " + mdx);

      getConnection().getCacheControl(null).flushSchemaCache();
      con = getTestContext().withSchemaPool(false).getConnection();
      reg = getRegistry(con);
      listener.setFoundEvaluator(false);
      reg.setListener(listener);
      // disable RolapNativeSet
      reg.setEnabled(false);
      result = executeQuery(mdx, con);
      String interpretedResult = TestContext.toString(result);
      if (listener.isFoundEvaluator()) {
        fail("did not expect native executions of " + mdx);
      }

      if (expectedResult != null) {
        TestContext.assertEqualsVerbose(
            expectedResult,
            nativeResult,
            false,
            "Native implementation returned different result than " + "expected; MDX=" + mdx);
        TestContext.assertEqualsVerbose(
            expectedResult,
            interpretedResult,
            false,
            "Interpreter implementation returned different result than " + "expected; MDX=" + mdx);
      }

      if (!nativeResult.equals(interpretedResult)) {
        TestContext.assertEqualsVerbose(
            interpretedResult,
            nativeResult,
            false,
            "Native implementation returned different result than " + "interpreter; MDX=" + mdx);
      }
    } finally {
      Connection con = getConnection();
      RolapNativeRegistry reg = getRegistry(con);
      reg.setEnabled(true);
      reg.useHardCache(false);
    }
  }
  public void testPositiveMatching() throws Exception {
    if (!MondrianProperties.instance().EnableNativeFilter.get()) {
      // No point testing these if the native filters
      // are turned off.
      return;
    }
    final String sqlOracle =
        "select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", \"fname\" || ' ' || \"lname\" as \"c4\", \"fname\" || ' ' || \"lname\" as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", \"fname\" || ' ' || \"lname\", \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having REGEXP_LIKE(\"fname\" || ' ' || \"lname\", '.*jeanne.*', 'i') order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, \"fname\" || ' ' || \"lname\" ASC NULLS LAST";
    final String sqlPgsql =
        "select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", fullname as \"c4\", fullname as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" as \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", fullname, \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having cast(fullname as text) ~ '(?i).*jeanne.*' order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, fullname ASC NULLS LAST";
    final String sqlMysql =
        "select `customer`.`country` as `c0`, `customer`.`state_province` as `c1`, `customer`.`city` as `c2`, `customer`.`customer_id` as `c3`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`, `customer`.`gender` as `c6`, `customer`.`marital_status` as `c7`, `customer`.`education` as `c8`, `customer`.`yearly_income` as `c9` from `customer` as `customer` group by `customer`.`country`, `customer`.`state_province`, `customer`.`city`, `customer`.`customer_id`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`), `customer`.`gender`, `customer`.`marital_status`, `customer`.`education`, `customer`.`yearly_income` having UPPER(c5) REGEXP '.*JEANNE.*' order by "
            + (TestContext.instance().getDialect().requiresOrderByAlias()
                ? "ISNULL(`c0`) ASC, `c0` ASC, "
                    + "ISNULL(`c1`) ASC, `c1` ASC, "
                    + "ISNULL(`c2`) ASC, `c2` ASC, "
                    + "ISNULL(`c4`) ASC, `c4` ASC"
                : "ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC, ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC, ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC, ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC");

    SqlPattern[] patterns = {
      new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length()),
      new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()),
      new SqlPattern(Dialect.DatabaseProduct.POSTGRESQL, sqlPgsql, sqlPgsql.length())
    };
    final String queryResults =
        "Axis #0:\n"
            + "{}\n"
            + "Axis #1:\n"
            + "{[Customers].[USA].[WA].[Issaquah].[Jeanne Derry], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "{[Customers].[USA].[CA].[Los Angeles].[Jeannette Eldridge], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "{[Customers].[USA].[CA].[Burbank].[Jeanne Bohrnstedt], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "{[Customers].[USA].[OR].[Portland].[Jeanne Zysko], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "{[Customers].[USA].[WA].[Everett].[Jeanne McDill], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "{[Customers].[USA].[CA].[West Covina].[Jeanne Whitaker], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "{[Customers].[USA].[WA].[Everett].[Jeanne Turner], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "{[Customers].[USA].[WA].[Puyallup].[Jeanne Wentz], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "{[Customers].[USA].[OR].[Albany].[Jeannette Bura], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "{[Customers].[USA].[WA].[Lynnwood].[Jeanne Ibarra], [Measures].[*FORMATTED_MEASURE_0]}\n"
            + "Row #0: 50\n"
            + "Row #0: 21\n"
            + "Row #0: 31\n"
            + "Row #0: 42\n"
            + "Row #0: 110\n"
            + "Row #0: 59\n"
            + "Row #0: 42\n"
            + "Row #0: 157\n"
            + "Row #0: 146\n"
            + "Row #0: 78\n";
    final String query =
        "With\n"
            + "Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Customers], Not IsEmpty ([Measures].[Unit Sales]))'\n"
            + "Set [*SORTED_COL_AXIS] as 'Order([*CJ_COL_AXIS],[Customers].CurrentMember.OrderKey,BASC,Ancestor([Customers].CurrentMember,[Customers].[City]).OrderKey,BASC)'\n"
            + "Set [*BASE_MEMBERS_Customers] as 'Filter([Customers].[Name].Members,[Customers].CurrentMember.Caption Matches (\"(?i).*\\Qjeanne\\E.*\"))'\n"
            + "Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'\n"
            + "Set [*CJ_COL_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Customers].currentMember)})'\n"
            + "Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Unit Sales]', FORMAT_STRING = 'Standard', SOLVE_ORDER=400\n"
            + "Select\n"
            + "CrossJoin([*SORTED_COL_AXIS],[*BASE_MEMBERS_Measures]) on columns\n"
            + "From [Sales]";
    assertQuerySqlOrNot(getTestContext(), query, patterns, false, true, true);
    assertQueryReturns(query, queryResults);
    verifySameNativeAndNot(query, null, getTestContext());
  }
 static TestContext setupMultiColDimCube(
     String aggName,
     String yearCols,
     String qtrCols,
     String monthCols,
     String monthProp,
     String defaultMeasure) {
   String cube =
       "<?xml version=\"1.0\"?>\n"
           + "<Schema name=\"FoodMart\">\n"
           + "  <Dimension name=\"Store\">\n"
           + "    <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">\n"
           + "      <Table name=\"store\"/>\n"
           + "      <Level name=\"Store Country\" column=\"store_country\" uniqueMembers=\"true\"/>\n"
           + "      <Level name=\"Store State\" column=\"store_state\" uniqueMembers=\"true\"/>\n"
           + "      <Level name=\"Store City\" column=\"store_city\" uniqueMembers=\"false\"/>\n"
           + "      <Level name=\"Store Name\" column=\"store_name\" uniqueMembers=\"true\">\n"
           + "        <Property name=\"Street address\" column=\"store_street_address\" type=\"String\"/>\n"
           + "      </Level>\n"
           + "    </Hierarchy>\n"
           + "  </Dimension>\n"
           + "  <Dimension name=\"Product\">\n"
           + "    <Hierarchy hasAll=\"true\" primaryKey=\"product_id\" primaryKeyTable=\"product\">\n"
           + "      <Join leftKey=\"product_class_id\" rightKey=\"product_class_id\">\n"
           + "        <Table name=\"product\"/>\n"
           + "        <Table name=\"product_class\"/>\n"
           + "      </Join>\n"
           + "      <Level name=\"Product Family\" table=\"product_class\" column=\"product_family\"\n"
           + "          uniqueMembers=\"true\"/>\n"
           + "      <Level name=\"Product Department\" table=\"product_class\" column=\"product_department\"\n"
           + "          uniqueMembers=\"false\"/>\n"
           + "      <Level name=\"Product Category\" table=\"product_class\" column=\"product_category\"\n"
           + "          uniqueMembers=\"false\"/>\n"
           + "    </Hierarchy>\n"
           + "  </Dimension>\n"
           + "<Cube name=\"ExtraCol\" defaultMeasure='#DEFMEASURE#'>\n"
           + "  <Table name=\"sales_fact_1997\">\n"
           + "           #AGGNAME# "
           + "  </Table>"
           + "  <Dimension name=\"TimeExtra\" foreignKey=\"time_id\">\n"
           + "    <Hierarchy hasAll=\"false\" primaryKey=\"time_id\">\n"
           + "      <Table name=\"time_by_day\"/>\n"
           + "      <Level name=\"Year\" #YEARCOLS#  type=\"Numeric\" uniqueMembers=\"true\""
           + "          levelType=\"TimeYears\">\n"
           + "      </Level>\n"
           + "      <Level name=\"Quarter\" #QTRCOLS#  uniqueMembers=\"false\""
           + "          levelType=\"TimeQuarters\">\n"
           + "      </Level>\n"
           + "      <Level name=\"Month\" #MONTHCOLS# uniqueMembers=\"false\" type=\"Numeric\""
           + "          levelType=\"TimeMonths\">\n"
           + "           #MONTHPROP# "
           + "      </Level>\n"
           + "    </Hierarchy>\n"
           + "  </Dimension>\n"
           + "  <Dimension name=\"Gender\" foreignKey=\"customer_id\">\n"
           + "    <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n"
           + "    <Table name=\"customer\"/>\n"
           + "      <Level name=\"Gender\" column=\"gender\" uniqueMembers=\"true\"/>\n"
           + "    </Hierarchy>\n"
           + "  </Dimension>  "
           + "  <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>"
           + "  <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>"
           + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n"
           + "      formatString=\"Standard\" visible=\"false\"/>\n"
           + "<Measure name=\"Avg Unit Sales\" column=\"unit_sales\" aggregator=\"avg\"\n"
           + "      formatString=\"Standard\" visible=\"false\"/>\n"
           + "  <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"\n"
           + "      formatString=\"#,###.00\"/>\n"
           + "<Measure name=\"Customer Count\" column=\"customer_id\" aggregator=\"distinct-count\" formatString=\"#,###\"/>"
           + "</Cube>\n"
           + "</Schema>";
   cube =
       cube.replace("#AGGNAME#", aggName)
           .replace("#YEARCOLS#", yearCols)
           .replace("#QTRCOLS#", qtrCols)
           .replace("#MONTHCOLS#", monthCols)
           .replace("#MONTHPROP#", monthProp)
           .replace("#DEFMEASURE#", defaultMeasure);
   return TestContext.instance().withSchema(cube);
 }
  public void testExplicitAggPropertiesOnAggTable() throws SQLException {
    TestContext testContext =
        setupMultiColDimCube(
            "    <AggName name=\"exp_agg_test_distinct_count\">\n"
                + "        <AggFactCount column=\"FACT_COUNT\"/>\n"
                + "        <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_s\" />\n"
                + "        <AggMeasure name=\"[Measures].[Customer Count]\" column=\"cust_cnt\" />\n"
                + "        <AggLevel name=\"[TimeExtra].[Year]\" column=\"testyear\" />\n"
                + "        <AggLevel name=\"[Gender].[Gender]\" column=\"gender\" />\n"
                + "        <AggLevel name=\"[Store].[Store Country]\" column=\"store_country\" />\n"
                + "        <AggLevel name=\"[Store].[Store State]\" column=\"store_st\" />\n"
                + "        <AggLevel name=\"[Store].[Store City]\" column=\"store_cty\" />\n"
                + "        <AggLevel name=\"[Store].[Store Name]\" column=\"store_name\" >\n"
                + "           <AggLevelProperty name='Street address' column='store_add' />"
                + "        </AggLevel>\n"
                + "    </AggName>\n",
            "column=\"the_year\"",
            "column=\"quarter\"",
            "column=\"month_of_year\" captionColumn=\"the_month\" ordinalColumn=\"month_of_year\"",
            "");

    String query =
        "with member measures.propVal as 'Store.CurrentMember.Properties(\"Street Address\")'"
            + "select { measures.[propVal], measures.[Customer Count], [Measures].[Unit Sales]} on columns, "
            + "non empty CrossJoin({[Gender].Gender.members},{[Store].[USA].[WA].[Spokane].[Store 16]}) on rows "
            + "from [ExtraCol]";
    assertQuerySql(
        testContext,
        query,
        mysqlPattern(
            "select\n"
                + "    `exp_agg_test_distinct_count`.`gender` as `c0`,\n"
                + "    `exp_agg_test_distinct_count`.`store_country` as `c1`,\n"
                + "    `exp_agg_test_distinct_count`.`store_st` as `c2`,\n"
                + "    `exp_agg_test_distinct_count`.`store_cty` as `c3`,\n"
                + "    `exp_agg_test_distinct_count`.`store_name` as `c4`,\n"
                + "    `exp_agg_test_distinct_count`.`store_add` as `c5`\n"
                + "from\n"
                + "    `exp_agg_test_distinct_count` as `exp_agg_test_distinct_count`\n"
                + "where\n"
                + "    (`exp_agg_test_distinct_count`.`store_name` = 'Store 16')\n"
                + "group by\n"
                + "    `exp_agg_test_distinct_count`.`gender`,\n"
                + "    `exp_agg_test_distinct_count`.`store_country`,\n"
                + "    `exp_agg_test_distinct_count`.`store_st`,\n"
                + "    `exp_agg_test_distinct_count`.`store_cty`,\n"
                + "    `exp_agg_test_distinct_count`.`store_name`,\n"
                + "    `exp_agg_test_distinct_count`.`store_add`\n"
                + "order by\n"
                + "    ISNULL(`exp_agg_test_distinct_count`.`gender`) ASC, `exp_agg_test_distinct_count`.`gender` ASC,\n"
                + "    ISNULL(`exp_agg_test_distinct_count`.`store_country`) ASC, `exp_agg_test_distinct_count`.`store_country` ASC,\n"
                + "    ISNULL(`exp_agg_test_distinct_count`.`store_st`) ASC, `exp_agg_test_distinct_count`.`store_st` ASC,\n"
                + "    ISNULL(`exp_agg_test_distinct_count`.`store_cty`) ASC, `exp_agg_test_distinct_count`.`store_cty` ASC,\n"
                + "    ISNULL(`exp_agg_test_distinct_count`.`store_name`) ASC, `exp_agg_test_distinct_count`.`store_name` ASC"));

    testContext.assertQueryReturns(
        "Store Address Property should be '5922 La Salle Ct'",
        query,
        "Axis #0:\n"
            + "{}\n"
            + "Axis #1:\n"
            + "{[Measures].[propVal]}\n"
            + "{[Measures].[Customer Count]}\n"
            + "{[Measures].[Unit Sales]}\n"
            + "Axis #2:\n"
            + "{[Gender].[F], [Store].[USA].[WA].[Spokane].[Store 16]}\n"
            + "{[Gender].[M], [Store].[USA].[WA].[Spokane].[Store 16]}\n"
            + "Row #0: 5922 La Salle Ct\n"
            + "Row #0: 45\n"
            + "Row #0: 12,068\n"
            + "Row #1: 5922 La Salle Ct\n"
            + "Row #1: 39\n"
            + "Row #1: 11,523\n");
    // Should use agg table for distinct count measure
    assertQuerySql(
        testContext,
        query,
        mysqlPattern(
            "select\n"
                + "    `exp_agg_test_distinct_count`.`testyear` as `c0`,\n"
                + "    `exp_agg_test_distinct_count`.`gender` as `c1`,\n"
                + "    `exp_agg_test_distinct_count`.`store_name` as `c2`,\n"
                + "    `exp_agg_test_distinct_count`.`unit_s` as `m0`,\n"
                + "    `exp_agg_test_distinct_count`.`cust_cnt` as `m1`\n"
                + "from\n"
                + "    `exp_agg_test_distinct_count` as `exp_agg_test_distinct_count`\n"
                + "where\n"
                + "    `exp_agg_test_distinct_count`.`testyear` = 1997\n"
                + "and\n"
                + "    `exp_agg_test_distinct_count`.`store_name` = 'Store 16'"));
  }
  public void testNativeFilterWithCompoundSlicer_1() {
    propSaver.set(MondrianProperties.instance().GenerateFormattedSql, true);
    final String mdx =
        "with member [measures].[avgQtrs] as 'count(filter([Customers].[Name].Members, [Measures].[Unit Sales] > 0))' "
            + "select [measures].[avgQtrs] on 0 from sales where ( {[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer], [Product].[Food].[Baked Goods].[Bread].[Muffins]} )";
    if (MondrianProperties.instance().EnableNativeFilter.get()
        && MondrianProperties.instance().EnableNativeNonEmpty.get()) {
      boolean requiresOrderByAlias = TestContext.instance().getDialect().requiresOrderByAlias();
      final String sqlMysql =
          propSaver.properties.UseAggregates.get() == false
              ? "select\n"
                  + "    `customer`.`country` as `c0`,\n"
                  + "    `customer`.`state_province` as `c1`,\n"
                  + "    `customer`.`city` as `c2`,\n"
                  + "    `customer`.`customer_id` as `c3`,\n"
                  + "    CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`,\n"
                  + "    CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`,\n"
                  + "    `customer`.`gender` as `c6`,\n"
                  + "    `customer`.`marital_status` as `c7`,\n"
                  + "    `customer`.`education` as `c8`,\n"
                  + "    `customer`.`yearly_income` as `c9`\n"
                  + "from\n"
                  + "    `customer` as `customer`,\n"
                  + "    `sales_fact_1997` as `sales_fact_1997`,\n"
                  + "    `time_by_day` as `time_by_day`,\n"
                  + "    `product_class` as `product_class`,\n"
                  + "    `product` as `product`\n"
                  + "where\n"
                  + "    `sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n"
                  + "and\n"
                  + "    `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n"
                  + "and\n"
                  + "    `time_by_day`.`the_year` = 1997\n"
                  + "and\n"
                  + "    `sales_fact_1997`.`product_id` = `product`.`product_id`\n"
                  //
                  + "and\n"
                  + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n"
                  + "and\n"
                  + "    `product_class`.`product_family` in ('Drink', 'Food')\n"
                  + "and\n"
                  + "    `product_class`.`product_department` in ('Alcoholic Beverages', 'Baked Goods')\n"
                  + "and\n"
                  + "    `product_class`.`product_category` in ('Beer and Wine', 'Bread')\n"
                  + "and\n"
                  + "    `product_class`.`product_subcategory` in ('Beer', 'Muffins')\n"
                  + "group by\n"
                  + "    `customer`.`country`,\n"
                  + "    `customer`.`state_province`,\n"
                  + "    `customer`.`city`,\n"
                  + "    `customer`.`customer_id`,\n"
                  + "    CONCAT(`customer`.`fname`, ' ', `customer`.`lname`),\n"
                  + "    `customer`.`gender`,\n"
                  + "    `customer`.`marital_status`,\n"
                  + "    `customer`.`education`,\n"
                  + "    `customer`.`yearly_income`\n"
                  + "having\n"
                  + "    (sum(`sales_fact_1997`.`unit_sales`) > 0)\n"
                  // ^^^^ This is what we are interested in. ^^^^
                  + "order by\n"
                  + (requiresOrderByAlias
                      ? "    ISNULL(`c0`) ASC, `c0` ASC,\n"
                          + "    ISNULL(`c1`) ASC, `c1` ASC,\n"
                          + "    ISNULL(`c2`) ASC, `c2` ASC,\n"
                          + "    ISNULL(`c4`) ASC, `c4` ASC"
                      : "    ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC,\n"
                          + "    ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC,\n"
                          + "    ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC,\n"
                          + "    ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC")
              : "select\n"
                  + "    `customer`.`country` as `c0`,\n"
                  + "    `customer`.`state_province` as `c1`,\n"
                  + "    `customer`.`city` as `c2`,\n"
                  + "    `customer`.`customer_id` as `c3`,\n"
                  + "    CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`,\n"
                  + "    CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`,\n"
                  + "    `customer`.`gender` as `c6`,\n"
                  + "    `customer`.`marital_status` as `c7`,\n"
                  + "    `customer`.`education` as `c8`,\n"
                  + "    `customer`.`yearly_income` as `c9`\n"
                  + "from\n"
                  + "    `customer` as `customer`,\n"
                  + "    `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n"
                  + "    `product_class` as `product_class`,\n"
                  + "    `product` as `product`\n"
                  + "where\n"
                  + "    `agg_c_14_sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n"
                  + "and\n"
                  + "    `agg_c_14_sales_fact_1997`.`the_year` = 1997\n"
                  + "and\n"
                  + "    `agg_c_14_sales_fact_1997`.`product_id` = `product`.`product_id`\n"
                  + "and\n"
                  + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n"
                  + "and\n"
                  + "    `product_class`.`product_family` in ('Drink', 'Food')\n"
                  + "and\n"
                  + "    `product_class`.`product_department` in ('Alcoholic Beverages', 'Baked Goods')\n"
                  + "and\n"
                  + "    `product_class`.`product_category` in ('Beer and Wine', 'Bread')\n"
                  + "and\n"
                  + "    `product_class`.`product_subcategory` in ('Beer', 'Muffins')\n"
                  + "group by\n"
                  + "    `customer`.`country`,\n"
                  + "    `customer`.`state_province`,\n"
                  + "    `customer`.`city`,\n"
                  + "    `customer`.`customer_id`,\n"
                  + "    CONCAT(`customer`.`fname`, ' ', `customer`.`lname`),\n"
                  + "    `customer`.`gender`,\n"
                  + "    `customer`.`marital_status`,\n"
                  + "    `customer`.`education`,\n"
                  + "    `customer`.`yearly_income`\n"
                  + "having\n"
                  + "    (sum(`agg_c_14_sales_fact_1997`.`unit_sales`) > 0)\n"
                  // ^^^^ This is what we are interested in. ^^^^
                  + "order by\n"
                  + "    ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC,\n"
                  + "    ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC,\n"
                  + "    ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC,\n"
                  + "    ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC";
      final SqlPattern[] patterns = mysqlPattern(sqlMysql);

      // Make sure the tuples list is using the HAVING clause.
      assertQuerySqlOrNot(getTestContext(), mdx, patterns, false, true, true);
    }
    // Make sure the numbers are right
    assertQueryReturns(
        mdx,
        "Axis #0:\n"
            + "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer]}\n"
            + "{[Product].[Food].[Baked Goods].[Bread].[Muffins]}\n"
            + "Axis #1:\n"
            + "{[Measures].[avgQtrs]}\n"
            + "Row #0: 1,281\n");
  }
  public void testDataSourceOverrideUserPass() throws SQLException, NamingException {
    // use the datasource property to connect to the database
    Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();
    final Dialect dialect = TestContext.instance().getDialect();
    if (dialect.getDatabaseProduct() == Dialect.DatabaseProduct.ACCESS) {
      // Access doesn't accept user/password, so this test is pointless.
      return;
    }

    final String jdbcUser = properties.get(RolapConnectionProperties.JdbcUser.name());
    final String jdbcPassword = properties.get(RolapConnectionProperties.JdbcPassword.name());
    if (jdbcUser == null || jdbcPassword == null) {
      // Can only run this test if username and password are explicit.
      return;
    }

    // Define a data source with bogus user and password.
    properties.put(RolapConnectionProperties.JdbcUser.name(), "bogususer");
    properties.put(RolapConnectionProperties.JdbcPassword.name(), "boguspassword");
    properties.put(RolapConnectionProperties.PoolNeeded.name(), "false");
    final StringBuilder buf = new StringBuilder();
    final DataSource dataSource = RolapConnection.createDataSource(null, properties, buf);
    final String desc = buf.toString();
    assertTrue(desc, desc.startsWith("Jdbc="));
    assertTrue(desc, desc.indexOf("JdbcUser=bogususer; JdbcPassword=boguspassword") >= 0);
    final String jndiName = "jndiDataSource";
    THREAD_INITIAL_CONTEXT.set(
        new InitialContext() {
          public Object lookup(String str) {
            return str.equals(jndiName) ? dataSource : null;
          }
        });

    // Create a property list that we will use for the actual mondrian
    // connection. Replace the original JDBC info with the data source we
    // just created.
    final Util.PropertyList properties2 = new Util.PropertyList();
    for (Pair<String, String> entry : properties) {
      properties2.put(entry.getKey(), entry.getValue());
    }
    properties2.remove(RolapConnectionProperties.Jdbc.name());
    properties2.put(RolapConnectionProperties.DataSource.name(), jndiName);

    // With JdbcUser and JdbcPassword credentials in the mondrian connect
    // string, the data source's "user" and "password" properties are
    // overridden and the connection succeeds.
    properties2.put(RolapConnectionProperties.JdbcUser.name(), jdbcUser);
    properties2.put(RolapConnectionProperties.JdbcPassword.name(), jdbcPassword);
    mondrian.olap.Connection connection = null;
    try {
      connection = DriverManager.getConnection(properties2, null);
      Query query = connection.parseQuery("select from [Sales]");
      final Result result = connection.execute(query);
      assertNotNull(result);
    } finally {
      if (connection != null) {
        connection.close();
        connection = null;
      }
    }

    // If we don't specify JdbcUser and JdbcPassword in the mondrian
    // connection properties, mondrian uses the data source's
    // bogus credentials, and the connection fails.
    properties2.remove(RolapConnectionProperties.JdbcUser.name());
    properties2.remove(RolapConnectionProperties.JdbcPassword.name());
    for (String poolNeeded : Arrays.asList("false", "true")) {
      // Important to test with & without pooling. Connection pools
      // typically do not let you change user, so it's important that
      // mondrian handles these right.
      properties2.put(RolapConnectionProperties.PoolNeeded.name(), poolNeeded);
      try {
        connection = DriverManager.getConnection(properties2, null);
        fail("Expected exception");
      } catch (MondrianException e) {
        final String s = TestContext.getStackTrace(e);
        assertTrue(
            s,
            s.indexOf("Error while creating SQL connection: " + "DataSource=jndiDataSource") >= 0);
        switch (dialect.getDatabaseProduct()) {
          case DERBY:
            assertTrue(
                s,
                s.indexOf(
                        "Caused by: java.sql.SQLException: " + "Schema 'BOGUSUSER' does not exist")
                    >= 0);
            break;
          case ORACLE:
            assertTrue(
                s,
                s.indexOf(
                        "Caused by: java.sql.SQLException: ORA-01017: "
                            + "invalid username/password; logon denied")
                    >= 0);
            break;
          case MYSQL:
            assertTrue(
                s,
                s.indexOf(
                        "Caused by: java.sql.SQLException: Access denied " + "for user 'bogususer'")
                    >= 0);
            break;
          case POSTGRESQL:
            assertTrue(
                s,
                s.indexOf(
                        "Caused by: org.postgresql.util.PSQLException: "
                            + "FATAL: password authentication failed for "
                            + "user \"bogususer\"")
                    >= 0);
            break;
        }
      } finally {
        if (connection != null) {
          connection.close();
          connection = null;
        }
      }
    }
  }