I'm curious why the following two expressions have a different result -- one NULL and the other TRUE:
postgres=# select array[1,null]=array[1,null];
?column?
----------
t
(1 row)
postgres=# select (1,null)=(1,null);
?column?
----------
(1 row)
What's the reasons for this? And in your answer could you please link to the docs in Postgres or some SQL reference where it says the behavior of the row- and list-type when comparing with a NULL value inside?
Arrays containing null in the same position(s) compare equal if all else is equal. Only actual null values (including array values as a whole) return null when compared:
SELECT null::int[] = null::int[]; --> null
For row comparison, Postgres follows the SQL standard. The manual on Row and Array Comparisons:
The forms involving array subexpressions are PostgreSQL extensions; the rest are SQL-compliant.
The manual on Row Constructor Comparison:
The
=and<>cases work slightly differently from the others. Two rows are considered equal if all their corresponding members are non-null and equal; the rows are unequal if any corresponding members are non-null and unequal; otherwise the result of the row comparison is unknown (null).
Bold emphasis mine. So row comparison is subtly different from array comparison. If nested null values in row values shall compare equal (like in arrays), use IS NOT DISTINCT FROM instead of =:
SELECT (1,null) IS NOT DISTINCT FROM (1,null); --> true
source: https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/utils/adt/arrayfuncs.c#n3760
explain first query select array[1,null]=array[1,null];
3784-3787 compare two array base element data type.
3790-3793 two array dimension/bounds info check.
3796-3813 check weather array base types have equality function. some data type don't have equality function.
3825-3828 set up iteration for two array.
3838-3853 for each base element in an array, either Datum or null. so iterate and copy datum or null (one element either datum (not null) or null) to variable (it1, isnull1, it2, isnull2).
You answer in line 3845 and line 3848.
src/backend/utils/adt/arrayfuncs.c
3760: Datum
3761: array_eq(PG_FUNCTION_ARGS)
3762: {
3763: LOCAL_FCINFO(locfcinfo, 2);
3764: AnyArrayType *array1 = PG_GETARG_ANY_ARRAY_P(0);
3765: AnyArrayType *array2 = PG_GETARG_ANY_ARRAY_P(1);
3766: Oid collation = PG_GET_COLLATION();
3767: int ndims1 = AARR_NDIM(array1);
3768: int ndims2 = AARR_NDIM(array2);
3769: int *dims1 = AARR_DIMS(array1);
3770: int *dims2 = AARR_DIMS(array2);
3771: int *lbs1 = AARR_LBOUND(array1);
3772: int *lbs2 = AARR_LBOUND(array2);
3773: Oid element_type = AARR_ELEMTYPE(array1);
3774: bool result = true;
3775: int nitems;
3776: TypeCacheEntry *typentry;
3777: int typlen;
3778: bool typbyval;
3779: char typalign;
3780: array_iter it1;
3781: array_iter it2;
3782: int i;
3783:
3784: if (element_type != AARR_ELEMTYPE(array2))
3785: ereport(ERROR,
3786: (errcode(ERRCODE_DATATYPE_MISMATCH),
3787: errmsg("cannot compare arrays of different element types")));
3788:
3789: /* fast path if the arrays do not have the same dimensionality */
3790: if (ndims1 != ndims2 ||
3791: memcmp(dims1, dims2, ndims1 * sizeof(int)) != 0 ||
3792: memcmp(lbs1, lbs2, ndims1 * sizeof(int)) != 0)
3793: result = false;
3794: else
3795: {
3796: /*
3797: * We arrange to look up the equality function only once per series of
3798: * calls, assuming the element type doesn't change underneath us. The
3799: * typcache is used so that we have no memory leakage when being used
3800: * as an index support function.
3801: */
3802: typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
3803: if (typentry == NULL ||
3804: typentry->type_id != element_type)
3805: {
3806: typentry = lookup_type_cache(element_type,
3807: TYPECACHE_EQ_OPR_FINFO);
3808: if (!OidIsValid(typentry->eq_opr_finfo.fn_oid))
3809: ereport(ERROR,
3810: (errcode(ERRCODE_UNDEFINED_FUNCTION),
3811: errmsg("could not identify an equality operator for type %s",
3812: format_type_be(element_type))));
3813: fcinfo->flinfo->fn_extra = (void *) typentry;
3814: }
3815: typlen = typentry->typlen;
3816: typbyval = typentry->typbyval;
3817: typalign = typentry->typalign;
3818:
3819: /*
3820: * apply the operator to each pair of array elements.
3821: */
3822: InitFunctionCallInfoData(*locfcinfo, &typentry->eq_opr_finfo, 2,
3823: collation, NULL, NULL);
3824:
3825: /* Loop over source data */
3826: nitems = ArrayGetNItems(ndims1, dims1);
3827: array_iter_setup(&it1, array1);
3828: array_iter_setup(&it2, array2);
3829:
3830: for (i = 0; i < nitems; i++)
3831: {
3832: Datum elt1;
3833: Datum elt2;
3834: bool isnull1;
3835: bool isnull2;
3836: bool oprresult;
3837:
3838: /* Get elements, checking for NULL */
3839: elt1 = array_iter_next(&it1, &isnull1, i,
3840: typlen, typbyval, typalign);
3841: elt2 = array_iter_next(&it2, &isnull2, i,
3842: typlen, typbyval, typalign);
3843:
3844: /*
3845: * We consider two NULLs equal; NULL and not-NULL are unequal.
3846: */
3847: if (isnull1 && isnull2)
3848: continue;
3849: if (isnull1 || isnull2)
3850: {
3851: result = false;
3852: break;
3853: }
3854:
3855: /*
3856: * Apply the operator to the element pair; treat NULL as false
3857: */
3858: locfcinfo->args[0].value = elt1;
3859: locfcinfo->args[0].isnull = false;
3860: locfcinfo->args[1].value = elt2;
3861: locfcinfo->args[1].isnull = false;
3862: locfcinfo->isnull = false;
3863: oprresult = DatumGetBool(FunctionCallInvoke(locfcinfo));
3864: if (locfcinfo->isnull || !oprresult)
3865: {
3866: result = false;
3867: break;
3868: }
3869: }
3870: }
3871:
3872: /* Avoid leaking memory when handed toasted input. */
3873: AARR_FREE_IF_COPY(array1, 0);
3874: AARR_FREE_IF_COPY(array2, 1);
3875:
3876: PG_RETURN_BOOL(result);
3877: }
3878:
