Union all problem
I test UNION on V5.3 and V5.3.1
create table tt1 (fid int, f2 TEXT ENCODING DICT(8)); create table tt2 (fid int, f2 TEXT ENCODING DICT(8));
select fid from tt1 UNION all select fid from tt2 ---- it is OK!
select * from tt1 UNION all select * from tt2 --- Subqueries of a UNION must have exact same data types. ??
what's the probleam ?
Nice to have you also in the Community forum.
I checked out, and it looks to be a bug on text datatypes; while the datatypes are precisely the same, the system believes they aren't.
Have you tried to run
select f2 from tt1 union all select f2 from tt2
I guess you will get the same error.
but if you change the DDL in this way
create table tt1 (fid int, f2 TEXT ENCODING DICT(8)); create table tt2 (fid int, f2 TEXT, shared dictionary (f2) on tt1(f2));
select * from tt1 UNION all select * from tt2
Would work flawlessly.
Hi @Gopinath_Jaganmohan, we are PRing a fix that allows columns with different string dictionaries to be properly unioned (without needing shared dictionaries, although that will always be preferable to maximize performance as otherwise dictionary translation is needed), and assuming all goes well it should be released before end-of-year. We'll keep you posted on the details, and thanks for your patience (and persistence!).
I tried UNION ALL and got this error. But I assume this should work without any issue right?
Error: UNION is not supported yet. There is an experimental enable-union option available to enable UNION ALL queries.
I have also tried enabling.
/omnisci/bin/omnisci_server $MAPD_DATA --config $MAPD_CONFIG --enable-union --allow-loop-joins --enable-runtime-udf --enable-table-functions --enable-interoperability
the SQL operator
union allgot several enhancements lately, so work perfectly with TEXT encoded fields that haven't the dictionary shared
Now you can also use aggregates in the queries
To enable the union all feature, you have to use the
enable-unionparameter set to true
mapd@zion-tr:/opt/mapd/omnisci-ee-5.10.0-20220107-8939c3b1c4-Linux-x86_64-render$ bin/omnisci_server --data /opt/mapd_storage/data48 --stringdict-parallelizm --enable-union=true
It should be enabled also without specifying the true value as you did; does it works for you?
Hi Candido, Thanks for it. After upgrading it worked.
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"""
Pls can you help to resolve this issue.
We are discussing about this particular issue in the new thread you opened.
As pointed out by @todd , the problem is related to dictionaries, that cannot be mixed right now, and the workaround is to use shared dictionaries.
Let's continue the discussion about this issue in the new thread
Please sign in to leave a comment.