Hi, I found what appears to be an unsafe qual pushdown through a DISTINCT subquery when the qual contains a simple CASE expression.
A small example using citext is:
```
CREATE EXTENSION citext;
CREATE TABLE cit (t citext);
INSERT INTO cit VALUES ('a'), ('A'), ('b');
SELECT *
FROM (SELECT DISTINCT t FROM cit) d
WHERE (CASE t::text WHEN 'A' THEN 1 ELSE 0 END) = 1;
```
On master, the qual is pushed below the DISTINCT:
```text
Unique
-> Sort
Sort Key: cit.t
-> Seq Scan on cit
Filter: (CASE (t)::text WHEN 'A'::text THEN 1 ELSE 0 END = 1)
```
This is suspicious because DISTINCT compares `t` using citext
equality, under which `'a'` and `'A'` are equal, while the CASE
expression casts `t` to text and therefore distinguishes them.
Interestingly, writing the comparison directly does not result in pushdown:
```
SELECT *
FROM (SELECT DISTINCT t FROM cit) d
WHERE t::text = 'A';
```
In this case, the qual remains above the DISTINCT:
```text
Subquery Scan on d
Filter: ((d.t)::text = 'A'::text)
-> Unique
-> Sort
Sort Key: cit.t
-> Seq Scan on cit
```
The first example by itself does not demonstrate a deterministic wrong
result, since plain DISTINCT does not specify which representation is
retained when `'a'` and `'A'` compare equal as citext.
The problem can be demonstrated deterministically with DISTINCT ON:
```sql
SELECT *
FROM
(
SELECT DISTINCT ON (t) t
FROM cit
ORDER BY t, t::text COLLATE "C"
) d
WHERE CASE t::text WHEN 'a' THEN 1 ELSE 0 END = 1;
```
`DISTINCT ON (t)` groups `'a'` and `'A'` using citext equality, while
the secondary sort key ensures that `'A'` is selected from that group.
Therefore, if the outer qual is evaluated after the subquery as
required, `'A'` does not satisfy the text comparison with `'a'`, and
the query returns no rows.
On master, however, the CASE qual is pushed below the DISTINCT ON. It
filters out `'A'` first and leaves `'a'`, causing the query to return
one row.
I believe the problem is in the special handling of simple CASE
expressions in `grouping_conflict_walker()`.
The comment there currently explains that a simple CASE is a
comparison in disguise: parse analysis stores the CASE argument
separately and represents each WHEN comparison using a `CaseTestExpr`
in its place. It then states that the WHEN operators are always the
type-default `=`, matching the grouping equality operator, and
therefore only a collation conflict needs to be considered.
That assumption does not hold when the CASE argument changes the
equality semantics of a grouping column.
For example, in:
```
CASE t::text WHEN 'A' THEN ...
```
the grouping column is `t` of type citext, so DISTINCT uses
`citext_eq`. But the CASE argument is `t::text`, and the type-default
equality operator used by the WHEN comparison is therefore `text_eq`.
Thus we have:
```
grouping equality operator: citext_eq
CASE WHEN equality operator: text_eq
```
The collation of `t` can still be deterministic, so the existing check
for nondeterministic collations does not catch this case.
For an ordinary expression such as:
```
t::text = 'A'
```
`grouping_conflict_walker()` keeps the grouping operand and the
comparison operator together and `grouping_check_operand()` correctly
detects that `text_eq` is not compatible with the grouping equality
operator.
For a simple CASE, however, the `CaseTestExpr` representation
separates the CASE argument from the WHEN comparison operator. The
existing special handling restores enough information to check
collation conflicts, but does not check equality-operator
compatibility.
The attached patch fixes this by checking the CASE argument against
the equality operator and input collation of each WHEN comparison
using the existing `grouping_check_operand()` logic. It also adds a
regression test using the deterministic DISTINCT ON example above.
--
Thanks,
Tender Wang
0001-Fix-case-when.patch
Description: Binary data
