BITNOT(BIT_NAND_AGG(...) OVER ()) produces wrong result #3512
Description
Consider this query:
select
bitnot(bit_nand_agg(v) over ()) c1,
bit_nand_agg(v) over () c2,
bitnot(bit_nor_agg (v) over ()) c3,
bit_nor_agg (v) over () c4,
bitnot(bit_xnor_agg(v) over ()) c5,
bit_xnor_agg(v) over () c6
from (values (0)) t (v)All the results from the BITNOT() functions are NULL, yet they should all be 0:
|C1 |C2 |C3 |C4 |C5 |C6 |
|---|---|---|---|---|---|
| |-1 | |-1 | |-1 |
Workaround, compute the BITNOT() expression from columns from a derived table:
select
bitnot(c2) c1, c2,
bitnot(c4) c3, c4,
bitnot(c6) c5, c6
from (
select
bit_nand_agg(v) over () c2,
bit_nor_agg (v) over () c4,
bit_xnor_agg(v) over () c6
from (values (0)) t (v)
) tNow, the results are correct
|C1 |C2 |C3 |C4 |C5 |C6 |
|---|---|---|---|---|---|
|0 |-1 |0 |-1 |0 |-1 |