[GitHub] [druid] gianm opened a new issue #10051: Documentation for SQL ARRAY vs. multi-value VARCHAR behavior

[GitBox <gi...@apache.org>]

gianm opened a new issue #10051:
URL: https://github.com/apache/druid/issues/10051


   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]
   ```


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org