Re: wrong query result with jit_above_cost= 0

> On 26 June 2018 at 22:11, Andres Freund <andres@anarazel.de> wrote:
> On 2018-06-26 22:09:10 +0200, Dmitry Dolgov wrote:
>> > 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)
>
> 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.