ArrayField: Bug on exact_nested_null

[Ion Alberdi]

Hello to all, 

a unit test added at
https://github.com/pricemoov/django/pull/2
shows that the exact filter on a full "NULL nested array" currently fails.
(the commit messages shows how to reproduce the error).

More precisely, the error is
"django.db.utils.DataError: invalid input syntax for type integer: "{NULL,NULL}"

    LINE 1: ...ullableintegerarraymodel"."field_nested" = (ARRAY['{NULL,NUL..."

and seems to be due to the query being generated as "...ARRAY['{NULL,NULL}']..."
instead of "...ARRAY[ARRAY[NULL,NULL]]..."

I wonder whether this is due to django or psycopg2. Indeed, the bug is not reproduced
if at least one of the element in the nested array is not null.

Is the bug worth creating a ticket?

Best regards,

[Adam Johnson]

Yes please create a ticket. If you’re feeling brave, try to adapt your test into Django’s test suite (here: https://github.com/django/django/blob/84206607d6bfd61e7f7a88b51163ffd4153e3b5a/tests/postgres_tests/test_array.py#L212 )!

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/bad3435e-1299-4b1d-b701-7881e111ec90n%40googlegroups.com.

[Ion Alberdi]

Great, thanks Adam! FYI the test is already on django's Test suite
(https://github.com/pricemoov/django/pull/2/files)
Would you like me to open a PR in Django or should I wait for the fix of the test being developed first?

You received this message because you are subscribed to a topic in the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-developers/4OvbN0EUUsM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-develop...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAMyDDM1Rs%3DxZjChzqU3qj6YttB3atLX7bhGuURd0-3v_OitK0g%40mail.gmail.com.

--

Ion Alberdi

[Adam Johnson]

Ah sorry didn't realize.

I would just link to the test from your ticket for now, the fellows are probably in a better position to determine how to treat this bug.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CANbgw4B81hwFvRhCVQpm%3DPg%3D_ZJikTVKKhAaXa%2B7rPpw8%3DoVEw%40mail.gmail.com.

[Ion Alberdi]

No pb & crystal clear, ticket created at
https://code.djangoproject.com/ticket/34080#ticket
thanks again Adam!

[Jörg Breitbart]

Not sure if it is related, but the last sentence here caught my attention:

> I wonder whether this is due to django or psycopg2. Indeed, the bug is
> not reproduced
> if at least one of the element in the nested array is not null.

When doing a COPY FROM replacement of bulk_update for postgres I
stumbled over several weird edge cases of nested arrays and its NULL
behavior - documented here
https://github.com/netzkolchose/django-fast-update/blob/59b0e6fa6affdcf8c66b2edee2e1bf1b926f4180/fast_update/copy.py#L566

This seems to be a postgres internal thing and how they implemented
ARRAY, as it is reproducible in pgsql as well. Imho the important bit
is, that ARRAY will drop the top level array to {} (empty array with no
dimension info), if there is at least one empty sub array declared with
ARRAY among NULLs, while if all sub entries are NULL the top level array
will still manifest with dimension info, but now with entries set to
NULL. Thats a quite weird behavior imho.


Am 09.10.22 um 12:49 schrieb Ion Alberdi:

> --
> You received this message because you are subscribed to the Google
> Groups "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-develop...@googlegroups.com

> <mailto:django-develop...@googlegroups.com>.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/django-developers/bad3435e-1299-4b1d-b701-7881e111ec90n%40googlegroups.com <https://groups.google.com/d/msgid/django-developers/bad3435e-1299-4b1d-b701-7881e111ec90n%40googlegroups.com?utm_medium=email&utm_source=footer>.

--
netzkolchose.de UG (haftungsbeschränkt)
Geschäftsführer: Jörg Breitbart
Handelsregister: HRB 504791 Amtsgericht Jena
Steuer-Nr.: 161/115/07450
USt-IdNr.: DE268234065

[Ion Alberdi]

>Imho the important bit
> is, that ARRAY will drop the top level array to {} (empty array with no
> dimension info), if there is at least one empty sub array declared with
> ARRAY among NULLs, while if all sub entries are NULL the top level array
> will still manifest with dimension info, but now with entries set to
> NULL. Thats a quite weird behavior imho.

Indeed, thanks for sharing that Jörg!

Just to be sure, on postgresql I have, 
psql> select version();
PostgreSQL 12.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit

psql>select (array[array[NULL,NULL]]);
{{NULL,NULL}} 

psql>select array['{NULL, NULL}'] ;
{"{NULL, NULL}"}

The third is correct, but not the generated query I'd expect when writing `nested_field=[[None, None]]` in the ORM.
(It outputs what I'd expect from `nested_field=["{NULL, NULL}"]`).

I wonder whether this bug could not be fixed in django by generating the additional "array" in nested fields,
(I did not yet figure out how the additional array is generated when at least one non NULL field is put there, it is in my todos :)).
WDYT?

You received this message because you are subscribed to a topic in the Google Groups "Django developers  (Contributions to Django itself)" group.

To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-developers/4OvbN0EUUsM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-develop...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/ca23d835-08cc-1c7a-323c-faa552560ef6%40netzkolchose.de.

[Jörg Breitbart]

@Ion

Well I am not deep enough into django's ArrayField for postgres to tell,
which one is the desired output or right behavioral pattern. For my COPY
FROM rewrite I simply tested several edge cases and tried to get as
close as possible to ArrayField.

From postgres side of things the following is given (trying to recap my
old findings):
- arrays only respect NOT NULL at top level (whole array)
- for array entries there seems to be no way to forbid NULL
- ARRAY constructor is not fully eqivalent to inline '{}' construction
(thats not mentioned in postgres docs!)

When playing around with inline {} construction vs. ARRAY constructor it
kinda seems, that only ARRAY does certain sanity checks:
- multi dimension balance check
- empty reduction to {}

At this point I think ArrayField should always use ARRAY constructor to
benefit from the additional sanity checks. But as noted earlier, ARRAY
also exposes this weird behavior:

postgres=# select ARRAY[null,null]::int[]; --> {NULL,NULL}
postgres=# select ARRAY[null,ARRAY[]]::int[]; --> {}
postgres=# select ARRAY[ARRAY[1,2],ARRAY[3,4]]::int[]; --> {{1,2},{3,4}}


which prolly can only be circumvented by some consistency checks in python.


Now to the question, whats the right value in DB for
`nested_field=[[None, None]]`:

Imho if the None here represents a non-array type, things are clear - it
should get constructed just like that:

postgres=# select ARRAY[ARRAY[null, null]]; --> {{NULL,NULL}}

If None represents an array-type itself (nested ArrayField), things are
not so clear anymore, as we have again the issue, whether to treat
things in the datatype itself (empty container) vs. sql-null:

as empty data container:
select ARRAY[ARRAY[ARRAY[], ARRAY[]]]::int[]; --> {}

as sql-null:
select ARRAY[ARRAY[null, null]]; --> {{NULL,NULL}}

From pure interface idea this could both be handled by
`null=True|False` argument on the nested ArrayField. But note, that the
ugly reduction on postgres side to {} for empty containers (thus
null=False) creates tons of implementation edge cases during querying,
as all dimension info is lost. So it might be better to always treat
nested ArrayFields as sql-null to preserve some of that info.

Not sure if any of that helps you, also changing ArrayField behavior too
much might not be wanted at all.

Overall arrays and even more nested arrays (multidimensional) in
postgres feel somehow like a bad hack with their non-stable dimension,
which imho results from the fact, that postgres does not store that info
during column construction (int[] == int[]...[]). Bummer.

[Ion Alberdi]

Hi Jörg!

>At this point I think ArrayField should always use ARRAY constructor to

benefit from the additional sanity checks. But as noted earlier, ARRAY
also exposes this weird behavior:

Definitely, FYI Daniele Varrazzo from psycopg2 said 
https://github.com/psycopg/psycopg2/issues/1507
that
>ARRAY[] is an always entertaining booby trap.
and that they plan not to use any ARRAY anymore in psycopg3.

Now, to fix https://code.djangoproject.com/ticket/34080
I'll try to modify https://github.com/django/django/blob/84206607d6bfd61e7f7a88b51163ffd4153e3b5a/django/contrib/postgres/fields/array.py#L238-L252
as so that the generated template is
> "ARRAY[%s::integer[]], "
instead of
>"ARRAY[%s]:integer[][]"
when we have NULL arrays.
Still, I'm not sure this behavior is what the django project would like to follow.
Your input on this proposal would be very welcome, whether here or in the track ticket.

In any case, I'll put all related info at https://code.djangoproject.com/ticket/34080

Thanks again!

--
You received this message because you are subscribed to a topic in the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-developers/4OvbN0EUUsM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-develop...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/a045a12e-6370-b792-9b96-35fd21566be2%40netzkolchose.de.

[Ion Alberdi]

Hello to all!
FYI a PR is available at
https://github.com/django/django/pull/16175
So far the PR unwraps ARRAY and lets psycopg2 handle these use cases.