Re: Extract numeric filed in JSONB more effectively

On Tue, Aug 15, 2023 at 1:24 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> Hi
>
> út 15. 8. 2023 v 5:24 odesílatel Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
> napsal:
>
>>
>>> jsonb_extract_xx_type just cares about the argtype, but
>>> 'explain select xx' will still access the const->constvalue.
>>> const->constvalue is 0 which is set by makeNullConst currently,
>>> and it is ok for the current supported type.
>>>
>>
>> The exception is numeric data type, the constvalue can't be 0.
>> so hack it with the below line. maybe not good enough, but I
>> have no better solution now.
>>
>> + Const *target =
>> makeNullConst(fexpr->funcresulttype,
>> +
>> -1,
>> +
>> InvalidOid);
>> + /*
>> + * Since all the above functions are strict, we
>> can't input
>> + * a NULL value.
>> + */
>> + target->constisnull = false;
>> +
>> + Assert(target->constbyval || target->consttype ==
>> NUMERICOID);
>> +
>> + /* Mock a valid datum for !constbyval type. */
>> + if (fexpr->funcresulttype == NUMERICOID)
>> + target->constvalue =
>> DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
>>
>>
> Personally I think this workaround is too dirty, and better to use a
> strict function (I believe so the overhead for NULL values is acceptable).
>

In the patch v8, I created a new routine named makeDummyConst,
which just sits by makeNullConst. It may be helpful to some extent.
a). The code is self-document for the user/reader. b). We have a
central place to maintain this routine.

Besides the framework, the troubles for the reviewer may be if the
code has some corner case issue or behavior changes. Especially
I have some code refactor when working on jsonb_extract_path.
so the attached test.sql is designed for this. I have compared the
result between master and patched version and I think reviewer
can do some extra testing with it.

v8 is the finished version in my mind, so I think it is ready for review
now.

--
Best Regards
Andy Fan