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 ?

Hi @ggking,

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));

the query

select * from tt1
UNION all
select * from tt2

Would work flawlessly.

It must use “shared dictionary”…
Do you have a road map for not using a dictionary for “union” ?

Hi,

we have just issued a patch that changes the error message, pointing the users to the use of shared dictionaries, but I don’t know when a real patch will land to correct this issue.

Anyway I will warn you when the issue will be fixed.

Hi Candido,

Is the Union All problem without a shared dictionary fixed?

Regards
Gopinath

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!).

Hi Todd,

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

Hi @Gopinath_Jaganmohan,

the SQL operator union all got several enhancements lately, so work perfectly with TEXT encoded fields that haven’t the dictionary shared

image

Now you can also use aggregates in the queries
image

To enable the union all feature, you have to use the enable-union parameter 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?

Regards,
Candido

Hi Candido,

Thanks for the explanation. I tried enabling it using --enable-union. Let me do it using --enable-union=true

Hi,

It should be the same; I tried both ways using the a 5.10 server; with 5.9 those features aren’t implemented, so if you are on a version older than 5.10 you have to upgrade

Regards,
Candido

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.

Regards
Gopinath

Hi @Gopinath_Jaganmohan,

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