Invalid filter propagation for expressions in PARTITION BY

Detail name	Value
Changelog Number	12622
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 6.2.16, Exasol 7.0.12, Exasol 7.1.1
Resolution Date	2021-08-05

Background

To speed up the query execution, the Exasol database propagates filters to subqueries if this is valid.

Description

A query returns wrong results due to an invalid filter propagation if the following conditions are met:

• The subquery contains an analytic function AF.
• The select list of the subquery contains an expression EX that uses a column from a table, view, or subselect.
• The PARTITION BY clause of AF contains another expression that uses EX as input and modifies it.
• The outer query uses a filter on EX.

Preparation

CREATE SCHEMA test;
CREATE OR REPLACE TABLE t(a VARCHAR(100));
INSERT INTO T VALUES ('x');
INSERT INTO T VALUES ('xa');
INSERT INTO T VALUES ('xa');

Example:

-- cnt returns 2 instead of the correct value 3
SELECT * FROM
(
SELECT lower(a) AS column_name1,
count(*) OVER (PARTITION BY replace(lower(a), 'a', '')) AS cnt
FROM t
)
WHERE column_name1 LIKE '%a';

Workaround

A) Break one of the conditions above. For example, avoid EX (here lower(a)) in the PARTITION BY clause.

SELECT * FROM
(
SELECT lower(a) AS column_name1,
count(*) OVER (PARTITION BY replace(lower(upper(a)), 'a', '')) AS cnt
FROM t
)
WHERE column_name1 LIKE '%a';

B) Use another analytic function in the subquery that prevents filter propagation:

SELECT * FROM
(
SELECT lower(a) AS column_name1,
count(*) OVER (PARTITION BY replace(lower(a), 'a', '')) AS cnt,
count(a) OVER()
FROM t
)
WHERE column_name1 LIKE '%a';

Fix

The query returns the correct results and throws no error.