Re: SQL/JSON revisited

On Tue, Mar 28, 2023 at 6:18 AM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> I ran sqlsmith on this patch for a short while, and reduced one of its
> appalling queries to this:
>
> postgres=# SELECT jsonb_object_agg_unique_strict('', null::xid8);
> ERROR: unexpected jsonb type as object key

I think this may have to do with the following changes to
uniqueifyJsonbObject() that the patch makes:

@@ -1936,7 +1942,7 @@ lengthCompareJsonbPair(const void *a, const void
*b, void *binequal)
* Sort and unique-ify pairs in JsonbValue object
*/
static void
-uniqueifyJsonbObject(JsonbValue *object)
+uniqueifyJsonbObject(JsonbValue *object, bool unique_keys, bool skip_nulls)
{
bool hasNonUniq = false;

@@ -1946,15 +1952,32 @@ uniqueifyJsonbObject(JsonbValue *object)
qsort_arg(object->val.object.pairs, object->val.object.nPairs,
sizeof(JsonbPair),
lengthCompareJsonbPair, &hasNonUniq);

- if (hasNonUniq)
+ if (hasNonUniq && unique_keys)
+ ereport(ERROR,
+ errcode(ERRCODE_DUPLICATE_JSON_OBJECT_KEY_VALUE),
+ errmsg("duplicate JSON object key value"));
+
+ if (hasNonUniq || skip_nulls)
{
- JsonbPair *ptr = object->val.object.pairs + 1,
- *res = object->val.object.pairs;
+ JsonbPair *ptr,
+ *res;
+
+ while (skip_nulls && object->val.object.nPairs > 0 &&
+ object->val.object.pairs->value.type == jbvNull)
+ {
+ /* If skip_nulls is true, remove leading items with null */
+ object->val.object.pairs++;
+ object->val.object.nPairs--;
+ }
+
+ ptr = object->val.object.pairs + 1;
+ res = object->val.object.pairs;

The code below the while loop does not take into account the
possibility that object->val.object.pairs would be pointing to garbage
when object->val.object.nPairs is 0.

Attached delta patch that applies on top of Alvaro's v12-0001 fixes
the case for me:

postgres=# SELECT jsonb_object_agg_unique_strict('', null::xid8);
jsonb_object_agg_unique_strict
--------------------------------
{}
(1 row)

postgres=# SELECT jsonb_object_agg_unique_strict('1', null::xid8);
jsonb_object_agg_unique_strict
--------------------------------
{}
(1 row)

SELECT jsonb_object_agg_unique_strict('1', '1'::xid8);
jsonb_object_agg_unique_strict
--------------------------------
{"1": "1"}
(1 row)

--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com