Re: wrong query result with jit_above_cost= 0

> On 26 June 2018 at 20:23, Andres Freund <andres@anarazel.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)