<p dir="auto">Selecting certain columns from certain SQL queries frequently returns the following error: <code class="notranslate">Unknown exception: Cannot coerce[java.util.ArrayList] to VARCHAR</code></p>
<p dir="auto">Which columns/queries that are affected seems to be non-deterministic, though I can always make a given query work by excluding particular columns. Eg. for one query, I cannot select column X without having an error returned, but for a different query I cannot select column Y. Sometimes a query can work for some time, and then start failing, despite pulling in the exact same set of data. In the case of this error, the columns in question seem to be always empty (determined by cross-referencing the column/query in question over the JSON query API).</p>
<p dir="auto">Doing a <code class="notranslate">select *</code> very frequently returns this error for me.</p>
<p dir="auto">Druid SQL doesn't support multi-value columns right now. They kind of work sometimes but not always.</p>
<ul dir="auto">
<li>They are reported as VARCHAR which does not give the user a hint that they are multi-value.</li>
<li><del>If you select one without aggregation then you get an error (<a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="247519522" data-permission-text="Title is private" data-url="https://github.com/apache/druid/issues/4637" data-hovercard-type="issue" data-hovercard-url="/apache/druid/issues/4637/hovercard" href="https://github.com/apache/druid/issues/4637">#4637</a>) as the runtime tries to cast them to string but cannot.</del> Fixed by <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="369729647" data-permission-text="Title is private" data-url="https://github.com/apache/druid/issues/6462" data-hovercard-type="pull_request" data-hovercard-url="/apache/druid/pull/6462/hovercard" href="https://github.com/apache/druid/pull/6462">#6462</a>.</li>
<li>If you group on one you get the standard Druid multi-value grouping behavior, which is surprising in the context of SQL.</li>
<li>Since Druid SQL thinks they're VARCHARs, you write filters on them as if you were filtering on a string column. But the planner may do some inappropriate optimizations (like optimize <code class="notranslate">a = 'x' AND a = 'y'</code> to <code class="notranslate">FALSE</code> even though a multi-value column could have both values).</li>
<li>I'm not sure what happens when a multi-value column is involved in a semi-join but I'm sure it's not anything good.</li>
</ul>
<p dir="auto">I'm not sure how to best express multi-value columns in SQL. Ideas are welcome. See also a calcite dev thread: <a href="https://www.mail-archive.com/dev@calcite.apache.org/msg02846.html" rel="nofollow">https://www.mail-archive.com/dev@calcite.apache.org/msg02846.html</a></p>