Union all problem

Follow

ggc888

• July 20, 2020 01:42

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 ?

0

• 

  Candido Dessanti

  • July 20, 2020 15:39

  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.

  0

  Comment actions Permalink
• 

  ggc888

  • July 21, 2020 02:12

  It must use "shared dictionary"... Do you have a road map for not using a dictionary for "union" ?

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • July 21, 2020 05:47

  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.

  0

  Comment actions Permalink
• 

  Gopinath Jaganmohan

  • October 28, 2021 21:01

  Hi Candido,

  Is the Union All problem without a shared dictionary fixed?

  Regards Gopinath

  0

  Comment actions Permalink
• 

  Todd Mostak

  • October 28, 2021 22:57

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

  0

  Comment actions Permalink
• 

  Gopinath Jaganmohan

  • February 10, 2022 15:47

  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

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • February 10, 2022 21:12

  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

  

  Now you can also use aggregates in the queries

  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

  0

  Comment actions Permalink
• 

  Gopinath Jaganmohan

  • February 11, 2022 17:57

  Hi Candido,

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

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • February 12, 2022 08:53

  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

  0

  Comment actions Permalink
• 

  Gopinath Jaganmohan

  • February 22, 2022 03:23

  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

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • February 23, 2022 03:52

  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

  https://community.heavy.ai/t/group-by-failing-on-coalesce-with-string-from-different-columns/2898?u=candido.dessanti

  0

  Comment actions Permalink

Please sign in to leave a comment.