OBI SERVER GENERATES INCORRECT SQL WHEN ‘BETWEEN’ AND ‘OR’ CLAUSES EXCEED EIGHT IN WHERE CLAUSE
When running a report using eight BETWEEN clauses or less for a report that should bring back zero rows, for example
SELECT Products.Brand saw_0 FROM Paint
WHERE (Products.Brand BETWEEN ’0′ AND ’1′)
OR (Products.Brand BETWEEN ’1′ AND ’2′)
OR (Products.Brand BETWEEN ’2′ AND ’3′)
OR (Products.Brand BETWEEN ’3′ AND ’4′)
OR (Products.Brand BETWEEN ’4′ AND ’5′)
OR (Products.Brand BETWEEN ’5′ AND ’6′)
OR (Products.Brand BETWEEN ’6′ AND ’7′)
OR (Products.Brand BETWEEN ’7′ AND ’8′)
ORDER BY saw_0
the following warning is displayed:
“…
No Results
The specified criteria didn’t result in any data. This is often caused by applying filters that are too restrictive or that contain incorrect values. Please check your Request Filters and try again. The filters currently being applied are shown below ..”
This is the correct result.
However, when an additional BETWEEN clause is added (i.e nine BETWEEN clauses in total) for example
SELECT Products.Brand saw_0 FROM Paint
WHERE (Products.Brand BETWEEN ’0′ AND ’1′)
OR (Products.Brand BETWEEN ’1′ AND ’2′)
OR (Products.Brand BETWEEN ’2′ AND ’3′)
OR (Products.Brand BETWEEN ’3′ AND ’4′)
OR (Products.Brand BETWEEN ’4′ AND ’5′)
OR (Products.Brand BETWEEN ’5′ AND ’6′)
OR (Products.Brand BETWEEN ’6′ AND ’7′)
OR (Products.Brand BETWEEN ’7′ AND ’8′)
OR (Products.Brand BETWEEN ’8′ AND ’9′)
ORDER BY saw_0
the result should also return zero rows but this report returns all rows in the table.
In the query using nine BETWEEN clauses, the WHERE clause of the SQL generated does not include the BETWEEN filter conditions hence all rows in the table are returned.
It was determined to be a bug – OBI SERVER GENERATES INCORRECT SQL WHEN BETWEEN AND OR CLAUSES EXCEED EIGHT, has been raised to address this sissue.
The issue is that for the case when there are more than 8 filters, e.g 9 filters in a query, OBI Server seems to drop the filter with the result that it erroneously returns incorrect number of rows.