sql: array_length not showing error message for multidimensional array with non matching dimensions

[techytoes]

Running a query in postgreSQL generates this:

postgres=# SELECT array_length(ARRAY[['a', 'b', 'c'], ['d']], 1);
ERROR:  multidimensional arrays must have array expressions with matching dimensions

whereas running a similar query in cockroachDB still returns a response without any error message.

demo@127.0.0.1:26257/movr> SELECT array_length(ARRAY[['a', 'b', 'c'], ['d']], 1);
  array_length
----------------
             2
(1 row)

My initial understanding of this problem is that this is probably because we missed a check to see if the number of columns in each array are same.

Jira issue: CRDB-8924

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

Thanks for this issue!

I’m surprised the statement works in CRDB at all. In general, CRDB does not support multi-dimensional arrays. It seems like we might be missing a check for a multi-dimensional array somewhere. If I had to guess, it could be an issue with constant expressions.

[rafiss]

It looks like we already have validation for the other array format:

root@:26257/defaultdb> select array_length('{{1,2},{3,4}}'::int[], 2);
ERROR: array_length(): could not parse "{{1,2},{3,4}}" as type int[]: unimplemented: nested arrays not supported
SQLSTATE: 0A000

I think we should look for the place where the ARRAY[] syntax is parsed/implemented, and prevent it from allowing a multi-dimensional array.

[techytoes]

@rafiss, you are right. I wanted to ask one more thing, do we allow multidimensional array for any other array functions?

Thanks!

[rafiss]

Sorry, I missed that question. No, I don't believe we allow multi-dimensional arrays anywhere else either.

However, after coming back to this issue after a while, I think it might be a good idea to fix this by matching the Postgres error message: when we evaluate the array expression, we can check for the array lengths. see https://github.com/cockroachdb/cockroach/blob/676dc076dc6a137d88eac9217764bbb63beae86f/pkg/sql/sem/tree/eval.go#L4490-L4506

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!