This issue affects specific queries against relational data sources authored in Report Studio, Query Studio, and Analysis Studio. End user could potentially see more data than originally anticipated.
Supported Product(s) Affected
- IBM Cognos 8 Business Intelligence Server 8.1
- IBM Cognos 8 Business Intelligence Server 8.2
- IBM Cognos 8 Business Intelligence Server 8.3
- IBM Cognos 8 Business Intelligence Server 8.4 First Customer Ship
Description
This is a specific case that is encountered when multiple nested queries (SELECT) and WHERE clauses are generated in a particular order. As a result of this issue, a WHERE clause is potentially eliminated causing the end user to see more data than originally anticipated. The issue is due to a defect in the query optimization engine that is eliminating a filter clause. The query engine optimizer prefers to do as much filtering as possible in the Native SQL, pushing the work to the data base server, rather than performing it locally to improve performance. This specific transformation defect occurs when the WITH clause syntax is turned off. This is a global setting available as a governor in the FM model and can be controlled per report in Report Studio.
Problem pre-conditions:
- The SQL must have a derived (aka "nested") table query, and that derived table must still exist after exiting from a transform step known within UDA as XFormAll. Otherwise, no push operation would be possible when the problem code is executed.
NB* Precondition 1 EXISTS if the projection list (i.e. the "SELECT" statement) from the reporting query contains any expressions. (COLUMN + ' ' is an expression)
NB* Precondition 1 does NOT exist if the projection list contained no expressions. (COLUMN by itself is a simple database reference, and is not an expression). In this case, the entire query has already been flattened into the single native query in prior code, and no derived table structure exists at the relevant point in time.
- The reporting query must have a filter condition of its own, and must fetch directly from the derived table, such that filters can be pushed from one to the other for useful gain. The use of WITH syntax eliminates the need to push filter conditions and thereby avoids the pushNode problem.
- The derived table must then have two or more separate (i.e. from different sources) filter conditions of its own. This is not in any sense "typical" but neither is it extraordinary: in the current scenario it happens because the table definition has a WHERE clause AND a join where the join condition is appended based on join rules. (These two filter conditions have not yet been merged together internally).
Structurally, the precondition looks just like this:
select ( B from
( select expressionA AS "B" from TBLX,
// pre-condition 1: this is a complex
// expression, not a simple column.
(select C from TBLY
where cond1) FA
where cond2 ) DT
// pre-condition 3: DT had two separate
// filter conditions. (cond2 will get lost as
// "cond3 AND cond1" are generated).
where cond3
// pre-condition 2: cond3 filters directly
// the result of
Problem:
The filter clause from the reporting query will be successfully pushed into the native (derived table) query, however if the derived table already had two or more un-merged filter clauses, then the second filter clause only (based on internal ordering) belonging to the derived table is dropped. Depending on the type of filter (WHERE clause) being dropped, the end user could potentially see more data than originally anticipated.
Suggested Action(s):
Install Product Download updates provided below to correct the issue. For IBM Cognos 8 Business Intelligence Server 8.4 First Customer Ship, contact Customer Support.
Workaround: Enabling the WITH clause syntax will also resolve the issue.
Failure to follow these important recommendations may result incorrect data provided to users. Cognos will not be liable for any loss or damages arising from the failure to implement these recommended directions.
