union two Imcs queries is not returning any rows when first query is not returning any rows

[simovesterinen]

Hi,

I have the following dynamic query:

SELECT DISTINCT (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[1]:: integer AS A0008, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[2]:: integer AS A0010, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[3]::integer AS A0010_name, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[4]:: integer AS A0019, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[5]::integer AS A0019_name, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[6]:: integer AS A0009, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[7]::integer AS A0009_name from Sales_Pipeline_imcs_5_get(array['20140112Native']) q, cs_project_agg(cs_hash_sum(cs_filter(((q.A0019 = cs_const(cs_str2code('25')))),q.M0516),cs_filter(((q.A0019 = cs_const(cs_str2code('25')))),q.A0008||q.A0010||q.A0010_name||q.A0019||q.A0019_name||q.A0009||q.A0009_name))) qq UNION
SELECT (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[1]:: integer AS A0008, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[2]:: integer AS A0010, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[3]::integer AS A0010_name, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[4]:: integer AS A0019, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[5]::integer AS A0019_name, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[6]:: integer AS A0009, (cs_as_array((qq).group_by,'i4i4i4i4i4i4i4'))[7]::integer AS A0009_name from Sales_imcs_get('Native') q, cs_project_agg(cs_hash_sum(cs_filter(((q.A0019 = cs_const(cs_str2code('25')))),q.M0001),cs_filter(((q.A0019 = cs_const(cs_str2code('25')))),q.A0008||q.A0010||q.A0010_name||q.A0019||q.A0019_name||q.A0009||q.A0009_name))) qq;

If I run the first query separately is not returning any rows. this is correct there is no data with the filters selected.

If I run the second query separately there are rows. This is also correct.

When I run the two queries with union together there are no rows. This is not correct. Why are there no returning rows of the union query?

I was trying to create a work around of this issue with temp table in a pgsql function but that either doens't seem to work (I was looping the queries and inserting them separately to the temp table.) Still after the function even a temp table had not rows.

Is this a bug or am I doing something wrong? I would really need this to be solved.

Best Regards

[knizhnik]

Hi, Yes, it seems to be a bug in IMCS. I will try to fix it. As workaround please pass disable_caching=true parameter to cs_project_agg function.

[simovesterinen]

Thank you :)