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);
}
}
/**
* 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();
}
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);
}
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");
}
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");
}
/**
* 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);
}
}
/**
* 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]");
}
}
}
protected Connection getFoodMartConnection() {
return TestContext.instance().getConnection();
}
/**
* 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]");
}
}
}
public static void checkNative(String mdx, Result expectedResult) {
BatchTestCase test = new BatchTestCase();
test.checkNative(
0, getRowCount(expectedResult), mdx, TestContext.toString(expectedResult), true);
}
/**
* 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;
}
}
}
}