Re: wrong query result with jit_above_cost= 0

> On 26 June 2018 at 22:11, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2018-06-26 22:09:10 +0200, Dmitry Dolgov wrote:
>> > 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)
>
> I've not reproduced the problem yet (I'm deep in a review / edit of
> another patchset). Could it be that you've not compiled with JIT
> support and thus don't see the problem Rushab was complaining about?
> SELECT pg_jit_available();

Yep, my bad, forgot to turn it on. Now I see what's the problem, one of the
null fields is screwed up, will try to figure out why is that.