Group By Failing on coalesce with String from different Columns

We got into another issue. The below query is failing with “‘Cannot group by string columns which are not dictionary encoded.’”

“”“SELECT
coalesce (sales1.m_unique_id , sales2.m_customer_name) as d, count(*)
from #sales_orders as sales1 LEFT JOIN (select * from #sales_orders) as sales2 on sales1.m_unique_id = sales2.m_unique_id group by d”""

Can you please help to resolve this issue.

–Gopi

Hi Gopi, thanks for reaching out.

The reason why that currently doesn’t work is requires translation between multiple string dictionaries (one for each column), but it should work if you set each of the columns up with shared dictionaries (described here: Datatypes - OmniSci Docs).

That said, in our next release there will be a number of new capabilities allowing such translations, including case statements involving multiple string-dictionary encoded outputs (not sharing dictionaries), UNION ALL, and tests for string equality. With the new string translation framework something like COALESCE should be doable as well, although we haven’t started work on that yet.

We can provide more detail after evaluating what this would require, but until then your best bet would likely be the shared dictionary approach, i.e.

create table flights (...
SHARED DICTIONARY (origin_city) REFERENCES us_geography(city),
SHARED DICTIONARY (origin_state) REFERENCES us_geography(state),
SHARED DICTIONARY (origin_country) REFERENCES us_geography(country),
SHARED DICTIONARY (dest_city) REFERENCES us_geography(city),
SHARED DICTIONARY (dest_state) REFERENCES us_geography(state),
SHARED DICTIONARY (dest_country) REFERENCES us_geography(country),

Let us know if this helps!

2 Likes

Thanks Todd. We have the setup without Shared dictionary need to check how to make this work.