Re: wrong query result with jit_above_cost= 0

> On 26 June 2018 at 20:23, Andres Freund <andres(at)anarazel(dot)de> wrote:
> Hi,
>
> On 2018-06-26 23:50:32 +0530, Rushabh Lathia wrote:
>> I found the below query which returns the wrong output
>> when jit_above_cost= 0 is set.
>>
>> Steps to reproduce:
>>
>> CREATE TABLE emp (
>> epno NUMERIC(4),
>> ename VARCHAR(10),
>> job VARCHAR(9),
>> mgr NUMERIC(4),
>> hiredate DATE,
>> sal NUMERIC(7,2),
>> comm NUMERIC(7,2),
>> deptno NUMERIC(2)
>> );
>>
>> INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
>> INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
>>
>> set jit_above_cost= 0;
>>
>> select max(epno) from emp group by rollup((deptno,epno)) order by 1 asc;
>>
>> without the ROLLUP, I don't see any problem with results.
>
> Interesting. I've opened an open item referencing this.

Hi,

Just out of curiosity, what exactly is wrong with the output of this query? I
see the same results with jit_above_cost = 0 and with the default value:

=# show jit_above_cost;
jit_above_cost
----------------
100000
(1 row)

=# select max(epno) from emp group by rollup((deptno,epno)) order by 1 asc;
max
------
7369
7499
7499
(3 rows)

=# set jit_above_cost = 0;
SET
=# select max(epno) from emp group by rollup((deptno,epno)) order by 1 asc;
max
------
7369
7499
7499
(3 rows)

And as far as I understand it's totally correct, since we do rollup by just two
values and have one more row as a total (with NULLs):

=# select max(epno), deptno, epno
from emp group by rollup((deptno,epno)) order by 1 asc;

max | deptno | epno
------+--------+------
7369 | 20 | 7369
7499 | NULL | NULL
7499 | 30 | 7499
(3 rows)