Cockroach: sql: support (more) array operators

[bra-fsn]

It would be nice to have array operators, like
postgres' array and range operators:
https://www.postgresql.org/docs/10/static/functions-array.html
https://www.postgresql.org/docs/10/static/functions-range.html

Especially I mean the following:
if an INT ARRAY 'a' contains [10,20,30]:

• [x] already existing concatenation (||) operator should allow ARRAYs:
  UPDATE table SET a = a || ARRAY[30,40,50] WHERE a[1] = 10; resulting a=[10,20,30,30,40,50]
  UPDATE table SET a = ARRAY[30,40,50] || a WHERE a[1] = 10; resulting a=[30,40,50,10,20,30]
• [ ] union (+) operator would enable appending only unique elements:
  UPDATE table SET a = a + ARRAY[30,40,50] WHERE a[1] = 10; resulting a=[10,20,30,40,50]
  UPDATE table SET a = ARRAY[30,40,50] + a WHERE a[1] = 10; resulting a=[40,50,10,20,30]
• [ ] difference (-) operator would enable removing specific elements:
  UPDATE table SET a = a - ARRAY[30,40,50] WHERE a[1] = 10; resulting a=[10,20]
  UPDATE table SET a = ARRAY[30,40,50] - a WHERE a[1] = 10; resulting a=[10,20]

About the use cases: there are a lot of questions about these topics (postgresql related for example), so I assume it's a general need.
Our use case is to store categories or placement (where is that exact entity, the location ID) for each rows where the above operators would make our life much more easier by not allowing redundant entries to the arrays.

[justinj]

We actually already support the first of your bullet points!

SELECT ARRAY[10, 20, 30] || ARRAY[30, 40, 50];

should work as you expect.

The other two operators you suggest are interesting and do seem useful - although I might almost prefer we implement them as functions rather than operators, something like ARRAY_UNION(a, b) and ARRAY_DIFFERENCE(a, b) (or a new overload of ARRAY_REMOVE). @knz @jordanlewis any thoughts?

[knz]

If folk want to perform set operations on data then array is a terrible choice. I fully support the idea of a "set" data type alongside "array" for this purpose though.

[justinj]

Postgres already overloads the concept of arrays as sets though. In particular, the array GIN indexing effectively treats them as sets, and a number of the builtin array functions, such as ARRAY_REMOVE make more sense if you think of Postgres arrays as being a substitute for sets. I agree that full on sets would be much better long term, but while we're sticking to a more Postgres mindset in terms of our datatypes (i.e., the near future) I can see this being a reasonable use of arrays as long as people are aware that the performance characteristics might not be exactly what they want.

[knz]

I'm not sure we want to emulate postgres there though. I think we should provide a set data type right away and define the features on top of that whenever it makes sense, then provide array/set conversions for compatibility.

[bra-fsn]

Thanks for the responses. Speaking of myself, I want the functionality, being it in arrays or sets doesn't really matter.
Of course having a set data type is perfectly OK for me, functions too. I just thought you want to stick with postgres as close as possible.
Now if you would support indexing these data types as well... :)

BTW, speaking of performance characteristics of arrays means that the implementation of sets could offer more in this regard? That would be awesome.

[bdarnell]

We are generally aiming for a high level of postgres compatibility, so I think implementing set operations on arrays (using the same operators as postgres) makes sense (even if we also have a more efficient set type). These operators are straightforward to implement and I don't see any downside to doing so.

[justinj]

The + and - operators being suggested here aren't a Postgres thing, actually. I think the only way to do unions and differences in Postgres as being requested here is to unnest and use SQL operators.

[knz]

We discussed this with Justin yesterday. One possible idea would be to use either an array or set representation in-memory while computing on SQL arrays, with the data structure changing depending on which operation is used. The on-disk format would still be an array.

[knz]

I would recommend a mini-RFC to sketch out.

[dianasaur323]

cc @awoods187

[bdarnell]

The "contains" operators <@ and @> would be useful too (we support these for JSON, we might as well add them for arrays).

[nvanbenschoten]

So would the array overlap operator &&, which evaluates to a boolean describing whether two arrays have any elements in common.

[jazoom]

I'm guessing this extends to JSONB as well? I'm trying to remove an item from an array and a simple query that works in Postgres doesn't work in CockroachDB.

create table catalog(sku text, properties jsonb);
insert into catalog values
('a', '[
    "one",
    "two",
    "three"
]')
returning *;  

update catalog
set properties = properties - 'two'     <== THIS PART DOESN'T WORK
returning *;

Here it is working in Postgres. Just select the Run it button to see the output.
http://rextester.com/SGUV84405

The opposite of this, with the || operator, works fine in CockroachDB.

It was frustrating to find this out via a long process of trial and error trying to manipulate JSONB arrays, especially since I'm fairly new to Postgres/CockroachDB. Not only does it fail, but it fails silently.

[knz]

@jazoom no this does not extend to JSON. I have forwarded your comment to #25665.

[bdarnell]

Postgres also supports the any/some operator for arrays. arrayVal = ANY(arrayVal2) returns true if the arrays (treated as sets) have a non-empty intersection. A gitter user was asking about using this syntax for a "contains" test:

SELECT * FROM brands WHERE domains = ANY('{"xxx.com"}'::text[]);

[mrtracy]

We've had some interest in this feature from Gitter, also of note (from user @jonwalch):

I think it would also make sense for using unsupported operators to fail loudly. For me they are failing silently

Perhaps these operators could be added as stubs to produce more explicit unsupported errors?

[knz]

Works for me.

Although to clarify: what are those operators that are currently failing silently?

[mrtracy]

It was reported that <@, &&, and & were failing silently, which was making a migration from postgres difficult.

[knz]

Please show examples.

Currently these operators are supported in cockroachdb insofar that clear error messages are already reported if they are used in a way that's not yet supported.

[mrtracy]

It appears that only && is not failing correctly, from Gitter:

root@:26257/defaultdb> select ARRAY[10,20,30] && ARRAY[20,50];
pq: unknown signature: inet_contains_or_contained_by(int[], int[])

The other two error as expected.

[knz]

ok thanks

[saeidakbari]

It's seems the && operator error still in place for array checking.
Any other news?

[knz]

@saeidakbari we welcome external contributions!

[saeidakbari]

I think the problem is for && operator only inet_contains_or_contained_by method is implemented as shown here
Am i right?

[knz]

The syntax is actually implemented here:

https://github.com/cockroachdb/cockroach/blob/b74a347f0cff4b23eb7a70dc2f426bc7e5a0901c/pkg/sql/parser/sql.y#L6646-L6649

The solution is to work both in this area and also define an additional function overload here:

https://github.com/cockroachdb/cockroach/blob/b74a347f0cff4b23eb7a70dc2f426bc7e5a0901c/pkg/sql/sem/builtins/builtins.go#L561-L576