Documentation for SQL ARRAY vs. multi-value VARCHAR behavior

[gianm]

In SQL there are two ways we can represent multi-value things:

• SQL VARCHARs that, under the hood in native land, are actually multi-value strings.
• Honest-to-goodness SQL ARRAYs (i.e. from the ARRAY[...]) constructor.

The documentation does not make it clear how how behavior differs between these two, but it should, because the behavior does differ. One example is that currently (as of today), GROUP BY of a multi-value VARCHAR will involve an implicit un-nesting operation, whereas GROUP BY of an ARRAY will involve casting the array to a string.

Additionally, I think we need to add more unit tests; I don't see any for doing a GROUP BY of an ARRAY. The one I tried was:

SELECT ARRAY[CONCAT(dim1, 'word'),'up'], COUNT(*) FROM foo GROUP BY 1

And the results were:

["[10.1word, up]", 1]
["[1word, up]", 1]
["[2word, up]", 1]
["[abcword, up]", 1]
["[defword, up]", 1]
["[word, up]", 1]

If this were a multi-value VARCHAR, the results would be different; you'd expect something like:

["10.1word", 1]
["1word", 1]
["2word", 1]
["abcword", 1]
["defword", 1]
["word", 1]
["up", 6]